Querying Hit Depth and Entry Page

Question

I am trying to query my data feed to exclude entry pages, but am coming up with a discrepancy and would appreciate any advice.

The result of the code below is 125229. This matches the hit depth dimension shown below.

select 
count(distinct(
case when visit_page_num = 1 and post_evar4 like '%account.box%' then concat(post_visid_high,post_visid_low) end))
FROM `adobe.adobe_data`
where date_trunc(date_time,month) = '2025-02-01' and exclude_hit = 0

However, when I change the query to visit_page_num <> 1 (exclude entry), I get a value of 195904, which is very different from 105,959.

select 
count(distinct(
case when visit_page_num <> 1 and post_evar4 like '%account.box%' then concat(post_visid_high,post_visid_low) end))
FROM `adobe.adobe_data`
where date_trunc(date_time,month) = '2025-02-01' and exclude_hit = 0

Account.box Segment = Contains 'account.box' (hit)

Entry Page <> account.box Segment = Exclude hit where entry page contains account.box

Can anyone explain why these numbers are different and if I should be considering another query?

czmudzin · Accepted Answer

Sorry for all of the replies. I found a solution by not using visit_page_num and instead replacing the logic with visit_start_pagename. This gave me a 0.3% difference:

select 
count(distinct(
case when post_pagename like '%account.box%' and visit_start_pagename not like '%account.box%' then concat(post_visid_high,post_visid_low) end))
FROM `adobe.adobe_data`
where date_trunc(date_time,month) = '2025-02-01' and exclude_hit = 0

Adobe Segments: 105,959

SQL Output: 105,693

Variance: 0.3%

Harveer_SinghGi1 · Answer

Hi @czmudzin ,The logic used in SQL and AA segment for second comparison is not the same. In SQL you are excluding all the entry pages by using "visit_page_num <> 1" clause while in AA segment you are still using Entry Page in the logic "Exclude hit where entry page contains account.box".Another thing, in the SQL for second comparison you haven't added the clause for checking the entry page value like you have in the segment.Try below query to replicate the logic you have in AA reports,select count(distinct(case when visit_page_num = 1 and post_evar4 like '%account.box%' and post_pagename not like '%account.box%' then concat(post_visid_high,post_visid_low) end))FROM `adobe.adobe_data`where date_trunc(date_time,month) = '2025-02-01' and exclude_hit = 0Cheers!

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded