これなに?

BQを利用して、機械学習を行うために有用そうな方法をまとめた記事です
この記事の中では

データセットの概観を確認し、探索的に分析
分析した結果から精度向上に役立ちそうな特徴量を生成

使用するデータセットやjupyterとの接続方法については、前記事にまとめてあります

わりと長くなったので、BQMLの説明は次の記事に回します

使用するデータセット

前回の記事で作成したgoogle_analyticsのdatasetから、特定の商品をclickする確率を予測します(データセットはgoogleAnalyticsを使用します)

前回と同様にmagic commandを利用します

from google.cloud.bigquery import magics
from google.oauth2 import service_account
credentials = (service_account.Credentials.from_service_account_file('[/path/to/key.json]'))
magics.context.credentials = credentials

%load_ext google.cloud.bigquery

PROJECT ='[自身の作成したプロジェクトID]'

%%bigquery --project $PROJECT 
SELECT * FROM `google_analytics.ga_dataset` LIMIT 5

	hour	browser	isMobile	country	productPrice	isClick
0	13	Chrome	False	United States	20990000	None
1	13	Chrome	False	United States	990000	None
2	7	Firefox	False	Canada	16990000	None
3	7	Firefox	False	Canada	16990000	None
4	13	Chrome	False	United States	12990000	None

データセットの特徴を調べる

まず、isClickがどのようになっているか確認します

%%bigquery --project $PROJECT 
SELECT 
    isClick, COUNT(1) AS numIsClick 
FROM `google_analytics.ga_dataset`
GROUP BY isClick

	isClick	numIsClick
0	None	90973
1	True	2034

この結果を見る限り、どうやらclickしなかった場合、はFalseではなく、nullが入っているようです

今後の分析のことを考えると、1/0で二値化したほうが楽なので、変換します

また、productPriceの値も極端に大きいので10の6乗で割ります

%%bigquery --project $PROJECT 
-- 新たなdatasetを定義
CREATE OR REPLACE TABLE google_analytics.ga_dataset_cleansed AS
SELECT 
    *EXCEPT(isClick, productPrice),
    IF(isClick, 1, 0) AS isClick,
    productPrice/1e6 AS productPrice
FROM `google_analytics.ga_dataset`

%%bigquery --project $PROJECT 
SELECT * FROM `google_analytics.ga_dataset_cleansed` LIMIT 5

	hour	browser	isMobile	country
0	0	Chrome	False	United States
1	0	Chrome	False	United States
2	1	Chrome	False	United States
3	1	Chrome	False	United States
4	1	Chrome	False	United States

pandasを利用して統計的な特徴を確認

次に、pandasを利用して、データセットの概観を確認します。ただし、全部とってくると当然メモリに乗らない(乗る量ならそもそもBigQueryを使わなくてもよい)ので、RAND()関数で適当にsamplingしてきます

%%bigquery data_sampled --project $PROJECT 
-- 10%だけサンプリング
SELECT 
    * FROM `google_analytics.ga_dataset_cleansed` 
WHERE  RAND() < 0.1

概観を確認します

data_sampled.describe()

	hour	isClick	productPrice
count	9230.000000	9230.000000	9230.000000
mean	12.747996	0.021993	23.121088
std	5.346544	0.146670	25.548049
min	0.000000	0.000000	0.000000
25%	9.000000	0.000000	7.967500
50%	12.000000	0.000000	16.990000
75%	17.000000	0.000000	21.990000
max	23.000000	1.000000	575.700000

大まかな値段の分布やclickする確率がわかります。CTRは0.02程度で、productPriceの分布は右に歪んでそうですね

よりよい特徴量を探索

よりよい説明変数を見つけたり、逆に役に立たない変数を削ったりするために、特徴量ごとに分布や特性を分析、可視化してきます

hour特徴量

hourごとに見たときになにか特徴がないかを確認します

%%bigquery --project $PROJECT 
-- hourとtransactionの数の相関係数
WITH data AS(
SELECT 
    hour, COUNT(1) AS numTrans 
FROM `google_analytics.ga_dataset_cleansed` GROUP BY hour
)
SELECT CORR(hour, numTrans) AS corr FROM data

	corr
