A/B test analysis in BigQuery with SQL, for GA4 data

Before you start

Your test sends experience_impression with the variant in exp_variant_string (see A/B testing in GA4), and the daily BigQuery export is on. It has no backfill.

The fields that matter

event_name and event_timestamp — the timestamp is in microseconds, UTC.
user_pseudo_id — one browser. It’s the unit the test was assigned on.
event_params — a repeated key and value. A parameter lands in string_value, int_value or double_value depending on what it looked like when sent, so read it with a fallback, or a value that looks like a number will quietly come back empty:

(SELECT COALESCE(value.string_value, CAST(value.int_value AS STRING))
 FROM UNNEST(event_params) WHERE key = 'exp_variant_string') AS variant

ecommerce.purchase_revenue_in_usd — revenue on purchase events, already converted to dollars.

Daily tables are named events_YYYYMMDD; today’s streaming table, if you export one, is events_intraday_YYYYMMDD. The query reads events_* between two dates. Because intraday_ sorts after any date, digit bounds on _TABLE_SUFFIX leave the intraday tables out, so a day can’t be counted twice.

The method, step by step

Exposures. Every experience_impression for your experiment, from visitors with an id.
One row per visitor. Their first exposure, the variant they saw then, and how many variants they ever saw. A visitor who saw both is dropped: they belong to neither arm.
Conversions after exposure, inside a window. Only conversions from the first exposure on count, and only for a fixed number of days after it — so a visitor exposed on the last day isn’t judged on a shorter window than one exposed on the first. The query reads past the end date for that reason: run it once the window has passed.
Per arm: visitors, converters, revenue per visitor and its variance.
The tests. Every pair of arms, compared below.

Check the split before the result

A planned 50/50 split should come out close to 50/50. If it doesn’t (a sample ratio mismatch), something is broken and the result is meaningless. The query runs a chi-square test; under 0.001, stop and find the cause.

Conversion rate: interval and p-value

A 95% confidence interval and a two-proportion z-test. Read the interval first: if it spans zero, there’s no shown difference. BigQuery has no normal distribution function, so the query defines one (Abramowitz and Stegun, accurate to 1.5e-7).

Revenue per visitor

Conversion rate can rise while revenue falls. Revenue is averaged over every visitor, zeros included, and compared with Welch’s test. One large order can decide it: cap at the 99th percentile if you have outliers.

The query

Replace the table name and the five settings at the top.

-- Your experiment, its dates, what counts as converting, and for how long
-- after a visitor's first exposure.
DECLARE experiment STRING DEFAULT 'RIG-HERO1';
DECLARE start_day STRING DEFAULT '20261001';
DECLARE end_day STRING DEFAULT '20261028';
DECLARE goal STRING DEFAULT 'purchase';
DECLARE window_days INT64 DEFAULT 7;

-- The normal CDF, which BigQuery doesn't have: erf by Abramowitz and
-- Stegun 7.1.26, accurate to 1.5e-7.
CREATE TEMP FUNCTION erf_t(x FLOAT64) AS (1 / (1 + 0.3275911 * ABS(x)));
CREATE TEMP FUNCTION erf(x FLOAT64) AS (
  SIGN(x) * (1 - erf_t(x) * (0.254829592 + erf_t(x) * (-0.284496736 + erf_t(x)
    * (1.421413741 + erf_t(x) * (-1.453152027 + erf_t(x) * 1.061405429)))) * EXP(-x * x))
);
CREATE TEMP FUNCTION norm_cdf(z FLOAT64) AS (0.5 * (1 + erf(z / SQRT(2))));

WITH exposures AS (
  SELECT
    user_pseudo_id,
    event_timestamp,
    (SELECT COALESCE(value.string_value, CAST(value.int_value AS STRING))
     FROM UNNEST(event_params) WHERE key = 'exp_variant_string') AS variant
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN start_day AND end_day
    AND event_name = 'experience_impression'
    AND user_pseudo_id IS NOT NULL
),

-- One row per visitor: when they first saw the test, which variant, and
-- whether they ever saw another.
assigned AS (
  SELECT
    user_pseudo_id,
    MIN(event_timestamp) AS first_seen,
    ARRAY_AGG(variant ORDER BY event_timestamp LIMIT 1)[OFFSET(0)] AS variant,
    COUNT(DISTINCT variant) AS variants_seen
  FROM exposures
  WHERE STARTS_WITH(variant, experiment || '-')
  GROUP BY user_pseudo_id
),

-- Conversions, read past end_day so the last visitors get their full window.
goals AS (
  SELECT user_pseudo_id, event_timestamp, IFNULL(ecommerce.purchase_revenue_in_usd, 0) AS revenue
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN start_day
      AND FORMAT_DATE('%Y%m%d', DATE_ADD(PARSE_DATE('%Y%m%d', end_day), INTERVAL window_days DAY))
    AND event_name = goal
),

