見出し画像

営業担当の変更作業を自動化！仕組みから実装まで手順を徹底解説

アタラ株式会社 Official note

こんにちは、アタラのデータコンサルタントの川田です。

営業現場では、顧客リストを住所でエリアごとに分類して、営業担当を割り当てることがよくあります。しかし、営業メンバーの入退社や異動、営業戦略の変更などのタイミングで、エリアごとの割り当てを変更したい or しなければならない場面が発生します。

この作業は営業の管理職の方が行うことが多いと思いますが、Excelでソートやフィルター機能を使って手作業でやっているのではないでしょうか。当然ながらこの方法だと、手間がかかる上に人為的なミスが発生しやすいという問題があります。

この記事では、エリアと営業担当の組み合わせをマスタで用意しておき、顧客リストとマスタを結合することで、顧客リストに営業担当が自動で紐づけされる方法を解説します。自動化するまでは大変ですが、一度やってしまえば手作業が大幅に削減し、正確な営業担当の割り当てを実現できます。

なお、この方法は私が事業会社にいたときに実装した方法で、上記のマスタのことを「テリ表」と呼んでいました。各営業の担当エリア（テリトリ）を一覧化したもの（＝表）で、テリ表です。本記事でもこの呼び方を使って、「テリ表を適用する」、「顧客リストとテリ表を結合する」のように表記します。


テリトリ、営業担当とは？

手順の解説の前に、処理がイメージしやすいように図で全体像を解説します。併せて用語の定義も行います。

例えば、下のような地図（東京都新宿区の一部を模式図にしたもの）を考えます。

ある企業の営業組織では、グレー・青・赤・黄の範囲を営業対象としています。そして各色の範囲を営業メンバーAAA～DDDの4名が各々担当しています。この、各営業が担当している範囲を「テリトリ」と呼びます。

上図は現在の営業の担当エリアですが、営業メンバーの入退社や異動（例1）、営業戦略の変更（例2、例3）などによって、この割り振りを変更しなければならないとします。 以下に図で表現してみました。

営業担当の変更をExcelで対応すると何が起こるか

業界や企業文化によって違いはあると思いますが、営業の現場では顧客リストの管理をExcelで行う企業が多いと思います。SFA（Sales Force Automation）、CRM（Customer Relationship Management）ツールを導入していても、現場では「やっぱりExcelだよね」という企業も多いのではないでしょうか。

例として下のような顧客リストを考えます。

Excelの顧客リストで、営業担当の変更を適用するとしたら、おそらく下の流れになると思います。

  1. フィルター機能で、住所に特定のワード（新宿区新宿、渋谷区、など）を含んでいるものを抽出

  2. 抽出されたリストの「担当者」の項目を新しい担当者情報で上書き

  3. 1~2を必要な分だけ繰り返す

もしかしたらこの作業のほかに、

  • 変更対象にはフラグを付け、作業が完了したらフラグを「未→済」に変更

  • 住所でソートして、目検で担当者情報を上書き

なども併せて行うことも考えられます。

リストが数百件程度しかなく、年に1〜2回程度だったら、我慢はしつつもできるかもしれません。しかしリストが数千件以上で四半期（もしくは毎月、さらには適宜など）ごとにやらなければならないとしたら、手作業で行うのは避けたいですよね。

作業量も問題ですが、作業の漏れや間違いがあったら、顧客への対応不備や大事な営業機会の損失につながってしまいます。

また、上記の懸念から、「営業担当の変更はできるだけやらないでおこう」となってしまったら、付随的な業務が本来の生産的な業務に悪影響を与える典型的な例になってしまいます。

以上をまとめると、手作業による営業担当の変更には、

  • （少なくない）作業時間の発生

  • 漏れ・誤りの原因

  • 柔軟な戦略変更を阻害

といった問題がつきまとうと言えます。

テリ表適用の自動化手順

こうした課題を解決するのが、作業の「自動化」です。「手作業を自動化させて、楽したい！」はもちろん、柔軟に営業担当の変更をできるようにして、フレキシブルに営業戦略を実行するための自動化手順を解説します。

はじめに全体の流れを示します。

  1. 顧客リスト、テリ表の作成

  2. テリ表に優先順位づけ

  3. テーブルの結合

  4. 担当者決定

図で示すと以下の通りです。

私がこの取り組みをしたときはSQL（データベースを操作するための言語で、データの編集や検索を行うためのコンピュータ言語の一つ）をメインに、補助的にいくつかツールを使っていました。本記事では、「営業担当変更に困っていてどうにかしたい！」と思っている方にも取り組めるように、

  • できるだけExcelで

  • Excelではどうしてもできないところだけ、BigQueryを使用して

