SQL イベントモデリング — ファネル・コホート分析の基礎

WITH user_times AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_type = 'view'     THEN event_time END) AS view_time,  -- 最初に view した時刻
    MIN(CASE WHEN event_type = 'click'    THEN event_time END) AS click_time,
    MIN(CASE WHEN event_type = 'purchase' THEN event_time END) AS purchase_time
  FROM   user_events
  GROUP BY user_id
)
SELECT
  COUNT(*) AS total_users,
  SUM(CASE WHEN view_time IS NOT NULL THEN 1 ELSE 0 END) AS view_users,
  SUM(CASE WHEN view_time <= click_time THEN 1 ELSE 0 END) AS click_users,  -- view→click の順序を満たす
  SUM(CASE WHEN view_time <= click_time
            AND click_time <= purchase_time THEN 1 ELSE 0 END) AS purchase_users  -- view→click→purchase の順序
FROM   user_times;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE user_times
  2. 外側クエリ
  */

WITH cohorts AS (                                              -- コホート月を定義
  SELECT
    user_id,
    signup_date,
    DATE_TRUNC('month', signup_date)::date  AS cohort_month
  FROM   users
),
activity AS (                                                -- 30日以内のアクティビティ
  SELECT
    c.user_id,
    c.cohort_month,
    COUNT(e.event_time)  AS event_count         -- 0 = 非アクティブ
  FROM       cohorts c
  LEFT JOIN  user_events e
    ON  c.user_id      = e.user_id
    AND e.event_time  >= c.signup_date
    AND e.event_time   < c.signup_date + INTERVAL '30 days'  -- ON 句に期間条件
  GROUP BY c.user_id, c.cohort_month
)
SELECT
  cohort_month,
  COUNT(*)                                                    AS cohort_size,
  COUNT(*) FILTER (WHERE event_count > 0)                   AS active_users,
  ROUND(
    COUNT(*) FILTER (WHERE event_count > 0) * 100.0
    / NULLIF(COUNT(*), 0), 1)                              AS retention_pct
FROM   activity
GROUP BY cohort_month
ORDER BY cohort_month;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE cohorts
  2. CTE activity
  3. 外側クエリ
  */

WITH event_counts AS (                   -- ユーザーごとのイベント数
  SELECT
    user_id,
    COUNT(*) AS event_count
  FROM   user_events
  GROUP BY user_id
),
segmented AS (                         -- CASE WHEN でセグメント列を追加
  SELECT
    user_id,
    event_count,
    CASE
      WHEN event_count >= 5 THEN 'power'   -- 最初に評価（5以上）
      WHEN event_count >= 2 THEN 'casual'  -- 次に評価（2以上かつ５未満）
      ELSE                       'light'   -- それ以外（1回）
    END AS segment
  FROM   event_counts
)
SELECT
  segment,
  COUNT(*)                   AS user_count,
  ROUND(AVG(event_count), 1) AS avg_events  -- セグメント内の平均イベント数
FROM   segmented
GROUP BY segment
ORDER BY user_count DESC, segment;   -- user_count 降順・同順は segment 昇順

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE event_counts
  2. CTE segmented
  3. 外側クエリ
  */

SELECT
  u.user_id,
  u.signup_date,
  MAX(e.event_time) AS last_event_time       -- 最終イベント日時（イベントなしの場合 NULL）
FROM       users u
LEFT JOIN  user_events e ON u.user_id = e.user_id  -- イベントなしユーザーも保持
GROUP BY   u.user_id, u.signup_date
HAVING     MAX(e.event_time) < '2024-01-25'::date  -- 最終イベントが21日以上前
        OR MAX(e.event_time) IS NULL               -- 一度もイベントなし
ORDER BY   last_event_time ASC NULLS FIRST,        -- NULL（イベントなし）を先頭に
           u.user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM users LEFT JOIN user_events          → 全ユーザーを保持
  2. GROUP BY u.user_id, u.signup_date         → 集約し MAX(event_time) を取得
  3. HAVING ...                                → 最終イベントが基準日前 or NULL を抽出
  4. ORDER BY last_event_time ASC NULLS FIRST, u.user_id  → 並び替えて出力
  */