同期群留存表
使用 pivot_table 构建同期群留存矩阵，展示第 0 周用户在后续各周仍保持活跃的百分比。
同期群留存表 是 CoddyKit 上的免费 Pandas & NumPy Academy 课时。 这是第 3 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 Pandas & NumPy Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Pandas & NumPy Academy 课程共包含 4 节课。
本课时的部分内容尚未翻译，以英文显示。
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.
常见问题解答
「同期群留存表」课时是免费的吗？
是的 — 「同期群留存表」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Pandas & NumPy Academy 课程的其余内容，请升级到 CoddyKit PRO。 Pandas & NumPy Academy 课程共包含 4 节课。
「同期群留存表」这节课中我会学到什么？
使用 pivot_table 构建同期群留存矩阵，展示第 0 周用户在后续各周仍保持活跃的百分比。 你通过在浏览器中直接运行的动手代码来练习 Pandas & NumPy Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 Pandas & NumPy Academy 需要有经验吗？
无需任何先前经验。CoddyKit 上的 Pandas & NumPy Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 3 节课，共 4 节。
「同期群留存表」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 Pandas & NumPy Academy 课中编写并运行代码吗？
能。每节 Pandas & NumPy Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。