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;

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.

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).

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.