The request
From Jordan Reyes, Head of Marketing
“Before we argue about which channel to fund, I want to know where people leave. For 2025: how many sessions do we get, how many actually look around, and how many of those buy? Show me each step as a share of the step before and of everything.”
Your goal
Using 2025 sessions only (started_at from 2025-01-01 up to but not including 2026-01-01), set result to a DataFrame with three rows, in this order, and columns stage, sessions, pct_of_previous and pct_of_all (both rounded to 2). Stages: "all" (every session), "engaged" (pages_viewed >= 3) and "converted" (converted is True). pct_of_previous is 100 for the first row; pct_of_all is sessions * 100 / all sessions.
- Use the
web_sessionsDataFramenot done yet - Show exactly
stagesessionspct_of_previouspct_of_allnot done yet - 3 rows, sorted as askednot done yet
Each bar is a subset of the bar above it. Most visitors leave before the third page.
Show the numbers
| sessions | |
|---|---|
| All sessions | 16,225 |
| 3+ pages | 7,213 |
| Converted | 500 |
Your code
Loading the editor…
Reference solution
| session_id | customer_id | started_at | device | source |
|---|---|---|---|---|
| 1 | NaN | 2023-01-01 23:02:24 | mobile | paid |
| 2 | 18 | 2023-01-01 15:07:49 | desktop | paid |
| 3 | NaN | 2023-01-01 16:55:46 | desktop | direct |
| 4 | NaN | 2023-01-01 12:43:28 | mobile | organic |
Mote checks your result
Press Run. Each row that matches the goal becomes a firefly in your lantern.
What Jordan gets, and why
DataFrames
Loaded for you as pandas DataFrames, plus pd and np.
web_sessions40,000 rows · 7 columns
session_idintegercustomer_idintegerstarted_attimestampdevicevarcharsourcevarcharpages_viewedintegerconvertedboolean
| session_id | customer_id | started_at | device | source | pages_viewed | converted |
|---|---|---|---|---|---|---|
| 1 | NaN | 2023-01-01 23:02:24 | mobile | paid | 1 | false |
| 2 | 18 | 2023-01-01 15:07:49 | desktop | paid | 1 | false |
| 3 | NaN | 2023-01-01 16:55:46 | desktop | direct | 7 | false |
| 4 | NaN | 2023-01-01 12:43:28 | mobile | organic | 3 | false |
| 5 | NaN | 2023-01-01 13:06:21 | mobile | social | 2 | false |
customers2,018 rows · 9 columns
customer_idintegerfirst_namevarcharlast_namevarcharemailvarcharcityvarcharstatevarcharsignup_datedatesegmentvarcharreferral_sourcevarchar
| customer_id | first_name | last_name | city | state | signup_date | segment | referral_source | |
|---|---|---|---|---|---|---|---|---|
| 1 | Owen | Hudson | owen.hudson@icloud.com | San Francisco | CA | 2022-06-02 | consumer | search |
| 2 | Douglas | Mendoza | dmendoza69@gmail.com | Atlanta | GA | 2022-06-02 | consumer | NaN |
| 3 | Henry | Brooks | hbrooks@lakesidecafe.com | Los Angeles | CA | 2022-06-04 | business | NaN |
| 4 | Brian | Graham | bgraham@bluecedarhomes.com | Seattle | WA | 2022-06-06 | business | |
| 5 | Kyle | Jimenez | kjimenez27@yahoo.com | Indianapolis | IN | 2022-06-07 | consumer | NaN |
products120 rows · 8 columns
product_idintegernamevarcharcategoryvarcharsubcategoryvarcharunit_pricedecimalunit_costdecimallaunched_ondateis_activeboolean
| product_id | name | category | subcategory | unit_price | unit_cost | launched_on | is_active |
|---|---|---|---|---|---|---|---|
| 101 | Vale Velvet Loveseat | Furniture | Sofas | 1659.00 | NaN | 2023-09-11 | true |
| 102 | Alder Oak Coffee Table | Furniture | Tables | 919.00 | 503.50 | 2024-05-16 | true |
| 103 | Orchard Rattan Dining Chair | Furniture | Chairs | 789.00 | 429.87 | 2023-05-27 | true |
| 104 | Upland Oak Bookcase | Furniture | Storage | 429.00 | 214.70 | 2022-12-20 | true |
| 105 | Avery Leather Bed Frame | Furniture | Beds | 449.00 | 232.70 | 2021-08-31 | true |
orders12,000 rows · 7 columns
order_idintegercustomer_idintegerorder_datetimestampstatusvarcharchannelvarcharshipping_feedecimalcoupon_codevarchar
| order_id | customer_id | order_date | status | channel | shipping_fee | coupon_code |
|---|---|---|---|---|---|---|
| 1001 | 125 | 2023-01-01 20:51:19 | completed | web | 0.00 | NaN |
| 1002 | 156 | 2023-01-02 07:58:46 | completed | web | 0.00 | NaN |
| 1003 | 22 | 2023-01-02 10:25:27 | completed | marketplace | 4.99 | WELCOME10 |
| 1004 | 48 | 2023-01-02 11:21:51 | completed | marketplace | 4.99 | WELCOME10 |
| 1005 | 82 | 2023-01-02 11:43:36 | completed | web | 0.00 | WELCOME10 |
order_items30,338 rows · 6 columns
order_item_idintegerorder_idintegerproduct_idintegerquantityintegerunit_pricedecimaldiscount_pctdecimal
| order_item_id | order_id | product_id | quantity | unit_price | discount_pct |
|---|---|---|---|---|---|
| 1 | 1001 | 121 | 9 | 31.99 | 0.00 |
| 2 | 1001 | 154 | 3 | 11.99 | 0.00 |
| 3 | 1002 | 154 | 4 | 11.99 | 0.00 |
| 4 | 1002 | 194 | 1 | 149.00 | 0.00 |
| 5 | 1002 | 193 | 2 | 19.99 | 0.00 |
returns1,673 rows · 5 columns
return_idintegerorder_item_idintegerreturned_ondatereasonvarcharrefund_amountdecimal
| return_id | order_item_id | returned_on | reason | refund_amount |
|---|---|---|---|---|
| 1 | 14 | 2023-01-10 | damaged | 83.69 |
| 2 | 42 | 2023-01-10 | wrong_item | 19.99 |
| 3 | 105 | 2023-01-14 | wrong_item | 10.99 |
| 4 | 24 | 2023-01-16 | changed_mind | 26.99 |
| 5 | 126 | 2023-01-16 | late | 20.99 |
employees60 rows · 8 columns
employee_idintegerfirst_namevarcharlast_namevarchardepartmentvarchartitlevarcharmanager_idintegerhire_datedatesalaryinteger
| employee_id | first_name | last_name | department | title | manager_id | hire_date | salary |
|---|---|---|---|---|---|---|---|
| 1 | Catherine | Hale | Executive | Chief Executive Officer | NaN | 2016-03-01 | 285000 |
| 2 | Robert | Kim | Executive | Chief Operating Officer | 1 | 2017-01-09 | 235000 |
| 3 | Megan | Collins | Sales | Head of Sales | 1 | 2018-04-16 | 172000 |
| 4 | Chloe | Gutierrez | Sales | Account Executive | 3 | 2020-01-21 | 72000 |
| 5 | Frank | Bell | Sales | Account Executive | 3 | 2023-05-21 | 73000 |
marketing_spend3,276 rows · 3 columns
spend_datedatechannelvarcharamountdecimal
| spend_date | channel | amount |
|---|---|---|
| 2023-01-01 | paid | 492.99 |
| 2023-01-01 | 37.38 | |
| 2023-01-01 | social | 200.53 |
| 2023-01-02 | paid | 709.13 |
| 2023-01-02 | 64.13 |
The lesson
A funnel is a chain of subsets
A funnel lists the steps a visitor takes toward a purchase and counts how many reach each one. Harbor & Pine’s data supports three stages:
- All sessions: every visit.
- Engaged: the visitor viewed at least 3 pages, so they looked around.
- Converted: the session ended in a purchase.
Each stage must be a subset of the stage before. That holds here: no session converts with fewer than 3 pages viewed. If a later stage could contain rows the earlier one doesn’t, the percentages stop meaning “how many made it through”.
Two percentages per stage
Every stage gets two numbers:
- Step conversion (
pct_of_previous): of the visitors who reached the stage before, how many got here. 7,213 of 16,225 sessions engaged (44.46%); 500 of 7,213 engaged sessions converted (6.93%). - Overall conversion (
pct_of_all): of all sessions, how many got here. The last row is the conversion rate from the first level: 3.08%.
Drop-off is the complement of step conversion: 55.54% of sessions leave before the third page, and 93.07% of engaged sessions leave without buying.
Bounce rate
A bounce is a session with exactly one page view. In 2025, 6,993 sessions bounced: a bounce rate of 43.10%. Bounces are part of the first drop-off, along with 2,019 two-page sessions that also never reached the engaged stage.
A high bounce rate usually points at the landing page or the traffic: visitors who were not looking for what the page offers. It is a guardrail worth watching, but not a goal on its own; a page that answers the question on the first screen can have a high bounce rate and be doing its job.
Where to look first
The step with the largest drop-off is not automatically the one to fix, but it is where to start. Here more sessions are lost before engagement (9,012) than after it (6,713), which points at traffic quality and landing pages before checkout.
Common mistakes
- Stages that are not subsets, such as counting “added to cart” from one table and “visited” from another.
- Mixing time windows, such as engaged sessions in December and conversions in January.
- Only showing
pct_of_all, which makes every later stage look tiny and hides which step leaks.
Lantern lit!
Practised: funneldrop offbounce ratestep conversionboolean masks
- Solved
- No hints
- First run