Funnel Analysis

Technical Explanation

Funnel analysis visualizes the steps users take from initial awareness to a desired conversion, showing where users drop off at each stage. It reveals bottlenecks and optimization opportunities in user journeys.

Funnel Components

Stage	Description
Entry	Users enter the funnel (awareness)
Progression	Users move through steps
Drop-off	Users who leave at each stage
Conversion	Users who complete the funnel

Common Funnels

• Acquisition: Visit → Signup → First purchase
• E-commerce: Browse → Add to cart → Checkout → Purchase
• Activation: Signup → Profile complete → First action

---

Code Examples

Using the CatCafe dataset:

-- Table: user_events
-- user_id, event_type, event_timestamp

-- Step 1: Identify funnel stages
-- Funnel: page_view → add_to_cart → checkout_start → purchase

WITH funnel_stages AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_type = 'page_view' THEN 1 END) as viewed,
        MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 END) as added_cart,
        MAX(CASE WHEN event_type = 'checkout_start' THEN 1 END) as started_checkout,
        MAX(CASE WHEN event_type = 'purchase' THEN 1 END) as purchased
    FROM user_events
    GROUP BY user_id
)
SELECT
    'Page Views' as stage,
    COUNT(*) as users
FROM funnel_stages
WHERE viewed = 1
UNION ALL
SELECT
    'Added to Cart',
    COUNT(*)
FROM funnel_stages
WHERE added_cart = 1
UNION ALL
SELECT
    'Started Checkout',
    COUNT(*)
FROM funnel_stages
WHERE started_checkout = 1
UNION ALL
SELECT
    'Purchased',
    COUNT(*)
FROM funnel_stages
WHERE purchased = 1;

-- Stage-by-stage conversion rates
WITH funnel_stages AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_type = 'page_view' THEN 1 END) as viewed,
        MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 END) as added_cart,
        MAX(CASE WHEN event_type = 'checkout_start' THEN 1 END) as started_checkout,
        MAX(CASE WHEN event_type = 'purchase' THEN 1 END) as purchased
    FROM user_events
    GROUP BY user_id
),
counts AS (
    SELECT
        SUM(viewed) as views,
        SUM(added_cart) as carts,
        SUM(started_checkout) as checkouts,
        SUM(purchased) as purchases
    FROM funnel_stages
)
SELECT
    views,
    carts,
    checkouts,
    purchases,
    carts * 100.0 / NULLIF(views, 0) as view_to_cart_pct,
    checkouts * 100.0 / NULLIF(carts, 0) as cart_to_checkout_pct,
    purchases * 100.0 / NULLIF(checkouts, 0) as checkout_to_purchase_pct,
    purchases * 100.0 / NULLIF(views, 0) as overall_conversion_pct
FROM counts;

-- Time-based funnel analysis
SELECT
    DATE_TRUNC('week', event_timestamp) as week,
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'page_view') as views,
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'add_to_cart') as carts,
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'purchase') as purchases
FROM user_events
WHERE event_timestamp >= CURRENT_DATE - INTERVAL '8 weeks'
GROUP BY DATE_TRUNC('week', event_timestamp)
ORDER BY week;

-- Finding the biggest drop-off point
WITH funnel AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_type = 'page_view' THEN 1 END) as viewed,
        MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 END) as added_cart,
        MAX(CASE WHEN event_type = 'checkout_start' THEN 1 END) as started_checkout,
        MAX(CASE WHEN event_type = 'purchase' THEN 1 END) as purchased
    FROM user_events
    GROUP BY user_id
)
SELECT
    SUM(viewed) as total_views,
    SUM(viewed) - SUM(added_cart) as dropped_at_cart,
    SUM(added_cart) - SUM(started_checkout) as dropped_at_checkout,
    SUM(started_checkout) - SUM(purchased) as dropped_at_purchase,
    (SUM(viewed) - SUM(added_cart)) * 100.0 / NULLIF(SUM(viewed), 0) as cart_drop_pct,
    (SUM(added_cart) - SUM(started_checkout)) * 100.0 / NULLIF(SUM(added_cart), 0) as checkout_drop_pct,
    (SUM(started_checkout) - SUM(purchased)) * 100.0 / NULLIF(SUM(started_checkout), 0) as purchase_drop_pct
FROM funnel;

---

The Cat Analogy

Think of funnel analysis like teaching a cat a new trick:

Stage 1: Show treat
  → All 10 cats show interest

Stage 2: Cat sniffs the command
  → 7 cats acknowledge (sit, look)

Stage 3: Cat performs the trick
  → 4 cats actually do it

Stage 4: Cat gets the treat (conversion!)
  → 3 cats successfully complete

Drop-off analysis:
  10 → 7 (30% drop): Need better command visibility
  7 → 4 (43% drop): Need clearer demonstration
  4 → 3 (25% drop): Treat not rewarding enough

Funnel tells you WHERE to optimize, not just how many converted overall.

---

Common Pitfalls

Wrong Attribution

-- WRONG: Counting users at each stage independently
SELECT
    COUNT(DISTINCT CASE WHEN event_type = 'page_view' THEN user_id END) as views,
    COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) as purchases
FROM user_events;
-- A user who viewed 10 times then purchased counts as 10 views!

-- RIGHT: Check if user passed through each stage
SELECT DISTINCT user_id, event_type FROM user_events WHERE ...;

Ignoring Time Windows

-- WRONG: Funnel across all time
SELECT ...
FROM user_events;
-- A 2023 page view and 2024 purchase are not a funnel!

-- RIGHT: Time-boxed funnel
SELECT ...
FROM user_events
WHERE event_timestamp >= CURRENT_DATE - INTERVAL '30 days';

---

Exercises

Exercise 1

Build a funnel for: page_view → signup → first_order. Write the queries showing stage counts and conversion rates.

Exercise 2

What is the biggest drop-off point in most e-commerce funnels?

Exercise 3

Write a query to compare the funnel for customers acquired via email vs organic search.

Exercise 4

Why might a funnel show 100% conversion at a stage?

Exercise 5

Design a funnel for a cat adoption website: visit → application → approval → adoption.

---

Key Takeaways

• Funnel analysis shows where users drop off between stages
• Calculate stage-by-stage conversion rates to find bottlenecks
• Always time-box your analysis appropriately
• Attribution matters: a user at multiple stages counts at each
• Focus optimization efforts on the biggest drop-off points
• Compare funnels across segments to find patterns