Kohorten-Retention-Tabelle
Erstellen Sie mit pivot_table eine Kohorten-Retention-Matrix, die den Anteil der Nutzer aus Woche 0 zeigt, die in den folgenden Wochen noch aktiv sind.
Kohorten-Retention-Tabelle ist eine kostenlose Pandas & NumPy Academy-Lektion auf CoddyKit. Dies ist Lektion 3 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des Pandas & NumPy Academy-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der Pandas & NumPy Academy-Kurs umfasst insgesamt 4 Lektionen.
Teile dieser Lektion wurden noch nicht übersetzt und werden auf Englisch angezeigt.
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.
Häufig gestellte Fragen
Ist die Lektion „Kohorten-Retention-Tabelle“ kostenlos?
Ja — der vollständige Text von „Kohorten-Retention-Tabelle“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des Pandas & NumPy Academy-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der Pandas & NumPy Academy-Kurs umfasst insgesamt 4 Lektionen.
Was lerne ich in „Kohorten-Retention-Tabelle“?
Erstellen Sie mit pivot_table eine Kohorten-Retention-Matrix, die den Anteil der Nutzer aus Woche 0 zeigt, die in den folgenden Wochen noch aktiv sind. Du übst Pandas & NumPy Academy mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.
Brauche ich Erfahrung, um Pandas & NumPy Academy zu starten?
Keine Vorkenntnisse erforderlich. Pandas & NumPy Academy auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 3 von 4.
Wie lange dauert die Lektion „Kohorten-Retention-Tabelle“?
Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.
Kann ich in dieser Pandas & NumPy Academy-Lektion Code schreiben und ausführen?
Ja. Jede Pandas & NumPy Academy-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.
Alle Lektionen in diesem Kurs
- Sessionisierung und Ereignisabfolge
- Trichteranalyse
- Kohorten-Retention-Tabelle
- Nutzerreisen visualisieren