The SQL behind an A/B test: Writing experiment queries in BigQuery

The hardest part of A/B test SQL is not averaging two groups. It is constructing two groups that still represent the randomized experiment.

A raw query can produce a plausible lift while getting the population wrong. It may count a user once per event, include purchases before exposure, mix production and QA traffic, keep users who saw two variations, or treat immature retention windows as zero. BigQuery will execute that logic quickly and consistently; it cannot tell you that the estimand changed.

This guide builds an experiment query from first principles. It starts with an exposure contract, produces one outcome value per randomized unit, returns the sufficient statistics an analysis engine needs, and adds checks for sample ratio mismatch, duplicate joins, and query cost.

The examples use users as the randomization unit and revenue as the primary metric. Replace the project, dataset, experiment ID, timestamps, and business filters before running them. Never paste production secrets into SQL.

Start with explicit table contracts

Assume two partitioned tables.

analytics.experiment_exposures has:

• experiment_id STRING
• user_id STRING
• variation_id STRING
• exposed_at TIMESTAMP
• environment STRING

analytics.orders has:

• order_id STRING
• user_id STRING
• order_at TIMESTAMP
• net_revenue NUMERIC
• order_status STRING

The exposure table records an actual opportunity for the user to receive treatment, not merely a flag definition download or an eligibility check. The outcome table has one row per order. If a metric comes from raw events, first create a fact model with a known grain.

The randomization unit must be stable across both tables. If assignment happens by account, replace user_id with account_id throughout. Joining account assignment to user outcomes without first aggregating to the account creates dependence and incorrect uncertainty.

Use half-open time intervals: include timestamps at the start and exclude the end. This avoids double-counting records when adjacent runs touch.

Build the first-exposure population

The following BigQuery script keeps all exposures for diagnostics, selects the first exposure per user, counts distinct variations, and excludes crossovers from the analysis population.

DECLARE experiment_key STRING DEFAULT 'checkout-copy-v3';
DECLARE analysis_start TIMESTAMP DEFAULT TIMESTAMP('2026-08-01 00:00:00+00');
DECLARE analysis_end TIMESTAMP DEFAULT TIMESTAMP('2026-08-22 00:00:00+00');

WITH raw_exposures AS (
  SELECT
    experiment_id,
    user_id,
    variation_id,
    exposed_at
  FROM `your-project.analytics.experiment_exposures`
  WHERE DATE(exposed_at) >= DATE(analysis_start)
    AND DATE(exposed_at) < DATE(analysis_end)
    AND exposed_at >= analysis_start
    AND exposed_at < analysis_end
    AND experiment_id = experiment_key
    AND environment = 'production'
    AND user_id IS NOT NULL
    AND variation_id IN ('control', 'treatment')
),
exposure_quality AS (
  SELECT
    user_id,
    COUNT(DISTINCT variation_id) AS variations_seen,
    COUNT(*) AS exposure_rows
  FROM raw_exposures
  GROUP BY user_id
),
first_exposure AS (
  SELECT
    experiment_id,
    user_id,
    variation_id,
    exposed_at AS first_exposed_at
  FROM raw_exposures
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY experiment_id, user_id
    ORDER BY exposed_at, variation_id
  ) = 1
),
eligible_exposures AS (
  SELECT f.*
  FROM first_exposure AS f
  JOIN exposure_quality AS q USING (user_id)
  WHERE q.variations_seen = 1
)
SELECT *
FROM eligible_exposures;

BigQuery's 0 clause filters after window functions, which makes first-exposure selection concise. The variation key in the ordering only breaks impossible timestamp ties deterministically; investigate ties rather than assuming they are harmless.

Keep the crossover count as a quality result. Excluding those users prevents ambiguous treatment, but a rising crossover rate may reveal identifier changes, non-sticky bucketing, environment mixing, or duplicate instrumentation.

Join post-exposure outcomes and aggregate once per user

This query extends the exposure CTEs and creates one revenue and conversion value per user. The broad order bounds support partition pruning; the per-user bounds enforce the actual conversion window.

