Advanced Funnel Analysis: Finding Conversion Rates and Drop-off Points in SQL

Pinpoint exactly where users drop off with SQL. Everything about calculating step-by-step conversion rates.

TL;DR

• Funnel Analysis: Measure step-by-step conversion rates toward a goal
• Drop-off Points: Steps with steep conversion drops = improvement opportunities
• SQL Implementation: Handle event sequences, time limits, session-based analysis
• Key Insight: You need to know where users drop off to know what to fix

1. What is Funnel Analysis?

Basic Concept

Visit (1000)
   ↓ 60%
Sign Up (600)
   ↓ 50%
Add to Cart (300)
   ↓ 33%
Purchase (100)

Overall conversion rate: 10% (100/1000)

Why It Matters

• Looking only at 10% conversion hides where the problem is
• Step-by-step view shows "Cart → Purchase" (33%) is the bottleneck
• Improving this step significantly boosts overall conversion

2. Basic Table Structure

-- Event log table
CREATE TABLE events (
    event_id BIGINT PRIMARY KEY,
    user_id INT,
    session_id VARCHAR(100),
    event_name VARCHAR(100),
    event_timestamp TIMESTAMP,
    page_url VARCHAR(500),
    properties JSONB
);

-- Example events
-- 'page_view', 'signup_start', 'signup_complete',
-- 'add_to_cart', 'checkout_start', 'purchase_complete'

3. Basic Funnel: Users per Step

Simplest Funnel Query

WITH funnel_steps AS (
    SELECT
        'step1_visit' as step,
        COUNT(DISTINCT user_id) as users
    FROM events
    WHERE event_name = 'page_view'
      AND event_timestamp >= '2024-01-01'
      AND event_timestamp < '2024-02-01'

    UNION ALL

    SELECT
        'step2_signup' as step,
        COUNT(DISTINCT user_id) as users
    FROM events
    WHERE event_name = 'signup_complete'
      AND event_timestamp >= '2024-01-01'
      AND event_timestamp < '2024-02-01'

    UNION ALL

    SELECT
        'step3_add_cart' as step,
        COUNT(DISTINCT user_id) as users
    FROM events
    WHERE event_name = 'add_to_cart'
      AND event_timestamp >= '2024-01-01'
      AND event_timestamp < '2024-02-01'

    UNION ALL

    SELECT
        'step4_purchase' as step,
        COUNT(DISTINCT user_id) as users
    FROM events
    WHERE event_name = 'purchase_complete'
      AND event_timestamp >= '2024-01-01'
      AND event_timestamp < '2024-02-01'
)
SELECT
    step,
    users,
    FIRST_VALUE(users) OVER (ORDER BY step) as total_users,
    ROUND(users * 100.0 / FIRST_VALUE(users) OVER (ORDER BY step), 2) as conversion_rate,
    LAG(users) OVER (ORDER BY step) as prev_step_users,
    ROUND(users * 100.0 / NULLIF(LAG(users) OVER (ORDER BY step), 0), 2) as step_conversion
FROM funnel_steps
ORDER BY step;

Result:

4. Sequential Funnel: When Order Matters

Count Only Users Who Completed Previous Steps

