インバウンドリード育成用テンプレートの使い方
テンプレートのダウンロード
無料でお使いいただけます。会員登録は必要ありません。VBA とGASは使っていないので、マクロが禁止されている環境でもそのまま開けます。Googleスプレッドシートで使う場合は、下のCSVを1つのスプレッドシートにシート名そのままで取り込んでください。
どんなときに使うか
このテンプレートは、Webフォームからの問い合わせ、ホワイトペーパーの資料ダウンロード、ウェビナー申込、無料トライアル申込など、自社に向こうから1件ずつ入ってくるリードを管理するためのものです。想定している利用者は、マーケティング担当者とインサイドセールス担当者です。
開くタイミングは大きく2つあります。1つは、フォーム通知やMAツールの通知を受けて新しいリードを1行追加するとき。もう1つは、毎朝「今日中に初回接触しなければならない行」と「期限を過ぎてしまった行」を確認するときです。インバウンドは反応速度がそのまま成果に効くため、後者の毎朝のチェックがこのテンプレートの中心的な使い方になります。
他のテンプレートとの使い分けは次のとおりです。こちらから電話をかけていく新規開拓は「新規開拓テレアポ用」、展示会で集めた名刺をまとめて取り込むなら「展示会・セミナー名刺フォロー用」を使ってください。本テンプレートは1行=1メールアドレス(1人)の設計で、重複判定もメールアドレスで行うため、企業単位で1行を作る他テンプレートとは行の意味が違います。
シート構成
ブックには7枚のシートが入っています。
| シート名 | 役割 | 最初に何をするか |
|---|---|---|
| 使い方 | 説明 | 一度読む。入力はしない |
| リードリスト | 入力(メイン) | 1行=1リードで追加していく。計算列には触らない |
| 既存顧客マスタ | 参照マスタ | ここに自社の既存顧客一覧を貼り付ける。 貼らないと既存顧客判定が全件「新規」になる |
| 配信停止・お断りリスト | 参照マスタ | ここに自社の配信停止・接触お断りの記録を貼り付け、以後も蓄積する。 行は削除しない |
| 選択肢マスター | 参照 | プルダウンの選択肢一覧。自社用語に変えたいときだけ編集する |
| 設定 | 設定 | 初回対応期限の日数とスコアのしきい値を自社の体制に合わせる |
| 列定義 | 説明 | 全60列(3シート分)の意味・必須かどうか・入力例が載っている。個別の列で迷ったらここを見る |
リードリストは列数が多いため、本ページでは肝になる列だけを扱います。全列の説明はブック内の〈列定義〉シートにあります。
参照マスタを同じブックに入れている理由
既存顧客マスタと配信停止・お断りリストは、本来なら全社共用の別ファイルで持ちたくなるシートです。しかしこのブックでは意図的に同じブック内に同梱しています。Excelの COUNTIF / COUNTIFS は、参照先のブックが閉じているとエラー(#VALUE!)になり、ブックをまたいだ集計系の参照は実質的に運用できないためです。
したがって、既存顧客マスタや配信停止リストを別ファイルに切り出さないでください。切り出すと、既存顧客判定の列はエラーにならず静かに全件「新規」と表示され、配信停止の判定も効かなくなります。気づきにくい壊れ方をするので注意が必要です。全社で1本のNGリストを持っている場合は、そのファイルからコピーして本ブックの〈配信停止・お断りリスト〉に貼り直す運用にしてください。
使い始める手順
最初の30分でここまでやれば、翌日から運用に入れます。
- 〈既存顧客マスタ〉に自社の顧客一覧を貼り付ける。 必須は企業名だけですが、代表メールアドレスも入れておくと、社名の表記が違っても会社のメールドメインで既存顧客を拾えるようになり、精度が上がります。法人番号を入れる場合は、貼り付ける前に列のセル書式を「文字列」に変えてください(数値のままだと先頭のゼロが落ちます)。会社名_照合キー・メール_照合キー・ドメイン_照合キーの3列は計算列なので触りません。
- 既存顧客マスタで赤や黄が付いた行を直す。 法人番号が13桁でない行は赤、同じ会社が二重登録されている行は会社名_照合キーが黄になります。マスタ側が二重だと照合結果が不安定になるため、先に整理しておきます。
- 〈配信停止・お断りリスト〉に既存のNG記録を貼る。 1行=1件の申し出です。NG区分(メールNG/架電NG/DM・郵送NG/全接触NG/一時停止)と申出日は必須です。解除予定日はNG区分が「一時停止」のときだけ入れます。
- 〈設定〉シートの4項目を決める。 既定は初回対応期限が受信日の翌日(1日)、ホット60点、ウォーム35点、育成フォロー間隔30日です。ここを変えるだけで、期限日と色分けの基準が全行に反映されます。
- 〈リードリスト〉にテスト用の1行を入れて動きを確かめる。 リードID・受信日・流入経路・企業名・氏名・メールアドレスを入れると、照合キー3列、加点3列、リードスコア、初回対応期限、重複判定、既存顧客判定、配信可否判定が自動で埋まります。既存顧客マスタに入っている会社のメールアドレスで試すと、行が青くなることを確認できます。確認後はその行を削除します。
- 運用に入る。 フォーム・資料DL・ウェビナー申込が入るたびに1行追加します。入力するのは基本項目と流入コンテンツまでで、緑色の斜体で表示されている計算列には入力しません。
- 毎朝、赤とオレンジの行をつぶす。 赤は「未対応のまま初回対応期限を過ぎた行」、オレンジは「本日が期限の行」です。初回接触できたら初回対応日を入れ、ステータスを「一次対応済」に変えると色が消えます。
- 会話できたらBANTの4列を選ぶ。 予算_BANT・決裁関与_BANT・ニーズ_BANT・導入時期_BANTをプルダウンから選ぶと、リードスコアが再計算され、緑=即架電、黄=メール育成という優先順位が自動で付きます。
- メールを送る前に必ず配信可否判定の列を見る。 濃い赤(送信不可)とオレンジ(要根拠確認)の行は、配信リストから外します。
- 週次・月次で振り返る。 週次は「育成中なのに次回アクション期日が空」の黄をつぶす作業、月次は初回対応リードタイム日数の平均とステータス別件数(別途COUNTIFで集計)の確認です。結果に応じて設定シートのしきい値を調整します。
自動で動く列
次の列はすべて数式が入った計算列です。手入力すると数式が消え、その行だけ判定が止まります。誤って上書きした場合は、同じ列の上下の行からコピーして貼り直してください。数式はデータ500行分(2行目から501行目まで)入っています。501行目以降に貼り足すときは、500行目の計算列をコピーして下方向に貼り付けます。
| 列名 | 何を自動計算するか | どう使うか |
|---|---|---|
| 加点_流入経路 | 流入経路による加点(問い合わせ・無料トライアル30/ウェビナー・セミナー20/資料DL15/メルマガ・一括資料請求5/その他0) | 点数の内訳を説明するときに見る |
| 加点_決裁関与 | 決裁関与_BANTによる加点(決裁者本人30/決裁に関与20/担当者10/不明0) | 同上 |
| 加点_検討度 | 予算・ニーズ・導入時期の加点合計(最大40点) | 同上 |
| リードスコア | 加点3列の単純合計(最大100点) | 60点以上が緑=即架電、35点以上が黄=メール育成 |
| 会社名_照合キー | 企業名から法人格・全角半角・スペースを除いた突合用の社名 | 既存顧客マスタ・お断りリストとの突合に使う。目視確認にも使える |
| メール_照合キー | メールアドレスを小文字・半角に正規化したもの | 重複判定の唯一のキー |
| ドメイン_照合キー | メールアドレスの@以降 | 社名の表記が違っても同じ会社を拾う2本目のキー |
| 初回対応期限 | 受信日+設定シートの初回対応期限_日数 | 毎朝のチェック対象を決める基準日 |
| 初回対応リードタイム日数 | 初回対応日−受信日 | 月次で平均を取り、0〜1日で捌けているか振り返る |
| 重複判定 | 同じメール_照合キーが複数あるとき、最初の行を「主レコード」、以降を「重複(2件目以降)」と表示 | グレーの行は削除せず、主レコードに情報を集約する |
| 既存顧客判定 | 既存顧客マスタと社名キー・メール・ドメインで突合した結果 | 「既存」系の行は新規アプローチせず、既存担当へ回す |
| 配信可否判定 | 停止リスト・配信停止日・オプトイン根拠・メール配信可否を突き合わせた最終判定 | メール配信の可否はこの列だけで判断する |
主要な数式は次のとおりです。実際のファイルでは列記号で書かれているため、列記号と列名を併記します。
リードスコア(Q列)。受信日が空の行では何も表示せず、加点3列(N〜P列)を合計します。
=IF($B2="","",SUM($N2:$P2))
メール_照合キー(S列)。メールアドレス(G列)を半角化・前後空白除去・小文字化して、Tanaka@Sample.CO.JP と tanaka@sample.co.jp を同じものとして扱えるようにします。
=IF($G2="","",LOWER(TRIM(ASC($G2))))
ドメイン_照合キー(T列)。メールアドレスの@以降を取り出します。@が無い不正な値でもエラーにならないよう IFERROR でくるんであります。
=IF($G2="","",IFERROR(LOWER(TRIM(MID(ASC($G2),FIND("@",ASC($G2))+1,255))),""))
重複判定(AH列)。同じメール_照合キー(S列)が2件以上あるとき、その中で自分より上に同じ値があれば「重複(2件目以降)」、なければ「主レコード」と表示します。
=IF($S2="","",IF(COUNTIF($S:$S,$S2)>1,IF(COUNTIF($S$2:$S2,$S2)>1,"重複(2件目以降)","主レコード"),""))
既存顧客判定(AI列)。既存顧客マスタを3段階で見ます。会社名_照合キーが一致すれば「既存」、担当者のメールアドレスが一致すれば「既存(担当者一致)」、メールドメインだけ一致すれば「既存(ドメイン一致)」、どれにも当たらなければ「新規」です。
=IF($R2="","",IF(COUNTIF('既存顧客マスタ'!$E:$E,$R2)>0,"既存",
IF(AND($S2<>"",COUNTIF('既存顧客マスタ'!$F:$F,$S2)>0),"既存(担当者一致)",
IF(AND($T2<>"",COUNTIF('既存顧客マスタ'!$G:$G,$T2)>0),"既存(ドメイン一致)","新規"))))
初回対応期限(V列)。受信日(B列)に設定シートの日数を足すだけです。設定シートの値を変えると全行の期限が一斉に変わります。
=IF($B2="","",$B2+設定!$B$2)
色が付いたときの意味
条件付き書式はすべて「カスタム数式」で書かれています(Excelでは『数式を使用して、書式設定するセルを決定』、Googleスプレッドシートでは『カスタム数式』)。Excelの『重複する値』などのプリセットはGoogleスプレッドシートに無いため使っていません。
リードリスト
| 色が付く場所 | 色 | 条件 | 何をすべきか |
|---|---|---|---|
| 行全体 | 赤 | 受信日があり、初回対応日が空のまま初回対応期限を過ぎている(ステータスが「対象外」「既存顧客へ引継ぎ」の行は除く) | 最優先。今すぐ接触する。接触したら初回対応日を入れる |
| 行全体 | オレンジ | 初回対応日が空で、初回対応期限が今日 | 今日中に初回接触する |
| 行全体 | グレー | 重複判定が「重複(2件目以降)」 | 削除しない。主レコードに情報を集約し、備考に「主レコードへ集約済」と書く |
| 行全体 | 青 | 既存顧客判定が「既存」または「既存(ドメイン一致)」 | 新規アプローチしない。既存顧客マスタの担当営業へ回す |
| リードスコア | 緑 | スコアが設定シートのホットしきい値以上(既定60) | 即架電の対象 |
| リードスコア | 黄 | スコアがウォームしきい値以上ホット未満(既定35〜59) | メール育成の中心。次回アクション期日を入れる |
| 配信可否判定 | 濃い赤 | 判定が「送信不可」で始まる | メール配信リストから外す |
| 配信可否判定 | オレンジ | 判定が「要根拠確認」 | オプトイン根拠を確認して埋めるまで配信しない |
| 次回アクション期日 | オレンジ | 期日を過ぎている(ステータスが「商談化」「対象外」の行は除く) | 期日を入れ直すか、接触する |
| ステータス | 黄 | ステータスが「育成中」なのに次回アクション期日が空で、受信日から設定日数(既定30日)が過ぎた | 次回アクション期日を入れる。追わないなら「対象外」にする |
行全体の色は上から順に評価され、先に当たったものが優先されます。赤(期限超過)が最優先で、次にオレンジ(本日期限)、グレー(重複)、青(既存顧客)の順です。
既存顧客マスタ
| 色が付く場所 | 色 | 条件 | 何をすべきか |
|---|---|---|---|
| 法人番号 | 赤 | 13桁でない | 貼り付け時に数値化されて先頭ゼロが落ちた可能性が高い。セル書式を文字列にして貼り直す |
| 会社名_照合キー | 黄 | 同じ会社がマスタ内に2件以上ある | 重複登録を整理する。放置すると照合結果が不安定になる |
| 行全体 | グレー | 取引ステータスが「解約」 | リードが当たった場合は新規ではなく再獲得として扱う |
配信停止・お断りリスト
| 色が付く場所 | 色 | 条件 | 何をすべきか |
|---|---|---|---|
| 行全体 | 赤 | 有効判定が「有効」 | 現在も有効なNG。接触しない |
| 行全体 | グレー | 有効判定が「解除済」 | 一時停止の期限切れ。再アプローチ可 |
| 解除予定日 | 黄 | NG区分が「一時停止」以外なのに解除予定日が入っている | 入力ミス。恒久NGに解除予定日を入れてはいけないので、解除予定日を消す |
設定シートで調整できること
| 項目 | 既定値 | 変えると何が変わるか |
|---|---|---|
| 初回対応期限_日数 | 1 | 受信日に足して初回対応期限を出す日数。全行の初回対応期限と、赤・オレンジが点灯するタイミングが一斉に変わる。当日対応を徹底するなら0、2営業日体制なら2にする |
| ホットしきい値_スコア | 60 | リードスコアが緑になる下限。即架電の対象をどこまで広げるかを決める |
| ウォームしきい値_スコア | 35 | リードスコアが黄になる下限。メール育成の母数が変わる |
| 育成フォロー間隔_日数 | 30 | 「育成中なのに次回アクション期日が空」の行のステータス列が黄になるまでの日数 |
値を入れるのはB列です。項目名(A列)と説明(C列)は書き換えないでください。数式と条件付き書式はB列のセル位置を直接参照しているため、行の挿入・削除・並べ替えをすると参照がずれます。
なお、スコアの配点そのもの(流入経路ごとの点数、BANTの各選択肢の点数)は設定シートではなく数式の中に直接書かれています。配点を変えたい場合は、加点3列(N・O・P列)の数式を1行目で直して、下方向にコピーしてください。しきい値だけを設定シートに出しているのは、色分けの基準変更という頻度の高い操作を、数式を触らずにできるようにするためです。
重複が出たときの扱い
このテンプレートでは重複行を削除しません。 表示を分けてグレーにするだけです。
同じ人が資料を何度もダウンロードするのは、インバウンドでは正常な行動で、むしろ関心の強さを示すシグナルです。2件目以降の受信日と流入コンテンツには「いつ何に反応したか」という履歴としての価値があるため、残します。
運用は次のようにします。
- 主レコード(最初の行)に集約する。 ステータス・担当者・BANTの4列・同意関連の情報は主レコードに入れます。
- 重複行は「対象外」にしない。 ステータスは触らず、備考に「主レコードへ集約済」と書いて残します。「対象外」にすると、その行が後から見て何だったのか分からなくなります。
- 配信停止日やオプトイン根拠が入っている行は、重複であっても絶対に削除しない。 削除すると配信停止の記録が消え、別の経路から同じアドレスが再登録されたときに再送事故が起きます。
重複の判定キーはメールアドレスだけです。同じ企業から別の人が複数件入るのは重複ではなく正常なので、会社名_照合キーでは重複判定をしていません。企業単位の視点は既存顧客判定(会社名_照合キー+ドメイン_照合キー)側で担保しています。
運用上の注意
計算列を手入力で上書きしない。 加点3列・リードスコア・照合キー3列・初回対応期限・初回対応リードタイム日数・重複判定・既存顧客判定・配信可否判定が計算列です。数式が消えた行は判定が止まり、しかもエラーにならず空白になるだけなので気づきにくくなります。CSVから値を貼り付けるときは、必ず「値の貼り付け」で基本項目の範囲だけに貼ってください。
参照マスタを別ブックに分けない。 前述のとおり、分けた瞬間に既存顧客判定が全件「新規」になります。
受信日に時刻を入れる場合の挙動。 受信日のセル表示形式を yyyy/mm/dd hh:mm にしても数式はそのまま動きますが、期限超過の赤はおよそ1日遅れて点灯します(時刻付きの値はその日の0時より後の時刻を持つため、初回対応期限 < TODAY() の成立が1日ずれます)。分単位で即時対応を管理したい場合は、受信日を日付のみで運用してください。
「既存(担当者一致)」の行には青が付きません。 行全体を青くする条件は「既存」と「既存(ドメイン一致)」の2つで、担当者のメールアドレスが完全一致した「既存(担当者一致)」は対象外になっています。既存顧客判定の列そのものも目で確認してください。
リードスコアは説明できる形で作ってある。 機械学習のような予測モデルではなく、3つの加点の単純合計です。「なぜこのリードがホットなのか」を営業に説明できることを優先しています。逆に言えば、配点が現場の実感と合わなくなったら数式を直す必要があります。月次の振り返りで見てください。
法人番号はリードリストに置いていません。 Webフォームで法人番号を取得できることはまずなく、常に空のままの列を作ると現場で列全体が無視されるようになるためです。法人番号は既存顧客マスタ側にのみ、文字列型で置いてあります。
配信可否判定は安全側に倒してあります。 評価の順番は、停止リスト → 配信停止日 → オプトイン根拠 → メール配信可否です。オプトイン根拠が空欄の行は「送信可」にはならず「要根拠確認」になります。根拠が確認できていないアドレスへ送るより、確認作業が発生するほうが望ましいという判断です。
配信停止の申し出を受けたら、行を削除せず配信停止日を入れるだけ。 これだけで配信可否判定が「送信不可(配信停止)」に変わり、以後の抽出から自動的に外れます。あわせて〈配信停止・お断りリスト〉にも1行追加しておくと、将来別のリードとして同じアドレスが入ってきたときにも止まります。
使っている関数について。 VBAやGASは一切使っておらず、Excelとスプレッドシートの標準関数だけで組んであります。XLOOKUP UNIQUE FILTER IFS DATEDIF TEXTJOIN は、Excel 2019以前やGoogleスプレッドシートとの互換性の問題があるため意図的に使っていません(DATEDIF についてはMicrosoft自身が誤った結果を返す場合があると案内しています)。日数の差は単純な引き算、N日後は足し算で書いてあります。
Googleスプレッドシートで使う場合。 このページのCSV一式にシートごとのCSVがあります。新規スプレッドシートを作り、シート名を元のファイル名と同じ(リードリスト/既存顧客マスタ/配信停止・お断りリスト/選択肢マスター/設定)にしてから各CSVを読み込んでください。シート名が違うとシート間参照の数式が壊れます。 条件付き書式と入力規則はCSVには含まれないため、スプレッドシート側で設定し直すか、xlsxをそのままGoogleドライブにアップロードして変換するほうが確実です。
法令に関する注意
オプトイン根拠・同意取得日・同意取得方法・配信停止日・メール配信可否の5列は、特定電子メール法への対応を意識した構成です。広告宣伝メールは原則オプトインで、例外は法第3条第1項の4類型(本人同意/アドレス通知/取引関係/公表アドレス・営業を営む個人等)に限られるとされており、同意があったことを証する記録の保存も求められます。そのため、1列にまとめず5列に分けています。1列にまとめると「根拠が無いのに配信可になっている行」を発見できなくなるためです。
ただし、ここに書かれている内容は調査に基づく実務上の整理であり、法的助言ではありません。 自社の運用が適法かどうかの最終判断は、個人情報保護委員会および総務省のガイドラインを確認のうえ、必要に応じて専門家にご相談ください。
最終更新: / ListGoo 編集部