解説します（私のExcelスキルではできなかっただけなので、もしかすると全てExcelで実装できるかもしれません）。ちなみに、上のスライド1枚目の部分はExcelを、2枚目の処理でBigQueryを使います。

BigQueryはGoogleから提供されているツールで、Google スプレッドシートを入力として使えて、操作をSQLで記述できます。無料でも使用できる（使用量が増える場合は有料となります）のでまずは試してみたい方にはおススメです。

1. 顧客リスト、テリ表の作成

顧客リストは図のように、「取引先」「リスト住所」の2カラムからなるテーブルを用意します。Excelで作ってもいいですし、最終的にGoogle スプレッドシートに入れるので、最初からそちらで作成しても構いません。

注意点として、「リスト住所」の書式は

  • 都道府県から開始

  • 数字は半角

  • 丁目、番地、番などは省略して、-（半角ハイフン）にする

にしておきます。

テリ表は「テリ住所」「担当者」からなるテーブルを用意します（こちらも最終的にGoogle スプレッドシートにする必要があります）。「テリ住所」の書式も同様の注意が必要です。

2. テリ表に優先順位づけ

顧客リストとテリ表を紐づける前に、テリ表に優先順位をつける必要があります。優先順位とは、「このリスト住所には、このテリ住所を紐づけるべき」と判断する際の判断材料になる情報のことです。ここがこの手法の一番わかりづらいところなので、手順の解説の前に、なぜこれが必要なのかを説明します。

上の図の顧客リストとテリ表を再度見てみましょう。

顧客リストのアタラ合同会社の住所は「東京都新宿区新宿1-36-2」です。テリ表の中から、この住所に最も適合する行（=担当者）はどの行になるでしょうか？

「東京都新宿区新宿1、CCC」ですよね。

人が考えると、途中の思考過程を省いて「これだ！」とわかってしまいますが、これをコンピュータに任せようとすると厳密かつ論理的に指示しなければなりません。

「東京都新宿区、AAA」でも「東京都新宿区新宿、BBB」でも間違っているとは言えないです。けど、より厳密に/細かい粒度で指定しているのは「東京都新宿区新宿1、CCC」です。

人間だったら瞬時に判断できてしまうことが、コンピュータではそうもいかないことの顕著な例かもしれません。

これをコンピュータにもできるように手順化したものが以下です。

住所を前方一致検索

この手法に欠かせないのが前方一致検索という文字列検索方法です。図中にもありますが、前方一致検索とは、「指定した検索キーワードで始まる」文字列を探す検索方法です。検索ワードが「ABC」だったら、下記の結果になります。
　ABCDE　→　前方一致〇
　DEABC　→　前方一致×（後方一致〇）
　ABC　　→　前方一致〇（完全一致〇かつ後方一致）
　DABCD　→　前方一致×（部分一致〇）

これを使って、テリ表に対して図のような処理を行います。

「テリ住所」列を参照して、全ての住所パターンに対して前方一致で重複する数がいくつかあるかを求めます。テリ表の最初の状態に対してこの操作を行うと

  • 「東京都新宿区」は7個の重複

  • 「東京都新宿区四谷」は3個の重複

になります。

Excelで前方一致検索を書くと

※下の図を見て、何をやっているかわかる方はこのセクションは飛ばしてしまって構いません。

これをExcelで実現するには、以下のような数式を書きます。

使っている機能は

  • セル、範囲の絶対参照　（$A$2:$A$9、$A2）

  • 文字列結合（&）

  • ワイルドカード（”*”）

  • COUNTIF関数

です。

各機能を紹介するととんでもない文字数になるので概略だけ記載します。

●セル、範囲の絶対参照　（$A$2:$A$9、$A2）
絶対参照は、「セルがどこにあっても、この場所を参照する」という参照方法です。これだけだと何言ってるのかよくわからないですが、対の概念である相対参照を理解すると分かりやすいです。相対参照は「ここから北へ10歩、東へ3歩」 のように、「ここから」という相対的な参照方法。絶対参照は「シートの左から2番目、上から5番目のセル」のように、今いる場所に関係なく場所を指定する方法です。絶対参照は、同じような計算式を連続したセルにコピーして使いたい時によく使われます。

●文字列結合（&）
文字列結合は複数の文字を連結します。”ABC”&”DEF”なら”ABCDEF”になります。セルとの結合もできるので、A10セルに”123”、B10セルに”456”が入っていて、A10&B10と書いたら”123456”になります。

