コホート分析と真のCAC/LTV ― 記述で事業を読む（連載第2回）

モデルを作る前に、まず何を見ればいいのか。

答えは「お金の真実が入っているテーブル」です。広告媒体の管理画面が報告するCVではなく、自社の購入記録から、チャネル別の本当のCAC、コホート別のLTV、そして「成果のうちどこまで行動を遡れるか（接合率）」を読みます。

この回では、過去の受託案件をもとに設計した架空のD2C企業SOLNAのデータを使い、その三つを実際のSQLとPythonで出すところまでを通します。

この回の到達目標

• 媒体が報告するCVと自社の実購入を切り分け、「真の成果」から逆算できる
• コホートで時間軸を揃え、未成熟データを「成績が悪い」と誤読しない
• 接合率（spine coverage）の概念を持ち、後続の手法が「どこまで効くか」の上限を見積もれる
• チャネル別の真のCACと回収期間を、自分の手で算出できる

前提

第1回で、平均・ばらつき・区間の読み方と、「相関 ≠ 因果」の入口を確認しました。本稿はその上に立ちます。因果の手法（DiD/傾向スコア等）はまだ出てきません。ここはあくまで成熟度のステップの最初、「記述 ― 何が起きたかを正しく数える」段階です。数える対象を間違えると、上の段（予測・因果）がすべて狂う。本稿はその土台の話です。

「お金の真実」はどのテーブルにあるか（媒体CV vs 実購入）

最初に決めるのは、評価の分子をどこから取るか、です。

広告媒体が報告するCVは、媒体に都合のいい定義でカウントされます。クリック後の計測窓、view-through（見ただけ）の扱い、媒体ごとのアトリビューション差。さらに複数媒体が同じ一件のCVを二重計上することもある。結果として、各媒体の管理画面のCVを足し合わせた数は、実際に増えた顧客数より多くなりがちです。

「お金の真実」は自社の購入テーブルにしかありません。SOLNAなら orders と customers、オフィス仲介のようなB2Bなら CRM（HubSpot）の成約です。測定は必ずそこまで届かせる。媒体のCVは「最適化の道具」であって「成果の真実」ではない、と割り切ります。

SOLNAのある月の例（架空データ）。

報告CVを評価指標に据えると、フォームやLPの「CV最適化」が、買わない人の登録を増やす方向に走りやすい。取引件数の少ないB2Bでインスタントフォームが安い送信を量産するのと、構造は同じです。

ファネルとコホート ― 時間軸を間違えない

ファネルは「同じ人の流れ」を段で見る道具、コホートは「同じ時期に入った集団」を時間で追う道具です。両者を混ぜると事故が起きます。

最大の事故は時間軸の混在です。たとえば「今月のCVR」を「今月獲得した人の最終的な成約率」と読んでしまう。SOLNAは定期購入モデルなので、初回購入 → F2（2回目）→ 定期転換、と成果は数週〜数ヶ月かけて確定します。獲得は今月でも、購入確定は先。だから率は必ず「獲得コホート」で締めます。そして直近コホートは確定前、つまり未成熟です。

つまずきポイントは一つだけ。直近コホートのCVRが低いのは「成績が悪い」のではなく「まだ確定していない」だけ。確定済みのコホートで評価し、直近は未成熟と明記します。この感覚はLTV（次節）でそのまま効いてきます。

真のCACと回収期間（チャネル別）

真のCACは、チャネルの支出を、そのチャネルで実際に獲得した新規「顧客」数で割ります。媒体CVではなく customers で割るのが肝です。

-- 真のCAC: 媒体CVではなく「実購入した新規顧客」で支出を割る
with new_customers as (                          -- customers = お金の真実
  select
    customer_id,
    acquisition_channel                     as channel,
    date_trunc(date(first_order_at), month) as cohort_month
  from `solna.customers`
),
acq as (                                         -- チャネル×月の新規顧客数
  select cohort_month as month, channel, count(*) as new_customers
  from new_customers
  group by 1, 2
),
spend as (                                        -- チャネル×月の広告支出
  select channel, date_trunc(date_day, month) as month, sum(spend) as spend
  from `solna.ad_spend`
  group by 1, 2
)
select
  s.month,
  s.channel,
  s.spend,
  a.new_customers,
  safe_divide(s.spend, a.new_customers) as cac    -- ← 真のCAC
from spend s
left join acq a using (month, channel)
order by s.month, s.channel

SOLNAのある月の出力例（架空データ）。

