本記事のポイント
_DealCurrentState + _CohortMilestones の 2 シート + Apps Script 日次蓄積 + Looker Studio ヒートマップで再現できます。蓄積コードは約 50 行です「3 月のキャンペーンと 4 月のキャンペーン、どちらのリード品質が良かったか」を絶対値の勝率で語ろうとすると、必ず判断を誤ります。3 月は経過 90 日、4 月は経過 60 日、というように成熟度が違うからです。私たち humbulls はある業務用美容機器メーカーの支援で、コホート × 経過日数 × 勝率のマトリクスを Apps Script + Looker Studio で組み、月次キャンペーンの質を同じ経過日数で比較できる仕組みを構築しました。本記事は Pillar 記事「HubSpot Starter で Pro 級ダッシュボードを再現する」シリーズ #5 として、その構築手順を STEP 1〜5 で再現できる形にまとめます。特別なスキルは不要で、型と巻末キットがあれば組めます。
最初に、この記事で組み上げるものを見てください。行が発生月 (cohort_month)、列が経過日数バケット (7 / 14 / 30 / 60 / 90 日)、セルの色が勝率のヒートマップになった Looker Studio の Pivot table です。
このマトリクスがあると、30 days の列を縦に見るだけで「経過 30 日時点」の勝率を月別に比較できます。絶対値比較では絶対に見えてこなかった「初動の質が改善しているか」が一目でわかります。
コホート分析 (= 同期間に発生した集団を時系列で追跡する分析手法) は、B2C SaaS では Amplitude や Reforge を中心に解約予測・リテンション計測の定石として知られています。一方 B2B 営業では実装例が少なく、「月別の勝率推移を線グラフで眺める」程度に留まりがちです。この手法を B2B 営業に持ち込むのが本記事の狙いです。
「先月より今月の勝率が下がった、キャンペーンが失敗した」。営業会議でこう言われて、そのまま鵜呑みにしていないでしょうか。
最初に設計するのは、データでもコードでもなく「何と何を比較するか」です。ここを間違えると、この先どれだけ丁寧に組んでも意味のない数字になります。よくある失敗は、月別の勝率を絶対値で比較することです。「3 月の cohort は勝率 40%、4 月は 25% → 4 月は失敗」と判断するパターン。これは間違いです。3 月の cohort は既に経過 60 日、4 月は経過 30 日、と成熟度が違うので、絶対値の比較は経過日数差で歪んでいます。
正しい比較軸は「同じ経過日数で揃える」ことです。具体例で見ると、判断がこう変わります。
| 観点 | 絶対値比較 (間違い) | コホート × 経過日数比較 (正しい) |
|---|---|---|
| 3 月 cohort | 勝率 40% (経過 60 日) | 30 日時点 18% / 60 日時点 27% |
| 4 月 cohort | 勝率 25% (経過 30 日) | 30 日時点 22% / (60 日経過待ち) |
| 判断 | 4 月は失敗 | 30 日時点で 4% 改善、初動の質が向上 |
「30 日時点」で揃えて比較すると、4 月 cohort はむしろ 3 月より初動が良いことがわかります。これがコホート分析を「月次キャンペーンの善し悪し」の判断に持ち込む価値です。
比較軸を決めるとき、もう一つ決めるのが経過日数のバケット (区切り) です。ここでは 7 / 14 / 30 / 60 / 90 日を使います。バケットは業態で調整でき、営業サイクルが短い B2B SaaS なら 3 / 7 / 14 / 30 / 60、長い業務用機器なら 30 / 60 / 90 / 120 / 180 のように設定します。今回は平均営業サイクル 60 日を前提に 7 / 14 / 30 / 60 / 90 を採用しました。
つまずきポイント: 最新月の cohort は経過 30 日時点までしか見えません。「最新キャンペーンが史上最高」と早合点しがちですが、90 日バケットまで揃うには 3 ヶ月かかります。最初の 3 ヶ月は蓄積期間と割り切り、30 日時点までの比較で運用してください。
_DealCurrentState + _CohortMilestones の 2 シート「1 枚のシートに全部入れておけば十分では」。最初はほぼ必ずこう考えます。
コホート分析の品質は、データ構造でほぼ決まります。1 シートで全てを賄おうとすると Pivot の計算が複雑化し、Looker Studio 側で式が肥大化します。「現在の状態 (上書き型)」と「時系列の蓄積 (追記型)」を物理的に 2 シートに分離しておくのが正解です。用意するのは次の 2 シートです。
| シート名 | 書き込み方式 | 主な列 |
|---|---|---|
_DealCurrentState |
毎日 overwrite | deal_id / cohort_month / current_stage_jp / won_flag / lost_flag / active_flag / amount |
_CohortMilestones |
毎日 append | recorded_date / cohort_month / days_elapsed / cohort_size / won_count / win_rate |
_DealCurrentState は「最新の Deal 一覧」で、Looker Studio の Funnel chart や Pipeline 表示に使います。_CohortMilestones は「時系列のコホート集計」で、STEP 4 の経過日数 × 勝率 Pivot に使います。
_CohortMilestones は毎朝 _DealCurrentState を読み込み、cohort_month 別に days_elapsed を計算して 1 行ずつ append する形で生成します。ここで集計を済ませておくと、後段の Pivot 設計が大幅に楽になります。
つまずきポイント: サブ #4 で扱った _StageSnapshots (Stage History の代替) からコホート分析を組もうとすると、Looker Studio 側の Calculated field が爆発します。_StageSnapshots は「日次の Stage 推移」が主用途で、「同じ Deal の経過日数の推移」を計算するには列構造が合いません。コホート分析には専用の 2 シートを用意してください。もう一点、_CohortMilestones は cohort_month × days_elapsed × snapshot_date の 3 次元で肥大化します (= 24 cohort × 30 マイルストーン × 365 日 = 約 26 万行)。半年経過したら別シートに退避する仕組みを入れておくと安心です。
「書き込んだはずの 2026-04 が、なぜか Sheet 上で日付になっている」。この症状で半日溶かすのが、この STEP で最も多いつまずきです。
_CohortMilestones に書き込む際、cohort_month 列 (= 2026-04 のような YYYY-MM 文字列) が Sheets 側で Date 型に自動変換される罠があります。放置すると、Looker Studio で cohort_month を Dimension に使ったとき「全部 2026 年で集計される」「2026-04-01 00:00:00 に展開されてグルーピングが崩れる」という症状になります。サブ #2 でも同じ罠を取り上げています。
回避策は、setNumberFormat('@') を 必ず setValues の前に呼ぶ ことです。列全体を Plain Text に強制します。
function recordCohortMilestones() {
const ss = SpreadsheetApp.openById('YOUR_SHEET_ID');
const sheet = ss.getSheetByName('_CohortMilestones');
const today = Utilities.formatDate(new Date(), 'JST', 'yyyy-MM-dd');
const currentStates = readCurrentDealStates();
const cohortGroups = groupByCohortMonth(currentStates);
const rows = [];
for (const [cohortMonth, deals] of Object.entries(cohortGroups)) {
const daysElapsed = computeDaysElapsed(cohortMonth, today);
const wonCount = deals.filter(d => d.won_flag === 1).length;
rows.push([today, cohortMonth, daysElapsed, deals.length, wonCount, wonCount / deals.length]);
}
const startRow = sheet.getLastRow() + 1;
const range = sheet.getRange(startRow, 1, rows.length, 6);
range.setNumberFormat('@'); // ← setValues の前に Plain Text 強制
range.setValues(rows);
}
groupByCohortMonth() と computeDaysElapsed() のヘルパーを含めても、全体で約 50 行の実装で済みます。あとはこの関数を毎朝 1 回の Apps Script Trigger で回せば、_CohortMilestones に日次で行が積み上がっていきます。
つまずきポイント: Plain Text を強制すると Sheet 内の =SUM(...) のような関数が動かなくなります。ただし Looker Studio 側でデータソース定義の型を「Number」に指定すれば問題なく集計できるので、実運用では困りません。どうしても Sheet 内で数値関数を使いたい列だけ setNumberFormat('0') に切り替える手もあります。
「表は出せた。でも数値の海で、会議では誰も見ない」。可視化のゴールを「色で一目わかる」に置かないと、こうなります。
Pivot table の 3 構造は Row = cohort_month (発生月) / Column = days_elapsed (経過日数バケット) / Value = win_rate です。この軸を間違えると分析が成立しません。よくある設計は Row = create_date, Column = stage, Value = COUNT のような月次トレンドグラフですが、これだと「同じ経過日数で比較する」という本質が抜け落ちます。正しく組むと、次のマトリクスになります。
| 7 days | 14 days | 30 days | 60 days | 90 days |
2026-01 | 5% | 12% | 22% | 31% | 38% |
2026-02 | 6% | 14% | 25% | 33% | 40% |
2026-03 | 7% | 15% | 27% | 35% | (90 日未到達) |
2026-04 | 9% | 18% | 30% | (60 日未到達) | - |
2026-05 | 10% | 19% | (30 日未到達) | - | - |
30 days の列を縦に見ると 2026-01: 22% → 2026-04: 30% という改善が読み取れます。Value の win_rate は won_count / cohort_size で計算しますが、cohort_size を「その月に発生したリード数 (初期母数固定)」とするか「その経過日数までに残っているアクティブ数」とするかで意味が変わります。今回は前者で運用しています (= 経過日数間の比較が容易になるため)。
数値が出たら、Pivot の Conditional formatting で 3 段階のヒートマップにします。これが会議で議論を回す肝です。
| 勝率 | 色 | 解釈 |
|---|---|---|
| < 15% | 薄い赤 (#FFE5E5) | 要改善 |
| 15% - 30% | 黄色 (#FFF8E5) | 平均水準 |
| > 30% | 薄い緑 (#E5F5E5) | 良好 |
しきい値 (15% / 30%) は業態で調整します。BtoB SaaS なら 10% / 20%、業務用機器なら 20% / 40% のように、経営層と相談して自社の感覚に合わせてください。月別 cohort の推移を折れ線で併せて見せると、改善の方向がさらに掴みやすくなります。
つまずきポイント: Looker Studio は 1 つの Pivot table に対して指定できる色数に上限があります (執筆時点で 6 色程度)。段階を増やすと見づらく判断も鈍るので、3 段階に絞るのが結局いちばん読みやすいです。
「3 月より 4 月が改善した、まではわかった。で、次は何をすればいい?」。マトリクスが完成すると、必ずこの問いに突き当たります。
コホート分析の最終ゴールは、可視化ではなく「次のキャンペーンで何を変えるか」の意思決定です。従来このフェーズは営業マネージャーの経験と勘で行われ、「3 月は資料ダウンロードが多かったから質が良かったのでは」といった感覚論に留まりがちでした。当たることもありますが、再現性に欠けます。
ここは、完成したマトリクスと月次施策の履歴を AI に渡し、打ち手仮説を出させる工程に置き換えられます。データポイントが「30 日時点で 3 月 22% → 4 月 30%」のように具体的なので、AI 側も「リード獲得チャネル / 初動 SLA / 初回提案までの日数」といった具体的な変数で仮説を返せます。営業会議ではこの仮説群を起点に議論します。
【AI が出した打ち手仮説の例】
1. 30 日時点の勝率が 8% 上がった主因は、3 月末に導入したインサイドセールスの
初動 SLA 短縮 (= 24 時間以内アプローチ) と推定される
2. 14 日時点では差が小さい (= +2%) ので、初動の質ではなく中盤の提案フェーズで
差がついている可能性
3. 5 月 cohort の 7 日時点が史上最高 (10%) なので、4 月末のキャンペーン素材
リニューアルが効いている可能性
4. 60 日時点の 3 月 27% は業界平均 (= 30%) より低い、後半失速の要因を調査すべき
5. 7 月以降は新規キャンペーン素材 + インサイド SLA の組み合わせで
30 日時点 35% を目指す
仮説を複数出してもらうと、営業マネージャーは「これは事実と合致する / これは違う / これは検証してみよう」と振り分けできます。打ち手の優先度付けが感覚論より早く決まります。
つまずきポイント: AI の仮説は「相関」を見ているだけで「因果」を保証しません。最終的に施策を打つかどうかは、営業現場の感触と組み合わせて人間が判断してください。AI は「仮説の幅」を出す役割で、「正解」を出す役割ではありません。検証は 1 ヶ月単位で 1 仮説に絞ると、因果が見えやすくなります。
STEP 1〜5 を通したら、次の 4 点が揃っていれば成功です。
_CohortMilestones に日次で行が増えている — 毎朝 Trigger が回り、recorded_date が前日ぶんまで積み上がっている。cohort_month が 2026-04 の文字列のまま保持され、日付に化けていない30 days の列を縦に読める。まだ 30 日経っていない最新月のセルは空欄で構いませんここまで来れば、絶対値比較では見えなかったキャンペーン品質の改善が、会議のその場で読み取れる状態になっています。
月別の勝率を絶対値で比べると、経過日数の差で判断を誤ります。同じ経過日数で揃えるマトリクスを組めば、キャンペーン品質を Apple to Apple で評価できます。作り方は 2 シートの分離 → Apps Script 日次蓄積 (Date 罠を先に潰す) → Looker Studio ヒートマップ → AI で打ち手仮説、の 5 STEP。構築は 1 時間、追加コストはゼロです。ただし数字が意味を持つには 3 ヶ月の蓄積が要るので、まずは STEP 2 の 2 シートを用意するところから始めてみてください。
本シリーズは全 6 回です。Pillar から順に読む必要はなく、自社の状況に合う 1 本から拾えます。
| # | タイトル | 役割 |
|---|---|---|
| #1 (Pillar) | HubSpot Starter で Pro 級ダッシュボードを再現する | 全体設計 |
| #2 | Apps Script で HubSpot ↔ Sheets を月額 $0 で自動同期 | データ層 |
| #3 | Looker Studio で HubSpot ダッシュボードを 30 分で組む | 可視化層 |
| #4 | Stage History を Apps Script 30 行で日次 Snapshot | Professional 専用機能の代替 |
| #5 (本記事) | 月次コホート × 経過日数 × 勝率マトリクスを組む | 分析フレームワーク |
| #6 | Kintone と HubSpot の並行運用 — 増分同期で破綻させない | 既存 CRM 統合 |
humbulls の Growth Partner サービス では、コホート分析の設計から営業会議の議論支援まで伴走しています。テンプレと打ち手仮説プロンプトを一括で入手したい方は、BtoB マーケ AI 活用ガイド もご活用ください。
STEP 1〜5 をまとめて Claude Code に実行させるためのキットです。分析軸の設計からシート構成、蓄積コード、Looker Studio 設定、打ち手仮説までを一気通貫で組み上げます。
種別: 実装キット 使うもの: Claude Code + HubSpot MCP (MCP がない場合は、_DealCurrentState の列一覧とサンプル数行をブラウザ版 Claude に貼り付けても設計まで進められます) 事前に用意するもの: サブ #2 の同期で作った _DealCurrentState シート (deal_id / cohort_month / won_flag / lost_flag / active_flag / amount)、自社の平均営業サイクル、Deal のステージ定義
プロンプト:
HubSpot Starter + Apps Script + Google Sheets + Looker Studio で、
月次コホート × 経過日数 × 勝率のマトリクスを構築する手順とコードを
一式生成してください。
【私の状況】(記入例。自社の値に置き換える)
- 業態: 業務用機器メーカー
- 平均営業サイクル: 60 日
- 月次新規リード数: 40〜60 件
- 既存シート: _DealCurrentState
(deal_id / cohort_month(YYYY-MM 文字列) / won_flag / lost_flag /
active_flag / amount)
- ステージ: 01_新規問合せ / 02_デモ / 03_商談化 / ... / 09_成約 / 99_失注
【必ずこの基準に従うこと】
1. 比較軸は「同じ経過日数で揃える」。絶対値の月別勝率比較はしない
2. 経過日数バケットは営業サイクルに合わせる
(60 日なら 7 / 14 / 30 / 60 / 90)。5 段以内に抑える
3. データは 2 シートに分離する —
_DealCurrentState(毎日 overwrite)と
_CohortMilestones(毎日 append: recorded_date / cohort_month /
days_elapsed / cohort_size / won_count / win_rate)
4. cohort_month は YYYY-MM 文字列で扱う。Apps Script では
setNumberFormat('@') を setValues の前に呼び、Date 自動変換を防ぐ
5. win_rate = won_count / cohort_size。cohort_size は
「その月に発生したリード数(初期母数固定)」で統一する
6. Looker Studio は Row=cohort_month / Column=days_elapsed /
Value=win_rate。Conditional formatting は 3 段階(要改善/平均/良好)
【出力】
1. _DealCurrentState / _CohortMilestones の列定義(型と計算式)
2. recordCohortMilestones() と、ヘルパー
(groupByCohortMonth / computeDaysElapsed)の Apps Script コード
3. 毎朝 1 回実行する Trigger の設定手順
4. Looker Studio の Pivot 設定手順(軸 / データソースの型指定 /
3 段階ヒートマップのしきい値と HEX)
5. 蓄積開始から意味のある比較ができるまでの時間軸の注記
出力の確認ポイント:
setNumberFormat('@') が setValues の 前 に来ているか。順序が逆だと Date 罠を防げませんcohort_size の定義が「初期母数固定」で統一されているか。経過日数ごとにアクティブ数へ切り替わっていると、列間の比較が崩れますcohort_month が Text 型、win_rate が Number 型として読まれる指定になっているかうまくいかないとき:
cohort_month が「2026 年」だけに丸められる → シート側で Date 化している兆候。_CohortMilestones の該当列が Plain Text か、データソースの型指定が Text か、両方を確認_DealCurrentState の CSV を書き出してブラウザ版 Claude に貼り付け、設計とコード生成だけ先に進める