WITH step1 AS (
    -- Step 1: Visit
    SELECT DISTINCT user_id, MIN(event_timestamp) as step1_time
    FROM events
    WHERE event_name = 'page_view'
      AND event_timestamp >= '2024-01-01'
    GROUP BY user_id
),
step2 AS (
    -- Step 2: Signup (after visit)
    SELECT DISTINCT s1.user_id, MIN(e.event_timestamp) as step2_time
    FROM step1 s1
    JOIN events e ON s1.user_id = e.user_id
    WHERE e.event_name = 'signup_complete'
      AND e.event_timestamp > s1.step1_time
    GROUP BY s1.user_id
),
step3 AS (
    -- Step 3: Add to cart (after signup)
    SELECT DISTINCT s2.user_id, MIN(e.event_timestamp) as step3_time
    FROM step2 s2
    JOIN events e ON s2.user_id = e.user_id
    WHERE e.event_name = 'add_to_cart'
      AND e.event_timestamp > s2.step2_time
    GROUP BY s2.user_id
),
step4 AS (
    -- Step 4: Purchase (after cart)
    SELECT DISTINCT s3.user_id, MIN(e.event_timestamp) as step4_time
    FROM step3 s3
    JOIN events e ON s3.user_id = e.user_id
    WHERE e.event_name = 'purchase_complete'
      AND e.event_timestamp > s3.step3_time
    GROUP BY s3.user_id
)
SELECT
    'Step 1: Visit' as step,
    COUNT(*) as users,
    100.0 as conversion_rate
FROM step1

UNION ALL

SELECT
    'Step 2: Signup',
    COUNT(*),
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM step1), 2)
FROM step2

UNION ALL

SELECT
    'Step 3: Add to Cart',
    COUNT(*),
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM step1), 2)
FROM step3

UNION ALL

SELECT
    'Step 4: Purchase',
    COUNT(*),
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM step1), 2)
FROM step4;

5. Time-Bounded Funnel

Users Who Completed Purchase Within 7 Days

WITH funnel AS (
    SELECT
        user_id,
        MIN(CASE WHEN event_name = 'page_view' THEN event_timestamp END) as visit_time,
        MIN(CASE WHEN event_name = 'signup_complete' THEN event_timestamp END) as signup_time,
        MIN(CASE WHEN event_name = 'add_to_cart' THEN event_timestamp END) as cart_time,
        MIN(CASE WHEN event_name = 'purchase_complete' THEN event_timestamp END) as purchase_time
    FROM events
    WHERE event_timestamp >= '2024-01-01'
    GROUP BY user_id
),
ordered_funnel AS (
    SELECT
        user_id,
        visit_time,
        -- Signup after visit
        CASE WHEN signup_time > visit_time THEN signup_time END as signup_time,
        -- Cart after signup
        CASE WHEN cart_time > signup_time AND signup_time > visit_time THEN cart_time END as cart_time,
        -- Purchase after cart (within 7 days)
        CASE WHEN purchase_time > cart_time
             AND cart_time > signup_time
             AND purchase_time - visit_time <= INTERVAL '7 days'
             THEN purchase_time END as purchase_time
    FROM funnel
)
SELECT
    COUNT(*) as total_visitors,
    COUNT(signup_time) as signed_up,
    COUNT(cart_time) as added_to_cart,
    COUNT(purchase_time) as purchased_within_7_days,
    ROUND(COUNT(signup_time) * 100.0 / COUNT(*), 2) as signup_rate,
    ROUND(COUNT(cart_time) * 100.0 / COUNT(*), 2) as cart_rate,
    ROUND(COUNT(purchase_time) * 100.0 / COUNT(*), 2) as purchase_rate_7d
FROM ordered_funnel
WHERE visit_time IS NOT NULL;

6. Session-Based Funnel

Users Who Converted Within Same Session

WITH session_funnel AS (
    SELECT
        session_id,
        user_id,
        MAX(CASE WHEN event_name = 'page_view' THEN 1 ELSE 0 END) as visited,
        MAX(CASE WHEN event_name = 'product_view' THEN 1 ELSE 0 END) as viewed_product,
        MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) as added_cart,
        MAX(CASE WHEN event_name = 'checkout_start' THEN 1 ELSE 0 END) as started_checkout,
        MAX(CASE WHEN event_name = 'purchase_complete' THEN 1 ELSE 0 END) as purchased
    FROM events
    WHERE event_timestamp >= '2024-01-01'
    GROUP BY session_id, user_id
)
SELECT
    SUM(visited) as sessions,
    SUM(viewed_product) as viewed_product,
    SUM(added_cart) as added_cart,
    SUM(started_checkout) as started_checkout,
    SUM(purchased) as purchased,
    ROUND(SUM(viewed_product) * 100.0 / NULLIF(SUM(visited), 0), 2) as view_rate,
    ROUND(SUM(added_cart) * 100.0 / NULLIF(SUM(viewed_product), 0), 2) as cart_rate,
    ROUND(SUM(started_checkout) * 100.0 / NULLIF(SUM(added_cart), 0), 2) as checkout_rate,
    ROUND(SUM(purchased) * 100.0 / NULLIF(SUM(started_checkout), 0), 2) as purchase_rate
