最終更新日: / 公開日:
最終更新日: / 公開日:
「3 月のキャンペーンと 4 月のキャンペーン、どちらの方がリード品質が良かったか」を、絶対値の勝率比較で語ろうとすると必ず歪みます。3 月は経過日数 90 日、4 月は経過日数 60 日、というように「成熟度」が違うからです。私たち humbulls はある業務用美容機器メーカーの実装で、Cohort × 経過日数 × 勝率のマトリクスを Apps Script + Looker Studio で組み上げ、月次キャンペーンの質を Apple to Apple で評価できる仕組みを構築しました。Pillar 記事「HubSpot Starter で Professional 級ダッシュボード」シリーズ #5 として、分析フレームワークの設計図を公開します。
1. なぜ B2B 営業でコホート分析が重要か — 絶対値比較の罠
Cohort 分析は B2C SaaS の解約予測手法として有名ですが、B2B 営業でも「キャンペーン品質の早期検知」に強力に機能します。
Cohort 分析 (= 同期間に発生した集団を時系列で追跡する分析手法) は、B2C SaaS の世界では Amplitude や Reforge の記事を中心に「解約予測 / リテンション計測」の定石として知られています。一方 B2B 営業の文脈では事例が少なく、「月別の勝率推移を線グラフで眺める」程度の比較しか行われていないのが実態です。※ 本記事の円換算はすべて 1 ドル = 150 円 (2026 年 5 月時点) を基準に計算しています。
実務でよくある失敗は 「絶対値で月別の勝率を比較する」 ことです。例えば「3 月の cohort は勝率 40%、4 月の cohort は勝率 25% → 4 月のキャンペーンは失敗」と判断するパターン。これは間違いです。3 月の cohort は既に経過 60 日、4 月の cohort は経過 30 日、というように成熟度が違うので、絶対値の比較は経過日数差で歪んでいます。
私たち humbulls は 「同じ経過日数で比較する」 という観点を Cohort 分析に持ち込み、キャンペーン品質を Apple to Apple で評価する設計に切り替えました。具体例で言うと、こう変わります:
| 観点 | 絶対値比較 (間違い) | Cohort × 経過日数比較 (正しい) |
|---|---|---|
| 3 月 cohort | 勝率 40% (経過 60 日) | 30 日時点 18% / 60 日時点 27% |
| 4 月 cohort | 勝率 25% (経過 30 日) | 30 日時点 22% / (60 日経過待ち) |
| 判断 | 4 月は失敗 | 30 日時点で 4% 改善、初動の質向上 |
「30 日時点」で揃えて比較すると、4 月 cohort はむしろ 3 月より初動が良いことがわかります。これが Cohort 分析を「月次キャンペーンの善し悪し」の判断に持ち込む価値です。
注意点がひとつ。Cohort 分析は「経過日数が同じ点でしか比較できない」という制約があります。最新月の cohort は経過 30 日時点までしか見えないので、「最新キャンペーンが今までで一番良い」と早急に判断できません。3 ヶ月待ってから初めて正しい比較が成立します。
🤖 AI で実行する — 使ったプロンプト / ワークフロー
目的: B2B 営業向けに Cohort 分析の比較軸を設計 所要時間: 30 分 ツール: Claude (Opus 4.7)
プロンプト:
あなたは B2B 営業 KPI の分析設計者です。月次キャンペーン (リード獲得) の品質を Cohort × 経過日数で比較する分析設計を提案してください。
【入力】
- 業態: [BtoB SaaS / 業務用機器 / 受託開発 等]
- 営業サイクル: 平均 [30 / 60 / 90] 日
- 月次新規リード数: [規模感]
【出力】
- Cohort 単位 (月次 / 週次 / キャンペーン単位 のどれを推奨か)
- 経過日数バケット (7 / 14 / 30 / 60 / 90 のどれを使うか)
- 比較軸 (cohort 同士 / 平均値 vs cohort / 直近 3 ヶ月平均 vs 当月)
- 営業会議でどう議論するか (= 「絶対値で判断しないルール」の徹底方法)
運用 Tips: - B2B で営業サイクルが 90 日以上の場合、月次 cohort でなく四半期 cohort に切る方が信頼性が高い - 営業マネージャーには「30 日時点で揃えた数字でしか議論しない」というルールを最初に共有
📚 参考文献
- Amplitude — Cohort Analysis 解説 — Amplitude, 取得日 2026-05
- Reforge — Retention Engagement Growth — Reforge, 取得日 2026-05
2. データ構造設計 — _DealCurrentState + _CohortMilestones の 2 シート構成
Cohort 分析の品質はデータ構造で 90% 決まります。「現在の状態」と「時系列蓄積」を物理的に分離するのが正解です。
Cohort 分析を組むうえで、データ構造の設計が最も重要です。1 シートで全てを賄おうとすると Pivot の計算が複雑化し、Looker Studio 側で式が肥大化します。最初に「上書き型 (current state)」と「追記型 (milestones)」を物理的に分離しておくと、後段の Pivot 設計がシンプルになります。
従来の実装では _StageSnapshots (= サブ記事 #4 で扱った Stage History 代替) から Cohort 分析を組もうとして、Looker Studio 側の Calculated field が爆発する、という設計失敗が起きがちです。_StageSnapshots は「日次の Stage 推移」が主用途なので、「同じ Deal の経過日数の推移」を計算するには列構造が合いません。
私たち humbulls の設計は、Cohort 分析専用に _DealCurrentState (上書き型) + _CohortMilestones (追記型) の 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 は「時系列の Cohort 集計」で、Pivot table での経過日数 × 勝率分析に使います。
_CohortMilestones の生成ロジックを Apps Script で書くと、毎朝 _DealCurrentState を読み込み、cohort_month 別に days_elapsed を計算して 1 行 ずつ append する、という処理になります。後段の Pivot 設計が大幅に楽になるので、ここで集計を済ませる価値があります。
なお _CohortMilestones は cohort_month × days_elapsed × snapshot_date の 3 次元データで肥大化しやすい (= 24 cohort × 30 マイルストーン × 365 日 = 約 26 万行) ので、半年経過したら別シートに退避する仕組みを入れておくと安心です。
注意点がひとつ。「2 シートに分けると Apps Script の改修コストが増える」と感じるかもしれませんが、Pivot 側の式が複雑化するコストの方が長期的に重いです。最初から分けておくと、後から「あの式どこに書いたか」を探す時間を節約できます。
🤖 AI で実行する — 使ったプロンプト / ワークフロー
目的: 自社の Deal データ構造から _DealCurrentState + _CohortMilestones の列定義を生成 所要時間: 30 分 ツール: Claude Code
プロンプト:
あなたは BtoB 営業 KPI のデータ設計者です。以下の Deal データ構造から Cohort 分析用の 2 シート構成を設計してください。
【既存の Deal データ】
- deal_id, dealname, amount, stage_jp, owner_name, create_date, close_date
【追加情報】
- ステージ: 01_新規問合せ / 02_デモ / 03_商談化 / ... / 09_成約 / 99_失注
【出力】
- _DealCurrentState の列定義 (型 / 計算式)
- _CohortMilestones の列定義 (型 / 計算式)
- Apps Script の processCohortMilestones() 関数の擬似コード
運用 Tips: - cohort_month は YYYY-MM 文字列で揃える (= Date 型にしない、Pivot 設計が楽) - 「失注」を含めるか除外するかで cohort_size の意味が変わるので、定義を社内で先に固める
📚 参考文献
- Apps Script SpreadsheetApp — Google Developer
- Cohort Analysis Best Practices — Heap — Heap, 取得日 2026-05
3. 「同じ経過日数で比較する」観点 — Pivot の Row / Column 設計
Cohort 分析の Pivot は、「Row = cohort_month / Column = days_elapsed」の 2 軸が定石。逆に切ると意味が崩れます。
Cohort × 経過日数の Pivot table は、Row と Column の選び方を間違えると分析が成立しません。正解はシンプルで 「Row = cohort_month (発生月) / Column = days_elapsed (経過日数バケット) / Value = win_rate」 の 3 構造です。
従来の Pivot 設計だと、「月別の勝率推移」を線グラフで見るために Row = create_date, Column = stage, Value = COUNT のような 2 軸 + フィルタの組み合わせが多用されます。これだと「同じ経過日数で比較する」という Cohort 分析の本質が抜け落ち、ただの月次トレンドグラフになってしまいます。
私たち humbulls の Pivot 設計は以下の通りです:
| 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 列 (= 経過 30 日時点) で月別の勝率を縦に比較できます。2026-01: 22% → 2026-04: 30% という改善が一目でわかります。これが絶対値比較では絶対に見えてこない情報です。
経過日数のバケット切り (7 / 14 / 30 / 60 / 90) は業態によって調整できます。営業サイクルが短い B2B SaaS なら 3 / 7 / 14 / 30 / 60、長い業務用機器なら 30 / 60 / 90 / 120 / 180 のように設定します。私たちは平均営業サイクル 60 日の前提で 7 / 14 / 30 / 60 / 90 を採用しました。
注意点がひとつ。Pivot table のセルの値は 「win_rate」(= won_count / cohort_size) で計算しますが、cohort_size は cohort_month 単位で固定 (= その月に発生したリード数) なのか、days_elapsed までに残っているリード数なのかで意味が変わります。前者は「初期母数固定の勝率」、後者は「失注を除いたアクティブ母数の勝率」です。私たちは前者で運用しています (= 経過日数間の比較が容易になるため)。
🤖 AI で実行する — 使ったプロンプト / ワークフロー
目的: 自社の営業サイクルに合った経過日数バケットを設計 所要時間: 20 分 ツール: Claude (Opus 4.7)
プロンプト:
あなたは B2B 営業の Cohort 分析設計者です。以下の業態 / 営業サイクルで使うべき「経過日数バケット」を提案してください。
【入力】
- 業態: [BtoB SaaS / 業務用機器 / 受託開発 / 等]
- 平均営業サイクル: [30 / 60 / 90 / 180 日]
- 商談額: [規模]
【出力】
- 推奨バケット (例: 7 / 14 / 30 / 60 / 90)
- 各バケットで読み取れるシグナル (= 7 日時点で何が見える / 30 日時点で何が見える)
- 比較レビューの頻度 (週次 / 月次 / 四半期)
運用 Tips: - バケットが多すぎる (= 7 段以上) と Pivot が読みにくくなる、5 段以内に抑える - 営業サイクルの 1 / 3 / 1 / 2 / 1 / 1.5 / 2 倍の倍率でバケットを切ると感覚的にわかりやすい
📚 参考文献
- Looker Studio Pivot tables — Google Support
- Mixpanel — Cohort Analysis Guide — Mixpanel, 取得日 2026-05
4. Apps Script で日次蓄積 — Date 自動変換の罠を回避
蓄積コードを動かす前に、Sheets が
2026-04を自動で Date 化する罠を必ず潰しておきましょう。回避しないと Cohort のグルーピングが崩壊します。
Apps Script で _CohortMilestones シートに書き込む際、cohort_month 列 (= 2026-04 のような YYYY-MM 文字列) が Sheets 側で Date 型に自動変換 されてしまう罠があります。これを放置すると、Looker Studio で cohort_month を Dimension として使った時に「全部 2026 年で集計される」「2026-04-01 00:00:00 のように展開されてグルーピングが崩れる」という症状になります。
従来の対応は、書き込み後に Sheet を見て「あれ、なぜ Date になってる」と気付き、Apps Script の setValues() の前後にデバッグログを仕込んで原因を特定する、というパターンでした。半日溶ける割に再発しやすい厄介な問題です。サブ記事 #2 (Apps Script で HubSpot ↔ Sheets を月額 $0 で自動同期) でも同じ罠を取り上げています。
私たち humbulls の実装では、_CohortMilestones 書き込み時に 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() のヘルパー関数の中身も Claude Code に書かせれば、全体で 50 行程度の実装で済みます。
注意点がひとつ。Plain Text 強制すると Sheet 内の =SUM(...) のような関数が動かなくなりますが、Looker Studio 側ではデータソース定義で「Number 型」として読ませれば問題なく集計できます。Sheet 内で関数を多用するなら、数値列だけ setNumberFormat('0') に切り替える対応も可能です。
🤖 AI で実行する — 使ったプロンプト / ワークフロー
目的: Date 自動変換罠を回避した蓄積コードを生成 所要時間: 30 分 ツール: Claude Code
プロンプト:
Apps Script で `_CohortMilestones` シートに毎日 cohort 単位の勝率を追記するコードを書いてください。
【要件】
- 入力: `_DealCurrentState` (deal_id, cohort_month, won_flag, lost_flag, active_flag)
- 出力列: recorded_date / cohort_month / days_elapsed / cohort_size / won_count / win_rate
- cohort_month 列の Date 自動変換を防ぐため setNumberFormat('@') を setValues の前に呼ぶ
- append-only (既存行は変更しない)
【出力】 recordCohortMilestones() + ヘルパー関数 (groupByCohortMonth / computeDaysElapsed)
運用 Tips: - cohort_month を YYYY-MM 文字列で扱う (Date 型にしないことが Cohort 分析の安定運用の鍵) - 失敗時の通知は recorded_date が前日と同じならスキップして再実行、というロジックも入れると安全
📚 参考文献
- Apps Script setNumberFormat — Google Developer
- HubSpot CRM Deals API — Deal データ取得
5. Looker Studio Pivot で可視化 — ヒートマップで質を一目化
Cohort 分析の真価は「色で一目わかる」可視化にあります。Looker Studio の Conditional formatting でヒートマップ化すれば、営業会議で議論が回ります。
Pivot table を作っただけでは、数値の海を眺めるだけになりがちです。Cohort 分析は「同じ経過日数で見たときに、月によって勝率が改善 / 悪化しているか」を視覚的に掴むのが目的なので、ヒートマップ風の Conditional formatting が必須です。
従来の Excel ベースの Cohort 分析だと、毎月手動でセルの色付けをやり直す運用が発生し、月次レビューのたびに 30 分溶ける、というパターンが多々あります。色付けが面倒で省略されると、結局「数字の表」のままレビューに持ち込まれて、議論が深まりません。
私たち humbulls の Looker Studio 設定は、Pivot table の Conditional formatting で 3 段階のヒートマップ を組んでいます:
| 勝率 | 色 | 解釈 |
|---|---|---|
| < 15% | 薄い赤 (#FFE5E5) | 要改善 |
| 15% - 30% | 黄色 (#FFF8E5) | 平均水準 |
| > 30% | 薄い緑 (#E5F5E5) | 良好 |
しきい値 (15% / 30%) は業態によって調整できます。BtoB SaaS なら 10% / 20% 、業務用機器なら 20% / 40% 、というように。営業マネージャーや経営層と相談して、自社の感覚に合わせるのが正解です。
サブ記事 #3 (Looker Studio で HubSpot ダッシュボードを 30 分で組む) で Pivot 設定の基本手順を扱っているので、Cohort 用の Conditional formatting はこの記事と並行して進めると効率的です。
注意点がひとつ。Conditional formatting は Pivot の「Style」設定で色を指定しますが、Looker Studio の仕様で 1 つの Pivot table に対して上限色数があります (執筆時点では 6 色程度)。3 段階に絞った方が見やすく、判断もしやすいです。
🤖 AI で実行する — 使ったプロンプト / ワークフロー
目的: Conditional formatting のしきい値を自社実績データから自動算出 所要時間: 20 分 ツール: Claude (Opus 4.7) / Google Sheets
プロンプト:
あなたは Looker Studio のヒートマップ設計者です。以下の Cohort 分析データから Conditional formatting の 3 段階しきい値を提案してください。
【入力データ】
- 過去 12 cohort 分の win_rate の中央値: [値]
- 25 パーセンタイル: [値]
- 75 パーセンタイル: [値]
【出力】
- 「要改善 / 平均 / 良好」の 3 段階しきい値
- 各しきい値の判断根拠 (= なぜそのパーセンタイルを採用したか)
- 推奨配色 (HEX) と Looker Studio の Style 設定手順
運用 Tips: - 「中央値以下を要改善」だと半分が赤くなるので、25 パーセンタイル以下を要改善にする方が見やすい - 配色は薄いトーン (彩度低め) を使う、ヒートマップが原色だと読み手の疲労が大きい
📚 参考文献
- Looker Studio Pivot tables — Conditional formatting — Google Support
- Looker Studio Pivot tables — Google Support
6. キャンペーン改善への落とし方 — Cohort データから打ち手仮説を出す
Cohort 分析の最終ゴールは「次のキャンペーンで何を変えるか」の意思決定です。AI に Cohort データを渡すと、打ち手仮説を 30 分で出せます。
Cohort 分析で「3 月より 4 月が改善した」とわかっても、「なぜ改善したのか / 次は何を変えるべきか」 という打ち手仮説に繋がらないと意味がありません。営業会議で議論する材料として、Cohort データを基にした仮説出しが必要です。
従来このフェーズは「営業マネージャーの経験と勘」で行われていました。「3 月は資料ダウンロードが多かったから質が良かったのでは」「4 月はテレアポを増やしたから初動が早かったのかも」というように。当たることもありますが、感覚論なので再現性に欠けます。
私たち humbulls は Cohort データ + 月次施策の履歴を Claude Code に渡し、打ち手仮説を 5 個出させる ワークフローに切り替えました。Cohort のデータポイントが「30 日時点で 3 月 22% → 4 月 30%」のように具体的なので、AI 側も「リード獲得チャネル / 初動 SLA / 初回提案までの日数」のような具体的な変数で仮説を出せます。営業会議ではこの 5 仮説を起点に議論を進めます:
【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% を目指す
仮説を 5 個出してもらうと、営業マネージャーは「この仮説は事実と合致する / これは違う / これは検証してみよう」と振り分けができます。打ち手の優先度付けが感覚論より早く決まります。
注意点がひとつ。AI の仮説は「相関」を見ているだけで「因果」を保証しません。最終的に施策を打つかどうかは、営業現場の感触と組み合わせて人間が判断してください。AI は「仮説の幅」を出す役割で、「正解」を出す役割ではありません。
🤖 AI で実行する — 使ったプロンプト / ワークフロー
目的: Cohort データから打ち手仮説を 5 個出す 所要時間: 30 分 ツール: Claude (Opus 4.7)
プロンプト:
あなたは BtoB 営業の KPI 改善コンサルタントです。以下の Cohort 分析データと月次施策履歴から、次のキャンペーンで打つべき施策仮説を 5 個出してください。
【Cohort × 経過日数 × 勝率 マトリクス】
[Pivot のスクリーンショット or CSV を貼り付け]
【月次施策履歴】
- 2026-01: 〇〇キャンペーン (リード単価 $X / 獲得数 Y)
- 2026-02: △△キャンペーン
- 2026-03: インサイド SLA 短縮導入
- 2026-04: キャンペーン素材リニューアル
【出力】
- 仮説 5 個 (= 各仮説に「数値根拠」「打ち手」「検証方法」を含める)
- 仮説の優先順位 (= 効果サイズ × 実行コスト)
運用 Tips: - 営業会議で「仮説 5 個」をスライド 1 枚にまとめて配布、議論の起点にする - 検証は 1 ヶ月単位で 1 仮説に絞る (= 同時に複数施策を打つと因果が見えなくなる)
📚 参考文献
- Reforge — Retention Engagement Growth — Reforge, 取得日 2026-05
- Mixpanel — Cohort Analysis Guide — Mixpanel, 取得日 2026-05
サブ記事ナビゲーション
Pillar 記事「HubSpot Starter で Professional 級ダッシュボード」を起点に、シリーズ全 6 本でデータ層から可視化層、分析フレームワークまでを解説しています。
| # | タイトル | 役割 |
|---|---|---|
| #1 (Pillar) | HubSpot Starter を Claude Code で限界突破！上位プランでしかできなかったレポートダッシュボードを再現 | 全体設計 |
| #2 | Apps Script で HubSpot ↔ Google Sheets を月額 $0 で自動同期 | データ層 |
| #3 | Looker Studio で HubSpot ダッシュボードを 30 分で組む | 可視化層 |
| #4 | Stage History を Apps Script 30 行で日次 Snapshot | Professional 専用機能の代替 |
| #5 (本記事) | 月次コホート × 経過日数 × 勝率 — B2B 営業の質を AI で 1 時間設計 | 分析フレームワーク |
| #6 | Kintone と HubSpot の並行運用 — 増分同期で破綻させない | 既存 CRM 統合 |
humbulls の Growth Partner サービス では、Cohort 分析の設計から営業会議の議論支援まで伴走しています。
Cohort 分析のテンプレと打ち手仮説プロンプトを一括でダウンロードしたい方は、BtoB マーケ AI 活用ガイド もご活用ください。
