Funnel Conversion Rate SQL: Calculate Step-by-Step Drop-off with Examples

How to calculate funnel conversion rate in SQL

Funnel conversion rate measures how many users move from one step of a funnel to the next. In SQL, the safest pattern is to create one row per user, flag the first time each funnel step happened, and then divide the number of users at each later step by the number of users at the previous step or the first step.

When to use this SQL funnel analysis

Use this funnel conversion SQL when a stakeholder asks why signup, checkout, activation, lead submission, or payment conversion changed. The query creates one user-level row, records whether each step happened, and then calculates conversion rate by step.

Example funnel

landing_page_view
  -> signup_start
  -> signup_complete
  -> checkout_start
  -> payment_success

SQL: user-level funnel flags

This version is useful when step order is usually reliable and you need a fast diagnostic table.

WITH user_steps AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_name = 'landing_page_view' THEN event_time END) AS viewed_at,
    MIN(CASE WHEN event_name = 'signup_start' THEN event_time END) AS signup_started_at,
    MIN(CASE WHEN event_name = 'signup_complete' THEN event_time END) AS signed_up_at,
    MIN(CASE WHEN event_name = 'checkout_start' THEN event_time END) AS checkout_started_at,
    MIN(CASE WHEN event_name = 'payment_success' THEN event_time END) AS paid_at
  FROM events
  WHERE event_time >= TIMESTAMP '2026-06-01'
    AND event_time < TIMESTAMP '2026-07-01'
  GROUP BY 1
)
SELECT
  COUNT(*) AS users,
  COUNT(viewed_at) AS viewed_users,
  COUNT(signup_started_at) AS signup_start_users,
  COUNT(signed_up_at) AS signup_complete_users,
  COUNT(checkout_started_at) AS checkout_start_users,
  COUNT(paid_at) AS paid_users,
  1.0 * COUNT(signup_started_at) / NULLIF(COUNT(viewed_at), 0) AS view_to_signup_start,
  1.0 * COUNT(signed_up_at) / NULLIF(COUNT(signup_started_at), 0) AS signup_completion_rate,
  1.0 * COUNT(paid_at) / NULLIF(COUNT(checkout_started_at), 0) AS payment_completion_rate
FROM user_steps;

SQL: enforce funnel order

If users can fire events out of order, add timestamp conditions so a later step only counts when the earlier step exists first.

WITH user_steps AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_name = 'landing_page_view' THEN event_time END) AS viewed_at,
    MIN(CASE WHEN event_name = 'signup_start' THEN event_time END) AS signup_started_at,
    MIN(CASE WHEN event_name = 'signup_complete' THEN event_time END) AS signed_up_at,
    MIN(CASE WHEN event_name = 'checkout_start' THEN event_time END) AS checkout_started_at,
    MIN(CASE WHEN event_name = 'payment_success' THEN event_time END) AS paid_at
  FROM events
  WHERE event_time >= TIMESTAMP '2026-06-01'
    AND event_time < TIMESTAMP '2026-07-01'
  GROUP BY 1
),
ordered_steps AS (
  SELECT
    user_id,
    viewed_at,
    CASE WHEN signup_started_at > viewed_at THEN signup_started_at END AS signup_started_at,
    CASE WHEN signed_up_at > signup_started_at THEN signed_up_at END AS signed_up_at,
    CASE WHEN checkout_started_at > signed_up_at THEN checkout_started_at END AS checkout_started_at,
    CASE WHEN paid_at > checkout_started_at THEN paid_at END AS paid_at
  FROM user_steps
)
SELECT
  COUNT(viewed_at) AS step_1_users,
  COUNT(signup_started_at) AS step_2_users,
  COUNT(signed_up_at) AS step_3_users,
  COUNT(checkout_started_at) AS step_4_users,
  COUNT(paid_at) AS step_5_users
FROM ordered_steps;

SQL: calculate step-by-step drop-off rate

After you have ordered step counts, calculate both previous-step conversion and total funnel conversion. This makes it clear whether the biggest leak is early, middle, or late in the journey.

WITH funnel_counts AS (
  SELECT 1 AS step_order, 'landing_page_view' AS step_name, COUNT(viewed_at) AS users FROM ordered_steps
  UNION ALL
  SELECT 2, 'signup_start', COUNT(signup_started_at) FROM ordered_steps
  UNION ALL
  SELECT 3, 'signup_complete', COUNT(signed_up_at) FROM ordered_steps
  UNION ALL
  SELECT 4, 'checkout_start', COUNT(checkout_started_at) FROM ordered_steps
  UNION ALL
  SELECT 5, 'payment_success', COUNT(paid_at) FROM ordered_steps
)
SELECT
  step_order,
  step_name,
  users,
  1.0 * users / NULLIF(LAG(users) OVER (ORDER BY step_order), 0) AS previous_step_conversion_rate,
  1.0 * users / NULLIF(FIRST_VALUE(users) OVER (ORDER BY step_order), 0) AS total_funnel_conversion_rate
FROM funnel_counts
ORDER BY step_order;

Daily funnel trend

Use a daily trend when the question is whether the issue started after a release, campaign change, pricing change, or tracking update.

SELECT
  DATE(event_time) AS event_date,
  COUNT(DISTINCT CASE WHEN event_name = 'checkout_start' THEN user_id END) AS checkout_users,
  COUNT(DISTINCT CASE WHEN event_name = 'payment_success' THEN user_id END) AS paid_users,
  1.0 * COUNT(DISTINCT CASE WHEN event_name = 'payment_success' THEN user_id END)
    / NULLIF(COUNT(DISTINCT CASE WHEN event_name = 'checkout_start' THEN user_id END), 0)
    AS payment_conversion_rate
FROM events
WHERE event_time >= TIMESTAMP '2026-06-01'
  AND event_time < TIMESTAMP '2026-07-01'
GROUP BY 1
ORDER BY 1;

Analyst notes

Use user-level grain before calculating funnel rates.
Check whether the conversion window should be same session, same day, 7 days, or 30 days.
Segment only after the overall funnel step is clear.
Review tracking changes before treating a drop as user behavior.

FAQ

Should funnel conversion rate use sessions or users?

Use users for product, signup, lead, and checkout funnels unless the business question is explicitly session-level. User-level grain avoids double-counting people who retry the same step multiple times.

Should each step divide by the first step or the previous step?

Use both. Previous-step conversion shows the leak between two adjacent steps. Total funnel conversion shows how much of the original audience reaches each later step.