Skip to main content

Funnel Analysis

Learning Focus

Use this lesson to build conversion funnel analysis — measure how many users progress through each step of a defined journey.

What is Funnel Analysis?​

A funnel tracks how many users (or events) move through a sequential series of steps:

Step 1: Visit page        1,000 users
Step 2: View product        600 users   (60% from step 1)
Step 3: Add to cart         300 users   (50% from step 2)
Step 4: Checkout            180 users   (60% from step 3)
Step 5: Purchase            120 users   (67% from step 4)

Sample Data​

CREATE TABLE user_events (
    user_id    INTEGER,
    event_type VARCHAR,
    event_date TIMESTAMP
);

INSERT INTO user_events VALUES
    (1, 'page_view',  '2024-01-01 10:00'),
    (1, 'view_product','2024-01-01 10:05'),
    (1, 'add_to_cart','2024-01-01 10:10'),
    (1, 'checkout',   '2024-01-01 10:15'),
    (1, 'purchase',   '2024-01-01 10:20'),
    (2, 'page_view',  '2024-01-01 11:00'),
    (2, 'view_product','2024-01-01 11:05'),
    (2, 'add_to_cart','2024-01-01 11:10'),
    (3, 'page_view',  '2024-01-01 12:00'),
    (3, 'view_product','2024-01-01 12:05'),
    (4, 'page_view',  '2024-01-01 13:00');

Build the Funnel​

WITH funnel AS (
    SELECT
        COUNT(DISTINCT CASE WHEN event_type = 'page_view'   THEN user_id END) AS step1_visit,
        COUNT(DISTINCT CASE WHEN event_type = 'view_product' THEN user_id END) AS step2_view,
        COUNT(DISTINCT CASE WHEN event_type = 'add_to_cart' THEN user_id END) AS step3_cart,
        COUNT(DISTINCT CASE WHEN event_type = 'checkout'    THEN user_id END) AS step4_checkout,
        COUNT(DISTINCT CASE WHEN event_type = 'purchase'    THEN user_id END) AS step5_purchase
    FROM user_events
)
SELECT
    'Visit'    AS step, step1_visit AS users, 100.0 AS pct_from_start,
    NULL AS pct_from_prev FROM funnel
UNION ALL
SELECT 'View Product', step2_view,
    ROUND(step2_view * 100.0 / step1_visit, 1),
    ROUND(step2_view * 100.0 / step1_visit, 1) FROM funnel
UNION ALL
SELECT 'Add to Cart',  step3_cart,
    ROUND(step3_cart * 100.0 / step1_visit, 1),
    ROUND(step3_cart * 100.0 / step2_view,  1) FROM funnel
UNION ALL
SELECT 'Checkout',     step4_checkout,
    ROUND(step4_checkout * 100.0 / step1_visit,  1),
    ROUND(step4_checkout * 100.0 / step3_cart,   1) FROM funnel
UNION ALL
SELECT 'Purchase',     step5_purchase,
    ROUND(step5_purchase * 100.0 / step1_visit,  1),
    ROUND(step5_purchase * 100.0 / step4_checkout, 1) FROM funnel;

Ordered Funnel (Strict Step Sequence)​

-- Only count users who completed steps in order
WITH step_flags AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_type = 'page_view'    THEN event_date END) AS s1,
        MAX(CASE WHEN event_type = 'view_product' THEN event_date END) AS s2,
        MAX(CASE WHEN event_type = 'add_to_cart'  THEN event_date END) AS s3,
        MAX(CASE WHEN event_type = 'checkout'     THEN event_date END) AS s4,
        MAX(CASE WHEN event_type = 'purchase'     THEN event_date END) AS s5
    FROM user_events
    GROUP BY user_id
)
SELECT
    COUNT(*) FILTER (WHERE s1 IS NOT NULL)                           AS visited,
    COUNT(*) FILTER (WHERE s2 IS NOT NULL AND s2 > s1)              AS viewed_product,
    COUNT(*) FILTER (WHERE s3 IS NOT NULL AND s3 > s2 AND s2 > s1)  AS added_to_cart,
    COUNT(*) FILTER (WHERE s4 IS NOT NULL AND s4 > s3)              AS checked_out,
    COUNT(*) FILTER (WHERE s5 IS NOT NULL AND s5 > s4)              AS purchased
FROM step_flags;

Common Pitfalls​

PitfallConsequencePrevention
Not enforcing step orderOver-counted conversionsUse event_date ordering per user
Using COUNT(*) instead of COUNT(DISTINCT user_id)Users counted multiple timesAlways use COUNT(DISTINCT user_id)
Including events outside session windowCross-session pollutionFilter by session window (e.g., same day)

Quick Reference​

COUNT(DISTINCT CASE WHEN event_type = 'step' THEN user_id END) AS step_n

-- Step % from previous
ROUND(step_n * 100.0 / step_n_minus_1, 1) AS conversion_pct

What's Next​