TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 27

Cohort, RFM and Funnel Analysis: practice questions

400 questions in four levels, all on RetailMart, the practice database of this course. Write every query yourself, get it wrong, read the error, fix it. That is how it sticks.

Open the SQL PlaygroundPut RetailMart on your laptop
Answers are coming soon. Post your query in the WhatsApp Community or Discord and we will check it together.

Core syntax, applied directly

CONCEPTUAL

  1. Q1What is a cohort (group of customers anchored to a common starting date)?
  2. Q2What does signup_month x months-since-signup measure (retention grid)?
  3. Q3What is period_index (months since signup, 0 = signup month)?
  4. Q4What does "Month-1 retention" mean in a cohort?
  5. Q5Why is a cohort triangle the standard shape (cells empty in the future)?
  6. Q6What does RFM stand for (Recency, Frequency, Monetary)?
  7. Q7How does NTILE(5) (Topic 16) produce a 1..5 score per dimension?
  8. Q8Why is "Recency" usually ordered so that low days = high score?
  9. Q9What does a 555 RFM score mean for a customer?
  10. Q10What does "111" RFM score mean?
  11. Q11What is a funnel (counts of users at each step)?
  12. Q12What is "drop-off" between two funnel steps?
  13. Q13What is CLV (customer lifetime value), as a single number?
  14. Q14Difference between MRR-style and per-customer CLV (concept).
  15. Q15Period-over-period growth: MoM / QoQ / YoY - what do they measure?
  16. Q16Why does cohort analysis answer "are new customers staying"?
  17. Q17Why does RFM segmentation drive marketing campaign targeting?
  18. Q18Why does a funnel reveal the worst step in a flow?
  19. Q19What's the anchor date for cohort (typically first order or registration)?
  20. Q20How does a date spine (Topic 23) help with cohort completeness?
  21. Q21Why do we need DISTINCT-customer counts in cohort cells (not order counts)?
  22. Q22What does "active in period N" mean (placed an order in that month)?
  23. Q23Why does a cohort table often show as % retention, not absolute counts?
  24. Q24Why does NTILE require ORDER BY in the OVER() clause?
  25. Q25Name three RetailMart funnels worth measuring (e.g. view->cart->order).

COHORT RETENTION

  1. Q26Per customer: signup_month = date_trunc('month', registration_date).
  2. Q27Per order: order_month = date_trunc('month', order_date).
  3. Q28Per (customer, order): months-since-signup = (order_month - signup_month)/30 (concept).
  4. Q29Use AGE or date math (Topic 23) to compute period_index in months.
  5. Q30Cohort sizes: count distinct customers per signup_month.
  6. Q31Active customers in cohort at period_index 0 (signup month).
  7. Q32Active customers per (signup_month, period_index) - cohort grid raw.
  8. Q33Retention %: active / cohort_size per (signup_month, period_index).
  9. Q34Limit to period_index 0..11; tidy long format.
  10. Q35Pivot the long format to wide (signup_month x period_index, Topic 21).
  11. Q36Same as Q35 with retention shown as a percentage.
  12. Q37Month-1 retention per signup_month (a single trend line).
  13. Q38Month-3 retention per signup_month.
  14. Q39Month-6 retention per signup_month.
  15. Q40Compare Month-1 retention 2025 vs 2024 (Topic 23 trend).
  16. Q41Cohort retention per region (join customers->addresses->region).
  17. Q42Cohort retention per acquisition tier (Bronze/Silver/Gold/Platinum).
  18. Q43Cohort sizes vs total customers - sanity reconcile.
  19. Q44Use a date spine (Topic 23) to ensure all (cohort, period) cells exist.
  20. Q45Show the cohort triangle (only past cells filled).
  21. Q46Cohort revenue: revenue per (signup_month, period_index).
  22. Q47Cumulative cohort revenue per period_index (running sum, Topic 17).
  23. Q48Average revenue per customer per cohort per period.
  24. Q49Identify the strongest cohort (highest Month-3 retention).
  25. Q50Identify the weakest cohort (lowest Month-3 retention).

