Funnel Analysis in Plain SQL (No Analytics Tool Required)

A funnel is just a sequence of steps and the count of people who made it to each one. Signup, then activate, then first purchase. Analytics products make this a drag-and-drop feature and charge accordingly. You can do the same thing in SQL, and once you've written it once it's not even hard.

The model

You have events, each with a user_id, an event name, and an occurred_at. A funnel asks: of the people who did step one, how many went on to do step two, then step three. The subtlety is the word "went on to." Order matters. Buying before signing up doesn't count, and if you ignore the ordering you'll overcount conversions and feel great about a product that isn't working.

The straightforward version

The cleanest way to express "did each step, in order" is to find each user's first timestamp for each step, then check that they increase. Conditional aggregation gets you there:

with steps as (
  select
    user_id,
    min(occurred_at) filter (where name = 'signup')   as step1,
    min(occurred_at) filter (where name = 'activate')  as step2,
    min(occurred_at) filter (where name = 'purchase')  as step3
  from events
  group by user_id
)
select
  count(*) filter (where step1 is not null) as signed_up,
  count(*) filter (where step2 is not null and step2 >= step1) as activated,
  count(*) filter (where step3 is not null and step3 >= step2) as purchased
from steps;

filter (where ...) is the underused Postgres feature that makes this readable. Each line counts users who reached that step, and the >= checks force the steps to happen in order. The result is three numbers: how many entered, how many activated after signing up, how many purchased after activating.

Turning it into conversion rates

The counts are the raw funnel. The percentages are what people actually want, because "we lose 60% between signup and activation" is the sentence that starts a meeting.

with steps as (
  -- same CTE as above
  ...
),
counts as (
  select
    count(*) filter (where step1 is not null) as signed_up,
    count(*) filter (where step2 is not null and step2 >= step1) as activated,
    count(*) filter (where step3 is not null and step3 >= step2) as purchased
  from steps
)
select
  signed_up,
  activated,
  purchased,
  round(100.0 * activated / nullif(signed_up, 0), 1) as signup_to_activate_pct,
  round(100.0 * purchased / nullif(activated, 0), 1) as activate_to_purchase_pct
from counts;

The nullif(..., 0) guards against dividing by zero on an empty funnel. Skip it and one quiet day with no signups throws an error instead of returning null.

The decisions that change the answer

Two choices quietly determine what your funnel says, and you should make them on purpose.

First, the time window. "Of people who signed up, how many ever purchased" is a different funnel from "how many purchased within 7 days." Lifetime conversion always looks better and tells you less. For a product with a real sales cycle, a bounded window is more honest. Add and step3 <= step1 + interval '7 days' if you want the time-boxed version.

Second, whether steps must be strictly ordered or just present. The query above requires order. Sometimes you genuinely don't care — you just want to know who eventually did all three things. Loosen the >= checks if order isn't meaningful for your funnel. But know which one you're computing, because they can differ a lot and "our activation rate is 40%" means nothing without it.

When SQL stops being the right tool

For a three or four step funnel, SQL is great and you don't need a product. Where it gets painful is funnels with many steps, or when you want to slice the same funnel by channel, plan, and cohort all at once. The conditional aggregation balloons and the query gets hard to maintain.

At that point I usually just ask for the funnel in plain English and read the SQL it generates, which is the workflow QueryBear is built for. But for the common case, the query above is the whole feature, and it runs against your real data instead of a sampled copy in some other tool's warehouse.