Telecom is one of the clearest domains for learning churn analysis. Customers can switch easily, prepaid users leave simply by not recharging, and companies have rich usage data to work with.
Defining churn
Churn must be defined before it can be measured:
- Postpaid: the customer cancels or ports out.
- Prepaid: no recharge for a set number of days after the plan expires (for example 30 or 60 days).
Agree on the definition with stakeholders; different windows give different numbers.
Core KPIs
- Churn rate: customers lost in a period ÷ customers at the start of the period.
- ARPU (average revenue per user): total revenue ÷ active users. Tariff changes aim to raise it, sometimes at the cost of churn.
- Recharge frequency and value: how often and how much prepaid users recharge.
- Data and voice usage: declining usage is often an early churn signal.
- Network quality: call drop rate, complaints per thousand users, data speed issues by area.
- Complaint resolution time.
- Port-out requests.
Analysis steps
- Build the customer table: one row per customer with plan, tenure, region, usage trends, recharges and complaints.
- Calculate churn by segment: plan, tenure band, region, device type, complaint history.
- Cohort view: churn by joining month or by plan change date.
- Before and after events: compare churn before and after a tariff change, carefully accounting for seasonal patterns.
- Leading indicators: falling usage, delayed recharges, recent complaints.
- Recommendations: targeted retention offers, network fixes in hotspot areas, better onboarding for new users.
Example SQL: churn rate by plan
SELECT plan,
COUNT(*) AS customers,
SUM(CASE WHEN churned = 1 THEN 1 ELSE 0 END) AS churned,
ROUND(100.0 * AVG(CASE WHEN churned = 1 THEN 1.0 ELSE 0 END), 1) AS churn_pct
FROM customer_summary
GROUP BY plan
ORDER BY churn_pct DESC;From analysis to prediction
Once drivers are understood, a classification model (logistic regression or random forest) can score customers by churn risk so the retention team can prioritise. Evaluate with precision and recall, since churners may be a minority. See handling imbalanced data.
Portfolio projects
Both are on the telecom projects page. For related techniques, read SQL cohort retention analysis.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: