Funnel Analysis in SQL: Find Where Users Drop Off

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_cart five 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() and FIRST_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:

  1. view_product
  2. add_to_cart
  3. begin_checkout
  4. purchase

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_id where your warehouse supports it.
  • Pre-aggregate. Materialize a daily user_id, event_name, first_ts table. 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_RECOGNIZE for ordered pattern matching, and ClickHouse offers windowFunnel. 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.

Hot this week

Cohort and Retention Analysis in SQL

Learn cohort and retention analysis in SQL with copy-paste PostgreSQL queries, retention matrices, and Python heatmaps. Build yours today.

Polars for Pandas Users: A Practical Migration Guide

Migrate from Pandas to Polars with side-by-side code, expressions, and lazy evaluation. Follow this guide and speed up your pipelines today.

SQL for Time Series: Date Functions, Rolling Averages, and Gaps

Master SQL time series analysis: date functions, rolling averages, and gap filling with copy-ready PostgreSQL code. Start querying smarter today.

Build a RAG App From Scratch: Documents, Embeddings, and Answers

Build a RAG app from scratch in Python: chunk documents, create embeddings, retrieve context, and generate cited answers. Copy the full code now.

SHAP vs LIME: Explain Any Model’s Predictions

Compare SHAP vs LIME for explaining ML predictions. See code, speed tradeoffs, and a decision guide. Pick the right explainer for your model today.

Topics

Cohort and Retention Analysis in SQL

Learn cohort and retention analysis in SQL with copy-paste PostgreSQL queries, retention matrices, and Python heatmaps. Build yours today.

Polars for Pandas Users: A Practical Migration Guide

Migrate from Pandas to Polars with side-by-side code, expressions, and lazy evaluation. Follow this guide and speed up your pipelines today.

SQL for Time Series: Date Functions, Rolling Averages, and Gaps

Master SQL time series analysis: date functions, rolling averages, and gap filling with copy-ready PostgreSQL code. Start querying smarter today.

Build a RAG App From Scratch: Documents, Embeddings, and Answers

Build a RAG app from scratch in Python: chunk documents, create embeddings, retrieve context, and generate cited answers. Copy the full code now.

SHAP vs LIME: Explain Any Model’s Predictions

Compare SHAP vs LIME for explaining ML predictions. See code, speed tradeoffs, and a decision guide. Pick the right explainer for your model today.

Predict House Prices With Linear Regression: A Complete Project

Build a house price predictor with linear regression in Python. Clean data, train, and evaluate with RMSE and R². Start the full project now.

AI and Copyright: The Lawsuits, the Rulings, and What They Mean for Creators

Track every major AI copyright lawsuit in 2026: Anthropic's $1.5B deal, NYT v. OpenAI, Ross, Midjourney. See what it means for your work. Read now.

Build Your First Spam Email Classifier

Build a spam email classifier in Python with TF-IDF and Naive Bayes. Copy the code, hit 98%+ accuracy, and ship your first ML model today.

Related Articles

Popular Categories