A/B Test Analysis in BigQuery: Statistical Significance and Results

Why Analyze A/B Tests in BigQuery

GA4's Experiments feature provides basic A/B test analysis but with limitations in segmentation and statistical methodology. BigQuery enables full control over test analysis: custom statistical tests, segmented analysis by user characteristics, multi-metric comparison, and complete data without sampling.

Tracking A/B Test Assignment in GA4

For BigQuery analysis, A/B test variant assignment must be tracked as a GA4 event or user property. The standard pattern:

• Push to data layer on test variant assignment: event: 'experiment_viewed', experiment_id: 'checkout-cta-test', variant: 'control' OR 'treatment'
• GA4 Event tag captures this via GTM
• BigQuery export contains the experiment event and variant for each user

Query: Conversion Rate by Variant

WITH experiment_users AS (
  SELECT
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params)
      WHERE key = 'variant') AS variant
  FROM `project.analytics_PROPERTY.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
    AND event_name = 'experiment_viewed'
    AND (SELECT value.string_value FROM UNNEST(event_params)
      WHERE key = 'experiment_id') = 'checkout-cta-test'
),
conversions AS (
  SELECT DISTINCT user_pseudo_id
  FROM `project.analytics_PROPERTY.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
    AND event_name = 'purchase'
)
SELECT
  eu.variant,
  COUNT(DISTINCT eu.user_pseudo_id) as users,
  COUNT(DISTINCT c.user_pseudo_id) as converters,
  ROUND(COUNT(DISTINCT c.user_pseudo_id) * 100.0
    / COUNT(DISTINCT eu.user_pseudo_id), 2) as conversion_rate_pct
FROM experiment_users eu
LEFT JOIN conversions c ON eu.user_pseudo_id = c.user_pseudo_id
GROUP BY eu.variant

Statistical Significance Check

For a simple two-proportion z-test in BigQuery:

-- After getting conversion counts per variant, calculate z-score:
-- p1 = control conversion rate, p2 = treatment conversion rate
-- n1 = control users, n2 = treatment users
-- z = (p2-p1) / SQRT(p*(1-p)*(1/n1+1/n2)) where p = (c1+c2)/(n1+n2)
-- z > 1.96 = 95% significance; z > 2.576 = 99% significance

For practical use, export the BigQuery results to a spreadsheet and use a standard A/B test significance calculator, or implement the z-test formula directly in BigQuery SQL.

Summary

BigQuery A/B test analysis requires: tracking experiment assignment as a GA4 event with experiment_id and variant parameters, then joining experiment users to conversion events. The key metrics are users per variant, converters per variant, and conversion rate per variant. Assess statistical significance with a two-proportion z-test (z > 1.96 for 95% confidence). BigQuery analysis provides unsampled results and enables segmentation (did the test perform differently on mobile vs desktop?) not available in GA4's native experiment reporting.

See our BigQuery Setup service for experiment analysis development.

Need A/B test analysis in BigQuery? Contact Adslytics.