SQL 行動分析 — コホート・ファネル・チャーンの基礎

WITH cohorts AS (
  -- 各ユーザーの登録月を月初日に丸めてコホートを定義
  SELECT
    user_id,
    DATE_TRUNC('month', registered_at)::date  AS cohort_month
  FROM  users
),
activity AS (
  -- ユーザーごとにアクティブだった月の一覧を重複排除して取得
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_date)::date    AS active_month
  FROM  login_events
)
SELECT
  c.cohort_month,
  COUNT(DISTINCT c.user_id)  AS cohort_size,
  COUNT(DISTINCT a.user_id)  AS retained_month1,
  ROUND(
    COUNT(DISTINCT a.user_id) * 100.0 /
    COUNT(DISTINCT c.user_id), 1
  ) AS retention_rate_pct
FROM   cohorts c
LEFT JOIN activity a
  ON  a.user_id     = c.user_id
  AND a.active_month = c.cohort_month + INTERVAL '1 month'  -- 翌月のみ結合
GROUP BY c.cohort_month
ORDER BY c.cohort_month;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE cohorts            → CTE を定義
  2. CTE activity           → CTE を定義
  3. FROM cohorts c
     LEFT JOIN activity a   → 結合（左表を全行保持）
  4. GROUP BY cohort_month  → グループ化
  5. 集計関数を評価         → COUNT(DISTINCT ...)
  6. SELECT                 → 値を整形
*/

WITH counts AS (
  -- 各ステップの到達ユーザーを1行に集約
  SELECT
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'page_view') AS s1,
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'signup')    AS s2,
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'add_cart')  AS s3,
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'purchase')  AS s4
  FROM   funnel_events
)
SELECT
  s1 AS page_view,
  s2 AS signup,
  s3 AS add_cart,
  s4 AS purchase,
  ROUND(s2 * 100.0 / s1, 1) AS view_to_signup_pct,    -- step1→2 の転換率
  ROUND(s3 * 100.0 / s2, 1) AS signup_to_cart_pct,    -- step2→3 の転換率
  ROUND(s4 * 100.0 / s3, 1) AS cart_to_purchase_pct   -- step3→4 の転換率
FROM   counts;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE counts        → CTE を定義
  2. 集計関数を評価    → COUNT(DISTINCT ...) FILTER で集約
  3. 外側クエリを評価  → 値を整形して列を選択
*/

WITH daily AS (
  -- 日ごとの DAU（ユニークユーザー数）を集計
  SELECT
    DATE_TRUNC('month', session_date)::date  AS month,
    session_date,
    COUNT(DISTINCT user_id)               AS dau
  FROM   user_sessions
  GROUP BY month, session_date
),
monthly AS (
  -- 月ごとの MAU（月内でユニークなユーザー数）を集計
  SELECT
    DATE_TRUNC('month', session_date)::date  AS month,
    COUNT(DISTINCT user_id)               AS mau
  FROM   user_sessions
  GROUP BY month
)
SELECT
  m.month,
  ROUND(AVG(d.dau), 1)                        AS avg_dau,
  m.mau,
  ROUND(AVG(d.dau) * 100.0 / m.mau, 1)       AS stickiness_pct  -- DAU平均/MAU
FROM   monthly m
JOIN   daily   d USING (month)
GROUP BY m.month, m.mau
ORDER BY m.month;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE daily            → CTE を定義
  2. CTE monthly          → CTE を定義
  3. FROM monthly m
     JOIN daily d         → 結合（一致行のみ）
  4. GROUP BY m.month     → グループ化
  5. 集計関数を評価       → AVG(d.dau)
  6. SELECT               → 値を整形
  7. ORDER BY m.month     → 並び替えて出力
*/

WITH jan_active AS (
  -- 2024年1月にアクティブだったユーザーを抽出（重複排除）
  SELECT DISTINCT user_id
  FROM   login_events
  WHERE  DATE_TRUNC('month', event_date) = '2024-01-01'
),
feb_active AS (
  -- 2024年2月にアクティブだったユーザーを抽出（重複排除）
  SELECT DISTINCT user_id
  FROM   login_events
  WHERE  DATE_TRUNC('month', event_date) = '2024-02-01'
)
SELECT
  j.user_id,
  '2024-01'                                             AS active_month,
  CASE WHEN f.user_id IS NULL THEN true ELSE false END  AS is_churned  -- NULL=翌月未ログイン=チャーン
FROM   jan_active  j
LEFT JOIN feb_active f USING (user_id)   -- 2月にいないユーザーは f.user_id = NULL
ORDER BY j.user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE jan_active        → CTE を定義
  2. CTE feb_active        → CTE を定義
  3. FROM jan_active j
     LEFT JOIN feb_active  → 結合（左表を全行保持）
  4. SELECT                → 列を評価（CASE で判定）
  5. ORDER BY j.user_id    → 並び替えて出力
*/