RFM SEGMENTATION

  1. Q51Per customer Recency: days since their last order.
  2. Q52Per customer Frequency: count of orders.
  3. Q53Per customer Monetary: SUM(net_total) lifetime.
  4. Q54Recency score: NTILE(5) over -days_since_last_order (low days -> high score).
  5. Q55Frequency score: NTILE(5) over order_count.
  6. Q56Monetary score: NTILE(5) over lifetime_revenue.
  7. Q57Combine into an RFM string score 'RFM' (e.g. '555').
  8. Q58Total RFM score: R + F + M (1..15).
  9. Q59Top customers by RFM total.
  10. Q60Champions segment: RFM = 555.
  11. Q61At-Risk segment: low R, high F/M (former big spenders going quiet).
  12. Q62New segment: R=5, F=1, M=1 (just started).
  13. Q63Hibernating segment: low R, low F, low M.
  14. Q64Count customers per RFM string score.
  15. Q65Distribution of R score, F score, M score (three histograms).
  16. Q66RFM per region (Topic 21 pivot).
  17. Q67RFM per registration cohort (Topic 23).
  18. Q68Find the median lifetime spend per RFM score (Topic 22).
  19. Q69Average orders per RFM score.
  20. Q70Top-10 customers in Champions by lifetime revenue.
  21. Q71Show how RFM scores correlate with customer tier (Bronze..Platinum).
  22. Q72Per RFM string score: pivot count by region (Topic 21).
  23. Q73Per tier: percent customers in each RFM segment label.
  24. Q74Drift: how many Champions are At-Risk this quarter vs last (Topic 23).
  25. Q75Sanity check: total customers = sum across segments (no double-count).

FUNNEL, CLV & PERIOD-OVER-PERIOD

  1. Q76Funnel step 1: distinct customers who viewed any page.
  2. Q77Funnel step 2: distinct customers who placed any order.
  3. Q78Drop-off step 1 -> step 2: % of viewers who ordered.
  4. Q79Funnel: distinct customers who viewed a product page (page_url LIKE '%product%').
  5. Q80Funnel: distinct customers who added to cart (page_url LIKE '%cart%').
  6. Q81Funnel: distinct customers who checked out (page_url LIKE '%checkout%').
  7. Q823-step funnel counts: product -> cart -> checkout (one wide row).
  8. Q83Drop-off % at each transition (cart/product, checkout/cart).
  9. Q84Sessions instead of customers: per session, did it reach each step?
  10. Q85Funnel per device_type (Topic 21 pivot).
  11. Q86Funnel per month (Topic 23 spine).
  12. Q87CLV per customer: lifetime SUM(net_total) over Delivered orders only.
  13. Q88Average CLV per region (Topic 22 also as median).
  14. Q89CLV per registration cohort (Topic 23).
  15. Q90CLV per RFM segment.
  16. Q91Period-over-period revenue: MoM growth per region (LAG, Topic 18).
  17. Q92PoP: this month vs same month last year (YoY).
  18. Q93PoP: QoQ growth per category (Topic 23 quarter + window).
  19. Q94Active-customer PoP per month per region.
  20. Q95New-customer PoP per month (first-order-month definition).
  21. Q96Cumulative revenue per cohort over months-since-signup.
  22. Q97CLV vs RFM-monetary correlation snapshot.
  23. Q98Cohort retention pivot: signup_month x period_index (Topic 21).
  24. Q99RFM dashboard: per segment count + median CLV (Topic 22).
  25. Q100Exec strip: Month-1 retention, top RFM segment count, funnel drop-off, MoM revenue.

Combined ideas, multi-step thinking

