SQL for Product Analytics: Funnel Conversion, Drop-off Points & A/B Testing Comparisons

Product Funnel Analysis

Your product has a conversion funnel: Visit → Sign Up → First Action → Purchase. Each step drops users. SQL helps you quantify exactly where and why.

1. Full Funnel Conversion Rates

Compute the conversion rate at each funnel step and the drop-off between steps.

WITH funnel_steps AS (
    SELECT
        user_id,
        CASE
            WHEN page = 'sign_up' THEN 1
            WHEN page = 'first_action' THEN 2
            WHEN page = 'purchase' THEN 3
        END AS step
    FROM page_views
    WHERE page IN ('home', 'sign_up', 'first_action', 'purchase')
),
step_counts AS (
    SELECT
        step,
        COUNT(DISTINCT user_id) AS users_at_step
    FROM funnel_steps
    GROUP BY step
)
SELECT
    s1.step AS step1, s1.users_at_step AS visitors,
    s2.step AS step2, s2.users_at_step AS sign_ups,
    s3.step AS step3 AS first_actions,
    s4.step AS step4 AS purchases,
    ROUND(s4.users_at_step::numeric / s1.users_at_step * 100, 2) AS overall_conversion_pct,
    ROUND(s2.users_at_step::numeric / s1.users_at_step * 100, 2) AS drop_off_visit_to_signup_pct,
    ROUND(s3.users_at_step::numeric / s2.users_at_step * 100, 2) AS drop_off_signup_to_action_pct,
    ROUND(s4.users_at_step::numeric / s3.users_at_step * 100, 2) AS drop_off_action_to_purchase_pct
FROM step_counts s1
JOIN step_counts s2 ON s1.step = 1 AND s2.step = 2
JOIN step_counts s3 ON s1.step = 1 AND s3.step = 3
JOIN step_counts s4 ON s1.step = 1 AND s4.step = 4;

2. Funnel Drop-off by User Segment

Compare conversion rates between new users and returning users to identify segment-specific friction.

WITH funnel_steps AS (
    SELECT
        user_id,
        CASE
            WHEN page = 'sign_up' THEN 1
            WHEN page = 'first_action' THEN 2
            WHEN page = 'purchase' THEN 3
        END AS step
    FROM page_views
    WHERE page IN ('home', 'sign_up', 'first_action', 'purchase')
),
segmented AS (
    SELECT
        f.user_id,
        f.step,
        CASE WHEN pv.first_seen_at >= CURRENT_DATE - INTERVAL '30 days' THEN 'new' ELSE 'returning' END AS user_segment
    FROM funnel_steps f
    JOIN user_profiles pv ON f.user_id = pv.user_id
)
SELECT
    user_segment,
    COUNT(DISTINCT CASE WHEN step = 1 THEN user_id END) AS visitors,
    COUNT(DISTINCT CASE WHEN step = 2 THEN user_id END) AS sign_ups,
    ROUND(COUNT(DISTINCT CASE WHEN step = 2 THEN user_id END)::numeric / COUNT(DISTINCT CASE WHEN step = 1 THEN user_id END) * 100, 2) AS signup_rate
FROM segmented
GROUP BY user_segment
ORDER BY signup_rate DESC;

3. A/B Test: Variant A vs Variant B Conversion

Compare purchase conversion rates between two product page variants.

WITH purchases AS (
    SELECT
        user_id,
        CASE WHEN EXISTS (
            SELECT 1 FROM page_views pv2
            WHERE pv2.user_id = pv.user_id
              AND pv2.page = 'purchase'
        ) THEN 1 ELSE 0 END AS purchased
    FROM page_views pv
    GROUP BY pv.user_id
),
variant_users AS (
    SELECT
        user_id,
        CASE WHEN page like '%variant_b%' THEN 'B' ELSE 'A' END AS variant
    FROM page_views
    GROUP BY user_id
)
SELECT
    v.variant,
    COUNT(DISTINCT v.user_id) AS total_users,
    COUNT(DISTINCT p.user_id) AS purchasers,
    ROUND(COUNT(DISTINCT p.user_id)::numeric / COUNT(DISTINCT v.user_id) * 100, 2) AS conversion_rate
FROM variant_users v
LEFT JOIN purchases p ON v.user_id = p.user_id AND p.purchased = 1
GROUP BY v.variant
ORDER BY conversion_rate DESC;

Key Takeaways for Production

• Always count distinct users, not events — a single user can trigger multiple events per step.
• Window functions with LAG/LEAD can compute step-by-step drop-off rates in a single pass.
• Segment analysis (new vs. returning, by geography, by device) reveals where UX friction is worst.
• Index user_id and page columns for performance on billions of page-view events.