Lesson 3 of 5 · 9 min · Behaviour over time
Funnels: where do people drop off?
What you will learn
- What a funnel is and how to choose its steps
- Step conversion and overall conversion, with a worked example
- Build a funnel in SQL and pandas
- Handle step order, time windows and repeated events
- Segment the funnel and find the step to fix
In simple words
Think of a shop with ten people at the door. Six come in, three pick something up, one pays. A funnel is that story as numbers: at each step some people drop out, and the shape narrows like a funnel. Looking at the steps one by one shows where you lose the most people, which tells you where an improvement would help most.
Words to know
- Funnel: an ordered series of steps people go through towards a goal.
- Step conversion: the share of people at one step who reach the next.
- Overall conversion: the share of people at the first step who reach the last.
- Drop-off: the people who leave between two steps.
A worked example
A learning website measures how visitors become paying students:
| Step | Users | Step conversion | Of first step |
|---|---|---|---|
| 1. Visited the site | 10,000 | 100% | |
| 2. Signed up | 1,200 | 12.0% | 12.0% |
| 3. Finished a first practice test | 480 | 40.0% | 4.8% |
| 4. Paid | 60 | 12.5% | 0.6% |
How the numbers work: 1,200 / 10,000 = 12 percent; 480 / 1,200 = 40 percent; 60 / 480 = 12.5 percent; and overall 60 / 10,000 = 0.6 percent. Overall conversion is the product of the step conversions: 0.12 x 0.40 x 0.125 = 0.006.
Which step to fix? Intuition says the biggest drop (visit to signup loses 88 percent), but that is the most normal step: most visitors are just browsing. Compare each step with benchmarks and with its own history, and also with the effort and impact: improving the 12.5 percent paid conversion to 15 percent raises revenue by 20 percent, and may be easier than finding more visitors.
Choosing steps
- Steps should be meaningful actions in order, not page views for their own sake: "added to cart", "started checkout", "paid".
- Include only steps that people must pass through (or be clear about alternative paths).
- Keep 3 to 7 steps; too many make the analysis noisy.
- Define each step as an event with a precise rule ("finished" means the test was submitted, not merely opened).
- Choose the unit being counted: unique users, sessions, or orders. Mixing them gives nonsense.
Calculating a funnel in SQL
Assume an events table with user_id, event_name and event_time, with events visit, signup, first_test and paid. The simplest way, when you only care whether each user ever did each step:
SELECT
COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'visit') AS visited,
COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'signup') AS signed_up,
COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'first_test') AS tested,
COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'paid') AS paid
FROM events
WHERE event_time >= '2025-10-01' AND event_time < '2025-11-01';
This counts people who did each step but does not check the order or that someone who paid had also done the earlier steps. For a strict funnel, build one row per user with the first time of each step, then check the order:
WITH per_user AS (
SELECT user_id,
MIN(event_time) FILTER (WHERE event_name = 'visit') AS t_visit,
MIN(event_time) FILTER (WHERE event_name = 'signup') AS t_signup,
MIN(event_time) FILTER (WHERE event_name = 'first_test') AS t_test,
MIN(event_time) FILTER (WHERE event_name = 'paid') AS t_paid
FROM events
GROUP BY user_id
)
SELECT
COUNT(*) AS visited,
COUNT(*) FILTER (WHERE t_signup >= t_visit) AS signed_up,
COUNT(*) FILTER (WHERE t_test >= t_signup AND t_signup >= t_visit) AS tested,
COUNT(*) FILTER (WHERE t_paid >= t_test AND t_test >= t_signup AND t_signup >= t_visit) AS paid
FROM per_user
WHERE t_visit IS NOT NULL;
Comparisons with NULL are unknown, so a user who never signed up is not counted at later steps, which is what we want. To add a time limit ("pays within 30 days of signing up"), add AND t_paid <= t_signup + INTERVAL '30 days'.
The same in pandas
steps = ["visit", "signup", "first_test", "paid"]
first = (events.pivot_table(index="user_id", columns="event_name",
values="event_time", aggfunc="min"))
counts = [first[s].notna().sum() for s in steps]
funnel = pd.DataFrame({"step": steps, "users": counts})
funnel["step_conv"] = funnel["users"] / funnel["users"].shift(1)
funnel["of_first"] = funnel["users"] / funnel["users"].iloc[0]
For strict ordering, filter rows where each timestamp is not earlier than the previous step before counting. Tools such as Power BI have funnel visuals, but the calculation above is the foundation.
Time windows and cohorts
Conversions take time. If you measure "visited in October, paid in October", users who visited on 30 October and would pay in November look like failures. Use a cohort approach: take everyone who entered the funnel in a period, and give each the same observation window ("paid within 14 days of visiting"). Exclude people whose window has not finished. The next lesson develops cohorts.
Segment the funnel
An overall funnel hides differences. Break it down by:
- Channel (organic search, ads, referral, WhatsApp share).
- Device (mobile versus desktop).
- City or language.
- New versus returning users.
- Plan or product.
-- add GROUP BY channel to the per_user query above
A common discovery: the overall step conversion is stable but a growing share of mobile traffic converts worse, pulling the average down (a mix effect). Always check segments before concluding that "the page got worse".
Turning drop-off into action
- Find the step with the biggest improvement potential (large drop and plausible fix).
- Ask why with evidence: session recordings, surveys at that step, error logs (a payment gateway timing out looks like drop-off), speed on mobile networks.
- Form a hypothesis ("users do not see the price until checkout") and test it with an A/B test.
- Measure the downstream effect: an easier signup can raise signups but lower quality, so watch the next step and a guardrail like refund rate.
Common mistakes
- Counting events instead of unique users.
- Ignoring step order and time windows, overstating conversion.
- Including users whose window is not complete, understating recent conversion.
- Reading the overall funnel only, missing segment differences.
- Fixing the step with the biggest drop without asking if it is normal or fixable.
- Mixing units (sessions at the top, users at the bottom).
Try it yourself
- Compute step and overall conversion for the table above and check that overall equals the product.
- Write the SQL to build a strict funnel with ordering for a dataset of events.
- Add a 14-day window and exclude users who entered less than 14 days ago.
- Split the funnel by device and note which step differs most.
- Write a hypothesis and an A/B test plan to improve the weakest step.
Check yourself
How do you calculate overall funnel conversion?
Divide the number of users at the last step by the number at the first step; it equals the product of the step conversions.
Why must funnels use consistent units?
Mixing sessions, users and orders at different steps makes the conversion rates meaningless.
Why use a fixed observation window for each user?
Conversion takes time; without equal windows, recent entrants look worse than older ones.
Why segment a funnel?
An overall figure can hide that one channel or device behaves very differently, and a change in traffic mix can move the average without any real change.
Interview questions
Conversion fell from 5 percent to 4 percent. What do you check first?
That the data and definition are the same, then break the funnel down by step, channel, device and new versus returning users to find where and for whom it dropped, and check for mix effects and external changes such as releases or outages.
How would you write SQL for a funnel with ordered steps?
Pivot events to one row per user with the first time of each step, then count users where each step's time is not earlier than the previous step (and within any time window).
Is the largest drop-off always the step to fix?
No. Some drops are natural (browsers who never intended to buy). Prioritise by potential impact, evidence about the cause and the cost of a fix.
Summary
- A funnel shows steps and drop-off; overall conversion is the product of step conversions.
- Use unique users, ordered steps and equal time windows; build it per user in SQL or pandas.
- Segment by channel, device and cohort; beware mix effects.
- Choose the step to fix by impact and evidence, then test and watch downstream metrics.
Going deeper
- PostgreSQL aggregate expressions with FILTER: the clause used for conditional counts.
- PostgreSQL date/time functions and intervals: time windows.
- pandas pivot_table: building one row per user.
See how much of this you can explain
A free mock interview asks you new questions on Metrics, KPIs and business analysis for Data Analyst and scores every answer, with what to fix.