休眠顧客・失注顧客 掘り起こし用テンプレートの使い方
テンプレートのダウンロード
無料でお使いいただけます。会員登録は必要ありません。VBA とGASは使っていないので、マクロが禁止されている環境でもそのまま開けます。Googleスプレッドシートで使う場合は、下のCSVを1つのスプレッドシートにシート名そのままで取り込んでください。
どんなときに使うか
過去に取引または商談があり、いまは接点が切れている企業へ再アプローチするときに開くテンプレートです。想定している使い手は、インサイドセールスや営業企画の担当者です。既存の顧客台帳やCRMから「最終接触日」「失注理由」「当時の担当者」を抜き出して貼り付け、月次で回すことを前提に設計しています。
典型的な場面は次のようなものです。
- 四半期の初めに「今月どこを掘り起こすか」を決めたい
- 失注案件が溜まってきたが、どれから当たるべきか判断がつかない
- 解約・商談停止になった先に、商材や価格が変わったタイミングで再提案したい
このテンプレートの中核は、経過日数(TODAY() − 最終接触日)→ 休眠区分の自動判定 → 優先度ランクのルールベース付与という3段の流れです。そのため並べ替えの第一キーは企業名ではなく、経過日数または優先度ランクになります。
他のテンプレートとの使い分けは次のとおりです。接点がゼロの企業に新規で架電するなら「新規開拓テレアポ用」、いま取引が続いている顧客の定期訪問なら「既存顧客ルート営業用」を使ってください。このテンプレートは、その中間にある「かつて接点があったが、いまは切れている」先専用です。再接触の結果「再商談化」まで進んだ行は、このブックではなく商談管理側へ移します。
シート構成
ブックは7シートで構成されています。うち3シートが実務で触るシートです。
| シート名 | 役割 | 最初に何をするか |
|---|---|---|
| 使い方 | テンプレート全体の説明 | 一度読む。入力はしない |
| 休眠リスト | メインの入力シート。1行=1休眠・失注企業 | 顧客台帳・CRMから転記する。ただし後述の2マスタを先に埋めること |
| お断りリスト | 参照マスタ。架電拒否・配信停止の記録を1行=1件で残す | ここに自社のお断り記録を貼る。 空のままだと照合が機能しない |
| 現取引先マスタ | 参照マスタ。現在取引中の企業を1行=1社で貼る | ここに自社の取引中企業データを貼る。 販売管理システムや顧客台帳から転記する |
| 選択肢マスター | 各列のプルダウン候補の一覧 | 選択肢を増減したいときだけ触る |
| 設定 | しきい値の設定値 | 自社の商材サイクルに合わせて数値を調整する |
| 列定義 | 全38列(休眠リスト)の説明一覧 | 列の意味が分からなくなったら参照する |
このページでは肝になる列だけを扱います。全列の説明はブック内の「列定義」シートに入っていますので、個々の列の意味はそちらを参照してください。
参照マスタを同じブックに入れている理由
お断りリストと現取引先マスタは、別ファイルに分けず必ず同じブックに同梱しています。これは設計上の都合ではなく、Excelの制約によるものです。
Excelの COUNTIF / COUNTIFS は、参照先のブックが閉じていると計算できず #VALUE! を返します。お断り照合を別ブックへの参照で書くと、参照先を開いていない限り「接触してはいけない先」が検出されない状態になります。事故防止の列が、ファイルを開いているかどうかで動いたり動かなかったりするのは危険なので、外部ブック参照は一切使わず、照合先を同じブックの中に置きました。
結果として、お断りリストと現取引先マスタは自社データを貼り付ける場所になります。ここが空のままだと、休眠リストの「お断り照合」「現在取引中照合」の2列は常に空欄を返し、接触してはいけない先が素通りします。
使い始める手順
最初の30分で、ここまで進めてください。
- 〈お断りリスト〉に自社のお断り記録を貼り付ける。 企業名・電話番号・メールアドレス・NG区分(架電NG/メールNG/DM・郵送NG/訪問NG/全接触NG/一時停止)を埋めます。NG区分をチャネル別に持っているのは、「架電はNGだがメールは可」という状態が実務で頻繁に起きるためです。1列にまとめないでください。
- 〈現取引先マスタ〉に現在取引中の企業を貼り付ける。 企業名・法人番号・電話番号・取引ステータスを入れます。「本社は休眠だが大阪支店で継続取引」のような事情は備考列に残してください。
- 〈設定〉シートでしきい値を調整する。 初期値は休眠しきい値が180/365/730日です。設備・システムなど更新サイクルの長い商材を扱うなら長期を1095日に上げ、消耗品やBtoC寄りの短サイクル商材なら軽度を90日に下げます。
- 〈休眠リスト〉に顧客台帳・CRMからデータを貼り付ける。 必須なのは顧客ID・企業名・区分・最終接触日です。最終接触日が空欄の行は経過日数も休眠区分も優先度ランクも一切動きませんので、まずこの列を埋め切ってください。法人番号は必ず文字列として貼り付けます。
- 計算列を貼り付けた行数ぶんだけ下へコピーする。 数式はあらかじめ500行目まで入っています。それより多い行数を貼り付けた場合は、照合キー列(会社名_照合キー・電話_照合キー・メール_照合キー)と判定列(法人番号チェック以降の7列)を、最終行まで下方向にコピーしてください。これを忘れると、下の行だけ判定が空欄になります。
- 法人番号チェックが赤くなった行を直す。 法人番号を数値として貼り付けると先頭のゼロが落ちて12桁以下になり、この列がエラーを立てます。該当セルを文字列形式にしてから貼り直してください。
- グレーアウトされた行を当月の対象から外す。 除外フラグ・お断り登録・現在取引中のいずれかが立った行は灰色になります。行は削除せず、灰色のまま残してください。
- 残った行を並べ替える。 第一キーを優先度ランク(A→B→C)、第二キーを経過日数の降順にします。着手は休眠区分「6ヶ月-1年」の層からが基本です。この層はまだ先方の記憶に残っており、最も復活しやすいためです。
- 当月分に再アプローチ予定日と再アプローチ手段を入れる。 メール配信可否が「停止」「不可」の行には、手段として「メール」を選ばないでください(メールアドレスのセルが灰色になっています)。
- 実施したら再接触実施日と再接触結果を入力する。 入力すると、予定日超過のオレンジ色が消えます。折り返し待ちの行は次回アクション日を入れておくと、翌月に拾い直せます。
- 月末に棚卸しする。 再接触結果が「再商談化」の行は商談管理側へ移し、「接触不可」の行は〈お断りリスト〉へ追記します。いずれの場合も、休眠リストからは削除しません。
自動で動く列
次の列はすべて数式が入った計算列です。手入力すると数式が消え、その行の判定が止まります。 値を変えたいときは、元になる入力列(企業名・最終接触日・金額など)か〈設定〉シートの数値を変えてください。
| 列名 | 何を自動計算するか | どう使うか |
|---|---|---|
| 会社名_照合キー | 企業名から法人格(株式会社・(株)・(株)・有限会社・(有))と空白を除き、全角を半角に揃えた突合専用の文字列 | 重複判定と他シート照合はすべてこの列。並べ替えや目視には使わない |
| 電話_照合キー | 電話番号からハイフン(半角・全角・長音記号)を除いた数字列 | 社名の表記が違っても電話一致で同一社として拾う |
| メール_照合キー | メールアドレスを半角・小文字に統一した文字列 | お断りリストのメールNG照合に使う |
| 法人番号チェック | 法人番号が13桁でない行に「法人番号桁数エラー」を立てる | 転記時の桁落ち検出。出たら文字列形式で貼り直す |
| 経過日数 | 最終接触日から今日までの日数 | 並べ替えの第一キー候補。 同一ランク内はこの降順で並べる |
| 休眠区分 | 経過日数を「6ヶ月未満/6ヶ月-1年/1-2年/2年以上」の4段階に分類 | 着手順の判断。6ヶ月-1年が月次の主戦場 |
| 重複チェック | 同じ会社名_照合キーが2件目以降なら「重複(2件目以降)」 | 統合するか両方残すかを目視判断する合図。自動削除はしない |
| お断り照合 | お断りリストに社名・電話・メールのいずれかが有効な状態で載っていないか照合 | 何か入った行には接触しない。優先度ランクも自動で「除外」になる |
| 現在取引中照合 | 現取引先マスタに社名または電話が一致する行がないか照合 | 別部署で取引が再開している先を休眠アプローチから外す |
| 優先度ランク | 金額と休眠期間の2軸でA/B/C/除外を付与 | 並べ替えの第一キー。 Aから着手する |
| (お断りリスト)NG有効判定 | 一時停止で解除予定日を過ぎた行だけ「解除済」にする | 休眠リストのお断り照合は、この列が「有効」の行だけを見る |
主要な数式
実際のファイルに入っている数式です(2行目の例)。
経過日数。DATEDIF は使わず単純な引き算で日数を出しています($H列=最終接触日)。
=IF($H2="","",TODAY()-$H2)
休眠区分。経過日数を、設定シートの3つのしきい値と上から順に比べて4段階に分けています(IFS は使わず IF の入れ子)。
=IF($AG2="","",IF($AG2>=設定!$B$4,"2年以上",IF($AG2>=設定!$B$3,"1-2年",IF($AG2>=設定!$B$2,"6ヶ月-1年","6ヶ月未満"))))
優先度ランク。除外条件を先に4つ潰してから、金額(累計取引金額と当時の提案金額の大きい方)と経過日数でA/B/Cを決めています。
=IF($H2="","",IF($U2="除外","除外",IF($AJ2<>"","除外",IF($AK2<>"","除外",IF($P2="閉鎖・廃業","除外",IF(AND(MAX($M2,$L2)>=設定!$B$5,$AG2<設定!$B$7),"A",IF(MAX($M2,$L2)>=設定!$B$6,"B","C")))))))
MAX を取っているのは、失注先には累計取引金額が存在しない(0になる)ためです。当時の提案金額との大きい方を見ることで、「取引実績はないが大型案件だった失注先」がCランクに沈まないようにしています。
お断り照合。全接触NGを最優先で検出し、次に社名一致、電話一致、メール一致の順に判定します。
=IF($R2="","",IF(COUNTIFS('お断りリスト'!$E:$E,$R2,'お断りリスト'!$D:$D,"全接触NG",'お断りリスト'!$M:$M,"有効")>0,"全接触NG",IF(COUNTIFS('お断りリスト'!$E:$E,$R2,'お断りリスト'!$M:$M,"有効")>0,"お断り登録あり",IF(AND($S2<>"",COUNTIFS('お断りリスト'!$F:$F,$S2,'お断りリスト'!$M:$M,"有効")>0),"お断り(電話一致)",IF(AND($T2<>"",COUNTIFS('お断りリスト'!$G:$G,$T2,'お断りリスト'!$M:$M,"有効")>0),"お断り(メール一致)","")))))
電話・メールの一致判定が AND(キー<>"", COUNTIFS(...)>0) で囲まれているのは、照合キーが空文字のときに COUNTIF が空白セルを数えてしまい、電話番号のない行が全部「お断り」と誤検出されるのを防ぐためです。このガードは外さないでください。
お断りリスト側のNG有効判定。一時停止のうち解除予定日が過ぎたものだけを「解除済」にします。
=IF($A2="","",IF(AND($D2="一時停止",$J2<>"",$J2<TODAY()),"解除済","有効"))
色が付いたときの意味
条件付き書式はすべて「カスタム数式」で書いてあります(Excel では「数式を使用して、書式設定するセルを決定」、Googleスプレッドシートでは「カスタム数式は」)。Excel の「重複する値」プリセットは Googleスプレッドシートに存在しないため使っていません。
休眠リスト
| 色 | 付く範囲 | 条件 | 何をすべきか |
|---|---|---|---|
| グレー | 行全体 | 除外フラグが「除外」/お断り照合が空でない/現在取引中照合が空でない | 当月の対象から外す。行は削除しない。 このルールは最上位に置いてあり、休眠度合いの赤・黄より優先されます |
| 濃い赤 | 休眠区分 | 休眠区分が「2年以上」 | 最も長く放置されている層。架電の前に、社名・所在地・担当者の鮮度確認から始める |
| 赤 | 休眠区分 | 休眠区分が「1-2年」 | 担当者交代が起きている可能性が高い。当時の担当者名指しではなく受付経由を想定する |
| 黄 | 休眠区分 | 休眠区分が「6ヶ月-1年」 | 最も復活しやすい層。月次の主戦場。ここから着手する |
| 緑 | 優先度ランク | 優先度ランクが「A」 | 金額規模が大きく休眠が深すぎない最優先行 |
| オレンジ | 行全体 | 再アプローチ予定日が今日より前で、再接触実施日が空欄 | 着手漏れ。 今日中に実施するか、予定日を引き直す |
| 青 | 行全体 | 再アプローチ予定日が今日以降、設定の日数(初期値7日)以内で、再接触実施日が空欄 | 今週やる分。準備に入る |
| 黄 | 重複チェック | 重複チェックが「重複(2件目以降)」 | 統合するか両方残すかを目視で判断する(次節参照) |
| 赤 | 法人番号 | 法人番号チェックが「法人番号桁数エラー」 | 桁落ち。文字列形式で貼り直す |
| グレー | メールアドレス | メール配信可否が「停止」または「不可」 | このアドレスには送らない。 再アプローチ手段からメールを外す |
お断りリスト
| 色 | 付く範囲 | 条件 | 何をすべきか |
|---|---|---|---|
| 濃い赤 | 行全体 | NG区分が「全接触NG」 | どのチャネルでも接触しない |
| 緑 | 行全体 | NG区分が「一時停止」で、解除予定日が今日以前 | 期限到来。解除してよいか見直す。登録者列で確認先が分かる |
| 黄 | 一時停止_解除予定日 | NG区分が「一時停止」以外なのに解除予定日が入っている | 入力ミス。解除予定日を消すか、NG区分を「一時停止」に直す |
現取引先マスタ
| 色 | 付く範囲 | 条件 | 何をすべきか |
|---|---|---|---|
| 黄 | 直近取引日 | 直近取引日から設定の日数(初期値365日)以上経過 | マスタの鮮度注意。実際には休眠へ移すべき先の可能性がある |
| オレンジ | 行全体 | 取引ステータスが「解約予定」 | 近く取引が切れる。休眠リストへの移管候補 |
設定シートで調整できること
〈設定〉シートのB列の数値を書き換えると、数式を触らずに判定の基準を変えられます。
| 項目 | 初期値 | 変えると何が変わるか |
|---|---|---|
| 休眠しきい値_軽度(日) | 180 | この日数以上で休眠区分が「6ヶ月-1年」になる。消耗品やBtoC寄りの短サイクル商材は90に下げる |
| 休眠しきい値_中度(日) | 365 | この日数以上で「1-2年」になる。年度予算で動く商材は、この線が実質的な区切りになる |
| 休眠しきい値_長期(日) | 730 | この日数以上で「2年以上」になる。設備・システムなど更新サイクルの長い商材は1095に上げる |
| 優先度_金額しきい値A(円) | 1000000 | 累計取引金額と当時の提案金額の大きい方がこの額以上で、Aランクの金額条件を満たす。上げるとAが減る |
| 優先度_金額しきい値B(円) | 300000 | 同じ金額がこの額以上でBランク。満たさなければCランク |
| 優先度_休眠上限A(日) | 730 | 経過日数がこの日数未満のときだけAを付ける。金額が大きくても休眠が深すぎる先はBへ落ちる |
| 再接触予定日_直前アラート(日) | 7 | 予定日がこの日数以内に迫った未着手行を青で表示する。週次の作業量に合わせる |
| マスタ鮮度確認(日) | 365 | 現取引先マスタの直近取引日がこの日数以上古いと「鮮度注意」の黄色になる |
休眠しきい値の3つは、必ず軽度 < 中度 < 長期の順になるようにしてください。順序が逆転すると、休眠区分の判定が意図しない段階で止まります。
重複が出たときの扱い
このテンプレートでは重複行を自動削除しません。 休眠リストの重複は「同じ企業に複数の部署・複数の過去案件がある」正当な重複であることが多く、片方を消すと失注理由や当時の担当者という、掘り起こしの材料そのものが失われるためです。
次の順で扱ってください。
- 重複チェック列が「重複(2件目以降)」を立てた行が黄色で表示されます。まずこれを目で確認します。
- 同一部署・同一案件の二重登録だと確認できたものだけ統合します。統合するときは、備考を先に結合してから古い行を消してください。
- 部署違い・案件違いの重複は両方残し、備考にその旨(「本社案件/大阪支店案件」など)を書きます。
- どうしても Excel の「重複の削除」機能を使う場合は、実行前に必ずシートを別シートへコピーしてバックアップを取り、判定の対象列には会社名_照合キーを指定してください。表示用の企業名を指定すると、「株式会社○○」と「(株)○○」が別物と判定され、重複が残ります。
参照マスタ側の扱いは次のとおりです。
- お断りリストの行は、重複していても削除しません。解除や誤判定の履歴そのものだからです。
- 現取引先マスタの重複は残して構いません。同一社に複数部署が取引しているサインであり、照合には COUNTIF を使っているので1件以上一致すれば判定は成立します。
運用上の注意
計算列を手入力で上書きしない。 照合キー列と判定列10列は数式です。値を直接打ち込むとその行だけ判定が止まり、お断り照合も効かなくなります。見た目では気づけないので、誤って上書きしたら隣の行から数式をコピーして戻してください。
行を増やしたら数式を下へ伸ばす。 数式はデータ500行分(2行目から501行目まで)入っています。501行目以降に貼り付けた行は、計算列が空のままです。並べ替えたときに判定列が空の行が混ざると、お断り登録のある企業がグレーアウトされずに対象に残ります。
2つの参照マスタを空のまま使わない。 お断りリストと現取引先マスタが空だと、お断り照合も現在取引中照合も必ず空欄を返します。数式は正しく動いているように見えるのに、事故防止がまったく効いていない状態になります。これが最も危険な壊れ方です。
お断りリストの行は消さない。 削除すると履歴が失われ、別ファイルから同じ企業・同じアドレスが再登録されたときに再架電・再送信してしまいます。解除したい場合も行は残し、NG区分を「一時停止」にして解除予定日を入れてください。解除予定日が過ぎると NG有効判定が自動で「解除済」になり、休眠リストのお断り照合から外れます。
法人番号は文字列で貼る。 数値として貼ると先頭ゼロが落ちます。また、購入リスト由来のCSVでは法人番号が ="1234567890123" という、Excelで先頭ゼロを落とさないための書式で入っていることがあります。これをそのまま貼ると Excel が数式として解釈するため、値として貼り付け(Excel: 形式を選択して貼り付け → 値/Googleスプレッドシート: 編集 → 特殊貼り付け → 値のみ貼り付け)を使ってください。購入リストから流し込む場合は、先に「購入した営業リストCSV用」テンプレートで取り込んでから転記するのが確実です。
優先度ランクはブラックボックスではない。 スコアリングではなく、(a) 除外フラグ/お断り照合/現在取引中照合/現在の企業状態が「閉鎖・廃業」のいずれかが立てば「除外」、(b) 金額がA基準以上かつ経過日数がA上限未満なら「A」、(c) B基準以上なら「B」、(d) それ以外は「C」という4段のルールで決まります。上長に「なぜこの順番なのか」と聞かれたら、この4行で説明できます。
最終更新日は自動では入りません。 鮮度管理のための列ですが、手で更新する必要があります。月次の棚卸しのタイミングでまとめて入れる運用にしてください。
使っている関数と、使っていない関数。 VBA・GAS は一切使っていません。Excel と Googleスプレッドシートの標準機能だけで動きます。このテンプレートで使っているのは IF / AND / OR / MAX / LEN / TODAY / COUNTIF / COUNTIFS / TRIM / SUBSTITUTE / ASC / LOWER のみです。XLOOKUP・UNIQUE・FILTER・IFS・SWITCH・ARRAYFORMULA・QUERY は、Excel 2019以前やGoogleスプレッドシートとの互換性のため使っていません。DATEDIF は、Microsoft 自身が誤った結果を返す場合があると案内しているため使わず、経過日数は単純な引き算で出しています。なお COUNTIFS は、名前に「IFS」という文字列を含みますが、不使用の IFS 関数とは別の関数です。
メール配信の可否について。 メール配信可否列は、特定電子メール法のオプトイン根拠を1列で表現するためのものです。広告宣伝メールは原則オプトインで、例外は限られた類型だけとされています。休眠・失注先の多くは「取引関係にある者」に当たりますが、商談のみで取引に至っていない失注先は別の根拠が必要になるため、選択肢を「可(取引関係)」と「可(同意取得済)」に分けてあります。根拠が示せない先は「不可」を選べば、メールアドレスのセルが灰色になり、送信先から自動的に落ちます。
お断りリストの位置づけについて。 「断られたら再勧誘しない」というルールの法的根拠としてよく挙げられる特定商取引法の再勧誘禁止は、電話勧誘販売、つまり主にBtoC取引を対象としたものです。純粋なBtoB営業にそのまま適用されるわけではありません。このテンプレートでお断りリストを標準装備しているのは、法令上の義務だからではなく、運用上の定石として、また再架電・再送信という事故を構造的に防ぐための仕組みとしてです。
以上は調査結果に基づく実務上の整理であり、法的助言ではありません。個人情報や広告メールの取り扱いについての最終的な判断は、個人情報保護委員会のガイドラインおよび専門家にご確認ください。
最終更新: / ListGoo 編集部