SQL KPI分析 — DAU/MAU・CVR・リテンションの基礎

WITH lagged AS (
  SELECT
    month, mau,
    LAG(mau) OVER (ORDER BY month) AS prev_mau  -- 前月の MAU を取得
  FROM  monthly_kpi
)
SELECT
  month, mau, prev_mau,
  ROUND(
    (mau - prev_mau) * 100.0
    / NULLIF(prev_mau, 0), 2  -- 0 除算を回避（0なら NULL）
  ) AS mau_growth_pct         -- 前月比成長率(%)
FROM  lagged
ORDER BY month;

/*
  実行順序:
  1. FROM monthly_kpi                         → 行を読込
  2. LAG(mau) OVER (ORDER BY month)           → 前月の mau を付与
  3. WITH lagged                              → CTE 完成
  4. (mau-prev_mau)*100.0/NULLIF(prev_mau,0)  → 成長率を計算
  5. ROUND(..., 2) / ORDER BY month           → 丸めて月順に出力
  */

SELECT
  COUNT(DISTINCT CASE WHEN step = 'signup'
    THEN user_id END)      AS signup_users,  -- 該当ステップのユニーク数（NULLは除外）
  COUNT(DISTINCT CASE WHEN step = 'trial_start'
    THEN user_id END)      AS trial_users,
  COUNT(DISTINCT CASE WHEN step = 'purchase'
    THEN user_id END)      AS purchase_users,
  ROUND(
    COUNT(DISTINCT CASE WHEN step = 'trial_start' THEN user_id END)
    * 100.0 / NULLIF(COUNT(DISTINCT CASE WHEN step = 'signup'
    THEN user_id END), 0), 2
  ) AS trial_cvr,     -- trial / signup の通過率(%)
  ROUND(
    COUNT(DISTINCT CASE WHEN step = 'purchase' THEN user_id END)
    * 100.0 / NULLIF(COUNT(DISTINCT CASE WHEN step = 'trial_start'
    THEN user_id END), 0), 2
  ) AS purchase_cvr  -- purchase / trial の通過率(%)
FROM  funnel_events;

/*
  実行順序:
  1. FROM funnel_events                   → 行を読込
  2. CASE WHEN step='x' THEN user_id END  → 該当ステップのみ user_id を返す
  3. COUNT(DISTINCT ...)                  → NULLをスキップしユニークカウント
  4. CVR を計算                              → 前段比で通過率を算出
  */

WITH first_login AS (
  SELECT
    user_id,
    MIN(login_date) AS cohort_date  -- 初回ログイン日＝コホート
  FROM  user_logins
  GROUP BY user_id
)
SELECT
  f.cohort_date,
  COUNT(DISTINCT f.user_id)                     AS cohort_size,
  COUNT(DISTINCT CASE WHEN l.login_date = f.cohort_date + 1
    THEN l.user_id END)                          AS d1_users,  -- 翌日(+1)に再訪
  ROUND(
    COUNT(DISTINCT CASE WHEN l.login_date = f.cohort_date + 1
      THEN l.user_id END) * 100.0
    / NULLIF(COUNT(DISTINCT f.user_id), 0), 2
  )                                                 AS d1_retention,  -- D1 再訪率(%)
  COUNT(DISTINCT CASE WHEN l.login_date = f.cohort_date + 7
    THEN l.user_id END)                          AS d7_users,
  ROUND(
    COUNT(DISTINCT CASE WHEN l.login_date = f.cohort_date + 7
      THEN l.user_id END) * 100.0
    / NULLIF(COUNT(DISTINCT f.user_id), 0), 2
  )                                                 AS d7_retention
FROM      first_login f
LEFT JOIN user_logins l USING (user_id)  -- コホートに全ログインを結合
GROUP BY  f.cohort_date
ORDER BY  f.cohort_date;

/*
  実行順序:
  1. CTE first_login                         → 各ユーザーの初回ログイン日を集計
  2. LEFT JOIN user_logins                   → コホートに全ログインを結合
  3. CASE WHEN login_date = cohort_date + N  → D1/D7 の再訪を判定
  4. リテンション率を計算                              → 再訪数 / コホートサイズ
  */

SELECT
  u.plan,
  COUNT(DISTINCT u.user_id)           AS user_count,
  COALESCE(SUM(p.amount), 0)         AS total_revenue,
  ROUND(
    COALESCE(SUM(p.amount), 0) * 1.0
    / COUNT(DISTINCT u.user_id),  -- DISTINCT 必須
    2
  )                                   AS arpu
FROM       users     u
LEFT JOIN  purchases p  USING (user_id)
GROUP BY   u.plan
ORDER BY   arpu DESC;

/*
  実行順序:
  1. FROM users u               → 行を読込
  2. LEFT JOIN purchases p      → 購入を結合（未購入はNULL）
  3. GROUP BY u.plan            → プランでグループ化
  4. SUM(p.amount)              → プラン別売上を集計
  5. COALESCE(SUM(...), 0)      → NULL を 0 に変換
  6. COUNT(DISTINCT u.user_id)  → プラン別ユーザー数
  7. ARPU = revenue / count     → 客単価を算出
  8. ORDER BY arpu DESC         → 降順で出力
  */