📊 Customer Churn Analytics Dashboard

This repository implements an end-to-end analytics pipeline and executive dashboard for analyzing customer churn for a subscription-based telecom service. It uses the public Telco Customer Churn dataset as a case study. The stack is SQL → R → Tableau: raw data is modeled and cleaned in SQL, transformed and aggregated in R, and visualized in a business-friendly Tableau dashboard to identify churn patterns, high-risk customer segments, and revenue at risk.

---

🎯 Project Overview

The goal is to:

• Build a reproducible analytics pipeline from raw Telco churn data to dashboard-ready outputs.
• Identify churn patterns and high-risk customer segments (e.g. by contract type, tenure, services).
• Quantify revenue at risk (MRR from churned or at-risk customers) to support retention decisions.
• Deliver an executive-facing Tableau dashboard with clear KPIs and actionable insights.
• Document the workflow so that raw data → database → queries → exports → dashboard is reproducible and easy to follow.

The pipeline cleanly separates ingest (SQL), transform (R), and visualize (Tableau) for maintainability and reuse.

---

✨ Key Features

• SQL layer (sql/): Create staging tables, clean and normalize data, and define KPI views for churn rate and revenue at risk.
• R layer (r/): Extract data from the database, build KPI and driver tables, and export CSVs for Tableau.
• Tableau dashboard: Connect to processed CSVs for churn rate, segment breakdowns, and revenue-at-risk visualizations.
• Structured repository: Clear separation of raw data, processed outputs, code, and documentation.
• R dependency file (r/utils/requirements.R) for easy setup of DBI, dplyr, and related packages.

---

📊 Tableau Dashboard

The main deliverable for visual storytelling is an interactive dashboard published on Tableau Public:

Customer Churn Dashboard — Telco

(the tableau/ folder contains the Tableau workbook .twbx so you can explore it locally)

The dashboard explores churn rate, high-risk segments, and revenue at risk for a subscription-based telco. It includes:

• KPI summary — Total customers, churned vs retained, churn rate %, total MRR, MRR at risk, and % revenue at risk.
• Churn by segment — Bar charts of churn rate by contract type, tenure bucket, internet service, and payment method.
• Contract × Tenure — Heatmap (or matrix) of churn rate and MRR at risk by contract and tenure band (e.g. month-to-month + low tenure = highest risk).
• Contract × Internet — Heatmap of churn and revenue at risk by contract and internet service type (e.g. fiber + month-to-month).
• Revenue focus — MRR vs MRR at risk by contract and by segment to prioritize retention efforts.

---

🧱 Repository Structure

customer-churn-dashboard/
│
├── data/                                  # Dataset storage (raw tracked in Git for GitHub Pages)
│   ├── raw/                               # Unmodified input data (as downloaded)
│   │   └── telco-churn.csv                # Telco Customer Churn source file (Kaggle)
│   ├── processed/                         # Cleaned / aggregated CSVs exported for Tableau (generated by R)
│   └── churn.db                           # SQLite database (not in Git; generated by load script)
│
├── sql/                                   # Schema and queries for the analytics database
│   ├── schema/                            # Table definitions
│   │   └── create_tables.sql              # Create staging table(s) for raw churn data
│   └── queries/                           # Cleaning, transforms, and KPI views
│       ├── 01_clean_staging.sql           # Clean, normalize, and build base table(s)
│       └── 02_kpi_views.sql               # KPI views (churn rate, revenue at risk, segment breakdowns)
│
├── r/                                     # Extract from SQL, build KPI tables, churn driver analysis
│   ├── scripts/                           # Pipeline scripts (run in order)
│   │   ├── 01_extract_from_sql.R          # Connect to DB and load staging / base tables into R
│   │   ├── 02_kpi_tables.R                # Build KPI aggregates and export to data/processed/
│   │   └── 03_churn_drivers.R             # Churn driver analysis and segment exports for Tableau
│   └── utils/                             # Utilities and setup
│       └── requirements.R                 # R package dependencies (DBI, RSQLite, dplyr, etc.)
│
├── tableau/                               # Dashboard assets (screenshot + workbook)
│   ├── Tableau Screenshot.png
│   └── *.twbx                             # Tableau workbook; data source: data/processed/
│
├── .gitignore
├── LICENSE
├── README.md
└── report.md                              # Technical report (methodology, pipeline, reproducibility)

