Calculation of avg time on site from DW data

[KapilGh1]

How to calculate avg time on site using adobe datawarehouse data ?

[Jennifer_Dungan]

I don't think that the DW has the granularity to do this properly... 

If you need to bring data outside of Adobe and have access to complex calculations like that, you need to use the Raw Data feeds where you can identify each user, each visit of each user, and access to all the timestamps of every page that each user accessed.

But Raw Data needs to also replicate all the exclusion logic, on top of identifying each and every user and visit... it's not an insignificant amount of work....

[Vinay_Chauhan · Accepted solution]

Data Warehouse (DW) doesn’t natively provide session-level granularity or timestamps per hit, so calculating average time on site directly from DW is limited.

To get an accurate average time on site, the best approach is to use Raw Data Feeds, as @Jennifer_Dungan mentioned, which include detailed timestamps (like hit_time_gmt) and session-level identifiers (visit_num, visitor_id). With that, you can calculate time on site per visit by:

1. Grouping hits by visitor ID and visit number.

2. Sorting by timestamp.

3. Taking the difference between the last and first timestamp within each visit to get the total duration.

4. Averaging those durations across all visits.

Do note that Adobe excludes single-page visits from time on site by default, if you want to replicate that behavior, you’ll need to apply the same logic (i.e., exclude visits with only one hit).

This method gives the closest approximation to how Adobe calculates it internally, but it does require building the logic from the ground up using the raw data.
Hope that helps!