Part 176: Funnel Conversion Analysis in Pure SQL

Calculating step-by-step conversion rates and user drop-offs through an e-commerce checkout funnel.

1. The E-Commerce Funnel Pipeline

A typical purchase funnel consists of sequential milestones: 1. view_product (Top of Funnel) 2. add_to_cart 3. start_checkout 4. purchase_completed (Conversion)

We need to calculate: - How many unique users reached each step. - The step-to-step drop-off percentage. - The overall conversion rate.

2. Funnel Aggregation with Conditional Max & Sum

WITH user_funnel_flags AS (
    -- Step 1: Flag whether each user achieved each funnel step
    SELECT 
        user_id,
        MAX(CASE WHEN event_name = 'view_product' THEN 1 ELSE 0 END) AS step_1_view,
        MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS step_2_cart,
        MAX(CASE WHEN event_name = 'start_checkout' THEN 1 ELSE 0 END) AS step_3_checkout,
        MAX(CASE WHEN event_name = 'purchase_completed' THEN 1 ELSE 0 END) AS step_4_purchased
    FROM event_logs
    WHERE event_time >= '2026-09-01' AND event_time < '2026-10-01'
    GROUP BY user_id
),
enforced_funnel AS (
    -- Step 2: Enforce sequential progression (cannot do step N without step N-1)
    SELECT 
        user_id,
        step_1_view,
        CASE WHEN step_1_view = 1 AND step_2_cart = 1 THEN 1 ELSE 0 END AS reached_cart,
        CASE WHEN step_1_view = 1 AND step_2_cart = 1 AND step_3_checkout = 1 THEN 1 ELSE 0 END AS reached_checkout,
        CASE WHEN step_1_view = 1 AND step_2_cart = 1 AND step_3_checkout = 1 AND step_4_purchased = 1 THEN 1 ELSE 0 END AS reached_purchase
    FROM user_funnel_flags
)
-- Step 3: Aggregate totals and calculate conversion rates
SELECT 
    SUM(step_1_view) AS views,
    SUM(reached_cart) AS carts,
    ROUND(100.0 * SUM(reached_cart) / NULLIF(SUM(step_1_view), 0), 2) AS view_to_cart_pct,
    SUM(reached_checkout) AS checkouts,
    ROUND(100.0 * SUM(reached_checkout) / NULLIF(SUM(reached_cart), 0), 2) AS cart_to_checkout_pct,
    SUM(reached_purchase) AS purchases,
    ROUND(100.0 * SUM(reached_purchase) / NULLIF(SUM(reached_checkout), 0), 2) AS checkout_to_purchase_pct,
    ROUND(100.0 * SUM(reached_purchase) / NULLIF(SUM(step_1_view), 0), 2) AS overall_conversion_pct
FROM enforced_funnel;

Key Takeaways

• Always use NULLIF(denominator, 0) to prevent division-by-zero errors when calculating conversion ratios.
• Sequential condition checks ensure users are only credited if they followed the intended step order.

In Part 177, we explore Retention & Cohort Analysis in SQL: N-Day Retention Matrices!