SQL Query for Funnel Analysis (Step-by-Step Conversion)

What a funnel query needs to produce

A funnel is a sequence of steps a user is expected to move through — page_view → add_to_cart → checkout → purchase — and the output is simple to describe but easy to get subtly wrong: one row per step, with the count of distinct users who reached it, and the conversion rate from the previous step. Everything below builds up from a single events table:

User 1 completed all three steps, user 2 dropped off after adding to cart, user 3 dropped off after the first view.

The loose funnel: conditional aggregation

The fastest way to a funnel count: flag each user with whether they ever fired each event, then sum the flags.

WITH user_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'page_view'   THEN 1 ELSE 0 END) AS did_view,
    MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS did_cart,
    MAX(CASE WHEN event_name = 'purchase'     THEN 1 ELSE 0 END) AS did_purchase
  FROM events
  GROUP BY user_id
)
SELECT
  SUM(did_view)     AS step1_page_view,
  SUM(did_cart)     AS step2_add_to_cart,
  SUM(did_purchase) AS step3_purchase
FROM user_steps;

This is "loose" because it only checks that each event happened at some point — a user who purchased first and viewed the page afterward would still count as completing all three steps, which usually isn't what "funnel" is supposed to mean.

Step-over-step vs. overall conversion rate

These are two different numbers that get confused constantly. Overall conversion always divides by the first step; step-over-step conversion divides by the immediately preceding step:

SELECT
  step1_page_view,
  step2_add_to_cart,
  step3_purchase,
  ROUND(step2_add_to_cart * 100.0 / step1_page_view, 1) AS step_over_step_1_to_2,
  ROUND(step3_purchase     * 100.0 / step2_add_to_cart, 1) AS step_over_step_2_to_3,
  ROUND(step3_purchase     * 100.0 / step1_page_view, 1) AS overall_conversion
FROM funnel_counts;

The strict (ordered) funnel

To require steps to happen in order, compare each step's timestamp against the previous step's, per user — a self-join on the same events table is the clearest way to express it:

WITH viewed AS (
  SELECT user_id, MIN(event_at) AS viewed_at
  FROM events WHERE event_name = 'page_view'
  GROUP BY user_id
),
carted AS (
  SELECT e.user_id, MIN(e.event_at) AS carted_at
  FROM events e
  JOIN viewed v ON v.user_id = e.user_id
  WHERE e.event_name = 'add_to_cart'
    AND e.event_at > v.viewed_at   -- must happen AFTER the view
  GROUP BY e.user_id
),
purchased AS (
  SELECT e.user_id, MIN(e.event_at) AS purchased_at
  FROM events e
  JOIN carted c ON c.user_id = e.user_id
  WHERE e.event_name = 'purchase'
    AND e.event_at > c.carted_at    -- must happen AFTER add-to-cart
  GROUP BY e.user_id
)
SELECT
  (SELECT COUNT(*) FROM viewed)    AS step1,
  (SELECT COUNT(*) FROM carted)    AS step2,
  (SELECT COUNT(*) FROM purchased) AS step3;

Each CTE chains onto the previous one — carted only keeps rows where the add-to-cart timestamp is strictly after that user's earliest page view, and purchased only keeps rows after that. This is the pattern that separates "did all three things eventually" from "actually walked the funnel."

Time-boxing the funnel

Without a time limit, a user who viewed a product a year ago and happened to purchase something today still counts as a funnel completion — which usually overstates how well the funnel is actually converting. Add a window, typically to the last join:

WHERE e.event_name = 'purchase'
  AND e.event_at > c.carted_at
  AND e.event_at <= c.carted_at + INTERVAL '7 days'  -- must purchase within 7 days

7 days is arbitrary here — the right window depends entirely on the product. A grocery delivery app's funnel window might be an hour; a B2B software trial's might be 30 days.

Full worked example

Putting the strict, time-boxed funnel together with conversion rates:

WITH step_counts AS (
  SELECT
    (SELECT COUNT(*) FROM viewed)    AS step1,
    (SELECT COUNT(*) FROM carted)    AS step2,
    (SELECT COUNT(*) FROM purchased) AS step3
)
SELECT
  step1, step2, step3,
  ROUND(step2 * 100.0 / step1, 1) AS view_to_cart_pct,
  ROUND(step3 * 100.0 / step2, 1) AS cart_to_purchase_pct,
  ROUND(step3 * 100.0 / step1, 1) AS overall_pct
FROM step_counts;

Common mistakes

• Using COUNT(*) instead of COUNT(DISTINCT user_id). A single user retriggering an event (double-clicking "add to cart") inflates that step's count.
• Building a loose funnel when a strict one was needed. If order matters for the question being asked, conditional aggregation alone silently overstates conversion.
• No time box on a long-running funnel. Without one, ancient activity gets credited toward a completion that happened for unrelated reasons.
• Dividing every step by the first step's count when step-over-step conversion was what stakeholders actually wanted — the two numbers tell very different stories.
• Ignoring timezone consistency between the events feeding each step, which can silently shift users across day boundaries when time-boxing.