CONCEPTUAL

  1. Q1Cohort anchor choice: registration_date vs first_order_date - implications.
  2. Q2Period grain: monthly vs weekly cohorts; tradeoffs.
  3. Q3Why an explicit date spine (Topic 23) prevents missing cells.
  4. Q4Cohort triangle shape: cells above the diagonal are empty (future).
  5. Q5Retention denominator: cohort size at period 0, fixed (not rolling).
  6. Q6Retention vs revenue cohorts - different denominators.
  7. Q7Cumulative cohort LTV vs per-period LTV.
  8. Q8RFM ordering pitfalls: ensure Recency is "smaller-days = higher score".
  9. Q9Ties in NTILE: how Postgres distributes ties (Topic 16).
  10. Q10Named RFM segments: Champions, Loyal, At-Risk, Hibernating, etc.
  11. Q11Segment drift over time: customers moving between segments (Topic 23).
  12. Q12Funnel: customer-level vs session-level - when to choose which.
  13. Q13Drop-off ratio: between consecutive steps; reporting clarity.
  14. Q14Funnel per device/channel/region (Topic 21 pivot).
  15. Q15Topic 22 percentile of CLV per segment - robust segment value.
  16. Q16Topic 22 DISTINCT ON for "latest order per customer" -> R component.
  17. Q17Topic 23 spine to align cohorts cross-month.
  18. Q18Topic 25 MV strategy: which cohort tables benefit.
  19. Q19Topic 26 cleansed inputs: dedup customers before cohort sizing.
  20. Q20Reconciliation: cohort sizes sum to total customers (assertion).
  21. Q21RFM x tier matrix (Topic 21) for marketing teams.
  22. Q22Funnel completeness via spine of steps (Topic 23).
  23. Q23CLV definition choices: gross vs net; included statuses.
  24. Q24PoP zero-division safety (NULLIF).
  25. Q25Reporting Month-1 retention as a single trend (Topic 23).

COHORT TRIANGLE & LTV

  1. Q26Per customer signup_month (registration_date -> month).
  2. Q27Per (customer, order) order_month and period_index (months since signup).
  3. Q28Cohort sizes (distinct customers) per signup_month.
  4. Q29Active customers per (signup_month, period_index).
  5. Q30Retention % per (signup_month, period_index).
  6. Q31Limit to period_index 0..11 and pivot wide (Topic 21).
  7. Q32Add a date spine (Topic 23) to guarantee cells.
  8. Q33Cohort revenue per (signup_month, period_index).
  9. Q34Cumulative cohort revenue per period_index (running sum, Topic 17).
  10. Q35Average revenue per active cohort customer per period.
  11. Q36CLV per cohort: cumulative revenue / cohort size at period 0.
  12. Q37Best cohorts by Month-3 retention.
  13. Q38Best cohorts by 12-month cumulative LTV.
  14. Q39Cohort retention by region (Topic 21 pivot).
  15. Q40Cohort retention by acquisition tier.
  16. Q41Cohort retention by signup channel proxy (if any) - concept.
  17. Q42Cohort LTV per region.
  18. Q43Median spend per active cohort customer per period (Topic 22).
  19. Q44P95 spend per active cohort customer per period (Topic 22).
  20. Q45Triangle: signup_month rows x period 0..11 cols, retention values.
  21. Q46Triangle: signup_month rows x period 0..11 cols, LTV values.
  22. Q47Compare 2024 vs 2025 cohorts: Month-1 retention.
  23. Q48Compare 2024 vs 2025 cohorts: 12-month LTV.
  24. Q49Reconcile: cohort sizes sum = total customers.
  25. Q50Save the triangle as a tidy long table (cohort, period, retention, ltv).

