ModelMonkey Logo
アナリティクス中級読了約 2 分

営業パイプラインCRMでステージ後退を追跡する方法

パイプラインの案件が後退するタイミングを検知し、Google Sheetsでフラグを立てて、営業部長がすぐに活用できる週次サマリーを構築する方法を解説します。

このガイドでは、CRMのステージ履歴エクスポートを使ってGoogle Sheetsでステージ後退を検知し、後退した動きすべてにフォーミュラでフラグを立て、月曜日の朝礼で営業部長がすぐに確認できるダッシュボードにまとめる方法を解説します。案件が「提案」から「ヒアリング」に戻るのは、追うべきシグナルです。しかし多くのCRMダッシュボードは、それを自動的に表示してくれません。

必要なもの

  • CRMのステージ履歴エクスポート（HubSpot Pipeline Activity、Salesforce OpportunityFieldHistory、または同等のもの）。最低限必要な列：案件ID、ステージ名、ステージ変更日、担当者／オーナー
  • そのエクスポートを読み込んだGoogle Sheets — 履歴の長さやチームの活動量によって、現実的には5,000〜8万行程度
  • VLOOKUP、QUERY、手動の範囲ソートを使いこなせること
  • 正しい順序で定義されたパイプラインステージのリスト（担当者が「ヒアリング」に5種類の名前を使っている場合は、先にそちらを統一してください）

ステップバイステップ

1

CRMから正しいデータをエクスポートする

標準的なCRMの案件レポートは、案件ごとに1行表示され、各案件が現在どのステージにあるかを示します。今回の目的にはこれでは不十分です。必要なのは、ステージ変更ごとに1行が記録された履歴ログです。案件がいつ、どの方向に動いたかがすべて記録されている必要があります。この2種類のエクスポートはまったく異なるものなので、作業を始める前に正しい方を取得しているか確認してください。

HubSpotでは「レポート > セールス > 案件ステージ履歴」から取得できます。Salesforceでは、Field = 'StageName' でフィルタした OpportunityFieldHistory オブジェクトをクエリしてください。Pipedriveでは「レポート」内の「パイプライン変更ログ」をエクスポートします。主要なCRMにはすべてこのデータが存在しますが、アクセス方法はサービスによって異なります。

  • 最低限必要な列をCSVでダウンロードする：案件ID、ステージ名、ステージ変更日、案件オーナー
  • 可能であれば案件金額も含めてください — 1,800万円の案件の後退と、30万円の案件の後退では話の重みがまったく異なります
  • トレンド分析には最低6ヶ月分の履歴をエクスポートしてください。1週間分だけではパターンはほとんど見えません
  • 担当者10名以上のチームで6ヶ月分のデータを扱う場合、1万5,000〜6万行程度になることを想定してください

Pro Tip

エクスポートに「移動元ステージ」と「移動先ステージ」が別々の列として含まれている場合は、そのフォーマットを使用してください。後述の2ステップを省略できます。イベントごとに現在のステージしか取得できない場合は、ステップ4で前のステージを自分で計算します。
2

ステージ名を確認・クレンジングする

何かを作り始める前に、ステージ列をピボットしてすべてのユニーク値を確認してください。このステップで、「デモ」「製品デモ」「デモ／プレゼンテーション」がすべて同じステージを担当者によって異なる形で入力されたものだと気づくことになります。このまま進めると、比較フォーミュラはこれらを3つの別ステージとして扱い、後退の3分の2を見逃してしまいます。

A列に「元の名前」、B列に「正規名」を記入した2列のクレンジングシートを作成してください。次に、元データの隣にクレンジング用の列を追加します：

  • =IFERROR(VLOOKUP(TRIM(C2), Cleanup!$A:$B, 2, FALSE), C2) — IFERRORにより、すでに正しく一致する名前はそのまま通過します
  • TRIMは必須です。CRMのエクスポートにはセル内で見えない先頭・末尾のスペースが含まれることが多く、完全一致を破壊します
  • =UNIQUE(C2:C) をスクラッチ列で使用して、クレンジングテーブルを作成する前にすべてのユニークなステージ名を抽出してください
  • クレンジング列の内容が正しく見えたら、コピーして「形式を選択して貼り付け > 値のみ」でステージ列に上書きし、ヘルパー列を削除してください
