Lesson Intermediate

Funnels

A funnel measures how many users complete each step of an ordered sequence: view → add_to_cart → checkout → purchase. The drop-off between steps tells you where the product is losing people.
Two common flavors:

Any-touch funnel: a user "completed" step N if they ever did it, in any order.
Time-ordered funnel: step N only counts if the user did step N-1 first.

Any-touch is easier to write and looks better; time-ordered is more honest.

A four-step funnel — view, cart, checkout, purchase — with 1000, 600, 360, 180 users; each step keeps 60%, 60%, then 50% of the previous, so the drop-offs are 40%, 40%, 50% — A funnel narrows at each step. The conversion rate is step N divided by step N-1; the drop-off is the rest.

Any-Touch Funnel #

The "did they ever do this?" version. One row per user, one flag per step, MAX(CASE WHEN ...) to collapse.

sql

WITH user_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'view'     THEN 1 ELSE 0 END) AS viewed,
    MAX(CASE WHEN event_name = 'cart'     THEN 1 ELSE 0 END) AS carted,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS purchased
  FROM events
  GROUP BY user_id
)
SELECT
  SUM(viewed)    AS users_viewed,
  SUM(carted)    AS users_carted,
  SUM(purchased) AS users_purchased,
  ROUND(100.0 * SUM(carted)    / NULLIF(SUM(viewed), 0), 1) AS view_to_cart_pct,
  ROUND(100.0 * SUM(purchased) / NULLIF(SUM(carted), 0), 1) AS cart_to_purchase_pct
FROM user_steps;

NULLIF(SUM(viewed), 0) protects against divide-by-zero when nobody hit step 1.

Time-Ordered Funnel #

Step N only counts if the user did step N-1 before it. Capture the first timestamp per step, then enforce the ordering.

sql

WITH user_steps AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_name = 'view'     THEN event_time END) AS t_view,
    MIN(CASE WHEN event_name = 'cart'     THEN event_time END) AS t_cart,
    MIN(CASE WHEN event_name = 'purchase' THEN event_time END) AS t_purchase
  FROM events
  GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (WHERE t_view IS NOT NULL)                                              AS users_viewed,
  COUNT(*) FILTER (WHERE t_view IS NOT NULL AND t_cart     >= t_view)                     AS users_carted,
  COUNT(*) FILTER (WHERE t_view IS NOT NULL AND t_cart     >= t_view
                                            AND t_purchase >= t_cart)                     AS users_purchased
FROM user_steps;

Each counter enforces "this step and every prior step happened in order". A user who purchased before they viewed doesn't count as converted, which is usually the right behavior.

One honest limitation: comparing only the first timestamp per step can undercount. A user who carted once before ever viewing, then viewed, then carted again, genuinely completed view → cart, but their first cart precedes their first view, so this query drops them. Fixing that requires per-event sequence matching (e.g. LEAD/LAG or pattern matching), which costs more than most funnels need.

Warning

Any-touch funnels silently count users who did steps out of order.
If a user bought something, then browsed related items a week later, the any-touch funnel reads that as view → purchase: a clean conversion that never happened. This bias inflates conversion numbers, sometimes dramatically. Default to the time-ordered form for product funnels; only use any-touch when the order genuinely doesn't matter (e.g. feature-adoption counts).

Drop-Off Between Steps #

The conversion rate is just step N divided by step N-1. The drop-off is the complement:

sql

SELECT
  users_viewed,
  users_carted,
  users_purchased,
  ROUND(100.0 - 100.0 * users_carted    / NULLIF(users_viewed, 0), 1) AS view_to_cart_dropoff_pct,
  ROUND(100.0 - 100.0 * users_purchased / NULLIF(users_carted, 0), 1) AS cart_to_purchase_dropoff_pct
FROM funnel_counts;

Dashboards often show both conversion and drop-off side by side: conversion for the win column, drop-off for the "where are we losing people?" column. Pick the framing that matches how stakeholders think.

Try It #

A tiny events input with three users on a signup → activate → first-query funnel. User 104 activated before signing up (bad data or out-of-order logging), which is the case any-touch ignores and time-ordered catches.

Loading SQL editor...

Practice #

From the same events input, build a time-ordered funnel returning users_signup, users_activate, users_first_query, signup_to_activate_pct (rounded to 1), activate_to_query_pct (rounded to 1). One row of output.

Loading SQL editor...

Mistakes to Watch For #

Using any-touch when you meant time-ordered: it inflates conversion by counting out-of-order users.
Dividing step totals without NULLIF(..., 0). An empty top of funnel crashes the query with divide-by-zero.
Mixing user-level and event-level grain. If you forget the GROUP BY user_id, the "users at step N" count counts events, not distinct users.
Hard-coding step names in the SELECT. When the product adds a step, the query needs editing; for a production funnel, parameterize the step list in a CTE of (step_name, step_order) pairs.
Measuring conversion over the wrong time window. A user who signed up today hasn't had time to do step 4 yet, and pulling them into the denominator makes today's conversion look artificially bad. Cohort the top of funnel by signup date and give each cohort a fair window to convert.

Knowledge check #

3 questions

0 / 3 answered

For each funnel step, the conversion rate is typically computed as:
A user who reached step 3 but not step 2 (skipped a step) — should they count as a step-3 conversion?
For "% of users who reached step 4," the most common time-window mistake is:

Next Step #

Continue to Rolling metrics: period-over-period deltas, % change, and smoothed trend metrics for time-series KPIs.