E-Commerce Cohort Retention Analysis

Analyze 541K e-commerce transactions to build a customer retention heatmap.

Introduction

30 Second Summary

Every online store wants to know the same thing: are the customers we acquired last month still shopping with us today? When retention drops, revenue follows.

In this project, you will analyze over 541,000 real transaction records from a UK online retailer to answer that question. You'll clean messy data, build monthly customer cohorts, and produce a retention heatmap that reveals exactly which groups of customers stick around.

What You'll Build

Your finished notebook walks through a complete analytical story: load raw data, surface its quality issues, clean it, group customers into monthly cohorts, visualize their return rates, and deliver a written recommendation a hiring manager can read in 30 seconds.

By the end of this project, you'll have:

  • A notebook you can run end-to-end that loads raw transactional data, surfaces its quality issues, and shows your documented cleaning decisions to anyone reviewing your work.
  • A cohort retention heatmap that reveals at a glance which monthly acquisition groups keep coming back and where the sharpest drop-offs hit.
  • A structured business recommendation backed by specific retention numbers from your analysis, ready to share as a portfolio piece.
  • Secret Mission: Segment customers by purchase frequency and visualize how a small group of VIP buyers drives most of the revenue.

Are there any prerequisites?

You should be comfortable with basic Python (loops, functions, variables) and have used Google Colab before. Everything else you need is covered step by step.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Every e-commerce business loses customers over time. The question that separates a useful analysis from a generic dashboard is specific: which monthly customer cohorts retain best, and what should the business do about it? Committing to that question now keeps your notebook focused and portfolio-ready.

Set Up Your Colab Notebook and Load the Data

You've locked in your business question: which monthly customer cohorts retain best, and what should the retailer do about it. Now it's time to get the raw data into a working environment so you can start answering it.

Google Colab gives you a ready-made Python environment with pandas, matplotlib, and seaborn pre-installed. You just need to create a notebook, add one extra package for reading Excel files, and pull in the dataset.

In this step, get ready to:
  • Create a new Google Colab notebook.
  • Install openpyxl and import your analysis libraries.
  • Load the UCI Online Retail dataset and inspect its quality issues.
Create a new Google Colab notebook
  • Open your browser and go to colab.research.google.com.
  • Sign in with your Google account if prompted.
  • Click File in the top menu bar, then click New notebook.
  • Rename the notebook by clicking the title at the top left. Type Cohort_Retention_Analysis and press Enter.

You should see a blank notebook with one empty code cell ready for input.

Install dependencies and load the dataset

The UCI Online Retail dataset is stored as an Excel file (.xlsx). Colab has pandas pre-installed, but it does not include openpyxl, the engine pandas needs to read Excel files.

  • In the first code cell of your notebook, type the following code to install openpyxl, import your libraries, and load the dataset:
# Install openpyxl for reading Excel files
!pip install openpyxl -q

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

# Load the Online Retail dataset from UCI
url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00352/Online%20Retail.xlsx'
df = pd.read_excel(url)

print(f"Dataset shape: {df.shape}")
df.head()

What does this code do?

  • !pip install openpyxl -q installs the Excel-reading engine that pandas requires. The -q flag keeps the output quiet so it does not clutter your notebook.
  • The three import lines bring in pandas for data manipulation, matplotlib for plotting, and seaborn for statistical visualizations.
  • pd.read_excel(url) downloads the .xlsx file directly from the UCI Machine Learning Repository and loads it into a DataFrame called df.
  • The last two lines print the row/column count and display the first five rows so you can confirm the data loaded.

Before you run this cell, how many rows do you think a real e-commerce retailer generates over a full year of online transactions?

  • Click the play button (or press Shift+Enter) to run the cell.

What should I see?

The cell takes 30-60 seconds to download the Excel file. Once complete, you should see:

  • Dataset shape: (541909, 8) printed above a table showing the first five rows.
  • Eight columns: InvoiceNo, StockCode, Description, Quantity, InvoiceDate, UnitPrice, CustomerID, and Country.

Cell taking too long or erroring out?

  • If you see a connection error, the UCI server may be temporarily slow. Wait 30 seconds and run the cell again.
  • If you see ModuleNotFoundError: No module named 'openpyxl', make sure the !pip install openpyxl -q line is at the very top of the cell, above the imports.
  • Still stuck? Help me debug my data loading cell.