0	0.32829

transction数と時間にはかなり相関がありますね。加えて、おそらくhourの値は国ごとに共通なので、時差のことを踏まえると、国別に見るとより相関が高いかもしれません

%%bigquery --project $PROJECT 
-- hourとtransactionの数の相関係数
WITH data AS(
SELECT 
    country, hour, COUNT(1) AS numTrans 
FROM `google_analytics.ga_dataset_cleansed` GROUP BY country, hour
)
-- 国ごと集計
SELECT 
    country, SUM(numTrans) AS numTrans, CORR(hour, numTrans) AS corr 
FROM data GROUP BY country
-- transction数が多い国のみ表示
ORDER BY numTrans DESC LIMIT 10

	country	numTrans	corr
0	United States	71621	0.378118
1	United Kingdom	2502	-0.377689
2	Canada	2067	0.377426
3	India	2020	-0.086196
4	Brazil	1438	0.140260
5	Mexico	971	0.446485
6	Taiwan	901	0.563291
7	Japan	891	-0.219272
8	Australia	643	-0.066862
9	Spain	570	0.884690

国ごとに見たほうがtransaction数と時間にかなり相関がありそうです。同じ時間でも国ごとに特徴が違うならば、hour特徴量はそのまま使うよりもcountryとの交差特徴量として生成したほうがより有用そうです

交差項の作成のために、bqにはML.FEATURE_CROSSという関数が用意されているので、これで交差特徴量を作成します

%%bigquery --project $PROJECT 
SELECT 
    country,
    hour,
    ML.FEATURE_CROSS(
    STRUCT(
            country,
-- 数値型の場合はサポートされていないので、CASTする
            CAST(hour AS STRING) AS hour
    )) AS countryHour
FROM `google_analytics.ga_dataset_cleansed`  LIMIT 5

	country	hour	country_hour
0	United States	0	{'country_hour': 'United States_0'}
1	United States	0	{'country_hour': 'United States_0'}
2	United States	1	{'country_hour': 'United States_1'}
3	United States	1	{'country_hour': 'United States_1'}
4	United States	1	{'country_hour': 'United States_1'}

productPrice特徴量

商品の値段分布にどのような特徴があるかを見てみます

まず、観測数を10分割したときに区切りとなる値をAPPROX_QUANTIELS関数で見てみます

%%bigquery --project $PROJECT 
-- hourとtransactionの数の総関係数
WITH data AS(
SELECT 
        APPROX_QUANTILES(productPrice, 10) AS q
FROM `google_analytics.ga_dataset_cleansed` 
)
SELECT 
    quantile
FROM data, UNNEST(q) AS quantile

	quantile
0	0.00
1	1.99
2	4.99
3	10.99
4	16.99
5	16.99
6	17.99
7	20.99
8	25.00
9	59.99
10	575.70

25以上の値の数は少なく、また、17あたりに値が集中しているようです

ここで、値段ごとにctrがどれ位変わるかを見るために、ML.BUCKETIZE関数で、指定した境界値ごとの値に区切ってみます

%%bigquery --project $PROJECT 
SELECT 
    productPrice,
    ML.BUCKETIZE(
        productPrice,
        [2, 5, 16, 17, 20, 50]
    ) AS priceBin
FROM `google_analytics.ga_dataset_cleansed` 
LIMIT 5

	productPrice	priceBin
0	0.0	bin_1
1	0.0	bin_1
2	0.0	bin_1
3	0.0	bin_1
4	0.0	bin_1

値段ごとにctrがどれ位変わるかを確認してみます

%%bigquery --project $PROJECT 

WITH data AS(
SELECT
    isClick,
    productPrice,
    ML.BUCKETIZE(
        productPrice,
        [2, 5, 16, 17, 20, 50]
    ) AS priceBin
FROM `google_analytics.ga_dataset_cleansed` 
) 

SELECT 
    priceBin,
    MIN(productPrice)　AS minPrice,
    MAX(productPrice)　AS maxPrice,
    COUNT(1)　AS numTrans,
    SUM(isClick) AS click,
    SUM(isClick)/COUNT(1) AS CTR
