Customer Lifetime Value & Churn Analytics
End-to-end customer value analytics workflow integrating data engineering, financial modeling, and executive dashboarding.
Overview
This project implements a full analytics pipeline designed to quantify customer value, identify churn exposure, and simulate retention strategy impact using SQL transformation, Excel scenario modeling, and Power BI visualization.
Architecture Overview
Dataset
IBM Telco Customer Churn Source: https://www.kaggle.com/datasets/blastchar/telco-customer-churn
Business Objective
Organizations experience revenue leakage due to customer attrition. This project builds a decision-support workflow to:
- Segment customers by lifetime value
- Quantify churn exposure
- Estimate recoverable revenue
- Model retention strategy ROI
Tech Stack
- SQL — Data cleaning, feature engineering, segmentation
- Excel — Scenario modeling and forecast simulation
- Power BI — Executive decision dashboard
- GitHub — Version control and documentation
Analytical KPI Framework
KPI| Definition| Business Relevance Customer Lifetime Value (CLV)| Monthly Charges × Estimated Lifetime Months| Measures long-term customer profitability Churn Rate| % of customers who discontinued service| Indicates retention health Revenue at Risk| CLV of churned customers| Quantifies financial exposure Segment Value Tier| Quartile-based CLV segmentation| Enables prioritization of resources Incremental CLV Saved| CLV retained under scenario simulation| Measures strategy impact
Pipeline Workflow
- Raw dataset ingestion and validation
- Data cleaning and feature engineering via SQL
- CLV proxy computation
- Quartile-based segmentation
- Churn concentration analysis
- Revenue exposure estimation
- Retention scenario simulation in Excel
- Dashboard storytelling via Power BI
Analytical Walkthrough
- Telco dataset ingested and validated
- SQL transformations applied for feature engineering
- CLV proxy computed and segmented into quartiles
- Excel used to simulate retention impact scenarios
- Power BI dashboards built for diagnostic exploration
- Revenue exposure and recovery potential visualized
- Strategic insights derived for cohort targeting
Analytical Techniques Applied
- Window-based aggregation for segmentation logic
- Quartile distribution modeling
- Revenue exposure quantification
- Scenario-based financial simulation
- KPI-driven dashboard storytelling
Key Analytical Outcomes
- Customer base segmented into four CLV tiers
- Churn risk concentration identified in low-value cohorts
- Revenue exposure quantified by contract structure
- Retention strategies modeled for incremental CLV recovery
Dashboard Highlights
- Segment value distribution
- Churn rate diagnostics
- Revenue vs churn loss comparison
- Retention strategy impact simulation
Financial Impact Modeling
This project quantifies revenue exposure and evaluates retention ROI through scenario simulation.
- Identified high-risk revenue concentration within low-tenure cohorts
- Estimated recoverable value through churn reduction scenarios
- Modeled incremental CLV preservation for strategic targeting
- Enables prioritization based on economic impact rather than churn volume
Repository Structure
data/ → Source dataset
sql/ → Data engineering scripts
excel/ → Forecast model
powerbi/ → Dashboard file
images/ → Visualization screenshots
This project applies Customer Lifetime Value (CLV) forecasting to support data-driven retention and profitability decisions.
- Forecasts customer value trajectories to quantify long-term revenue potential
- Identifies value-at-risk through churn probability and contract segmentation
- Evaluates retention scenarios based on incremental CLV preservation
- Enables prioritization of high-impact customer segments using economic value signals
Reproducibility
To replicate this project:
- Download dataset from Kaggle
- Execute SQL script in '/sql/'
- Open Excel model in '/excel/'
- Load dataset into Power BI file in '/powerbi/'
- Refresh visuals
All required assets are included in the repository.