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

やりかた

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

usersテーブル

id	name	email	password	created_at	updated_at
1	user_name_1	xxx_1@example.com	*************	2019-01-03 8:20:21	2019-01-03 8:20:21
2	user_name_2	xxx_2@example.com	*************	2019-01-03 11:20:21	2019-01-03 11:20:21
3	user_name_3	xxx_3@example.com	*************	2019-01-03 12:30:21	2019-01-03 12:30:21
4	user_name_4	xxx_4@example.com	*************	2019-01-03 12:30:21	2019-01-03 12:30:21
5	user_name_5	xxx_5@example.com	*************	2019-01-04 8:20:21	2019-01-04 8:20:21
6	user_name_6	xxx_6@example.com	*************	2019-01-04 11:20:21	2019-01-04 11:20:21
7	user_name_7	xxx_7@example.com	*************	2019-01-04 12:30:21	2019-01-04 12:30:21
8	user_name_8	xxx_8@example.com	*************	2019-01-04 12:30:21	2019-01-04 12:30:21
9	user_name_9	xxx_9@example.com	*************	2019-01-05 8:20:21	2019-01-05 8:20:21
10	user_name_10	xxx_10@example.com	*************	2019-01-05 11:20:21	2019-01-05 11:20:21
11	user_name_11	xxx_11@example.com	*************	2019-01-05 12:30:21	2019-01-05 12:30:21
12	user_name_12	xxx_12@example.com	*************	2019-01-05 16:20:21	2019-01-05 16:20:21
13	user_name_13	xxx_13@example.com	*************	2019-01-15 8:20:21	2019-01-15 8:20:21
14	user_name_14	xxx_14@example.com	*************	2019-01-16 8:20:21	2019-01-16 8:20:21
15	user_name_15	xxx_15@example.com	*************	2019-01-17 8:20:21	2019-01-17 8:20:21
16	user_name_16	xxx_16@example.com	*************	2019-01-18 8:20:21	2019-01-18 8:20:21
17	user_name_17	xxx_17@example.com	*************	2019-01-19 8:20:21	2019-01-19 8:20:21
18	user_name_18	xxx_18@example.com	*************	2019-01-20 8:20:21	2019-01-20 8:20:21
19	user_name_19	xxx_19@example.com	*************	2019-01-21 8:20:21	2019-01-21 8:20:21
20	user_name_20	xxx_20@example.com	*************	2019-01-22 8:20:21	2019-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

結果