FROM session_funnel;

7. Drop-off Analysis: Where Do Users Leave?

Count Users Who Dropped at Each Step

WITH user_furthest_step AS (
    SELECT
        user_id,
        CASE
            WHEN MAX(CASE WHEN event_name = 'purchase_complete' THEN 1 ELSE 0 END) = 1 THEN 'purchased'
            WHEN MAX(CASE WHEN event_name = 'checkout_start' THEN 1 ELSE 0 END) = 1 THEN 'checkout'
            WHEN MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) = 1 THEN 'cart'
            WHEN MAX(CASE WHEN event_name = 'product_view' THEN 1 ELSE 0 END) = 1 THEN 'product_view'
            WHEN MAX(CASE WHEN event_name = 'page_view' THEN 1 ELSE 0 END) = 1 THEN 'visit_only'
            ELSE 'unknown'
        END as furthest_step
    FROM events
    WHERE event_timestamp >= '2024-01-01'
    GROUP BY user_id
)
SELECT
    furthest_step as dropped_at,
    COUNT(*) as users,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as percentage
FROM user_furthest_step
GROUP BY furthest_step
ORDER BY
    CASE furthest_step
        WHEN 'visit_only' THEN 1
        WHEN 'product_view' THEN 2
        WHEN 'cart' THEN 3
        WHEN 'checkout' THEN 4
        WHEN 'purchased' THEN 5
        ELSE 6
    END;

Result:

→ 50% drop off after just visiting! Product exposure might be the issue.

8. Time-to-Convert Analysis

Average Time Between Steps

WITH step_times AS (
    SELECT
        user_id,
        MIN(CASE WHEN event_name = 'page_view' THEN event_timestamp END) as visit_time,
        MIN(CASE WHEN event_name = 'signup_complete' THEN event_timestamp END) as signup_time,
        MIN(CASE WHEN event_name = 'add_to_cart' THEN event_timestamp END) as cart_time,
        MIN(CASE WHEN event_name = 'purchase_complete' THEN event_timestamp END) as purchase_time
    FROM events
    GROUP BY user_id
),
time_diffs AS (
    SELECT
        user_id,
        EXTRACT(EPOCH FROM (signup_time - visit_time)) / 60 as visit_to_signup_min,
        EXTRACT(EPOCH FROM (cart_time - signup_time)) / 60 as signup_to_cart_min,
        EXTRACT(EPOCH FROM (purchase_time - cart_time)) / 60 as cart_to_purchase_min,
        EXTRACT(EPOCH FROM (purchase_time - visit_time)) / 60 as total_time_min
    FROM step_times
    WHERE purchase_time IS NOT NULL
)
SELECT
    'Visit → Signup' as transition,
    ROUND(AVG(visit_to_signup_min), 2) as avg_minutes,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY visit_to_signup_min), 2) as median_minutes
FROM time_diffs
WHERE visit_to_signup_min > 0

UNION ALL

SELECT
    'Signup → Cart',
    ROUND(AVG(signup_to_cart_min), 2),
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY signup_to_cart_min), 2)
FROM time_diffs
WHERE signup_to_cart_min > 0

UNION ALL

SELECT
    'Cart → Purchase',
    ROUND(AVG(cart_to_purchase_min), 2),
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cart_to_purchase_min), 2)
FROM time_diffs
WHERE cart_to_purchase_min > 0;

