Calculation of avg time on site from DW data

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!