Lesson 4 · Real-World Data Analytics
Cohort Analysis in Python: Customer Retention Heatmap | Data Analytics #4
Are customers coming back? We group real retail customers by their first purchase month, build a cohort retention table in pandas, and visualise it as a…
- CourseReal-World Data Analytics
- Lesson4 of 100
- FormatJupyter notebook · 30 code cells
- Data1 dataset
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- online_retail_clean.csv preview36.2 MB
📓 Full notebook
Download .ipynbData Analytics 100, Video 4: Cohort Analysis and Customer Retention#
- Video four of the hundred-video real-world data analytics series.
- Grouping real customers by their real first purchase month, and tracking how each real cohort actually retains over time.
- Let's get into it.
This lesson is for learners who want to measure customer loyalty properly, rather than relying on one overall repeat rate. The business question is: once a customer buys for the first time, how likely are they to come back in the following months, and is the business getting better or worse at keeping them? You will use cohort analysis. A cohort is a group of customers who share a starting point, here the month of their first purchase, and you follow each group over time. The data is the cleaned UK online gift retailer file from Video 1, online_retail_clean.csv, using CustomerID, InvoiceNo, InvoiceDate and Revenue, covering December 2010 to December 2011.
In this lesson you will:
- Assign each customer to a cohort month and calculate a cohort index (months since first purchase).
- Build a retention table with
groupby,pivotanddivide, and draw it as a heatmap. - Track revenue per cohort and compare customers who return early with those who do not.
Prerequisites: you should be comfortable with pandas groupby, merge and datetime columns. Videos 1 and 2 of the series cover these.
Part 1: What a Cohort Actually Is#
A cohort groups customers by when they started, so you can compare like with like: January starters with January starters, rather than mixing new and long-standing customers. The code loads the cleaned file with parse_dates=['InvoiceDate'] and makes sure CustomerID is an integer with astype(int), so it works cleanly as a grouping and merge key. The output (391150, 9) confirms you have the same cleaned data as in earlier lessons.
import pandas as pd
import matplotlib.pyplot as plt
clean = pd.read_csv('online_retail_clean.csv', parse_dates=['InvoiceDate'])
clean['CustomerID'] = clean['CustomerID'].astype(int)
clean.shape
Part 2: Real Invoice Month#
Cohort analysis works in months, so every transaction needs a month label. The code takes the raw date values and converts them with .astype('datetime64[M]'), which cuts each timestamp down to the first day of its month. So 2010-12-01 08:26 becomes 2010-12-01. The output shows the first three rows side by side to confirm the conversion. Tip: if you use .dt.to_period('M') instead you get a period type, which behaves differently in arithmetic, so pick one approach and stick to it.
clean['InvoiceMonth'] = clean['InvoiceDate'].values.astype('datetime64[M]')
clean[['InvoiceDate', 'InvoiceMonth']].head(3)
Part 3: Real Cohort Month per Customer#
A customer's cohort is the month of their first purchase. groupby('CustomerID')['InvoiceMonth'].min() finds the earliest month for each customer, and .rename('CohortMonth') gives the Series a clear name for the merge. The output shows customer 12346 started in January 2011, 12347 and 12348 in December 2010, and 12349 not until November 2011. Note that the data only starts in December 2010, so the December cohort also contains customers who may have been buying before the data begins.
first_purchase = clean.groupby('CustomerID')['InvoiceMonth'].min()
first_purchase = first_purchase.rename('CohortMonth')
first_purchase.head()
Part 4: Attaching Cohort Month Back to Every Transaction#
Each transaction now needs to know which cohort its customer belongs to, so you can see when cohorts come back. clean.merge(first_purchase, on='CustomerID') adds the CohortMonth column to every row by matching customer IDs. The output shows customer 17850's first rows, with both invoice month and cohort month in December 2010, as you would expect for a first purchase. Because each customer has exactly one cohort month, the merge does not create extra rows.
merged = clean.merge(first_purchase, on='CustomerID')
merged[['CustomerID', 'InvoiceMonth', 'CohortMonth']].head(3)
Part 5: Real Cohort Index#
The cohort index puts every cohort on the same timeline: month 0 is the first purchase month, month 1 the next month, and so on. The code finds the difference in years and in months separately with .dt.year and .dt.month, then combines them as year_diff * 12 + month_diff. This handles the change from December to January correctly. describe() shows the index runs from 0 to 12, with a median of 3. At least a quarter of transactions happen in a customer's very first month.
year_diff = merged['InvoiceMonth'].dt.year - merged['CohortMonth'].dt.year
month_diff = merged['InvoiceMonth'].dt.month - merged['CohortMonth'].dt.month
merged['CohortIndex'] = year_diff * 12 + month_diff
merged['CohortIndex'].describe()
Part 6: Real Active Customers per Cohort per Month#
Retention is about people, not transactions, so you count unique customers in each cohort at each month offset. groupby(['CohortMonth', 'CohortIndex'])['CustomerID'].nunique() does that, and reset_index() turns the result back into a flat table. The output shows the December 2010 cohort started with 884 customers; 323 of them bought again the following month, 286 in month 2 and 339 in month 3. Using nunique() rather than count() is essential here, otherwise a customer with ten lines would count ten times.
cohort_data = merged.groupby(['CohortMonth', 'CohortIndex'])['CustomerID'].nunique().reset_index()
cohort_data.head()
Part 7: Real Cohort Pivot Table#
A long list is hard to read, so you reshape it into a grid: one row per cohort and one column per month since first purchase. pivot(index='CohortMonth', columns='CohortIndex', values='CustomerID') does this. The shape line is not displayed; the output shows the first five cohorts and first six months with iloc[:5, :6]. The January 2011 cohort started with 416 customers and only 91 returned in month 1. Values are floats because later cohorts have empty cells for months that have not happened yet.
cohort_pivot = cohort_data.pivot(index='CohortMonth', columns='CohortIndex', values='CustomerID')
cohort_pivot.shape
cohort_pivot.iloc[:5, :6]
Part 8: Real Cohort Sizes#
To turn counts into rates, you need each cohort's starting size. cohort_pivot.iloc[:, 0] takes the first column, month 0, which is the number of new customers in each cohort. December 2010 is by far the largest at 884, partly because it includes everyone already active when the data starts. Other cohorts range from 169 in August 2011 to 452 in March 2011. December 2011 has only 41, because the data ends on 9 December. Watch for partial periods at both ends of any dataset.
cohort_sizes = cohort_pivot.iloc[:, 0]
cohort_sizes
Part 9: Real Retention Rate Table#
Raw counts are hard to compare because cohorts start at different sizes. The retention rate divides each cell by the cohort's starting size. divide(cohort_sizes, axis=0) divides each row by its own value; axis=0 tells pandas to line the sizes up with the row index. Month 0 is always 1.0 (100%). The output shows the December 2010 cohort kept 36.5% in month 1, while the March 2011 cohort kept only 14.8%. Most later cohorts sit around 15% to 30% in their early months.
retention = cohort_pivot.divide(cohort_sizes, axis=0)
retention.round(3).iloc[:6, :6]
Part 10: Real Month-1 Retention Across Cohorts#
Month-1 retention, the share who return the very next month, is an early warning sign that is easy to compare across cohorts. retention[1] takes the month-1 column, dropna() removes the December 2011 cohort which has no month 1 yet, and sort_index() keeps cohorts in date order. Only the mean is displayed: 0.204, so on average about one in five new customers buys again the following month. Run month1_retention.round(3) on its own to see each cohort's value.
month1_retention = retention[1].dropna().sort_index()
month1_retention.round(3)
month1_retention.mean().round(3)
Part 11: Visualizing the Real Retention Heatmap#
A heatmap shows the whole retention table at once, with colour standing in for the rate. plt.imshow draws the grid; cmap='YlGnBu' runs from yellow (low) to dark blue (high), and vmin=0, vmax=1 fixes the colour scale from 0% to 100%. strftime('%Y-%m') formats the cohort labels for the y-axis. The chart is saved to cohort_retention_heatmap.png. Look for the dark first column (always 100%), the noticeably stronger December 2010 row, and the empty triangle in the lower right where later cohorts have no data yet.
plt.figure(figsize=(11, 7))
plt.imshow(retention, cmap='YlGnBu', aspect='auto', vmin=0, vmax=1)
plt.colorbar(label='Real Retention Rate')
plt.yticks(range(len(retention.index)), [d.strftime('%Y-%m') for d in retention.index])
plt.xlabel('Real Months Since First Purchase')
plt.title('Real Customer Retention Heatmap by Cohort')
plt.tight_layout()
plt.savefig('cohort_retention_heatmap.png', dpi=120)
plt.close()
Part 12: Real Retention Curves for a Few Cohorts#
Line charts make it easier to compare the shape of retention over time for a handful of cohorts. The code loops over the first six cohorts and plots each row of retention against the months since first purchase, with marker='o' marking each point. The chart is saved to cohort_retention_curves.png. Look for the sharp drop from 100% to month 1 and whether lines then flatten, which suggests a stable core of loyal customers. Expect the December 2010 line to sit clearly above the others.
plt.figure(figsize=(9, 6))
for cohort in retention.index[:6]:
plt.plot(retention.columns, retention.loc[cohort], marker='o', label=cohort.strftime('%Y-%m'))
plt.xlabel('Real Months Since First Purchase')
plt.ylabel('Real Retention Rate')
plt.title('Real Retention Curves for the First Six Cohorts')
plt.legend()
plt.tight_layout()
plt.savefig('cohort_retention_curves.png', dpi=120)
plt.close()
Part 13: Real Revenue per Cohort per Month#
Retention counts people, but the business also cares how much returning customers spend. The code repeats the grouping and pivot, this time summing Revenue instead of counting customers. The output, rounded to whole pounds, shows the December 2010 cohort spent 565,200 in its first month and still 325,803 in month 5. The January 2011 cohort dropped from 289,033 in month 0 to 53,129 in month 1. This tells you which cohorts are actually driving revenue over time.
revenue_data = merged.groupby(['CohortMonth', 'CohortIndex'])['Revenue'].sum().reset_index()
revenue_pivot = revenue_data.pivot(index='CohortMonth', columns='CohortIndex', values='Revenue')
revenue_pivot.iloc[:5, :6].round(0)
Part 14: Real Average Revenue per Retained Customer#
Dividing revenue by active customers shows whether those who stay are spending more or less per head. revenue_pivot / cohort_pivot divides cell by cell, since both tables share the same rows and columns. For the December 2010 cohort, spend per active customer rises from 639.37 in month 0 to 847.21 in month 1 and 925.58 in month 5. Other cohorts are flatter, mostly between about 360 and 700. Rising values suggest that the customers who stay tend to be the bigger buyers.
avg_revenue_per_active = revenue_pivot / cohort_pivot
avg_revenue_per_active.iloc[:5, :6].round(2)
Part 15: Best and Worst Real Cohorts by Month-1 Retention#
Naming the best and worst cohorts makes the analysis easier to report. idxmax() and idxmin() return the index label, here the cohort month, with the highest and lowest month-1 retention. The f-strings format the date and turn the rate into a percentage. The output shows December 2010 was best at 36.5% and November 2011 weakest at 11.1%. Remember that November's new customers include many one-off Christmas shoppers, and December 2010 includes existing customers, so seasonality may explain much of the gap.
best_cohort = month1_retention.idxmax()
worst_cohort = month1_retention.idxmin()
print(f'Best real cohort: {best_cohort.strftime("%Y-%m")} at {round(month1_retention[best_cohort]*100,1)}% month-1 retention')
print(f'Weakest real cohort: {worst_cohort.strftime("%Y-%m")} at {round(month1_retention[worst_cohort]*100,1)}% month-1 retention')
Part 16: Is Retention Actually Trending Over Time#
A key management question is whether retention is improving or getting worse. The code splits the month-1 retention series in half with iloc and // (whole-number division) and averages each half. The earlier cohorts average 0.22 and the later ones 0.189, a drop of about 3 percentage points. That looks like a decline, but treat it carefully: the early half includes the unusual December 2010 cohort, and the later half includes the pre-Christmas months. Check whether removing December 2010 changes the conclusion.
early_cohorts = month1_retention.iloc[:len(month1_retention)//2]
late_cohorts = month1_retention.iloc[len(month1_retention)//2:]
round(early_cohorts.mean(), 3), round(late_cohorts.mean(), 3)
Part 17: Real Long-Term Retention at Month 6#
Month 1 shows early behaviour; month 6 shows whether customers stay over the longer term. retention[6].dropna() takes the month-6 column and keeps only cohorts old enough to have one. Only the mean is displayed: 0.244, so on average about 24% of a cohort is still buying six months after starting. That is higher than the average month-1 rate of about 20%, which can happen because seasonal peaks bring customers back. Run the round(3) line alone to see which cohorts drive it.
month6_retention = retention[6].dropna()
month6_retention.round(3)
month6_retention.mean().round(3)
Part 18: Real Cumulative Retained Customer-Months#
Customer-months add up how many months each cohort's customers were active, a simple measure of total engagement. cohort_pivot.sum(axis=1) sums across each row, and the result is sorted from highest to lowest. December 2010 dominates with 4,801 customer-months, more than the next two cohorts combined (January at 1,628 and March at 1,290). Older cohorts have had more months to build up a total, so compare cohorts of similar age rather than ranking them all together.
customer_months = cohort_pivot.sum(axis=1)
customer_months.sort_values(ascending=False).round(0)
Part 19: Real Sanity Check on the Pivot Math#
When a table is built through several reshaping steps, it is worth checking one cell by hand. The code filters merged directly for the first cohort and cohort index 1, counts unique customers, and compares the result with the matching pivot cell, cohort_pivot.iloc[0, 1]. The output is True, so the groupby and pivot produced the right number (323). Spot checks like this give you confidence before sharing the results.
manual_check = merged[(merged['CohortMonth'] == cohort_pivot.index[0]) & (merged['CohortIndex'] == 1)]['CustomerID'].nunique()
manual_check == cohort_pivot.iloc[0, 1]
Part 20: Saving the Real Retention Table#
Saving the retention table lets you reuse it in a report, dashboard or later lesson. The code rounds to four decimal places and writes cohort_retention_table.csv, keeping the index so the cohort months are saved. It then reloads with index_col=0, which uses the first column as the row index again, and compares shapes. The output True shows the rows and columns match. Without index_col=0 the reloaded table would gain an extra column and the check would fail.
retention.round(4).to_csv('cohort_retention_table.csv')
reloaded = pd.read_csv('cohort_retention_table.csv', index_col=0)
reloaded.shape == retention.shape
Part 21: Real One-Time vs Real Repeat Customers#
Some customers buy only in their first month and never return. The code groups merged by customer and applies a small lambda function that checks whether every one of their transactions has a cohort index of 0. That gives True or False per customer, and .mean() turns it into a share. The output is 37.8%. Note the difference from the usual repeat rate: a customer who places two orders in the same month counts as a repeat buyer elsewhere but as a first-month-only customer here.
months_active = merged.groupby('CustomerID')['CohortIndex'].apply(lambda s: (s == 0).sum() == len(s))
one_time_pct = months_active.mean()
round(one_time_pct * 100, 1)
Part 22: Real Cohort Size Trend Over Time#
Retention is only half of growth; you also need to know how many new customers arrive each month. The code draws a bar chart of cohort_sizes, with dates formatted as year-month and labels rotated 45 degrees so they do not overlap. The chart is saved as cohort_acquisition_sizes.png. Look for the large December 2010 bar, a dip in the summer months down to 169 in August, a pick-up in autumn and a tiny December 2011 bar caused by the data ending early that month.
plt.figure(figsize=(9, 5))
plt.bar([d.strftime('%Y-%m') for d in cohort_sizes.index], cohort_sizes.values, color='steelblue')
plt.xticks(rotation=45)
plt.ylabel('Real New Customers')
plt.title('Real New Customer Acquisition by Cohort Month')
plt.tight_layout()
plt.savefig('cohort_acquisition_sizes.png', dpi=120)
plt.close()
Part 23: Real Overall Repeat Purchase Rate#
The overall repeat purchase rate is the simplest loyalty metric, useful as a headline next to cohort detail. groupby('CustomerID')['InvoiceNo'].nunique() counts each customer's orders, > 1 flags repeat buyers, and .mean() gives the share. The output is 65.3%, matching the figure from Video 1. Compare this with the 37.8% who only bought in their first month: some customers come back, but not necessarily in a later month, and others return much later.
orders_per_customer = clean.groupby('CustomerID')['InvoiceNo'].nunique()
repeat_rate = (orders_per_customer > 1).mean()
round(repeat_rate * 100, 1)
Part 24: Real Time Between First and Second Purchase#
Knowing how long customers take to come back helps you time follow-up emails and offers. The code keeps repeat customers, sorts by date, and applies a function to each customer that finds their unique purchase times and calculates the gap between the first and second in days, by dividing by pd.Timedelta(days=1). dropna() removes anyone without a second distinct time. The median gap is 51.0 days. The median is used because a few very long gaps would pull the mean upward.
repeat_customers = orders_per_customer[orders_per_customer > 1].index
first_two = clean[clean['CustomerID'].isin(repeat_customers)].sort_values('InvoiceDate')
def gap_to_second_visit(dates):
uniq = sorted(dates.unique())
return (uniq[1] - uniq[0]) / pd.Timedelta(days=1) if len(uniq) > 1 else None
gap_days = first_two.groupby('CustomerID')['InvoiceDate'].apply(gap_to_second_visit).dropna()
gap_days.median().round(1)
Part 25: Do Month-1 Returners Actually Spend More Overall#
This step tests whether early return predicts long-term value. Customers active at cohort index 1 are labelled returners. The code totals revenue per customer, then uses isin and ~ (not) to split them into returners and everyone else, and averages each group. Returners spent 4,717.98 on average against 1,238.93 for the rest. That is a big gap, but be careful: returners have more orders by definition, so part of the gap is built in. It shows a strong link, not proof that a retention campaign would cause the extra spend.
month0_month1_customers = merged[(merged['CohortIndex'] == 1)]['CustomerID'].unique()
total_revenue_per_customer = clean.groupby('CustomerID')['Revenue'].sum()
returners_revenue = total_revenue_per_customer.loc[total_revenue_per_customer.index.isin(month0_month1_customers)].mean()
non_returners_revenue = total_revenue_per_customer.loc[~total_revenue_per_customer.index.isin(month0_month1_customers)].mean()
round(returners_revenue, 2), round(non_returners_revenue, 2)
Part 26: Real Matured Cohorts Only#
Comparing young and old cohorts on the same chart can mislead, because young cohorts have no long-term data yet. retention[6].notna() keeps only cohorts that have reached month 6. The count line is not displayed, but the output lists seven matured cohorts, December 2010 to June 2011, at months 0, 1, 3 and 6. Most hold about 22% to 27% at month 6, while June 2011 falls to 9.5%. December 2010 stays highest at 36.2%.
matured = retention[retention[6].notna()]
matured.shape[0]
matured[[0, 1, 3, 6]].round(3)
Part 27: Real Country Mix Within the Largest Cohort#
The largest cohort deserves a closer look, to understand who these early customers are. cohort_sizes.idxmax() finds the biggest cohort, December 2010, and the code collects its customer IDs from first_purchase. It then counts the country of every transaction those customers made. The United Kingdom accounts for 147,183 rows, followed by EIRE (7,126), Germany (3,475) and France (3,076). Note these are transaction rows, not customers, so a few busy accounts can inflate a country's count.
largest_cohort_month = cohort_sizes.idxmax()
largest_cohort_customers = first_purchase[first_purchase == largest_cohort_month].index
clean[clean['CustomerID'].isin(largest_cohort_customers)]['Country'].value_counts().head(5)
Part 28: Real Retention Improvement Recommendation#
Analysis is only useful if it leads to a recommendation. The code calculates how much more, in percentage terms, month-1 returners spend than non-returners, and prints it as a sentence. The output says retained customers are worth 280.8% more in lifetime revenue on average. This supports investing in early follow-up, such as a welcome series in the first few weeks. When you present it, mention that the comparison is a correlation, and suggest a small test campaign to measure the real effect.
gap = round((returners_revenue - non_returners_revenue) / non_returners_revenue * 100, 1)
print(f'Customers retained through month one are worth {gap}% more in real lifetime revenue on average, making early-lifecycle retention campaigns a genuinely high-value real investment.')
Part 29: Real Verification the Full Table Reconstructs Correctly#
Every customer belongs to exactly one cohort, so the month-0 counts should add up to the total customer base. The code sums the first column of the pivot and compares it with clean['CustomerID'].nunique(). The output is True. If this failed, it would mean some customers were lost or counted twice during the merge or pivot. A reconciliation like this is a quick way to confirm that the whole table, not just one cell, is complete.
total_from_pivot = cohort_pivot.iloc[:, 0].sum()
total_unique_customers = clean['CustomerID'].nunique()
total_from_pivot == total_unique_customers
Part 30: Real Cohort Count Recap#
A one-line recap states the scope of the analysis clearly for anyone reading it later. The code prints the number of cohorts using len(cohort_pivot) and the total customer count from the previous step. The output reads: 13 monthly cohorts covering 4,334 unique customers. Always put this kind of scope statement at the top of a report, so readers know how much data sits behind the percentages.
print(f'Analyzed {len(cohort_pivot)} real monthly cohorts covering {total_unique_customers} real unique customers.')
Wrap-Up: What You Learned#
- A cohort groups real customers by their real first purchase month, tracked together going forward.
- The real cohort index measures months elapsed since that first purchase, letting different real cohorts be compared fairly.
- A retention table divides real active counts by each real cohort's starting size, turning raw headcounts into a real comparable percentage.
- Real revenue-per-cohort tracking shows whether retained customers are genuinely still worth as much, not just whether they came back.
- Comparing real early cohorts to real later ones reveals whether the business is actually improving at keeping customers over time.
- Next video: real sales forecasting, predicting real future retail demand from this same historical data.
Practice on your own
- Recalculate the early versus late month-1 retention comparison after removing the December 2010 cohort, and see whether the decline still holds.
- Build a revenue retention table by dividing
revenue_pivotby its own month-0 column, then compare it with the customer retention table for the first six cohorts. - Repeat the cohort analysis for UK customers only and then for all other countries, and compare their average month-1 retention.
The next video in the series covers sales forecasting, predicting future retail demand from this same historical data.
🧠 Test yourself
Pick an answer to check it. Your best score is saved in this browser.
In cohort analysis, what defines a customer's cohort?
A cohort groups customers by when they first bought (the CohortMonth) so groups can be tracked together over time.
A customer's CohortMonth is January 2011 and an invoice is dated March 2011. What is the CohortIndex?
year_diff = inv.year - cohort.year month_diff = inv.month - cohort.month index = year_diff * 12 + month_diff
year_diff = 0 and month_diff = 3 - 1 = 2, so the index is 2: two months after the first purchase.
Why does the lesson use
.nunique()on CustomerID when buildingcohort_data?Each customer may have many invoice lines in a month. nunique counts distinct active customers per cohort and month.
How is the retention table built from
cohort_pivotandcohort_sizes?Dividing every row by its month-0 size (column 0) turns headcounts into retention percentages that are comparable across cohorts.
Why does the lesson look at "matured cohorts only" when checking month-6 retention?
A cohort that started recently has NaN for later months because those months haven't happened. Comparing only matured cohorts is fair.
💻 Practice exercises
Try each one in the notebook or in your own editor before opening the solution.
Create a DataFrame of 12 transactions (CustomerID, InvoiceDate) for 4 customers over several months. Add InvoiceMonth, find each customer's CohortMonth with groupby/min, merge it back, and print the first rows.
💡 Hint
Use
dt.to_period('M')for the month andgroupby('CustomerID')['InvoiceMonth'].min().✅ Show solution
import pandas as pd tx = pd.DataFrame({ 'CustomerID': [1,1,1,2,2,3,3,3,4,4,4,4], 'InvoiceDate': pd.to_datetime(['2024-01-05','2024-02-10','2024-04-02','2024-01-20','2024-03-15', '2024-02-03','2024-02-25','2024-03-30','2024-03-05','2024-04-10', '2024-05-02','2024-06-01']), }) tx['InvoiceMonth'] = tx['InvoiceDate'].dt.to_period('M') first_purchase = tx.groupby('CustomerID')['InvoiceMonth'].min().rename('CohortMonth') merged = tx.merge(first_purchase, on='CustomerID') print(merged.head())
Using the same kind of 14-row transaction data, compute the CohortIndex (months since first purchase) and build a cohort pivot of distinct active customers (rows = CohortMonth, columns = CohortIndex). Print the pivot.
💡 Hint
Index = year difference * 12 + month difference; then
groupby([...])['CustomerID'].nunique().unstack().✅ Show solution
import pandas as pd tx = pd.DataFrame({ 'CustomerID': [1,1,1,2,2,3,3,3,4,4,4,4,5,5], 'InvoiceDate': pd.to_datetime(['2024-01-05','2024-02-10','2024-04-02','2024-01-20','2024-03-15', '2024-02-03','2024-02-25','2024-03-30','2024-02-05','2024-03-10', '2024-04-02','2024-05-01','2024-03-12','2024-04-20']), }) tx['InvoiceMonth'] = tx['InvoiceDate'].dt.to_period('M') cohort = tx.groupby('CustomerID')['InvoiceMonth'].min().rename('CohortMonth') merged = tx.merge(cohort, on='CustomerID') merged['CohortIndex'] = (merged['InvoiceMonth'].dt.year - merged['CohortMonth'].dt.year) * 12 \ + (merged['InvoiceMonth'].dt.month - merged['CohortMonth'].dt.month) cohort_pivot = merged.groupby(['CohortMonth', 'CohortIndex'])['CustomerID'].nunique().unstack() print(cohort_pivot)
From a hand-made cohort pivot (4 monthly cohorts, months 0 to 3, with NaN for months not yet reached), compute the retention table, print the average month-1 retention, and report the best and worst cohort by month-1 retention.
💡 Hint
Divide by column 0 with
axis=0, then useidxmax/idxminon column 1.✅ Show solution
import pandas as pd import numpy as np cohort_pivot = pd.DataFrame( {0: [100, 120, 80, 150], 1: [40, 36, 20, 45], 2: [30, 24, 12, np.nan], 3: [25, 18, np.nan, np.nan]}, index=pd.period_range('2024-01', periods=4, freq='M'), ) cohort_pivot.columns.name = 'CohortIndex' cohort_sizes = cohort_pivot[0] retention = cohort_pivot.divide(cohort_sizes, axis=0) print(retention.round(3)) month1 = retention[1] print('Average month-1 retention:', round(month1.mean() * 100, 1), '%') print('Best cohort:', month1.idxmax(), round(month1.max() * 100, 1), '%') print('Worst cohort:', month1.idxmin(), round(month1.min() * 100, 1), '%')
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



