Analyzing A/B Test Results with SQL

A/B testing is the scientific method of product development. You split users into two groups:

• Control: Sees the original version.
• Variant: Sees the new version.

Then you measure which group performs better. While tools like Optimizely exist, the raw data usually lands in your data warehouse, and SQL is the best tool to analyze it.

The Data Model

Typically, you have two tables (or one giant event log):

1. experiment_assignments: Which user is in which group?
2. conversions: Who actually bought something?

Step 1: Calculate Conversion Rate

The core metric is the Conversion Rate: Conversion Rate = (Unique Users converted) / (Total Unique Users exposed)

SELECT
    group_name,
    COUNT(user_id) as total_users,
    SUM(has_converted) as converted_users,
    SUM(has_converted) * 1.0 / COUNT(user_id) as conversion_rate
FROM experiment_data
GROUP BY group_name;

That query shape works only if experiment_data is already one row per exposed user. In real systems, raw event logs usually contain multiple events per user, which means you must deduplicate to the right grain first.

For example:

WITH user_level_outcomes AS (
    SELECT
        user_id,
        group_name,
        MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS has_converted
    FROM experiment_events
    GROUP BY user_id, group_name
)
SELECT
    group_name,
    COUNT(*) AS total_users,
    SUM(has_converted) AS converted_users,
    SUM(has_converted) * 1.0 / COUNT(*) AS conversion_rate
FROM user_level_outcomes
GROUP BY group_name;

That "one row per experiment subject" rule is the first thing to verify in any trustworthy A/B test analysis.

Step 2: Checking for Sample Ratio Mismatch (SRM)

Before looking at the results, you must check for SRM. If you intended a 50/50 split but got 60% of users in Control, your test is broken. A simple COUNT(*) per group reveals this.

SELECT
    group_name,
    COUNT(*) AS assigned_users
FROM experiment_assignments
GROUP BY group_name;

If those counts are far from the intended split, stop the analysis and investigate assignment logic, bot filtering, or missing event ingestion before discussing lift.

Step 3: Calculate Absolute Lift and Relative Lift

Teams often say "lift" loosely, so it helps to define terms explicitly.

• Absolute lift: variant_rate - control_rate
• Relative lift: (variant_rate - control_rate) / control_rate

Example:

• Control converts at 10%
• Variant converts at 12%
• Absolute lift is 2 percentage points
• Relative lift is 20%

Both numbers are useful. Absolute lift is easier for business forecasting. Relative lift is easier for comparing experiments across different baselines.

Interactive Playground

Let's analyze a "Checkout Redesign" experiment. We'll join the assignment data with the purchase data to see if the new design (Variant B) beat the old one (Variant A).

Segment Results Carefully

Once the headline metric is calculated, the next temptation is to slice results by device, country, traffic source, or user tenure. That is useful, but it comes with two risks:

• small segments create noisy rates
• too many slices increase the chance of false positives

A practical approach is:

1. validate the overall experiment first
2. analyze only a small set of pre-planned segments
3. always show segment sample size next to the rate

If a segment shows a dramatic lift on only 37 users, it is a hypothesis for follow-up, not a launch decision.

What SQL Can Tell You and What It Cannot

SQL is excellent for preparing experiment data:

• assignment counts
• exposure windows
• user-level outcome tables
• conversion rates
• revenue per user
• segment breakdowns

But SQL alone does not magically make a result trustworthy. You still need statistical judgment:

• was the test duration long enough?
• was the experiment powered for the expected effect size?
• did you peek early and stop on noise?
• did you change tracking during the experiment?

The operational lesson is simple: use SQL to build a clean experiment dataset, then interpret the result with discipline.

Conclusion

SQL allows you to go beyond the summary dashboard. You can segment results by device type, country, or user tenure to see if the variant worked better for specific sub-populations—insights that automated tools often miss.

Related Articles

• Building Conversion Funnels in SQL for the stage-based metric model that often sits next to experiment analysis.
• Conditional Aggregation in SQL: A Complete Guide for the CASE-based counting patterns behind conversion and experiment metrics.
• Calculating Percentiles and Median in SQL for the distribution-aware statistics that become useful once average lift is not enough.
• Calculating Weighted Averages in SQL for another example where choosing the right denominator and aggregation grain is more important than the formula itself.