Funnel Conversion in SQL

Example query

This example assumes: events(id, user_id bigint, event_name text, occurred_at timestamptz). It is written in PostgreSQL syntax, and your table and column names will differ from these.

WITH steps AS (
  SELECT user_id,
         MIN(occurred_at) FILTER (WHERE event_name = 'visit')    AS visited_at,
         MIN(occurred_at) FILTER (WHERE event_name = 'signup')   AS signed_up_at,
         MIN(occurred_at) FILTER (WHERE event_name = 'purchase') AS purchased_at
  FROM events
  GROUP BY user_id
)
SELECT COUNT(visited_at) AS visited,
       COUNT(signed_up_at) FILTER (WHERE signed_up_at >= visited_at) AS signed_up,
       COUNT(purchased_at) FILTER (
         WHERE signed_up_at >= visited_at AND purchased_at >= signed_up_at) AS purchased,
       COUNT(signed_up_at) FILTER (WHERE signed_up_at >= visited_at)::numeric
         / NULLIF(COUNT(visited_at), 0) AS visit_to_signup_rate
FROM steps;

How it works

• The CTE finds the first time each user did each step, one row per user.
• A step only counts if it happened on or after the step before it, which enforces the order of the funnel.
• Each count is a subset of the previous one, and dividing consecutive counts gives the conversion between steps.

MySQL, BigQuery, SQL Server and SQLite differences

• FILTER is PostgreSQL and SQLite 3.30+; elsewhere use MIN(CASE WHEN event_name = 'visit' THEN occurred_at END) and COUNT(CASE WHEN ... THEN 1 END).

Common mistakes

• Without an order check, a user who purchased before signing up on another device counts as converted.
• A time limit between steps (for example purchase within 7 days of signup) changes the result; add it explicitly if your definition has one.
• Users who skip a step (purchase without a recorded signup event) are lost unless you decide how to treat them.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

• What is the conversion from visit to signup to purchase this month?
• Where do most users drop off between signup and first purchase?