GA4 BigQuery analysis

-- ab-test-by-variant.sql
-- Per-variant conversion analysis for one ABTestly experiment.
-- Joins `experience_impression` exposures to a conversion event on user_pseudo_id.
--
-- Parameters (replace before running, or set as @-bound parameters):
--   @project        e.g. "my-gcp-project"
--   @dataset        e.g. "analytics_123456789"
--   @experiment_id  the ABTestly per-org display number, e.g. "7"
--   @conversion     the GA4 event name to attribute, e.g. "purchase" or "sign_up"
--   @start_date     inclusive, YYYYMMDD, e.g. "20260501"
--   @end_date       inclusive, YYYYMMDD, e.g. "20260531"

WITH events AS (
  SELECT
    user_pseudo_id,
    event_name,
    event_timestamp,
    (
      SELECT value.string_value
      FROM UNNEST(event_params)
      WHERE key = 'exp_variant_string'
    ) AS exp_variant_string,
    (
      SELECT value.string_value
      FROM UNNEST(event_params)
      WHERE key = 'experiment_id'
    ) AS experiment_id,
    (
      SELECT value.string_value
      FROM UNNEST(event_params)
      WHERE key = 'variant_id'
    ) AS variant_id,
    -- Revenue: prefer `value` parameter (purchase events) else event_value_in_usd
    COALESCE(
      (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'),
      event_value_in_usd,
      0
    ) AS revenue
  FROM `@project.@dataset.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '@start_date' AND '@end_date'
),

-- Users exposed to each variant of THIS experiment.
exposed AS (
  SELECT
    variant_id,
    exp_variant_string,
    user_pseudo_id,
    COUNT(*) AS impressions
  FROM events
  WHERE event_name = 'experience_impression'
    AND experiment_id = '@experiment_id'
  GROUP BY variant_id, exp_variant_string, user_pseudo_id
),

-- Conversions BY exposed users — joined on user, only counted if it
-- happened on or after the user's first exposure.
first_exposure AS (
  SELECT
    user_pseudo_id,
    variant_id,
    MIN(event_timestamp) AS first_exposure_ts
  FROM events
  WHERE event_name = 'experience_impression'
    AND experiment_id = '@experiment_id'
  GROUP BY user_pseudo_id, variant_id
),

conversions AS (
  SELECT
    fe.variant_id,
    e.user_pseudo_id,
    COUNT(*) AS conversion_events,
    SUM(e.revenue) AS revenue
  FROM events e
  JOIN first_exposure fe USING (user_pseudo_id)
  WHERE e.event_name = '@conversion'
    AND e.event_timestamp >= fe.first_exposure_ts
  GROUP BY fe.variant_id, e.user_pseudo_id
)

SELECT
  e.variant_id,
  ANY_VALUE(e.exp_variant_string) AS exp_variant_string,
  COUNT(DISTINCT e.user_pseudo_id) AS users,
  SUM(e.impressions) AS impressions,
  COUNT(DISTINCT c.user_pseudo_id) AS converters,
  COALESCE(SUM(c.conversion_events), 0) AS conversion_events,
  COALESCE(SUM(c.revenue), 0) AS revenue,
  SAFE_DIVIDE(COUNT(DISTINCT c.user_pseudo_id), COUNT(DISTINCT e.user_pseudo_id))
    AS conversion_rate
FROM exposed e
LEFT JOIN conversions c
  ON c.variant_id = e.variant_id
  AND c.user_pseudo_id = e.user_pseudo_id
GROUP BY e.variant_id
ORDER BY e.variant_id;