Inspect data quality

You have 541,909 rows of transaction data. Before you can analyze customer retention, you need to know what quality issues exist. Real-world data is never clean.

  • Click the + Code button below your first cell to create a new code cell.
  • Type the following code to check data types and missing values:
# Check data types and missing values
print(df.info())
print("\nMissing values:")
print(df.isnull().sum())

What does this code do?

  • df.info() shows each column's data type and how many non-null entries it has. This tells you where data is missing.
  • df.isnull().sum() counts the exact number of null values per column, giving you a precise picture of the gaps.

What kinds of data quality issues do you expect in a real retailer's transaction log? Think about it before running the cell.

  • Press Shift+Enter to run Cell 2.

You should see that CustomerID has approximately 135,080 null values, and Description has approximately 1,454 nulls. This means about 25% of transactions have no customer ID attached.

Why do missing CustomerIDs matter?

Cohort analysis assigns each customer to the month they first purchased. Without a CustomerID, you cannot place that transaction into any cohort. These rows will need to be removed in the next step.

The Description nulls are less critical. They indicate rows where the product name is missing, but you still have the transaction data (quantity, price, customer) needed for retention analysis.

Seeing a NameError for df?

  • This means Cell 1 did not run successfully. Go back to Cell 1 and run it again before running Cell 2.
  • If Colab disconnected (you see a red Disconnected label in the top right), click Reconnect and re-run Cell 1 first.
  • Need help? Help me fix my notebook connection.

✔️ Awesome, I've got everything!

Great. Make sure both cells ran without errors and you can see the null value counts in your output.

ⓧ I'd like to double check the full code

Here is the complete code for both cells in your notebook so far.

Cell 1:

# Install openpyxl for reading Excel files
!pip install openpyxl -q

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

# Load the Online Retail dataset from UCI
url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00352/Online%20Retail.xlsx'
df = pd.read_excel(url)

print(f"Dataset shape: {df.shape}")
df.head()

Cell 2:

# Check data types and missing values
print(df.info())
print("\nMissing values:")
print(df.isnull().sum())

You have loaded over half a million real e-commerce transactions and already spotted a major quality issue: 135,000 rows with no customer ID. In the next step, you will clean this data by removing the unusable rows, filtering out cancellations, and engineering a Revenue column so the dataset is ready for cohort analysis.

Explore and Clean the Raw Data

In the previous step, you loaded 541,909 rows of transaction data and immediately spotted problems.

Around 135,000 rows have no CustomerID, which means you cannot assign those transactions to a customer cohort. The dataset also contains cancellation records, zero-price rows, and negative quantities. If you skip cleaning, your retention numbers will be meaningless. This step forces you to make deliberate, documented decisions about what stays and what goes.

In this step, get ready to:
  • Remove rows that cannot be used for cohort analysis (missing IDs, cancellations, invalid values).
  • Create a Revenue column and a monthly period column for grouping transactions.
  • Verify your cleaned dataset has zero nulls and sensible revenue values.
Remove invalid rows from the dataset

You need to remove three categories of bad data: rows without a CustomerID (no way to assign a cohort), cancellation rows (they reverse a purchase, not represent one), and rows with non-positive quantities or prices (data entry errors).

  • Create a new code cell below your existing cells. Add this filtering code to remove the bad rows:
# Start with the original row count
print(f"Original rows: {len(df)}")

# Remove rows with missing CustomerID
df_clean = df.dropna(subset=['CustomerID'])
print(f"After removing null CustomerID: {len(df_clean)}")

# Remove cancellations (InvoiceNo starts with 'C')
df_clean = df_clean[~df_clean['InvoiceNo'].astype(str).str.startswith('C')]
print(f"After removing cancellations: {len(df_clean)}")

# Remove rows with non-positive Quantity or UnitPrice
df_clean = df_clean[(df_clean['Quantity'] > 0) & (df_clean['UnitPrice'] > 0)]
print(f"After removing invalid quantities/prices: {len(df_clean)}")

What does this code do?

Each filter prints the remaining row count so you can see exactly how many rows each decision removes.

  • dropna(subset=['CustomerID']) drops any row where CustomerID is null. No ID means no cohort assignment.
  • The ~str.startswith('C') filter removes cancellations. The UCI dataset encodes cancelled invoices with a 'C' prefix on the InvoiceNo column.
  • The final filter keeps only rows where both Quantity and UnitPrice are positive. Zero or negative values indicate data entry errors.
