Synthetic data. Every number in this case study is synthetic, generated for illustration with a deterministic script. No real company, customer or order data is shown.
Key findings
- Of 1,900 carts, 1,180 never reached checkout (62%): the steepest fall after the product page, and the step where shipping cost and prescription upload first appear.
- Retention is improving cohort over cohort (month-1 return 24%, 27%, 31%), so the problem is conversion before checkout, not loyalty after it.
- Scenario: lifting cart-to-checkout from 38% to 48% would add about 27% more orders on the same traffic, if the checkout-to-order rate holds.
Context
An online pharmacy saw visits climb while orders stayed flat. Two questions followed: where do visitors leave, and do first-time buyers come back? The structure reflects the funnel and conversion tracking I set up at imcure (Google Ads conversion tracking, partner-API journeys); the data here is entirely synthetic and the SQL runs on the synthetic dataset.
Approach
I modelled the journey as a stream of events (visit, product view, add to cart, checkout, order), built the funnel and monthly retention cohorts in SQL, and ranked candidate fixes by the number of orders each could recover.
Analysis
1. Funnel
Browsing drop-off is expected. Among shoppers with intent, 1,180 of 1,900 carts (62%) never reach checkout (Fig. 1). Cart to checkout converts at 37.9%, while checkout to order converts at 84.7%.
Source: synthetic dataset generated for this case study (10,000 sessions). Analysis: Rajul Raman.
| Step | Sessions | Conversion from previous step |
|---|---|---|
| Visits | 10,000 | |
| Product view | 5,200 | 52.0% |
| Add to cart | 1,900 | 36.5% |
| Checkout | 720 | 37.9% |
| Order | 610 | 84.7% |
SELECT event AS stage,
COUNT(DISTINCT session_id) AS sessions,
ROUND(100.0 * COUNT(DISTINCT session_id)
/ (SELECT COUNT(DISTINCT session_id)
FROM events WHERE event = 'visit'), 1) AS pct_of_visits
FROM events
GROUP BY event
ORDER BY sessions DESC;| stage | sessions | pct_of_visits |
|---|---|---|
| visit | 10,000 | 100.0 |
| product_view | 5,200 | 52.0 |
| add_to_cart | 1,900 | 19.0 |
| checkout | 720 | 7.2 |
| order | 610 | 6.1 |
2. Retention by first-order cohort
Month-1 return improves from 24% to 27% to 31% across cohorts, so loyalty after the first order is not the constraint. Share of each cohort that ordered again (synthetic):
| First-order cohort | Month 0 | Month 1 | Month 2 | Month 3 |
|---|---|---|---|---|
| 2026-01 | 100% | 24% | 17% | 14% |
| 2026-02 | 100% | 27% | 19% | 15% |
| 2026-03 | 100% | 31% | 22% | - |
WITH first_order AS (
SELECT user_id, MIN(substr(ordered_at, 1, 7)) AS cohort
FROM orders GROUP BY user_id)
SELECT f.cohort,
(CAST(substr(o.ordered_at, 1, 4) AS INT) - CAST(substr(f.cohort, 1, 4) AS INT)) * 12
+ CAST(substr(o.ordered_at, 6, 2) AS INT) - CAST(substr(f.cohort, 6, 2) AS INT) AS month_n,
ROUND(100.0 * COUNT(DISTINCT o.user_id)
/ (SELECT COUNT(*) FROM first_order x WHERE x.cohort = f.cohort), 0) AS retention_pct
FROM first_order f
JOIN orders o ON o.user_id = f.user_id
GROUP BY 1, 2
ORDER BY 1, 2;Recommendations
Three changes, ranked by orders recoverable.
| Recommendation | What changes | How to test |
|---|---|---|
| 1. Guest checkout | No forced sign-up before payment. | A/B test on the checkout page. |
| 2. Prescription upload at the cart | Upload earlier, with a clear reason if a file is rejected. | A/B test; track rejected-upload rate. |
| 3. Refill reminders | For repeat buyers, ahead of the refill date. | Holdout group; track month-1 return. |
Impact
Scenario, not a measurement. If cart-to-checkout rose from 37.9% to 48%, checkouts would rise from 720 to 912, and with the checkout-to-order rate unchanged at 84.7%, orders from 610 to about 773 (+26.7%) on the same 10,000 visits. This sizes the prize and prioritises the work; it needs validating with an A/B test.