Skip to content

README.md

Latest commit

 

History

History
116 lines (91 loc) · 4.79 KB

README.md

File metadata and controls

116 lines (91 loc) · 4.79 KB
Raw

Telecom Customer Churn & Revenue Analysis

Author: Divya Jagtap Type: Pure MySQL / SQL portfolio project (no BI tools, no ML, no external scripts)

Project Overview

This project analyzes churn, revenue, usage, payment behaviour, and support-ticket patterns for a simulated telecom company using only SQL. It's built to be small enough to fully explain in a data analyst / data science internship interview, while still covering the SQL skills companies actually screen for: joins, subqueries, CTEs, window functions, and business-metric calculation.

The entire dataset (~1,500 customers, 12 months of usage and billing, ~800 support tickets) is generated inside MySQL itself using RAND(), recursive CTEs, and date functions — there are no CSV imports and no external data source.

Why This Project

Telecom is one of the clearest industries to demonstrate churn analysis because the core metrics (churn rate, retention rate, MRR, ARPU) map directly onto subscription businesses in general — the same logic applies to SaaS, OTT platforms, gyms, and insurance. It's a natural project to discuss in a Data Analyst / Data Science interview.

Database Schema

Five tables, connected by customer_id / plan_id:

Table Purpose Approx. Rows
subscription_plans Master list of plans (price, data/call/SMS limits) 6
customers One row per customer (demographics, plan, churn status) ~1,500
usage_details Monthly data/call/SMS usage per customer ~15,000
billing Monthly invoice + payment status per customer ~15,000
support_tickets Customer support interactions ~800

See 01_database_schema.sql for full DDL with constraints and indexes.

File Guide

Run the files in this exact order:

# File What it does
1 01_database_schema.sql Creates the database and all 5 tables
2 02_data_quality.sql Generates the full synthetic dataset + runs data quality checks
3 03_customer_analysis.sql Customer demographics and tenure segmentation
4 04_churn_analysis.sql Churn rate, retention rate, churn by plan and by month
5 05_revenue_analysis.sql MRR trend, ARPU, top revenue customers
6 06_usage_billing_support.sql Usage utilisation, payment behaviour, ticket analysis
7 07_advanced_sql.sql RANK / DENSE_RANK / ROW_NUMBER / LAG demonstrations
8 08_churn_risk.sql Rule-based churn-risk scoring model (Low/Medium/High)
9 09_business_questions.sql 18 business questions answered end-to-end

How to Run

  1. Install MySQL 8.0+ (Workbench, CLI, or any MySQL client).
  2. Open each .sql file in numeric order and execute it fully.
  3. File 2 (02_data_quality.sql) can take a few seconds since it generates ~30,000+ rows — this is expected.
  4. Because the dataset uses RAND(), your exact numbers will differ slightly from any example numbers in business_insights.md — that's normal for a randomly generated dataset. The patterns and relationships will hold.

SQL Concepts Demonstrated

  • SELECT, WHERE, GROUP BY, HAVING, ORDER BY
  • Multiple JOIN types (INNER, LEFT)
  • CASE WHEN for segmentation and scoring logic
  • Correlated and non-correlated subqueries
  • Common Table Expressions (CTEs), including multiple chained CTEs
  • Window functions: RANK(), DENSE_RANK(), ROW_NUMBER(), LAG(), PARTITION BY
  • Date functions: DATE_ADD, DATE_FORMAT, TIMESTAMPDIFF, DATEDIFF
  • Aggregate functions with ROUND() for clean reporting

The Churn-Risk Score

08_churn_risk.sql builds a transparent, rule-based (not machine-learning) risk score out of 5 factors, each worth 0–2 points (short tenure is 0-or-2):

  1. Overdue/failed payments
  2. Low overall data usage (vs. plan limit)
  3. Declining usage trend (via LAG())
  4. Multiple support tickets
  5. Short tenure (< 6 months)

Total score (0–10) maps to Low (0–2) / Medium (3–6) / High (7–10) risk. This is intentionally simple and 100% explainable — a good middle ground between "no churn model at all" and a full ML pipeline, and appropriate for a SQL-only portfolio project.

What This Project Is NOT

  • Not a machine learning project — the churn-risk score is rule-based SQL logic.
  • Not a BI/dashboard project — no Tableau, Power BI, or Excel.
  • Not using Python/Flask/Streamlit — 100% SQL, run in any MySQL client.

Files in This Project

telecom_churn_project/
├── 01_database_schema.sql
├── 02_data_quality.sql
├── 03_customer_analysis.sql
├── 04_churn_analysis.sql
├── 05_revenue_analysis.sql
├── 06_usage_billing_support.sql
├── 07_advanced_sql.sql
├── 08_churn_risk.sql
├── 09_business_questions.sql
├── README.md
├── business_insights.md
├── interview_questions.md
└── resume_bullets.md