Add Revenue and InvoiceMonth columns

With the bad rows gone, you need two new columns for the analysis. A Revenue column captures how much each transaction earned, and an InvoiceMonth column groups each transaction into the month it occurred.

  • Add this code directly below the filtering code in the same cell:
# Create Revenue column
df_clean = df_clean.copy()
df_clean['Revenue'] = df_clean['Quantity'] * df_clean['UnitPrice']

# Convert InvoiceDate to datetime and create InvoiceMonth
df_clean['InvoiceDate'] = pd.to_datetime(df_clean['InvoiceDate'])
df_clean['InvoiceMonth'] = df_clean['InvoiceDate'].dt.to_period('M')

What does this code do?

  • df_clean.copy() creates a fresh copy of the DataFrame so pandas does not raise a SettingWithCopyWarning when you add new columns.
  • Revenue is simply Quantity multiplied by UnitPrice. Every value will be positive because you already removed non-positive rows.
  • dt.to_period('M') converts each datetime into a monthly period (e.g., 2011-01). This is the granularity you will use for cohort grouping in the next step.
Verify your cleaned dataset

The final piece of this cell prints the shape, null counts, and revenue statistics so you can confirm everything is clean.

  • Add this verification code at the bottom of the same cell:
# Verify
print(f"\nCleaned dataset shape: {df_clean.shape}")
print(f"\nNull values:\n{df_clean.isnull().sum()}")
print(f"\nRevenue statistics:\n{df_clean['Revenue'].describe()}")

Before you run this cell, think about what you expect. You started with 541,909 rows. You removed nulls, cancellations, and invalid values. Roughly how many rows do you think survived?

  • Run the cell by pressing Shift+Enter.

You should see approximately 392,000 rows remaining. The null values output should show zero across all columns. The revenue statistics should show a positive minimum and a mean around 17-20 GBP.

Why did we lose so many rows?

About 25% of the original data lacked a CustomerID. Those transactions were likely guest checkouts that the system did not track. Removing them is a trade-off: you lose data volume, but every remaining row can now be assigned to a customer cohort.

Seeing unexpected row counts or errors?

  • If you get a NameError: name 'df' is not defined, your Cell 1 (data loading) has not been run yet. Go back and run it first.
  • If your row count after removing null CustomerIDs is much lower than expected, make sure you used subset=['CustomerID'] in dropna(). Without it, pandas drops rows with ANY null value (including Description nulls you want to keep).
  • If Revenue statistics show negative values, check that your quantity/price filter uses > 0 (strictly greater than), not >= 0 (which would keep zeros).

Help me debug my data cleaning code in pandas

✔️ Awesome, I've got everything!

Make sure you have saved your notebook. Your Cell 3 should produce row-count printouts at each step and end with the shape, null check, and revenue statistics.

ⓧ I'd like to double check the full code

Here is the complete Cell 3. Make sure your code matches:

print(f"Original rows: {len(df)}")

df_clean = df.dropna(subset=['CustomerID'])
print(f"After removing null CustomerID: {len(df_clean)}")

df_clean = df_clean[~df_clean['InvoiceNo'].astype(str).str.startswith('C')]
print(f"After removing cancellations: {len(df_clean)}")

df_clean = df_clean[(df_clean['Quantity'] > 0) & (df_clean['UnitPrice'] > 0)]
print(f"After removing invalid quantities/prices: {len(df_clean)}")

df_clean = df_clean.copy()
df_clean['Revenue'] = df_clean['Quantity'] * df_clean['UnitPrice']

df_clean['InvoiceDate'] = pd.to_datetime(df_clean['InvoiceDate'])
df_clean['InvoiceMonth'] = df_clean['InvoiceDate'].dt.to_period('M')

print(f"\nCleaned dataset shape: {df_clean.shape}")
print(f"\nNull values:\n{df_clean.isnull().sum()}")
print(f"\nRevenue statistics:\n{df_clean['Revenue'].describe()}")

Your data is clean, documented, and ready for analysis. Next up, you will group customers into monthly cohorts and calculate how many return over time.

Build the Cohort Retention Matrix

