もっと詳しく

こんにちは。MackerelチームでCRE(Customer Reliability Engineer)をしているid:syou6162です。主にカスタマーサクセスを支えるデータ基盤の構築や、データ分析を担当しています。

意思決定をする際には不確実性がつきまといますが、不確実性を信頼区間という形で考慮して意思決定を行なおう、という話をします。

この記事ははてなエンジニア Advent Calendar 2020の4日目の記事です。

前日はid:dekokunさんによるGoogle Cloud の Associate Cloud Engineer 資格を取得するためにした勉強でした。

数字のバラ付きを考慮して意思決定したいケース: NPSでの事例

お客さまに対してサービスの利用調査をさせてもらう機会が定期的にあります。サービスの利用目的、気にいっている点や使いにくいと思っている機能などを定量 / 定性的に調査することが目的であることが多いと思います。アンケートの形式ではなく、ユーザーインタビューの形を取ることもあるでしょう。

MackerelでもNPS(Net Promoter Score)を定期的にお客さまに送付させてもらっています。NPSは「XXを友人や同僚にお薦めしますか? 0~10の11段階でお答えください」という質問に答えていただくアンケートの形式です。9~10点を付けたお客さまを「推奨者」、7~8点を「中立者」、0~6点を「批判者」と分類します。NPSのスコアは、回答者全体に占める推奨者の割合から批判者の割合を引いた値で定義されます。サブスクリプション時代において、リテンションの高さなどはますます重要になっていますが、「NPSのスコアと売上成長率への相関があること」や「推奨者は批判者に比べてLTVが高いこと」などから、NPSは最近カスタマーサクセスの分野を中心に注目を上げているKPIの一つとなっています。

NPSに代表されるアンケートはお客さまから回答する時間をいただくため、あまりに高頻度に行なうわけにはいきません。調査の度にサンプルサイズがバラ付くことも結構あります。サンプルサイズが多ければその調査を元に計算された(NPSのような)数値はバラ付きが少なく、逆にサンプルサイズが少なければバラ付きは大きくなります。バラ付きが大きいにも関わらず、「よし、数値が改善されたからこの施策は効果があったぞ!」とか「NPSのスコアが悪化しているから、一刻も早くユーザーのペインを取り除く施策を打たなければ!」と結論付けるのはあまりよくないでしょう。

統計学を使って数字のバラ付きを考慮する: 信頼区間

NPSのように「回答者全体に占めるXXXの割合」といった割合のバラ付きに興味がある場合、統計学で昔からよく研究されている信頼区間というものが利用できます。信頼区間は「調査によって出た値がどれくらいバラ付くか」を統計的に考えた手法です。例えば、95%信頼区間と呼ばれるものでは「仮に100回調査が実施可能だった場合、(神様だけが知っている)真の割合は95回はこの区間内にいる」というものを表わします。この範囲が狭いほどバラ付きが少なく、意思決定の判断材料として使いやすいというわけです。コストの面などから100回実際に調査するのが不可能だったとしても、このくらいの範囲にいるというのが統計的に分かります。人類の偉大な知恵ですね。

割合の信頼区間であれば統計学の教科書に載っている典型的な問題となりますが、NPSの場合は「推奨者の割合から批判者の割合を引いた値」という「割合の差」が興味のある数値となります。この数字の信頼区間は教科書には載っていないため、自分で以下のような計算する必要があります。

  • 標本比率のモーメント(平均および分散)
  • 中心極限定理の標本比率への応用
  • 差の分散といった統計量の計算
  • 自分の問題に適した信頼区間の算出

多少混み入った計算が必要ですが、私のブログに詳細を書いているので興味がある方はそちらをご覧ください。

NPSの信頼区間をSQLで計算する

前述した通り、MackerelではNPSを定期的に実施しています。回答結果はデータ基盤(BigQuery)に取り込むのですが、調査の度にNPSの信頼区間をスクリプトやスプレッドシートで計算するのは面倒です。ここでは、SQLを使って自動的にNPSの信頼区間を計算する方法を紹介します。

まず、集計元になる元データを用意しましょう。NPSを送付した時期と個々の回答のスコアが必要になります。例えば以下のようなデータを想定します。

nps_send_season score
2020-01-01 7
2020-01-01 8
2020-04-01 6
2020-04-01 8
2020-07-01 10
2020-07-01 9
2020-10-01 10
2020-10-01 9

完成したSQLは多少長いため、分割して解説します。まず、生のscoreをNPSでいうところの推奨者 / 中立者 / 批判者に区分しましょう。区分をここでは、customer_typeと呼ぶことにします。

