Funnels and conversion — SQL Intermediate Module 5

A funnel is a sequence of steps an entity is supposed to pass through — signup → first purchase → second purchase → churn, or visit → add-to-cart → checkout → payment. The funnel query answers: how many entities reached each step, and where did the biggest drop-offs happen?

The shape of a funnel query

For each user, you want one row with timestamps (or NULLs) for each step they completed. Then you count non-NULLs per step.

sql

WITH user_funnel AS (
  SELECT user_id,
    MIN(CASE WHEN event = 'signup'        THEN event_at END) AS signup_at,
    MIN(CASE WHEN event = 'first_visit'   THEN event_at END) AS visit_at,
    MIN(CASE WHEN event = 'add_to_cart'   THEN event_at END) AS cart_at,
    MIN(CASE WHEN event = 'checkout'      THEN event_at END) AS checkout_at,
    MIN(CASE WHEN event = 'payment'       THEN event_at END) AS paid_at
  FROM events
  GROUP BY user_id
)
SELECT
  COUNT(*)                              AS step_0_total_users,
  COUNT(visit_at)                       AS step_1_visit,
  COUNT(cart_at)                        AS step_2_cart,
  COUNT(checkout_at)                    AS step_3_checkout,
  COUNT(paid_at)                        AS step_4_paid,
  COUNT(paid_at) * 1.0 / COUNT(*)       AS overall_conversion
FROM user_funnel;

Why MIN(CASE …)

MIN() over a CASE that returns the event timestamp on match (NULL otherwise) gives you the first time that user reached that step. SUM/COUNT would over-count; MIN preserves the 'first occurrence' semantic.

Drop-off between steps

Stakeholders usually want both absolute numbers and step-over-step rates. A second-pass pivot makes it clean:

sql

WITH user_funnel AS ( /* as above */ ),
counts AS (
  SELECT
    COUNT(*)            AS s0,
    COUNT(visit_at)     AS s1,
    COUNT(cart_at)      AS s2,
    COUNT(checkout_at)  AS s3,
    COUNT(paid_at)      AS s4
  FROM user_funnel
)
SELECT 'signup'   AS step, s0 AS users, 1.0::float                AS step_rate, 1.0::float                AS overall FROM counts UNION ALL
SELECT 'visit',           s1,            s1 * 1.0 / NULLIF(s0,0),       s1 * 1.0 / NULLIF(s0,0)      FROM counts UNION ALL
SELECT 'cart',            s2,            s2 * 1.0 / NULLIF(s1,0),       s2 * 1.0 / NULLIF(s0,0)      FROM counts UNION ALL
SELECT 'checkout',        s3,            s3 * 1.0 / NULLIF(s2,0),       s3 * 1.0 / NULLIF(s0,0)      FROM counts UNION ALL
SELECT 'paid',            s4,            s4 * 1.0 / NULLIF(s3,0),       s4 * 1.0 / NULLIF(s0,0)      FROM counts;

The 'within N days of the previous step' constraint

Real funnels usually have time windows: 'added to cart within 7 days of signup'. That's a single condition added to the CASE WHEN:

sql

WITH user_funnel AS (
  SELECT user_id,
    MIN(CASE WHEN event = 'signup' THEN event_at END) AS signup_at,
    -- 'cart within 7 days of signup' constraint
    MIN(
      CASE WHEN event = 'add_to_cart'
        AND event_at <= MIN(CASE WHEN event = 'signup' THEN event_at END) + INTERVAL '7 days'
      THEN event_at END
    ) AS cart_within_7d
  FROM events
  GROUP BY user_id
)
SELECT … FROM user_funnel;

The aggregate-inside-aggregate trick

Postgres and most modern engines accept the nested MIN() inside the CASE because the inner MIN is over the same GROUP BY. Older engines or strict-mode setups may require a two-CTE rewrite where you compute signup_at first, then JOIN back.

Funnel vs cohort — when to use which

Funnel — for a single linear sequence of steps; output is rate-per-step
Cohort — for tracking the same entities through time; output is rate-per-period-since-entry
Both — common for activation funnels. Group users by signup cohort, then compute the activation funnel within each cohort.

Exercise

Marketing wants to know, per signup channel, the percentage of users who reach the 'first purchase' step within 30 days of signup. (1) Write the query. (2) Why is it important that 'first purchase' be 'within 30 days' rather than just 'ever'? (3) What's the trap if you compare channels with different cohort ages?