Your cleaned dataset is ready. Now comes the core analytical technique: cohort analysis.

Cohort analysis groups customers by the month they first purchased, then tracks what percentage return in each subsequent month. The retention matrix you build here is the data structure that powers the final heatmap visualization.

In this step, get ready to:
  • Find each customer's first purchase month and assign them to a cohort.
  • Calculate how many months after their first purchase each transaction occurs.
  • Build a retention percentage matrix showing how cohorts retain over time.
Assign each customer to their cohort month

Every customer's "cohort" is defined by the month they made their very first purchase. A customer who first bought in December 2010 belongs to the December 2010 cohort, regardless of when they buy again later.

  • Create a new code cell below your cleaning cell, and add the following code to find each customer's earliest purchase month and merge it back onto the main dataframe:
# Find each customer's first purchase month (cohort month)
cohort_month = df_clean.groupby('CustomerID')['InvoiceMonth'].min().reset_index()
cohort_month.columns = ['CustomerID', 'CohortMonth']

# Merge cohort month back to the main dataframe
df_clean = df_clean.merge(cohort_month, on='CustomerID')

What does this code do?

The groupby('CustomerID')['InvoiceMonth'].min() finds the earliest month each customer ever purchased. This becomes their cohort assignment.

The .merge() joins that cohort month back onto every row of df_clean. Now each transaction knows which cohort its customer belongs to.

Calculate the cohort index

The cohort index represents how many months have passed since a customer's first purchase. A transaction in the same month as the first purchase has index 0. A transaction one month later has index 1.

  • Add this code below what you already have in the same cell:
# Calculate cohort index (months since first purchase)
df_clean['CohortIndex'] = (
    df_clean['InvoiceMonth'].apply(lambda x: x.ordinal) -
    df_clean['CohortMonth'].apply(lambda x: x.ordinal)
)

What is the ordinal attribute?

Each pandas Period object has an ordinal attribute. It returns an integer representing the Period's position in its frequency sequence. For monthly periods, subtracting one ordinal from another gives you the number of months between them.

  • Run the cell to execute both operations. Then confirm your new columns exist by adding this line at the bottom of the cell and re-running:
df_clean[['CustomerID', 'InvoiceMonth', 'CohortMonth', 'CohortIndex']].head()

You should see a table with four columns. CohortMonth shows the customer's first purchase month, and CohortIndex shows a 0 for transactions in that same month (or higher numbers for later months).

Seeing an AttributeError on ordinal?

Make sure your InvoiceMonth column uses dt.to_period('M') from the cleaning step. If it is still a regular datetime column, the ordinal attribute will not be available.

help me fix an AttributeError when calculating CohortIndex with Period.ordinal

  • Once you see the table with correct columns, remove the df_clean[['CustomerID', 'InvoiceMonth', 'CohortMonth', 'CohortIndex']].head() line from the cell. You will replace it with the retention matrix code next.
Build the retention percentage matrix

Now you will count how many unique customers appear in each cohort-period combination, then convert those raw counts into percentages by dividing each row by the number of customers in period 0.

  • Add this code at the bottom of your cell (replacing the line you just removed):
# Count unique customers per cohort per period
cohort_data = df_clean.groupby(['CohortMonth', 'CohortIndex'])['CustomerID'].nunique().reset_index()
cohort_data.columns = ['CohortMonth', 'CohortIndex', 'CustomerCount']

# Pivot to create the cohort table
cohort_table = cohort_data.pivot(index='CohortMonth', columns='CohortIndex', values='CustomerCount')

# Calculate retention: divide each row by its first column (period 0)
cohort_size = cohort_table.iloc[:, 0]
retention_matrix = cohort_table.divide(cohort_size, axis=0)

# Display first 5 rows and columns
print("Retention Matrix (first 5 cohorts, first 5 periods):")
print(retention_matrix.iloc[:5, :5])

What does this code do?

The groupby counts unique CustomerIDs in each cohort-period cell. The .pivot() reshapes the data so rows are cohort months and columns are period indices (0, 1, 2, ...).

Dividing each row by its period-0 count converts raw customer counts into retention percentages. Period 0 is always 1.0 (100%) because every customer is active in the month they first purchased.

Before you run this, what do you expect the first column (period 0) to show for every cohort?

  • Run the cell.

