【ITニュース解説】How to See Your Best and Repeat Customers in a Spreadsheet

スプレッドシートを使った顧客分析は、システムエンジニアを目指す上でデータ処理の基本を学ぶ良い機会となる。この分析では、注文データから「誰が」「どれくらい」購入しているか、「いつから」「いつまで」利用しているかといった情報を抽出し、顧客の特性を理解するための指標を作り出す。これにより、ビジネスにとって価値の高い顧客を見つけ出したり、離反しそうな顧客に早期に気づいたりすることが可能になる。

まず、分析の元となるのは「注文リスト」だ。このリストには、いつ（日付）、誰が（顧客名）、何を（金額）注文したかが記録されている。この生データだけでは全体像を掴みにくいため、ここから「顧客サマリー」を作成する。顧客サマリーは、各顧客につき一行でまとめられた情報で、誰に連絡すべきか、どの顧客が重要かといった洞察を提供する。例えば、二ヶ月間注文のない常連客、一度きりの購入者、売上の大部分を占める少数の優良顧客などがこれにあたる。

この顧客サマリーは、ピボットテーブルのような高度な機能を使わず、基本的なスプレッドシート関数のみで構築できるのが特徴だ。これにより、Excel 2019以降、Microsoft 365、またはGoogle Sheetsで利用可能なMINIFS関数とMAXIFS関数を除けば、ほとんどのバージョンのスプレッドシートで機能する。

具体的な手順としては、まず注文リストを用意し、その横に各顧客を一度だけリストアップする。この顧客リストは、注文リストの顧客名をコピーして重複を削除することで簡単に作成できる。ここで重要なのは、顧客名を正確に綴ることだ。わずかなスペルミスでも、システムは別の顧客と認識してしまうため、注意が必要となる。

次に、この顧客リストの各行に、それぞれの顧客に関する詳細情報を集計していく。「合計購入額」は、SUMIFS関数を使って、特定の顧客が購入したすべての金額を合計する。これは、顧客がビジネスにもたらした総収益を把握するために不可欠な指標だ。「注文回数」は、COUNTIFS関数で、特定の顧客からの注文が何回あったかを数える。注文回数が多いほど、その顧客のロイヤリティが高い可能性を示す。

「初回注文日」はMINIFS関数で、その顧客が最初に行った注文の日付を特定する。これは、顧客との関係がいつ始まったかを知る上で重要だ。「最終注文日」はMAXIFS関数で、その顧客が最後に行った注文の日付を特定する。この情報は、顧客の活動状況を測る上で非常に重要となる。「最終注文からの経過日数」は、最終注文日から今日の（または任意の基準日）までの日数を計算する。この日数が長ければ長いほど、顧客がビジネスから遠ざかっている可能性を示唆する。ここで注意すべきは、まだ注文のない顧客の場合、これらの日付計算がエラーになるのを防ぐために、IF関数を使って「注文回数が0であれば空白を表示する」という条件を加えることだ。これにより、日付が「1900年」のような誤った値になるのを防ぎ、データの信頼性を保つことができる。

これらの情報が集計できたら、次に各顧客をその行動に基づいて分類する。「注文なし」「一回のみの購入」「リピート顧客」「リピート顧客だが休眠中」といったカテゴリ分けを行うのだ。この分類はIF関数を組み合わせることで自動化できる。例えば、「最終注文からの経過日数が60日以上」であれば「休眠中」と判断するなど、ビジネスの性質に合わせて「休眠」の定義（日数）を変更することが重要だ。カフェと卸売業者では、顧客が「静かになった」と感じる期間が異なるためだ。

さらに、顧客の「ランキング」や「全売上に対するシェア」も計算する。「ランキング」は、合計購入額に基づいて顧客を順位付けし、誰が最も貢献しているかを一目でわかるようにする。RANK関数を使用する。「シェア」は、各顧客の合計購入額が、全体の売上に対してどれくらいの割合を占めるかを示す。これは、顧客の合計額を全体の合計額で割るようなシンプルな割り算で計算し、パーセンテージ形式で表示する。ここで、分母となる「全体の合計額」は、顧客リストに載っていない顧客の売上も含めた全注文の合計を用いることで、もし顧客リストに漏れがあっても正確なシェアを把握できるよう工夫する。

これらの個別顧客データに加え、全体のサマリー情報も作成する。顧客リストに記載された顧客の合計売上、全ての注文の合計売上、そして「顧客リストに載っていない顧客からの注文数」などを計算する。特に「顧客リストに載っていない顧客からの注文数」は重要で、これにより、新規顧客の登録漏れや顧客名の入力ミスを検出し、データ全体の整合性をチェックできる。例えば、リスト上の合計売上と全注文の合計売上に差があれば、「CHECK」というアラートを出すことで、問題に気づけるようにするのだ。加えて、注文のある顧客のうち、何割がリピート顧客であるかを示す「リピート率」も計算する。

この一連の分析によって、具体的にどのような洞察が得られるか見てみよう。例えば、記事のサンプルでは「ハリ・ミルズ」という顧客が、リピート購入者であるにもかかわらず、最終注文から185日も経過していることが判明した。これは、ビジネスにとって非常に重要な情報で、この顧客が「休眠状態」にあることを示唆している。このような顧客には、早期にアプローチすることで、関係を再構築し、再び購入してもらう機会を生み出すことができる。この分析がなければ、そうした顧客の存在を見落としてしまう可能性が高い。

最後に、作成したスプレッドシートの信頼性を確認することは非常に重要だ。例えば、リストにない顧客からの注文を追加してみて「CHECK」アラートが正しく出るか、休眠状態の顧客が正しく分類されるかなど、意図通りの結果が得られるかを確認する。これは、システムやツールを開発する上で、テストが不可欠であることの示唆でもある。自分で設定した条件で意図した結果が得られるかを確認することで、そのツールが実用可能であるかを判断するのだ。このように、スプレッドシートの関数を組み合わせることで、複雑な顧客データを効果的に分析し、ビジネス上の意思決定に役立つ具体的な情報を引き出すことができる。これは、システムエンジニアがデータとロジックを扱って課題を解決する能力を養う上で、非常に良い演習となるだろう。