Time on Page with Windowing Function
February 15, 2016
|
SELECT
fullVisitorId,
visitId,
hits.hitNumber,
hits.page.pagePath,
-- get next and previous hit time to be able to work out length of each hit
hits.time AS hit_time,
LEAD(hits.time, 1) OVER (PARTITION BY fullVisitorId, visitId ORDER BY hits.time ASC) AS next_hit_time,
FROM
[google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
WHERE
hits.type = "PAGE"