Funnel Analysis
Funnel modelling, step versus end-to-end conversion, windows, multi-entry, and drop-off diagnosis.
8 questions
JuniorTheoryVery commonWhat is a conversion funnel, and how does step conversion differ from end-to-end conversion?
What is a conversion funnel, and how does step conversion differ from end-to-end conversion?
A funnel is an ordered sequence of steps users pass through toward a goal. Step conversion is the share who move from one step to the next; end-to-end conversion is the share who finish the whole path. End-to-end equals the product of the step rates, so the rates multiply rather than average.
Common mistakes
- ✗Averaging the per-step rates instead of multiplying them for end-to-end conversion
- ✗Treating the funnel as an unordered set of events rather than an ordered sequence
- ✗Assuming end-to-end conversion equals the single worst step's rate
Follow-up questions
- →If two steps each convert at 50%, what is the end-to-end conversion across both?
- →Why can adding a step to a funnel only lower the end-to-end conversion?
JuniorTheoryCommonWhat makes an event count as a conversion, and why is 'clicked the Buy button' not the same as 'bought'?
What makes an event count as a conversion, and why is 'clicked the Buy button' not the same as 'bought'?
A conversion event is a logged action that reliably marks the outcome you care about — a completed, confirmed purchase, not an intent to buy. 'Clicked Buy' is only an intent signal: the payment can still fail or the user abandon. Count the server-confirmed order event, defined once and applied consistently across every step.
Common mistakes
- ✗Counting an intent click as a completed purchase
- ✗Trusting a client-side click over the server-confirmed order event
- ✗Redefining the conversion event differently at each step
Follow-up questions
- →Why can a client-side 'Buy' click overcount versus the server order event?
- →How would you define the confirmed-purchase event so every step agrees?
JuniorTheoryCommonA funnel goes 1000 → 600 → 300 → 60 users. What are the step rates, the end-to-end rate, and the largest drop?
A funnel goes 1000 → 600 → 300 → 60 users. What are the step rates, the end-to-end rate, and the largest drop?
Step rates are 600/1000 = 60%, 300/600 = 50%, and 60/300 = 20%. End-to-end is 60/1000 = 6%, equal to 0.6 × 0.5 × 0.2 — the product of the step rates. The lowest step rate is the last, 20%, but the largest absolute loss is the first step, which sheds 400 users.
Common mistakes
- ✗Averaging or summing step rates instead of multiplying for end-to-end
- ✗Assuming the lowest-rate step also loses the most users in absolute terms
- ✗Reading end-to-end conversion as the single worst step's rate
Follow-up questions
- →Which step loses the most users in absolute terms, and why is it not the last?
- →How would the end-to-end rate change if the last step doubled to 40%?
MiddleCodeCommonBuild an ordered view → cart → purchase funnel from a raw event log
Build an ordered view → cart → purchase funnel from a raw event log
Reduce the log to one row per user with each step's first timestamp via MIN(event_time) FILTER (...) grouped by user_id. Then count per step with ordering and window guards: cart after view, purchase after cart, each within 7 days of the first view — making each step a strict subset of the one above.
Common mistakes
- ✗Counting event rows instead of deduping to distinct users per step
- ✗Counting step membership with no ordering or window constraint
- ✗Anchoring on an absolute gap that lets a purchase precede its cart
Follow-up questions
- →Why does
MIN(event_time) FILTER (...)per user keep the step counts monotonic? - →How would you anchor the 7-day window per adjacent step instead of from entry?
MiddleDebuggingCommonFix a funnel query that reports more purchases than cart-adds
Fix a funnel query that reports more purchases than cart-adds
Two faults inflate purchased: it uses COUNT(*), so repeat purchases count many times, and it counts every purchaser — including 'Buy Now' users who never carted. Dedup to distinct users, and require the purchase to follow the same user's first cart within the window. Then it is a strict subset of carted.
Common mistakes
- ✗Using COUNT(*) so repeat purchases inflate the step
- ✗Counting every purchaser, including those who never carted
- ✗Assuming a purchase always implies a prior cart in order
Follow-up questions
- →Why can direct 'Buy Now' purchases break funnel monotonicity?
- →How does the conversion window keep an unrelated later purchase out?
MiddleDesignCommonYour checkout funnel is view → cart → shipping → payment → confirmation. Over the last week the shipping → payment conversion fell from 70% to 58%, while every other step held steady, and no product release note explains it. You have the full event log with user_id, step, timestamp, device, browser, app version, country, and payment method. Describe how you would diagnose the drop — what you check first, how you decide whether it is a real behavioural change or a data/tracking artifact, and how you localize the cause to a specific segment before proposing any fix.
Your checkout funnel is view → cart → shipping → payment → confirmation. Over the last week the shipping → payment conversion fell from 70% to 58%, while every other step held steady, and no product release note explains it. You have the full event log with user_id, step, timestamp, device, browser, app version, country, and payment method. Describe how you would diagnose the drop — what you check first, how you decide whether it is a real behavioural change or a data/tracking artifact, and how you localize the cause to a specific segment before proposing any fix.
First rule out a tracking artifact — check event volume, a recent deploy or SDK change, and whether the payment event stopped logging. Then slice the shipping→payment step by device, browser, app version, country, and payment method: a drop concentrated in one segment points to a specific broken path, a uniform drop to a global change.
Common mistakes
- ✗Jumping to hypotheses before validating the data is real and complete
- ✗Ranking segments by conversion without checking sample size or noise
- ✗Assuming a step drop must be an artifact rather than real behaviour
Follow-up questions
- →How would you tell a genuine behavioural drop from a stopped-logging event?
- →Why weight a segment's drop by its user volume before acting on it?
MiddleDesignCommonA funnel runs 100,000 visits → 60,000 product views → 12,000 carts → 6,000 purchases, and each purchase is worth 50 dollars. Your team can realistically lift exactly one step's conversion by 20% relative. The view → cart step has the steepest drop-off. Explain how you decide which step to improve to win the most incremental revenue, why the biggest drop-off is not automatically the right target, and what you would compute to rank the steps by opportunity.
A funnel runs 100,000 visits → 60,000 product views → 12,000 carts → 6,000 purchases, and each purchase is worth 50 dollars. Your team can realistically lift exactly one step's conversion by 20% relative. The view → cart step has the steepest drop-off. Explain how you decide which step to improve to win the most incremental revenue, why the biggest drop-off is not automatically the right target, and what you would compute to rank the steps by opportunity.
Rank steps by the incremental purchases each 20% lift produces, not by drop-off size. An early-step lift is diluted by every downstream rate, while a lift late in the funnel converts almost directly into purchases. Compute each step's incremental conversions to purchase, value them at 50 dollars, and pick the largest.
Common mistakes
- ✗Equating the biggest drop-off with the biggest revenue opportunity
- ✗Ignoring that an early-step lift is diluted by every downstream rate
- ✗Ranking steps by conversion rate instead of incremental purchases
Follow-up questions
- →Why does a 20% lift late in the funnel convert almost directly to purchases?
- →How would a step's traffic volume change which lift wins the most revenue?
SeniorDesignOccasionalYour PM insists the product funnel is a clean linear path signup → activate → subscribe, but the data shows users entering at different steps, activating before signing up through invites, revisiting steps out of order, and some subscribing months later. A naive linear funnel measured over a fixed calendar month reports a low, unstable conversion. Explain how you would model a funnel that is not strictly linear and is multi-entry, and describe what the conversion window does to the reported number — in particular why recent entrants make the number look worse than it truly is.
Your PM insists the product funnel is a clean linear path signup → activate → subscribe, but the data shows users entering at different steps, activating before signing up through invites, revisiting steps out of order, and some subscribing months later. A naive linear funnel measured over a fixed calendar month reports a low, unstable conversion. Explain how you would model a funnel that is not strictly linear and is multi-entry, and describe what the conversion window does to the reported number — in particular why recent entrants make the number look worse than it truly is.
Model it as ordered step-pairs with a conversion window, not one rigid path: anchor each user at their true entry step, allow multiple entry points, and require downstream steps within the window. The window right-censors recent entrants — they lack a full window yet — so counting them drags the rate down; measure only cohorts whose window has elapsed.
Common mistakes
- ✗Forcing a rigid linear path onto genuinely multi-entry, out-of-order behaviour
- ✗Pooling recent, right-censored entrants with fully-elapsed cohorts
- ✗Widening the window instead of cohorting to remove the censoring bias
Follow-up questions
- →How would you choose the conversion window length for this funnel?
- →Why does measuring only fully-elapsed cohorts stabilize the reported rate?
