BigQueryレシピ

BigQuery | ABテストでユーザー振り分け

やりかた

約50%の割合でコントロールグループ/トリートメントグループを分類します。

usersテーブル

idnameemailpasswordcreated_atupdated_at
1user_name_1xxx_1@example.com*************2019-01-03 8:20:212019-01-03 8:20:21
2user_name_2xxx_2@example.com*************2019-01-03 11:20:212019-01-03 11:20:21
3user_name_3xxx_3@example.com*************2019-01-03 12:30:212019-01-03 12:30:21
4user_name_4xxx_4@example.com*************2019-01-03 12:30:212019-01-03 12:30:21
5user_name_5xxx_5@example.com*************2019-01-04 8:20:212019-01-04 8:20:21
6user_name_6xxx_6@example.com*************2019-01-04 11:20:212019-01-04 11:20:21
7user_name_7xxx_7@example.com*************2019-01-04 12:30:212019-01-04 12:30:21
8user_name_8xxx_8@example.com*************2019-01-04 12:30:212019-01-04 12:30:21
9user_name_9xxx_9@example.com*************2019-01-05 8:20:212019-01-05 8:20:21
10user_name_10xxx_10@example.com*************2019-01-05 11:20:212019-01-05 11:20:21
11user_name_11xxx_11@example.com*************2019-01-05 12:30:212019-01-05 12:30:21
12user_name_12xxx_12@example.com*************2019-01-05 16:20:212019-01-05 16:20:21
13user_name_13xxx_13@example.com*************2019-01-15 8:20:212019-01-15 8:20:21
14user_name_14xxx_14@example.com*************2019-01-16 8:20:212019-01-16 8:20:21
15user_name_15xxx_15@example.com*************2019-01-17 8:20:212019-01-17 8:20:21
16user_name_16xxx_16@example.com*************2019-01-18 8:20:212019-01-18 8:20:21
17user_name_17xxx_17@example.com*************2019-01-19 8:20:212019-01-19 8:20:21
18user_name_18xxx_18@example.com*************2019-01-20 8:20:212019-01-20 8:20:21
19user_name_19xxx_19@example.com*************2019-01-21 8:20:212019-01-21 8:20:21
20user_name_20xxx_20@example.com*************2019-01-22 8:20:212019-01-22 8:20:21

クエリ

WITH users AS (
  SELECT
    id,
    ROW_NUMBER() OVER() AS rn
  FROM
    `dataset.users`
), tc_group AS (
  SELECT
    id AS user_id,
    -- treatment: 1 介入、0 未介入
    CASE
      --  50%=1/2なのでMOD(N, 2)
      WHEN MOD(ABS(FARM_FINGERPRINT(CAST(rn AS STRING))), 2) = 0 THEN 1
    ELSE 0 END AS treatment
  FROM
    users
)
SELECT
  user_id,
  treatment
FROM
  tc_group

結果

user_idtreatment
10
20
30
40
50
60
71
80
90
101
110
121
130
140
151
160
171
181
191
201

解説

以下の手順で取得しています。

  1. ROW_NUMBER で 1 から連番に振る
  2. FARM_FINGERPRINT でハッシュ生成
  3. FARM_FINGERPRINT は負の値を返す可能性があるので ABS で絶対値に変換
  4. MOD でサンプル数を調整(例では全体の約50%)

関連リンク

より実務的なクエリを身につけるには「ビッグデータ分析・活用のためのSQLレシピ」がおすすめです。

ビッグデータ分析
created by Rinker
¥4,180
(2022/09/26 16:59:15時点 Amazon調べ-詳細)

created by Rinker
技術評論社
¥2,398
(2022/09/26 18:09:01時点 Amazon調べ-詳細)