SQL untuk A/B Testing Analysis: Panduan Praktis

Apa Itu A/B Testing?

"Kita ganti warna button checkout jadi hijau. Conversion naik ga ya?"

Pertanyaan kayak gini ga bisa dijawab dengan feeling atau opini. Butuh data. Dan cara paling reliable untuk dapet data itu adalah A/B testing.

A/B testing (atau split testing) adalah metode experiment di mana kamu membagi user jadi dua grup:
- Control (A): User yang dapet versi lama/existing
- Treatment (B): User yang dapet versi baru

Terus kamu bandingin behavior kedua grup. Kalau grup B performanya lebih bagus secara statistik, berarti perubahan kamu sukses.

Kenapa ini penting? Karena:
- Keputusan berbasis data, bukan opini
- Bisa isolate impact dari satu perubahan
- Mengurangi risiko deploy fitur yang ga work

Apa yang Akan Kamu Pelajari

• Dasar-dasar A/B Testing
• Struktur data experiment
• Query untuk conversion rate per variant
• Menghitung lift/improvement
• Statistical significance dengan SQL
• Segmented analysis
• Common pitfalls dan cara menghindarinya
• Template reporting A/B test

Konsep Dasar A/B Testing

Terminologi

• Control: Versi existing (biasanya diberi label A)
• Treatment: Versi baru yang mau ditest (label B, C, dst)
• Conversion: Aksi yang kamu ukur (purchase, signup, click)
• Conversion Rate: Persentase user yang melakukan aksi
• Lift: Persentase peningkatan treatment vs control
• Statistical Significance: Confidence bahwa hasil bukan kebetulan

Contoh Skenario

Kamu mau test apakah button "Beli Sekarang" warna hijau lebih efektif dari warna biru (existing).

• Hypothesis: Button hijau akan meningkatkan conversion rate
• Metric: Purchase conversion rate
• Control (A): Button biru
• Treatment (B): Button hijau

Dataset yang Akan Kita Pakai

Ceritanya kamu Data Analyst di e-commerce Indonesia. Tim Product mau test checkout flow baru.

Tabel: experiment_assignments

User mana masuk ke variant mana.

Tabel: experiment_events

Event yang terjadi selama experiment.

Step 1: Cek Experiment Setup

Sebelum analisis, selalu validate experiment setup dulu.

Cek Sample Size per Variant

SELECT
    variant,
    COUNT(DISTINCT user_id) AS users,
    ROUND(100.0 * COUNT(DISTINCT user_id) / SUM(COUNT(DISTINCT user_id)) OVER (), 2) AS pct
FROM experiment_assignments
WHERE experiment_name = 'checkout_redesign'
GROUP BY variant;

Hasil:

Split-nya udah balance 50/50.

Cek Date Range

SELECT
    MIN(assigned_at) AS start_date,
    MAX(assigned_at) AS end_date,
    MAX(assigned_at)::DATE - MIN(assigned_at)::DATE AS duration_days
FROM experiment_assignments
WHERE experiment_name = 'checkout_redesign';

Hasil:

Experiment jalan selama 14 hari.

Step 2: Hitung Conversion Rate per Variant

Ini inti dari A/B testing analysis.

WITH user_conversions AS (
    SELECT
        a.user_id,
        a.variant,
        MAX(CASE WHEN e.event_type = 'purchase' THEN 1 ELSE 0 END) AS converted
    FROM experiment_assignments a
    LEFT JOIN experiment_events e
        ON a.user_id = e.user_id
        AND a.experiment_name = e.experiment_name
    WHERE a.experiment_name = 'checkout_redesign'
    GROUP BY a.user_id, a.variant
)
SELECT
    variant,
    COUNT(*) AS total_users,
    SUM(converted) AS conversions,
    ROUND(100.0 * SUM(converted) / COUNT(*), 2) AS conversion_rate
FROM user_conversions
GROUP BY variant
ORDER BY variant;

Hasil:

Treatment punya conversion rate 12% vs control 10%.

Step 3: Hitung Lift (Improvement)

Lift = berapa persen improvement treatment vs control.

