SQL Analytics Case Study
25 end-to-end SQL analyses on a synthetic product dataset · DuckDB · deterministic (seed 42)
20,000users
182,892events
80,000sessions
928orders
268subscriptions
53refunds
98cancellations
Funnel
Biggest drop is add-to-cart → checkout (54% of carts never proceed) — the highest-leverage step to fix.
Retention
D1 retention is stable at ~19-21% but collapses to ~5% by D30. The onboarding window is the leak, not long-term engagement.
A/B test
Treatment wins on purchase conversion (4.9% vs 3.8%, +1.1pp) and the result is statistically significant (z=4.8, p<0.01).
Lifecycle
New users fall from 100% to ~30% of the weekly base while resurrecting grows to ~33% — reactivation does as much work as acquisition.
Revenue retention
Month-1 revenue retention holds (~67-88%) but month-2 cliffs to ~10-20%; repeat rate is just 3.5% — one purchase ≈ lifetime.
Subscriptions & churn
MRR compounds ~15x Jan→Jun (case 14) while logo churn reaches ~15%/month (case 21) — the engine grows even as it leaks; churn is the next lever.
01 Funnel conversion
Question: For each funnel step, how many sessions reached it, and what is
Approach: count distinct sessions per event step (cumulative by funnel order),
| event_name | sessions | overall_pct | step_pct |
|---|---|---|---|
| app_open | 80000 | 100.00 | NaN |
| view_item | 65739 | 82.17 | 82.17 |
| add_to_cart | 29641 | 37.05 | 45.09 |
| checkout | 6584 | 8.23 | 22.21 |
| purchase | 928 | 1.16 | 14.09 |
Signal → The sharpest drop is add-to-cart -> checkout (54% of carts never proceed). That is the highest-leverage step to instrument and fix.
02 N-day retention by cohort
Question: What share of users from each signup-month cohort returned on
Approach: cohort = month(signup_date); flag per user whether any event
| cohort | users | d1_retention | d7_retention | d30_retention |
|---|---|---|---|---|
| 2024-01-01 | 3455 | 20.69 | 14.96 | 5.15 |
| 2024-02-01 | 3211 | 19.87 | 15.95 | 4.64 |
| 2024-03-01 | 3406 | 18.67 | 15.53 | 5.02 |
| 2024-04-01 | 3349 | 19.53 | 15.68 | 5.29 |
| 2024-05-01 | 3358 | 18.64 | 15.22 | 4.79 |
| 2024-06-01 | 3221 | 19.47 | 10.96 | 0.00 |
Signal → D1 ~19-21% collapses to D30 ~5%. The D1->D7 drop points at the onboarding window as the retention bottleneck, not long-term engagement.
03 Rolling 30-day retention
Question: Of users signed up in month M, what % were active at least once in
Approach: EXISTS subquery per user for each window [signup+D, signup+D+30).
| cohort | users | win_7d | win_14d | win_30d | win_60d |
|---|---|---|---|---|---|
| 2024-01-01 | 3455 | 92.27 | 82.75 | 56.21 | 14.79 |
| 2024-02-01 | 3211 | 90.44 | 81.28 | 54.84 | 15.85 |
| 2024-03-01 | 3406 | 92.31 | 82.03 | 53.05 | 17.70 |
| 2024-04-01 | 3349 | 91.97 | 82.89 | 53.69 | 11.38 |
| 2024-05-01 | 3358 | 92.11 | 81.33 | 38.77 | 0.00 |
| 2024-06-01 | 3221 | 52.41 | 26.42 | 0.00 | 0.00 |
Signal → Windowed retention is a fairer lens for sporadic-use products — it does not punish users who return a few days late.
04 DAU / MAU / stickiness
Question: For each active day, how many users were active, how many were
Approach: DAU = distinct users per date; MAU = distinct users active in the
| d | dau | mau | stickiness_pct |
|---|---|---|---|
| 2024-01-02 | 27 | 27 | 100.00 |
| 2024-01-03 | 46 | 66 | 69.70 |
| 2024-01-04 | 72 | 128 | 56.25 |
| 2024-01-05 | 78 | 178 | 43.82 |
| 2024-01-06 | 111 | 252 | 44.05 |
| 2024-01-07 | 121 | 333 | 36.34 |
| 2024-01-08 | 158 | 434 | 36.41 |
| 2024-01-09 | 140 | 512 | 27.34 |
| 2024-01-10 | 196 | 614 | 31.92 |
| 2024-01-11 | 175 | 715 | 24.48 |
| 2024-01-12 | 188 | 802 | 23.44 |
| 2024-01-13 | 217 | 888 | 24.44 |
| 2024-01-14 | 241 | 988 | 24.39 |
| 2024-01-15 | 239 | 1086 | 22.01 |
| 2024-01-16 | 253 | 1164 | 21.74 |
| 2024-01-17 | 250 | 1258 | 19.87 |
| 2024-01-18 | 272 | 1356 | 20.06 |
| 2024-01-19 | 281 | 1470 | 19.12 |
| 2024-01-20 | 262 | 1570 | 16.69 |
| 2024-01-21 | 302 | 1680 | 17.98 |
| 2024-01-22 | 308 | 1793 | 17.18 |
| 2024-01-23 | 357 | 1904 | 18.75 |
| 2024-01-24 | 322 | 2013 | 16.00 |
| 2024-01-25 | 364 | 2126 | 17.12 |
| 2024-01-26 | 318 | 2226 | 14.29 |
| 2024-01-27 | 335 | 2332 | 14.37 |
| 2024-01-28 | 327 | 2425 | 13.48 |
| 2024-01-29 | 351 | 2526 | 13.90 |
| 2024-01-30 | 365 | 2643 | 13.81 |
| 2024-01-31 | 381 | 2745 | 13.88 |
| 2024-02-01 | 369 | 2849 | 12.95 |
| 2024-02-02 | 377 | 2958 | 12.75 |
| 2024-02-03 | 399 | 3059 | 13.04 |
| 2024-02-04 | 372 | 3158 | 11.78 |
| 2024-02-05 | 394 | 3266 | 12.06 |
| 2024-02-06 | 394 | 3371 | 11.69 |
| 2024-02-07 | 410 | 3463 | 11.84 |
| 2024-02-08 | 450 | 3581 | 12.57 |
| 2024-02-09 | 404 | 3673 | 11.00 |
| 2024-02-10 | 415 | 3741 | 11.09 |
| 2024-02-11 | 405 | 3830 | 10.57 |
| 2024-02-12 | 366 | 3914 | 9.35 |
| 2024-02-13 | 423 | 3988 | 10.61 |
| 2024-02-14 | 435 | 4066 | 10.70 |
| 2024-02-15 | 429 | 4156 | 10.32 |
| 2024-02-16 | 435 | 4227 | 10.29 |
| 2024-02-17 | 448 | 4305 | 10.41 |
| 2024-02-18 | 432 | 4411 | 9.79 |
| 2024-02-19 | 433 | 4487 | 9.65 |
| 2024-02-20 | 447 | 4555 | 9.81 |
| 2024-02-21 | 420 | 4619 | 9.09 |
| 2024-02-22 | 475 | 4703 | 10.10 |
| 2024-02-23 | 415 | 4764 | 8.71 |
| 2024-02-24 | 418 | 4834 | 8.65 |
| 2024-02-25 | 383 | 4862 | 7.88 |
| 2024-02-26 | 468 | 4963 | 9.43 |
| 2024-02-27 | 426 | 5012 | 8.50 |
| 2024-02-28 | 429 | 5070 | 8.46 |
| 2024-02-29 | 440 | 5124 | 8.59 |
| 2024-03-01 | 438 | 5173 | 8.47 |
| 2024-03-02 | 447 | 5200 | 8.60 |
| 2024-03-03 | 495 | 5273 | 9.39 |
| 2024-03-04 | 453 | 5296 | 8.55 |
| 2024-03-05 | 442 | 5367 | 8.24 |
| 2024-03-06 | 448 | 5405 | 8.29 |
| 2024-03-07 | 421 | 5435 | 7.75 |
| 2024-03-08 | 426 | 5484 | 7.77 |
| 2024-03-09 | 420 | 5515 | 7.62 |
| 2024-03-10 | 450 | 5553 | 8.10 |
| 2024-03-11 | 469 | 5623 | 8.34 |
| 2024-03-12 | 441 | 5647 | 7.81 |
| 2024-03-13 | 442 | 5677 | 7.79 |
| 2024-03-14 | 456 | 5691 | 8.01 |
| 2024-03-15 | 482 | 5700 | 8.46 |
| 2024-03-16 | 448 | 5707 | 7.85 |
| 2024-03-17 | 457 | 5741 | 7.96 |
| 2024-03-18 | 436 | 5748 | 7.59 |
| 2024-03-19 | 438 | 5756 | 7.61 |
| 2024-03-20 | 458 | 5794 | 7.90 |
| 2024-03-21 | 464 | 5804 | 7.99 |
| 2024-03-22 | 455 | 5833 | 7.80 |
| 2024-03-23 | 474 | 5880 | 8.06 |
| 2024-03-24 | 475 | 5934 | 8.00 |
| 2024-03-25 | 467 | 5959 | 7.84 |
| 2024-03-26 | 487 | 5983 | 8.14 |
| 2024-03-27 | 492 | 6026 | 8.16 |
| 2024-03-28 | 511 | 6063 | 8.43 |
| 2024-03-29 | 438 | 6089 | 7.19 |
| 2024-03-30 | 484 | 6110 | 7.92 |
| 2024-03-31 | 479 | 6091 | 7.86 |
| 2024-04-01 | 453 | 6078 | 7.45 |
| 2024-04-02 | 427 | 6076 | 7.03 |
| 2024-04-03 | 450 | 6076 | 7.41 |
| 2024-04-04 | 474 | 6102 | 7.77 |
| 2024-04-05 | 453 | 6088 | 7.44 |
| 2024-04-06 | 503 | 6130 | 8.21 |
| 2024-04-07 | 470 | 6124 | 7.67 |
| 2024-04-08 | 444 | 6139 | 7.23 |
| 2024-04-09 | 466 | 6154 | 7.57 |
| 2024-04-10 | 496 | 6161 | 8.05 |
| 2024-04-11 | 445 | 6167 | 7.22 |
| 2024-04-12 | 476 | 6164 | 7.72 |
| 2024-04-13 | 480 | 6187 | 7.76 |
| 2024-04-14 | 473 | 6202 | 7.63 |
| 2024-04-15 | 458 | 6201 | 7.39 |
| 2024-04-16 | 472 | 6198 | 7.62 |
| 2024-04-17 | 469 | 6210 | 7.55 |
| 2024-04-18 | 477 | 6218 | 7.67 |
| 2024-04-19 | 457 | 6231 | 7.33 |
| 2024-04-20 | 461 | 6222 | 7.41 |
| 2024-04-21 | 448 | 6198 | 7.23 |
| 2024-04-22 | 482 | 6195 | 7.78 |
| 2024-04-23 | 448 | 6180 | 7.25 |
| 2024-04-24 | 454 | 6168 | 7.36 |
| 2024-04-25 | 447 | 6166 | 7.25 |
| 2024-04-26 | 389 | 6146 | 6.33 |
| 2024-04-27 | 505 | 6167 | 8.19 |
| 2024-04-28 | 466 | 6167 | 7.56 |
| 2024-04-29 | 495 | 6190 | 8.00 |
| 2024-04-30 | 468 | 6185 | 7.57 |
| 2024-05-01 | 490 | 6205 | 7.90 |
| 2024-05-02 | 485 | 6200 | 7.82 |
| 2024-05-03 | 471 | 6216 | 7.58 |
| 2024-05-04 | 454 | 6216 | 7.30 |
| 2024-05-05 | 453 | 6214 | 7.29 |
| 2024-05-06 | 479 | 6227 | 7.69 |
| 2024-05-07 | 441 | 6216 | 7.09 |
| 2024-05-08 | 493 | 6219 | 7.93 |
| 2024-05-09 | 428 | 6242 | 6.86 |
| 2024-05-10 | 477 | 6264 | 7.61 |
| 2024-05-11 | 456 | 6270 | 7.27 |
| 2024-05-12 | 486 | 6272 | 7.75 |
| 2024-05-13 | 451 | 6266 | 7.20 |
| 2024-05-14 | 468 | 6264 | 7.47 |
| 2024-05-15 | 485 | 6267 | 7.74 |
| 2024-05-16 | 443 | 6283 | 7.05 |
| 2024-05-17 | 495 | 6283 | 7.88 |
| 2024-05-18 | 502 | 6300 | 7.97 |
| 2024-05-19 | 477 | 6306 | 7.56 |
| 2024-05-20 | 485 | 6305 | 7.69 |
| 2024-05-21 | 479 | 6331 | 7.57 |
| 2024-05-22 | 499 | 6335 | 7.88 |
| 2024-05-23 | 459 | 6338 | 7.24 |
| 2024-05-24 | 474 | 6384 | 7.42 |
| 2024-05-25 | 434 | 6373 | 6.81 |
| 2024-05-26 | 471 | 6352 | 7.41 |
| 2024-05-27 | 448 | 6334 | 7.07 |
| 2024-05-28 | 478 | 6349 | 7.53 |
| 2024-05-29 | 485 | 6343 | 7.65 |
| 2024-05-30 | 441 | 6332 | 6.96 |
| 2024-05-31 | 445 | 6310 | 7.05 |
| 2024-06-01 | 448 | 6306 | 7.10 |
| 2024-06-02 | 477 | 6324 | 7.54 |
| 2024-06-03 | 475 | 6288 | 7.55 |
| 2024-06-04 | 463 | 6308 | 7.34 |
| 2024-06-05 | 458 | 6316 | 7.25 |
| 2024-06-06 | 465 | 6295 | 7.39 |
| 2024-06-07 | 470 | 6303 | 7.46 |
| 2024-06-08 | 463 | 6305 | 7.34 |
| 2024-06-09 | 450 | 6298 | 7.15 |
| 2024-06-10 | 441 | 6304 | 7.00 |
| 2024-06-11 | 461 | 6322 | 7.29 |
| 2024-06-12 | 463 | 6322 | 7.32 |
| 2024-06-13 | 441 | 6322 | 6.98 |
| 2024-06-14 | 413 | 6309 | 6.55 |
| 2024-06-15 | 433 | 6286 | 6.89 |
| 2024-06-16 | 459 | 6264 | 7.33 |
| 2024-06-17 | 422 | 6251 | 6.75 |
| 2024-06-18 | 439 | 6235 | 7.04 |
| 2024-06-19 | 420 | 6210 | 6.76 |
| 2024-06-20 | 464 | 6220 | 7.46 |
| 2024-06-21 | 452 | 6208 | 7.28 |
| 2024-06-22 | 400 | 6206 | 6.45 |
| 2024-06-23 | 444 | 6227 | 7.13 |
| 2024-06-24 | 467 | 6245 | 7.48 |
| 2024-06-25 | 446 | 6229 | 7.16 |
| 2024-06-26 | 463 | 6234 | 7.43 |
| 2024-06-27 | 463 | 6226 | 7.44 |
| 2024-06-28 | 466 | 6231 | 7.48 |
| 2024-06-29 | 470 | 6222 | 7.55 |
| 2024-06-30 | 443 | 6201 | 7.14 |
Signal → Stickiness is a frequency metric, not reach. ~40% means the average user is active ~12 days/month.
05 LTV by cohort
Question: Average revenue per user (LTV) by signup cohort.
Approach: cohort = month(signup_date); lifetime order revenue per user;
| cohort | users | revenue | ltv_per_user |
|---|---|---|---|
| 2024-01-01 | 3455 | 5264.15 | 1.52 |
| 2024-02-01 | 3211 | 3907.32 | 1.22 |
| 2024-03-01 | 3406 | 5112.27 | 1.50 |
| 2024-04-01 | 3349 | 4528.17 | 1.35 |
| 2024-05-01 | 3358 | 4077.04 | 1.21 |
| 2024-06-01 | 3221 | 2305.10 | 0.72 |
Signal → Cohort LTV must be compared at the same age, not calendar date — younger cohorts look smaller simply because they have had less time to spend.
06 Top-3 categories per country
Question: Which 3 categories bring the most revenue in each country?
Approach: join orders→users, sum revenue by (country, category), rank within
| country | product_category | revenue | rnk |
|---|---|---|---|
| BY | electronics | 529.15 | 1 |
| BY | books | 425.79 | 2 |
| BY | home | 324.85 | 3 |
| KZ | home | 628.41 | 1 |
| KZ | sports | 525.27 | 2 |
| KZ | clothing | 520.43 | 3 |
| Other | clothing | 710.97 | 1 |
| Other | electronics | 648.10 | 2 |
| Other | beauty | 591.17 | 3 |
| RU | clothing | 2629.39 | 1 |
| RU | electronics | 2562.38 | 2 |
| RU | beauty | 2429.23 | 3 |
| UA | sports | 679.20 | 1 |
| UA | electronics | 607.27 | 2 |
| UA | clothing | 565.69 | 3 |
Signal → Use ROW_NUMBER (not RANK/DENSE_RANK) when you want exactly N rows per group regardless of ties.
07 Cumulative revenue
Question: Daily revenue and the cumulative running total over time.
Approach: SUM(amount) per day; windowed SUM() ORDER BY day, UNBOUNDED PRECEDING.
| d | daily_revenue | cumulative |
|---|---|---|
| 2024-01-03 | 39.23 | 39.23 |
| 2024-01-04 | 72.66 | 111.89 |
| 2024-01-05 | 77.74 | 189.63 |
| 2024-01-06 | 57.76 | 247.39 |
| 2024-01-08 | 79.10 | 326.49 |
| 2024-01-09 | 42.92 | 369.41 |
| 2024-01-10 | 79.63 | 449.04 |
| 2024-01-11 | 109.30 | 558.34 |
| 2024-01-12 | 50.21 | 608.55 |
| 2024-01-13 | 57.51 | 666.06 |
| 2024-01-14 | 69.58 | 735.64 |
| 2024-01-15 | 75.80 | 811.44 |
| 2024-01-16 | 117.14 | 928.58 |
| 2024-01-17 | 88.46 | 1017.04 |
| 2024-01-18 | 61.05 | 1078.09 |
| 2024-01-19 | 134.10 | 1212.19 |
| 2024-01-20 | 37.71 | 1249.90 |
| 2024-01-21 | 113.89 | 1363.79 |
| 2024-01-22 | 87.24 | 1451.03 |
| 2024-01-23 | 45.82 | 1496.85 |
| 2024-01-24 | 62.13 | 1558.98 |
| 2024-01-25 | 232.81 | 1791.79 |
| 2024-01-26 | 98.67 | 1890.46 |
| 2024-01-27 | 91.38 | 1981.84 |
| 2024-01-28 | 156.95 | 2138.79 |
| 2024-01-29 | 95.25 | 2234.04 |
| 2024-01-30 | 157.43 | 2391.47 |
| 2024-01-31 | 77.56 | 2469.03 |
| 2024-02-01 | 204.85 | 2673.88 |
| 2024-02-02 | 51.36 | 2725.24 |
| 2024-02-03 | 61.55 | 2786.79 |
| 2024-02-04 | 123.75 | 2910.54 |
| 2024-02-05 | 149.24 | 3059.78 |
| 2024-02-06 | 155.57 | 3215.35 |
| 2024-02-07 | 124.72 | 3340.07 |
| 2024-02-08 | 115.46 | 3455.53 |
| 2024-02-09 | 69.99 | 3525.52 |
| 2024-02-10 | 166.93 | 3692.45 |
| 2024-02-11 | 99.29 | 3791.74 |
| 2024-02-12 | 178.21 | 3969.95 |
| 2024-02-13 | 150.83 | 4120.78 |
| 2024-02-14 | 108.69 | 4229.47 |
| 2024-02-15 | 87.96 | 4317.43 |
| 2024-02-16 | 171.69 | 4489.12 |
| 2024-02-17 | 84.97 | 4574.09 |
| 2024-02-18 | 134.50 | 4708.59 |
| 2024-02-19 | 228.69 | 4937.28 |
| 2024-02-20 | 248.80 | 5186.08 |
| 2024-02-21 | 212.57 | 5398.65 |
| 2024-02-22 | 204.46 | 5603.11 |
| 2024-02-23 | 84.84 | 5687.95 |
| 2024-02-24 | 105.58 | 5793.53 |
| 2024-02-25 | 70.54 | 5864.07 |
| 2024-02-26 | 125.41 | 5989.48 |
| 2024-02-27 | 260.64 | 6250.12 |
| 2024-02-28 | 110.72 | 6360.84 |
| 2024-02-29 | 156.79 | 6517.63 |
| 2024-03-01 | 143.16 | 6660.79 |
| 2024-03-02 | 199.81 | 6860.60 |
| 2024-03-03 | 148.79 | 7009.39 |
| 2024-03-04 | 200.81 | 7210.20 |
| 2024-03-05 | 172.74 | 7382.94 |
| 2024-03-06 | 161.61 | 7544.55 |
| 2024-03-07 | 197.13 | 7741.68 |
| 2024-03-08 | 218.72 | 7960.40 |
| 2024-03-09 | 88.48 | 8048.88 |
| 2024-03-10 | 188.60 | 8237.48 |
| 2024-03-11 | 124.94 | 8362.42 |
| 2024-03-12 | 204.16 | 8566.58 |
| 2024-03-13 | 87.42 | 8654.00 |
| 2024-03-14 | 58.63 | 8712.63 |
| 2024-03-15 | 216.35 | 8928.98 |
| 2024-03-16 | 170.31 | 9099.29 |
| 2024-03-17 | 96.77 | 9196.06 |
| 2024-03-18 | 124.18 | 9320.24 |
| 2024-03-19 | 158.66 | 9478.90 |
| 2024-03-20 | 57.02 | 9535.92 |
| 2024-03-21 | 91.76 | 9627.68 |
| 2024-03-22 | 153.10 | 9780.78 |
| 2024-03-23 | 398.83 | 10179.61 |
| 2024-03-24 | 201.40 | 10381.01 |
| 2024-03-25 | 46.28 | 10427.29 |
| 2024-03-26 | 141.26 | 10568.55 |
| 2024-03-27 | 120.47 | 10689.02 |
| 2024-03-28 | 245.27 | 10934.29 |
| 2024-03-29 | 91.06 | 11025.35 |
| 2024-03-30 | 264.32 | 11289.67 |
| 2024-03-31 | 59.44 | 11349.11 |
| 2024-04-01 | 90.18 | 11439.29 |
| 2024-04-02 | 97.60 | 11536.89 |
| 2024-04-03 | 201.19 | 11738.08 |
| 2024-04-04 | 85.90 | 11823.98 |
| 2024-04-05 | 188.34 | 12012.32 |
| 2024-04-06 | 102.91 | 12115.23 |
| 2024-04-07 | 384.06 | 12499.29 |
| 2024-04-08 | 108.38 | 12607.67 |
| 2024-04-09 | 77.30 | 12684.97 |
| 2024-04-10 | 153.91 | 12838.88 |
| 2024-04-11 | 168.40 | 13007.28 |
| 2024-04-12 | 91.44 | 13098.72 |
| 2024-04-13 | 165.30 | 13264.02 |
| 2024-04-14 | 148.16 | 13412.18 |
| 2024-04-15 | 141.79 | 13553.97 |
| 2024-04-16 | 174.01 | 13727.98 |
| 2024-04-17 | 153.54 | 13881.52 |
| 2024-04-18 | 269.63 | 14151.15 |
| 2024-04-19 | 94.37 | 14245.52 |
| 2024-04-20 | 63.82 | 14309.34 |
| 2024-04-21 | 218.53 | 14527.87 |
| 2024-04-22 | 168.26 | 14696.13 |
| 2024-04-23 | 196.61 | 14892.74 |
| 2024-04-24 | 105.66 | 14998.40 |
| 2024-04-25 | 203.88 | 15202.28 |
| 2024-04-26 | 120.42 | 15322.70 |
| 2024-04-27 | 301.12 | 15623.82 |
| 2024-04-28 | 98.96 | 15722.78 |
| 2024-04-29 | 113.30 | 15836.08 |
| 2024-04-30 | 121.83 | 15957.91 |
| 2024-05-01 | 233.38 | 16191.29 |
| 2024-05-02 | 241.39 | 16432.68 |
| 2024-05-03 | 192.41 | 16625.09 |
| 2024-05-04 | 149.37 | 16774.46 |
| 2024-05-05 | 161.99 | 16936.45 |
| 2024-05-06 | 84.66 | 17021.11 |
| 2024-05-07 | 52.09 | 17073.20 |
| 2024-05-08 | 299.10 | 17372.30 |
| 2024-05-09 | 92.06 | 17464.36 |
| 2024-05-10 | 247.91 | 17712.27 |
| 2024-05-11 | 42.86 | 17755.13 |
| 2024-05-12 | 173.38 | 17928.51 |
| 2024-05-13 | 39.68 | 17968.19 |
| 2024-05-14 | 176.65 | 18144.84 |
| 2024-05-15 | 157.29 | 18302.13 |
| 2024-05-16 | 177.48 | 18479.61 |
| 2024-05-17 | 68.38 | 18547.99 |
| 2024-05-18 | 132.11 | 18680.10 |
| 2024-05-19 | 129.36 | 18809.46 |
| 2024-05-20 | 232.00 | 19041.46 |
| 2024-05-21 | 154.73 | 19196.19 |
| 2024-05-22 | 303.32 | 19499.51 |
| 2024-05-23 | 116.43 | 19615.94 |
| 2024-05-24 | 57.89 | 19673.83 |
| 2024-05-25 | 130.25 | 19804.08 |
| 2024-05-26 | 208.17 | 20012.25 |
| 2024-05-27 | 122.40 | 20134.65 |
| 2024-05-28 | 139.35 | 20274.00 |
| 2024-05-29 | 86.97 | 20360.97 |
| 2024-05-30 | 194.49 | 20555.46 |
| 2024-05-31 | 186.11 | 20741.57 |
| 2024-06-01 | 91.79 | 20833.36 |
| 2024-06-02 | 50.48 | 20883.84 |
| 2024-06-03 | 178.81 | 21062.65 |
| 2024-06-04 | 90.41 | 21153.06 |
| 2024-06-05 | 132.78 | 21285.84 |
| 2024-06-06 | 34.78 | 21320.62 |
| 2024-06-07 | 145.36 | 21465.98 |
| 2024-06-08 | 149.11 | 21615.09 |
| 2024-06-09 | 91.34 | 21706.43 |
| 2024-06-10 | 131.26 | 21837.69 |
| 2024-06-11 | 93.59 | 21931.28 |
| 2024-06-12 | 144.82 | 22076.10 |
| 2024-06-13 | 251.10 | 22327.20 |
| 2024-06-14 | 338.63 | 22665.83 |
| 2024-06-15 | 226.87 | 22892.70 |
| 2024-06-16 | 118.39 | 23011.09 |
| 2024-06-17 | 47.17 | 23058.26 |
| 2024-06-18 | 260.64 | 23318.90 |
| 2024-06-19 | 221.96 | 23540.86 |
| 2024-06-20 | 165.60 | 23706.46 |
| 2024-06-21 | 93.92 | 23800.38 |
| 2024-06-22 | 136.84 | 23937.22 |
| 2024-06-23 | 153.24 | 24090.46 |
| 2024-06-24 | 199.12 | 24289.58 |
| 2024-06-25 | 99.17 | 24388.75 |
| 2024-06-26 | 382.69 | 24771.44 |
| 2024-06-27 | 126.40 | 24897.84 |
| 2024-06-28 | 55.39 | 24953.23 |
| 2024-06-29 | 66.98 | 25020.21 |
| 2024-06-30 | 173.84 | 25194.05 |
Signal → ROWS UNBOUNDED PRECEDING is the safe default for running totals when order dates can repeat; RANGE would merge duplicate days.
08 Longest active-day streak
Question: For each user, the longest run of consecutive active days.
Approach: distinct (user, active day); ROW_NUMBER() over user by day;
| user_id | longest_streak_days |
|---|---|
| 5000 | 6 |
| 7212 | 6 |
| 17542 | 6 |
| 18183 | 6 |
| 18196 | 6 |
| 1412 | 5 |
| 1616 | 5 |
| 2052 | 5 |
| 3492 | 5 |
| 4373 | 5 |
| 5962 | 5 |
| 6313 | 5 |
| 6593 | 5 |
| 7038 | 5 |
| 9121 | 5 |
| 9481 | 5 |
| 10141 | 5 |
| 10597 | 5 |
| 10827 | 5 |
| 13483 | 5 |
| 13725 | 5 |
| 14605 | 5 |
| 15393 | 5 |
| 15657 | 5 |
| 16697 | 5 |
| 18019 | 5 |
| 18757 | 5 |
| 18885 | 5 |
| 19386 | 5 |
| 19420 | 5 |
| 3 | 4 |
| 34 | 4 |
| 106 | 4 |
| 132 | 4 |
| 286 | 4 |
| 437 | 4 |
| 570 | 4 |
| 628 | 4 |
| 897 | 4 |
| 989 | 4 |
| 1110 | 4 |
| 1462 | 4 |
| 1471 | 4 |
| 1480 | 4 |
| 1799 | 4 |
| 1996 | 4 |
| 2062 | 4 |
| 2372 | 4 |
| 2418 | 4 |
| 2491 | 4 |
Signal → The day - row_number trick is the canonical gaps-and-islands pattern: it converts 'consecutive' into 'same group' in one pass.
09 A/B conversion by variant (with significance)
Question: Does the treatment variant lift purchase conversion, and is the
Approach: per-variant user-level conversion; two-proportion z-test with
| ab_variant | users | purchasers | conv_pct | lift_pp | z_score | p_value | significant_005 |
|---|---|---|---|---|---|---|---|
| control | 9918 | 374 | 3.77 | NaN | 4.81 | 0.000002 | YES |
| treatment | 10082 | 522 | 5.18 | 1.41 | 4.81 | 0.000002 | YES |
Signal → Treatment converts higher and the lift is statistically significant (z=4.8, p<0.01). Note the unit: user-level conversion (~4-5%) is far higher than session-level (~1%, case 01) — pick the unit before reporting.
10 Revenue attribution: lifetime vs first-touch
Question: How much revenue is attributed to each signup channel under
Approach: channel = users.channel (acquisition); lifetime = SUM(all orders);
| channel | lifetime_revenue | first_touch_revenue |
|---|---|---|
| organic | 8000.03 | 7476.33 |
| referral | 4863.22 | 4737.66 |
| social | 4508.01 | 4449.29 |
| paid_search | 3985.64 | 3940.63 |
| 3837.15 | 3701.95 |
Signal → The gap between first-touch and lifetime revenue is itself a signal — large gaps mark channels with repeat-purchase potential worth investing in.
11 7-day moving average of DAU
Question: What is the day-level DAU and its 7-day moving average, so the
Approach: count distinct users per calendar day; AVG() as a window function
| d | dau | dau_ma7 |
|---|---|---|
| 2024-01-02 | 27 | 27.0 |
| 2024-01-03 | 46 | 36.5 |
| 2024-01-04 | 72 | 48.3 |
| 2024-01-05 | 78 | 55.8 |
| 2024-01-06 | 111 | 66.8 |
| 2024-01-07 | 121 | 75.8 |
| 2024-01-08 | 158 | 87.6 |
| 2024-01-09 | 140 | 103.7 |
| 2024-01-10 | 196 | 125.1 |
| 2024-01-11 | 175 | 139.9 |
| 2024-01-12 | 188 | 155.6 |
| 2024-01-13 | 217 | 170.7 |
| 2024-01-14 | 241 | 187.9 |
| 2024-01-15 | 239 | 199.4 |
| 2024-01-16 | 253 | 215.6 |
| 2024-01-17 | 250 | 223.3 |
| 2024-01-18 | 272 | 237.1 |
| 2024-01-19 | 281 | 250.4 |
| 2024-01-20 | 262 | 256.9 |
| 2024-01-21 | 302 | 265.6 |
| 2024-01-22 | 308 | 275.4 |
| 2024-01-23 | 357 | 290.3 |
| 2024-01-24 | 322 | 300.6 |
| 2024-01-25 | 364 | 313.7 |
| 2024-01-26 | 318 | 319.0 |
| 2024-01-27 | 335 | 329.4 |
| 2024-01-28 | 327 | 333.0 |
| 2024-01-29 | 351 | 339.1 |
| 2024-01-30 | 365 | 340.3 |
| 2024-01-31 | 381 | 348.7 |
| 2024-02-01 | 369 | 349.4 |
| 2024-02-02 | 377 | 357.9 |
| 2024-02-03 | 399 | 367.0 |
| 2024-02-04 | 372 | 373.4 |
| 2024-02-05 | 394 | 379.6 |
| 2024-02-06 | 394 | 383.7 |
| 2024-02-07 | 410 | 387.9 |
| 2024-02-08 | 450 | 399.4 |
| 2024-02-09 | 404 | 403.3 |
| 2024-02-10 | 415 | 405.6 |
| 2024-02-11 | 405 | 410.3 |
| 2024-02-12 | 366 | 406.3 |
| 2024-02-13 | 423 | 410.4 |
| 2024-02-14 | 435 | 414.0 |
| 2024-02-15 | 429 | 411.0 |
| 2024-02-16 | 435 | 415.4 |
| 2024-02-17 | 448 | 420.1 |
| 2024-02-18 | 432 | 424.0 |
| 2024-02-19 | 433 | 433.6 |
| 2024-02-20 | 447 | 437.0 |
| 2024-02-21 | 420 | 434.9 |
| 2024-02-22 | 475 | 441.4 |
| 2024-02-23 | 415 | 438.6 |
| 2024-02-24 | 418 | 434.3 |
| 2024-02-25 | 383 | 427.3 |
| 2024-02-26 | 468 | 432.3 |
| 2024-02-27 | 426 | 429.3 |
| 2024-02-28 | 429 | 430.6 |
| 2024-02-29 | 440 | 425.6 |
| 2024-03-01 | 438 | 428.9 |
| 2024-03-02 | 447 | 433.0 |
| 2024-03-03 | 495 | 449.0 |
| 2024-03-04 | 453 | 446.9 |
| 2024-03-05 | 442 | 449.1 |
| 2024-03-06 | 448 | 451.9 |
| 2024-03-07 | 421 | 449.1 |
| 2024-03-08 | 426 | 447.4 |
| 2024-03-09 | 420 | 443.6 |
| 2024-03-10 | 450 | 437.1 |
| 2024-03-11 | 469 | 439.4 |
| 2024-03-12 | 441 | 439.3 |
| 2024-03-13 | 442 | 438.4 |
| 2024-03-14 | 456 | 443.4 |
| 2024-03-15 | 482 | 451.4 |
| 2024-03-16 | 448 | 455.4 |
| 2024-03-17 | 457 | 456.4 |
| 2024-03-18 | 436 | 451.7 |
| 2024-03-19 | 438 | 451.3 |
| 2024-03-20 | 458 | 453.6 |
| 2024-03-21 | 464 | 454.7 |
| 2024-03-22 | 455 | 450.9 |
| 2024-03-23 | 474 | 454.6 |
| 2024-03-24 | 475 | 457.1 |
| 2024-03-25 | 467 | 461.6 |
| 2024-03-26 | 487 | 468.6 |
| 2024-03-27 | 492 | 473.4 |
| 2024-03-28 | 511 | 480.1 |
| 2024-03-29 | 438 | 477.7 |
| 2024-03-30 | 484 | 479.1 |
| 2024-03-31 | 479 | 479.7 |
| 2024-04-01 | 453 | 477.7 |
| 2024-04-02 | 427 | 469.1 |
| 2024-04-03 | 450 | 463.1 |
| 2024-04-04 | 474 | 457.9 |
| 2024-04-05 | 453 | 460.0 |
| 2024-04-06 | 503 | 462.7 |
| 2024-04-07 | 470 | 461.4 |
| 2024-04-08 | 444 | 460.1 |
| 2024-04-09 | 466 | 465.7 |
| 2024-04-10 | 496 | 472.3 |
| 2024-04-11 | 445 | 468.1 |
| 2024-04-12 | 476 | 471.4 |
| 2024-04-13 | 480 | 468.1 |
| 2024-04-14 | 473 | 468.6 |
| 2024-04-15 | 458 | 470.6 |
| 2024-04-16 | 472 | 471.4 |
| 2024-04-17 | 469 | 467.6 |
| 2024-04-18 | 477 | 472.1 |
| 2024-04-19 | 457 | 469.4 |
| 2024-04-20 | 461 | 466.7 |
| 2024-04-21 | 448 | 463.1 |
| 2024-04-22 | 482 | 466.6 |
| 2024-04-23 | 448 | 463.1 |
| 2024-04-24 | 454 | 461.0 |
| 2024-04-25 | 447 | 456.7 |
| 2024-04-26 | 389 | 447.0 |
| 2024-04-27 | 505 | 453.3 |
| 2024-04-28 | 466 | 455.9 |
| 2024-04-29 | 495 | 457.7 |
| 2024-04-30 | 468 | 460.6 |
| 2024-05-01 | 490 | 465.7 |
| 2024-05-02 | 485 | 471.1 |
| 2024-05-03 | 471 | 482.9 |
| 2024-05-04 | 454 | 475.6 |
| 2024-05-05 | 453 | 473.7 |
| 2024-05-06 | 479 | 471.4 |
| 2024-05-07 | 441 | 467.6 |
| 2024-05-08 | 493 | 468.0 |
| 2024-05-09 | 428 | 459.9 |
| 2024-05-10 | 477 | 460.7 |
| 2024-05-11 | 456 | 461.0 |
| 2024-05-12 | 486 | 465.7 |
| 2024-05-13 | 451 | 461.7 |
| 2024-05-14 | 468 | 465.6 |
| 2024-05-15 | 485 | 464.4 |
| 2024-05-16 | 443 | 466.6 |
| 2024-05-17 | 495 | 469.1 |
| 2024-05-18 | 502 | 475.7 |
| 2024-05-19 | 477 | 474.4 |
| 2024-05-20 | 485 | 479.3 |
| 2024-05-21 | 479 | 480.9 |
| 2024-05-22 | 499 | 482.9 |
| 2024-05-23 | 459 | 485.1 |
| 2024-05-24 | 474 | 482.1 |
| 2024-05-25 | 434 | 472.4 |
| 2024-05-26 | 471 | 471.6 |
| 2024-05-27 | 448 | 466.3 |
| 2024-05-28 | 478 | 466.1 |
| 2024-05-29 | 485 | 464.1 |
| 2024-05-30 | 441 | 461.6 |
| 2024-05-31 | 445 | 457.4 |
| 2024-06-01 | 448 | 459.4 |
| 2024-06-02 | 477 | 460.3 |
| 2024-06-03 | 475 | 464.1 |
| 2024-06-04 | 463 | 462.0 |
| 2024-06-05 | 458 | 458.1 |
| 2024-06-06 | 465 | 461.6 |
| 2024-06-07 | 470 | 465.1 |
| 2024-06-08 | 463 | 467.3 |
| 2024-06-09 | 450 | 463.4 |
| 2024-06-10 | 441 | 458.6 |
| 2024-06-11 | 461 | 458.3 |
| 2024-06-12 | 463 | 459.0 |
| 2024-06-13 | 441 | 455.6 |
| 2024-06-14 | 413 | 447.4 |
| 2024-06-15 | 433 | 443.1 |
| 2024-06-16 | 459 | 444.4 |
| 2024-06-17 | 422 | 441.7 |
| 2024-06-18 | 439 | 438.6 |
| 2024-06-19 | 420 | 432.4 |
| 2024-06-20 | 464 | 435.7 |
| 2024-06-21 | 452 | 441.3 |
| 2024-06-22 | 400 | 436.6 |
| 2024-06-23 | 444 | 434.4 |
| 2024-06-24 | 467 | 440.9 |
| 2024-06-25 | 446 | 441.9 |
| 2024-06-26 | 463 | 448.0 |
| 2024-06-27 | 463 | 447.9 |
| 2024-06-28 | 466 | 449.9 |
| 2024-06-29 | 470 | 459.9 |
| 2024-06-30 | 443 | 459.7 |
Signal → The MA smooths weekly noise and shows steady growth to a ~460 DAU plateau. The flat end is saturation, not a data artifact — sessions are right-truncated, not clamped to the last day.
12 Top-2 revenue users per country (QUALIFY)
Question: Who are the two highest-revenue users in each country?
Approach: aggregate revenue per (country, user); QUALIFY keeps only the
| country | user_id | revenue |
|---|---|---|
| BY | 11458 | 70.41 |
| BY | 1551 | 68.30 |
| KZ | 826 | 77.43 |
| KZ | 10661 | 62.55 |
| Other | 18437 | 86.82 |
| Other | 10787 | 67.59 |
| RU | 8596 | 131.48 |
| RU | 6181 | 103.51 |
| UA | 8680 | 70.43 |
| UA | 453 | 66.92 |
Signal → QUALIFY filters after window functions but before SELECT — a modern DuckDB idiom that removes a wrapping subquery.
13 Monthly revenue by category (PIVOT)
Question: How does revenue split across product categories month by month?
Approach: aggregate revenue per (month, category), then PIVOT so each
| month | beauty | books | clothing | electronics | home | sports |
|---|---|---|---|---|---|---|
| 2024-01-01 | 466.84 | 344.01 | 432.00 | 385.19 | 385.64 | 455.35 |
| 2024-02-01 | 648.97 | 664.07 | 754.60 | 701.15 | 758.01 | 521.80 |
| 2024-03-01 | 611.16 | 508.99 | 930.18 | 1134.15 | 727.85 | 919.15 |
| 2024-04-01 | 560.09 | 622.41 | 972.63 | 981.57 | 753.31 | 718.79 |
| 2024-05-01 | 768.52 | 532.49 | 876.49 | 946.01 | 776.19 | 883.96 |
| 2024-06-01 | 869.45 | 784.77 | 779.79 | 656.84 | 717.07 | 644.56 |
Signal → Revenue mix shifts month to month; June is up across every category. PIVOT turns long-form revenue into a readable category-by-month matrix.
14 Subscription MRR (recursive CTE)
Question: What is the monthly recurring revenue (MRR) from subscriptions,
Approach: MRR convention — a monthly plan books its full amount each month;
| month | mrr | active_subs |
|---|---|---|
| 2024-01-01 | 169.86 | 18 |
| 2024-02-01 | 522.08 | 56 |
| 2024-03-01 | 969.22 | 104 |
| 2024-04-01 | 1411.37 | 152 |
| 2024-05-01 | 1918.45 | 206 |
| 2024-06-01 | 2500.47 | 268 |
Signal → MRR grows ~15x Jan->Jun (169 -> 2500) as subscriptions compound. A recursive CTE expands each sub into one row per billing month — annual plans recognized as ARR/12.
15 Order amount distribution (median/p90/p99)
Question: What is the spend distribution per product category, and where
Approach: MEDIAN() and QUANTILE_CONT(amount, k) window/aggregate functions;
| product_category | orders | median_amount | p90 | p99 | total_revenue |
|---|---|---|---|---|---|
| electronics | 173 | 25.34 | 45.10 | 82.63 | 4804.91 |
| clothing | 163 | 25.02 | 52.51 | 74.50 | 4745.69 |
| sports | 156 | 22.95 | 43.36 | 75.47 | 4143.61 |
| home | 152 | 23.88 | 46.05 | 62.81 | 4118.07 |
| beauty | 154 | 24.36 | 39.11 | 64.35 | 3925.03 |
| books | 130 | 23.65 | 43.50 | 71.01 | 3456.74 |
Signal → Medians sit at $21-25 but p99 reaches $63-93 — a fat tail. A p99 AOV guard catches outliers; median (not mean) is the honest central AOV.
16 Sessionization & session depth
Question: Can we reconstruct user sessions from raw event timestamps alone
Approach: gaps-and-islands on event_time — a new session starts whenever the
| metric | value |
|---|---|
| derived_sessions | 79700 |
| true_sessions | 80000 |
| fidelity_pct | 99.6 |
| merged_pairs | 298 |
| sessions_1_event | 14142 |
| sessions_2_3_events | 58784 |
| sessions_4_5_events | 6711 |
| sessions_6plus_events | 63 |
| median_events_per_session | 2.0 |
| median_duration_min | 0.0 |
Signal → A 30-min inactivity gap reconstructs the 80k pre-assigned sessions with 99.6% fidelity (298 sub-30-min same-day sessions merged, none split). Median session = 2 events — depth, not duration, is the engagement signal.
17 Weekly lifecycle composition
Question: What is the weekly mix of new / returning / resurrecting / dormant
Approach: weekly grain (Monday-start). A user-week is: new = first-ever
| wk | new_users | returning_users | resurrecting_users | dormant_users | active_users | new_pct | returning_pct | resurrecting_pct | dormant_pct |
|---|---|---|---|---|---|---|---|---|---|
| 2024-01-01 | 333 | 0 | 0 | 0 | 333 | 100.0 | 0.0 | 0.0 | 0.0 |
| 2024-01-08 | 655 | 231 | 0 | 102 | 886 | 73.9 | 26.1 | 0.0 | 11.5 |
| 2024-01-15 | 692 | 529 | 63 | 357 | 1284 | 53.9 | 41.2 | 4.9 | 27.8 |
| 2024-01-22 | 745 | 725 | 184 | 559 | 1654 | 45.0 | 43.8 | 11.1 | 33.8 |
| 2024-01-29 | 748 | 811 | 291 | 843 | 1850 | 40.4 | 43.8 | 15.7 | 45.6 |
| 2024-02-05 | 769 | 863 | 440 | 987 | 2072 | 37.1 | 41.7 | 21.2 | 47.6 |
| 2024-02-12 | 757 | 921 | 524 | 1151 | 2202 | 34.4 | 41.8 | 23.8 | 52.3 |
| 2024-02-19 | 740 | 919 | 587 | 1283 | 2246 | 32.9 | 40.9 | 26.1 | 57.1 |
| 2024-02-26 | 778 | 894 | 665 | 1352 | 2337 | 33.3 | 38.3 | 28.5 | 57.9 |
| 2024-03-04 | 744 | 910 | 694 | 1427 | 2348 | 31.7 | 38.8 | 29.6 | 60.8 |
| 2024-03-11 | 765 | 912 | 749 | 1436 | 2426 | 31.5 | 37.6 | 30.9 | 59.2 |
| 2024-03-18 | 770 | 929 | 749 | 1497 | 2448 | 31.5 | 37.9 | 30.6 | 61.2 |
| 2024-03-25 | 794 | 958 | 806 | 1490 | 2558 | 31.0 | 37.5 | 31.5 | 58.2 |
| 2024-04-01 | 726 | 977 | 749 | 1581 | 2452 | 29.6 | 39.8 | 30.5 | 64.5 |
| 2024-04-08 | 780 | 944 | 759 | 1508 | 2483 | 31.4 | 38.0 | 30.6 | 60.7 |
| 2024-04-15 | 733 | 977 | 781 | 1506 | 2491 | 29.4 | 39.2 | 31.4 | 60.5 |
| 2024-04-22 | 757 | 913 | 804 | 1578 | 2474 | 30.6 | 36.9 | 32.5 | 63.8 |
| 2024-04-29 | 752 | 942 | 822 | 1532 | 2516 | 29.9 | 37.4 | 32.7 | 60.9 |
| 2024-05-06 | 764 | 944 | 785 | 1572 | 2493 | 30.6 | 37.9 | 31.5 | 63.1 |
| 2024-05-13 | 793 | 944 | 798 | 1549 | 2535 | 31.3 | 37.2 | 31.5 | 61.1 |
| 2024-05-20 | 787 | 951 | 792 | 1584 | 2530 | 31.1 | 37.6 | 31.3 | 62.6 |
| 2024-05-27 | 722 | 948 | 839 | 1582 | 2509 | 28.8 | 37.8 | 33.4 | 63.1 |
| 2024-06-03 | 750 | 952 | 815 | 1557 | 2517 | 29.8 | 37.8 | 32.4 | 61.9 |
| 2024-06-10 | 709 | 910 | 808 | 1607 | 2427 | 29.2 | 37.5 | 33.3 | 66.2 |
| 2024-06-17 | 712 | 863 | 812 | 1564 | 2387 | 29.8 | 36.2 | 34.0 | 65.5 |
| 2024-06-24 | 763 | 888 | 793 | 1499 | 2444 | 31.2 | 36.3 | 32.4 | 61.3 |
Signal → New users fall from 100% to ~30% of the weekly base while resurrecting grows to ~33% and dormant climbs to ~60% of active — reactivation does as much work as acquisition.
18 Cohort revenue retention (triangle)
Question: How does revenue from each signup cohort evolve by months-since-
Approach: cohort = signup month; period = months since signup. Per-period
| cohort | period | paying_users | revenue | pct_of_period0 | cum_revenue | ltv_per_user |
|---|---|---|---|---|---|---|
| 2024-01-01 | 0 | 89 | 2469.03 | 100.0 | 2469.03 | 0.71 |
| 2024-01-01 | 1 | 79 | 2117.46 | 85.8 | 4586.49 | 1.33 |
| 2024-01-01 | 2 | 14 | 481.53 | 19.5 | 5068.02 | 1.47 |
| 2024-01-01 | 3 | 4 | 149.76 | 6.1 | 5217.78 | 1.51 |
| 2024-01-01 | 4 | 2 | 46.37 | 1.9 | 5264.15 | 1.52 |
| 2024-02-01 | 0 | 70 | 1931.14 | 100.0 | 1931.14 | 0.60 |
| 2024-02-01 | 1 | 60 | 1561.27 | 80.8 | 3492.41 | 1.09 |
| 2024-02-01 | 2 | 11 | 294.85 | 15.3 | 3787.26 | 1.18 |
| 2024-02-01 | 3 | 4 | 120.06 | 6.2 | 3907.32 | 1.22 |
| 2024-03-01 | 0 | 93 | 2788.68 | 100.0 | 2788.68 | 0.82 |
| 2024-03-01 | 1 | 62 | 1877.92 | 67.3 | 4666.60 | 1.37 |
| 2024-03-01 | 2 | 14 | 340.23 | 12.2 | 5006.83 | 1.47 |
| 2024-03-01 | 3 | 5 | 105.44 | 3.8 | 5112.27 | 1.50 |
| 2024-04-01 | 0 | 88 | 2286.27 | 100.0 | 2286.27 | 0.68 |
| 2024-04-01 | 1 | 70 | 2003.19 | 87.6 | 4289.46 | 1.28 |
| 2024-04-01 | 2 | 10 | 238.71 | 10.4 | 4528.17 | 1.35 |
| 2024-05-01 | 0 | 88 | 2273.81 | 100.0 | 2273.81 | 0.68 |
| 2024-05-01 | 1 | 65 | 1803.23 | 79.3 | 4077.04 | 1.21 |
| 2024-06-01 | 0 | 79 | 2305.10 | 100.0 | 2305.10 | 0.72 |
Signal → Revenue retention holds at ~67-88% into month 1 but collapses to ~10-20% by month 2 — one purchase is effectively lifetime. Monetization has no repeat engine.
19 Repeat purchase & time between orders
Question: How repeatable is purchase behavior, and how quickly do repeat
Approach: repeat rate = share of buyers with >=2 orders. Days between
| metric | value |
|---|---|
| buyers | 896 |
| repeat_buyers | 31 |
| repeat_rate_pct | 3.5 |
| median_days_between | 9.0 |
| p90_days_between | 28.0 |
| buyers_1_order | 865 |
| buyers_2_orders | 30 |
| buyers_3_orders | 1 |
| buyers_4plus_orders | 0 |
Signal → Only 3.5% of buyers ever return (896 buyers, 31 repeat). This is a one-and-done purchase engine — the repeat lever is the biggest monetization gap.
20 RFM segmentation (NTILE)
Question: Which buyer segments deserve the most attention, and where does
Approach: NTILE(5) on recency (days since last order, inverted so 5 = most
| segment | buyers | revenue | pct_of_buyers | pct_of_revenue |
|---|---|---|---|---|
| Loyal | 181 | 5169.10 | 20.2 | 20.5 |
| Regular | 179 | 5133.16 | 20.0 | 20.4 |
| Hibernating | 172 | 4735.40 | 19.2 | 18.8 |
| Champions | 166 | 4695.36 | 18.5 | 18.6 |
| At Risk | 176 | 4636.58 | 19.6 | 18.4 |
| Big Spenders | 11 | 526.70 | 1.2 | 2.1 |
| Promising | 11 | 297.75 | 1.2 | 1.2 |
Signal → Revenue splits ~evenly across segments; Big Spenders are just 1.2%. The frequency axis barely discriminates because repeat rate is 3.5% — RFM here is mostly a recency story.
21 Subscription churn (logo & MRR)
Question: What is monthly logo and MRR churn, and how fast does the sub base leak?
Approach: active subs at month start = started before the month and not yet
| m | subs_at_start | churned | logo_churn_pct | mrr_at_start | mrr_churned | mrr_churn_pct |
|---|---|---|---|---|---|---|
| 2024-02-01 | 18 | 1 | 5.56 | 169.86 | 9.99 | 5.88 |
| 2024-03-01 | 54 | 7 | 12.96 | 502.10 | 64.95 | 12.94 |
| 2024-04-01 | 91 | 13 | 14.29 | 846.82 | 122.40 | 14.45 |
| 2024-05-01 | 119 | 14 | 11.76 | 1101.63 | 129.90 | 11.79 |
| 2024-06-01 | 153 | 23 | 15.03 | 1421.36 | 209.84 | 14.76 |
Signal → Logo churn grows from 5.6% to ~15% as the base matures; MRR churn tracks it. MRR compounding (case 14) is hiding a fast-leaking bucket — a churn alarm.
22 Refunds & net revenue
Question: How much gross revenue is refunded, and what is net revenue by month?
Approach: left join refunds to orders (an order can be refunded once, full or
| m | gross | refunded | net | refund_rate_pct |
|---|---|---|---|---|
| 2024-01-01 | 2469.03 | 53.74 | 2415.29 | 2.18 |
| 2024-02-01 | 4048.60 | 244.06 | 3804.54 | 6.03 |
| 2024-03-01 | 4831.48 | 137.63 | 4693.85 | 2.85 |
| 2024-04-01 | 4608.80 | 215.09 | 4393.71 | 4.67 |
| 2024-05-01 | 4783.66 | 203.11 | 4580.55 | 4.25 |
| 2024-06-01 | 4452.48 | 177.12 | 4275.36 | 3.98 |
Signal → ~4.1% of gross is refunded (2.2-6.0%/month, February worst). Report net, not gross — refund rate is a revenue-quality number.
23 Pareto revenue concentration
Question: What share of revenue comes from the top deciles of buyers — and is
Approach: per-buyer lifetime revenue; NTILE(10) on revenue descending; report
| decile | buyers | revenue | pct_of_buyers | pct_of_revenue | cum_pct |
|---|---|---|---|---|---|
| 1 | 90 | 5626.19 | 10.04 | 22.33 | 22.33 |
| 2 | 90 | 3847.13 | 10.04 | 15.27 | 37.60 |
| 3 | 90 | 3171.72 | 10.04 | 12.59 | 50.19 |
| 4 | 90 | 2714.34 | 10.04 | 10.77 | 60.96 |
| 5 | 90 | 2372.17 | 10.04 | 9.42 | 70.38 |
| 6 | 90 | 2082.67 | 10.04 | 8.27 | 78.65 |
| 7 | 89 | 1759.27 | 9.93 | 6.98 | 85.63 |
| 8 | 89 | 1510.16 | 9.93 | 5.99 | 91.62 |
| 9 | 89 | 1239.70 | 9.93 | 4.92 | 96.54 |
| 10 | 89 | 870.70 | 9.93 | 3.46 | 100.00 |
Signal → Top decile = 22.3% of revenue, top 3 deciles = 50.2%. No 80/20 — there is no whale tier, so don't build a VIP product for one.
24 Daily revenue anomaly detection
Question: Which days deviate significantly from their own recent baseline?
Approach: daily revenue; trailing-14-day baseline median and MAD (robust to
| d | daily_revenue | baseline_median | z_mad | anomalous |
|---|---|---|---|---|
| 2024-01-11 | 109.30 | 72.66 | 1.35 | |
| 2024-01-12 | 50.21 | 75.20 | -0.77 | |
| 2024-01-13 | 57.51 | 72.66 | -0.44 | |
| 2024-01-14 | 69.58 | 65.21 | 0.14 | |
| 2024-01-15 | 75.80 | 69.58 | 0.23 | |
| 2024-01-16 | 117.14 | 71.12 | 2.05 | |
| 2024-01-17 | 88.46 | 72.66 | 0.58 | |
| 2024-01-18 | 61.05 | 74.23 | -0.56 | |
| 2024-01-19 | 134.10 | 74.23 | 2.61 | |
| 2024-01-20 | 37.71 | 76.77 | -1.70 | |
| 2024-01-21 | 113.89 | 72.69 | 1.80 | |
| 2024-01-22 | 87.24 | 77.45 | 0.32 | |
| 2024-01-23 | 45.82 | 77.72 | -1.05 | |
| 2024-01-24 | 62.13 | 77.72 | -0.52 | |
| 2024-01-25 | 232.81 | 72.69 | 5.30 | YES |
| 2024-01-26 | 98.67 | 72.69 | 0.86 | |
| 2024-01-27 | 91.38 | 81.52 | 0.32 | |
| 2024-01-28 | 156.95 | 87.85 | 2.23 | |
| 2024-01-29 | 95.25 | 89.92 | 0.12 | |
| 2024-01-30 | 157.43 | 93.32 | 1.49 | |
| 2024-01-31 | 77.56 | 93.32 | -0.37 | |
| 2024-02-01 | 204.85 | 93.32 | 2.60 | |
| 2024-02-02 | 51.36 | 96.96 | -0.87 | |
| 2024-02-03 | 61.55 | 93.32 | -0.60 | |
| 2024-02-04 | 123.75 | 93.32 | 0.64 | |
| 2024-02-05 | 149.24 | 93.32 | 1.21 | |
| 2024-02-06 | 155.57 | 96.96 | 1.24 | |
| 2024-02-07 | 124.72 | 111.21 | 0.24 | |
| 2024-02-08 | 115.46 | 124.24 | -0.15 | |
| 2024-02-09 | 69.99 | 119.60 | -1.08 | |
| 2024-02-10 | 166.93 | 119.60 | 0.83 | |
| 2024-02-11 | 99.29 | 124.24 | -0.36 | |
| 2024-02-12 | 178.21 | 119.60 | 1.02 | |
| 2024-02-13 | 150.83 | 124.24 | 0.39 | |
| 2024-02-14 | 108.69 | 124.24 | -0.27 | |
| 2024-02-15 | 87.96 | 124.24 | -0.63 | |
| 2024-02-16 | 171.69 | 119.60 | 1.03 | |
| 2024-02-17 | 84.97 | 124.24 | -0.78 | |
| 2024-02-18 | 134.50 | 124.24 | 0.18 | |
| 2024-02-19 | 228.69 | 129.61 | 1.77 | |
| 2024-02-20 | 248.80 | 129.61 | 2.13 | |
| 2024-02-21 | 212.57 | 129.61 | 1.48 | |
| 2024-02-22 | 204.46 | 142.67 | 0.96 | |
| 2024-02-23 | 84.84 | 158.88 | -1.03 | |
| 2024-02-24 | 105.58 | 158.88 | -0.72 | |
| 2024-02-25 | 70.54 | 142.67 | -0.92 | |
| 2024-02-26 | 125.41 | 142.67 | -0.21 | |
| 2024-02-27 | 260.64 | 129.95 | 1.67 | |
| 2024-02-28 | 110.72 | 129.95 | -0.23 | |
| 2024-02-29 | 156.79 | 129.95 | 0.31 | |
| 2024-03-01 | 143.16 | 145.64 | -0.03 | |
| 2024-03-02 | 199.81 | 138.83 | 0.71 | |
| 2024-03-03 | 148.79 | 149.98 | -0.01 | |
| 2024-03-04 | 200.81 | 152.79 | 0.53 | |
| 2024-03-05 | 172.74 | 152.79 | 0.24 | |
| 2024-03-06 | 161.61 | 152.79 | 0.12 | |
| 2024-03-07 | 197.13 | 152.79 | 0.80 | |
| 2024-03-08 | 218.72 | 152.79 | 1.25 | |
| 2024-03-09 | 88.48 | 159.20 | -1.34 | |
| 2024-03-10 | 188.60 | 159.20 | 0.56 | |
| 2024-03-11 | 124.94 | 167.18 | -1.01 | |
| 2024-03-12 | 204.16 | 167.18 | 0.70 | |
| 2024-03-13 | 87.42 | 167.18 | -1.62 | |
| 2024-03-14 | 58.63 | 167.18 | -1.85 | |
| 2024-03-15 | 216.35 | 167.18 | 0.77 | |
| 2024-03-16 | 170.31 | 180.67 | -0.15 | |
| 2024-03-17 | 96.77 | 171.53 | -1.16 | |
| 2024-03-18 | 124.18 | 171.53 | -0.69 | |
| 2024-03-19 | 158.66 | 165.96 | -0.11 | |
| 2024-03-20 | 57.02 | 160.14 | -1.52 | |
| 2024-03-21 | 91.76 | 141.80 | -0.70 | |
| 2024-03-22 | 153.10 | 124.56 | 0.39 | |
| 2024-03-23 | 398.83 | 124.56 | 3.83 | YES |
| 2024-03-24 | 201.40 | 139.02 | 0.87 | |
| 2024-03-25 | 46.28 | 139.02 | -1.26 | |
| 2024-03-26 | 141.26 | 138.64 | 0.03 | |
| 2024-03-27 | 120.47 | 132.72 | -0.15 | |
| 2024-03-28 | 245.27 | 132.72 | 1.53 | |
| 2024-03-29 | 91.06 | 147.18 | -0.76 | |
| 2024-03-30 | 264.32 | 132.72 | 1.67 | |
| 2024-03-31 | 59.44 | 132.72 | -0.83 | |
| 2024-04-01 | 90.18 | 132.72 | -0.48 | |
| 2024-04-02 | 97.60 | 130.87 | -0.38 | |
| 2024-04-03 | 201.19 | 109.04 | 1.05 | |
| 2024-04-04 | 85.90 | 130.87 | -0.51 | |
| 2024-04-05 | 188.34 | 130.87 | 0.65 | |
| 2024-04-06 | 102.91 | 130.87 | -0.31 | |
| 2024-04-07 | 384.06 | 111.69 | 3.23 | YES |
| 2024-04-08 | 108.38 | 111.69 | -0.04 | |
| 2024-04-09 | 77.30 | 114.43 | -0.50 | |
| 2024-04-10 | 153.91 | 105.65 | 0.64 | |
| 2024-04-11 | 168.40 | 105.65 | 0.81 | |
| 2024-04-12 | 91.44 | 105.65 | -0.18 | |
| 2024-04-13 | 165.30 | 105.65 | 0.86 | |
| 2024-04-14 | 148.16 | 105.65 | 0.62 | |
| 2024-04-15 | 141.79 | 128.27 | 0.21 | |
| 2024-04-16 | 174.01 | 144.98 | 0.45 | |
| 2024-04-17 | 153.54 | 151.04 | 0.04 | |
| 2024-04-18 | 269.63 | 150.85 | 2.01 | |
| 2024-04-19 | 94.37 | 153.73 | -1.01 | |
| 2024-04-20 | 63.82 | 150.85 | -1.47 | |
| 2024-04-21 | 218.53 | 150.85 | 1.01 | |
| 2024-04-22 | 168.26 | 150.85 | 0.26 | |
| 2024-04-23 | 196.61 | 153.73 | 0.64 | |
| 2024-04-24 | 105.66 | 159.61 | -0.80 | |
| 2024-04-25 | 203.88 | 159.42 | 0.62 | |
| 2024-04-26 | 120.42 | 159.42 | -0.60 | |
| 2024-04-27 | 301.12 | 159.42 | 2.19 | |
| 2024-04-28 | 98.96 | 160.90 | -0.96 | |
| 2024-04-29 | 113.30 | 160.90 | -0.65 | |
| 2024-04-30 | 121.83 | 160.90 | -0.52 | |
| 2024-05-01 | 233.38 | 137.69 | 1.27 | |
| 2024-05-02 | 241.39 | 145.05 | 1.15 | |
| 2024-05-03 | 192.41 | 145.05 | 0.56 | |
| 2024-05-04 | 149.37 | 180.34 | -0.41 | |
| 2024-05-05 | 161.99 | 180.34 | -0.26 | |
| 2024-05-06 | 84.66 | 165.13 | -1.18 | |
| 2024-05-07 | 52.09 | 155.68 | -1.47 | |
| 2024-05-08 | 299.10 | 135.60 | 2.17 | |
| 2024-05-09 | 92.06 | 155.68 | -0.78 | |
| 2024-05-10 | 247.91 | 135.60 | 1.21 | |
| 2024-05-11 | 42.86 | 155.68 | -1.06 | |
| 2024-05-12 | 173.38 | 135.60 | 0.35 | |
| 2024-05-13 | 39.68 | 155.68 | -1.09 | |
| 2024-05-14 | 176.65 | 155.68 | 0.16 | |
| 2024-05-15 | 157.29 | 167.69 | -0.08 | |
| 2024-05-16 | 177.48 | 159.64 | 0.17 | |
| 2024-05-17 | 68.38 | 159.64 | -1.11 | |
| 2024-05-18 | 132.11 | 153.33 | -0.20 | |
| 2024-05-19 | 129.36 | 144.70 | -0.14 | |
| 2024-05-20 | 232.00 | 130.74 | 0.95 | |
| 2024-05-21 | 154.73 | 144.70 | 0.09 | |
| 2024-05-22 | 303.32 | 156.01 | 1.96 | |
| 2024-05-23 | 116.43 | 156.01 | -0.53 | |
| 2024-05-24 | 57.89 | 156.01 | -1.71 | |
| 2024-05-25 | 130.25 | 143.42 | -0.23 | |
| 2024-05-26 | 208.17 | 143.42 | 1.48 | |
| 2024-05-27 | 122.40 | 143.42 | -0.47 | |
| 2024-05-28 | 139.35 | 143.42 | -0.13 | |
| 2024-05-29 | 86.97 | 135.73 | -1.56 | |
| 2024-05-30 | 194.49 | 131.18 | 1.40 | |
| 2024-05-31 | 186.11 | 131.18 | 0.84 | |
| 2024-06-01 | 91.79 | 135.73 | -0.67 | |
| 2024-06-02 | 50.48 | 134.80 | -1.23 | |
| 2024-06-03 | 178.81 | 134.80 | 0.57 | |
| 2024-06-04 | 90.41 | 134.80 | -0.65 | |
| 2024-06-05 | 132.78 | 126.33 | 0.09 | |
| 2024-06-06 | 34.78 | 126.33 | -1.40 | |
| 2024-06-07 | 145.36 | 126.33 | 0.28 | |
| 2024-06-08 | 149.11 | 131.51 | 0.27 | |
| 2024-06-09 | 91.34 | 136.07 | -0.68 | |
| 2024-06-10 | 131.26 | 127.59 | 0.06 | |
| 2024-06-11 | 93.59 | 132.02 | -0.59 | |
| 2024-06-12 | 144.82 | 112.43 | 0.49 | |
| 2024-06-13 | 251.10 | 132.02 | 1.83 | |
| 2024-06-14 | 338.63 | 132.02 | 3.17 | YES |
| 2024-06-15 | 226.87 | 132.02 | 1.45 | |
| 2024-06-16 | 118.39 | 138.80 | -0.31 | |
| 2024-06-17 | 47.17 | 138.80 | -1.50 | |
| 2024-06-18 | 260.64 | 132.02 | 2.10 | |
| 2024-06-19 | 221.96 | 138.80 | 1.35 | |
| 2024-06-20 | 165.60 | 145.09 | 0.22 | |
| 2024-06-21 | 93.92 | 147.24 | -0.86 | |
| 2024-06-22 | 136.84 | 146.97 | -0.14 | |
| 2024-06-23 | 153.24 | 140.83 | 0.17 | |
| 2024-06-24 | 199.12 | 149.03 | 0.74 | |
| 2024-06-25 | 99.17 | 159.42 | -0.79 | |
| 2024-06-26 | 382.69 | 159.42 | 2.65 | |
| 2024-06-27 | 126.40 | 182.36 | -0.53 | |
| 2024-06-28 | 55.39 | 159.42 | -1.21 | |
| 2024-06-29 | 66.98 | 145.04 | -0.91 | |
| 2024-06-30 | 173.84 | 131.62 | 0.49 |
Signal → Median+MAD z-score on a trailing-14-day baseline flags exactly 4 days (Jan 25 z=5.3, Mar 23, Apr 7, Jun 14) — a promo pattern to investigate, not noise.
25 Purchase → subscription conversion
Question: Of purchasers, who converts to a paid subscription, and how fast?
Approach: subscribers / purchasers by plan; days from first order to
| metric | value |
|---|---|
| purchasers | 896 |
| subscribers | 268 |
| conversion_pct | 29.9 |
| monthly_subs | 197 |
| annual_subs | 71 |
| median_days_to_convert | 3.0 |
| p90_days_to_convert | 6.0 |
Signal → 29.9% of purchasers subscribe within a week (median 3 days, p90 6); 26% pick annual. The upsell window is narrow — hit it fast.
Generated from 25 SQL cases against data/analytics.duckdb ·
Reproduce: uv run python data/generate_data.py then
uv run python scripts/report.py.