Markdown

Subscription Cohort Analysis – Monthly Retention Heatmap

A Power BI cohort retention heatmap analyzing monthly subscriber churn trends from September 2022 to September 2023.

📋 Table of Contents

• Project Overview
• The Scenario
• Dataset
• Objectives
• Data Preparation & Cleaning
• Technical Implementation
• The Cohort Heatmap Explained
• Key Insights & Recommendations
• Tools Used

---

📌 Project Overview

This project analyzes subscriber retention for Mavenflix, a subscription-based streaming platform. Using one year of subscription data (Sept 2022 - Sept 2023), I built a monthly cohort retention heatmap in Power BI to help leadership identify churn patterns and trends.

The core question: Are we getting better at keeping our customers?

---

🎬 The Scenario

Mavenflix, a streaming platform with customers worldwide, had subscription data locked in spreadsheets. Leadership needed answers to critical questions:

• When do customers typically churn?
• Is retention improving over time?
• Which months have the best/worst retention?

Managers were spending too much time exporting and manually analyzing data instead of acting on insights.

---

📊 Dataset

The dataset comes from Maven Analytics and contains subscription records from September 2022 to September 2023.

🔗 Dataset Source: Maven Analytics Guided Project

Key columns:

• customer_id - Unique customer identifier
• created_date - Subscription start date
• canceled_date - Subscription end date (null if still active)

Key observation: Empty canceled_date values don't indicate missing data—they represent subscribers still active when the study period ended.

---

🎯 Objectives

1. Profile & QA the raw data - Remove unpaid subscriptions and understand duplicate customer IDs
2. Prepare the data - Transform data to support cohort analysis
3. Build the visualization - Create a monthly cohort retention heatmap

---

🧹 Data Preparation & Cleaning

Step 1: Handling Null Canceled Dates

Empty canceled_date values meant customers were still active. I replaced nulls with October 2, 2023, assuming active subscribers would at least finish their September billing cycle.

Before	After
canceled_date = null	canceled_date = 2023-10-02

Why October 2, not September 30?

This ensures subscribers active through September are counted in the September cohort's Month 1 retention. Using September 30 would incorrectly exclude them from the retention calculation for that month.

---

Step 2: Creating "Created Date (SOM)"

I created a new column rounding each subscription start date to the first of its month. This groups all January sign-ups together, all February sign-ups together, etc.

created_date	Created Date (SOM)
2023-01-15	2023-01-01
2023-01-28	2023-01-01
2023-02-05	2023-02-01

---

Step 3: Calculating Month Span

I calculated the total months between created_date and canceled_date for each customer.

Example: A customer subscribed from Jan 2023 to March 2023 = 3 months.

---

Step 4: Expanding into Monthly Records

This was the most critical transformation. Using Power Query's List.Numbers function, I expanded each customer's subscription into one row per active month.

Before	After
1 row per customer	1 row per customer-month

Example: Customer (3-month subscription)

Month	Period
Month 1	Jan 2023
Month 2	Feb 2023
Month 3	Mar 2023

This transformation enables month-by-month retention tracking.

---

⚙️ Technical Implementation

Power Query (Data Transformation)

• Used Replace Values to handle null canceled_date values
• Created Created Date (SOM) column using Date.StartOfMonth([created_date]) to round each subscription start date to the first day of its month
• Calculated Month Span using a custom formula that accounts for partial months and includes both start and end months:

Date.Month([canceled_date]) - Date.Month([created_date]) + 
(Date.Year([canceled_date]) - Date.Year([created_date])) * 12 + 
(if Date.Day([canceled_date]) < Date.Day([created_date]) then -1 else 0) + 1

• Added Month List column using List.Numbers(1, [Month Span]) to generate a list of numbers starting from 1 (e.g., if Month Span = 3, the list is {1, 2, 3})
• Expanded lists into rows using "Expand to New Rows"
• Changed data types to ensure accuracy

---

DAX Measures (Power BI)

Customer Retention Volume

Customer Retention Volume = 
DISTINCTCOUNT('Subscription Cohort Analysis Data'[customer_id])

Counts unique customers active in each cell's row/column context.

Customer Retention %

Customer Retention % = 
DIVIDE(
    [Customer Retention Volume],
    CALCULATE(
        [Customer Retention Volume],
        'Subscription Cohort Analysis Data'[Month List] = 1
    ),
    0
)

Divides each month's active customers by the cohort's original size (Month 1).

Visualization

• Created a matrix with:
  • Rows = Created Date (SOM) (or Sign-up Month)
  • Columns = Month List (or Months since joining)
• Added Customer Retention % as values
• Applied conditional formatting with color gradients for instant pattern recognition

---

📊 The Cohort Heatmap Explained

Sign-up Month	Month 1	Month 2	Month 3	Month 4	...
Sep 2022	100%	79%	51%	41%	...
Oct 2022	100%	78%	56%	44%	...
Nov 2022	100%	81%	59%	41%	...

---

Axis Definitions

• Rows (Y-axis): Sign-up month (cohort)
• Columns (X-axis): Months since joining
  • Month 1 = The sign-up month itself (always 100%)
  • Month 2 = 1 month after sign-up
  • Month 3 = 2 months after sign-up
• Values: Percentage of original cohort subscribers still active

---

Color Scale

• Dark Purple = High retention (80–100%)
• Purple = Medium retention (40–60%)
• Light Purple = Low retention (0–20%)

---

💡 Key Insights & Recommendations

Insight 1: The Critical 90-Day Window

Retention drops most sharply between Month 2 and Month 3 across all cohorts. For example, Sep 2022 fell from 79% to 51% in just one month.

Recommendation:

Implement a "first 90-day" onboarding campaign:

• Personalized content recommendations
• Early access to new releases
• Engagement emails at days 30, 60, and 90

---

Insight 2: Retention is Improving Over Time

Comparing cohorts side-by-side reveals that newer cohorts (2023) retain better than older ones (2022).

Cohort	Month 3 Retention
Sep 2022	51%
Dec 2022	59%
Jun 2023	61%

Recommendation:

Investigate what's driving improvement—new content, pricing changes, or marketing campaigns—and double down on what's working.

---

Insight 3: Don't Overreact to Incomplete Data

The September 2023 cohort shows an anomalous 17% retention at Month 2. This is likely due to incomplete data (customers may still be active), not a real trend.

Recommendation:

• Always flag data caveats in dashboards
• Never make strategic decisions based on a single, potentially incomplete data point

---

Insight 4: Most New Subscriptions Were Created in July

Based on the Customer Retention Volume measure where Month List = 1 (sign-up month), July 2023 had the highest number of new subscribers.

Recommendation:

Investigate what drove the July spike:

• Was it a marketing campaign?
• Seasonality?
• New content drop?
• Replicate that success.

---

🛠️ Tools Used

Tool	Purpose
Power BI	Data transformation, DAX measures, visualization
Power Query	Data cleaning, expanding subscriptions into monthly rows
DAX	Retention volume and percentage calculations
Conditional Formatting	Heatmap color coding for instant pattern recognition

---

📁 Files in This Repository

File	Description
README.md	Project documentation and overview
cohort_heatmap.png	Screenshot of the final retention heatmap visualization

---

🏷️ Tags

cohort-analysis customer-retention churn-analysis power-bi power-query dax data-visualization subscription-analytics streaming-analytics business-intelligence data-cleaning saas-analytics retention-strategy maven-analytics