WITH variant_stats AS (
    SELECT
        variant,
        COUNT(*) AS total_users,
        SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END) AS conversions,
        AVG(CASE WHEN converted = 1 THEN 1.0 ELSE 0.0 END) AS conversion_rate
    FROM (
        SELECT
            a.user_id,
            a.variant,
            MAX(CASE WHEN e.event_type = 'purchase' THEN 1 ELSE 0 END) AS converted
        FROM experiment_assignments a
        LEFT JOIN experiment_events e
            ON a.user_id = e.user_id
            AND a.experiment_name = e.experiment_name
        WHERE a.experiment_name = 'checkout_redesign'
        GROUP BY a.user_id, a.variant
    ) user_conv
    GROUP BY variant
)
SELECT
    treatment.conversion_rate AS treatment_cr,
    control.conversion_rate AS control_cr,
    treatment.conversion_rate - control.conversion_rate AS absolute_lift,
    ROUND(100.0 * (treatment.conversion_rate - control.conversion_rate) / control.conversion_rate, 2) AS relative_lift_pct
FROM
    (SELECT * FROM variant_stats WHERE variant = 'treatment') treatment,
    (SELECT * FROM variant_stats WHERE variant = 'control') control;

Hasil:

Treatment punya 20% relative lift dibanding control. Tapi apakah ini significant?

Step 4: Statistical Significance dengan SQL

Untuk tau apakah hasil ini bukan kebetulan, kita perlu cek statistical significance.

Z-Test Approach

WITH variant_stats AS (
    SELECT
        variant,
        COUNT(*) AS n,
        SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END) AS conversions,
        AVG(CASE WHEN converted = 1 THEN 1.0 ELSE 0.0 END) AS p
    FROM (
        SELECT
            a.user_id,
            a.variant,
            MAX(CASE WHEN e.event_type = 'purchase' THEN 1 ELSE 0 END) AS converted
        FROM experiment_assignments a
        LEFT JOIN experiment_events e
            ON a.user_id = e.user_id
            AND a.experiment_name = e.experiment_name
        WHERE a.experiment_name = 'checkout_redesign'
        GROUP BY a.user_id, a.variant
    ) user_conv
    GROUP BY variant
),
pooled_stats AS (
    SELECT
        SUM(conversions) AS total_conv,
        SUM(n) AS total_n,
        SUM(conversions)::FLOAT / SUM(n) AS p_pooled
    FROM variant_stats
),
z_score AS (
    SELECT
        t.p AS p_treatment,
        c.p AS p_control,
        t.n AS n_treatment,
        c.n AS n_control,
        ps.p_pooled,
        -- Standard Error
        SQRT(ps.p_pooled * (1 - ps.p_pooled) * (1.0/t.n + 1.0/c.n)) AS se,
        -- Z-score
        (t.p - c.p) / SQRT(ps.p_pooled * (1 - ps.p_pooled) * (1.0/t.n + 1.0/c.n)) AS z
    FROM
        (SELECT * FROM variant_stats WHERE variant = 'treatment') t,
        (SELECT * FROM variant_stats WHERE variant = 'control') c,
        pooled_stats ps
)
SELECT
    p_treatment,
    p_control,
    p_treatment - p_control AS difference,
    ROUND(z, 4) AS z_score,
    CASE
        WHEN ABS(z) >= 2.576 THEN 'Significant (99%)'
        WHEN ABS(z) >= 1.96 THEN 'Significant (95%)'
        WHEN ABS(z) >= 1.645 THEN 'Significant (90%)'
        ELSE 'Not Significant'
    END AS significance
FROM z_score;

Hasil:

Z-score 3.16 > 2.576, jadi hasilnya significant di level 99%!

Confidence Interval

WITH stats AS (
    -- ... (query sama kayak di atas sampai z_score)
)
SELECT
    p_treatment - p_control AS point_estimate,
    (p_treatment - p_control) - 1.96 * se AS ci_lower_95,
    (p_treatment - p_control) + 1.96 * se AS ci_upper_95
FROM z_score;

Hasil:

95% confidence interval: [0.76%, 3.24%]. Karena ga include 0, kita yakin ada positive effect.

Step 5: Revenue per User Analysis

Conversion rate bukan satu-satunya metric. Revenue per user juga penting.

