Funnel Queries

An office building checks its visitors at four points: the sign-in desk, the ID check, the bag scanner, and the elevator gate. A guard who counts the people at each point might see 200 sign in, 180 show an ID, 170 clear the scanner, and 160 reach the elevators. The four numbers show where people leave the line.

A website can be measured the same way, with one difference. In the building, each person passes the points once and in order, while a website's event log guarantees neither: a visitor can repeat a step, open a later page through a saved link, or come back a week later. Counting how many people reach each step of an ordered process, and what share continue from step to step, is called a funnel analysis, and the SQL that produces it is a funnel query.

The Table

An insurance company sells home insurance online, and a quote has four steps. The visitor starts a quote (start), enters details about the home (details), sees a price (price_shown), and buys the policy (purchase). Each page view writes one row to quote_events: event_id, visitor_id, day_no, event_min (the minute of the day, from 0 to 1439), and step.

The table holds 30 rows from nine visitors:

Every query below was run on MySQL 8.4, and the output shown is the real output.

Events Are Not Visitors

A step count is the number of distinct visitors with at least one event at that step, not the number of events. A GROUP BY on the step returns both figures:

SELECT step,
       COUNT(*) AS events,
       COUNT(DISTINCT visitor_id) AS visitors
FROM quote_events
GROUP BY step
ORDER BY FIELD(step, 'start', 'details', 'price_shown', 'purchase');
Copy

Output:

+-------------+--------+----------+
| step        | events | visitors |
+-------------+--------+----------+
| start       |      8 |        8 |
| details     |      7 |        7 |
| price_shown |     10 |        7 |
| purchase    |      5 |        4 |
+-------------+--------+----------+
Copy

V-04 viewed the price three times and V-02 twice, so price_shown has 10 events but only 7 visitors. V-08's purchase was logged twice, which turns 4 buyers into 5 purchase events. COUNT(DISTINCT visitor_id) counts each visitor once, and FIELD in the ORDER BY lists the steps in funnel order instead of alphabetical order. Event counts show repeat views, but they cannot say how many people moved forward.

Step Counts in One Row

A funnel report is easier to read when every step sits in one row. A one-row step count gives each step its own column, a COUNT(DISTINCT ...) over a CASE expression that keeps only that step's visitors. This is the conditional aggregation from Retention and Cohort Tables, with COUNT(DISTINCT ...) in place of SUM.

SELECT COUNT(DISTINCT CASE WHEN step = 'start' THEN visitor_id END) AS started,
       COUNT(DISTINCT CASE WHEN step = 'details' THEN visitor_id END) AS details,
       COUNT(DISTINCT CASE WHEN step = 'price_shown' THEN visitor_id END) AS priced,
       COUNT(DISTINCT CASE WHEN step = 'purchase' THEN visitor_id END) AS purchased
FROM quote_events;
Copy

Output:

+---------+---------+--------+-----------+
| started | details | priced | purchased |
+---------+---------+--------+-----------+
|       8 |       7 |      7 |         4 |
+---------+---------+--------+-----------+
Copy

Each CASE has no ELSE part, so for a row of another step it returns NULL, and the MySQL manual states that COUNT(DISTINCT ...) counts only "different non-NULL" values. Each column therefore counts only its own step's visitors. MySQL has no FILTER clause on aggregates, so CASE is the portable form. PostgreSQL also accepts COUNT(DISTINCT visitor_id) FILTER (WHERE step = 'start'), where only the matching rows "are fed to the aggregate function".

The row reads 8, 7, 7, and 4, and two of those counts are wrong for a funnel. V-09 counts as priced without ever starting a quote, and V-07 counts at details although its details event came before its start. The query counts visitors who ever opened each page, not visitors who got this far in order.

Ordered Funnels

An ordered funnel counts a visitor at a step only when the visitor reached every earlier step first, in sequence. To compare times across days, the query turns each event into one number of minutes, day_no * 1440 + event_min, the same absolute minute used in Sessionizing Events. It then takes each visitor's first time at each step and finds the deepest step reached in order:

