RFM・ar・ARPPU・課金者比率：購買行動の分解

WITH rfm AS (  SELECT    user_id,    DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) AS recency,    COUNT(*) AS frequency,    SUM(amount) AS monetary  FROM `project.dataset.orders`  WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)  GROUP BY user_id)SELECT  user_id,  6 - NTILE(5) OVER (ORDER BY recency) AS r,  -- 直近ほど高  NTILE(5) OVER (ORDER BY frequency) AS f,  NTILE(5) OVER (ORDER BY monetary) AS m,  CONCAT(    6 - NTILE(5) OVER (ORDER BY recency),    NTILE(5) OVER (ORDER BY frequency),    NTILE(5) OVER (ORDER BY monetary)  ) AS rfm_segmentFROM rfm;

import pandas as pdimport numpy as npimport matplotlib.pyplot as pltimport seaborn as snsimport japanize_matplotlib  # noqa: F401
# 1. データ読み込みorders = pd.read_csv("orders.csv", parse_dates=["order_date"])
# 2. 直近 1 年のデータに絞るcutoff = orders["order_date"].max()last_year = orders[orders["order_date"] >= cutoff - pd.Timedelta(days=365)]
# 3. ユーザーごとに R, F, M を計算rfm = last_year.groupby("user_id").agg(    recency=("order_date", lambda x: (cutoff - x.max()).days),    frequency=("order_date", "count"),    monetary=("amount", "sum"),).reset_index()
# 4. 5 分位スコア化（R は逆順：直近ほど高い）rfm["r_score"] = pd.qcut(rfm["recency"], 5, labels=[5, 4, 3, 2, 1]).astype(int)rfm["f_score"] = pd.qcut(rfm["frequency"].rank(method="first"), 5, labels=[1, 2, 3, 4, 5]).astype(int)rfm["m_score"] = pd.qcut(rfm["monetary"], 5, labels=[1, 2, 3, 4, 5]).astype(int)rfm["rfm_segment"] = (    rfm["r_score"].astype(str) + rfm["f_score"].astype(str) + rfm["m_score"].astype(str))print(rfm.head())

# 5. VIP / 通常 / 離脱予備の分類def classify(row):    if row["r_score"] >= 4 and row["f_score"] >= 4 and row["m_score"] >= 4:        return "VIP"    elif row["r_score"] <= 2:        return "離脱予備"    elif row["r_score"] >= 4 and row["m_score"] <= 2:        return "新規少額"    else:        return "通常"
rfm["segment"] = rfm.apply(classify, axis=1)print(rfm["segment"].value_counts(normalize=True))
# 6. ヒートマップ（R × F、平均 monetary）heatmap = rfm.pivot_table(values="monetary", index="r_score", columns="f_score", aggfunc="mean")fig, ax = plt.subplots(figsize=(8, 6))sns.heatmap(heatmap, annot=True, fmt=",.0f", cmap="YlOrRd", ax=ax)ax.set_title("R × F セグメント別の平均購入額")ax.set_xlabel("Frequency Score"); ax.set_ylabel("Recency Score")plt.tight_layout(); plt.show()

# 全 MAUtotal_mau = events["user_id"].nunique()# 課金 MAUpaying_mau = orders["user_id"].nunique()# 売上total_revenue = orders["amount"].sum()
arpu = total_revenue / total_mau            # 全ユーザー 1 人あたりarppu = total_revenue / paying_mau           # 課金者 1 人あたりpaying_ratio = paying_mau / total_mau
print(f"ARPU: {arpu:,.0f} 円 / 課金者比率: {paying_ratio:.2%}")print(f"ARPPU: {arppu:,.0f} 円")print(f"検算: ARPU = ARPPU × 課金者比率 = {arppu * paying_ratio:,.0f}")