RFM SEGMENTS & DRIFT

  1. Q51Per customer Recency (days since last order).
  2. Q52Per customer Frequency (order count).
  3. Q53Per customer Monetary (lifetime revenue).
  4. Q54Recency score NTILE(5) over -days_since_last_order.
  5. Q55Frequency score NTILE(5) over order_count.
  6. Q56Monetary score NTILE(5) over lifetime_revenue.
  7. Q57Combine to 'RFM' string score and total R+F+M.
  8. Q58Named segments: Champions, Loyal Customers, Potential Loyalists, New Customers, Promising, Need Attention, About to Sleep, At Risk, Hibernating, Lost.
  9. Q59Apply name-mapping based on score buckets (CASE).
  10. Q60Count customers per named segment.
  11. Q61Median CLV per segment (Topic 22).
  12. Q62P95 CLV per segment.
  13. Q63Per region: counts per segment (Topic 21 pivot).
  14. Q64Per tier: counts per segment.
  15. Q65Drift report: segment this quarter vs last quarter (Topic 23).
  16. Q66Promotion candidates: At Risk + high Monetary.
  17. Q67Reactivation candidates: Hibernating with > 5 historical orders.
  18. Q68New-customer pipeline: count per registration month (Topic 23).
  19. Q69Average days-since-last-order per segment.
  20. Q70Average orders per segment.
  21. Q71Average lifetime revenue per segment.
  22. Q72RFM x region heatmap (Topic 21).
  23. Q73RFM x tier heatmap.
  24. Q74Top-N customers per segment (Topic 16).
  25. Q75Reconcile: customers across segments sum = total customers.

FUNNELS & PERIOD-OVER-PERIOD

  1. Q763-step funnel: product -> cart -> checkout (distinct customers).
  2. Q774-step funnel: product -> cart -> checkout -> ordered.
  3. Q78Drop-off % per step.
  4. Q79Session funnel via session_id (per session reach).
  5. Q80Funnel per device (Topic 21 pivot).
  6. Q81Funnel per month (Topic 23 spine).
  7. Q82Funnel per region (joins via customers->addresses).
  8. Q83Funnel per registration cohort.
  9. Q84Funnel + median time-between-steps (Topic 22 + Topic 23).
  10. Q85Conversion rate per device per month.
  11. Q86CLV per cohort x per channel proxy.
  12. Q87PoP MoM revenue per region (LAG, Topic 18).
  13. Q88PoP YoY revenue per region.
  14. Q89PoP QoQ revenue per region.
  15. Q90Active-customer PoP per month per region.
  16. Q91New-customer PoP per month.
  17. Q92Reactivated-customer PoP per month.
  18. Q93Funnel conversion drift: WoW shift (Topic 23) per channel.
  19. Q94CLV per RFM segment with MoM trend.
  20. Q95Cohort x CLV table for exec dashboard.
  21. Q96Combined RFM + cohort dashboard per region.
  22. Q97Funnel JSON export (Topic 24) for product team.
  23. Q98Cohort triangle JSON export (Topic 24).
  24. Q99Reconciliation: cohort revenue total = orders revenue (Delivered only).
  25. Q100Exec strip: Month-1 retention by cohort, top RFM segments, funnel drop-off, MoM revenue per region.

Interview grade, edge cases