DECLARE experiment_key STRING DEFAULT 'checkout-copy-v3';
DECLARE analysis_start TIMESTAMP DEFAULT TIMESTAMP('2026-08-01 00:00:00+00');
DECLARE analysis_end TIMESTAMP DEFAULT TIMESTAMP('2026-08-22 00:00:00+00');
DECLARE conversion_days INT64 DEFAULT 14;

WITH raw_exposures AS (
  SELECT experiment_id, user_id, variation_id, exposed_at
  FROM `your-project.analytics.experiment_exposures`
  WHERE DATE(exposed_at) >= DATE(analysis_start)
    AND DATE(exposed_at) < DATE(analysis_end)
    AND exposed_at >= analysis_start
    AND exposed_at < analysis_end
    AND experiment_id = experiment_key
    AND environment = 'production'
    AND user_id IS NOT NULL
    AND variation_id IN ('control', 'treatment')
),
exposure_quality AS (
  SELECT user_id, COUNT(DISTINCT variation_id) AS variations_seen
  FROM raw_exposures
  GROUP BY user_id
),
first_exposure AS (
  SELECT
    experiment_id,
    user_id,
    variation_id,
    exposed_at AS first_exposed_at
  FROM raw_exposures
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY experiment_id, user_id
    ORDER BY exposed_at, variation_id
  ) = 1
),
eligible_exposures AS (
  SELECT f.*
  FROM first_exposure AS f
  JOIN exposure_quality AS q USING (user_id)
  WHERE q.variations_seen = 1
),
unit_values AS (
  SELECT
    e.variation_id,
    e.user_id,
    COUNT(DISTINCT o.order_id) > 0 AS converted,
    COALESCE(SUM(o.net_revenue), 0) AS revenue
  FROM eligible_exposures AS e
  LEFT JOIN `your-project.analytics.orders` AS o
    ON o.user_id = e.user_id
   AND DATE(o.order_at) >= DATE(analysis_start)
   AND DATE(o.order_at) < DATE(TIMESTAMP_ADD(analysis_end, INTERVAL 14 DAY))
   AND o.order_at >= e.first_exposed_at
   AND o.order_at < TIMESTAMP_ADD(
     e.first_exposed_at,
     INTERVAL conversion_days DAY
   )
   AND o.order_status = 'completed'
  GROUP BY e.variation_id, e.user_id
)
SELECT
  variation_id,
  COUNT(*) AS units,
  COUNTIF(converted) AS converted_units,
  SAFE_DIVIDE(COUNTIF(converted), COUNT(*)) AS conversion_rate,
  AVG(revenue) AS mean_revenue_per_unit,
  VAR_SAMP(revenue) AS sample_variance_revenue,
  SUM(revenue) AS total_revenue
FROM unit_values
GROUP BY variation_id
ORDER BY variation_id;

SAFE_DIVIDE returns NULL instead of failing when the denominator is zero. BigQuery's function reference documents this and related safe operations.

The LEFT JOIN matters. A user with no qualifying order must remain in the population with zero revenue and converted = FALSE. Moving an order predicate from ON to a final WHERE clause can turn the join into an effective inner join and delete non-converters.

The output is descriptive, not a complete decision. units, sums, means, and sample variances are inputs to statistical inference. A production engine should apply a tested method for confidence intervals or Bayesian posteriors, sequential monitoring, variance reduction, and multiple metrics. Google's Firebase A/B Testing BigQuery examples similarly return counts and dispersion values for independent analysis.

Put transparent SQL to work

Connect BigQuery exposures and business metrics to repeatable experiment analysis without building every statistical workflow yourself.

Start Building Free

Calculate a readable lift without pretending it is inference

For a quick reconciliation, pivot the variation summaries and calculate absolute and relative differences. Keep this separate from the official statistical result.

