Cohort Retention Table
Build a cohort retention matrix showing the percentage of week-0 users still active in subsequent weeks using pivot_table.
Cohort Retention Table is a free Pandas & NumPy Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Pandas & NumPy Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Is a Cohort Retention Table?
A cohort retention table groups users by the week (or month) they first used the product and then tracks what percentage of each cohort is still active in subsequent weeks. Row 0 always shows 100 % because the definition of week-0 is the signup week. Values in later columns reveal how quickly the product loses users — the slower the decay, the stronger the retention.
import pandas as pd
df = pd.read_csv('user_events.csv', parse_dates=['event_date'])
print(df.dtypes)
print(df.head())Assigning Each User a Cohort Week
The cohort week is the week of each user's first event. Use groupby('user_id')['event_date'].min() to find the first event date per user, then convert to an ISO week with .dt.to_period('W'). Merge this cohort label back to the main events DataFrame so every row knows which cohort week its user belongs to.
cohort_week = df.groupby('user_id')['event_date'].min().dt.to_period('W').rename('cohort_week')
df = df.join(cohort_week, on='user_id')
df['event_week'] = df['event_date'].dt.to_period('W')
print(df[['user_id', 'event_date', 'cohort_week', 'event_week']].head())Computing the Week Number Since Cohort
The week number (also called period number or age) is the difference between the event week and the cohort week. Pandas Period subtraction returns an integer offset. Week 0 means the user was active during their sign-up week; week 4 means they returned four weeks later. This offset column becomes the column axis of the retention matrix.
df['week_number'] = (df['event_week'] - df['cohort_week']).apply(lambda x: x.n)
print(df[['user_id', 'cohort_week', 'event_week', 'week_number']].head(10))Counting Active Users per Cohort-Week Pair
Group by both cohort_week and week_number and count unique users to get the raw retention counts matrix. Each cell tells you how many users from cohort C were active in week N. The diagonal from top-left to bottom-right (week 0 for each cohort) gives the cohort sizes, which are the denominators for computing percentages.
retention_counts = (
df.groupby(['cohort_week', 'week_number'])['user_id']
.nunique()
.reset_index(name='active_users')
)
print(retention_counts.head(10))Pivoting to a Retention Matrix
Use pivot_table() to turn the long-format retention counts into a wide matrix: rows are cohort weeks and columns are week numbers. The aggfunc='sum' handles any duplicate groupby keys. Fill cells with 0 for weeks where no users from that cohort were active. This matrix is the core of the retention analysis.
retention_matrix = retention_counts.pivot_table(
index='cohort_week',
columns='week_number',
values='active_users',
aggfunc='sum'
).fillna(0)
print(retention_matrix.iloc[:5, :8])Computing Retention Percentages
Divide each row by its week-0 value to convert counts to percentages. The week-0 column is the cohort size, stored in retention_matrix[0]. Use divide(cohort_sizes, axis=0) and multiply by 100. Round to one decimal place for readability. The result is the standard retention heatmap expected in product analytics reports.
cohort_sizes = retention_matrix[0]
retention_pct = retention_matrix.divide(cohort_sizes, axis=0) * 100
retention_pct = retention_pct.round(1)
print(retention_pct.iloc[:5, :8])Interpreting the Retention Curve
Read the retention table diagonally: each row decays over time. A product with strong retention will show values like 100, 60, 50, 45, 42 — a rapid initial drop followed by a stable plateau. A product with poor retention shows 100, 30, 15, 8, 4 — continual decay with no plateau. The plateau level (if any) is called the long-term retention rate and is the most important retention KPI.
# Average retention rate per week number across all cohorts
avg_retention = retention_pct.mean(axis=0)
print('Average retention by week:')
print(avg_retention.round(1))Identifying the Best and Worst Cohorts
Compare cohort performance by looking at week-4 or week-8 retention across cohorts. The cohort with the highest week-4 retention benefited from a product improvement or an effective acquisition channel; the worst cohort may have been acquired via a low-quality channel. Use .loc[:, 4].sort_values(ascending=False) to rank cohorts by their week-4 retention.
if 4 in retention_pct.columns:
week4 = retention_pct[4].sort_values(ascending=False)
print('Cohorts ranked by week-4 retention:')
print(week4)Visualising Retention as a Heatmap
A colour-coded heatmap is the canonical visualisation for retention tables. Use sns.heatmap() with annot=True to show the percentage value inside each cell and a diverging colour map (low retention = red, high = green). Set vmin=0 and vmax=100 so colours are comparable across different retention scales.
import seaborn as sns
import matplotlib.pyplot as plt
fig, ax = plt.subplots(figsize=(14, 6))
sns.heatmap(retention_pct.iloc[:, :13],
annot=True, fmt='.0f',
cmap='RdYlGn', vmin=0, vmax=100,
ax=ax, linewidths=0.5)
ax.set_title('Weekly Cohort Retention (%)')
ax.set_xlabel('Week Number')
ax.set_ylabel('Cohort Week')
plt.tight_layout()
plt.show()Plotting the Retention Curve
A line chart of average retention by week communicates the overall decay curve more clearly than a heatmap when presenting to non-technical stakeholders. Plot the average retention curve with a shaded band showing plus-or-minus one standard deviation to convey how much variation exists across cohorts. The plateau — where the line flattens — is the product's natural retention floor.
avg_ret = retention_pct.mean(axis=0)
std_ret = retention_pct.std(axis=0)
fig, ax = plt.subplots(figsize=(10, 5))
ax.plot(avg_ret.index, avg_ret.values, color='steelblue', linewidth=2, marker='o')
ax.fill_between(avg_ret.index, avg_ret - std_ret, avg_ret + std_ret, alpha=0.2)
ax.set_xlabel('Week Number')
ax.set_ylabel('Retention %')
ax.set_title('Average Cohort Retention Curve')
plt.tight_layout()
plt.show()Exporting the Retention Table
Export the retention table to Excel so product managers can share it in weekly reviews. Use to_excel() and apply conditional formatting manually in Excel, or use Styler.background_gradient() in Pandas to add colour directly to the exported file. The retention table is one of the most requested outputs in product analytics.
styled = retention_pct.style.background_gradient(cmap='RdYlGn', vmin=0, vmax=100)
styled.to_excel('retention_table.xlsx', engine='openpyxl')
print('Retention table saved to retention_table.xlsx')Quick Check
Test your understanding of Data Analysis concepts from this lesson.
Lesson Recap
In this lesson you learned: assigning users to cohort weeks and computing week offsets, pivoting to a retention matrix and converting counts to percentages, and visualising cohort retention as a heatmap and average retention curve. Next up we explore visualising user journeys through funnel bar charts and heatmaps.
Frequently asked questions
Is the “Cohort Retention Table” lesson free?
Yes — the full text of “Cohort Retention Table” is free to read here on the web, and the Pandas & NumPy Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Pandas & NumPy Academy course, upgrade to CoddyKit PRO.
What will I learn in “Cohort Retention Table”?
Build a cohort retention matrix showing the percentage of week-0 users still active in subsequent weeks using pivot_table. You practise Pandas & NumPy Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start Pandas & NumPy Academy?
No prior experience is required. Pandas & NumPy Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Cohort Retention Table” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this Pandas & NumPy Academy lesson?
Yes. Every Pandas & NumPy Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Sessionisation and Event Sequencing
- Funnel Analysis
- Cohort Retention Table
- Visualising User Journeys