9. Segment Comparison

Conversion Rates by Channel

WITH first_touch AS (
    SELECT
        user_id,
        FIRST_VALUE(properties->>'utm_source') OVER (
            PARTITION BY user_id ORDER BY event_timestamp
        ) as channel
    FROM events
    WHERE event_timestamp >= '2024-01-01'
),
user_funnel AS (
    SELECT
        e.user_id,
        ft.channel,
        MAX(CASE WHEN e.event_name = 'page_view' THEN 1 ELSE 0 END) as visited,
        MAX(CASE WHEN e.event_name = 'signup_complete' THEN 1 ELSE 0 END) as signed_up,
        MAX(CASE WHEN e.event_name = 'purchase_complete' THEN 1 ELSE 0 END) as purchased
    FROM events e
    JOIN first_touch ft ON e.user_id = ft.user_id
    WHERE e.event_timestamp >= '2024-01-01'
    GROUP BY e.user_id, ft.channel
)
SELECT
    COALESCE(channel, 'direct') as channel,
    SUM(visited) as visitors,
    SUM(signed_up) as signups,
    SUM(purchased) as purchases,
    ROUND(SUM(signed_up) * 100.0 / NULLIF(SUM(visited), 0), 2) as signup_rate,
    ROUND(SUM(purchased) * 100.0 / NULLIF(SUM(visited), 0), 2) as purchase_rate
FROM user_funnel
GROUP BY channel
ORDER BY visitors DESC;

Result:

→ Direct traffic converts best!

10. Dynamic Funnel Query

Generate Funnel from Event List Parameter

-- PostgreSQL: Define funnel steps as array
WITH funnel_events AS (
    SELECT unnest(ARRAY[
        'page_view',
        'product_view',
        'add_to_cart',
        'checkout_start',
        'purchase_complete'
    ]) as event_name,
    generate_series(1, 5) as step_order
),
user_steps AS (
    SELECT
        e.user_id,
        fe.event_name,
        fe.step_order,
        MIN(e.event_timestamp) as first_occurrence
    FROM events e
    JOIN funnel_events fe ON e.event_name = fe.event_name
    WHERE e.event_timestamp >= '2024-01-01'
    GROUP BY e.user_id, fe.event_name, fe.step_order
),
sequential_funnel AS (
    SELECT
        us.user_id,
        us.step_order,
        us.event_name,
        us.first_occurrence,
        LAG(us.first_occurrence) OVER (
            PARTITION BY us.user_id ORDER BY us.step_order
        ) as prev_step_time
    FROM user_steps us
)
SELECT
    step_order,
    event_name,
    COUNT(DISTINCT user_id) as users,
    ROUND(
        COUNT(DISTINCT user_id) * 100.0 /
        FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (ORDER BY step_order),
        2
    ) as conversion_rate
FROM sequential_funnel
WHERE step_order = 1 OR first_occurrence > prev_step_time
GROUP BY step_order, event_name
ORDER BY step_order;

11. Pro Tips

1. Overall vs Step Conversion

-- Overall conversion: vs step 1
conversion_rate = step_n_users / step1_users

-- Step conversion: vs previous step
step_conversion = step_n_users / step_(n-1)_users

Both matter! Step conversion finds bottlenecks.

2. Statistical Significance

-- Watch out for small samples
WHERE cohort_size >= 100

3. Time Window Settings

-- Too long is meaningless
AND purchase_time - visit_time <= INTERVAL '30 days'

Conclusion

Funnel Analysis Key Points:

• Step-by-step conversion matters more than overall rate
• High drop-off step = improvement priority
• Segment comparison reveals optimization opportunities

References

1. Amplitude - Funnel Analysis Guide
2. Mixpanel - Understanding Conversion Funnels
3. Google Analytics - Goal Funnel Visualization