Analytics
4.6K members online now
Understand information in your reports and troubleshoot reporting issues such as self-referrals, (not set) data, and inaccurate information
 
Guide Me
star_border
Reply

Big Query Export Average Time on Page

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hi,

I'm trying to determine the best way to query average time on page using the Big Query Analytics Export Schema (https://support.google.com/analytics/answer/3437719?hl=en&ref_topic=3416089). The issue is that the schema is framed by session, but the query is about pageviews. 

I've created a query that's correct in most (but not all) cases, but it's fairly expensive. Is there an easier way to extract this metric from the Big Query dataset?

WITH base AS (
  SELECT
    fullVisitorId,
    visitId,
    pageview_id,
    diff,
    hits,
    date,
    FIRST_VALUE(hits.page.pagePath) OVER (
      PARTITION BY visitId, fullVisitorId, pageview_id
      ORDER BY hits.hitNumber
    ) AS initialPage,
    COALESCE(
      MAX(hits.isExit) OVER (
      PARTITION BY visitId, fullVisitorId, pageview_id
      ORDER BY hits.hitNumber
    ), FALSE) AS isExit
FROM (
  SELECT
    visitId,
    fullVisitorId,
    date,
    SUM(CASE WHEN hits.type = 'PAGE' THEN 1 ELSE 0 END) OVER (
      PARTITION BY visitId, fullVisitorId
      ORDER BY hits.hitNumber ROWS UNBOUNDED PRECEDING
    ) AS pageview_id,
    LEAD(hits.time, 1) OVER (
      PARTITION BY visitId, fullVisitorId
      ORDER BY hits.hitNumber
    ) - hits.time AS diff,
    hits
    FROM
    `data-ga.xxxxxxx.ga_sessions_*`,
    UNNEST(hits) AS hits
    WHERE _TABLE_SUFFIX
    BETWEEN "20170120" AND "20170320"
    ORDER BY visitId, fullVisitorId
))

SELECT
SUM(CASE WHEN diff IS NOT NULL THEN diff ELSE 0 END) AS time_on_page,
SUM(CASE WHEN hits.type = 'PAGE' AND NOT isExit THEN 1 ELSE 0 END) AS pageviews
FROM base