Funnel conversion for product managers

Why funnels exist and when they lie

A funnel is the ordered sequence of steps a user takes toward a target action — landing visit, signup start, signup complete, first key action, purchase, renewal. At every step a slice of users drops out, and the funnel exists to show where the leak is rather than where you assume it is. The whole point is to replace a hunch with a number.

Without a funnel, product optimization is a coin flip. A team at Notion can spend a quarter polishing the "Upgrade" CTA, then realize the bottleneck was the email confirmation step three pages earlier. That kind of misallocation is the default outcome when you skip funnels. The funnel forces you to look at the narrowest stage first, which is almost always where the highest-leverage fix lives.

On a PM interview at Stripe, DoorDash, or Airbnb, expect a funnel question within the first 20 minutes. The trap is to immediately reach for SQL. The high-signal move is to clarify the steps, the user identifier, the window between steps, and the event definitions. Half of the score comes from showing that you would not let two teams measure two different things and call it the same metric.

Step conversion

Step conversion is the share of users who moved from step N to step N+1. It is the local rate of survival, and it tells you which transition is bleeding.

CR(step N → N+1) = users who completed step N+1 / users who completed step N

Worked example. Out of 10,000 landing visitors at a Vercel-hosted SaaS, 4,000 click "Sign up" (CR1 = 40%). Of those, 2,800 finish the signup form (CR2 = 70%). Of those, 700 complete a first key action like creating a project (CR3 = 25%). The decomposition tells you immediately that the signup form itself is fine, but the first-action stage is the bottleneck, and that is where you spend the next sprint.

A step conversion above 100% is always a bug, not a miracle. The usual cause is users entering step N+1 without having completed step N — a deep link from email, a referral that bypasses the landing, a legacy event firing for migrated accounts. When you see 105%, treat it as a data contract violation and fix the join before you trust any other number on the dashboard.

Step conversion is the metric you bring to weekly product reviews; end-to-end is the one you bring to the board.

End-to-end conversion

End-to-end conversion is the share of users who started the funnel and reached a specific later step.

CR(1 → N) = users at step N / users at step 1

For the example above, end-to-end landing → first action = 700 / 10,000 = 7%. That is the integrated number for the channel or the product, and it is the one you compare across quarters and acquisition sources.

End-to-end and step conversion are linked by a clean identity:

CR(1 → N) = CR(1→2) × CR(2→3) × ... × CR(N-1→N)

40% × 70% × 25% = 7%. The fact that it multiplies out is also a sanity check on your SQL. If your dashboard shows step conversions of 40%, 70%, 25% and an end-to-end of 12%, somebody is double-counting somewhere — usually a JOIN that fans out duplicate events.

In reports, show both. End-to-end tells the executive how the product is doing overall. Step conversion tells the squad which stage to touch first. Mixing them up is a classic PM interview red flag.

The funnel window

The single most underrated funnel decision is the window between steps. If a user lands on April 1 and signs up on April 15, do they count as one funnel completion or not? That choice changes every number on the chart.

Without a window, every late conversion counts and end-to-end rates look artificially high. With a window that is too tight, you under-count real users who just took longer. The honest move is to pick the window from product data — the 90th percentile of actual step-to-step lag for converters — and document it on the dashboard.

A small but real detail: the window must be identical across step pairs for a given funnel. A 7-day window from step 1→2 and a 30-day window from step 2→3 creates an end-to-end number that nobody can interpret. Lock it down, version it, and rebuild old dashboards if you change it.

Cohort-sliced funnels

A single-bucket funnel is an average over everything that ever happened, which is the analytics equivalent of "the lights are on somewhere in the building." Slicing by cohort is what turns a funnel into a debugging tool.

Cohort by first-seen date. Everybody whose first session landed on April 1 runs through the funnel together, separate from April 2. Comparing the end-to-end of consecutive day-cohorts surfaces release regressions in days, not weeks. This is also why daily cohorting beats monthly for products that ship every sprint.

Cohort by acquisition source. Paid search, organic, referral, partner integration, App Store browse — each source produces a radically different funnel shape. At a typical Series B SaaS you might see paid search converting 40% landing → signup but only 8% signup → activation, while organic converts 15% landing → signup with 35% signup → activation. Same end-to-end, completely different product story.

Cohort by segment. Region, device, OS, plan tier, prior product usage. Web vs iOS vs Android can diverge at any single step, and the aggregated number hides it cleanly. Marketplaces in particular need to slice by side — buyer funnel and seller funnel are different products living inside one report.

The clean output format is a pivot table: cohorts down the rows, funnel steps across the columns, the cell is the share of that cohort surviving to that step. A PM should be able to spot the outlier row in under five seconds.

Funnel in SQL

The core trick: for each user, compute whether they reached each step within the window and in the right order. Then aggregate.

