コホート mROIC ダッシュボードを組む — 広告費×CRM から「踏む/止める」判定まで

create table ads_spend (date date, channel text, cost bigint);
create table leads     (lead_id text, acquired_at date, channel text);
create table deals     (lead_id text, closed_at date, gross_profit bigint);
 
-- 広告費: 1〜2月 100万 / 3〜4月 300万（増額） / 5月 60万（削減） / 6月 100万
insert into ads_spend
select month_start, 'search', cost from (values
  (date '2025-01-01', 1000000),
  (date '2025-02-01', 1000000),
  (date '2025-03-01', 3000000),
  (date '2025-04-01', 3000000),
  (date '2025-05-01',  600000),
  (date '2025-06-01', 1000000)
) t(month_start, cost);
 
-- リード: 月ごとの獲得数を展開する（CPL 5万円前後・絞りすぎた5月だけ 6万円）
insert into leads
select printf('L-%s-%02d', strftime(cohort, '%Y%m'), i) as lead_id,
       cohort + (i - 1) % 28 * interval 1 day        as acquired_at,
       'search'                                      as channel
from (values
  (date '2025-01-01', 20), (date '2025-02-01', 20),
  (date '2025-03-01', 60), (date '2025-04-01', 60),
  (date '2025-05-01', 10), (date '2025-06-01', 20)
) spec(cohort, n_leads), lateral (select unnest(generate_series(1, n_leads)) as i);
 
-- 成約: 「どのコホートの何件が、どの月に閉じるか」の計画から展開する
insert into deals
with plan as (
  select cohort, close_month, unnest(generate_series(1, n)) as j from (values
    (date '2025-01-01', date '2025-03-01', 1), (date '2025-01-01', date '2025-04-01', 2),
    (date '2025-02-01', date '2025-04-01', 1), (date '2025-02-01', date '2025-05-01', 2),
    (date '2025-03-01', date '2025-05-01', 3), (date '2025-03-01', date '2025-06-01', 6),
    (date '2025-04-01', date '2025-06-01', 3), (date '2025-04-01', date '2025-07-01', 6),
    (date '2025-05-01', date '2025-08-01', 1),
    (date '2025-06-01', date '2025-08-01', 1), (date '2025-06-01', date '2025-09-01', 2)
  ) t(cohort, close_month, n)
),
expanded as (
  select cohort, close_month,
         row_number() over (partition by cohort order by close_month, j) as k
  from plan
),
ranked_leads as (
  select lead_id, date_trunc('month', acquired_at) as cohort,
         row_number() over (partition by date_trunc('month', acquired_at)
                            order by acquired_at, lead_id) as k
  from leads
)
select l.lead_id, e.close_month + 9 * interval 1 day as closed_at, 1000000 as gross_profit
from expanded e
join ranked_leads l using (cohort, k);

-- 単月 PL ビュー: その月に使った広告費 vs その月に閉じた粗利
with spend as (
  select date_trunc('month', date) as month, sum(cost) as ad_cost
  from ads_spend group by 1
),
closed as (
  select date_trunc('month', closed_at) as month, sum(gross_profit) as gross
  from deals group by 1
)
select
  strftime(s.month, '%Y-%m')                          as month,
  s.ad_cost,
  coalesce(c.gross, 0)                                as gross_closed,
  round(100.0 * coalesce(c.gross, 0) / s.ad_cost, 0)  as monthly_roas_pct  -- 粗利ベース
from spend s
left join closed c using (month)
order by 1;

-- コホート成果テーブル: リード獲得月で束ね、その後の成約を帰属させる
with cohort_spend as (
  select date_trunc('month', date) as cohort, sum(cost) as ad_cost
  from ads_spend group by 1
),
cohort_leads as (
  select date_trunc('month', acquired_at) as cohort, count(*) as n_leads
  from leads group by 1
),
cohort_deals as (
  select date_trunc('month', l.acquired_at) as cohort,
         count(*) as n_deals, sum(d.gross_profit) as gross
  from deals d
  join leads l using (lead_id)          -- 結合キーは lead_id ただ1本
  group by 1
)
select strftime(s.cohort, '%Y-%m') as cohort,
       s.ad_cost, cl.n_leads,
       coalesce(cd.n_deals, 0) as n_deals,
       coalesce(cd.gross, 0)   as gross
from cohort_spend s
left join cohort_leads cl using (cohort)
left join cohort_deals cd using (cohort)
order by 1;

-- コホート mROIC ビュー: 財務ロジックと3値判定を載せる
with params as (
  select
    0.10              as wacc,          -- 年率（架空の例示値）
    120               as payback_days,  -- 回収期間 = 成熟日数（架空・90〜150日級の商材想定）
    1000000           as alloc_monthly, -- 広告費以外の割り当て（営業人件費・ツール等/月・架空）
    date '2025-11-01' as as_of          -- 評価日
),
cohort_leads as (
  select date_trunc('month', acquired_at) as cohort, count(*) as n_leads
  from leads group by 1
),
cohort_deals as (
  select date_trunc('month', l.acquired_at) as cohort,
         count(*)            as n_deals,
         sum(d.gross_profit) as gross
  from deals d
  join leads l using (lead_id)
  join params p on d.closed_at <= p.as_of   -- 評価日までに閉じた成約のみ
  group by 1
),
cohort_spend as (
  select date_trunc('month', date) as cohort, sum(cost) as ad_cost
  from ads_spend group by 1
)
select
  strftime(s.cohort, '%Y-%m')                                  as cohort,
  s.ad_cost + p.alloc_monthly                                  as total_cac,
  cl.n_leads,
  coalesce(cd.n_deals, 0)                                      as n_deals,
  coalesce(cd.gross, 0)                                        as gross,
  round(100.0 * (coalesce(cd.gross, 0) - (s.ad_cost + p.alloc_monthly))
        / (s.ad_cost + p.alloc_monthly), 1)                    as mroic_pct,
  round(100.0 * p.wacc * p.payback_days / 365, 1)              as hurdle_pct,
  case
    when s.cohort + interval 1 month + p.payback_days * interval 1 day > p.as_of
      then '暫定'
    when (coalesce(cd.gross, 0) - (s.ad_cost + p.alloc_monthly))
         / (s.ad_cost + p.alloc_monthly)
         >= p.wacc * p.payback_days / 365                      then '超過'
    else '毀損'
  end                                                          as verdict
from cohort_spend s
join params p on true
left join cohort_leads cl using (cohort)
left join cohort_deals cd using (cohort)
order by 1;