-- Only what happened after first exposure, inside the window, by visitors
-- who saw one variant.
users AS (
  SELECT
    a.variant,
    a.user_pseudo_id,
    COUNT(g.event_timestamp) > 0 AS converted,
    IFNULL(SUM(g.revenue), 0) AS revenue
  FROM assigned AS a
  LEFT JOIN goals AS g
    ON g.user_pseudo_id = a.user_pseudo_id
    AND g.event_timestamp >= a.first_seen
    AND g.event_timestamp < a.first_seen + window_days * 86400 * 1000000
  WHERE a.variants_seen = 1
  GROUP BY a.variant, a.user_pseudo_id
),

arms AS (
  SELECT variant, COUNT(*) AS n, COUNTIF(converted) AS x, AVG(revenue) AS rpu, VAR_SAMP(revenue) AS rpu_var
  FROM users
  GROUP BY variant
),

pairs AS (
  SELECT
    c.variant AS control, t.variant AS treatment,
    c.n AS n_c, t.n AS n_t,
    c.x / c.n AS p_c, t.x / t.n AS p_t,
    (c.x + t.x) / (c.n + t.n) AS p_pool,
    c.rpu AS rpu_c, t.rpu AS rpu_t, c.rpu_var AS v_c, t.rpu_var AS v_t
  FROM arms AS c
  JOIN arms AS t ON c.variant < t.variant
)

SELECT
  control, treatment, n_c, n_t,
  -- Sample ratio mismatch, for a planned 50/50 split: below 0.001, stop and find out why.
  2 * (1 - norm_cdf(ABS(n_t - n_c) / SQRT(n_c + n_t))) AS srm_p_value,
  ROUND(p_c, 4) AS rate_control,
  ROUND(p_t, 4) AS rate_treatment,
  ROUND(SAFE_DIVIDE(p_t, p_c) - 1, 4) AS relative_lift,
  -- 95% confidence interval for the difference in conversion rate.
  ROUND(p_t - p_c - 1.96 * SQRT(p_c * (1 - p_c) / n_c + p_t * (1 - p_t) / n_t), 4) AS diff_low,
  ROUND(p_t - p_c + 1.96 * SQRT(p_c * (1 - p_c) / n_c + p_t * (1 - p_t) / n_t), 4) AS diff_high,
  -- Two-proportion z-test.
  2 * (1 - norm_cdf(SAFE_DIVIDE(ABS(p_t - p_c), SQRT(p_pool * (1 - p_pool) * (1 / n_c + 1 / n_t))))) AS p_value,
  -- Revenue per visitor, zeros included, with Welch's test.
  ROUND(rpu_c, 2) AS revenue_per_user_control,
  ROUND(rpu_t, 2) AS revenue_per_user_treatment,
  2 * (1 - norm_cdf(SAFE_DIVIDE(ABS(rpu_t - rpu_c), SQRT(v_c / n_c + v_t / n_t)))) AS revenue_p_value
FROM pairs;

What it returns

Run on Google’s public GA4 sample dataset (bigquery-public-data.ga4_obfuscated_sample_ecommerce, 1 to 14 December 2020), with page views as the exposure and visitors split at random, it returns what a test with no real difference should:

Visitors, A / B	27,436 / 27,254
Sample ratio p-value	0.44
Conversion rate, A / B	2.24% / 2.37%
Difference, 95% interval	−0.12 to +0.38 points
p-value	0.30
Revenue per visitor, A / B	$1.76 / $1.91
Revenue p-value	0.36

Healthy split, interval spanning zero, nothing significant: what a random split should show.

Where it goes wrong

Consent. With consent mode, events from visitors who declined analytics cookies can arrive with no user_pseudo_id. They can’t be tied to an exposure, so the query leaves them out. If declines differ between your variants, so will your samples — the split check catches it.
Devices. user_pseudo_id is one browser. A visitor on a phone and a laptop is two visitors, possibly in two arms. If you send a user_id for logged-in visitors and assign on it, analyse on it too.
Peeking. Checking the p-value every day and stopping when it dips under 0.05 makes false winners likely. Decide the sample size first. For 80% power at 5% significance, each arm needs about 16 × p × (1 − p) / d² visitors, where p is the baseline rate and d the smallest difference worth finding: at a 3% baseline, finding a 10% relative lift (0.3 points) takes about 52,000 visitors per arm.
More than two variants. The query compares every pair. Each comparison is another chance of a false positive; with three variants, require p below 0.05 divided by the number of comparisons.
Export limits. Standard GA4 properties export up to a million events a day in the daily export. A busy site can hit it.

Or let agents run it

Riggle’s AI agents build, assign and measure every change in your own analytics, against one goal, and keep getting better on their own.

A/B test analysis in BigQuery, with SQL