WITH user_revenue AS (
    SELECT
        a.user_id,
        a.variant,
        COALESCE(SUM(e.revenue), 0) AS total_revenue
    FROM experiment_assignments a
    LEFT JOIN experiment_events e
        ON a.user_id = e.user_id
        AND a.experiment_name = e.experiment_name
        AND e.event_type = 'purchase'
    WHERE a.experiment_name = 'checkout_redesign'
    GROUP BY a.user_id, a.variant
)
SELECT
    variant,
    COUNT(*) AS users,
    SUM(total_revenue) AS total_revenue,
    ROUND(AVG(total_revenue), 0) AS avg_revenue_per_user,
    ROUND(STDDEV(total_revenue), 0) AS std_dev
FROM user_revenue
GROUP BY variant
ORDER BY variant;

Hasil:

Treatment punya average revenue per user 44% lebih tinggi!

Step 6: Segmented Analysis

Breakdown hasil per segment untuk deeper insights.

By Device

WITH user_data AS (
    SELECT
        a.user_id,
        a.variant,
        u.device,
        MAX(CASE WHEN e.event_type = 'purchase' THEN 1 ELSE 0 END) AS converted
    FROM experiment_assignments a
    LEFT JOIN experiment_events e
        ON a.user_id = e.user_id
        AND a.experiment_name = e.experiment_name
    LEFT JOIN users u ON a.user_id = u.user_id
    WHERE a.experiment_name = 'checkout_redesign'
    GROUP BY a.user_id, a.variant, u.device
)
SELECT
    device,
    variant,
    COUNT(*) AS users,
    SUM(converted) AS conversions,
    ROUND(100.0 * SUM(converted) / COUNT(*), 2) AS conversion_rate
FROM user_data
GROUP BY device, variant
ORDER BY device, variant;

Hasil:

Treatment menang di kedua device, tapi lift lebih besar di mobile (25% vs 16.7%).

By User Segment

WITH user_data AS (
    SELECT
        a.user_id,
        a.variant,
        CASE
            WHEN u.total_orders >= 10 THEN 'power_user'
            WHEN u.total_orders >= 3 THEN 'regular'
            ELSE 'new_user'
        END AS user_segment,
        MAX(CASE WHEN e.event_type = 'purchase' THEN 1 ELSE 0 END) AS converted
    FROM experiment_assignments a
    LEFT JOIN experiment_events e
        ON a.user_id = e.user_id
        AND a.experiment_name = e.experiment_name
    LEFT JOIN users u ON a.user_id = u.user_id
    WHERE a.experiment_name = 'checkout_redesign'
    GROUP BY a.user_id, a.variant, user_segment
)
SELECT
    user_segment,
    variant,
    COUNT(*) AS users,
    SUM(converted) AS conversions,
    ROUND(100.0 * SUM(converted) / COUNT(*), 2) AS conversion_rate
FROM user_data
GROUP BY user_segment, variant
ORDER BY user_segment, variant;

Step 7: Time-based Analysis

Cek apakah effect konsisten dari waktu ke waktu.

WITH daily_stats AS (
    SELECT
        DATE(a.assigned_at) AS date,
        a.variant,
        COUNT(DISTINCT a.user_id) AS users,
        COUNT(DISTINCT CASE WHEN e.event_type = 'purchase' THEN a.user_id END) AS conversions
    FROM experiment_assignments a
    LEFT JOIN experiment_events e
        ON a.user_id = e.user_id
        AND a.experiment_name = e.experiment_name
    WHERE a.experiment_name = 'checkout_redesign'
    GROUP BY DATE(a.assigned_at), a.variant
)
SELECT
    date,
    variant,
    users,
    conversions,
    ROUND(100.0 * conversions / users, 2) AS conversion_rate
FROM daily_stats
ORDER BY date, variant;

Kalau effect baru muncul di hari-hari terakhir, mungkin ada novelty effect yang perlu diinvestigasi.

Step 8: Sample Ratio Mismatch (SRM) Check

SRM terjadi kalau distribusi user ke variant ga sesuai expected. Ini red flag.