FROM data
GROUP BY priceBin
ORDER BY priceBin

	priceBin	minPrice	maxPrice	numTrans	click	CTR
0	bin_1	0.00	1.99	9247	168	0.018168
1	bin_2	2.00	4.99	11340	306	0.026984
2	bin_3	5.60	15.99	15928	365	0.022916
3	bin_4	16.79	16.99	17176	285	0.016593
4	bin_5	17.50	19.99	10084	140	0.013883
5	bin_6	20.00	49.95	17630	322	0.018264
6	bin_7	50.00	575.70	11602	448	0.038614

値段が高くなると、CTRが下がるような傾向は見られず、また、16.8まわりに値段が極端に集中してます

なので、productPriceはそのまま数値としてではなく、binで区切り、カテゴリ特徴量に変換し、モデルに入力します

カテゴリ系の特徴量

ブラウザごと、あるいは携帯からのアクセスかどうかで、CTRに違いが現れるかを確認します

%%bigquery --project $PROJECT 
SELECT
    browser,
    isMobile,
    COUNT(1) AS numTrans,
    SUM(isClick) AS numClick,
    SUM(isClick)/COUNT(1) AS CTR
FROM `google_analytics.ga_dataset_cleansed` 
GROUP BY browser, isMobile
ORDER BY browser, isMobile DESC

	browser	isMobile	numTrans	numClick	CTR
0	Amazon Silk	True	26	0	0.000000
1	Chrome	True	9271	159	0.017150
2	Chrome	False	67104	1574	0.023456
3	Edge	False	870	5	0.005747
4	Firefox	False	3699	55	0.014869
5	Internet Explorer	True	34	0	0.000000
6	Internet Explorer	False	1500	13	0.008667
7	Opera	False	140	2	0.014286
8	Safari	True	7207	146	0.020258
9	Safari	False	2570	71	0.027626
10	Safari (in-app)	True	215	9	0.041860
11	UC Browser	True	68	0	0.000000
12	YaBrowser	False	303	0	0.000000

同じブラウザでも、携帯からのアクセスかどうかで異なるようなので、この2つも交差特徴量として入力します

モデルに入力するデータセットを作成する

以上をまとめて、機械学習モデルに入力するデータセットを作成します

%%bigquery --project $PROJECT 
CREATE OR REPLACE TABLE google_analytics.ga_dataset_feature AS
SELECT
    ML.FEATURE_CROSS(
    STRUCT(
            country,
            CAST(hour AS STRING) AS hour
    )) 
    AS countryHour,
    ML.FEATURE_CROSS(
    STRUCT(
            browser,
            isMobile
    )) 
    AS browserMobile,
    ML.BUCKETIZE(
    productPrice,
    [2, 5, 16, 17, 20, 50]
    ) AS priceBin,
    isClick
FROM  `google_analytics.ga_dataset_cleansed`

%%bigquery --project $PROJECT 

SELECT * FROM  `google_analytics.ga_dataset_feature`  LIMIT 5

	countryHour	browserMobile	priceBin
0	{'country_hour': 'Peru_9'}	{'browser_isMobile': 'Chrome_false'}	bin_1
1	{'country_hour': 'Peru_9'}	{'browser_isMobile': 'Chrome_false'}	bin_1
2	{'country_hour': 'Peru_9'}	{'browser_isMobile': 'Chrome_false'}	bin_1
3	{'country_hour': 'Peru_9'}	{'browser_isMobile': 'Chrome_false'}	bin_1
4	{'country_hour': 'Peru_9'}	{'browser_isMobile': 'Chrome_false'}	bin_1

次に、このデータセットをモデルを作成し入力します(また今度書きます)

参考文献

スケーラブルデータサイエンスデータエンジニアのための実践Google Cloud Platform
- bqのTech LeadであるValliappa Lakshmananさんの本。日本語版が最近出た
Google BigQuery: The Definitive Guide
- 同じくValliappa Lakshmananさんの本。ただし、発売は来年
ビッグデータ分析・活用のためのSQLレシピ
- ここまでできるのかというレベルでのSQLの黒魔術がいろいろのってます

BigQuery + jupyterで機械学習を行う(その2: 前処理、EDA、特徴量生成)