WITH
nps_with_customer_type AS (
  SELECT
    nps_send_season,
    CASE
      WHEN score >= 0 AND score <= 6 THEN "detractors"
      WHEN score >= 7 AND score <= 8 THEN "passives"
      WHEN score >= 9 AND score <= 10 THEN "promoters"
      END AS customer_type,
  FROM
    my-project.my_dataset.raw_nps
),
...

次に送付した時期毎に推奨者 / 中立者 / 批判者がそれぞれ何人いたかを集計します。

...
nps_by_season_and_customer_type AS (
SELECT
  nps_send_season,
  customer_type,
  COUNT(*) AS users_count
FROM
  nps_with_customer_type
GROUP BY
  nps_send_season,
  customer_type
ORDER BY
  nps_send_season,
  customer_type ),
...

今回着目したいのは推奨者 / 中立者 / 批判者毎の人数ではなく割合なので、Window関数を使って送付した時期毎に推奨者 / 中立者 / 批判者それぞれの割合の列を追加します。

customer_type_ratio AS (
SELECT
  *,
  SUM(users_count) OVER (PARTITION BY nps_send_season) AS total_users_count,
  users_count / SUM(users_count) OVER (PARTITION BY nps_send_season) AS users_count_ratio,
FROM
  nps_by_season_and_customer_type ),
...

ここまで集計したデータを表にまとめると、以下のようになります。

nps_send_season customer_type users_count total_users_count users_count_ratio
2020-01-01 detractors 10 30 0.3333333333
2020-01-01 passives 10 30 0.3333333333
2020-01-01 promoters 10 30 0.3333333333
2020-04-01 detractors 15 35 0.4285714286
2020-04-01 passives 10 35 0.2857142857
2020-04-01 promoters 10 35 0.2857142857
2020-07-01 detractors 12 40 0.3
2020-07-01 passives 18 40 0.45
2020-07-01 promoters 10 40 0.25

このデータはいわゆる縦持ちになっているわけですが、NPSの信頼区間を計算するのに使いにくい形式であるため、以下のような横持ちの形式に変換します。

nps_send_season users_count promoters_ratio detractors_ratio nps_score
2020-01-01 30 0.3333333333 0.3333333333 0
2020-04-01 35 0.2857142857 0.4285714286 -0.1428571429
2020-07-01 40 0.25 0.3 -0.05

GROUP BYとCASE WHENを使うと、縦持ちのデータを横持ちに変換することができます。これはSQLを使ったデータ分析では頻出のテクニックの一つですが、興味がある方は以下の本が参考になるかと思います。

nps_score_by_season AS (    
  SELECT
    nps_send_season,
    SUM(users_count) AS users_count,
    MAX(CASE WHEN customer_type = "promoters" THEN users_count_ratio ELSE NULL END) AS promoters_ratio,
    MAX(CASE WHEN customer_type = "detractors" THEN users_count_ratio ELSE NULL END) AS detractors_ratio,
    MAX(CASE WHEN customer_type = "promoters" THEN users_count_ratio ELSE NULL END) - MAX(CASE WHEN customer_type = "detractors" THEN users_count_ratio ELSE NULL END) AS nps_score,
  FROM
    customer_type_ratio
  GROUP BY
    nps_send_season
  ORDER BY
    nps_send_season
)

さて、ようやく今回興味があった推奨者の割合と批判者の割合が計算できたので、NPSの信頼区間の計算式に当てはめましょう。

SELECT
  *,
  nps_score + 1.96 * sqrt((- (nps_score * nps_score) + promoters_ratio + detractors_ratio) / users_count) AS upper_bound,
  nps_score - 1.96 * sqrt((- (nps_score * nps_score) + promoters_ratio + detractors_ratio) / users_count) AS lower_bound,
FROM
  nps_score_by_season

SQLができたので、あとはGoogleデータポータルなどのツールで可視化してやれば完成です。データマートにviewとして置いておけば、データを追加するだけでグラフも自動的に更新されます。スクリプトやスプレッドシートを間に挟むと運用の手間が増えますが、SQLで完結するとそういった手間を削減できます。

f:id:syou6162:20201129022229p:plain
NPSの時系列の推移。NPSの信頼区間の上限と下限も見ることでバラ付きについても考慮する

サンプルデータなので、大分恣意的ではありますが、このグラフから例えば以下のようなことが分かります。

まとめ

データを用いた意思決定を行なう場合、数値のバラ付きを考慮する必要があります。そのための手法の一つとして、信頼区間を紹介しました。NPSのような(ある意味独自の)数値についても統計学の知識を使って信頼区間を構成できます。最後に、SQLを使ってNPSの信頼区間を計算する方法について紹介しました。バラ付きを考慮する必要がある意思決定は、統計も駆使しつつ行なえるといいですね。

明日のアドベントカレンダーはid:mizdraさんです!!