Why a CRO programme starts with the funnel
Most websites lose the great majority of their visitors before anything measurable happens. An online shop that turns two sessions in a hundred into an order is doing reasonably well, which still means that ninety-eight left along the way. Conversion rate optimisation, usually shortened to CRO, is the discipline of finding out where and why visitors leave and of changing the site, test by test, so that fewer of them do.
It is tempting to begin with solutions: a new headline, a bigger button, a different colour. Teams that work this way run a handful of disconnected tests, most of which change nothing, and then conclude that testing does not work for them. A CRO programme is the opposite of that. It is a repeatable routine that measures where the funnel leaks, researches why, ranks the ideas, tests them at a steady pace and reports honestly what the winners are worth. Every step of the routine rests on numbers that fit comfortably into a spreadsheet.
This free lesson covers the first step of the loop, funnel leak analysis. You will learn the three numbers that describe every funnel step, calculate them in Excel for a complete purchase funnel, let a formula point at the weakest step, and learn how to read the result without jumping to conclusions. The example company is Quillmere Outfitters, a fictional online shop for hiking and camping gear. The company, its people and every number are invented for teaching, and any benchmark mentioned is illustrative.
What a funnel is and what counts as a step
A funnel is an ordered list of steps that a visitor has to pass on the way to a goal, with a count of how many users reached each one. For a shop the goal is an order. For a software company it may be a trial sign-up, for a charity a donation, for a bank a completed application. The steps differ, the arithmetic does not.
A useful funnel has three properties. Each step is something the user actually has to do or see to move on, so viewing the basket is a step and scrolling a page is usually not. The steps are counted in the same unit, ideally users or sessions throughout, never sessions at the top and clicks further down. And the list is short enough to act on: six to ten steps is typical. Quillmere Outfitters uses eight:
- Sessions: every visit to the site, whatever page it starts on.
- Product page viewed: the visitor looked at one product at least.
- Added to basket: the first sign of buying intent.
- Basket viewed: the visitor opened the basket to check it.
- Checkout started: the first checkout screen was loaded.
- Delivery details completed: address and delivery option were submitted.
- Payment details entered: the payment form was filled in.
- Order placed: the confirmation page was reached.
Three numbers for every step
Raw user counts are hard to compare, because every step is smaller than the one above it. Three derived numbers make a funnel readable.
- Step conversion rate = users at this step ÷ users at the previous step. It answers a local question: of the people who got this far, how many took the next step? It is the number a page owner can influence directly.
- Drop-off = users at the previous step − users at this step. It is the same information in absolute terms: how many people were lost here. Some teams also use the drop-off rate, which is simply 1 − step conversion rate.
- Share of sessions reaching the step = users at this step ÷ sessions. It answers a global question: how much of the traffic is still with us? In the last row this number is the site conversion rate that every dashboard shows.
The step rate and the share of sessions look similar and are confused all the time. The difference is the denominator. The step rate divides by the row directly above, so its denominator changes from row to row. The share of sessions always divides by the same cell at the top. In spreadsheet terms the first uses a relative reference and the second an absolute reference, and that small technical difference is the heart of the lab.
A fully worked example: Quillmere Outfitters in September 2026
In September 2026 the shop counted 184,000 sessions and 3,830 orders. The table shows all eight steps with the three calculated columns, exactly as they appear in the lab above once it is solved.
| Funnel step | Users | Step conversion rate | Drop-off (users lost) | Share of sessions |
|---|---|---|---|---|
| Sessions | 184,000 | – | – | 100.00 % |
| Product page viewed | 96,600 | 52.5 % | 87,400 | 52.50 % |
| Added to basket | 21,250 | 22.0 % | 75,350 | 11.55 % |
| Basket viewed | 15,300 | 72.0 % | 5,950 | 8.32 % |
| Checkout started | 9,180 | 60.0 % | 6,120 | 4.99 % |
| Delivery details completed | 6,790 | 74.0 % | 2,390 | 3.69 % |
| Payment details entered | 4,350 | 64.1 % | 2,440 | 2.36 % |
| Order placed | 3,830 | 88.0 % | 520 | 2.08 % |
Read one row slowly. 15,300 users viewed the basket and 9,180 of them started the checkout. The step conversion rate is 9,180 ÷ 15,300 = 60.0 %. The drop-off is 15,300 − 9,180 = 6,120 users, people who had products in the basket, looked at it and still did not begin to pay. And 9,180 ÷ 184,000 = 4.99 % of all sessions got as far as the checkout.
The picture has the classic funnel shape: wide at the top and narrow at the bottom. Between the first and the last bar the shop loses 180,170 sessions. About nine tenths of that loss, 162,750 sessions, happens in the first two steps, before anything is in a basket. That is normal for a shop. Many visitors come to look, to compare or to read, and no checkout redesign will change their plans for the day.
The formulas, step by step
The sheet has the step names in column A and the users in column B, from the sessions in row 4 to the orders in row 11. The three calculated columns start in row 5, because the first step has no previous step.
| Column | Formula in row 5 | Copied down to row 11 | What it answers |
|---|---|---|---|
| C · Step conversion rate | =B5/B4 | =B11/B10 | Of those who reached the previous step, how many went on? |
| D · Drop-off | =B4-B5 | =B10-B11 | How many users were lost at this step? |
| E · Share of sessions | =B5/$B$4 | =B11/$B$4 | Which part of all sessions got this far? |
- Click C5, type
=B5/B4and press Enter. Both references are relative. When you copy the formula down one row it becomes=B6/B5, which is exactly what the next step needs. - Click D5 and type
=B4-B5. The larger number comes first, so the loss is shown as a positive count of users. - Click E5 and type
=B5/$B$4. The dollar signs fix the reference to the sessions. Press F4 right after typing B4 and Excel inserts them for you. - Select C5:E5 and drag the fill handle down to row 11, or double-click it.
- Format columns C and E as percentages with one or two decimals and column D as a whole number with a thousands separator.
=B11/$B$4. If it reads =B11/B10, the reference slid down with the formula and column E is showing step rates a second time.Finding the weakest step with INDEX, MATCH and MIN
With seven step rates on screen you can see the lowest one at a glance. A programme, however, repeats this analysis every month and for every device, channel and country, and then it helps when the sheet names the weakest step by itself. Three functions do it together.
MIN(C5:C11)returns the lowest step rate, 22.0 % in the example.MATCH(MIN(C5:C11),C5:C11,0)looks for that value in the same range and returns its position, here 2, because the lowest rate is the second entry. The final 0 asks for an exact match.INDEX(A5:A11, position)returns the entry at that position from the step names.
Nested into one formula this is =INDEX(A5:A11,MATCH(MIN(C5:C11),C5:C11,0)), and the result is the text Added to basket. In current versions of Excel, =XLOOKUP(MIN(C5:C11),C5:C11,A5:A11) does the same in one function. Both update when next month's numbers are pasted in.
How to read the result: a low rate is not yet a leak
The formula says “Added to basket”: only 22.0 % of the visitors who viewed a product put one in the basket. Is that where the shop is leaking? Not necessarily. A step rate has to be judged against what is normal for that kind of step. Product pages serve browsers, researchers and returning customers checking a detail, so a rate around a fifth can be perfectly healthy. A checkout step is different. Somebody who has typed in an address wants to buy, so a rate that falls clearly below the step's own best months deserves attention.
Look at the checkout rows with that in mind. Of 9,180 users who started the checkout, only 3,830 placed an order. 5,350 people with clear buying intent left on the last three screens, most of them at the delivery and payment steps with rates of 74.0 % and 64.1 %. In absolute numbers that is small beside the 75,350 lost at the product page, but each of these users was far closer to an order.
This is why a programme never stops at the lowest rate. Lesson 2 of the course compares each step with an internal benchmark, the best quarter the same step has ever had, and converts the gap into extra orders and pounds per month. Only then do the steps become comparable, and in most shops the result points at the checkout rather than at the top of the funnel.
Why one better step lifts the whole funnel
Multiply the seven step rates of the table and you get 2.08 %, exactly the share of sessions in the last row. This is no coincidence: every user count cancels against the next denominator, so the site conversion rate is the product of all step rates. In Excel, =PRODUCT(C5:C11) confirms it.
The practical consequence is powerful. If a single step improves and the others stay as they are, the orders grow by the same percentage as that step's rate. Suppose the delivery step rose from 74.0 % to 78 %. That is a relative improvement of 78 ÷ 74.0 − 1 = 5.5 %, so the 3,830 orders would become about 4,039, a gain of roughly 209 orders a month without a single extra visitor. Sizing leaks in this way is the subject of the next lab.
Common mistakes and how to spot them
- Dividing every step by the sessions and calling it a step rate. Then every “rate” falls from row to row and the bottom of the funnel always looks worst. If column C never rises, check the denominator.
- Mixing units. Sessions at the top, users in the middle and events at the bottom produce rates above 100 % or absurdly low ones. Decide on one unit for the whole funnel.
- Ranking steps by lost users alone. The first step always loses most people, in every shop. Absolute drop-off shows scale, not opportunity.
- Reading a low rate as a broken page. Judge each step against its own history or a comparable step, never against the other steps of the same funnel.
- Forgetting optional steps. If customers can go from the product page straight to the checkout, a “basket viewed” step undercounts and the following step rate can exceed 100 %. Either make the funnel reflect the real paths or merge the steps.
- Looking at one month only. A single month contains campaigns, stock problems and holidays. Compare several periods before declaring a leak.
Before you trust a funnel: three checks
Funnel numbers come from tracking, and tracking breaks quietly. Three checks take a few minutes and save embarrassing conclusions.
- The last step should match reality. The orders in the funnel should be close to the orders in the shop system. A gap of a few per cent is usual, because some visitors decline analytics cookies, and that choice must be respected. A gap of a quarter means a broken tag.
- No step should be larger than the one before it. If it is, the steps are not in the order users really take, or they are counted in different units.
- The definition should be stable. A step rate that jumps on the day of a site release usually says more about the tracking than about the visitors.
Quick reference: functions and techniques in this lesson
| Function or technique | What it does here | Example |
|---|---|---|
| Relative reference | Moves with the formula when it is copied down | =B5/B4 |
| Absolute reference | Stays on the sessions in every row | =B5/$B$4 |
MIN | Finds the lowest step rate | =MIN(C5:C11) |
MATCH | Returns the position of that rate in the column | =MATCH(MIN(C5:C11),C5:C11,0) |
INDEX | Returns the step name at that position | =INDEX(A5:A11,2) |
PRODUCT | Multiplies all step rates to check the site rate | =PRODUCT(C5:C11) |
| F4 key | Adds the dollar signs to a reference while typing | B4 becomes $B$4 |
Where funnel analysis is used
- Monthly programme review. Paste the new user counts into column B and the three columns and the weakest step update. Kept month by month, the sheet becomes a history that shows whether a step is drifting.
- Before a redesign. A funnel shows which template deserves the design budget and gives the before-figures against which the new version is judged.
- Lead generation. Replace the steps with landing page, form started, form completed, qualified lead and sale. The formulas are identical.
- Subscription sign-up. Plan page, account created, payment added, trial started, first payment: again one row per step and the same three columns.
- Comparing audiences. One funnel per device or channel, side by side, shows whether a weak step is weak for everyone or only for mobile visitors, which is the subject of a later lab.
What the rest of this course covers
Lesson 1 is free. Lessons 2 to 20 continue with the same fictional shop and a new data set in every lab, and take the programme through the whole loop of Figure 1.
Sizing and researching the leak. You compare every step with a benchmark and express the gap in extra orders and monthly revenue, then turn a heuristic review of eight page templates into weighted scores with SUMPRODUCT, a fix-first rank and a heat map.
Prioritising the backlog. You score twelve test ideas with ICE, rank page templates with PIE using an importance score derived from revenue, and work out for each planned test how many visitors and weeks it needs, so that only testable ideas enter the queue.
Running and reporting the programme. From a test log you calculate test velocity, win rate and the average lift of the winners per quarter, and project the revenue impact of the winners with a haircut on observed lifts and the months each change is live.
Going deeper. Later labs cover segment breakdowns by device and channel, a falling site rate that turns out to be a traffic mix shift, reading a test result by segment without fooling yourself, revenue per visitor as the better success metric, a test calendar with date arithmetic, a trend baseline with FORECAST.LINEAR, win rates by hypothesis theme, a scenario grid of velocity and win rate, the cost and ROI of the programme, sample ratio checks and a backlog ranked by expected value per development day. The final project is a quarterly programme scorecard with status flags and a combo chart.
Summary
Funnel leak analysis turns a list of user counts into three numbers per step. The step conversion rate, =B5/B4, compares each step with the one before it. The drop-off, =B4-B5, counts the users lost. The share of sessions, =B5/$B$4, relates every step to the top of the funnel and ends in the site conversion rate, 2.08 % in the example. INDEX, MATCH and MIN name the step with the lowest rate, here “Added to basket” with 22.0 %. That step is where the questions start, not where they end: whether it is a real leak depends on what is normal for the step and on what the gap is worth.
Now scroll up to the lab and build the sheet yourself. It takes about a quarter of an hour, each formula is checked as you enter it, and you finish with a funnel template you can fill with your own numbers.
Frequently asked questions
What is funnel leak analysis in conversion rate optimisation?
It is the measurement of how many users move from each step of a funnel to the next, for example from basket to checkout, in order to find the steps that lose more users than they should. It is the first step of a CRO programme because it shows where research and tests are most likely to pay off.
How do I calculate a step conversion rate in Excel?
Divide the users at the step by the users at the previous step. With the user counts in column B, type =B5/B4 in row 5 and copy the formula down. Both references are relative, so every row divides by the row above it.
What is the difference between the step conversion rate and the overall conversion rate?
The step conversion rate divides by the previous step and describes one transition. The overall or site conversion rate divides the final step, usually orders, by the sessions at the top of the funnel. The overall rate equals the product of all step rates.
Is the step with the lowest conversion rate always the biggest leak?
No. Some steps have naturally low rates, such as product page to basket, because many visitors are only browsing. A leak is a step that performs below what is normal for it, so compare each step with its own history or a benchmark and value the gap in orders or revenue.
How do I return the name of the weakest funnel step with a formula?
Use =INDEX(A5:A11,MATCH(MIN(C5:C11),C5:C11,0)), where column A holds the step names and column C the step rates. MIN finds the lowest rate, MATCH its position and INDEX the name at that position. XLOOKUP(MIN(C5:C11),C5:C11,A5:A11) gives the same result.
Why do I need an absolute reference for the share of sessions?
Every row must divide by the same cell, the sessions at the top. Writing $B$4 with dollar signs keeps the reference fixed when the formula is copied down. Without them the reference moves one row per copy and the column shows step rates instead.
Keep going: Conversion Rate Optimisation Programmes: Funnel Leak Analysis, Heuristic Scoring, ICE & PIE Backlogs, Test Velocity, Win Rate & Revenue Impact
This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.
- Sizing the Leak: Step Rates Against a Benchmark, Extra Orders if One Step Is Fixed and the Monthly Revenue at Stake
- Heuristic Scoring: a Weighted Page Score with SUMPRODUCT, a Fix-First Rank, the Weakest Criterion per Page and a Heat Map
- Backlog Prioritisation with ICE: Impact, Confidence and Ease Scores, a Rank, Averages per Page Area and the Top Idea
- Where to Test First with PIE: Potential, a Data-Driven Importance Score, Ease, the PIE Score per Page and a Ranked Chart
- Can This Page Carry a Test? Absolute Effect, Visitors Needed per Variant, Weeks to Run and a Verdict per Planned Test
- Test Velocity and Win Rate: Quarters from End Dates, Tests Concluded, the Share of Winners and the Average Winning Lift
- Revenue Impact Projection: a Haircut on Observed Lifts, Expected Monthly Uplift, In-Year Impact by Rollout Month and the Programme Total
- Segment Breakdowns: Conversion Rate by Device and Channel, an Index Against the Site, Device Rates with SUMIFS and the Gap in Orders
- Mix Shift: When the Site Rate Falls but the Segments Improve, a Mix-Adjusted Rate with SUMPRODUCT and the Mix Effect
- Reading a Test Result by Segment: Relative Lift, a z-Score per Slice, a Stricter Threshold for Slices and a Lift Chart
- Beyond Conversion Rate: Conversion Lift, Change in Order Value, Revenue per Visitor Lift and a Rollout Decision per Test
- Test Calendar: End Dates from Weeks, Back-to-Back Start Dates with a Gap, Tests Concluded by a Deadline and Lane Capacity
- Did the Programme Move the Needle? A Trend Baseline with SLOPE and FORECAST.LINEAR, Actual Minus Trend and Incremental Orders
- Learning from the Log: Tests per Theme, Win Rate, Average Winning Lift with IFERROR and the Expected Lift per Test
- Scenario Grid: In-Year Impact by Test Velocity and Win Rate with Mixed References, Tests Needed for a Target and Compounded Lift
- Programme Economics: Quarterly Cost, Incremental Margin, Cumulative Net, Payback and ROI of the CRO Programme with a Combo Chart
- Quality Checks Before the Readout: Sample Ratio Mismatch with Chi-Square, a p-Value, a Check Column and Flagged Rows
- From Scores to Pounds: Expected Value per Idea, Value per Development Day, a Priority Rank and What Fits into the Capacity
- Final Project: CRO Programme Scorecard by Quarter with Test Velocity, Win Rate, Projected Revenue Impact, Status Flags and a Combo Chart