Level 1

0% to

Level 2

Tip /

to gain points, level up, and earn exciting badges like the new
Mission!

View all

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

Level 1

1 Accepted Solution

Employee

@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

Employee

@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