CONCEPTUAL

  1. Q1Cohort anchor: registration vs first_order - implications for retention shape.
  2. Q2Weekly vs monthly cohorts - when noisy/clean.
  3. Q3Cohort triangle storage shape: long vs wide; pivot in BI vs SQL.
  4. Q4Cumulative cohort LTV: when to divide by cohort size vs active size.
  5. Q5RFM ties at percentile boundaries (NTILE vs PERCENTILE_CONT, Topic 22).
  6. Q6Named-segment governance (Champions/Loyal/At-Risk) - rule book.
  7. Q7Drift accounting: customers moving segments quarter-over-quarter.
  8. Q8Funnel: customer reach vs session reach vs per-event count.
  9. Q9Drop-off math: ratio chain vs absolute deltas.
  10. Q10Time-between-steps (median/P95, Topic 22) as a funnel KPI.
  11. Q11Multi-channel/device funnel splits (Topic 21 pivot).
  12. Q12Survivor-bias trap in cohort analysis.
  13. Q13CLV horizon: 12-month vs lifetime; cap and report both.
  14. Q14PoP growth decomposition: new vs returning vs reactivated contribution.
  15. Q15Per-cohort PoP: aging vs same-period-by-cohort.
  16. Q16Spine-driven completeness for cohort and funnel reports (Topic 23).
  17. Q17Topic 26 dependency: cleansed customers/orders/sessions feed the cohort layer.
  18. Q18Topic 25 dependency: which cohort/funnel results should be MVs.
  19. Q19Plan inspection (Topic 19): cohort joins over 150k orders.
  20. Q20Index strategy (Topic 20): customer_id PK + order_date btree, view_timestamp btree.
  21. Q21JSON export (Topic 24) of triangles and funnel payloads.
  22. Q22Segmentation playback: scoring as-of historical points (point-in-time).
  23. Q23Anti-pattern: changing RFM cutoffs without versioning the segment definitions.
  24. Q24Cross-team handoff: marketing wants segments; product wants funnels.
  25. Q25Documenting the cohort/RFM/funnel rules in a runbook.

COHORT TRIANGLE + LTV

  1. Q26Monthly cohort grid (signup_month x period 0..11): distinct active customers.
  2. Q27Monthly cohort grid as retention % (active / cohort_size_at_0).
  3. Q28Cumulative cohort LTV per period_index (running sum, Topic 17).
  4. Q29Pivot the LTV grid wide (Topic 21).
  5. Q30Per region cohort grid (join customers->addresses->region).
  6. Q31Per tier cohort grid (Bronze..Platinum).
  7. Q32Weekly cohort grid (signup_week x week 0..12).
  8. Q33Spine-driven completeness (Topic 23) for all (cohort, period) cells.
  9. Q34Cohort size sanity: sum across grid columns equals total.
  10. Q35Best cohort by Month-1 retention.
  11. Q36Best cohort by Month-3 retention.
  12. Q37Best cohort by 12-month LTV.
  13. Q38Worst cohort by Month-3 retention (for diagnostics).
  14. Q39Cohort x region retention heatmap (Topic 21).
  15. Q40Cohort x tier LTV heatmap.
  16. Q41Average revenue per active customer per period (Topic 22 median per cell).
  17. Q42Reactivation rate per cohort (active in period N after gap).
  18. Q43Survival proxy: cohort customers with >=3 active months.
  19. Q44Compare 2024 vs 2025 cohorts: retention shape.
  20. Q45Compare 2024 vs 2025 cohorts: cumulative LTV shape.
  21. Q46JSON cohort export (Topic 24): {cohort, period, retention, ltv} array.
  22. Q47MV candidate (Topic 25): cohort x period retention/LTV - refresh cadence.
  23. Q48Plan check (Topic 19): cohort query on 150k orders + 50k customers.
  24. Q49Index check (Topic 20): orders(order_date, cust_id) helps cohort joins.
  25. Q50Reconciliation: monthly cohort active count vs MAU per month (Topic 23).