●ワイルドカード
ワイルドカードは、2つの文字列が部分的に一致しているかを判定するときに使います。任意の長さの文字列を表すには*（アスタリスク）、任意の1文字を表すには?を使います。

●COUNTIF関数
COUNTIF関数は、条件に合致するセルが範囲内にいくつあるか数える関数です。

この数式を言葉で表すと、

  1. 紫のセル（$A2）と”*”を連結して、「『$A2セルに入ってる文字列』から始まる文字列」を作る。

  2. オレンジの範囲内に、1と一致する（$A2と前方一致する）文字列がいくつあるかを数える。

となります。

前方一致検索を繰り返して、優先順位を決定

一度「前方一致数」を計算したら、下の図のようにテリ表を分割します。

この工程を、「前方一致数」に1だけが出現する状態になるまで繰り返します。これは図で見た方が理解しやすいので、下図を参照してください。

右下のテーブルが完成したテリ表です。

「優先」が高い（数字が小さい）ほど粒度が細かく、低い（数字が大きい）ほど粒度は粗くなります。後工程でこの「優先」を使って、顧客リストの結合を行います。

3. テーブルの結合、4.担当者決定

テリ表ができあがったら顧客リストとの結合を行います。ここでは冒頭で述べたように、Excelだけではできない（私のスキルではできなかった）処理になるので、 BigQueryを使った処理手順を紹介します。

手順の解説の前に、テーブルの結合、担当者決定の作業後にできあがるものを確認してみましょう。先ほど例にあげた 「東京都新宿区新宿1-36-2」にテリ表を適用させることを考えます。適合しそうな候補は、下の3つでした。

「東京都新宿区、AAA、4」
「東京都新宿区新宿、BBB、2」
「東京都新宿区新宿1、CCC、1」

下記には、先程の工程で計算した「優先」も併せて記載しています。このように列挙されていれば、「優先」が最も小さいものを選べば良いというのがすぐわかります。

この工程を全ての顧客リストの行に対して行えば、全ての取引先の担当者が計算できます。 

BigQueryでテーブルの結合～担当者決定を実装

テーブルの結合～担当者決定の部分はExcelでは処理できないので、BigQueryを使います。なので、ここから急に話がとっつきづらくなるかもしれません。画面キャプチャ付きで解説しますので、この方法を試してみたいという方は、少しだけ頑張って挑戦してみてください。なお、以下の解説はGoogle アカウントを持っている前提で話を進めます。

A. BigQueryの使用準備

※既にBigQueryを使用したことがある方は読み飛ばしてください。

以下をクリックして、Google Cloudを開きます。

画面右上の「コンソール」をクリックします。

利用規約を確認してチェックを入れたら、「同意して続行」をクリックします。

画面左側の「BigQuery」>「SQL ワークスペース」をクリックします。

画面右の「プロジェクトを作成」をクリックします。

各項目を適宜設定して「作成」をクリックします。

これでBigQueryを使う準備は完了です。

B. 顧客リスト、テリ表のインポート

BigQueryで処理を行うため、テーブルの取り込みを行います。顧客リストとテリ表を、Google スプレッドシートの二つのファイルに分けて用意します。BigQueryへのGoogle スプレッドシートのインポートは、ファイルの先頭シートしか選択できないのでご注意ください。

インポート作業の時に下記のサイトがとても参考になりました。この場を借りてお礼申し上げます。

上記サイトから、注意点だけ抜粋してご紹介します。

  1. 参照できるのはファイル内の最初のタブだけ

  2. スキーマの変更はフィールドの追加だけ

  3. Google スプレッドシートのヘッダは無視される

顧客リスト

テリ表

同じシートを作れるように、テキストを下記に掲載します。

【顧客リスト】
取引先 リスト住所
ミライ株式会社 東京都新宿区四谷1-2-3
タイム株式会社 東京都新宿区四谷坂町1
ムカシ株式会社 東京都新宿区四谷坂町4-5-6
アタラ合同会社 東京都新宿区新宿1-36-2
フルイ株式会社 東京都新宿区新宿2-3-4
モダン株式会社 東京都新宿区新宿3-4-5
ナウ株式会社 東京都新宿区西新宿横浜市西区北幸
イマ株式会社 東京都渋谷区道玄坂
【テリ表】
テリ住所 担当者 優先
東京都新宿区新宿1 CCC 1
東京都新宿区新宿2 DDD 1
東京都新宿区四谷坂町1 GGG 1
東京都渋谷区 HHH 1
東京都新宿区新宿 BBB 2
東京都新宿区四谷坂町 FFF 2
東京都新宿区四谷 EEE 3
東京都新宿区 AAA 4