3

ステージ順序のルックアップテーブルを作成する

ステージ後退は、「前進」を数値で定義してから初めて意味を持ちます。StageOrder という新しいシートを作成し、「ステージ」と「順序」の2列を設けてください。すべての正規ステージ名を列挙し、それぞれに整数を割り当てます。この数値の方向が、後退ロジックの判定基準になります。

ステージ順序
Prospecting1
Qualification2
Discovery3
Demo4
Proposal5
Negotiation6
Closed Won7
Closed Lost99

Closed Lostには8ではなく99を割り当てます。8にすると、Closed Lostから交渉ステージに戻った案件が後退として検知されてしまいます。これは実際には「再オープン」であり、別途追跡すべき事象です。

  • 小数ではなく整数のみを使用してください。比較を明確にするためです
  • パイプラインに分岐がある場合（例：「技術評価」が「提案」と並行して進む場合）、並列ステージには同じ順序番号を割り当て、レーン間の移動を後退とみなすかどうかチームで決定してください
  • 年度途中でリネームされたステージの履歴データを失わないよう、StageOrderに「ステータス」列（有効／廃止）を追加してください
  • CRM管理者が新しいステージを追加した際には、このテーブルも更新してください。ステージが登録されていない場合、IFERRORで捕捉されたVLOOKUPエラーが発生し、修正が必要であることを知らせてくれます

Pro Tip

StageOrderシートをロックして、担当者やCRM管理者が分析中に誤って数値を編集できないようにしてください。「データ > シートと範囲の保護」で設定します。
4

データをソートしてステージ順序番号を付与する

ここがすべての要となるステップです。行を案件IDの昇順でソートし、さらに変更日の昇順でソートする必要があります。これにより、各案件のイベントが時系列順に並び、同じ案件内で各行を直前の行と比較できるようになります。ソートを誤ると、すべての後退フラグが無意味になります。

Google Sheetsで：「データ > 範囲を並べ替え > 案件ID（A→Z）」でソートし、「変更日（A→Z）」を第2ソート条件として追加します。4万行の場合、約10〜15秒かかります。

次に、データに3つのヘルパー列を追加します：

G列（Stage_Order）— 各行のステージの数値順序：

=IFERROR(VLOOKUP(C2, StageOrder!$A:$B, 2, FALSE), 0)

H列（Prev_Stage_Name）— 同じ案件の直前行のステージ名：

=IF(A2=A1, C1, "")

I列（Prev_Stage_Order）— 同じ案件チェックを行った数値順序：

=IF(A2=A1, G1, "")
  • 3つのフォーミュラを2行目からデータの最終行までドラッグしてコピーしてください
  • 案件IDが変わる行（新しい案件の最初のイベント）はHとIが空白を返します。これは正しい動作です。比較すべき前のステージが存在しないためです
  • Gが0を返す行は認識されていないステージ名を意味します。0をフィルタして、ステップ2のクレンジングテーブルを確認してください

Pro Tip

5万行以上では、これらのヘルパー列は編集のたびに20〜40秒の再計算が発生します。初期セットアップ後は、G列からI列をコピーして「形式を選択して貼り付け > 値のみ」で固定してください。毎週新しいデータを読み込む際に再実行します。
5

後退イベントにフラグを立てる

データがソートされ、数値のステージ順序が揃ったら、後退フラグは2つの比較に絞られます。比較すべき前のステージがあるか（I列が空白でないか）、現在の順序番号が前の順序番号より小さいか。順序番号が小さいということは、案件が後退したことを意味します。

J列（Regression_Flag）：

=IF(I2="", "First Entry", IF(G2<I2, "Regression", IF(G2=I2, "No Change", "Forward")))

K列（Regression_Path）— 部長向けのテーブルで読みやすい、具体的なステージ遷移：

=IF(J2="Regression", H2&" → "&C2, "")