RFM SEGMENTS + DRIFT

  1. Q51R/F/M raw values per customer (Topic 22 latest order for R).
  2. Q52RFM scores via NTILE(5) on each (Topic 16).
  3. Q53Combine to 'RFM' string + total R+F+M.
  4. Q54Named segments via score buckets (CASE).
  5. Q55Counts per named segment + total customers.
  6. Q56Median & P95 CLV per segment (Topic 22).
  7. Q57Per region segment counts (Topic 21 pivot).
  8. Q58Per tier segment counts.
  9. Q59Per cohort segment counts (cohort_month x segment).
  10. Q60Drift: segment Q1 vs Q2 (transition matrix).
  11. Q61Movers report: Champions in Q1 -> which segments in Q2.
  12. Q62Promotion candidates: At Risk + monetary high.
  13. Q63Reactivation candidates: Hibernating + >=5 historical orders.
  14. Q64New-customer pipeline per month (Topic 23).
  15. Q65Average orders, avg lifetime revenue per segment.
  16. Q66CLV percentile bands per segment (Topic 22).
  17. Q67Top-10 customers per segment by CLV (Topic 16).
  18. Q68Segment x delivery-SLA breach rate (Topic 22).
  19. Q69Segment x support-ticket volume per customer.
  20. Q70JSON RFM segment export (Topic 24).
  21. Q71Point-in-time segmentation: scores as-of last quarter (Topic 22 DISTINCT ON).
  22. Q72Versioned segment rules (v1 vs v2); count divergence.
  23. Q73RFM x cohort retention join: do high-R customers have higher Month-3 retention?
  24. Q74RFM x region anomaly (Topic 22 baseline + segment).
  25. Q75Reconciliation: customers across segments sum = total customers.

FUNNELS + GROWTH ATTRIBUTION

  1. Q764-step funnel: product -> cart -> checkout -> ordered (distinct customers).
  2. Q77Session-level funnel via session_id (per session reach).
  3. Q78Drop-off per step (% lost).
  4. Q79Time-between-steps median/P95 (Topic 22 + Topic 23).
  5. Q80Funnel per device per month (Topic 21 + Topic 23).
  6. Q81Funnel per region per cohort.
  7. Q82Conversion rate per device per month.
  8. Q83PoP funnel: this month vs last month per device.
  9. Q84PoP funnel: same month last year (YoY).
  10. Q85Revenue PoP per region: MoM, YoY, QoQ in one pivot (Topic 21+23).
  11. Q86Growth decomposition per month: new / returning / reactivated revenue.
  12. Q87Active-customer decomposition per month.
  13. Q88Cohort-level PoP: cohort revenue MoM vs aging.
  14. Q89Funnel x RFM segment: do Champions convert better at checkout?
  15. Q90Funnel JSON export (Topic 24) with drop-off and time-between-steps.
  16. Q91CLV per RFM segment with MoM trend.
  17. Q92Triangle pivot to wide (Topic 21) + JSON envelope (Topic 24).
  18. Q93Top movers report: cohorts whose retention shifted > X% (Topic 23).
  19. Q94Plan-check (Topic 19) the heaviest funnel; index strategy (Topic 20).
  20. Q95MV candidates (Topic 25): funnel per month per device; cohort grid.
  21. Q96Reconciliation: funnel-ordered customers = orders.distinct cust_id.
  22. Q97Reconciliation: PoP MoM revenue sum equals monthly_revenue MV (Topic 25).
  23. Q98Cleansed-base (Topic 26) -> cohort layer: deduped customers -> cohort sizes.
  24. Q99Exec dashboard: Month-1 retention per cohort + RFM segment counts + funnel drop-off + MoM revenue per region.
  25. Q100Capstone: cohort triangle (retention+LTV), RFM segments with named labels, 4-step funnel with drop-off and times, MoM/YoY/QoQ per region, and a JSON exec export - one cohesive set of queries.

Production scenarios, optimisation