WITH variant_counts AS (
    SELECT
        variant,
        COUNT(*) AS actual_count
    FROM experiment_assignments
    WHERE experiment_name = 'checkout_redesign'
    GROUP BY variant
),
expected AS (
    SELECT
        SUM(actual_count) / 2.0 AS expected_per_variant
    FROM variant_counts
)
SELECT
    v.variant,
    v.actual_count,
    e.expected_per_variant,
    v.actual_count - e.expected_per_variant AS difference,
    ROUND(100.0 * ABS(v.actual_count - e.expected_per_variant) / e.expected_per_variant, 2) AS pct_diff
FROM variant_counts v
CROSS JOIN expected e;

Kalau pct_diff > 1-2%, perlu investigasi lebih lanjut.

Template Reporting A/B Test

-- =============================================
-- A/B TEST REPORT TEMPLATE
-- =============================================

-- 1. EXPERIMENT OVERVIEW
SELECT
    experiment_name,
    MIN(assigned_at) AS start_date,
    MAX(assigned_at) AS end_date,
    COUNT(DISTINCT user_id) AS total_users
FROM experiment_assignments
WHERE experiment_name = 'your_experiment'
GROUP BY experiment_name;

-- 2. SAMPLE SIZE CHECK
SELECT
    variant,
    COUNT(DISTINCT user_id) AS users,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct
FROM experiment_assignments
WHERE experiment_name = 'your_experiment'
GROUP BY variant;

-- 3. CONVERSION RESULTS
WITH user_conv AS (
    SELECT
        a.user_id,
        a.variant,
        MAX(CASE WHEN e.event_type = 'your_conversion_event' THEN 1 ELSE 0 END) AS converted
    FROM experiment_assignments a
    LEFT JOIN experiment_events e ON a.user_id = e.user_id
    WHERE a.experiment_name = 'your_experiment'
    GROUP BY a.user_id, a.variant
)
SELECT
    variant,
    COUNT(*) AS users,
    SUM(converted) AS conversions,
    ROUND(100.0 * SUM(converted) / COUNT(*), 2) AS conversion_rate
FROM user_conv
GROUP BY variant;

-- 4. LIFT CALCULATION
-- (Gunakan query dari Step 3 di atas)

-- 5. STATISTICAL SIGNIFICANCE
-- (Gunakan query dari Step 4 di atas)

-- 6. RECOMMENDATION
-- Based on results, [SHIP / ITERATE / KILL] the treatment

Common Pitfalls yang Harus Dihindari

1. Peeking Bias

Jangan liat hasil dan stop experiment begitu "significant". Tunggu sampai sample size yang ditargetkan tercapai.

-- SALAH: Cek setiap hari dan stop kalau udah significant
-- BENAR: Tentukan sample size dan duration di awal, tunggu sampai selesai

2. Multiple Testing Problem

Kalau kamu test 20 metrics, secara statistik 1 akan significant by chance (5% false positive rate).

-- Solusi: Tentukan primary metric di awal
-- Secondary metrics sebagai supporting evidence

3. Sample Ratio Mismatch

Kalau split ga 50/50 padahal harusnya, ada bug di randomization.

4. Novelty Effect

User mungkin excited sama fitur baru, tapi effect berkurang seiring waktu. Jalankan experiment cukup lama.

5. Seasonality

Jangan jalankan experiment pas periode unusual (flash sale, Lebaran, dll).

Tips dan Best Practices

1. Define Hypothesis First

Sebelum jalankan experiment, tulis:
- Apa yang mau ditest
- Metric apa yang diukur
- Berapa improvement yang diharapkan

2. Calculate Sample Size

-- Rule of thumb untuk sample size
-- Minimum detectable effect: 10%
-- Baseline conversion: 10%
-- Power: 80%, Significance: 95%
-- Approximately 3,900 users per variant

3. Run Experiment Long Enough

Minimal 1-2 business cycles (biasanya 1-2 minggu).

4. Document Everything

## Experiment: checkout_redesign

### Hypothesis
Redesigned checkout flow will increase purchase conversion by 10%

### Setup
- Start date: 2024-01-15
- End date: 2024-01-29
- Traffic split: 50/50
- Primary metric: Purchase conversion rate

### Results
- Control: 10.0% conversion
- Treatment: 12.0% conversion
- Lift: +20% (relative)
- Significance: 99% confidence

### Decision
SHIP - Roll out to 100% of users

Latihan

