Chege-jr0/Subscription-Business-Churn-and-Revenue-Analysis
Domain:
socioeconomicRecord type:
dataset
Creator:
Che
Host:
This is a data cleaning and analysis project on a 100,000 -row subscription business dataset. Cleaning was performed in Power Query and Analysis in Excel Pivot Tables. This project covers churn analysis, customer lifetime value, revenue leakage, retention trends, and satisfaction-churn relationship across 6 African Markets.
# Subscription-Business-Churn-and-Revenue-Analysis
## Introduction
This is a data cleaning and analysis project on a 100,000 -row subscription business dataset. Cleaning was performed in Power Query and Analysis in Excel Pivot Tables. This project covers churn analysis, customer lifetime value, revenue leakage, retention trends, and satisfaction-churn relationship across 6 African Markets.
## Repository Structure
All work from cleaning, analysis and visualisation is contained in a single Excel workbook. Power Query steps are embedded in the workbook and visible via Data Queries and Connections.
```markdown
subscription-business-analysis/
│
├── Subscription_Business_Dataset.xlsx # Raw + cleaned data, analysis, dashboard
└── README.md # Project documentation
```
## Dataset Overview
1. Columns(16) - CustomerID, SignupDate, Country, City, Gender, Age, SubscriptionTier, MonthlyPrice, TenureMonths, AcquisitionChannel, Payment Method, SatisfactionScore, Status, Churn, CLV.
2. Rows - 100, 000 customers
3. Markets - Ghana, Kenya, Nigeria, South Africa, Tanzania, Uganda.
4. Subscription Tiers - Basic, Standard, Premium, Entreprise
5. Acquisition Channel - Google, TikTok, Refferal, Organic.
## Data Cleaning(Used Power Query Since the Dataset was very huge)
All data cleaning was perfomed in Power Query and every transformation was recorded in the Applied Steps panel and reproducible.
1. Typos in categorical columns - Replaced the visible typos using Find and Replace.
2. Used Conditional column to fix the typos in the Gender Column(F .- Female, M - Male, null - Unknown
3. The mixed date format - Created a custom date column classifying the dates to indivual and then parsed ecah format correctly based on the detected type.
4. There were visible outliers in the MonthlyPrice (-50 and 5000) - replaced them with the median.
5. There were visible outliers in the Age columm (112 and 7) - replaced them with global median.
6. Replaced the missing values …