Expand my Community achievements bar.

Join us at Adobe Summit 2024 for the Coffee Break Q&A Live series, a unique opportunity to network with and learn from expert users, the Adobe product team, and Adobe partners in a small group, 30 minute AMA conversations.
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

Avatar

Level 1
 
1 Accepted Solution

Avatar

Correct answer by
Employee Advisor

@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

 

 

View solution in original post

3 Replies

Avatar

Correct answer by
Employee Advisor

@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

 

 

Avatar

Community Advisor

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.