SQL KPI分析 — ファネル・コホート・パレートの応用

WITH step_counts AS (
  SELECT
    step,
    CASE step                       -- ファネル順を数値で明示
      WHEN 'visit'    THEN 1
      WHEN 'signup'   THEN 2
      WHEN 'activate' THEN 3
      WHEN 'purchase' THEN 4
    END AS step_order,
    COUNT(DISTINCT user_id) AS users
  FROM  funnel_events
  GROUP BY step
)
SELECT
  step, users,
  LAG(users) OVER (ORDER BY step_order) AS prev_users,
  ROUND(
    users * 100.0
    / NULLIF(LAG(users) OVER (ORDER BY step_order), 0), 2
  ) AS step_cvr
FROM  step_counts
ORDER BY step_order;

/*
  実行順序:
  1. FROM funnel_events                       → 行を読込
  2. GROUP BY step + COUNT(DISTINCT user_id)  → ステップ別ユニーク数を集計
  3. WITH step_counts                         → CTE 完成
  4. LAG(users) OVER (ORDER BY step_order)    → 前段の通過者数を取得
  5. ステップ間通過率を計算                              → 前段比で算出
  */

WITH RECURSIVE date_spine AS (
  SELECT DATE '2024-02-01' AS d        -- ① アンカー（1行）
  UNION ALL
  SELECT d + 1                          -- ② 再帰項: 直前の d に +1
  FROM  date_spine
  WHERE d < DATE '2024-02-05'          -- ③ 停止条件
)
SELECT
  s.d AS activity_date,
  COALESCE(a.active_users, 0) AS active_users  -- 未マッチ日は 0 に
FROM      date_spine s
LEFT JOIN active_days a ON a.activity_date = s.d
ORDER BY s.d;

/*
  実行順序（再帰CTEの展開）:
  1. アンカー                              → 起点日を生成
  2. 再帰                                → 翌日を1日ずつ追加
  3. 再帰終了                              → 上限日で停止
  4. date_spine LEFT JOIN active_days  → 各日に実績を結合（無い日はNULL）
  5. COALESCE(NULL,0) / ORDER BY s.d   → 0補完して日付順出力
  */

WITH first_login AS (              -- ① コホート日 = 初回ログイン日
  SELECT user_id, MIN(login_date) AS cohort_date
  FROM  logins
  GROUP BY user_id
),
activity AS (                          -- ② 経過週を算出
  SELECT
    f.cohort_date, f.user_id,
    (l.login_date - f.cohort_date) / 7 AS week_no
  FROM      first_login f
  JOIN      logins l USING (user_id)
)
SELECT
  cohort_date,
  COUNT(DISTINCT user_id) AS cohort_size,
  ROUND(COUNT(DISTINCT user_id) FILTER (WHERE week_no = 0)
      * 100.0 / COUNT(DISTINCT user_id), 2) AS w0_pct,
  ROUND(COUNT(DISTINCT user_id) FILTER (WHERE week_no = 1)
      * 100.0 / COUNT(DISTINCT user_id), 2) AS w1_pct,
  ROUND(COUNT(DISTINCT user_id) FILTER (WHERE week_no = 2)
      * 100.0 / COUNT(DISTINCT user_id), 2) AS w2_pct
FROM  activity
GROUP BY cohort_date
ORDER BY cohort_date;

/*
  実行順序:
  1. first_login                     → 各ユーザーのコホート日を集計
  2. activity                        → 週番号を算出
  3. GROUP BY cohort_date            → コホート別に集約
  4. COUNT(DISTINCT user_id) FILTER  → 週ごとの再訪率を算出
  */

WITH ranked AS (
  SELECT
    customer_id, revenue,
    RANK() OVER (ORDER BY revenue DESC) AS rev_rank,
    ROUND(revenue * 100.0 / SUM(revenue) OVER (), 2) AS rev_share,  -- 構成比
    ROUND(
      SUM(revenue) OVER (ORDER BY revenue DESC
                         ROWS UNBOUNDED PRECEDING)            -- 上位からの累積
      * 100.0 / SUM(revenue) OVER (), 2
    ) AS cum_share
  FROM  customer_revenue
)
SELECT
  customer_id, revenue, rev_rank, rev_share, cum_share,
  CASE                                  -- 累積構成比でABC分類
    WHEN cum_share <= 80  THEN 'A'
    WHEN cum_share <= 95  THEN 'B'
    ELSE 'C'
  END AS abc_class
FROM  ranked
ORDER BY rev_rank;

/*
  実行順序:
  1. FROM customer_revenue                          → 行を読込
  2. SUM(revenue) OVER ()                           → 総合計を全行に付与
  3. SUM(revenue) OVER (ORDER BY revenue DESC ...)  → 上位からの累積を計算
  4. rev_share / cum_share                          → 構成比と累積構成比を算出
  5. 外側 CASE                                        → A/B/C ランクに分類
  */