DM6: Analysing behaviour: metrics, funnels, cohorts and attribution
Funnels and drop-off
🎯You'll be able to
Build a funnel from events, calculate step-to-step rates, and find the step worth fixing first.
🏠The everyday picture
In a railway station, people walk from the entrance to the ticket window to the platform. If a crowd forms at one staircase, that's where to add a second one.
📘What it really is
-
A funnel is a sequence of steps, each counted as people (or sessions) who reached it:
view_item→add_to_cart→begin_checkout→purchase. -
Step rate: people at step n+1 ÷ people at step n. Overall rate: last step ÷ first step.
-
Count people, in order, within a window: someone who purchased must have viewed the item first, within the same visit or a set number of days.
-
The biggest drop isn't necessarily the best fix: compare with benchmarks of your own (last month, another category), and look at why (research, recordings, support messages).
-
SQL for funnels: count distinct client IDs per event, then join or filter by time order. Window functions (Module 56) help with "first time each person reached each step".
-
Funnels need clean tracking (DM5): a missing
begin_checkoutmakes it look as if people jump from cart to purchase.
📝A worked example
Input: one week of invented MittiMart events.
| Step | People | Step rate |
|---|---|---|
| view_item | 8,000 | — |
| add_to_cart | 960 | 12.0% |
| begin_checkout | 480 | 50.0% |
| purchase | 240 | 50.0% |
Steps:
- Overall: 240 ÷ 8,000 = 3.0%.
- Compare with a month ago: add_to_cart was 14.5%; checkout steps unchanged.
- Look at why: support messages mention "no delivery date shown".
Expected output: the product page (view to cart) is the step to investigate, which is what MittiMart's delivery-date test (Project 2) went on to test. (Illustrative figures.)
🌍Where it's used
- E-commerce teams watch checkout funnels by device.
- Lending apps track application steps, where each document upload is a step.
- Subscription businesses follow sign-up to first use to payment.
📝Case scenario
An insurance site's funnel showed half of visitors leaving at "Enter vehicle details". Session recordings showed many didn't know their vehicle's registration date. Adding "Find it on your RC card" with a picture cut the drop noticeably.
🖱️Try it
Write a SQL query (in words or real SQL) that counts distinct client_ids for view_item and for add_to_cart in one week. Success: the
query counts people, not events, for each step.
⚠️Common mistakes
- Counting events instead of people. Sign: more add-to-carts than views. Fix: count distinct IDs per step.
- Ignoring order. Sign: people counted at "purchase" who didn't view the item in the window. Fix: require steps in sequence.
- Fixing the biggest drop blindly. Sign: redesigning checkout when the product page changed. Fix: compare with earlier periods and look for reasons.
🛠️For your project
Project 1's events make this funnel possible; Project 2 tests a change at the product page step.
✅Quick check
How is a step rate calculated?
Show the answer
People at the next step divided by people at this step.
Why count distinct people rather than events?
Show the answer
One person can trigger an event many times, which inflates the step.