Funnel decomposition: finding exactly which step is broken

A funnel is the sequence of steps a user takes toward a goal (view a product, add to cart, check out), where some people drop off at each step. "Decomposing" a funnel means measuring the conversion rate between each pair of steps separately, instead of one overall number, so you can see exactly where people are actually leaving.

🎯 Explain Like I'm Hired A funnel is the path a user walks toward doing something, like buying a product. At each step, some people fall away. Decomposing it means checking the drop-off at every single step, not just the start-to-finish rate, so you know precisely where the leak is. Example: if overall view-to-purchase conversion drops from 5% to 3%, decomposing the funnel might show view-to-cart is unchanged but cart-to-checkout collapsed, pointing straight at a checkout bug, instead of a vague "the whole funnel got worse."

WITH steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event = 'viewed_product' THEN 1 ELSE 0 END) AS viewed,
    MAX(CASE WHEN event = 'added_to_cart' THEN 1 ELSE 0 END) AS added,
    MAX(CASE WHEN event = 'checked_out' THEN 1 ELSE 0 END) AS checked_out
  FROM events
  GROUP BY user_id
)
SELECT
  SUM(viewed) AS step1, SUM(added) AS step2, SUM(checked_out) AS step3,
  ROUND(100.0 * SUM(added) / NULLIF(SUM(viewed), 0), 1) AS view_to_cart_pct,
  ROUND(100.0 * SUM(checked_out) / NULLIF(SUM(added), 0), 1) AS cart_to_checkout_pct
FROM steps;