Can we use data feed to calculate average time on site or time spent per visit, if yes , what will be the sql query to do so. ? Our data feed goes to data lake. Want to know if we can generate a query which can help to calculate those two metrics | Community
Skip to main content
M
mahjoshi
New Participant
May 9, 2022
Solved

Can we use data feed to calculate average time on site or time spent per visit, if yes , what will be the sql query to do so. ? Our data feed goes to data lake. Want to know if we can generate a query which can help to calculate those two metrics

  • May 9, 2022
  • 3 replies
  • 2292 views
No text available
This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by VaniBhemarasetty

@mahjoshi You can definitely try calculating this metric using data feeds. Before doing so, it is important to know how this metric works. 

For a given dimension item, take the timestamp of each hit where that dimension item exists. Compare it with the timestamp of the next hit in the visit. If the hit doesn’t have a subsequent hit, do not include it in this metric. Out of all the time spent for the dimension item, divide them all by the number of “sequences” for that dimension item. A “sequence” is where a dimension item is the same for one or more consecutive hits. This resulting number is the metric displayed in reports.

 

Based on this calculation, pick up post_evar/or any post column of the dimension and then pickup of date_time/hit_time_gmt column which has the timestamp of the hit difference of this value between two hits divided by a number of sequences should give you the average time spent on site.

 

You can try this logic and build a SQL query to get this metric.

 

Hope this helps.

 

Regards

Vani

 

 

3 replies

leocwlau
leocwlau
New Participant
May 10, 2022

@mahjoshi the calculation logic can be found at https://experienceleague.adobe.com/docs/analytics/components/metrics/average-time-on-site.html?lang=en with a bit of illustration.

Jennifer_Dungan
Jennifer_Dungan
New Participant
May 9, 2022

This should be doable. Raw Data feeds contain all the timestamps for all server calls. Per user (based on visid _high and _low) you would need to identify first page view/server call in the user's visit, and the last server call made by that user within each visit (you should be able to use visit number to disambiguate the visits from one another). Then you should be able to calculate the time spent in each visit, then average that across that all your users.

 

Don't forget in Raw Data that you need to ensure that like Workspace, you need to make sure that you don't include any hits that should be excluded (i.e. bots, internal IP filters, etc). Raw Data is just that, all the data collected (including data that is essentially thrown out) is provided in this feed.

VaniBhemarasetty
VaniBhemarasettyAccepted solution
Employee
May 9, 2022

@mahjoshi You can definitely try calculating this metric using data feeds. Before doing so, it is important to know how this metric works. 

For a given dimension item, take the timestamp of each hit where that dimension item exists. Compare it with the timestamp of the next hit in the visit. If the hit doesn’t have a subsequent hit, do not include it in this metric. Out of all the time spent for the dimension item, divide them all by the number of “sequences” for that dimension item. A “sequence” is where a dimension item is the same for one or more consecutive hits. This resulting number is the metric displayed in reports.

 

Based on this calculation, pick up post_evar/or any post column of the dimension and then pickup of date_time/hit_time_gmt column which has the timestamp of the hit difference of this value between two hits divided by a number of sequences should give you the average time spent on site.

 

You can try this logic and build a SQL query to get this metric.

 

Hope this helps.

 

Regards

Vani

 

 

Terms & ConditionsAccessibility statement

Loading footer...