0～10点の回答からグループ別NPSを求めるには？
点数を−1・0・1に対応させ、グループ別の平均に100を掛けます。7・8点も分母に含め、空欄や範囲外の点数を区別します。
NPSは、有効回答のうち9・10点を付けた人の割合から、0～6点を付けた人の割合を引いた点数です。JOIN SHEETでは、回答の点数を計算用の−1・0・1に対応させ、グループごとの平均に100を掛けて求めます。7・8点の回答も、全回答数を数えるときには含めます。
1回答に1つの点数
架空の回答には、それぞれ一意なResponseIDがあります。同じ回答が複数行に分かれた資料では、先に調査のルールに従って整理してください。∅は点数が空欄のセルです。
| Group | ResponseID | Score |
|---|---|---|
| A | R1 | 9 |
| A | R2 | 10 |
| A | R3 | 6 |
| A | R4 | 8 |
| B | R5 | 0 |
| B | R6 | 5 |
| B | R7 | 7 |
| B | R8 | 9 |
| C | R9 | ∅ |
| C | R10 | 11 |
集計対象は整数の0～10点です。Cの空欄と11点は有効回答から除外し、元データを確認する対象にします。0点は有効な回答なので、空欄と一緒に除外しないでください。
点数対応表を用意する
次の11行を別のシートに用意します。Scoreは重複させず、各点数を1行ずつ入れてください。Contributionには、NPSの計算に使う−1・0・1を入れます。個人ごとのNPSを表す値ではありません。
| Score | Contribution |
|---|---|
| 0 | -1 |
| 1 | -1 |
| 2 | -1 |
| 3 | -1 |
| 4 | -1 |
| 5 | -1 |
| 6 | -1 |
| 7 | 0 |
| 8 | 0 |
| 9 | 1 |
| 10 | 1 |
範囲を自動分類する式を設定する作業ではありません。Scoreの完全一致でContributionを取得します。7・8点に対応する0も平均に含める数値なので、空欄にはしません。
回答ごとに計算用の値を付け、グループ別に集計する
- ResponsesとScoreMapを読み込み、両方のScoreが数値型であることを確認します。Responsesを結果の基準にします。
- Score同士を「一致する行のみ」で接続します。有効回答が8行残ることを確認し、ScoreMap側の重複するScoreは非表示にします。
- 「結果を整理」で「集計」を追加し、「まとめる項目」にGroupを選びます。最初の集計は「件数」のまま、「結果の項目名」にValidResponsesと入力します。「集計を追加」を押し、「集計方法」を「平均」、「項目」をContributionに変え、「結果の項目名」にMeanContributionと入力します。
- 集計の下に「計算」を追加します。MeanContributionに固定の数値100を掛け、NPSという名前にします。Groupの昇順で並べ替え、「適用」を押します。
7・8点も分母に入っているか確認する
| Group | ValidResponses | MeanContribution | NPS |
|---|---|---|---|
| A | 4 | 0.25 | 25 |
| B | 4 | -0.25 | -25 |
Aは推奨者2件、批判者1件、中立者1件で、(2−1)÷4×100=25です。Bは推奨者1件、批判者2件、中立者1件なので−25になります。中立者を除いて3件で割ると別の数値になるため、有効回答数の4も確認してください。
NPS列の25は点数の25です。0.25にパーセント表示を適用するという意味ではありません。有効回答のないCは結果に現れません。Cを0で埋めると、回答がないことが分からなくなります。この小さな架空例から、満足度の傾向やグループ間の優劣までは判断できません。
自分のファイルで始めましょう
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。
新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
Pivot table calculate field based on grouped valuesの質問を参考に、架空のデータを用意しました。
0～10点の分類と計算の定義は、Bain & CompanyのMeasuring Your Net Promoter Scoreに基づきます。