You should see a 5x5 table where the first column (period 0) is 1.0 for every cohort. Subsequent columns show declining values like 0.3 or 0.2, representing the fraction of customers who returned.

Seeing NaN values in the matrix?

NaN values in later columns are expected for newer cohorts. A cohort that started in November 2011 only has 1-2 months of data, so columns for period 6+ will be empty.

If you see NaN in the first column (period 0), your groupby or pivot has an issue. Check that cohort_data has rows for every CohortMonth at CohortIndex 0.

help me debug NaN values in my retention matrix

✔️ Awesome, I've got everything!

Great. Double check you have saved the cell and the output shows 1.0 values in the first column.

ⓧ I'd like to double check the full code

Your Cell 4 should contain exactly this code:

# Find each customer's first purchase month (cohort month)
cohort_month = df_clean.groupby('CustomerID')['InvoiceMonth'].min().reset_index()
cohort_month.columns = ['CustomerID', 'CohortMonth']

# Merge cohort month back to the main dataframe
df_clean = df_clean.merge(cohort_month, on='CustomerID')

# Calculate cohort index (months since first purchase)
df_clean['CohortIndex'] = (
    df_clean['InvoiceMonth'].apply(lambda x: x.ordinal) -
    df_clean['CohortMonth'].apply(lambda x: x.ordinal)
)

# Count unique customers per cohort per period
cohort_data = df_clean.groupby(['CohortMonth', 'CohortIndex'])['CustomerID'].nunique().reset_index()
cohort_data.columns = ['CohortMonth', 'CohortIndex', 'CustomerCount']

# Pivot to create the cohort table
cohort_table = cohort_data.pivot(index='CohortMonth', columns='CohortIndex', values='CustomerCount')

# Calculate retention: divide each row by its first column (period 0)
cohort_size = cohort_table.iloc[:, 0]
retention_matrix = cohort_table.divide(cohort_size, axis=0)

# Display first 5 rows and columns
print("Retention Matrix (first 5 cohorts, first 5 periods):")
print(retention_matrix.iloc[:5, :5])

Your retention matrix is ready. Next, you will turn these numbers into a visual heatmap that makes retention patterns jump out at a glance.

Visualize Retention with a Heatmap

Your retention matrix is built. Each cell holds a concrete percentage showing how many customers came back in each subsequent month. But scrolling through rows and columns of numbers makes it hard to spot patterns at a glance.

A heatmap translates those percentages into color intensity, making high-retention and low-retention cohorts jump out immediately. In this step, you'll create a polished seaborn heatmap and write a brief interpretation of the patterns you see.

In this step, get ready to:
  • Create a seaborn heatmap from the retention matrix with percentage annotations.
  • Add descriptive labels and a title for readability.
  • Write a markdown interpretation highlighting retention patterns.
Create the retention heatmap

Hiring managers scanning a portfolio look at visualizations first. A well-labeled heatmap communicates months of customer behavior in a single image, something a raw table cannot do.

  • Create a new code cell below your retention matrix cell.
  • Build the heatmap by pasting this code:
# Create the retention heatmap
plt.figure(figsize=(12, 8))
sns.heatmap(
    retention_matrix,
    annot=True,
    fmt='.0%',
    cmap='YlGnBu',
    vmin=0,
    vmax=0.5
)
plt.title('Monthly Cohort Retention Analysis - UK Online Retailer', fontsize=14)
plt.xlabel('Cohort Period (Months Since First Purchase)', fontsize=11)
plt.ylabel('Cohort Month', fontsize=11)
plt.yticks(rotation=0)
plt.tight_layout()
plt.show()

What does this code do?

This cell creates a single figure and fills it with a color-coded grid of your retention data.

  • plt.figure(figsize=(12, 8)) sets the canvas size to 12 inches wide by 8 inches tall, giving each cell room to display its annotation.
  • annot=True prints the actual percentage inside every cell. fmt='.0%' formats those numbers as whole-number percentages (e.g. 35%).
  • cmap='YlGnBu' uses a yellow-green-blue gradient where darker blue means higher retention.
  • vmin=0, vmax=0.5 caps the color scale at 50%, which spreads the color range across realistic retention values instead of wasting most of the gradient on the 100% column.
  • plt.yticks(rotation=0) keeps the cohort month labels horizontal so they're easy to read.

