How to calculate Hit Depth in Adobe Data Feeds

[alvaroP]

Dear all.

I was trying to replicate the following Adobe Analytics segment in Adobe Data Feeds Extraction:

This is how I wrote in SQL to return the visits in for example, august-2022:

	select distinct post_visid_high||post_visid_low||visit_num||visit_start_time_gmt as visit_id
	from daci_privatespace.adobe_datafeeds
	where
	    exclude_hit = '0'
	   	and hit_source not in (5,7,8,9)
		and "_corrupt_record" is null
	    and upper(page_event_var2) in ('SAVINGPLANIMP','A/B TEST TRACK','SAVINGS PLAN NEW ELIGIBLE', 'SAVINGPLANELIGIBLE', 'SAVINGS PLAN RENEWAL ELIGIBLE')
	   	and visit_page_num =1 -- Hit Depth
	    and upper(post_prop47) like '%/CHECKOUT/PREVIEW%'
	    and reference_date >= date('2022-08-01') and reference_date <= date('2022-08-31') --Test

The thing is that I see a big discrepancy in Adobe Visits vs. Adobe Data Feeds Visits. (around 4% of discrepancy) and I realized that it is because I'm replicating fine the Hit Depth in Adobe (I removed this filter in both places and discrepancy reduced to 0,2%).

So my question is, how could I replicate exactly the Hit Depth Behaviour in Adobe Data Feeds extraction. I checked the Data Columns Data feeds info from Adobe Support and I read that I was using the right field, but maybe I'm missing something (https://experienceleague.adobe.com/docs/analytics/export/analytics-data-feed/data-feed-contents/data...) 

Thanks in advance. Regards

Update: I can't reply (I don´t know why). Sorry @Jennifer_Dungan but I don't see your point. I'm bringing the Visits that matches in one hit these three conditions. In my SQL I'm doing the same thing. In fact, I've tested without Hit Depth condition (neither SQL nor Adobe Segment) and the results are the sames 

Anyway, just to avoid anything. I've modified the Segment to a Hit Level and also the SQL to a Hit Level and happens the same thing (big discrepancy)

	select count(cast(hitid_high as varchar)||cast(hitid_low as varchar)) as hit_id
	from daci_privatespace.adobe_datafeeds
	where
	    exclude_hit = '0'
	   	and hit_source not in (5,7,8,9)
		and "_corrupt_record" is null
	    and upper(page_event_var2) in ('SAVINGPLANIMP','A/B TEST TRACK','SAVINGS PLAN NEW ELIGIBLE', 'SAVINGPLANELIGIBLE', 'SAVINGS PLAN RENEWAL ELIGIBLE')
	   	and visit_page_num =1 -- Hit Depth
	    and upper(post_prop47) like '%/CHECKOUT/PREVIEW%'
	    and reference_date >= date('2022-07-01') and reference_date <= date('2022-08-31') --Test

Edit 2: Sorry again for mention you @Jennifer_Dungan . If you see my second example, you can see that I'm trying to extract the Hit, and the same error arrises (discrepancy due to visit_page_num=1 is not equivalent to Hit Depth = 1).

I've tried an alternative way which is take the first hit of the visit (not necessary where visit_page_num=1) and the results are better, but still with big discrepancy.

Do you know if there is any way to replicate Hit Depth=1 in Adobe Data Feeds?

Thanks and sorry again

Edit 3: Sorry again for mention you @Jennifer_Dungan, The Hit Depth gets increase in every Hit, even with there is in some of the hit any Page Load. The question is, like you can see in my second example, I'm extracting the first Hit (hit_depth=1) and check the conditions other conditions, but like I see that Hit_depth=1 is not the same that visit_page_num=1 (like Adobe mentions in the reference columns https://experienceleague.adobe.com/docs/analytics/export/analytics-data-feed/data-feed-contents/data...)

Additionaly, (and also opened another questions in the community) I also check that some of the behaviour metrics are not working in Adobe Data Feeds like it is explained in the documentation (for example: https://experienceleaguecommunities.adobe.com/t5/adobe-analytics-questions/entry-page-type-wrong-cal...) 

[Jennifer_Dungan · Accepted solution]

Part of the discrepancy is likely that your segment is a "VISIT" level definition... you will be pulling back ALL the hits in a visit where there is any hit that matches your criteria... I don't see any such logic in your SQL.. that appears to just be pulling in the hits where the criteria matches....

[Jennifer_Dungan]

Hi, no worries @alvaroP ... I am not sure why you can't reply either... I've seen other people with the same issue...

Your Workspaces segment looks like this:

Visit

        (hit container)

         condition 1

         AND

         condition 2

         AND

         condition 3

The HIT container is looking for hits that have all three conditions met (this is fine)... but let's look at what this will return in your data:

Page 1 > Page 2 > Page 3 > Page 4 > Page 5

(because you have a condition for page depth = 1, well will assume that Page 1 is the container match)

But your segment will return ALL the page views and actions for the entire visit (including Page 2, Page 3, Page 4, Page 5, etc) because the scope of the segment is Visits that include Hit X

Your SQL query is only pulling out Hit X... I don't see anything where you are getting the Visit Number, then pulling all subsequent hits for that User that are in the same Visit.

If you only want the Hits that meet the criteria, then you need to change your Segment Scope to be HIT instead of Visit. If you are actually trying to pull out all the pages in the visit, then your SQL has to changed to retrieve all the Hits within the identified visit (based on the visit number of the matched Hit)

Right now your segment and your SQL are looking at very different scopes of data.

[Jennifer_Dungan]

I think the last part of the SQL may be that the visit_page_num increments on each row... I think you will have to exclude subsequent hits?

I don't work in the raw data directly, but often chat and screen share with people who do... but if you pull our a user.. and pull ALL the hits by a specific visit number, what does the visit_page_num data look like on each hit? Does it go from 1 to 2 to 3, etc?

If so, you will need to add some extra logic to exclude those visits...