継続率と cohort 分析：ユーザーは何日後にいなくなるか

WITH first_active AS (  -- 各ユーザーの初回 active 日（cohort 定義）  SELECT    user_id,    DATE_TRUNC(MIN(active_date), WEEK(MONDAY)) AS cohort_week,    MIN(active_date) AS first_date  FROM `project.dataset.active_event`  GROUP BY user_id),activity AS (  SELECT user_id, active_date FROM `project.dataset.active_event`)SELECT  fa.cohort_week,  DATE_DIFF(a.active_date, fa.first_date, DAY) AS days_since_join,  COUNT(DISTINCT a.user_id) AS active_users,  COUNT(DISTINCT a.user_id) * 1.0 /    (SELECT COUNT(DISTINCT user_id) FROM first_active WHERE cohort_week = fa.cohort_week)    AS retention_rateFROM first_active faJOIN activity a USING(user_id)WHERE DATE_DIFF(a.active_date, fa.first_date, DAY) BETWEEN 0 AND 30GROUP BY 1, 2ORDER BY 1, 2;

import pandas as pdimport numpy as npimport matplotlib.pyplot as pltimport seaborn as snsimport japanize_matplotlib  # noqa: F401
# 1. データ読み込み（user_id, active_date の 2 列）df = pd.read_csv("active_events.csv", parse_dates=["active_date"])
# 2. 各ユーザーの初回 active 日を計算 → cohort 週df["first_date"] = df.groupby("user_id")["active_date"].transform("min")df["cohort_week"] = df["first_date"].dt.to_period("W").dt.start_timedf["days_since_join"] = (df["active_date"] - df["first_date"]).dt.days
# 3. cohort × 経過日数の継続率テーブルcohort_size = df.groupby("cohort_week")["user_id"].nunique()retention = (    df.groupby(["cohort_week", "days_since_join"])["user_id"]    .nunique()    .reset_index()    .pivot(index="cohort_week", columns="days_since_join", values="user_id"))retention_pct = retention.div(cohort_size, axis=0)
print(retention_pct.iloc[:5, :8])  # 上位 5 cohort × 0〜7 日

# 4. ヒートマップfig, ax = plt.subplots(figsize=(14, 6))sns.heatmap(    retention_pct.iloc[:, :30],  # 30 日分    annot=True,    fmt=".0%",    cmap="YlGnBu",    vmin=0, vmax=0.5,    cbar_kws={"label": "継続率"},    ax=ax,)ax.set_title("週次 cohort × 経過日数の継続率")ax.set_xlabel("経過日数")ax.set_ylabel("入会週")plt.tight_layout()plt.savefig("cohort_heatmap.png", dpi=120)

# 5. 残存曲線：改善前後 cohort を重ねて表示before_cohorts = retention_pct.loc[    (retention_pct.index >= "2026-01-01") & (retention_pct.index < "2026-04-01")].mean()after_cohorts = retention_pct.loc[retention_pct.index >= "2026-04-01"].mean()
fig, ax = plt.subplots(figsize=(11, 5))ax.plot(before_cohorts.index, before_cohorts.values, label="改善前 (Q1)", linewidth=2)ax.plot(after_cohorts.index, after_cohorts.values, label="改善後 (Q2)", linewidth=2, color="#eb5d32")ax.set_xlabel("経過日数"); ax.set_ylabel("継続率")ax.set_title("プロダクト改善打ち手の効果（残存曲線）")ax.legend(); ax.grid(alpha=0.3)ax.set_ylim(0, 0.5)plt.tight_layout(); plt.show()