販売者ごとに2回以上注文した顧客を数えるには？
商品明細を注文単位にまとめ、顧客ごとの注文数を数えた後、条件を満たす顧客数を販売者別に集計します。
1回の注文に商品が2つあっても、リピート購入にはなりません。販売者ごとのリピート顧客を知りたいなら、同じ販売者に同じ顧客が何回注文したかを調べます。まず商品明細を注文単位にまとめ、2回以上注文した顧客だけを数えます。
7行あっても7注文ではない
架空の明細では、V1のC1がO1で2商品を買ったため同じ注文番号が2行あります。注文番号は販売者と顧客の中で1注文を識別します。取消注文を除くルールなら、集計より前に絞り込んでください。顧客 IDや注文番号が空欄の明細は、元データで対応を確認します。空欄のまま集計すると、別の顧客や注文が1つにまとまるおそれがあります。
| 販売者 | 顧客 ID | 注文番号 |
|---|---|---|
| V1 | C1 | O1 |
| V1 | C1 | O1 |
| V1 | C2 | O2 |
| V1 | C2 | O3 |
| V2 | C1 | O4 |
| V2 | C1 | O5 |
| V2 | C2 | O6 |
1. まず商品明細を1注文1行にまとめる
データを読み込んだシートを選び、下部の結果プレビューから「結果を整理」を開きます。「集計」を押して、次のように設定します。
| 設定欄 | 操作 |
|---|---|
| まとめる項目 | 販売者、顧客 ID、注文番号を1つずつ選ぶ |
| 結果の項目名 | 注文明細件数と入力する |
| 集計方法 | 最初から選ばれている「件数」のままにする |
V1・C1・O1の2行が1つになり、7行の明細が6注文にまとまります。注文明細件数は、その注文に含まれる商品明細の数です。次に数えたいのはこの数の合計ではなく、注文の行数です。
2. 顧客ごとの注文数を確認する
続けて「集計」をもう1つ追加します。
| 設定欄 | 操作 |
|---|---|
| まとめる項目 | 販売者、顧客 IDを1つずつ選ぶ |
| 結果の項目名 | 注文数と入力する |
| 集計方法 | 「件数」のままにする |
「適用」を押すと、販売者と顧客の組み合わせごとに次の4行になります。
| 販売者 | 顧客 ID | 注文数 |
|---|---|---|
| V1 | C1 | 1 |
| V1 | C2 | 2 |
| V2 | C1 | 2 |
| V2 | C2 | 1 |
3. 2回以上注文した顧客を販売者別に数える
「結果を整理」を開き直し、作成済みの集計の下に「絞り込み」を追加します。「項目」は注文数、「条件」は「以上」にし、「値」に2を入力します。V1のC2とV2のC1だけが残る設定です。
最後に「集計」を追加し、「まとめる項目」で販売者だけを選びます。「集計方法」は「件数」のまま、「結果の項目名」にリピート顧客数と入力して「適用」を押します。ここで数えるのは注文ではなく、条件を満たした顧客の行数です。
各販売者の該当顧客は1人
V1で残るのはO2とO3を注文したC2です。C1のO1は2行でも1注文なので対象外です。V2はO4とO5を注文したC1が残り、1注文だけのC2は除かれます。最後に数えるのは条件を満たした顧客の行数です。
| 販売者 | リピート顧客数 |
|---|---|
| V1 | 1 |
| V2 | 1 |
途中の集計で販売者を外すと、別の販売者への注文が混ざってしまいます。また、ここで数えるのは読み込んだ期間内の購入だけです。該当する顧客がいない販売者は、この結果には出ません。全販売者を表示したい場合は、結果を保存して販売者一覧とともに読み込み直し、販売者一覧を左に置いて「左のシートの全行を保持」で結合します。該当のない販売者の件数は空欄になるため、0で表示したい場合は保存後にExcelなどで整えます。
自分のファイルで始めましょう
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。
新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
How can I find the amount of repeat customers each vendor has?の質問を参考に、独自の架空データで手順を作成しました。