WITH first_step AS (
  SELECT user_id, MIN(created_at) AS step1_at
  FROM events
  WHERE event_name = 'landing_view'
  GROUP BY user_id
),
step2 AS (
  SELECT f.user_id, MIN(e.created_at) AS step2_at
  FROM first_step f
  JOIN events e
    ON e.user_id = f.user_id
   AND e.event_name = 'signup_started'
   AND e.created_at >= f.step1_at
   AND e.created_at <  f.step1_at + INTERVAL '7 day'
  GROUP BY f.user_id
),
step3 AS (
  SELECT s.user_id, MIN(e.created_at) AS step3_at
  FROM step2 s
  JOIN events e
    ON e.user_id = s.user_id
   AND e.event_name = 'signup_completed'
   AND e.created_at >= s.step2_at
   AND e.created_at <  s.step2_at + INTERVAL '7 day'
  GROUP BY s.user_id
)
SELECT
  COUNT(DISTINCT f.user_id)                         AS n_step1,
  COUNT(DISTINCT s2.user_id)                        AS n_step2,
  COUNT(DISTINCT s3.user_id)                        AS n_step3,
  COUNT(DISTINCT s2.user_id)::NUMERIC
    / NULLIF(COUNT(DISTINCT f.user_id), 0)          AS cr_1_2,
  COUNT(DISTINCT s3.user_id)::NUMERIC
    / NULLIF(COUNT(DISTINCT s2.user_id), 0)         AS cr_2_3
FROM first_step f
LEFT JOIN step2 s2 USING (user_id)
LEFT JOIN step3 s3 USING (user_id);

Three pieces are doing the load-bearing work. MIN(created_at) per step takes the earliest qualifying event; without it, a user with multiple step-2 events can have their step-3 join match a later step 2, scrambling the order. The e.created_at >= prev_step_at predicate enforces forward direction so a user who viewed the landing page after already signing up doesn't get counted as a funnel completion. And NULLIF(..., 0) saves you from a divide-by-zero on empty cohorts and from the silent integer-division trap in dialects that still default to it.

On Snowflake or BigQuery you'd swap INTERVAL '7 day' for the dialect's syntax (INTERVAL 7 DAY and INTERVAL 7 DAY respectively), and on huge event tables you'd partition by event_date and filter early. The structure stays the same.

Common pitfalls

Computing the funnel without a window is the single most common error. With "all events forever" counted, end-to-end conversion looks great and trends look flat, because every laggard eventually converts and dilutes any signal about product speed. The fix is to set the window from the 90th percentile of converter lag, document it, and never quietly change it.

Ignoring event order is the second most common. If step 2 fires before step 1 for a user — common for migrated accounts, deep links, or staff test traffic — they should not enter the funnel at step 1 at all. The fix is the created_at >= step_prev_at predicate plus an explicit exclusion of internal user IDs.

Joining events without per-user aggregation quietly inflates everything. If a user has 5 landing views and 3 signup starts, a naive JOIN produces 15 rows for that one user, and COUNT(*) will tell you the funnel has 15 entries. Always reduce to one row per user per step before joining, and count DISTINCT user_id rather than rows.

Mixing step and end-to-end is the PM interview classic. Someone reports "step 3 conversion is 25%" but actually computed step3_users / step1_users, which is end-to-end to step 3. The fix is to label every metric in the dashboard explicitly — cr_step_2_to_3 is unambiguous, "conversion" is not.

Skipping segmentation smooths over the only useful signal. A 7% end-to-end across the whole product can be 15% on web and 3% on Android, and the average is meaningless. If your funnel has only one number, it's lying to you.

Comparing funnels of different lengths is the silent killer in cross-product reviews. A 3-step funnel and a 5-step funnel will always have different end-to-end rates, and not because one product is worse. Compare like steps to like steps, or compare normalized metrics like step CR distribution, not raw end-to-end.

Related reading

• How to calculate funnel in SQL
• How to calculate funnel step conversion in SQL
• How to calculate funnel drop-off in SQL
• Activation rate metric for PM
• Cohort analysis explained simply

If you want to drill PM-style metric and SQL questions like this every day, NAILDD is launching with 500+ problems across activation, funnels, retention, and pricing — built around exactly this pattern.

FAQ

How is a funnel different from cohort analysis?

A funnel measures the user path across steps within a window, while cohort analysis measures behavior of a fixed group across time. They are complementary, not interchangeable. The common move is to build a separate funnel for each acquisition cohort so you can see whether a January cohort converts differently from a March one. If your dashboard only has one of the two, you're missing half the picture.

How do I pick the funnel window?

Pull the distribution of step-to-step lag among users who actually converted, take the 90th percentile, and round to a friendly value — a day, a week, a month. That captures most real conversions without inflating the rate with stragglers. If your product has obvious lifecycle anchors (trial length, billing cycle, fiscal quarter), align the window to those instead of picking arbitrary durations.

How many steps should a funnel have?

Three to six steps is the working range. Fewer and you can't see the bottleneck; more and the end-to-end rate gets crushed by compounding small drops and the chart becomes unreadable. If the real user journey is genuinely longer, split it into two or three thematically grouped funnels — acquisition funnel, activation funnel, monetization funnel — and report each separately.

Should I count by users or by sessions?

Count by users for almost every business question, because users are the decision-making unit and they move through the product over many sessions. Count by sessions only when the question is genuinely about within-session behavior — an e-commerce checkout funnel within a single shopping session, for example. Mixing the two without saying which is which is how teams end up disagreeing for hours.

What do I do when one step has a giant drop-off?

Dig into it before you trust the headline number. Slice by device, OS version, app version, acquisition source, and timezone. A 60% drop on signup-complete is often a 95% drop on a single Android build with a broken email validator, hidden inside an otherwise normal 30% average. Local fixes from segmented diagnosis ship in days; global fixes from aggregated numbers take quarters and miss.

How does the funnel relate to activation rate?

Activation rate is the end-to-end conversion from funnel start to the activation step — the moment a user does the thing that predicts long-term retention. So activation rate is a funnel metric by definition. If your team has an activation rate but no funnel, you're reporting one number without showing the upstream steps that explain it, which makes it impossible to debug when it moves.