WITH variation_summary AS (
  -- Replace this comment with the complete query above through unit_values.
  SELECT
    variation_id,
    COUNT(*) AS units,
    SAFE_DIVIDE(COUNTIF(converted), COUNT(*)) AS conversion_rate,
    AVG(revenue) AS revenue_per_unit
  FROM unit_values
  GROUP BY variation_id
),
pivoted AS (
  SELECT
    MAX(IF(variation_id = 'control', conversion_rate, NULL)) AS control_cvr,
    MAX(IF(variation_id = 'treatment', conversion_rate, NULL)) AS treatment_cvr,
    MAX(IF(variation_id = 'control', revenue_per_unit, NULL)) AS control_rpu,
    MAX(IF(variation_id = 'treatment', revenue_per_unit, NULL)) AS treatment_rpu
  FROM variation_summary
)
SELECT
  control_cvr,
  treatment_cvr,
  treatment_cvr - control_cvr AS cvr_absolute_change,
  SAFE_DIVIDE(treatment_cvr - control_cvr, control_cvr) AS cvr_relative_lift,
  control_rpu,
  treatment_rpu,
  treatment_rpu - control_rpu AS rpu_absolute_change,
  SAFE_DIVIDE(treatment_rpu - control_rpu, control_rpu) AS rpu_relative_lift
FROM pivoted;

Relative lift is undefined when the control mean is zero. Preserve the absolute change and show the missing relative value rather than substituting an impressive but meaningless percentage.

Do not choose a winner from lift alone. A large observed difference can be noisy in a small sample, and a narrow interval around a negligible effect may not justify shipping. Define a practical decision threshold before the experiment.

Add SQL checks before interpreting the result

Check sample allocation

For a 50/50 experiment, this query returns the observed counts and Pearson chi-square statistic. A two-arm test has one degree of freedom; use a statistical library or experimentation platform to calculate the p-value and apply the program's alert threshold.

WITH counts AS (
  SELECT variation_id, COUNT(*) AS observed
  FROM eligible_exposures
  GROUP BY variation_id
),
totals AS (
  SELECT SUM(observed) AS total_units FROM counts
)
SELECT
  SUM(
    POW(observed - total_units * 0.5, 2)
    / (total_units * 0.5)
  ) AS chi_square_statistic
FROM counts
CROSS JOIN totals;

A sample ratio mismatch is a smoke alarm, not a diagnosis. GrowthBook's SRM documentation covers the check, while allocation problems can come from targeting, assignment, exposure logging, filters, identifiers, or pipeline loss.

Report crossovers and duplicates

SELECT
  COUNT(*) AS exposed_units,
  COUNTIF(variations_seen > 1) AS crossover_units,
  SAFE_DIVIDE(
    COUNTIF(variations_seen > 1),
    COUNT(*)
  ) AS crossover_rate,
  MAX(exposure_rows) AS max_exposure_rows_for_one_unit
FROM exposure_quality;

Repeated rows in one variation may be legitimate repeated exposure. Multiple variations are not. Set an alert relative to historical instrumentation behavior and investigate changes.

Test the fact-table grain

If orders promises one row per order, assert it.

SELECT order_id, COUNT(*) AS rows_per_order
FROM `your-project.analytics.orders`
WHERE DATE(order_at) >= DATE('2026-08-01')
  AND DATE(order_at) < DATE('2026-09-05')
GROUP BY order_id
HAVING COUNT(*) > 1;

An empty result passes the check. If duplicates are valid versions, build a current-order view with explicit ordering rather than adding DISTINCT to the experiment query and hiding the model problem.

Check for pre-exposure outcomes

An outcome query should never attribute an order before first exposure. This diagnostic intentionally looks for such rows.

SELECT
  COUNT(*) AS pre_exposure_order_rows,
  COUNT(DISTINCT e.user_id) AS affected_users
FROM eligible_exposures AS e
JOIN `your-project.analytics.orders` AS o
  ON o.user_id = e.user_id
WHERE DATE(o.order_at) >= DATE('2026-07-18')
  AND DATE(o.order_at) < DATE('2026-08-22')
  AND o.order_at < e.first_exposed_at;