CONCEPTUAL

  1. Q1Architect a growth analytics platform: cleansed -> cohort -> segments -> funnels -> exec.
  2. Q2Cohort anchor choice and impact on retention shape; both grids in parallel.
  3. Q3Weekly cohorts: noise/clean tradeoff; rolling-12-week strategy.
  4. Q4Cumulative LTV per cohort: window functions (17) + spine (23) integration.
  5. Q5Point-in-time RFM scoring vs current-as-of; versioned segment definitions.
  6. Q6Named-segment governance and renamings without breaking dashboards.
  7. Q7Transition matrix (segment Q->Q+1) - interpretation and uses.
  8. Q8Survival models in SQL (concept) for retention curves.
  9. Q9Funnel design: customer vs session vs event grain; choosing per question.
  10. Q10Time-between-steps distribution: P50/P95 (Topic 22) per step.
  11. Q11Attribution by channel/device/region: pivot architectures (Topic 21).
  12. Q12Growth decomposition: new/returning/reactivated revenue + active.
  13. Q13PoP zero/NULL safety; comparing across regimes (seasonality, Topic 23).
  14. Q14Cohort x region x tier 3D slice: pivot architecture limits.
  15. Q15CLV horizons (12-month vs lifetime) and reporting both.
  16. Q16Spine-driven completeness for cohorts/funnels (Topic 23).
  17. Q17MV strategy (Topic 25) for cohort grids and funnel marts.
  18. Q18Plan inspection (Topic 19) of cohort joins; covering indexes (Topic 20).
  19. Q19JSON exec exports (Topic 24): triangles, RFM, funnel envelopes.
  20. Q20Cleansed inputs (Topic 26): why dedup of customers/orders matters.
  21. Q21Idempotent rebuilds of cohort/funnel MVs (refresh DAG).
  22. Q22SLA per insight: freshness, completeness, accuracy.
  23. Q23Cross-team contracts: marketing wants segments; product wants funnels.
  24. Q24Anti-patterns: cohort with a moving cohort_size denominator.
  25. Q25Documentation: cohort/RFM/funnel runbook in MV comments.

COHORT + LTV ENGINES

  1. Q26Cohort engine: monthly grid (signup_month x period 0..11) retention.
  2. Q27Cohort engine: cumulative LTV per period (Topic 17 running sum).
  3. Q28Cohort engine: per region/tier/cohort heatmap (Topic 21 pivot).
  4. Q29Cohort engine: weekly grid (signup_week x week 0..12).
  5. Q30Spine-completeness audit: missing cells per cohort (Topic 23).
  6. Q31Best/worst cohort by Month-1/Month-3/12-month LTV (Topic 16 ranks).
  7. Q32Compare 2024 vs 2025 cohorts on both retention and LTV shape.
  8. Q33Reactivation rate per cohort + period (gap + reactivate).
  9. Q34Median revenue per active per cell (Topic 22 in cohort).
  10. Q35Cohort-LTV decomposition: new revenue vs repeat revenue per period.
  11. Q36Cohort x device proxy (concept) using web_events.
  12. Q37Cohort x tier vs region split (Topic 21 multi-dim pivot).
  13. Q38JSON cohort triangle export (Topic 24): {cohort,period,retention,ltv}.
  14. Q39MV mv_cohort_triangle + UNIQUE INDEX(cohort_month, period_index).
  15. Q40MV mv_cohort_ltv + UNIQUE INDEX(cohort_month, period_index).
  16. Q41CONCURRENTLY-refresh both MVs; plan check on the read path (Topic 19).
  17. Q42Cohort engine consumer: exec strip MV joining cohort + RFM.
  18. Q43Cleansed-input dependency (Topic 26): dedup customers before sizing.
  19. Q44Sanity: cohort sizes sum = total customers; LTV sum = total revenue.
  20. Q45Best cohort by 6-month repeat-rate (active in months 1..6).
  21. Q46Cohort-CLV curve per region; identify steepest curves.
  22. Q47Cohort x tier x period - top performers by LTV.
  23. Q48Cohort x channel proxy (web_events) - preliminary attribution.
  24. Q49Cohort export as JSON arrays per region (Topic 24).
  25. Q50Cohort capstone: monthly + weekly grids + LTV + region/tier slice as one mart.