すぐ後の工程で、各シートのURLを入力するので、コピーしておきましょう。

「データを追加する」をクリックします。

「Google ドライブ」をクリックします。

テーブル作成の各種設定を行います。図の下に各項目について簡単な説明を記載します。

「ドライブのURLを選択」
先ほどコピーした、顧客リストのURLを入力します。

「ファイル形式」
Google スプレッドシートを選択

「データセット」
データセットとはいくつかのデータ（=テーブル）の集まりと理解しておきましょう。
はじめにデータセットを作成する必要があります（ファイルをコピー/移動する前にフォルダを作るイメージです）。
空欄をクリックすると下のメッセージが出現するので、「新しいデータセットの作成」をクリックします。

続いて下のポップアップが出現します。「データセットID」を入力するのですが、テリ表関連のデータの集まりとなるので、terihyou と入力します。最後に「データセット作成」をクリックします。

元の画面に戻ってきますので、先ほど作成したデータセットID（terihyou）を選択します。

「テーブル」
顧客リストのテーブル名として、list と入力します。

「スキーマ」
顧客リストのカラム名（列名）をBigQuery内でどう定義するかを設定します。
BigQueryではカラム名に日本語（全角文字）が使えないので、ここでは
　　取引先名　　→　company
　　リスト住所　→　addr
とします。

以上を設定したら、「テーブルを作成」をクリックします。これで顧客リストのインポートが完了です。

続いて、テリ表のインポートに進みます。先ほどと違う箇所のみ赤四角で示します。特に注意が必要となるのが、rank（テリ表の「優先」カラム）のタイプをINTEGERにするところです。他の文字は文字列なのでSTRINGを指定しますが、rankは数字（整数）なので、INTEGERを指定します。

正しくインポートできていれば下記のように表示されているはずです。もし表示されていなければブラウザをリロード（Windowsの場合はF5キーを押下）してみましょう。

正常に取り込めたかを確認するには、「クエリを新規作成」をクリックします。


クエリのエディタタブが開きます。

下記のようにクエリを書いて、「実行」ボタンをクリックします。

C. テーブルの結合～担当者決定のクエリ作成

続いて、顧客リストとテリ表を結合するためのクエリを作ります。「クエリを新規作成」をクリックします。

下記のSQLをコピー＆ペーストして、「クエリを保存」をクリックします。名前は teri_join とします。

with


/* ①テリ表の住所で、顧客リストの住所を前方一致で検索して結合する。
   顧客リストのある1行の住所に対して、テリ表の複数行の住所が前方一致する場合、
  複数のrankが付与される */
list_teri as (
  select
    list.company
    , list.addr
    , teri.addr_teri
    , teri.person
    , teri.rank
  from `terihyou.list` as list


  left outer join `terihyou.teri` as teri
  on  list.addr like concat(teri.addr_teri, "%")
)
,


/* ②顧客リストの各行に対して、複数付与されたrankの中から最小（min）を求める */
min_rank as (
  select
    company
    , min(rank) as mrank
  from list_teri


  group by
    company
)




/* ③ ①②を企業名とrankをキーにして紐づけることで、合致するテリ表の住所の中で、
   最も粒度が細かい（rankが小さい）住所が計算できる */
select
  l.*
from list_teri as l


inner join min_rank as m
on
  l.company = m.company
  and
  l.rank = m.mrank


order by l.addr


実行して、このような結果になっていれば成功です！

前述で例に挙げた、「アタラ合同会社、東京都新宿区新宿1-36-2」が、テリ表の「東京都新宿区新宿1、CCC」と紐づいていることが確認できます。

ちなみに、①まで実行した状態で出力し、企業ごとに分け（赤線）、どのrank（優先順位）が採用されたかを示した（黄色）のが下図です。リストの住所とどのテリ表住所が前方一致でヒットして、最終的にどれが採用されたかを確認できます。

大分長くなりましたが、以上テリ表適用の自動化の手順になります。

実践する上での注意・ポイント

この方法を実際に運用するにあたっての注意点や、「こうするともっと便利！」という点を紹介します。実際の経験に基づくものなので、実践の時に役立つはずです。

１．住所の入力規則

この手法は「テリ表住所がリスト住所に前方一致しているか？」をコンピュータに判断させているので、住所の入力規則は厳密に決める必要があります。例えば、「１丁目」（１が全角）と「1丁目」（1が半角）は人間には一緒に見えますが、コンピュータは全角半角を区別するので別物として判断してしまいます。住所の入力規則は厳密に決める必要があります。

