Customer Churn — Revenue at Risk

Context

A telecom (or subscription) 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 — contract type, tenure, payment method, service usage — best explain cancellation, and how much revenue is at risk in each segment?

The second half of that question is the one that decides where budget goes. 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.

Architecture

The analytical logic lives in SQL, not in the BI tool. Segmentation views are defined once in PostgreSQL with CTEs, and revenue at risk is computed with window functions — SUM() OVER (PARTITION BY ...) and RANK() — so the dashboard reads from a modeled layer rather than re-deriving the numbers in a measure. That keeps the figures reproducible outside the BI tool and makes them auditable line by line.

Data Source

• Source: Telco Customer Churn dataset (Kaggle). Olist (Brazilian e-commerce) is a drop-in alternative for an order-based churn framing.
• Size: ~7,000 customer records with demographic, contractual, and usage attributes.
• Key variables: contract type, tenure, payment method, subscribed services, churn (yes/no), monthly and total charges.
• Storage: loaded into PostgreSQL for modeling.
• Known limitations: a static, non-temporal snapshot — no real seasonality; a moderate sample size that thins out in some sub-segments; and a self-contained dataset, so external drivers such as price changes or competitor moves are not captured.

Methodology

Structured with CRISP-DM to keep the work business-first and reproducible:

1. Business Understanding — frame the question in revenue terms: which segments to protect, and what they are worth.
2. Data Understanding — profile the dataset in SQL: overall churn rate and distribution across contract type, tenure, and payment method.
3. Data Preparation — model the data in PostgreSQL; build segmentation views with CTEs by contract type, tenure band, and payment method.
4. Modeling — apply window functions (SUM() OVER (PARTITION BY ...), RANK()) to compute revenue at risk and rank segments by exposure.
5. Evaluation — cross-check every dashboard figure against direct SQL aggregations; confirm segment totals reconcile to the global total.
6. Deployment — publish an interactive dashboard (Power BI / Tableau) with cross-filters by segment, tenure, and service.

AI assists, human validates

An LLM drafts the executive summary from the numbers the SQL layer computes, and proposes candidate labels for the high-risk segments — for example, "new month-to-month, electronic-check payers." The analyst validates every figure and label against the underlying data before it ships. The model accelerates the write-up; it does not source the conclusions.

Challenges

These are the problems the build has to solve — named up front so the finished project can be judged on how it handled them.

• Defining "revenue at risk" before computing it. Monthly charges times churn rate? Annualized contract value? Total charges already booked? Each definition is defensible and each moves the headline number by an order of magnitude. The assumption has to be written down first, then held constant everywhere it appears — in SQL and on the dashboard.
• Association is not causation. The dataset is a static snapshot with no temporal dimension, so nothing here can establish that a contract type causes cancellation. Findings have to be reported as associations, and the recommendations framed as experiments worth running rather than outcomes worth promising.
• Confirmation bias on a well-known dataset. The Telco churn patterns are widely published, which makes it easy to go looking for them, find them, and stop. Every hypothesis gets tested and reported with its real figure — including any that do not hold up in this cut of the data.
• Reconciling the dashboard to SQL, and knowing when a segment is too thin. Every BI figure has to tie back to a direct SQL aggregation, with segment totals reconciling to the global total — a fan-out join that silently double-counts revenue is the standard failure mode here. Slicing 7,000 records by contract × tenure band × payment method also produces cells too small to read, so a minimum-N rule has to gate which segments earn a recommendation.

Results

Hypotheses to test — not findings

The patterns below come from prior work on this dataset, not from this analysis. They are what the model is built to quantify, and each will ship with its exact figure and a supporting dashboard view — including any that turn out not to hold:

• Month-to-month (no-commitment) contracts churn materially more than one- or two-year contracts.
• The earliest months of the relationship concentrate most cancellations.
• Electronic-check payment is associated with higher churn than automatic bank transfer or credit card.
• A small number of high-risk segments concentrate a disproportionate share of revenue at risk.

The provisional recommendations in the repository README — prioritizing outreach in the first 90 days, incentivizing migration off month-to-month, and reviewing the electronic-check payment experience — follow from those hypotheses, and will be re-prioritized against the real revenue-at-risk figures during the build.

Tech Stack