Level 2 · Check and build the web funnel in pandas

What you are checking, and why

web_sessions is a tracking table: every visit to the site, its device and source, how many pages it saw and whether it ended in a purchase. Tracking tables are full of holes, so before you build a funnel on one, find out what it can and cannot show. Step 1 asks four questions in one pass:

• Is any session counted twice? Compare len(s) with s["session_id"].nunique().
• Are there empty sessions? A visit with zero pages is a tracking glitch.
• Who can convert? Count anonymous sessions, and how many of them converted.
• Does the definition hold? Count conversions with fewer than three pages.

A condition on a column gives a boolean Series, and summing it counts the True values:

anon = s["customer_id"].isna()
(anon & s["converted"]).sum()   # 0

The zero is the most important number in the case. More than half the sessions can never show a purchase, because purchases are only recorded against an account. The rates you build next are rates of recorded purchases, and the funnel needs a stage for signing in. Wrapping each count in int() turns NumPy integers into plain numbers for the one-row DataFrame.

A funnel is a chain of boolean columns

A funnel follows the same sessions through stages that narrow down: all sessions, engaged (pages_viewed >= 3), engaged and signed in, converted. In pandas each stage is a boolean column, and named aggregation sums them per device in one call:

result = s.groupby("device", as_index=False).agg(
    sessions=("session_id", "count"),
    engaged=("engaged", "sum"),
    known_engaged=("known_engaged", "sum"),
    converted=("converted", "sum"),
)

The rates divide a stage by the one before it, so you can see where people drop out: engaged_pct is the browsing step, close_pct is the checkout step.

Reading the funnel

device	engaged_pct	close_pct	conversion_pct
desktop	44.1	21.4	3.87
mobile	44.1	12.9	2.41
tablet	44.8	14.6	2.81

The browsing step is the same on every device. The checkout step is where mobile falls behind. That changes the brief for the mobile team: the product pages are doing their job; the basket, the sign-in and the payment form are where to look.

Why the year view matters

Step 3 repeats the conversion rate by year, one column per device. A neat trick: multiply a boolean by 100 and its mean is a percentage. unstack() then turns the device level of the index into columns. Mobile’s share of traffic grew every year, and mobile converts less, so the overall rate is a weighted average whose weights keep moving towards the low rate. Every device improved from 2023 to 2025, while the overall rate grew much less. If you kept 2023’s device mix and applied 2025’s rates, the overall rate would be about 3.24% instead of 3.08%. Always split a rate by its segments before you call it a trend.

Pitfalls

• == None and == np.nan never match; use .isna() and .notna().
• & binds tighter than <, so wrap each comparison in brackets.
• Dividing by sessions for the close rate mixes stages: divide by the stage just before.