🗒️ Note:

• data/churn.db is the SQLite database created when you run the SQL load script; it is not in Git.

---

🧰 Run Locally

You can reproduce the pipeline using R (DBI, RSQLite, dplyr, readr) and a SQL engine (SQLite).

1️⃣ Clone the repository

HTTPS (recommended for most users):

git clone https://github.com/florykhan/customer-churn-dashboard.git
cd customer-churn-dashboard

SSH (for users who have SSH keys configured):

git clone git@github.com:florykhan/customer-churn-dashboard.git
cd customer-churn-dashboard

2️⃣ Add the raw dataset

The Telco Customer Churn dataset is in data/raw/telco-churn.csv. If you need to re-download it, place the CSV there (same path).

📥 Download the dataset: Kaggle – Telco Customer Churn

3️⃣ Run the SQL pipeline

Run the following from the project root (so churn.db is created there and raw/telco-churn.csv is found). If you use SQLite (e.g. sqlite3 in terminal):

cd data
sqlite3 churn.db < ../sql/schema/create_tables.sql
sqlite3 -csv -header churn.db ".import 'raw/telco-churn.csv' staging_churn"
sqlite3 churn.db < ../sql/queries/01_clean_staging.sql
sqlite3 churn.db < ../sql/queries/02_kpi_views.sql
cd ..

This creates data/churn.db with staging_churn, base_churn, and the KPI views. If you use Postgres or another engine, adjust the commands and paths to match your environment.

4️⃣ Install R and run the R pipeline

Install R first (from CRAN or your package manager).

In R or RStudio, set the working directory to the project root (e.g. Session → Set Working Directory → Choose Directory → select customer-churn-dashboard), then run:

setwd("/path/to/customer-churn-dashboard")   # or use Choose Directory in RStudio
source("r/utils/requirements.R")
source("r/scripts/02_kpi_tables.R")
source("r/scripts/03_churn_drivers.R")

(Optional: run source("r/scripts/01_extract_from_sql.R") if you want staging_churn and base_churn loaded into your R session.)

5️⃣ Build or refresh the Tableau dashboard

• Connect Tableau to the CSVs in data/processed/.
• Create views for churn rate, segments, and revenue at risk (or open the workbook in tableau/).
• Save the workbook as tableau/dashboard.twbx (or your preferred name).

---

📈 Results (Summary)

Deliverable	Description
Churn rate & trend	Overall and segment-level churn metrics (contract, tenure, internet, payment).
High-risk segments	e.g. Month-to-month, low tenure, fiber + month-to-month.
Revenue at risk	MRR from churned customers; % revenue at risk.
Executive dashboard	Tableau workbook for stakeholders (link and screenshot above).

➡️ For pipeline details, metrics definitions, and reproducibility steps, see the full report: report.md.

---

🔍 Key Questions Answered

• What is the overall churn rate and how much revenue is at risk? — KPI summary and kpi_overall.csv.
• Which segments churn most? — Breakdowns by contract, tenure bucket, internet service, and payment method.
• Where is the combination of contract + tenure or contract + internet riskiest? — Heatmaps from churn_drivers_contract_tenure.csv and churn_drivers_contract_internet.csv.
• Where should we focus retention efforts first? — MRR at risk by segment and contract × tenure / contract × internet views.

---

🧠 Tech Stack

• Data: Telco Customer Churn (Kaggle)
• SQL: Schema, staging, cleaning, KPI views (SQLite / Postgres compatible)
• R: DBI, RSQLite, dplyr, tidyr, readr — extract, transform, export to CSV
• Visualization: Tableau Public (dashboard on processed CSVs)
• Version control: Git + GitHub (raw data tracked for GH Pages)

---

🧾 License

MIT License, feel free to use and modify with attribution. See the LICENSE file for full details.

---

👤 Author

Ilian Khankhalaev
BSc Computing Science, Simon Fraser University
📍 Vancouver, BC | florykhan@gmail.com | GitHub | LinkedIn