Your product has a signup funnel. Someone visits the pricing page, starts a trial, invites a teammate, and (hopefully) subscribes. Your CEO asks a simple-sounding question in standup: "Where are we losing people?"
If your answer is a shrug and a promise to "pull some numbers," this article is for you. Funnel analysis is one of the highest-leverage things you can do with the data you already have — and you don't need a dedicated product analytics tool to do it. You need an events table and a handful of SQL patterns.
The catch is that funnels are deceptively easy to get wrong. Count the steps naively and you'll produce numbers that look precise and are quietly inflated by 30%. Let's build a funnel the right way, and see exactly which mistakes to avoid.
The data we're working with
Assume a single wide events table — the shape most product analytics setups converge on:
CREATE TABLE events (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
event_name TEXT NOT NULL, -- 'viewed_pricing', 'started_trial', ...
created_at TIMESTAMPTZ NOT NULL
);
Our funnel has four steps:
| Step | event_name |
|---|---|
| 1. Viewed pricing | viewed_pricing |
| 2. Started trial | started_trial |
| 3. Invited a teammate | invited_teammate |
| 4. Subscribed | subscribed |
The naive version (and why it lies)
The tempting first attempt: count distinct users for each event, independently.
SELECT
COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'viewed_pricing') AS viewed,
COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'started_trial') AS trial,
COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'invited_teammate') AS invited,
COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'subscribed') AS subscribed
FROM events;
This runs fast and gives you four numbers. It's also wrong for a funnel. It counts anyone who ever fired each event, in any order, at any time. A user who subscribed in January and viewed the pricing page again in June counts toward both "viewed" and "subscribed" — even though their subscribe never followed this pricing view. As Fivetran's team puts it, calculating each step independently and lumping them together mixes historical actions from different time periods into one funnel and artificially inflates conversion.
A funnel is fundamentally about order: step 3 only counts if it happened after the same user's step 2.
The right way: ordered, per-user steps
The reliable pattern is to figure out, for each user, the furthest step they reached in sequence. Window functions make this clean. First, stamp each user's first timestamp for each step:
WITH step_times AS (
SELECT
user_id,
MIN(created_at) FILTER (WHERE event_name = 'viewed_pricing') AS t1,
MIN(created_at) FILTER (WHERE event_name = 'started_trial') AS t2,
MIN(created_at) FILTER (WHERE event_name = 'invited_teammate') AS t3,
MIN(created_at) FILTER (WHERE event_name = 'subscribed') AS t4
FROM events
GROUP BY user_id
)
SELECT * FROM step_times;
Now a user "reached step 3" only if t1 <= t2 <= t3 — each step's first occurrence comes after the previous one. We translate that into a single step_reached number:
WITH step_times AS (
SELECT
user_id,
MIN(created_at) FILTER (WHERE event_name = 'viewed_pricing') AS t1,
MIN(created_at) FILTER (WHERE event_name = 'started_trial') AS t2,
MIN(created_at) FILTER (WHERE event_name = 'invited_teammate') AS t3,
MIN(created_at) FILTER (WHERE event_name = 'subscribed') AS t4
FROM events
GROUP BY user_id
),
progress AS (
SELECT
user_id,
CASE
WHEN t4 IS NOT NULL AND t4 >= t3 AND t3 >= t2 AND t2 >= t1 THEN 4
WHEN t3 IS NOT NULL AND t3 >= t2 AND t2 >= t1 THEN 3
WHEN t2 IS NOT NULL AND t2 >= t1 THEN 2
WHEN t1 IS NOT NULL THEN 1
ELSE 0
END AS step_reached
FROM step_times
)
SELECT
COUNT(*) FILTER (WHERE step_reached >= 1) AS viewed,
COUNT(*) FILTER (WHERE step_reached >= 2) AS trial,
COUNT(*) FILTER (WHERE step_reached >= 3) AS invited,
COUNT(*) FILTER (WHERE step_reached >= 4) AS subscribed
FROM progress;
Because we use >= on step_reached, each stage is a proper subset of the one before it. The funnel can only ever go down — which is what a funnel is supposed to do.
Turning counts into a conversion table
Raw counts are hard to read. What people actually want is the drop-off rate at each step. Unpivot the stages into rows and compute conversion against both the previous step and the top of funnel:
WITH counts AS (
-- the progress CTE from above, aggregated
SELECT
COUNT(*) FILTER (WHERE step_reached >= 1) AS s1,
COUNT(*) FILTER (WHERE step_reached >= 2) AS s2,
COUNT(*) FILTER (WHERE step_reached >= 3) AS s3,
COUNT(*) FILTER (WHERE step_reached >= 4) AS s4
FROM progress
),
stages AS (
SELECT 1 AS step, 'Viewed pricing' AS label, s1 AS reached, s1 AS prev FROM counts
UNION ALL SELECT 2, 'Started trial', s2, s1 FROM counts
UNION ALL SELECT 3, 'Invited teammate', s3, s2 FROM counts
UNION ALL SELECT 4, 'Subscribed', s4, s3 FROM counts
)
SELECT
label,
reached,
ROUND(100.0 * reached / MAX(reached) OVER (), 1) AS pct_of_top,
ROUND(100.0 * reached / NULLIF(prev, 0), 1) AS pct_of_prev
FROM stages
ORDER BY step;
That produces the report your CEO actually wanted:
| Stage | Users | % of top | % of previous |
|---|---|---|---|
| Viewed pricing | 10,000 | 100.0% | — |
| Started trial | 4,200 | 42.0% | 42.0% |
| Invited teammate | 1,500 | 15.0% | 35.7% |
| Subscribed | 1,050 | 10.5% | 70.0% |
Now the story is obvious. The single biggest leak isn't the final subscribe step (70% convert once they invite someone) — it's the jump from viewing pricing to starting a trial. That's where a product team should spend its energy.
Add a time window (this is the realistic version)
"Subscribed at some point after viewing pricing" is a weak definition. Someone who viewed pricing in 2024 and subscribed in 2026 didn't really move through this funnel. Real attribution almost always has a window — 30 minutes for a session, 7 days for a marketing funnel, 14 days for a trial.
You enforce it by requiring each step to fall within N days of the funnel's start (t1):
CASE
WHEN t4 >= t3 AND t3 >= t2 AND t2 >= t1
AND t4 <= t1 + INTERVAL '14 days' THEN 4
WHEN t3 >= t2 AND t2 >= t1
AND t3 <= t1 + INTERVAL '14 days' THEN 3
WHEN t2 >= t1
AND t2 <= t1 + INTERVAL '14 days' THEN 2
WHEN t1 IS NOT NULL THEN 1
ELSE 0
END AS step_reached
Expect your conversion numbers to drop when you add the window. That's not the query breaking — it's the previous version having been too generous.
Common mistakes and gotchas
Counting events instead of users. Use COUNT(DISTINCT user_id), not COUNT(*). One enthusiastic user viewing the pricing page 40 times should count once, or your top-of-funnel is meaningless.
Ignoring order. The independent-count version treats "subscribed then viewed pricing" the same as "viewed pricing then subscribed." Always anchor later steps to earlier timestamps.
Forgetting the time window. Without one, a funnel silently accumulates matches across a user's entire history and inflates conversion. Pick a window that reflects how the funnel is actually supposed to work.
Off-by-one in window frames. If you extend this to rolling windows, remember ROWS BETWEEN 7 PRECEDING AND CURRENT ROW spans 8 rows, not 7 — one of the most common bugs in window-function queries.
Mismatched grain. If a funnel should be per-session rather than per-user (a user can enter a funnel many times), partition by a session_id, not user_id. Choose your grain before you write a line of SQL.
Key takeaways
Funnel analysis lives or dies on two rules: count distinct users, and respect event order within a time window. The independent-count query is fast and wrong; the ordered step_reached pattern is only a few more lines and actually answers the question. Once you have step_reached, everything else — conversion tables, segmenting by plan or channel, comparing this month to last — is just a GROUP BY away. And because these are subsets of the previous step, the funnel behaves like a funnel instead of a pile of unrelated metrics.
The best part: this all runs against the database you already have. No new pipeline, no event-tracking migration — just SQL you can drop into a saved query and turn into a dashboard.
Your turn
How do you handle funnels where users can re-enter — per session, per day, or first-touch only? And what time window do you use for your signup funnel? Drop your approach (or your gnarliest funnel query) in the comments. If you'd rather not hand-write this every time, tools like Draxlr let you save funnel queries and turn them into shareable dashboards straight on top of your existing database.
Sources: Fivetran — Funnel Analysis and Conversion Metrics in SQL, Cube — SQL Queries for Funnel Analysis, Vikram Oberoi — Funnel analysis in SQL using window functions and range frames, Metabase Learn — How to do funnel analysis.
Top comments (0)