郵便番号や何らかの情報から、整った住所が入っていれば問題はないのですが、

  • 人による手入力で規則から外れるパターンになっている

  • 手入力により、そもそも存在しない住所になっている（東京都横浜市中区〇〇など）

  • システムの入れ替えなどで、入力パターンが新旧混ざっている

など、様々な要因で規則通りになっていないことがあります。

私が実施した時に決めたルールの一部を紹介すると、

  • スペースは全角、半角どちらも削除する

  • 「丁目」、「番地」、「番」は「-（半角ハイフン）」にする

  • 「-（半角ハイフン）」を似ているものと混同しない
    例：ー（全角長母音）、ｰ（半角長母音）、－（全角マイナス）

等です。特に三つ目はフォントによっては全く同じに見えるものもあるので要注意です。

ハイフンと混同しやすいものをGoogleで検索してみたら、こんな記事が見つかりました。ご参考まで。

話は脱線しますが、こちらのnoteで日本の住所の奥深さ（？）が紹介されています。共感するところ、そんな住所があるんだ！、ととても面白く読めましたので、ご興味あればぜひ。

顧客リスト住所の事前クリーニング

リストとテリ表を結合する前に、住所がこの規則に則っているかを事前にチェックする必要があります。チェックというよりも、機械的に全件文字置換をかけてクリーニングするというイメージです。

リスト住所が人による修正も可能な運用だと、一度クリーニングをしても規則外のパターンは何度も発生します。「入力規則は～～～です」と周知したところで、それを守ってくれる保証はないので、住所は様々なパターンで入力されうるという前提で処理を組み立てましょう。

２．テリ表の管理

手順の説明では話を簡潔にするために、シンプルなテリ表を例示しました。しかし実際には、複数人（数十人、時には百人以上）がテリ表を適宜編集することになります。それを安全に運用するために以下のことを検討しましょう。

ファイルは共有フォルダに格納

テリ表適用を自動化するメリットの一つに、「柔軟に営業担当を変更できるようにして、フレキシブルな営業戦略を実行できる」を挙げました。「柔軟に」を実現するために、関係者がアクセスできる共有フォルダにファイルを格納して管理しましょう。

テリ表ファイルをメールでやり取りすると、どれが最新版なのか把握しづらくなるので避けましょう。

ファイルは適度に分割する

追加の注意点です。単一のファイルを共有フォルダに置くだけだと、

  • 大容量のファイルに複数人が同時アクセスして操作できなくなってしまった

  • 誤って内容を上書き、削除されている（しかも誰がいつ行ったかもわからない）

などの予期せぬトラブルが発生しがちです。

リスク分散のためにも、規模が大きくなる場合はテリ表ファイルを複数ファイルに分けましょう。適用作業の時に、一つのテリ表ファイルにまとめる処理を組み込むことで、問題を未然に防げます。

おわりに

私が事業会社にいたときに行ったデータ活用支援施策の中で、最も事業貢献度が大きかったのがこの「テリ表の自動適用」だと思っています。

営業統括部門へのデータ活用支援をメイン業務としていたため、通常業務での直接的な支援対象者は少ないことが常でした。しかし、テリ表の自動適用は間接的ではあるものの大勢の営業現場の人たちに影響があるので、やりがいを感じられた仕事でもありました。

また、自分が営業部門からデータ活用支援部門へ異動したので、自分の経験を基に

  • 営業現場がリスト管理で困っていること

  • 実装は大変だけど実現すべきであること

  • 運用に乗せるために工夫すべきこと

などを明確にイメージできたことも、成功の大きな要因でした。

「IT部門は事業側に寄り添ってくれない」、「事業部門は技術的なことがまるでわかっていない」という話はよく聞きます。私はこの仕事を通じて、事業部門・IT部門、双方のことをわかった上で、事業のための最適解は何かを自らに問い続ける姿勢の大切さを学んだ気がします。

テリ表の適用には特別な思い入れもあり、かつ手順がそれなりに難しいので、解説がかなり長くなってしまいました。しかし、この問題に困っている企業の担当者は少なくないと思うので、理解は大変かもしれませんが活用してもらえたら嬉しいです。

こちらも思い入れのある仕事の一つなので、ご興味あればぜひ。

ここまでお読みいただきありがとうございました。

※この記事の内容、所属、肩書き等は記事作成時点のものです。



アタラでは一緒に働くメンバーを募集しています