WITH firsts AS (
  SELECT visitor_id,
         MIN(CASE WHEN step = 'start' THEN day_no * 1440 + event_min END) AS t_start,
         MIN(CASE WHEN step = 'details' THEN day_no * 1440 + event_min END) AS t_details,
         MIN(CASE WHEN step = 'price_shown' THEN day_no * 1440 + event_min END) AS t_price,
         MIN(CASE WHEN step = 'purchase' THEN day_no * 1440 + event_min END) AS t_purchase
  FROM quote_events
  GROUP BY visitor_id
)
SELECT visitor_id, t_start, t_details, t_price, t_purchase,
       CASE
         WHEN t_details >= t_start AND t_price >= t_details AND t_purchase >= t_price THEN 4
         WHEN t_details >= t_start AND t_price >= t_details THEN 3
         WHEN t_details >= t_start THEN 2
         ELSE 1
       END AS deepest_step
FROM firsts
WHERE t_start IS NOT NULL
ORDER BY visitor_id;
Copy

Output:

+------------+---------+-----------+---------+------------+--------------+
| visitor_id | t_start | t_details | t_price | t_purchase | deepest_step |
+------------+---------+-----------+---------+------------+--------------+
| V-01       |    2140 |      2146 |    2153 |       2161 |            4 |
| V-02       |    2200 |      2211 |    2218 |       NULL |            3 |
| V-03       |    2260 |      2266 |    2274 |       6970 |            4 |
| V-04       |    3360 |      3368 |    3375 |      17880 |            4 |
| V-05       |    3780 |      3792 |    NULL |       NULL |            2 |
| V-06       |    4930 |      NULL |    NULL |       NULL |            1 |
| V-07       |    4840 |      4820 |    4851 |       NULL |            1 |
| V-08       |    6580 |      6587 |    6595 |       6606 |            4 |
+------------+---------+-----------+---------+------------+--------------+
Copy

For V-01, day 1 at minute 700 becomes 1 * 1440 + 700 = 2140, and its first times rise from 2140 to 2161, so it reaches step 4. V-07's first details event, at 4820, comes before its start at 4840, so V-07 stays at step 1 even though a price was shown later. V-09 has no start and is removed by WHERE t_start IS NOT NULL.

The CASE tests the deepest step first, and each WHEN repeats every earlier comparison, so a visitor who skips a step stops before the gap. With >=, two steps logged in the same minute still count as in order. Counting the deepest_step values gives the ordered funnel: 8 visitors started, 6 reached details, 5 saw a price, and 4 bought. Each count is now at most the count before it, which the unordered row did not guarantee.

• Cost of first times: a later repeat is ignored. A visitor who opened details before starting and again after starting stays at step 1. A join that finds the first details event after the start fixes this, at the cost of one more join per step.
• When the unordered count is better. When the question is page reach, such as how many visitors ever saw a price, order does not matter, and the simpler query answers it.

Time-Bounded Funnels

A time-bounded funnel counts a step only when it happens within a fixed time after the visitor's first step. Without a limit, V-04 counts as a buyer although it bought on day 12, ten days after starting. This section asks who bought within 7 days of starting a quote, and seven days are 7 * 1440 = 10,080 minutes.

The next query adds the limit to each WHEN and builds the funnel with conversion figures:

