Customer Churn: Revenue at Risk

Context

A telecom business loses a meaningful share of its customer base every month. Commercial leadership needs to know which segments concentrate the most cancellation risk, and what that risk is worth in revenue, to spend the retention budget efficiently instead of across the board.

Business Problem

Which customer characteristics, namely contract type, tenure, payment method, best explain cancellation, and how much revenue is at risk in each segment? A segment can churn at a high rate and still be worth ignoring if it carries little revenue; a lower-rate segment attached to high monthly charges can matter far more. Revenue at risk answers that directly: each segment's current active MRR weighted by its own historical churn rate, a projection kept distinct from revenue already lost.

Architecture

The analytical logic lives in SQL, not in the dashboard. Segmentation views are defined once in PostgreSQL with CTEs, and revenue at risk is computed with window functions (RANK() OVER (ORDER BY revenue_at_risk DESC)), so the dashboard reads from a modeled layer rather than re-deriving the numbers. A 9-check quality gate sits between staging and the marts and RAISE EXCEPTIONs on violation.

The Numbers (evidence)

All figures below come from query output on the built database, not estimation.

Overview: 7,043 customers · 26.54% churn rate · $316,986/mo active MRR · $139,131/mo already lost to churned customers.

Month-to-month churns 15× more than two-year contracts and carries almost 5× the revenue at risk of the other two combined ($58,277 vs. $12,005/mo): 83% of the $70,281/mo at risk across all three contract types sits in this one segment.

The 0-6 month band has the highest churn rate, but 25-48 months ranks a close 2nd in dollar risk on a much lower rate, because it carries 2.5× the exposure. Ranking by churn rate alone would have misdirected the budget.

By payment method: Electronic check churns at 45.29%, 2.4-3.0× every other method (16.71% bank transfer, 15.24% credit card, 19.11% mailed check), and alone accounts for $43,504/mo of revenue at risk, more than the other three payment methods combined ($36,989/mo).

Highest-risk single segment (min. 30 customers): Month-to-month + 0-6 months + Electronic check: 680 customers, 67.65% churn, $9,063/mo at risk.

Interactive Dashboard

Cross-filter by contract, tenure and payment method: every KPI and bar recomputes live in your browser from the same 7,043 rows the SQL model reads. Self-contained HTML/JS, no CDN, no server: open it full-screen ↗.

Methodology

Structured with CRISP-DM:

1. Business Understanding: frame the question in revenue terms, and define "revenue at risk" before computing it.
2. Data Understanding: profile in SQL: churn rate and distribution across contract, tenure, payment method.
3. Data Preparation: raw → staging, with every cleansing rule commented with the problem it fixes (including the TotalCharges-blank-means-not-billed-yet rule for 11 brand-new customers).
4. Modeling: segmentation views with CTEs; RANK() for exposure ranking; a minimum-N=30 rule gates the micro-segment table so no "top segment" claim rests on a handful of rows.
5. Evaluation: 9 fail-loud quality checks; the dashboard is verified byte-for-byte against the SQL layer.
6. Deployment: a self-contained interactive dashboard, plus CSV exports for Tableau Public / Power BI Desktop.

Challenges

• Defining "revenue at risk" before computing it. The build keeps two concepts explicitly distinct: mrr_lost_to_churn (money already gone) vs. revenue_at_risk (active MRR × segment churn rate, a projection). Reporting only one would either understate today's risk or overstate historical damage.
• No GUI on a headless Linux server. Power BI and Tableau Desktop both need a machine this project doesn't have. Rather than leave the BI piece undone, the dashboard is a dependency-free HTML/JS page that reads the same customer-level data and reconciles exactly to the SQL layer, genuinely cross-filterable, verified with headless Playwright rather than a screenshot.
• A real bug the automated check caught: the dashboard's bars were built from <span> elements, inline by default, so their width: X% inline styles silently rendered at zero pixels. A visual screenshot alone made the bars look plausibly-empty-but-fine; a Playwright bounding-box check caught the actual zero-width render. Fixed with display:block.
• Ranking by churn rate alone would have misdirected the budget. The tenure-band cut proves segment size can outweigh churn rate: 25-48 months ranks 2nd in dollar risk despite a much lower rate than three other bands, because its active base is 2.5× larger.

Recommendations

• Prioritize retention outreach on month-to-month, electronic-check payers in their first six months: the single highest-risk, large-enough-to-act-on segment (680 customers, 67.65% churn).
• Test incentives to migrate month-to-month customers to annual contracts: the churn-rate gap (42.71% vs. 11.27%) is the largest lever in the dataset.
• Investigate the electronic-check payment experience: its churn rate is an outlier by a wide margin across every contract and tenure cut; this needs a hypothesis-driven follow-up, not just a retention offer.
• Don't chase 0-6 month churn as the top priority by rate alone: 25-48 month customers carry nearly as much dollar risk on a much larger, stickier base.

Tech Stack

View the code on GitHub ↗