Before you run this cell, think about what you expect the heatmap to look like. Will the first column be the darkest or lightest? Where do you think the biggest color drop will be?

  • Run the cell.

A large heatmap renders below the cell. The first column (period 0) is the darkest blue across all rows because every customer is active in their first month. Subsequent columns fade toward yellow as retention drops.

Not seeing the heatmap?

  • If you see a NameError for retention_matrix, make sure you ran Cell 4 (the cohort matrix cell) first. Colab cells must be run in order.
  • If the chart looks blank or all one color, check that your retention matrix contains values between 0 and 1. Run retention_matrix.iloc[:3, :3] to confirm.
  • If annotations overlap and are unreadable, confirm figsize=(12, 8) is set. A smaller figure squishes the text.

Why isn't my seaborn heatmap rendering in Colab?

Interpret the retention patterns

A chart without interpretation is just decoration. Adding a few sentences about what you observe proves to a reader that you can extract meaning from data, not just plot it.

  • Click the + Text button in the toolbar above your cells to add a new markdown cell below the heatmap.
  • Write 2-3 sentences addressing these questions:
  • Which cohorts retain best at period 3 and beyond? Look for rows with darker cells in the later columns.
  • Do you see any seasonal patterns? Compare cohorts from holiday months (November, December) to summer months.
  • Where does the steepest drop-off happen across most cohorts?

Write for a hiring manager

Reference specific cohort months and percentages from your heatmap. "The December 2010 cohort retains at 38% by period 3" is far stronger than "some cohorts retain better than others."

  • Run the markdown cell to render your interpretation.

Your interpretation now appears as formatted text directly below the heatmap. The two together form a complete visual story.

✔️ Awesome, I've got everything!

Great. Double check you've saved your notebook (Colab auto-saves, but you can press Ctrl+S to be sure).

ⓧ I'd like to double check the full code

Here is the complete code for Cell 5:

# Create the retention heatmap
plt.figure(figsize=(12, 8))
sns.heatmap(
    retention_matrix,
    annot=True,
    fmt='.0%',
    cmap='YlGnBu',
    vmin=0,
    vmax=0.5
)
plt.title('Monthly Cohort Retention Analysis - UK Online Retailer', fontsize=14)
plt.xlabel('Cohort Period (Months Since First Purchase)', fontsize=11)
plt.ylabel('Cohort Month', fontsize=11)
plt.yticks(rotation=0)
plt.tight_layout()
plt.show()

Your notebook now has a polished, annotated heatmap that makes retention patterns visible at a glance. Next up, you'll turn those patterns into a written business recommendation backed by specific numbers from your analysis.

Write Your Business Recommendation

Your heatmap tells a compelling visual story. But hiring managers consistently say that a chart without a recommendation is "just decoration."

This step transforms your analysis from a technical exercise into a business deliverable. You will extract specific numbers from your retention matrix and write a structured recommendation that proves you can turn data into action.

In this step, get ready to:
  • Extract key retention percentages from your matrix using pandas indexing.
  • Write a structured business recommendation with specific numbers, an action plan, and noted limitations.
Pull specific numbers from the retention matrix

A strong recommendation cites exact figures from the data. Vague statements like "retention drops off" carry no weight. You need specific percentages that a stakeholder can act on.

  • Create a new code cell below your heatmap interpretation markdown cell.
  • Extract the key retention statistics by pasting this code:
# Extract key numbers for the recommendation
avg_retention_month1 = retention_matrix.iloc[:, 1].mean()
avg_retention_month6 = retention_matrix.iloc[:, 6].mean()
best_cohort_idx = retention_matrix.iloc[:, 3].idxmax()
best_cohort_rate = retention_matrix.iloc[:, 3].max()

print(f"Average retention at month 1: {avg_retention_month1:.1%}")
print(f"Average retention at month 6: {avg_retention_month6:.1%}")
print(f"Best cohort at month 3: {best_cohort_idx} ({best_cohort_rate:.1%})")

What does this code do?

Each line slices into your retention_matrix to pull a specific insight.

  • iloc[:, 1].mean() averages column 1 (month 1) across all cohorts, showing how many customers return after their first purchase.
  • iloc[:, 6].mean() averages column 6 (month 6), revealing the long-term loyal core.
  • iloc[:, 3].idxmax() finds which cohort month has the highest retention at period 3.
  • Run the cell by pressing Shift+Enter.