WITH firsts AS (
  SELECT visitor_id,
         MIN(CASE WHEN step = 'start' THEN day_no * 1440 + event_min END) AS t_start,
         MIN(CASE WHEN step = 'details' THEN day_no * 1440 + event_min END) AS t_details,
         MIN(CASE WHEN step = 'price_shown' THEN day_no * 1440 + event_min END) AS t_price,
         MIN(CASE WHEN step = 'purchase' THEN day_no * 1440 + event_min END) AS t_purchase
  FROM quote_events
  GROUP BY visitor_id
),
deepest AS (
  SELECT visitor_id,
         CASE
           WHEN t_details >= t_start AND t_price >= t_details AND t_purchase >= t_price
                AND t_purchase - t_start <= 10080 THEN 4
           WHEN t_details >= t_start AND t_price >= t_details
                AND t_price - t_start <= 10080 THEN 3
           WHEN t_details >= t_start
                AND t_details - t_start <= 10080 THEN 2
           ELSE 1
         END AS deepest_step
  FROM firsts
  WHERE t_start IS NOT NULL
),
counts AS (
  SELECT s.step_no, s.step, COUNT(d.visitor_id) AS visitors
  FROM (SELECT 1 AS step_no, 'start' AS step
        UNION ALL SELECT 2, 'details'
        UNION ALL SELECT 3, 'price_shown'
        UNION ALL SELECT 4, 'purchase') AS s
  LEFT JOIN deepest AS d ON d.deepest_step >= s.step_no
  GROUP BY s.step_no, s.step
)
SELECT step_no, step, visitors,
       CAST(ROUND(100 * visitors / LAG(visitors) OVER (ORDER BY step_no)) AS SIGNED) AS step_pct,
       CAST(ROUND(100 * visitors / FIRST_VALUE(visitors) OVER (ORDER BY step_no)) AS SIGNED) AS overall_pct
FROM counts
ORDER BY step_no;
Copy

Output:

+---------+-------------+----------+----------+-------------+
| step_no | step        | visitors | step_pct | overall_pct |
+---------+-------------+----------+----------+-------------+
|       1 | start       |        8 |     NULL |         100 |
|       2 | details     |        6 |       75 |          75 |
|       3 | price_shown |        5 |       83 |          63 |
|       4 | purchase    |        3 |       60 |          38 |
+---------+-------------+----------+----------+-------------+
Copy

V-04 bought 17880 - 3360 = 14,520 minutes after its start, which is more than 10,080, so it now stops at step 3 and purchases fall from 4 to 3. V-03 bought 6970 - 2260 = 4,710 minutes after its start, inside the limit. Testing only the last step of each WHEN against the start is enough, because the order checks already place every earlier step between the start and that step.

The counts CTE joins a four-row list of steps to the per-visitor result, so a visitor at step 4 is counted at steps 1 to 4. The LEFT JOIN keeps a step that no visitor reached, with a count of 0.

• Cost of a limit: recent visitors look worse. A visitor who started yesterday has had one day of a seven-day limit. Leave out visitors whose start is less than 10,080 minutes before the end of the data, as with cohorts that are too young to measure.

Step and Overall Conversion

Step conversion is the share of visitors at one step who also reach the next step, and overall conversion is the share of starting visitors who reach a step. In the output, step_pct divides by the previous count, read with LAG (defined in Gaps and Islands), and overall_pct divides by the first count, read with FIRST_VALUE (covered in Window Frames and Running Totals).

Details converts 6 of 8 visitors, or 75 percent. The price step converts 5 of 6, or 83 percent, and purchase converts 3 of 5, or 60 percent. Overall, 3 of 8 starting visitors bought, which is 37.5 percent, and ROUND turns it into 38. Step 1 has no earlier step, so its step_pct is NULL.

• Step conversion finds the weak step. Here it is purchase, at 60 percent: the largest share of visitors stop between the price and the purchase.
• Overall conversion compares funnels. Two channels or two months are compared on overall conversion.
• Cost of percentages on small counts. One visitor more or fewer at the price step moves its 83 percent to 100 or 67 percent, so report the counts as well.

Practice: Quote Funnel by Channel

A second table, visitors, gives each visitor's acquisition channel (for example search, partner, or email). For each channel, return channel, started (visitors with at least one start event), priced (visitors who reached price_shown in order), purchased (visitors who reached purchase in order), and purchase_pct, which is CAST(ROUND(100 * purchased / started) AS SIGNED).

