ROLE: ANALYSTTIMELINE: 3 WEEKSPYTHONPANDASPOWER BI
DATA NOTE: this analysis uses a public dataset. Figures reflect the actual data.
CONTEXT
100K+ sessions of e-commerce clickstream data, reconstructed into a five-stage funnel. Revenue was flat while traffic grew. Somewhere between browse and purchase, buyers were leaking out.
QUESTION
Where exactly does the funnel leak, for whom, and what is the cheapest fix worth shipping first?
WORK
Sessions were sequenced into browse → product → cart → checkout → purchase. The aggregate funnel hides the story; the device split exposes it. Toggle below.
FIG 2.1 — SESSION FUNNEL BY DEVICE
21% of sessions reach checkout. Only 6.7% purchase: 68% abandon at the final stage.
Cutting the same funnel by visitor segment localizes the loss: the checkout leak is concentrated in mobile and first-time visitors.
FIG 2.2 — SEGMENT COMPARISON
SEGMENT
SESSIONS
CART RATE
CHECKOUT→BUY
CONVERSION
Desktop
38,410
38%
38%
9.5%
Mobile
64,270
30%
24%
4.1%
New visitor
71,180
28%
26%
4.6%
Returning visitor
31,500
44%
47%
11.2%
Mobile first-time visitors carry the leak: high cart intent, collapsed checkout completion.
Each candidate fix was scored on estimated recovered revenue against build effort, with the team's engineers in the room.
FIG 2.3 — RECOMMENDATION IMPACT MATRIX
Three fixes sit in the do-first quadrant. Guest checkout leads: highest impact, highest confidence.
DECISION
Fix mobile checkout first: guest checkout, shipping cost upfront, address autofill. The recommendation ranked every fix by recovered revenue per unit of engineering effort.
RESULT
A prioritized, quantified roadmap in place of a vague "improve conversion" goal. The 68% checkout abandonment and the 2.3x mobile gap became the two numbers the team tracked weekly.
WHAT I'D DO DIFFERENTLY
Pair the clickstream with checkout error logs from the start. Session paths show where users leave; error logs would have shown why, and cut a week of hypothesis testing.
METHODOLOGY ▸
Sessions reconstructed from event timestamps with a 30-minute inactivity boundary. Funnel stages assigned by furthest event reached per session. Device from user agent; segment cuts are non-overlapping within each dimension.
Impact scores estimate recovered revenue from closing each leak to the desktop baseline; confidence reflects supporting evidence across segments.