experiment_summary.sql

WITH by_variant AS (
    SELECT
        experiment_name,
        variant,
        COUNT(*) AS users,
        ROUND(AVG(activated), 4) AS activation_rate,
        ROUND(AVG(purchased), 4) AS purchase_rate,
        ROUND(AVG(retained_30d), 4) AS retained_30d_rate,
        ROUND(AVG(estimated_revenue), 2) AS revenue_per_user
    FROM experiment_observations
    GROUP BY 1, 2
),
control AS (
    SELECT * FROM by_variant WHERE variant = 'control'
),
treatment AS (
    SELECT * FROM by_variant WHERE variant = 'treatment'
)
SELECT
    control.experiment_name,
    control.users AS control_users,
    treatment.users AS treatment_users,
    control.activation_rate AS control_activation_rate,
    treatment.activation_rate AS treatment_activation_rate,
    COALESCE(
        ROUND(
            (treatment.activation_rate - control.activation_rate)
                / NULLIF(control.activation_rate, 0),
            4
        ),
        0.0
    ) AS activation_lift_pct,
    control.purchase_rate AS control_purchase_rate,
    treatment.purchase_rate AS treatment_purchase_rate,
    COALESCE(
        ROUND(
            (treatment.purchase_rate - control.purchase_rate)
                / NULLIF(control.purchase_rate, 0),
            4
        ),
        0.0
    ) AS purchase_lift_pct,
    control.retained_30d_rate AS control_retained_30d_rate,
    treatment.retained_30d_rate AS treatment_retained_30d_rate,
    COALESCE(
        ROUND(
            (treatment.retained_30d_rate - control.retained_30d_rate)
                / NULLIF(control.retained_30d_rate, 0),
            4
        ),
        0.0
    ) AS retained_30d_lift_pct,
    control.revenue_per_user AS control_revenue_per_user,
    treatment.revenue_per_user AS treatment_revenue_per_user,
    ROUND(
        treatment.revenue_per_user - control.revenue_per_user,
        2
    ) AS revenue_per_user_delta,
    CASE
        WHEN (treatment.purchase_rate - control.purchase_rate) >= 0.03
            THEN 'Treatment improved downstream conversion enough to recommend rollout.'
        WHEN (treatment.activation_rate - control.activation_rate) >= 0.03
            THEN 'Treatment improved activation, but purchase lift is modest. Run a longer follow-up test.'
        ELSE 'Lift is too small to justify rollout without more iteration.'
    END AS recommendation
FROM control
CROSS JOIN treatment;