You should see three printed lines showing the average retention at month 1, average retention at month 6, and the best-performing cohort at month 3 with its retention rate.

Write down your numbers

Note the specific percentages printed in the output. You will plug these exact numbers into your recommendation markdown cell next.

Seeing a KeyError or IndexError?

This means your retention matrix does not have enough columns. Make sure you ran all the cells above in order (Cells 1 through 5) so that retention_matrix is defined with 13 columns (periods 0 through 12).

Help me debug my retention matrix extraction code

Write your structured business recommendation

Now you have concrete numbers. A strong portfolio recommendation follows a clear structure: state the question, present findings with evidence, make a recommendation, and acknowledge limitations.

  • Create a new Markdown cell below Cell 6. Click the + Text button in the toolbar to add a text cell.
  • Paste the following recommendation template into the markdown cell:
## Business Recommendation

### Business Question
Which monthly customer cohorts retain best over time, and what should the retailer do about it?

### Key Findings
- Average retention drops to [X]% by month 1, meaning [Y]% of customers never return after their first purchase
- By month 6, average retention stabilizes around [Z]%, representing the loyal core
- The [best cohort month] cohort retains at [rate]% by month 3, significantly above average

### Recommendation
The retailer should implement a re-engagement campaign targeting customers who have not returned within 30 days of their first purchase, as this is where the steepest drop occurs. Additionally, the acquisition strategies used during [best cohort month] should be studied and replicated, as that cohort demonstrates significantly higher long-term retention. A loyalty program incentivizing the second and third purchase would address the largest single point of customer loss.

### Limitations
- Data covers Dec 2010 to Dec 2011; patterns may not reflect current behavior
- ~25% of transactions lack CustomerID and were excluded, potentially biasing results toward tracked (likely returning) customers
- No marketing or channel data is available to explain why certain cohorts perform better

What does each section do?

  • Business Question states the one-sentence question your entire analysis answers.
  • Key Findings presents 2-3 bullet points backed by specific numbers from your output.
  • Recommendation tells the business what to DO based on your findings.
  • Limitations shows analytical maturity by acknowledging what the data cannot tell you.
  • Replace every bracketed placeholder ([X], [Y], [Z], [best cohort month], [rate]) with the actual numbers printed by Cell 6.
  • For the [Y]% placeholder (customers who never return), calculate it as 100 minus your month 1 retention percentage.

Why acknowledge limitations?

Hiring managers specifically look for this. An analyst who presents findings without caveats looks naive. Noting that ~25% of transactions were excluded and that no marketing channel data exists shows you understand the boundaries of your analysis.

  • Click outside the markdown cell (or press Shift+Enter) to render the formatted markdown.

Your markdown cell should render with a large "Business Recommendation" heading followed by four clearly formatted sub-sections. The Key Findings bullets should contain your actual percentages, not the bracket placeholders.

Markdown not rendering?

Make sure you created a Text cell (using + Text), not a code cell. If you pasted into a code cell, the markdown syntax will print as plain text instead of rendering as formatted headings and bullets.

Help me format my business recommendation as a markdown cell in Colab

Verify your complete notebook

Before you call this step done, scroll through your entire notebook from top to bottom. What do you expect to see at each stage?

  • Scroll to the top of your notebook and use Runtime > Run all to re-run every cell from scratch.

Your notebook should execute cleanly from top to bottom. At the end, you should see Cell 6 printing three retention statistics, followed by a rendered markdown cell with your structured Business Recommendation containing your actual numbers.

What makes this portfolio-ready?

Your notebook now tells a complete story: it loads raw data, cleans it with documented decisions, builds a cohort retention matrix, visualizes the patterns, and delivers a structured recommendation backed by specific numbers. This is the format hiring managers expect from a take-home analysis.

✔️ Awesome, I've got everything!

Great. Double check you have replaced all bracket placeholders with your actual computed values.

ⓧ I'd like to double check the full code

Here is Cell 6 (the code cell for extracting numbers):

# Extract key numbers for the recommendation
avg_retention_month1 = retention_matrix.iloc[:, 1].mean()
avg_retention_month6 = retention_matrix.iloc[:, 6].mean()
best_cohort_idx = retention_matrix.iloc[:, 3].idxmax()
best_cohort_rate = retention_matrix.iloc[:, 3].max()