これにより「Proposal → Discovery」「Negotiation → Demo」といった遷移が表示されます。営業部長が掘り下げたいトランジションがここに集まります。

  • J列を「Regression」でフィルタして、大規模なデータで信頼する前に10〜15行を目視確認してください
  • 「No Change」は、担当者がステージ以外のフィールド（商談完了日や金額など）を編集してCRMに保存した際に表示されます。これはノイズであり、後退ではありません
  • 同じステージを2回通る案件に注意してください。「Forward」→「No Change」→「Regression」の順で表示され、CRM管理者に報告すべきデータ入力の問題を示していることが多いです
  • 「First Entry」行はI列が空白のため、下流クエリから自動的に除外されます。IFERRORは不要です
6

QUERYで後退サマリーを作成する

J列にすべての後退フラグが立てられたら、オペレーション報告に必要な集計は3つです。後退が最も多い担当者、最も頻繁に発生するステージ遷移、月次トレンドは改善しているか悪化しているか。QUERYはこれらの集計を8万行以上でも2秒以内に処理します。ここではCOUNTIFSを使わないでください。他の計算が動いているシートでは、3万行を超えると動作が重くなります。

Regression_Summary というシートを作成し、以下の3つのクエリから始めてください：

担当者別後退数：

=QUERY(Data!A:K, "SELECT E, COUNT(A) WHERE J='Regression' GROUP BY E ORDER BY COUNT(A) DESC LABEL E '担当者', COUNT(A) '後退数'", 1)

最も多い後退パス：

=QUERY(Data!A:K, "SELECT K, COUNT(A) WHERE J='Regression' GROUP BY K ORDER BY COUNT(A) DESC LIMIT 10 LABEL K '後退パス', COUNT(A) '件数'", 1)

月次トレンド：

=QUERY(Data!A:K, "SELECT YEAR(D), MONTH(D), COUNT(A) WHERE J='Regression' GROUP BY YEAR(D), MONTH(D) ORDER BY YEAR(D) DESC, MONTH(D) DESC LABEL YEAR(D) '年', MONTH(D) '月', COUNT(A) '後退数'", 1)
  • Data!A:K を実際のシート名とタブ名に置き換えてください
  • QUERYの日付関数YEAR()とMONTH()は、日付列がGoogle Sheetsの真の日付値である必要があります。テキスト文字列では機能しません。Salesforceのエクスポートからテキストとして取り込まれた場合は、=DATEVALUE(D2) で変換してからクエリを実行してください
  • 担当者テーブルの隣に後退率の列を追加してください。担当者ごとの後退数をステージイベントの総数で割ると、件数だけより正確な実態が見えます（200案件で20件の後退も、30案件で3件の後退も後退率は同じ10%ですが、生の件数だけ見ると印象が大きく異なります）
  • Changed_Date列に「2024-01-15」「1/15/24」「15 Jan 2024」といった複数のフォーマットが混在している場合（複数のCRMリージョンからエクスポートした際によく発生します）、QUERYで日付フィルタリングを行う前に日付の正規化が必要です

Pro Tip

日付フォーマットの混在は別のクレンジング問題です。IFERROR、DATEVALUE、REGEXEXTRACTの組み合わせで多くのフォーマットを解析できますが、初めて混在列に直面する場合は30〜60分を見込んでください。
7

部長向けダッシュボードタブを作成する

サマリーシートは分析用です。ダッシュボードタブは月曜日の朝礼でプロジェクターに映すものです。3つのパネルに絞ってください。ヘッドライン数値、担当者別内訳、主要な後退パス。これ以上追加すると、部長が見なくなるスプレッドシートになります。

ダッシュボードが自動更新されるには、QUERYフォーミュラがライブデータから取得する必要があります。ステップ4のヘルパー列のように値を固定しないでください。

  • パネル1（ヘッドライン数値）： 日付範囲を指定した2つのQUERYカウント（今週と先週）と、差分を示すシンプルな引き算セル。後退が増えていれば赤、減っていれば緑の条件付き書式を設定してください。
  • パネル2（今四半期の担当者テーブル）： ステップ6の担当者QUERYを使用し、DATE(YEAR(TODAY()), MONTH(TODAY())-MOD(MONTH(TODAY())-1, 3), 1) を下限日付としてフィルタしてください。LIMIT 5 でテーブルを5行に固定してサイズを一定に保ちます。
  • パネル3（主要な後退パス）： 上位5件に絞ったパスQUERY。各パスが後退全体の何パーセントを占めるかの列も手動で追加する価値があります。
  • 「最終更新日時」セルに =TEXT(NOW(), "YYYY年M月D日") を入れておくと、ファイルを開いた人がデータが最新かどうかをすぐに確認できます
  • 「データ > シートと範囲の保護」でダッシュボードタブの編集をロックしてください。誤って1キー押してしまうだけでQUERYフォーミュラが壊れ、数値の更新が止まっても原因がすぐにはわかりません

