aw_page.sql

WITH
  -- Subquery to define static and/or dynamic start and end date for the whole query
  period AS (
  SELECT
    '20200220' AS start_date,
    FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS end_date),
  -- Subquery to prepare and calculate page view data
  pages AS (
  SELECT
    user_pseudo_id,
    (
    SELECT
      value.int_value
    FROM
      UNNEST(event_params)
    WHERE
      event_name = 'page_view'
      AND key = 'ga_session_id') AS session_id,
    event_timestamp,
    event_name,
    (
    SELECT
      device.web_info.hostname
    FROM
      UNNEST(event_params)
    WHERE
      event_name = 'page_view'
      AND key = 'page_location') AS hostname,
    (
    SELECT
      value.string_value
    FROM
      UNNEST(event_params)
    WHERE
      event_name = 'page_view'
      AND key = 'page_location') AS page,
    LAG((
      SELECT
        value.string_value
      FROM
        UNNEST(event_params)
      WHERE
        event_name = 'page_view'
        AND key = 'page_location'), 1) OVER (PARTITION BY user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params)
      WHERE
        event_name = 'page_view'
        AND key = 'ga_session_id')
    ORDER BY
      event_timestamp ASC) AS previous_page,
    CASE
      WHEN SPLIT(SPLIT(( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location'),'/')[SAFE_ORDINAL(4)],'?')[SAFE_ORDINAL(1)] = '' THEN NULL
    ELSE
    CONCAT('/',SPLIT(SPLIT((
          SELECT
            value.string_value
          FROM
            UNNEST(event_params)
          WHERE
            event_name = 'page_view'
            AND key = 'page_location'),'/')[SAFE_ORDINAL(4)],'?')[SAFE_ORDINAL(1)])
  END
    AS pagepath_level_1,
    CASE
      WHEN SPLIT(SPLIT(( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location'),'/')[SAFE_ORDINAL(5)],'?')[SAFE_ORDINAL(1)] = '' THEN NULL
    ELSE
    CONCAT('/',SPLIT(SPLIT((
          SELECT
            value.string_value
          FROM
            UNNEST(event_params)
          WHERE
            event_name = 'page_view'
            AND key = 'page_location'),'/')[SAFE_ORDINAL(5)],'?')[SAFE_ORDINAL(1)])
  END
    AS pagepath_level_2,
    CASE
      WHEN SPLIT(SPLIT(( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location'),'/')[SAFE_ORDINAL(6)],'?')[SAFE_ORDINAL(1)] = '' THEN NULL
    ELSE
    CONCAT('/',SPLIT(SPLIT((
          SELECT
            value.string_value
          FROM
            UNNEST(event_params)
          WHERE
            event_name = 'page_view'
            AND key = 'page_location'),'/')[SAFE_ORDINAL(6)],'?')[SAFE_ORDINAL(1)])
  END
    AS pagepath_level_3,
    CASE
      WHEN SPLIT(SPLIT(( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location'),'/')[SAFE_ORDINAL(7)],'?')[SAFE_ORDINAL(1)] = '' THEN NULL
    ELSE
    CONCAT('/',SPLIT(SPLIT((
          SELECT
            value.string_value
          FROM
            UNNEST(event_params)
          WHERE
            event_name = 'page_view'
            AND key = 'page_location'),'/')[SAFE_ORDINAL(7)],'?')[SAFE_ORDINAL(1)])
  END
    AS pagepath_level_4,
    (
    SELECT
      value.string_value
    FROM
      UNNEST(event_params)
    WHERE
      event_name = 'page_view'
      AND key = 'page_title') AS page_title,
    CASE
      WHEN ( SELECT value.int_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'entrances') = 1 THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location')
  END
    AS landing_page,
    CASE
      WHEN ( SELECT value.int_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'entrances') = 1 THEN LEAD(( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location'), 1) OVER (PARTITION BY user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'ga_session_id') ORDER BY event_timestamp ASC)
    ELSE
    NULL
  END
    AS second_page,
    CASE
      WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location') = FIRST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location')) OVER (PARTITION BY user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'ga_session_id') ORDER BY event_timestamp DESC) THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location')
    ELSE
    NULL
  END
    AS exit_page
  FROM
    -- Change this to your Google Analytics 4 export location in BigQuery
    `ga4.analytics_1234567890.events_*`,
    period
  WHERE
    _table_suffix BETWEEN period.start_date
    AND period.end_date
    AND event_name = 'page_view'
  ORDER BY
    1,
    2,
    3,
    4)
SELECT
  -- Hostname (dimension | the hostname from which the tracking request was made)
  hostname,
  -- Page (dimension | a page on the website specified by path and/or query parameters)
  page,
  -- Previous Page Path (dimension | a page visited before another page on the same property)
  previous_page,
  -- Page path level 1 (dimension | this dimension rolls up all the page paths in the first hierarchical level)
  pagepath_level_1,
  -- Page path level 2 (dimension | this dimension rolls up all the page paths in the second hierarchical level)
  pagepath_level_2,
  -- Page path level 3 (dimension | this dimension rolls up all the page paths in the third hierarchical level)
  pagepath_level_3,
  -- Page path level 4 (dimension | this dimension rolls up all the page paths in the fourth hierarchical level)
  pagepath_level_4,
  -- Page Title (dimension | the web page's title, multiple pages might have the same page title)
  page_title,
  -- Landing Page (dimension | the first page in users' sessions)
  landing_page,
  -- Second Page (dimension | the second page in users' sessions)
  second_page,
  -- Exit Page (dimension | the last page in users' sessions)
  exit_page,
  -- Entrances (metric | the number of entrances to the property measured as the first pageview in a session)
  COUNT(landing_page) AS entrances,
  -- Pageviews (metric | the total number of pageviews for the property)
  COUNT(page) AS pageviews,
  -- Unique Pageviews (metric | the number of sessions during which the specified page was viewed at least once, a unique pageview is counted for each page URL + page title combination)
  COUNT(DISTINCT CONCAT(page,page_title,session_id)) AS unique_pageviews,
  -- Pages / Session (metric | the average number of pages viewed during a session, including repeated views of a single page)
  COUNT(page)/COUNT(DISTINCT session_id) AS pages_per_session,
  -- Exits (metric | the number of exits from the property)
  COUNT(exit_page) AS exits,
  -- Exit % (metric | the percentage of exits from the property that occurred out of the total pageviews)
  COUNT(exit_page)/COUNT(page) AS exit_rate
FROM
  pages,
  period
GROUP BY
  1,
  2,
  3,
  4,
  5,
  6,
  7,
  8,
  9,
  10,
  11
LIMIT
  10