回収期間（payback period）は、CACを1顧客あたりの月次貢献利益で割ったものです。LTVがCACを上回るだけでは足りません。いつ回収できるか、というキャッシュの時間軸が、特にサブスクでは効いてきます。

つまずきポイント。新規顧客の定義は first_order_at の月で固定します。そして organic はCACがほぼゼロに見えますが、ここで「全部organicに寄せよう」と判断してはいけません。それが成り立たない理由（獲得チャネルと顧客の質が交絡している）は、記述だけでは語れない。第4回以降の宿題として、ここでは旗だけ立てておきます。

LTVの素朴な見積もりと落とし穴（未成熟コホート）

素朴なLTVは、コホートの累積売上をコホート人数で割り、経過月ごとに積み上げたものです。これがLTV曲線になります。

-- コホート別 1顧客あたり累積売上（= LTV曲線の素）
with orders_rel as (
  select
    c.customer_id,
    date_trunc(date(c.first_order_at), month)                  as cohort_month,
    date_diff(date(o.order_at), date(c.first_order_at), month) as m,   -- 初回からの経過月
    o.amount
  from `solna.customers` c
  join `solna.orders`    o using (customer_id)
  where o.order_at >= c.first_order_at
),
by_month as (
  select cohort_month, m, sum(amount) as revenue
  from orders_rel
  group by 1, 2
),
cohort_size as (
  select date_trunc(date(first_order_at), month) as cohort_month, count(*) as n
  from `solna.customers`
  group by 1
)
select
  b.cohort_month,
  b.m                                          as months_since_first,
  cs.n                                         as cohort_customers,
  safe_divide(
    sum(b.revenue) over (
      partition by b.cohort_month order by b.m
      rows between unbounded preceding and current row
    ),
    cs.n
  )                                            as cum_revenue_per_customer   -- ≒ コホートLTV
from by_month b
join cohort_size cs using (cohort_month)
order by b.cohort_month, b.m

これを可視化し、コホート同士を比較するときに罠が三つあります。

一つ目が未成熟コホート。経過が浅いコホート（まだ3ヶ月しか経っていない直近月）を、12ヶ月経った古いコホートと同じ土俵で比べると、直近が必ず負けます。それは質ではなく時間の差です。対策は、固定窓（例: 90日LTV）で全コホートを揃えて比べること。

import pandas as pd
import matplotlib.pyplot as plt
 
# 上のSQL(コホート別累積売上)の結果を読み込む想定
ltv = pd.read_gbq("select * from `solna.mart_cohort_ltv`")
 
# コホートごとにLTV曲線を引く（横軸 = 初回からの経過月）
fig, ax = plt.subplots()
for cohort, g in ltv.groupby("cohort_month"):
    g = g.sort_values("months_since_first")
    ax.plot(g["months_since_first"], g["cum_revenue_per_customer"], label=str(cohort))
ax.set_xlabel("初回購入からの経過月")
ax.set_ylabel("1顧客あたり累積売上（≒ LTV）")
ax.legend(title="獲得コホート", fontsize=8)
plt.show()
 
# --- 未成熟コホートの罠を避ける: 固定窓(例 90日 ≒ 3ヶ月)で揃えて比較する ---
WINDOW_M = 3
at_window = (
    ltv[ltv["months_since_first"] <= WINDOW_M]
    .sort_values("months_since_first")
    .groupby("cohort_month")
    .tail(1)                                # 各コホートの「3ヶ月時点」の累積
    .rename(columns={"cum_revenue_per_customer": "ltv_90d"})
)
# 経過がWINDOW_Mに満たない直近コホートは比較から除外（= 未成熟）
mature = at_window[at_window["months_since_first"] >= WINDOW_M]
print(mature[["cohort_month", "ltv_90d"]])

SOLNAの例（架空データ）では、90日（3ヶ月）時点で1顧客あたり累積 ¥12,000〜15,000、12ヶ月で ¥35,000前後。直近2コホートは経過90日未満なので、比較からは外します。

残る二つの罠。生存者バイアス（解約者を分母から落とすとLTVが過大になる）と、「ARPU × 平均継続月数」式の単純計算（継続月数の分布が歪んでいると平均が実態を表さない）。素朴な掛け算より、コホートの累積曲線を素直に見るほうが安全です。

接合率（spine coverage）: どこまで遡れるかが適用範囲の上限

ここまでは「成果を数える」話でした。次に、その成果が「どこまで行動を遡れるか」を測ります。