A visitor reaches a step in order when two conditions hold. First, the first event of each step up to it is at or after the first event of the step before it, with time measured as day_no * 1440 + event_min. Second, that step's first event is at most 10,080 minutes after the visitor's first start.

Leave out visitors with no start event, so a channel with no started visitor does not appear. Sort the result by channel.

Using This in an Interview

Before writing SQL, state the funnel definition as three choices: the steps and their order, whether order is required, and the time limit. Then name the unit: "I count distinct visitors, not events." V-07 and V-04 show how the order rule and the time limit change the answer.

Ask which database the screen runs on before choosing a syntax. CASE inside an aggregate runs in both MySQL and PostgreSQL, while FILTER returns a syntax error in MySQL. Two follow-up questions are worth preparing for:

• "Where do visitors leave the funnel?" Compare the step conversions and name the lowest one. Then split that step by a column such as channel to see whether one group causes the loss.
• "A visitor starts two quotes a month apart. Which start counts?" The query uses the first start, so the second attempt's purchase falls outside the 7-day limit. To count each attempt, split the events into attempts first, as sessions are split, and run the funnel per attempt.

Key Takeaways

• Count distinct visitors at each step: repeats and duplicate logs add events but not visitors (10 price events from 7 visitors here).
• Conditional aggregation, a CASE with no ELSE inside COUNT(DISTINCT ...), places every step's count in one row.
• An ordered funnel compares each visitor's first time at each step and counts the deepest step reached in sequence, so the counts never rise from one step to the next.
• A time-bounded funnel measures each step from the first start and leaves out visitors who started less than one limit before the data ends.
• Step conversion divides by the step before and finds the weak step, while overall conversion divides by the first step and compares funnels.

On a large event table, the cost of a funnel query depends on how the database reads and groups the events. The next lesson, Reading Query Plans, shows how to read a MySQL plan and which index removes a full table scan or a sort.

Practice Questions

1. A dashboard counts 12 purchase events on one day and reports 12 buyers. What can make that number wrong, and how is it fixed?

<details> <summary>Show answer</summary>

The query counts events, not buyers. A duplicate log entry, like V-08's purchase logged twice, or a visitor who bought two policies adds events without adding buyers. COUNT(DISTINCT visitor_id) counts each buyer once. The event count can still be reported, but under its own name.

</details>

2. A visitor's first times are start 5000, details 5004, price_shown 4990, and purchase 5010. What is the deepest step reached in order?

<details> <summary>Show answer</summary>

Step 2, details. Details at 5004 is after the start at 5000, so step 2 passes. The first price at 4990 is before details, so step 3 fails. Step 4 requires every earlier comparison, so the purchase at 5010 does not count, even though it is after the start.

</details>

3. A funnel has 1,000 started, 600 details, 450 price_shown, and 90 purchase. What are the step and overall conversion for purchase, and which step is weakest?

<details> <summary>Show answer</summary>

Purchase converts 20 percent from price_shown and 9 percent overall, and it is the weakest step. Step conversion is 90 / 450 = 20 percent, and overall conversion is 90 / 1,000 = 9 percent. The other steps convert 600 / 1,000 = 60 percent and 450 / 600 = 75 percent, so purchase loses the largest share.

</details>

4. Why does the time-bounded query compare only the last step of each WHEN with the start, and not every step?

<details> <summary>Show answer</summary>

The order checks already place every earlier step between the start and the last step. If the purchase is at most 10,080 minutes after the start, and details and price_shown are at or after the start and at or before the purchase, they are also inside the limit. Checking them again would not change any result.

</details>

5. A 7-day funnel runs on data that ends on day 30. Visitors who started on day 28 show a much lower purchase conversion than older visitors. Why, and what is the fix?

<details> <summary>Show answer</summary>

They have had at most 3 of their 7 days to buy. Their purchases on days 31 to 35 are not in the data yet, so the funnel counts them as visitors who did not buy. Leave out visitors whose start is less than 10,080 minutes before the end of the data, or report them separately as incomplete.

</details>