Where Did the Ecommerce Funnel Break?
Decomposing a conversion decline by funnel step, device, channel, and user segment to identify the specific point of friction.
The Result at a Glance:
Aggregate conversion rate declined with no obvious cause
Drop-off concentrated at a specific funnel step in a specific segment
Segmented funnel analysis isolating the friction point
Identified the specific step and segment responsible for the conversion decline, enabling targeted remediation.
Analytics / Data
KPI definition → funnel construction → segmentation → hypothesis testing → recommendation
Business Question
The conversion rate declined. The aggregate number told stakeholders that fewer visitors were converting, but it did not answer why or where. The question was: at which step in the customer journey did behavior change, and for which segment of users?
KPI Definition
I defined the funnel steps clearly before querying any data:
- Product View — user viewed a product detail page (view_item event)
- Add to Cart — user added a product to cart (add_to_cart event)
- Checkout — user initiated checkout (begin_checkout event)
- Purchase — user completed purchase (purchase event)
- Conversion Rate — purchases / sessions (not purchases / page views)
Funnel Construction
I built the funnel using user-level logic rather than simple aggregate event counts. A user who completed a purchase should appear once in each preceding step, regardless of how many events they generated. This distinction matters for accurate step-to-step conversion rates.
Segmentation
I decomposed the funnel across multiple dimensions to isolate where behavior changed:
- Device — desktop vs. mobile vs. tablet
- Channel — organic, paid, direct, referral, social
- Product category — across major product lines
- New vs. returning users
- Geography — by country and region
Hypothesis Testing
Each segment told a different story. The decline was not uniform across all segments — it was concentrated in specific intersections of device, channel, and funnel step.
Root Cause
The investigation revealed that the conversion decline was driven by a specific funnel step within a specific segment, not a site-wide issue. The overall conversion rate dropped because the affected segment represented a large enough portion of total traffic to move the aggregate number.
Recommendation
The recommendation was targeted at the specific step and segment where friction was identified, rather than a blanket "improve checkout" directive. This specificity made the recommendation actionable.
Business Impact
Identifying the specific step and segment responsible for the decline enabled the team to focus remediation efforts precisely. The conversion decline was not a mystery — it was a diagnosable, specific problem.
Diagnostic Code & Query Architecture:
SELECT
COUNT(DISTINCT CASE WHEN event_name = 'view_item' THEN user_pseudo_id END) AS product_viewers,
COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_pseudo_id END) AS cart_adders,
COUNT(DISTINCT CASE WHEN event_name = 'begin_checkout' THEN user_pseudo_id END) AS checkout_starters,
COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id END) AS purchasers
FROM `project.analytics.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131';