まとめ

ここで構築したのは、あらゆるCRMエクスポートに対応する後退検知レイヤーです。ステージ順序のマッピング、案件IDチェックで保護された行ごとの比較、そして8万行を超えても速度が落ちないQUERY集計。部長向けダッシュボードは、「パイプラインの健全性は改善しているか？」という問いに対して、「調べてみます」ではなく根拠のある答えを提供します。

1ヶ月ほど運用した後、多くのチームが次に尋ねるのは「週次のデータ更新を自動化できないか」という問いです。そこが手作業プロセスの限界です。検知ロジック自体は堅牢ですが、毎週エクスポートのダウンロード、クレンジング、ソート、再読み込みを誰かが行う必要があります。ModelMonkeyのプランを選ぶ — Google SheetsとExcelの両方に対応しています。

よくある質問

ステージ後退と案件の再オープンの違いは何ですか？

ステージ後退は、アクティブなパイプライン内で案件が後退すること（提案からヒアリングへの移動など）です。再オープンは、失注としてクローズされた案件がアクティブなステージに戻ることを指します。StageOrderテーブルでClosed Lostに99を割り当てることで、再オープンが後退として表示されるのを防ぎます。アクティブなステージ（順序1〜6）はすべて99より数値が小さいため、フォーミュラはこれを「Regression」ではなく「Forward」と読み取ります。再オープンは、Prev_Stage_Nameが「Closed Lost」に等しい行をフィルタして別途追跡してください。

CRMがステージ履歴ではなく現在のステージしかエクスポートできません。後退を検知できますか？

はい、ただし異なるタイミングで取得した2つのスナップショットが必要です。今週の全案件リストをエクスポートし、来週も同様にエクスポートします。案件IDをキーに、先週のステージを今週のエクスポートにVLOOKUPで紐付けます。現在のステージの順序が前回スナップショットの順序より低い場合、それが後退です。ただし、2回のエクスポート日時の間に発生した後退しか検知できません。同じ週に複数回後退した場合は、1つのシグナルに集約されます。

ステージをスキップして前進した後に後退した案件はどう処理されますか？

ステップ5の行ごとの比較は、最初のイベントではなく直前のイベントと比較するため、これを正しく処理します。Prospecting→Demo→Proposal→Discoveryと移動した案件は、最後の移動をProposal（順序5）からDiscovery（順序3）への後退として正しくフラグ立てします。

サマリーテーブルにCOUNTIFSではなくQUERYを使う理由は？

5,000行以下ではCOUNTIFSで問題ありません。3万行を超えると、複数条件のCOUNTIFSは再計算のたびにすべてのセルの組み合わせを評価します。他のフォーミュラが動いているシートでは、再計算に60秒以上かかることがあります。QUERYはSQLライクなエンジンを使用して集計を大幅に高速化します。8万行の担当者別内訳でも3秒以内に完了します。シートの動作が重くなってきたら、COUNTIFSが原因である可能性が高いです。

CRM管理者が新しいパイプラインステージを追加した場合はどうなりますか？

すぐにStageOrderテーブルに新しいステージと順序番号を追加してください。追加前の新しいステージ名を含む履歴行は、VLOOKUPから0を返します（IFERRORで捕捉され、フラグとして表示されます）。StageOrderに行を追加すると、次の再計算時にそれらのゼロが正しく解決されます。難しいのは、既存のステージ間に新しいステージが挿入される場合です。例えば「Technical Evaluation」をDemo（順序4）とProposal（順序5）の間に追加するケースでは、相対順序を維持するためにそれ以降のすべてのステージを番号付けし直す必要があります。