コホート・リテンションテーブル
pivot_tableを使って、0週目のユーザーのうちその後の週にもアクティブである割合を示すコホート・リテンション行列を作成します。
「コホート・リテンションテーブル」はCoddyKit上の無料Pandas & NumPy Academyレッスンです。 これはレッスン3/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応の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.
よくある質問
「コホート・リテンションテーブル」レッスンは無料ですか？
はい。「コホート・リテンションテーブル」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、Pandas & NumPy Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 Pandas & NumPy Academyコースには全4レッスンが含まれています。
「コホート・リテンションテーブル」で何を学びますか？
pivot_tableを使って、0週目のユーザーのうちその後の週にもアクティブである割合を示すコホート・リテンション行列を作成します。 ブラウザで直接実行するハンズオンコードでPandas & NumPy Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
Pandas & NumPy Academyを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのPandas & NumPy Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン3/4です。
「コホート・リテンションテーブル」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このPandas & NumPy Academyレッスンでコードを書いて実行できますか？
はい。すべてのPandas & NumPy Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。
このコースのすべてのレッスン
- セッション化とイベントの時系列化
- ファネル分析
- コホート・リテンションテーブル
- ユーザージャーニーの可視化