Every e-commerce site loses potential customers between the first page view and the final purchase. By measuring how many unique users reach each step of the funnel, product teams can prioritize where to invest in optimization — a leaky cart-to-checkout step calls for a different fix than a leaky checkout-to-purchase step. Using the user_events table (with event types page_view, add_to_cart, checkout, purchase), return a single row with step1_views, step2_cart, step3_checkout, step4_purchase, view_to_cart_pct, cart_to_checkout_pct, and checkout_to_purchase_pct (each conversion rate rounded to 2 decimal places).
user_events
| column | type |
|---|---|
| id | INTEGER |
| user_id | INTEGER |
| event_type | TEXT |
| event_date | DATE |
user_events (abridged — 10 page views, 6 add-to-cart, 4 checkout, 3 purchase)
| id | user_id | event_type | event_date |
|---|---|---|---|
| 1 | 1 | page_view | 2024-01-01 |
| 2 | 2 | page_view | 2024-01-01 |
| ... | 3–10 | page_view | 2024-01-01 |
| 11 | 1 | add_to_cart | 2024-01-01 |
| ... | 2–6 | add_to_cart | 2024-01-01 |
| 17 | 1 | checkout | 2024-01-01 |
| ... | 2–4 | checkout | 2024-01-01 |
| 21 | 1 | purchase | 2024-01-01 |
| 22 | 2 | purchase | 2024-01-01 |
| 23 | 3 | purchase | 2024-01-01 |
| step1_views | step2_cart | step3_checkout | step4_purchase | view_to_cart_pct | cart_to_checkout_pct | checkout_to_purchase_pct |
|---|---|---|---|---|---|---|
| 10 | 6 | 4 | 3 | 60.00 | 66.67 | 75.00 |
10 users reached the page-view step; 6 of them added to cart (60.00%). Of those 6, 4 reached checkout (66.67%). Of those 4, 3 completed a purchase (75.00%).