E-Commerce & Marketplaces
Thousands visit your store daily — so why are only a handful actually buying?"
ShopWise is a mid-sized e-commerce platform specializing in consumer electronics, with approximately 180,000 monthly active users and an average monthly revenue of $3.2M. Despite healthy traffic, the growth team has noticed that overall purchase conversion rates have been stagnant at around 4.1% for the past two quarters. The Head of Growth suspects that users are dropping off at a specific stage in the purchase journey — but without data to back this up, no targeted intervention can be planned. The team wants to analyze funnel performance for the month of March 2024 using event-level tracking data collected from the platform.
The analytics database captures every user interaction as an event, recording which funnel step each user reached: product_view, add_to_cart, checkout_start, or purchase. Your task is to count how many distinct users reached each funnel step, then calculate the drop-off rate between consecutive steps. This requires SQL aggregations using COUNT(DISTINCT ...) combined with conditional aggregation (CASE WHEN inside aggregate functions) to pivot event-level rows into a single summary row showing all funnel stages side by side.
You are the data analyst on ShopWise's Growth Analytics team. The Head of Growth has asked you to write a SQL query that produces a single-row (or ordered multi-row) funnel summary showing: the count of unique users at each stage, and the percentage drop-off from one stage to the next. This will feed directly into a funnel visualization on the weekly growth dashboard and inform where the UX team should focus optimization efforts.
No results yet
Write a SQL query and click Run to see results