Pre-exposure orders are not necessarily bad data. They are useful for covariates such as CUPED or for eligibility. They must not leak into the post-exposure outcome.

Handle maturity, late data, and changing identity

A fourteen-day metric is incomplete for a user exposed three days ago. Choose one of two coherent analyses:

1. Include only units whose full conversion window has elapsed.
2. Use a cumulative analysis where every variation has comparable follow-up at each time point.

For the first approach, add this predicate to eligible exposures:

WHERE TIMESTAMP_ADD(first_exposed_at, INTERVAL 14 DAY)
      <= TIMESTAMP('2026-09-05 00:00:00+00')

Use an as_of timestamp tied to data completeness, not the analyst's wall clock. Mobile uploads, payment adjustments, and source backfills can change old partitions. Document when a metric is preliminary, mature, and frozen.

Identity changes can be more damaging than late events. If anonymous visitors become authenticated users, preserve a deterministic identity-resolution model with effective timestamps. Do not join today's identity map to last month's exposures if it rewrites historical membership differently across variations.

Keep BigQuery queries efficient and observable

The examples filter DATE(exposed_at) and DATE(order_at) to make the intended partitions explicit. Match those expressions to the actual partition columns. BigQuery's partition-pruning guidance explains which predicates can eliminate partitions, and tables can require a qualifying partition filter.

For production analysis:

• select only the columns needed by the metric;
• use fixed broad bounds for every large fact table;
• aggregate raw events into reusable unit-level facts;
• cluster high-volume facts on common unit or metric keys when it helps;
• separate exploratory jobs from scheduled experiment refreshes;
• label jobs with application, environment, and metric identifiers;
• dry-run new query shapes before deploying them.

Google's cost-control guidance documents dry runs and maximum bytes billed. A dry run validates syntax and estimates data processed without executing the query. Use the estimate as a gate in CI for generated or version-controlled metric SQL.

Monitor the actual jobs. 0 exposes near-real-time job metadata including bytes processed, bytes billed, slot time, cache use, errors, labels, and normalized query hashes. Group by the experiment service and query hash to find regressions.

Connect this SQL model to GrowthBook

Handwritten SQL is useful for learning, audit, and bespoke analysis. A shared experimentation program also needs reusable definitions, statistical methods, result history, diagnostics, permissions, and a workflow product and engineering teams can use.

GrowthBook's warehouse-native architecture connects to BigQuery and makes generated SQL visible. Configure:

1. a data source with least-privilege credentials;
2. an experiment assignment query equivalent to eligible_exposures;
3. a fact table with a stable unit and event time;
4. reusable metrics for conversion and revenue;
5. conversion windows, caps, guardrails, and analysis settings;
6. an A/A test and historical A/B reconciliation.

Preview the generated query and compare unit counts, sums, means, and variances with the reference SQL. Only then let experiment owners reuse the metric broadly.

Production checklist

Before a BigQuery experiment query informs a decision, verify:

• the exposure means a real opportunity to receive treatment;
• the randomization unit is stable and matches the metric grain;
• first exposure is selected deterministically;
• cross-variation units are reported and handled consistently;
• production eligibility and environment filters are explicit;
• only post-exposure outcomes enter the primary metric;
• zero-outcome units remain in the denominator;
• the full observation window is mature or follow-up is comparable;
• event joins cannot multiply the randomized units;
• allocation, null IDs, duplicates, and data lag are monitored;
• SQL uses qualifying partition filters and bounded scans;
• descriptive output is passed to a tested statistical method;
• query jobs and costs are attributable to the analysis system;
• metric definitions and changes have owners and version history.

The SQL behind an A/B test is a causal data contract written in a query language. Make the population and time rules explicit, test every join at the randomized-unit grain, and keep the statistical layer separate from descriptive arithmetic. Then BigQuery becomes a reliable foundation for experimentation rather than a fast way to calculate the wrong answer.

Scale beyond one-off queries

Reuse governed BigQuery metrics, inspect every analysis query, and give teams a consistent path from exposure to decision.

Build with GrowthBook