Need SQL Query for "Avg Time on Site" from DataFeed

[Basu212]

Could anyone share the SQL query used to calculate "Average time on site" from the data-feed export?

[Krishna_Musku]

Hello @Basu212, Take a look at the below thread:

https://experienceleaguecommunities.adobe.com/t5/adobe-analytics-blogs/sample-sql-queries-to-query-a...

7.  To calculate time spent

SELECT CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt) as visit_id,
       visit_page_num,
       post_cust_hit_time,
       CASE 
          WHEN LEAD(visit_id) OVER (ORDER BY CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt), visit_page_num) = visit_id AND LEAD(visit_page_num) OVER (ORDER BY CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt), visit_page_num) > visit_page_num 
          THEN LEAD(post_cust_hit_time) OVER (ORDER BY CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt), visit_page_num) - post_cust_hit_time 
          ELSE NULL 
       END as time_spent
FROM your_data_feed_name
WHERE date >= 'your_start_date'
  AND date <= 'your_end_date'

Hope this helps!