Soal: Dari data experiment di atas, jawab:
1. Apakah ada SRM (Sample Ratio Mismatch)?
2. Berapa lift di revenue per user?
3. Segment mana yang dapet benefit paling besar dari treatment?

-- 1. SRM Check
SELECT
    variant,
    COUNT(*) AS actual,
    5000 AS expected,  -- 50% dari 10000
    ABS(COUNT(*) - 5000) AS difference
FROM experiment_assignments
WHERE experiment_name = 'checkout_redesign'
GROUP BY variant;
-- Hasil: Kalau difference kecil (< 50), ga ada SRM

-- 2. Revenue Lift
WITH revenue AS (
    SELECT
        variant,
        AVG(COALESCE(revenue, 0)) AS avg_rev
    FROM experiment_assignments a
    LEFT JOIN experiment_events e ON a.user_id = e.user_id
    WHERE a.experiment_name = 'checkout_redesign'
    GROUP BY variant
)
SELECT
    (SELECT avg_rev FROM revenue WHERE variant = 'treatment') -
    (SELECT avg_rev FROM revenue WHERE variant = 'control') AS absolute_lift,
    ROUND(100.0 *
        ((SELECT avg_rev FROM revenue WHERE variant = 'treatment') -
         (SELECT avg_rev FROM revenue WHERE variant = 'control')) /
        (SELECT avg_rev FROM revenue WHERE variant = 'control')
    , 2) AS pct_lift;
-- Hasil: 44% revenue lift

-- 3. Best Segment
-- Mobile users dapet lift terbesar (25% vs 16.7% desktop)

FAQ

Berapa lama idealnya A/B test dijalankan?

Minimal 1-2 minggu, biar cover satu business cycle penuh. Behavior user weekday sama weekend itu beda banget lho. Hindari juga periode unusual kayak flash sale atau Lebaran, soalnya hasilnya bakal bias. Kalau experiment cuma jalan 3 hari terus di-stop, kamu berisiko kena novelty effect. User excited sama fitur baru, tapi efeknya hilang seminggu kemudian.

Berapa sample size minimum buat A/B test?

Tergantung baseline conversion rate sama seberapa kecil effect yang mau kamu deteksi. Contoh di artikel ini: baseline 10%, minimum detectable effect 10% relative, power 80%. Butuh sekitar 3.900 user per variant. Makin kecil effect yang mau dideteksi, makin gede sample yang dibutuhin. Tentuin angka ini di awal ya, jangan di tengah jalan.

Apa itu peeking bias?

Peeking bias itu kebiasaan ngecek hasil experiment tiap hari, terus buru-buru stop begitu hasilnya keliatan significant. Masalahnya, makin sering kamu ngecek, makin gede peluang dapet false positive. Hasil yang keliatan bagus padahal cuma kebetulan. Solusinya simpel kok: tentuin sample size dan durasi di awal, terus tunggu sampai selesai.

Bisa nggak hitung statistical significance cuma pakai SQL?

Bisa. Z-test untuk dua proporsi kayak di artikel ini cuma butuh SQRT, ABS, dan aritmetika dasar. Semua database standar punya. Untuk kebutuhan lebih lanjut kayak t-test dengan sample kecil atau Bayesian analysis, biasanya lebih praktis lempar hasilnya ke Python atau R. Tapi buat conversion rate test sehari-hari, SQL udah cukup.

Kesimpulan

A/B testing itu skill wajib buat Data Analyst di tech company. Inget poin-poin ini:

1. A/B test = cara scientific untuk ukur impact
2. Selalu validate setup sebelum analisis (sample size, SRM)
3. Hitung conversion rate dan lift
4. Check statistical significance sebelum conclude
5. Segment analysis bisa kasih deeper insights
6. Avoid common pitfalls: peeking, multiple testing, novelty effect
7. Document hypothesis, results, dan decisions

Dengan A/B testing, kamu bisa kasih evidence-based recommendations ke product team. "Ship fitur ini, conversion rate naik 20% dengan 99% confidence" itu powerful banget.

Happy experimenting!

Selanjutnya

Kalau kamu udah paham A/B testing, next step-nya:
- Funnel Analysis - analisis conversion flow
- Cohort Analysis - analisis retention
- Dashboard Metrics - track KPIs