SQL Funnel Analysis

A funnel measures how a defined population progresses through milestones. The SQL is straightforward only after the product definition is precise: who enters, whether steps must occur in order, how long users have to progress, which identity represents a person or account, and how retries and duplicate events behave.

Reduce events to one row per actor

WITH events AS (
  SELECT account_id, event_name, timestamp_utc
  FROM product_events
  WHERE timestamp_utc >= now() - INTERVAL '30 days'
),
signups AS (
  SELECT account_id, MIN(timestamp_utc) AS signed_up_at
  FROM events WHERE event_name = 'signup_completed'
  GROUP BY account_id
),
workspaces AS (
  SELECT s.account_id, s.signed_up_at,
    MIN(e.timestamp_utc) AS workspace_created_at
  FROM signups AS s
  LEFT JOIN events AS e ON e.account_id = s.account_id
    AND e.event_name = 'workspace_created'
    AND e.timestamp_utc >= s.signed_up_at
  GROUP BY s.account_id, s.signed_up_at
),
account_steps AS (
  SELECT w.account_id, w.signed_up_at, w.workspace_created_at,
    MIN(e.timestamp_utc) AS first_event_sent_at
  FROM workspaces AS w
  LEFT JOIN events AS e ON e.account_id = w.account_id
    AND e.event_name = 'first_event_sent'
    AND e.timestamp_utc >= w.workspace_created_at
  GROUP BY w.account_id, w.signed_up_at, w.workspace_created_at
)
SELECT
  COUNT(*) AS accounts_seen,
  COUNT(signed_up_at) AS signed_up,
  COUNT(workspace_created_at) AS created_workspace,
  COUNT(first_event_sent_at) AS sent_first_event
FROM account_steps;

Each stage keeps one row per account and selects the first milestone at or after the previous eligible stage. A missing or out-of-order milestone blocks every later stage; a later valid retry can still advance the account. Duplicate events count once, and equal timestamps are accepted. Add a maximum conversion window when progression must happen within a fixed period.

Keep cohort and observation windows distinct

If the query includes signups from yesterday, those accounts have had less time to activate than accounts from the start of the month. Either allow every cohort a complete observation window or label recent cohorts as incomplete. Filtering all events to the same calendar range can unintentionally cut off valid later milestones.

Choose an account identifier for account-level activation and a user identifier for person-level behavior. Do not switch identities between steps. Define how merged accounts, anonymous sessions, reopened accounts, and repeated completions work.

Always show step counts beside percentages. A conversion change can come from the numerator, denominator, traffic mix, or instrumentation. The tested signup funnel recipe and conversion-rate guide provide a complete starting point. Use Cohort Retention when the question is continued behavior after activation.