Churn Analysis & Customer Intelligence

An end-to-end churn analytics pipeline for an OTT subscription platform (Netflix / Hotstar / Prime style business). Built by connecting a relational SQLite database to Python, engineering churn-relevant features, and translating the findings into an executive-ready retention strategy.

The Business Challenge

In the hyper-competitive OTT landscape, retention is the only way to survive. This project takes on the role of a Data Analyst identifying high-risk subscribers using a multi-dimensional dataset spanning customer demographics, subscription tiers, and support escalations.

Tech Stack

• Python — numpy, pandas, matplotlib, seaborn
• SQL — sqlite3 (relational data extraction across 3 linked tables)
• Jupyter Notebook for the full analysis workflow

Database Schema

The SQLite database customer_churn.db contains three related tables:

Table	Key Columns
db_customer	customerid, name, country, state, gender, dob, interests, pincode
db_subscription	customerid, subscription_start_date, subscription_type, renewal_date, plan_type, contract_type, cancellation_date, cancellation_reason, monthly_charges, cltv, churn_score
db_support	customerid, complaint_date, escalations, csat_score, comment

All three tables join on customerid.

Workflow

1. Relational Data Extraction — connect Python to SQLite (sqlite3 + pandas) and pull all three tables.
2. Data Cleaning — fix data types, rename/drop columns, standardize categorical values (e.g. gender), impute missing values (e.g. filling missing country from state).
3. Feature Engineering — derive churn_flag, tenure_days, churn_risk tiers (low/med/high from churn_score), and complaint counts per customer.
4. Data Analysis (EDA) — churn rate, retention rate, churn by plan type/state, ARPU, average tenure, revenue at risk, escalation rate, and escalation-churn correlation.
5. Visualization — trend lines, bar charts, correlation heatmaps, pairplots, and multi-dimensional catplots (matplotlib + seaborn).
6. Executive Reporting — translating the technical findings into business-ready insights and action items.

Key Insights

KPI	Value
Churn Rate	28.6%
Retention Rate	71.4%
Monthly-contract churn	55.6%
Annual-contract churn	8.3% (≈6.7x lower than monthly)
Average Customer Tenure	1,451 days
ARPU	₹18.8
Revenue at Risk (churn_score > 70)	₹74
CLTV Lost	₹2,047
% Revenue Loss	18%
Escalation → Churn Correlation	Strong positive

• Most churn comes from the Basic plan — limited major revenue impact.
• Churn spiked in September 2024, concentrated in Karnataka.
• Escalated support interactions are disproportionately concentrated among churned customers.

Recommended Action Items

• Investigate the Karnataka churn spike — pricing changes, complaints, or tech issues.
• Review whether Basic-plan pricing changed around September.
• Track competitor moves — at least one churned user switched to a competitor.
• Prioritize outreach (email/SMS/calls) to customers flagged High and Medium churn risk, ranked by CLTV.
• Push a contract-migration strategy: monthly-plan subscribers churn at ~6.7x the rate of annual-plan subscribers.

Project Structure

churn-analysis-customer-intelligence/
├── notebooks/
│   └── churn_analysis.ipynb        # Full analysis: SQL import → cleaning → features → EDA → visualization
├── data/
│   ├── customer_churn.db           # SQLite database (3 relational tables)
│   └── customer_churn_data_raw.xlsx  # Same data in Excel form (fallback / manual inspection)
├── docs/
│   ├── churn_analysis_report.pdf   # Presentation-style project report
│   └── assets/                     # Chart images used in this README
└── README.md

Running It Yourself

git clone https://github.com/<your-username>/churn-analysis-customer-intelligence.git
cd churn-analysis-customer-intelligence
pip install numpy pandas matplotlib seaborn jupyter
jupyter notebook notebooks/churn_analysis.ipynb

The notebook connects to customer_churn.db by default. If you hit any issues loading the .db file, the notebook includes a fallback cell that rebuilds the SQLite database directly from customer_churn_data_raw.xlsx.

Portfolio Summary

Engineered an end-to-end churn analytics pipeline for an OTT subscription dataset (20+ KPIs), identifying a 28.6% overall churn rate and surfacing that monthly-contract subscribers churned at 55.6% — 6.7x the 8.3% annual-contract rate — directly attributing revenue and CLTV erosion to a small set of at-risk customers, and translating the findings into a targeted contract-migration retention strategy.

License

This project is released under the MIT License.