葛飾でひっそり暮らすIT屋の G Suite メモ

Google Apps で快適業務ライフなノウハウを伝えます

Google スプレッドシートで販売リストから顧客台帳を作る方法

ネットショップで出品してたりすると、販売データはダウンロードできるけど顧客データは無いっていう場合があります。

でも、顧客一覧を作って購入額なんかを管理したいこともあるだろうかと思います。

ということで、販売データから顧客台帳を作ります。

顧客台帳を作るためのやること

顧客台帳を作るためには販売データをどういう風に処理するかというと。

  • 顧客の重複を削除する
  • 顧客ごとの購入額を集計する
  • 最後に購入した日を登録する

こんなところでしょうかね。

顧客ごとの購入額は、上客を選んで手厚くサポートするとかそういった用途に使えますし。単にどんな人がどのくらいかっているかの分析をしてもいいでしょう。

最終購入日は、直近で購入している人のほうが追加購入を見込めるとか、色々とマーケティングに使えるかと思ったので。不要な方はその部分を読み飛ばしていただければ。

では、早速やっていきましょう。

顧客情報のユニーク化

まずは顧客をユニーク化します。

販売情報には同じ人が二回登場したりしているので、顧客台帳として扱えません。なので、それを一人一回ずつの登場にしていただこうという話。

この場合使うのは "UNIQUE" 関数です。

www.katsushikatech.net

これを使って、顧客情報のメールアドレスをユニーク化します。

ネットショップに限らず、大抵の販売システムはメールアドレスを個人を特定する情報として扱っているので、メールアドレスをキーにします。

CSV を Spreadsheet で扱えるようにする

と、その前に。

販売データのダウンロードは大抵の場合 CSV だと思うので、CSV を Spreadsheet で扱えるようにします。

そこで登場するのが "SPLIT" 関数です。

support.google.com

文字列を指定した文字、記号で分割するというもの。

これを使っていきましょう。

ダウンロードした CSV のテキストを A1 セルに貼り付けたら、B 列に関数を記入します。

それをデータがある行全てにコピーするとカンマで区切られたテキストが列に分割されます。

便利ですねぇ。

ここで改めて "UNIQUE" 関数です。

新たに顧客台帳のシートを作成して、A1 セルに関数を入力します。

これで、メールアドレスがユニーク化されます。

そして、このメールアドレスを基準に、各個人情報を拾っていきます。

INDEX と MATCH の組み合わせで

ところで、メールアドレスを基準にといいましたが、メールアドレスの CSV データでの位置はどこでしょう。

はい、3列目ですね。

そして、それを SPLIT 関数で展開した後は4列目となります。

これ、何が問題かというと VLOOKUP が使えないんですね。

VLOOKUP というのはあくまでも特定の範囲の右側のデータしか持ってこれません。なのでこの場合は別の方法を検討しなくてはいけません。

そして、登場するのは INDEX と MATCH です。

どう使うか、については過去に記事を書きましたのでそちらを御覧ください。

www.katsushikatech.net

今回の場合の使い方としては、INDEX 関数の第一引数で販売データ シートの A 列から H 列までを指定します。

元の CSV も含んじゃってますが、それでも大丈夫。むしろそうした方が数式がスッキリします。

そして、第二、第三引数では MATCH でデータの位置を調べています。

これを組み合わせてデータを表示する範囲にコピーすると表の出来上がり。

ではなかったりします。

はい、最終購入日付です。

これについては、ここまで紹介してきた方法ではうまく行きません。

ではどうするか。

ちょっと長くなるので続きは下記の記事で。

www.katsushikatech.net

Google Spreadsheet の入門書って、ぱっと思いつくものがないのですが、Excel の書籍で代用は充分可能です。

Excel と共通の関数も多いので、結構参考になります。

この本は、Excel のテクニックだけでなく、表計算ソフトをどのようにして経営に活かすかということも書かれているので、事務で表計算ソフトをあつかうなら必読と言ってもいいでしょう。