Need BigQuery SQL query to collect time on page from Google Analytics data

Question

can anyone help with a BIgQuery SQL query to extract the time on page for a specific page from Google Analytics data please?

For every visitorId who has visited a particular page I would like the time on page for that page. This is so that I can calculate the median time on page rather than the mean.

I'm assuming that the visitorId, hits.hitsNumber and hits.time dimensions will be needed. Also that somehow the hits.time for the hit where the page was viewed will need to be subtracted from the hits.time of the following hit.

Any help much appreciated.

Answer 1

Try this:

SELECT 
  fullVisitorId,
  hits.page.hostname,
  hits.page.pagePath,
  hits.hitNumber,
  hits.time,
  nextTime,
  (nextTime - hits.time) as timeOnPage
FROM(
  SELECT
    fullVisitorId, 
    hits.page.hostname,
    hits.page.pagePath,
    hits.hitNumber,
    hits.time,
    LEAD(hits.time, 1) OVER (PARTITION BY fullVisitorId, visitNumber ORDER BY hits.time ASC) as nextTime
  FROM [PROJECTID:DATASETID.ga_sessions_YYYYMMDD]
  WHERE hits.type = "PAGE"
)

The key to this code is the LEAD() function, which grabs the specified value from the next row in the partition, based on the PARTITION BY and ORDER BY qualifiers.

Hope that helps!