How to analyze a conversion funnel

funnel analysis breaks a multi-step process — browse, add to cart, check out, pay — into stages and measures the conversion rate from one stage to the next, so you can see exactly where people drop off rather than only knowing the overall rate from start to finish. It's one of the most common analyses in product and marketing work, and one of the easiest to build wrong in a way that still produces a plausible-looking chart.

Ordered vs unordered funnels

The first real decision is whether steps must happen in sequence within a defined window, or simply all happen at some point. An ordered funnel requires step 2's tracked event to occur after step 1's for the same user, within a time window you set — this is the right model for a checkout flow, where the steps genuinely happen in order. An unordered funnel just checks whether a user did all the listed events in any order, which suits a case like "viewed product, contacted support, made a purchase" where there's no meaningful sequence requirement. Using an unordered funnel for a genuinely sequential process (checkout) will overstate conversion, because it credits users who abandoned and returned out of order as if they completed the flow cleanly.

Building it in SQL

Against a generic events table (user_id, event_name, event_time), an ordered three-step funnel with a same-session window looks like this in ANSI SQL:

with step1 as (
  select user_id, min(event_time) as t1
  from events
  where event_name = 'product_viewed'
  group by user_id
),
step2 as (
  select s1.user_id, min(e.event_time) as t2
  from step1 s1
  join events e
    on e.user_id = s1.user_id
    and e.event_name = 'added_to_cart'
    and e.event_time between s1.t1 and s1.t1 + interval '1 day'
  group by s1.user_id
),
step3 as (
  select s2.user_id, min(e.event_time) as t3
  from step2 s2
  join events e
    on e.user_id = s2.user_id
    and e.event_name = 'checkout_completed'
    and e.event_time between s2.t2 and s2.t2 + interval '1 day'
  group by s2.user_id
)
select
  (select count(distinct user_id) from step1) as viewed,
  (select count(distinct user_id) from step2) as added_to_cart,
  (select count(distinct user_id) from step3) as completed_checkout;

The interval '1 day' window is the detail most templates skip and the one that changes the result most: too tight, and legitimate multi-day purchase journeys get excluded from the funnel entirely, understating conversion; too loose, and unrelated later activity gets credited as if it were part of the same journey, overstating it. Set the window based on your actual sales cycle, not a default.

Where funnels mislead

• Survivorship in the denominator. A funnel built only from users who reached step 1 already excludes everyone who never got that far — a traffic-quality problem upstream of the funnel won't show up in it at all. Always check the absolute volume entering step 1, not just the conversion rates between steps.
• A single blended funnel across very different traffic sources. New organic visitors and returning logged-in customers convert completely differently; a blended funnel's overall rate can stay flat while both segments are actually moving in opposite directions. Cut the funnel by cohort or source before trusting the aggregate number.
• Step definitions that drift. If "checkout started" gets re-instrumented mid-quarter to fire slightly earlier or later in the flow, the funnel shows a conversion change that's actually a tracking change. Treat any sudden step in the trend line as a tracking question first, a behavior question second.
• Treating a funnel step as causal. A funnel shows correlation between stages, not that fixing step 2's friction will proportionally lift step 3 — a genuine causal claim needs an experiment, not just a funnel read. See how to read an A/B test result before promising a specific lift from a funnel fix.
• Subscription and paywall funnels need their own step definitions. paywall conversion rate behaves differently from a one-time purchase funnel — trial starts, trial-to-paid conversion, and involuntary churn from failed payments each need separate tracking, or a "conversion rate" number quietly mixes voluntary and involuntary drop-off together.
• Not every touch that influenced a purchase happened inside the funnel you're measuring. An ad that drove a view-through but no click won't show up as a funnel entry point at all, even though it may have mattered — a reminder that funnel analysis measures a specific tracked path, not the full set of influences on a decision.

Tools built for this

• General product-analytics funnels with cohort and segment cuts built in. Amplitude and Mixpanel both let you build ordered or unordered funnels interactively and slice by any tracked property without writing SQL for every cut.
• Open source, want to validate the built-in funnel against a custom SQL query. PostHog's built-in data warehouse makes it straightforward to run a query like the one above directly against the same event data the UI funnel is built from.
• A Shopify or DTC store wanting the funnel tied directly to ad spend and attribution. Polar Analytics combines store, ad and email data into one funnel view rather than requiring a separate product-analytics instrumentation project.

Questions to ask before trusting a funnel number

• Is this funnel ordered or unordered, and does that match how the process actually happens?
• What time window is used between steps, and was it chosen deliberately or left at a tool's default?
• Is the funnel blended across traffic sources, or cut by the segments that behave differently?
• When did each step's tracking definition last change, and does the trend line show a jump at that date?

Common mistakes

• Using an unordered funnel for a genuinely sequential process, overstating true conversion.
• Reading a blended funnel across very different traffic sources as if it described one customer journey.
• Treating a funnel drop-off as proof that fixing that step will proportionally lift the next one, without testing it.
• Ignoring a sudden shift in the trend that lines up with a tracking change rather than a real behavior change.

For the retention side of the customer journey once someone converts, see how to run a cohort retention analysis. Browse product analytics tools and e-commerce analytics tools for the full field.