A funnel tracks how users move through a sequence of steps — visit a page, click an item, add to cart, begin checkout, complete purchase. At every step some users drop off. The goal of funnel analysis in SQL is to measure exactly how many users reach each step, the conversion rate between steps, and where the biggest drop-offs happen.
This tutorial builds a complete funnel analysis pipeline, handling ordered funnels, time-bounded funnels, and multi-path variants.
The Dataset
CREATE TABLE user_events (
event_id BIGINT,
user_id INT,
session_id VARCHAR(36),
event_type VARCHAR(30),
product_id INT,
event_ts TIMESTAMP
);
INSERT INTO user_events VALUES
-- User 101: completes full funnel
(1, 101, 'sess-a1', 'page_view', 10, '2024-03-01 09:00:00'),
(2, 101, 'sess-a1', 'product_view', 10, '2024-03-01 09:02:00'),
(3, 101, 'sess-a1', 'add_to_cart', 10, '2024-03-01 09:05:00'),
(4, 101, 'sess-a1', 'begin_checkout', 10, '2024-03-01 09:07:00'),
(5, 101, 'sess-a1', 'purchase', 10, '2024-03-01 09:10:00'),
-- User 102: drops off at add_to_cart
(6, 102, 'sess-b1', 'page_view', 10, '2024-03-01 10:00:00'),
(7, 102, 'sess-b1', 'product_view', 10, '2024-03-01 10:03:00'),
(8, 102, 'sess-b1', 'add_to_cart', 10, '2024-03-01 10:06:00'),
-- User 103: drops off at product_view
(9, 103, 'sess-c1', 'page_view', 11, '2024-03-01 11:00:00'),
(10, 103, 'sess-c1', 'product_view', 11, '2024-03-01 11:04:00'),
-- User 104: completes full funnel
(11, 104, 'sess-d1', 'page_view', 10, '2024-03-01 14:00:00'),
(12, 104, 'sess-d1', 'product_view', 10, '2024-03-01 14:03:00'),
(13, 104, 'sess-d1', 'add_to_cart', 10, '2024-03-01 14:08:00'),
(14, 104, 'sess-d1', 'begin_checkout', 10, '2024-03-01 14:10:00'),
(15, 104, 'sess-d1', 'purchase', 10, '2024-03-01 14:13:00'),
-- User 105: page_view only
(16, 105, 'sess-e1', 'page_view', 12, '2024-03-01 15:00:00'),
-- User 106: reaches checkout but does not purchase
(17, 106, 'sess-f1', 'page_view', 10, '2024-03-01 16:00:00'),
(18, 106, 'sess-f1', 'product_view', 10, '2024-03-01 16:02:00'),
(19, 106, 'sess-f1', 'add_to_cart', 10, '2024-03-01 16:05:00'),
(20, 106, 'sess-f1', 'begin_checkout', 10, '2024-03-01 16:08:00');
Method 1 — Simple Funnel With COUNT DISTINCT
The most direct approach: count unique users who performed each event type, regardless of order.
SELECT
'page_view' AS funnel_step, 1 AS step_order,
COUNT(DISTINCT user_id) AS users
FROM user_events WHERE event_type = 'page_view'
UNION ALL
SELECT 'product_view', 2, COUNT(DISTINCT user_id)
FROM user_events WHERE event_type = 'product_view'
UNION ALL
SELECT 'add_to_cart', 3, COUNT(DISTINCT user_id)
FROM user_events WHERE event_type = 'add_to_cart'
UNION ALL
SELECT 'begin_checkout', 4, COUNT(DISTINCT user_id)
FROM user_events WHERE event_type = 'begin_checkout'
UNION ALL
SELECT 'purchase', 5, COUNT(DISTINCT user_id)
FROM user_events WHERE event_type = 'purchase'
ORDER BY step_order;
Output:
| funnel_step | step_order | users |
|---|---|---|
| page_view | 1 | 6 |
| product_view | 2 | 5 |
| add_to_cart | 3 | 4 |
| begin_checkout | 4 | 3 |
| purchase | 5 | 2 |
Limitation: This counts any user who ever performed each event — even if they did it in a different session, out of order, or weeks apart. For a strict sequential funnel, use Method 2.
Method 2 — Strict Sequential Funnel (Users Must Complete Steps in Order)
This is the correct approach for purchase funnels where step order matters. Each user must have performed step N before step N+1 — and within the same session.
WITH funnel AS (
SELECT
user_id,
session_id,
MAX(CASE WHEN event_type = 'page_view' THEN 1 ELSE 0 END) AS reached_step1,
MAX(CASE WHEN event_type = 'product_view' THEN 1 ELSE 0 END) AS reached_step2,
MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS reached_step3,
MAX(CASE WHEN event_type = 'begin_checkout' THEN 1 ELSE 0 END) AS reached_step4,
MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS reached_step5,
-- Ensure step order: product_view happened AFTER page_view, etc.
MIN(CASE WHEN event_type = 'page_view' THEN event_ts END) AS ts_step1,
MIN(CASE WHEN event_type = 'product_view' THEN event_ts END) AS ts_step2,
MIN(CASE WHEN event_type = 'add_to_cart' THEN event_ts END) AS ts_step3,
MIN(CASE WHEN event_type = 'begin_checkout' THEN event_ts END) AS ts_step4,
MIN(CASE WHEN event_type = 'purchase' THEN event_ts END) AS ts_step5
FROM user_events
GROUP BY user_id, session_id
),
ordered_funnel AS (
SELECT
user_id,
session_id,
-- A step counts only if all previous steps also happened AND in order
CASE WHEN reached_step1 = 1 THEN 1 ELSE 0 END AS step1,
CASE WHEN reached_step1 = 1 AND reached_step2 = 1
AND ts_step2 > ts_step1 THEN 1 ELSE 0 END AS step2,
CASE WHEN reached_step1 = 1 AND reached_step2 = 1 AND reached_step3 = 1
AND ts_step2 > ts_step1 AND ts_step3 > ts_step2 THEN 1 ELSE 0 END AS step3,
CASE WHEN reached_step1 = 1 AND reached_step2 = 1
AND reached_step3 = 1 AND reached_step4 = 1
AND ts_step2 > ts_step1 AND ts_step3 > ts_step2
AND ts_step4 > ts_step3 THEN 1 ELSE 0 END AS step4,
CASE WHEN reached_step1 = 1 AND reached_step2 = 1
AND reached_step3 = 1 AND reached_step4 = 1 AND reached_step5 = 1
AND ts_step2 > ts_step1 AND ts_step3 > ts_step2
AND ts_step4 > ts_step3 AND ts_step5 > ts_step4 THEN 1 ELSE 0 END AS step5
FROM funnel
)
SELECT
'page_view' AS funnel_step, 1 AS step_order, SUM(step1) AS users FROM ordered_funnel UNION ALL
SELECT 'product_view', 2, SUM(step2) FROM ordered_funnel UNION ALL
SELECT 'add_to_cart', 3, SUM(step3) FROM ordered_funnel UNION ALL
SELECT 'begin_checkout', 4, SUM(step4) FROM ordered_funnel UNION ALL
SELECT 'purchase', 5, SUM(step5) FROM ordered_funnel
ORDER BY step_order;
Output:
| funnel_step | step_order | users |
|---|---|---|
| page_view | 1 | 6 |
| product_view | 2 | 5 |
| add_to_cart | 3 | 4 |
| begin_checkout | 4 | 3 |
| purchase | 5 | 2 |
Step 3 — Add Conversion Rates Between Steps
WITH funnel_counts AS (
SELECT 'page_view' AS step, 1 AS ord, 6 AS users UNION ALL
SELECT 'product_view', 2, 5 UNION ALL
SELECT 'add_to_cart', 3, 4 UNION ALL
SELECT 'begin_checkout', 4, 3 UNION ALL
SELECT 'purchase', 5, 2
)
SELECT
step,
users,
LAG(users) OVER (ORDER BY ord) AS prev_step_users,
ROUND(
users * 100.0 / NULLIF(LAG(users) OVER (ORDER BY ord), 0),
1
) AS step_conversion_pct,
ROUND(
users * 100.0 / NULLIF(FIRST_VALUE(users) OVER (ORDER BY ord), 0),
1
) AS overall_conversion_pct
FROM funnel_counts
ORDER BY ord;
Output:
| step | users | prev_step_users | step_conversion_pct | overall_conversion_pct |
|---|---|---|---|---|
| page_view | 6 | NULL | NULL | 100.0% |
| product_view | 5 | 6 | 83.3% | 83.3% |
| add_to_cart | 4 | 5 | 80.0% | 66.7% |
| begin_checkout | 3 | 4 | 75.0% | 50.0% |
| purchase | 2 | 3 | 66.7% | 33.3% |
Step conversion shows the drop between consecutive steps. Overall conversion shows how far users have progressed relative to the top of the funnel. Only 33.3% of people who saw the page completed a purchase.
Step 4 — Time-Bounded Funnel (Complete Within N Hours)
A user who added to cart on Monday and purchased on Friday should not count as a successful same-session conversion. Time-bounded funnels enforce a maximum window.
WITH funnel AS (
SELECT
user_id,
session_id,
MIN(CASE WHEN event_type = 'page_view' THEN event_ts END) AS ts1,
MIN(CASE WHEN event_type = 'product_view' THEN event_ts END) AS ts2,
MIN(CASE WHEN event_type = 'add_to_cart' THEN event_ts END) AS ts3,
MIN(CASE WHEN event_type = 'begin_checkout' THEN event_ts END) AS ts4,
MIN(CASE WHEN event_type = 'purchase' THEN event_ts END) AS ts5
FROM user_events
GROUP BY user_id, session_id
)
SELECT
user_id,
session_id,
CASE WHEN ts5 IS NOT NULL
AND ts5 - ts1 <= INTERVAL '2 hours' -- full funnel within 2 hours
THEN 'converted'
WHEN ts4 IS NOT NULL THEN 'abandoned_at_checkout'
WHEN ts3 IS NOT NULL THEN 'abandoned_at_cart'
WHEN ts2 IS NOT NULL THEN 'abandoned_at_product'
ELSE 'bounced'
END AS funnel_outcome,
EXTRACT(EPOCH FROM (ts5 - ts1)) / 60 AS time_to_purchase_minutes
FROM funnel
WHERE ts1 IS NOT NULL;
Output:
| user_id | session_id | funnel_outcome | time_to_purchase_minutes |
|---|---|---|---|
| 101 | sess-a1 | converted | 10.0 |
| 102 | sess-b1 | abandoned_at_cart | NULL |
| 103 | sess-c1 | abandoned_at_product | NULL |
| 104 | sess-d1 | converted | 13.0 |
| 105 | sess-e1 | bounced | NULL |
| 106 | sess-f1 | abandoned_at_checkout | NULL |
Each user is now classified by their funnel outcome. This classification table feeds directly into downstream analytics: “what percentage of users abandoned at checkout?” and “what is the average time-to-purchase for converters?”
Step 5 — Funnel by Segment (Device, Country, Product)
Breaking down funnel performance by segment reveals which user groups convert best.
-- Extend user_events with a device column for demo
WITH device_funnel AS (
SELECT
CASE WHEN user_id IN (101, 103, 105) THEN 'mobile' ELSE 'desktop' END AS device,
SUM(CASE WHEN event_type = 'page_view' THEN 1 ELSE 0 END) AS views,
SUM(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS carts,
SUM(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS purchases
FROM user_events
GROUP BY CASE WHEN user_id IN (101, 103, 105) THEN 'mobile' ELSE 'desktop' END
)
SELECT
device,
views,
carts,
purchases,
ROUND(carts * 100.0 / NULLIF(views, 0), 1) AS view_to_cart_rate,
ROUND(purchases * 100.0 / NULLIF(views, 0), 1) AS overall_conversion
FROM device_funnel
ORDER BY overall_conversion DESC;
Output:
| device | views | carts | purchases | view_to_cart_rate | overall_conversion |
|---|---|---|---|---|---|
| desktop | 3 | 3 | 2 | 100.0% | 66.7% |
| mobile | 3 | 1 | 0 | 33.3% | 0.0% |
Desktop users convert at 66.7%. Mobile users view the page but barely add to cart and never complete a purchase. This is a classic finding in e-commerce — mobile UX for checkout is a critical improvement area.
Common Mistakes
Mistake 1 — Not scoping funnel to a session
If a user visited in January and purchased in March, a session-agnostic funnel would count them as a converted user in both months. Scope your funnel to sessions for accurate single-visit conversion measurement.
Mistake 2 — Double-counting users who trigger a step multiple times
A user who viewed the product page 5 times should count as 1 at the product_view step. Always use COUNT(DISTINCT user_id) or MAX(CASE WHEN ...) to collapse multiple events into one flag per user per step.
Mistake 3 — Ignoring time ordering between steps
A user who purchased first and then viewed the product should not count as a converted funnel user. Always validate that step timestamps increase monotonically using the ts_step2 > ts_step1 pattern.
What to Learn Next
Funnels show where users drop off. Once you know users are dropping off at checkout, the next question is “which users are most likely to return and complete the purchase?” — that is the domain of the next tutorial on customer segmentation with SQL.