背骨（spine）とは、広告接触 → ID → 成果 を一本に繋ぐ結合の連鎖です。接合率は、成果のうち、その手前の行動まで一本で辿れるものの割合。これは0か1かではなく、割合です。そしてこの割合が、後続の手法（リードスコアリング、データドリブンアトリビューション、予測）の適用可能ユニバースの上限を決めます。背骨が55%しか通っていない経路では、その手法は最大でも55%にしか効きません。

-- 接合率(spine coverage): 成果(顧客)のうち、行動まで遡れる割合
with outcomes as (
  select
    customer_id,
    acquisition_channel as channel,
    ga_client_id,                                 -- 行動の結合キー
    first_order_at,
    date_trunc(date(first_order_at), month) as cohort_month
  from `solna.customers`
),
behavior as (
  select
    o.customer_id,
    count(s.session_id) as sessions_before_first
  from outcomes o
  left join `solna.sessions` s
    on  s.ga_client_id  = o.ga_client_id
    and s.session_start < o.first_order_at        -- 成果時刻より前の行動だけ
  group by 1
)
select
  o.cohort_month,
  o.channel,
  count(*)                                              as customers,
  round(avg(if(o.ga_client_id is not null, 1, 0)), 3)  as join_key_rate,
  round(avg(if(b.sessions_before_first > 0, 1, 0)), 3) as spine_coverage  -- 中心指標
from outcomes o
left join behavior b using (customer_id)
group by 1, 2
order by o.cohort_month, o.channel

SOLNAの出力例（架空データ）。

店舗・催事（store_visits）経由はWeb行動の背骨を持たないため、接合率がほぼゼロになります。これは構造的な死角です。後でこの層に行動ベースのスコアリングを当てようとしても、原理的に当たらない。だから「やらない」を先に決められる。

セグメンテーションの初歩

最後に、セグメンテーション。いきなりk-meansに行く必要はありません。まずは記述の「切り口」で十分です。

region × acquisition_channel × membership_tier × age_band で、CVR・CAC・LTVを割って眺める。意思決定を変えるほどの差は、その単純なクロス集計の段階でほとんど見えます。クラスタリングは、手で切れる切り口が出尽くしてからの話です。

つまずきポイントは第1回に戻ります。セグメントを細かく切りすぎると、各セルの件数（n）が小さくなり、ただのばらつきを「差」だと誤読する。切り口を増やす前に、そのセルのnが、差を語れるだけの大きさかを確認します。

フレームとの接続

この回は、診断フレームの次の位置に対応します。

• データ確認: 「成果の所在（お金の真実はどのシステムが持つか）」と「背骨が一本で繋がるか」を、まさにこの回で実装しました。
• 接合率（spine coverage）: 後続のすべての手法が使える範囲の上限を、着手前に画定する作業です。
• 成熟度のステップの「記述」段階: 何が起きたかを正しく数える最初の段階。ここを誤ると、上の「予測」「因果」がすべて狂います。

そして伏線を一つ。ここで出した真のCAC・LTVは「相関」までしか語りません。「organicが安いから全部organicに寄せる」が成り立たない理由 ― 獲得チャネルと顧客の質が交絡している ― は、第4回（因果の言語）以降の宿題です。記述で土台を固め、因果でその先へ進みます。

章末チェックリスト

• 「お金の真実」がどのテーブルにあるか特定したか（媒体CV vs 実購入の乖離を見たか）
• 率を獲得コホートで締めたか（直近は未成熟と注記したか）
• 真のCAC（実購入ベース）と回収期間を出したか
• LTVの比較を固定窓（例: 90日）で揃えたか（未成熟コホートを同列に並べていないか）
• 接合率を経路別・時間別に測り、後続手法の適用範囲の上限を把握したか
• セグメントのnが小さすぎないか（ばらつきを「差」と誤読していないか）

ここまでは「SOLNA（取引件数が多く反復購入があり複数地域のD2C）で、どう実装するか（HOW）」の話でした。同じ手法が自社で成立するか、つまり予算配分やチャネル選別をどう動かすかという判断（WHAT/WHY）は、対になる記述・予測編で扱います。

対照的な構造も見ておきます。SOLNAは反復購入があるためコホートLTVが主役になりますが、取引件数が少なく一回限りで営業が介在するB2B（記述・予測編の対照例）では、LTVより真のCACが主役で、成果ラベルの少なさが律速になります。手法は同じでも成立条件は逆を向くわけです。一方で、「店舗・催事は行動の背骨がほぼ通らない」という死角は、B2Bのインスタントフォームと同型でした。接合率という見方は、業態を越えて効きます。