A funnel query answers one question: of the users who started, how many reached each next step, and where did the rest leave? The SQL is short, but a few details decide whether the numbers are right. Those details are step ordering, time windows, and counting distinct users instead of events. This guide builds a production-ready funnel from raw event logs, adds drop-off metrics with window functions, and extends it to segments and time-to-convert.
Quick Takeaways
- Count distinct users per step, not events. One user firing
add_to_cartfive times is one conversion. - Enforce step order and a conversion window by joining each step to the previous one on
event_ts > prev_ts. - Use
LAG()andFIRST_VALUE()to compute step-to-step conversion and overall conversion in a single pass. - Segment by one dimension at a time (device, channel, cohort) to locate the cause of a drop-off, not just its size.
Funnel Approaches at a Glance
| Approach | Enforces Order | Complexity | Best For | Main Risk |
|---|---|---|---|---|
Conditional aggregation (MIN(CASE WHEN...)) |
Weak | Low | Quick exploration | Counts users who did steps out of order |
| Chained CTE joins | Strict | Medium | Production dashboards | Slower on very large event tables |
MATCH_RECOGNIZE / windowFunnel |
Strict | Low (if supported) | Snowflake, Oracle, Trino, ClickHouse | Not portable across warehouses |
| Sessionized funnel | Strict, per session | High | Web and app analytics | Session definition drives results |
Data Model: The Event Table
Funnel analysis needs an event log with three fields at minimum. Everything below assumes this table:
-- One row per user action
CREATE TABLE events (
user_id BIGINT NOT NULL,
event_name VARCHAR(64) NOT NULL, -- 'view_product', 'add_to_cart', ...
event_ts TIMESTAMP NOT NULL,
device VARCHAR(16), -- 'mobile', 'desktop' (used for segmentation)
channel VARCHAR(32) -- 'paid', 'organic', 'email'
);
-- Speeds up per-user, per-event lookups in the chained joins
CREATE INDEX idx_events_user_event_ts ON events (user_id, event_name, event_ts);
The example funnel has four steps:
view_productadd_to_cartbegin_checkoutpurchase
Queries use PostgreSQL syntax. Interval and date functions differ slightly in BigQuery, Snowflake, and Redshift, so adjust those lines for your engine.
Step 1: Define the Funnel Before You Write SQL
Three decisions change your results more than any query optimization.
| Decision | Options | Effect on Results |
|---|---|---|
| Step order | Strict sequence vs. any order | Strict order lowers conversion but reflects real behavior |
| Conversion window | 1 hour, 24 hours, 7 days, 30 days | Longer windows raise conversion and delay reporting |
| Counting unit | Distinct users vs. sessions vs. events | Sessions suit short-cycle flows; users suit long-cycle products |
Write these down first. A funnel with a 7-day window and strict order is a different metric from one with a 30-day window and any order. Teams that skip this step argue about numbers later.
Step 2: The Naive Funnel (and Why It Misleads)
Conditional aggregation is the fastest way to get a rough funnel:
-- Quick look: users who ever performed each event in the date range
SELECT
COUNT(DISTINCT CASE WHEN event_name = 'view_product' THEN user_id END) AS viewed,
COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END) AS carted,
COUNT(DISTINCT CASE WHEN event_name = 'begin_checkout' THEN user_id END) AS checkout,
COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS purchased
FROM events
WHERE event_ts >= DATE '2026-10-01'
AND event_ts < DATE '2026-11-01';
This query counts a user as “purchased” even if the purchase happened before they viewed anything in the window. It also ignores time limits. Use it for a sanity check, not for reporting.
Step 3: The Strict-Order Funnel with Chained CTEs
Each CTE keeps only users who completed the previous step, then looks for the next event strictly after it and inside the window.
WITH step1_view AS (
-- Entry cohort: first product view per user in the reporting period
SELECT user_id, MIN(event_ts) AS ts
FROM events
WHERE event_name = 'view_product'
AND event_ts >= DATE '2026-10-01'
AND event_ts < DATE '2026-11-01'
GROUP BY user_id
),
step2_cart AS (
-- First add_to_cart AFTER the view, within 7 days
SELECT s.user_id, MIN(e.event_ts) AS ts
FROM step1_view s
JOIN events e
ON e.user_id = s.user_id
AND e.event_name = 'add_to_cart'
AND e.event_ts > s.ts -- strict ordering
AND e.event_ts <= s.ts + INTERVAL '7 days' -- conversion window
GROUP BY s.user_id
),
step3_checkout AS (
SELECT s.user_id, MIN(e.event_ts) AS ts
FROM step2_cart s
JOIN events e
ON e.user_id = s.user_id
AND e.event_name = 'begin_checkout'
AND e.event_ts > s.ts
AND e.event_ts <= s.ts + INTERVAL '7 days'
GROUP BY s.user_id
),
step4_purchase AS (
SELECT s.user_id, MIN(e.event_ts) AS ts
FROM step3_checkout s
JOIN events e
ON e.user_id = s.user_id
AND e.event_name = 'purchase'
AND e.event_ts > s.ts
AND e.event_ts <= s.ts + INTERVAL '7 days'
GROUP BY s.user_id
),
funnel AS (
SELECT 1 AS step_no, 'view_product' AS step_name, COUNT(*) AS users FROM step1_view
UNION ALL
SELECT 2, 'add_to_cart', COUNT(*) FROM step2_cart
UNION ALL
SELECT 3, 'begin_checkout', COUNT(*) FROM step3_checkout
UNION ALL
SELECT 4, 'purchase', COUNT(*) FROM step4_purchase
)
SELECT * FROM funnel ORDER BY step_no;
Three details matter here. MIN(e.event_ts) takes the earliest qualifying event, which keeps later steps anchored to the first valid progression. Each join uses > rather than >=, so two events with the same timestamp do not count as sequential. And every step reads only from the previous step’s users, so the counts can only shrink.
Step 4: Add Drop-Off Metrics with Window Functions
Replace the final SELECT with this to get the rates people actually read:
SELECT
step_no,
step_name,
users,
-- Share of the entry cohort still present at this step
ROUND(100.0 * users / FIRST_VALUE(users) OVER (ORDER BY step_no), 2) AS pct_of_top,
-- Step-to-step conversion
ROUND(100.0 * users / NULLIF(LAG(users) OVER (ORDER BY step_no), 0), 2) AS pct_of_prev,
-- Absolute users lost since the previous step
LAG(users) OVER (ORDER BY step_no) - users AS users_dropped
FROM funnel
ORDER BY step_no;
Example output:
| step_no | step_name | users | pct_of_top | pct_of_prev | users_dropped |
|---|---|---|---|---|---|
| 1 | view_product | 120,000 | 100.00 | NULL | NULL |
| 2 | add_to_cart | 31,200 | 26.00 | 26.00 | 88,800 |
| 3 | begin_checkout | 14,040 | 11.70 | 45.00 | 17,160 |
| 4 | purchase | 8,424 | 7.02 | 60.00 | 5,616 |
Read the two rate columns together. pct_of_prev shows where the funnel is weakest per step. users_dropped shows where the biggest absolute loss sits. In this example the cart step has the lowest step rate and the largest raw loss, so it is the first place to investigate.
Step 5: Segment the Funnel to Find the Cause
An aggregate funnel says where users leave. A segmented funnel suggests why. Carry the segment column from step 1 through every CTE, then group by it.
WITH step1_view AS (
SELECT user_id, MIN(event_ts) AS ts,
MIN(device) AS device -- one segment value per user
FROM events
WHERE event_name = 'view_product'
AND event_ts >= DATE '2026-10-01'
AND event_ts < DATE '2026-11-01'
GROUP BY user_id
),
step2_cart AS (
SELECT s.user_id, s.device, MIN(e.event_ts) AS ts
FROM step1_view s
JOIN events e
ON e.user_id = s.user_id AND e.event_name = 'add_to_cart'
AND e.event_ts > s.ts AND e.event_ts <= s.ts + INTERVAL '7 days'
GROUP BY s.user_id, s.device
),
step3_checkout AS (
SELECT s.user_id, s.device, MIN(e.event_ts) AS ts
FROM step2_cart s
JOIN events e
ON e.user_id = s.user_id AND e.event_name = 'begin_checkout'
AND e.event_ts > s.ts AND e.event_ts <= s.ts + INTERVAL '7 days'
GROUP BY s.user_id, s.device
)
SELECT
v.device,
COUNT(DISTINCT v.user_id) AS viewed,
COUNT(DISTINCT c.user_id) AS carted,
COUNT(DISTINCT k.user_id) AS checkout,
ROUND(100.0 * COUNT(DISTINCT c.user_id) / COUNT(DISTINCT v.user_id), 2) AS view_to_cart_pct,
ROUND(100.0 * COUNT(DISTINCT k.user_id) / NULLIF(COUNT(DISTINCT c.user_id), 0), 2) AS cart_to_checkout_pct
FROM step1_view v
LEFT JOIN step2_cart c ON c.user_id = v.user_id
LEFT JOIN step3_checkout k ON k.user_id = v.user_id
GROUP BY v.device
ORDER BY viewed DESC;
Use LEFT JOIN here so users who dropped keep their row. Segment on attributes fixed at the entry step. Segmenting on an attribute that changes mid-funnel, such as a user switching devices, splits one journey into two.
| Segment Dimension | What It Reveals | Typical Finding |
|---|---|---|
| Device | UI or performance issues | Mobile checkout converts far below desktop |
| Acquisition channel | Traffic quality | Paid traffic views but rarely adds to cart |
| Signup cohort (week) | Product or onboarding changes | A release week shows a sudden step drop |
| Geography | Payment or shipping friction | One region fails at payment step |
Step 6: Measure Time-to-Convert
Conversion rate hides speed. Add elapsed time between steps to see whether users stall before they leave.
SELECT
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY c.ts - v.ts) AS median_view_to_cart,
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY c.ts - v.ts) AS p90_view_to_cart
FROM step1_view v
JOIN step2_cart c USING (user_id);
A long p90 with a healthy median means a minority of users hesitate. That pattern usually points to a pricing, trust, or shipping-cost question. Retargeting emails fit this group well.
Step 7: Run the Funnel from Python
Pull the result into pandas to chart it or schedule it.
import pandas as pd
import plotly.express as px
from sqlalchemy import create_engine, text
engine = create_engine("postgresql+psycopg2://user:password@host:5432/analytics")
# Load the funnel query saved as a .sql file
with open("funnel.sql") as f:
query = text(f.read())
funnel_df = pd.read_sql(query, engine)
# Recompute rates in pandas as a cross-check against the SQL output
funnel_df["pct_of_top"] = (funnel_df["users"] / funnel_df["users"].iloc[0] * 100).round(2)
funnel_df["pct_of_prev"] = (funnel_df["users"] / funnel_df["users"].shift(1) * 100).round(2)
# Native funnel chart
fig = px.funnel(funnel_df, x="users", y="step_name", title="Checkout Funnel, Oct 2026")
fig.show()
Recomputing the rates in pandas gives you a free validation step. If the SQL and pandas values disagree, a join is duplicating rows.
Real-World Use Cases
Ecommerce checkout leakage. The funnel above is the classic case. Segment the add_to_cart → begin_checkout step by device and shipping region. A cluster of drop-offs on one platform usually traces to a rendering bug or a missing payment method.
SaaS onboarding activation. Define steps as signup, connect_data_source, create_first_report, invite_teammate. Use a 14-day window and a weekly signup cohort segment. If connect_data_source falls after a release, the integration flow regressed.
Mobile app first-session flow. Use a session-based funnel with a 30-minute inactivity rule. Partition by session_id instead of user_id and shorten the window to the session length. Compare first-session and returning-session funnels to see which screens new users abandon.
Lead generation. Track landing_view, form_start, form_submit, email_verified. A large gap between form_start and form_submit points to form length or field validation errors. A gap between form_submit and email_verified points to email deliverability.
Common Mistakes That Corrupt Funnel Numbers
| Mistake | Symptom | Fix |
|---|---|---|
| Counting events instead of users | Step counts exceed the entry count | Use COUNT(DISTINCT user_id) |
| No ordering condition | Conversion looks unrealistically high | Add e.event_ts > prev.ts to each join |
| No conversion window | Old users inflate later steps | Cap with + INTERVAL 'N days' |
| Joining on all events, then aggregating | Duplicate rows, slow queries | Aggregate with MIN(event_ts) per step first |
| Mixed time zones | Day boundaries shift between steps | Cast all timestamps to UTC before filtering |
| Bots and internal traffic | Inflated top of funnel | Filter on a bot flag or internal user list |
Performance Tips for Large Event Tables
- Filter early. Apply the date range in the first CTE, and bound later joins with the window so the engine prunes partitions.
- Partition and cluster the events table by date and by
user_idwhere your warehouse supports it. - Pre-aggregate. Materialize a daily
user_id, event_name, first_tstable. Funnels then scan a table a fraction of the size of the raw log. - Select only needed columns in each CTE. Carrying wide rows through four joins costs memory.
- Check native tools. Snowflake and Trino support
MATCH_RECOGNIZEfor ordered pattern matching, and ClickHouse offerswindowFunnel. They express the same logic in fewer lines.
FAQ
How do you calculate funnel drop-off rate in SQL?
Compute step-to-step conversion with LAG(), then subtract from 100. The formula is 100 - 100.0 * users / LAG(users) OVER (ORDER BY step_no). Drop-off at a step equals the users who reached the previous step minus the users who reached this one.
How do you enforce step order in a SQL funnel?
Join each step to the previous step’s timestamp and require e.event_ts > prev.ts. Take MIN(e.event_ts) per user so each step anchors to the earliest valid event. This stops a purchase that occurred before the cart event from counting as a conversion.
What is a good conversion window for funnel analysis?
Match the window to the buying cycle. Use minutes or hours for in-session flows, 1 to 7 days for ecommerce, and 14 to 30 days for SaaS onboarding or B2B lead funnels. Report the window next to every funnel number so comparisons stay fair.
Should funnel analysis count users or sessions?
Count users for multi-day journeys and sessions for single-visit flows such as checkout or signup forms. Sessions isolate the experience of one visit. Users capture total behavior but blend several visits into one path.



