夕蛙のなく頃に

データアナリストとして学んだことや趣味で勉強し始めたIoTをアウトプットする

CTRやCVRのような比率データを95%信頼区間のエラーバーが付くようSQLで集計する

モチベーション

会社でコンバージョン率(CVR)を月次ないし日次でダッシュボード化し、追いかけている。

スタートアップでデータが少ないこともあり、月や日によって母数のばらつきが大きいので、ただCVRを出すだけでなく、95%信頼区間を計算して、エラーバー付きで描画したい。

分野: 母比率の区間推定
言語: PostgreSQL 描画するBIツール: Redash

母比率の区間推定

参考サイト bellcurve.jp

ここでは、ある商品が閲覧された回数のうち購入された回数がどのくらいかのCVRを考える。

\displaystyle{
CVR(p) = \frac{購入された回数(X)}{閲覧された回数(n)}
}

試行が成功するか失敗するかの0, 1データなので、Xは二項分布(n, p)に従う。 このpは観測された標本比率ではなく、母比率を表す。

nが十分に大きい時、中心極限定理よりこの二項分布(n, p)は、正規分布(np, np(1-p))に近似できる。

すなわち、Xを標準化した統計量Zは標準正規分布(0, 1)に従う。


Z = \frac{X - np}{\sqrt{np(1-p)}}

標本比率 \hat{p}\frac{X}{n}で計算できるので、上記の分子・分母をnで割ると以下のように変換できる。


Z = \frac{\hat{p} - p}{\sqrt{\frac{p(1-p)}{n}}}

95%信頼区間とすると、標準正規分布表からこの標準統計量Zは-1.96 〜 1.96に収まる。これを変換すると母比率pは観測されたCVRと閲覧された回数を用いて以下のように書ける。


\hat{p} - 1.96 * \sqrt{\frac{\hat{p}(1-\hat{p})}{n}} \leq p \leq \hat{p} + 1.96 * \sqrt{\frac{\hat{p}(1-\hat{p})}{n}}

SQLで集計する

上記の数式の通りに計算するだけです。

テストデータを作成

本番データで出すわけにはいかないので、テストデータを作成して、それを描画してみます。

下記クエリで、2019-01-01〜2019-01-10のいずれかの日付に対して、母比率p=20%でクリックされたデータが10行作成されます。

SELECT
  '2019-01-01'::date + cast(((random() * 10)::int % 10)::text || ' days' AS INTERVAL) AS created_at,
  CASE
    WHEN random() <= 0.2 THEN 1
    ELSE 0
  END AS clicked
FROM generate_series(1,10)

f:id:frogdusk:20190411152411p:plain:h300

95%信頼区間のエラーバーつきでCTR推移を集計して描画する

10000分のデータを出し、最後Redashで描画する。

WITH base AS (
    SELECT
      '2019-01-01' :: DATE + cast(((random() * 10) :: INT % 10) :: TEXT || ' days' AS INTERVAL) AS created_at,
      CASE
      WHEN random() <= 0.2
        THEN 1
      ELSE 0
      END                                                                                       AS clicked
    FROM generate_series(1, 10000)
), agg AS (
    SELECT
      to_char(created_at, 'YYYY-MM-DD') AS yyyymmdd,
      count(*)                          AS show_cnt,
      count(clicked = 1 OR NULL)        AS click_cnt,
      1.0 * count(clicked = 1 OR NULL) / count(*) AS ctr
    FROM base
    GROUP BY yyyymmdd
    ORDER BY yyyymmdd
)
SELECT
  *,
  1.96 * SQRT(ctr * (1 - ctr) / show_cnt) AS error_bar
FROM agg

f:id:frogdusk:20190411153629p:plain:h300 f:id:frogdusk:20190411153656p:plain

おまけ

2019-01-01〜2019-01-05は閲覧回数1000回/day程度、2019-01-06〜2019-01-10は閲覧回数10000回/day程度と、データ量に差をつけてみる。

WITH base1 AS (
    SELECT
      '2019-01-01' :: DATE + cast(((random() * 10) :: INT % 5) :: TEXT || ' days' AS INTERVAL) AS created_at,
      CASE
      WHEN random() <= 0.2
        THEN 1
      ELSE 0
      END                                                                                      AS clicked
    FROM generate_series(1, 5000)
), base2 AS (
    SELECT
      '2019-01-06' :: DATE + cast(((random() * 10) :: INT % 5) :: TEXT || ' days' AS INTERVAL) AS created_at,
      CASE
      WHEN random() <= 0.2
        THEN 1
      ELSE 0
      END                                                                                      AS clicked
    FROM generate_series(1, 50000)
), all_base AS (
  SELECT *
  FROM base1
  UNION ALL
  SELECT *
  FROM base2
), agg AS (
    SELECT
      to_char(created_at, 'YYYY-MM-DD') AS yyyymmdd,
      count(*)                          AS show_cnt,
      count(clicked = 1 OR NULL)        AS click_cnt,
      1.0 * count(clicked = 1 OR NULL) / count(*) AS ctr
    FROM all_base
    GROUP BY yyyymmdd
    ORDER BY yyyymmdd

)
SELECT
  *,
  1.96 * SQRT(ctr * (1 - ctr) / show_cnt) AS error_bar
FROM agg

f:id:frogdusk:20190411154043p:plain

たしかにデータ量が多い後半の方がエラーバーが小さいことがわかります。データ量が10倍になったことで、エラーバーは \frac{1}{\sqrt{10}}になっています。