print(f"Average retention at month 1: {avg_retention_month1:.1%}")
print(f"Average retention at month 6: {avg_retention_month6:.1%}")
print(f"Best cohort at month 3: {best_cohort_idx} ({best_cohort_rate:.1%})")

And here is Cell 7 (the markdown cell). Remember to replace the bracketed placeholders with your numbers:

## Business Recommendation

### Business Question
Which monthly customer cohorts retain best over time, and what should the retailer do about it?

### Key Findings
- Average retention drops to [X]% by month 1, meaning [Y]% of customers never return after their first purchase
- By month 6, average retention stabilizes around [Z]%, representing the loyal core
- The [best cohort month] cohort retains at [rate]% by month 3, significantly above average

### Recommendation
The retailer should implement a re-engagement campaign targeting customers who have not returned within 30 days of their first purchase, as this is where the steepest drop occurs. Additionally, the acquisition strategies used during [best cohort month] should be studied and replicated, as that cohort demonstrates significantly higher long-term retention. A loyalty program incentivizing the second and third purchase would address the largest single point of customer loss.

### Limitations
- Data covers Dec 2010 to Dec 2011; patterns may not reflect current behavior
- ~25% of transactions lack CustomerID and were excluded, potentially biasing results toward tracked (likely returning) customers
- No marketing or channel data is available to explain why certain cohorts perform better

Your notebook is now a complete, portfolio-ready business deliverable. Next up, you will tackle a Secret Mission that segments customers by purchase frequency to reveal where revenue truly concentrates.

Secret mission

Customer Segmentation by Purchase Frequency

Your cohort analysis reveals when customers come back. But who are your most valuable customers? Segment buyers by purchase frequency and discover whether revenue is concentrated in a small group of VIPs or spread evenly across all customers.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely in Google Colab, so there are no ongoing costs. Your notebook persists in Google Drive indefinitely at no charge.

Decide whether to keep your notebook in Google Drive, download it for your portfolio, or delete it entirely.

Resources you used:

  • Google Colab notebook (stored in your Google Drive).
  • Colab runtime with openpyxl installed (ephemeral, resets automatically when the runtime disconnects).

Keep everything running

No action needed. Choose this if you plan to revisit your analysis or share it with others.

  • Your notebook stays in Google Drive and is accessible anytime at colab.research.google.com.
  • Share it by clicking Share in the top right corner of Colab and copying the link. This is a great portfolio piece.
  • To download a copy, click File, then Download, then Download .ipynb. You can upload the .ipynb file to a GitHub repository for your portfolio.

Pause - I'll come back to this later

Disconnect the Colab runtime to free up memory. Your notebook file stays safe in Google Drive.

  • In your Colab notebook, click Runtime in the top menu, then click Disconnect and delete runtime.
  • This frees up the cloud compute session. The openpyxl package will need to be reinstalled when you reconnect, but that happens automatically when you re-run Cell 1.
  • Your notebook code and outputs remain saved in Google Drive.

Delete - I don't want to use this again

Remove the notebook entirely from your Google Drive.

  • In your Colab notebook, click File in the top menu, then click Move to trash.
  • To permanently delete it, go to drive.google.com/drive/trash, find the notebook, right-click it, and select Delete forever.
  • The Colab runtime (and any installed packages) is automatically cleaned up when disconnected. No further action needed for the runtime.

Nice Work!

Nice Work!

You just completed an end-to-end cohort retention analysis on a real 541K-row e-commerce dataset. You now have a portfolio-ready notebook that proves you can take messy transactional data and turn it into actionable business insight.

Here's what you learned:

  • Cleaned genuinely messy data by handling missing CustomerIDs, cancellation records, and invalid quantities/prices, then documented every decision with printed row counts.
  • Built a cohort retention matrix from scratch using pandas groupby, merge, and pivot operations to track how monthly customer cohorts behave over time.
  • Produced a polished seaborn heatmap and wrote a structured business recommendation backed by specific retention numbers from your analysis.
  • Secret Mission: Segmented customers by purchase frequency (One-Time, Repeat, VIP) and revealed how revenue concentrates in a small group of repeat buyers.

Ready to quiz yourself?