-- ---------------------------------------------------------------------------
-- Legacy bounce rate, the production way: a daily upsert that fills in
-- the column GA4 buried.
--
-- Companion to: https://frequentlybayesian.net/writing/ga4-legacy-bounce-rate
-- Author:      Corey Abramson, frequentlybayesian.net
-- ---------------------------------------------------------------------------
--
-- What this computes
--
--   For each (date, page_path), the share of pageviews on that URL that
--   were the last pageview in their session. That is the working
--   definition of a "bounce" once you have decided that the UA-strict
--   version (a session whose only event was a single page_view) is more
--   restrictive than is useful on a real ecommerce site.
--
--   Denominator: total pageviews of URL X in the window.
--   Numerator:   count of those pageviews that were the last pageview in
--                their session.
--
--   Both numerator and denominator are scoped to a single locale prefix.
--
--   This is a practical proxy, not strict UA bounce. The strict version
--   is sketched at the bottom of this header.
--
-- Production patterns worth calling out
--
--   1. CREATE TABLE IF NOT EXISTS with PARTITION BY date and CLUSTER BY
--      page_path so historical reads against the result table stay
--      cheap as the table grows.
--   2. MERGE upsert against a rolling 7-day window. Daily refresh, no
--      full-history recompute, late-arriving events absorbed by the
--      window.
--   3. Path normalization. Strip query string and fragment, lowercase
--      the path, drop the trailing slash except for the root. Without
--      this step /products and /products/ count as two different pages.
--   4. Last pageview per session via ROW_NUMBER() OVER PARTITION BY
--      (user_pseudo_id, ga_session_id, ga_session_number) ORDER BY
--      event_timestamp DESC. The session_number partition matters when
--      the same user opens multiple sessions inside the window.
--   5. ROW_NUMBER is computed across ALL pageviews first, then the locale
--      filter is applied. Filtering before ranking would falsely mark a
--      locale page as the session-ender when the user actually continued
--      into a different locale.
--
-- Placeholders to replace before you run this:
--
--   <your-project>                  GCP project id
--   <your-export-dataset>           GA4 BigQuery export dataset, looks
--                                   like analytics_NNNNNNNNN
--   <your-destination-dataset>      where the upsert lands
--   <your-destination-table>        the upsert destination table
--   <your-locale-prefix>            path prefix to scope a single locale
--                                   (for example /en-us or /shop). If
--                                   you do not want a locale filter,
--                                   remove the STARTS_WITH lines from
--                                   pageviews_localized and exits_agg.
--
-- ---------------------------------------------------------------------------
-- Variant: the UA-strict version
--
-- If you want the strict UA definition (single-event session, single
-- page_view, no other events), replace the exits_agg CTE with one that
-- joins every page_view in the window against the count of total events
-- in that session, and keeps only the rows where the count is one. The
-- resulting numerator is smaller and the rate is harsher, but it matches
-- the UA bounce definition exactly. For most page-level CRO work, the
-- looser exit-style definition below is more actionable.
--
-- ---------------------------------------------------------------------------

-- Rolling 7-day window, inclusive of today.
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY);
DECLARE end_date   DATE DEFAULT CURRENT_DATE();

-- Create destination table on first run, with partitioning and clustering
-- so downstream reads stay cheap.
CREATE TABLE IF NOT EXISTS `<your-project>.<your-destination-dataset>.<your-destination-table>`
(
  date         DATE,
  page_path    STRING,
  pageviews    INT64,
  exits        INT64,
  exit_rate    FLOAT64
)
PARTITION BY date
CLUSTER BY page_path;

-- Upsert the rolling window so reruns dedupe cleanly by (date, page_path).
MERGE `<your-project>.<your-destination-dataset>.<your-destination-table>` T
USING (
  WITH
    -- Single pass over page_view events in the rolling window.
    pageviews AS (
      SELECT
        PARSE_DATE('%Y%m%d', event_date)                                                      AS date,
        user_pseudo_id,
        (SELECT value.int_value    FROM UNNEST(event_params) WHERE key = 'ga_session_id')     AS ga_session_id,
        (SELECT value.int_value    FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location')     AS page_location,
        event_timestamp
      FROM `<your-project>.<your-export-dataset>.events_*`
      WHERE event_name = 'page_view'
        AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date)
                              AND FORMAT_DATE('%Y%m%d', end_date)
    ),

    -- Normalize the path. Strip query string and fragment, lowercase, drop
    -- trailing slash except for the root. Without this step /products and
    -- /products/ count as different pages.
    pageviews_norm AS (
      SELECT
        date,
        user_pseudo_id,
        ga_session_id,
        ga_session_number,
        event_timestamp,
        CASE
          WHEN LOWER(COALESCE(REGEXP_EXTRACT(page_location, r'https?://[^/]+(/[^?#]*)'), '/')) = '/'
            THEN '/'
          ELSE REGEXP_REPLACE(
                 LOWER(COALESCE(REGEXP_EXTRACT(page_location, r'https?://[^/]+(/[^?#]*)'), '/')),
                 r'/$',
                 ''
               )
        END AS page_path
      FROM pageviews
      WHERE ga_session_id IS NOT NULL
    ),

    -- Scope to a single locale prefix.
    pageviews_localized AS (
      SELECT *
      FROM pageviews_norm
      WHERE STARTS_WITH(page_path, '<your-locale-prefix>')
    ),

    -- Denominator: total pageviews per page per date, locale-scoped.
    pageviews_agg AS (
      SELECT
        date,
        page_path,
        COUNT(*) AS pageviews
      FROM pageviews_localized
      GROUP BY date, page_path
    ),

    -- Numerator: the last pageview in each session.
    -- ROW_NUMBER ranks pageviews per session by timestamp DESC. rn = 1
    -- is the last pageview of the session. We rank across ALL pageviews,
    -- then filter to the locale, so a session that left the locale is
    -- not falsely credited as ending on a locale page.
    exits_agg AS (
      SELECT
        date,
        page_path,
        COUNT(*) AS exits
      FROM (
        SELECT
          date,
          page_path,
          ROW_NUMBER() OVER (
            PARTITION BY user_pseudo_id, ga_session_id, ga_session_number
            ORDER BY event_timestamp DESC
          ) AS rn
        FROM pageviews_norm
      )
      WHERE rn = 1
        AND STARTS_WITH(page_path, '<your-locale-prefix>')
      GROUP BY date, page_path
    )

  SELECT
    p.date,
    p.page_path,
    p.pageviews,
    COALESCE(e.exits, 0)                           AS exits,
    SAFE_DIVIDE(COALESCE(e.exits, 0), p.pageviews) AS exit_rate
  FROM pageviews_agg p
  LEFT JOIN exits_agg e USING (date, page_path)
) S
ON  T.date      = S.date
AND T.page_path = S.page_path
WHEN MATCHED THEN
  UPDATE SET
    pageviews = S.pageviews,
    exits     = S.exits,
    exit_rate = S.exit_rate
WHEN NOT MATCHED THEN
  INSERT (date, page_path, pageviews, exits, exit_rate)
  VALUES (S.date, S.page_path, S.pageviews, S.exits, S.exit_rate);