RFM PLATFORM & SEGMENT DRIFT

  1. Q51RFM engine: per customer R/F/M raw values (Topic 22 latest order).
  2. Q52NTILE(5) scoring per R/F/M (Topic 16).
  3. Q53Named-segment mapping (Champions, Loyal, At-Risk, ...).
  4. Q54Per region/tier/cohort segment counts (Topic 21).
  5. Q55Median/P95 CLV per segment (Topic 22).
  6. Q56Point-in-time RFM scoring as-of any quarter end.
  7. Q57Transition matrix segment Q->Q+1 (Topic 23).
  8. Q58Movers report: Champions in Q1 -> segments in Q2.
  9. Q59Promotion candidates: At Risk + monetary high; reactivation candidates.
  10. Q60Top movers by CLV per quarter.
  11. Q61RFM x tier x region 3-way matrix (Topic 21).
  12. Q62Versioned segment rules v1/v2 and divergence report.
  13. Q63Segment SLA: % customers stable for >=2 quarters.
  14. Q64JSON RFM export per region (Topic 24).
  15. Q65MV mv_rfm_segments + UNIQUE INDEX(customer_id).
  16. Q66mv_rfm_drift_quarter + UNIQUE INDEX(quarter, customer_id).
  17. Q67mv_segment_ltv_summary + UNIQUE INDEX(segment).
  18. Q68CONCURRENTLY refresh, with plan check (Topic 19).
  19. Q69Segment x delivery SLA breach rate (Topic 22).
  20. Q70Segment x support ticket volume per customer.
  21. Q71Segment x cohort retention correlation.
  22. Q72RFM x month: count per segment per month (Topic 23).
  23. Q73RFM-based marketing list (top movers + promotion candidates).
  24. Q74Plan-check the RFM engine; index strategy (Topic 20).
  25. Q75RFM capstone: scoring + named segments + drift + marketing JSON export.

FUNNEL + GROWTH ATTRIBUTION & PRODUCTION MARTS

  1. Q765-step funnel: view -> product -> cart -> checkout -> ordered (distinct customers).
  2. Q77Session funnel via session_id; reach per session per step.
  3. Q78Drop-off per step + cumulative reach.
  4. Q79Time-between-steps median/P95 per step (Topic 22 + Topic 23).
  5. Q80Funnel per device per month (Topic 21 + Topic 23).
  6. Q81Funnel per region per cohort.
  7. Q82PoP funnel MoM/YoY per device.
  8. Q83Conversion rate per device per month (a moving baseline).
  9. Q84Funnel JSON export (Topic 24) per channel/month with drop-off and times.
  10. Q85MV mv_funnel_steps_monthly + UNIQUE INDEX(month, device).
  11. Q86MV mv_conversion_rates_monthly + UNIQUE INDEX(device, month).
  12. Q87Growth decomposition MV: new/returning/reactivated per month per region.
  13. Q88PoP revenue per region (MoM, YoY, QoQ) in one wide row.
  14. Q89Cohort-aware PoP: cohort revenue MoM vs aging.
  15. Q90Funnel x RFM segment: do Champions convert better?
  16. Q91Reconciliation: funnel ordered-step customers = orders.distinct cust_id.
  17. Q92Reconciliation: PoP MoM revenue total matches mv_daily_revenue (Topic 23/25).
  18. Q93Exec mart: Month-1 retention per cohort + RFM segment counts + funnel drop-off + MoM revenue per region.
  19. Q94JSON exec dashboard export (Topic 24): cohort triangle + RFM segments + funnel + PoP.
  20. Q95Marketing handoff: JSON list of Reactivation Candidates with last order and CLV.
  21. Q96Product handoff: JSON funnel diagnostic with worst-step per channel.
  22. Q97Finance handoff: JSON cohort LTV curves with assumptions in meta.
  23. Q98Drift alerting: cohorts/segments shifting > X% (Topic 23).
  24. Q99End-to-end refresh DAG: cleansed (Topic 26) -> cohort/RFM/funnel MVs (Topic 25) -> JSON exports (Topic 24).
  25. Q100Capstone: production growth analytics platform - cohort triangle (retention+LTV), versioned RFM segments + drift, 5-step funnel with drop-off and times, MoM/YoY/QoQ decomposition, all delivered as MVs with refresh DAG and JSON exec exports.