BigQueryで時系列データから異常部位検出 (k近傍法)
概要
手法についてはこちらの異常部位検出の解説部分が、分かりやすかったです。
www.albert2005.co.jp
用途としては、ネット広告について言うと、配信全体の異常なクリック増加・減少や、特定のキーワードの異常検知に使えると考えている。
具体的に何をやっているかと言うと、例えばある日の異常度として、その日の直近7日と、それ以前の連続した7日との距離を計算し、距離の最小値を出している。
※異常検知にはいくつか手法がありますが、k近傍法を使おうと思ったのは、BigQueryで実装が簡単そうだったからです。
なお、今回はBigQueryのUDFを使っているが、UDFについての詳細な解説はしない。
cloud.google.com
今回のゴール
異常度を計算し、データポータルで可視化
アウトプットのイメージ
青がデータで、赤が計算された異常度。左始まりの時系列データとして処理している。左の方、即ち時系列の最初の方で異常度が高く出ているのは、計算上やむを得ない部分だが、真ん中あたりで、青いグラフがそれまでと異なる動きをした際に、それに追従して赤の異常度も高くなっている。
今回のキーワード
- UDF
- ARRAY_AGG関数
- ウィンドウ関数
- サンプルデータ生成
コードサンプル
#StandardSQL CREATE TEMPORARY FUNCTION calc_min_distance(part_arr ARRAY<FLOAT64>,part_arr_arr ARRAY<STRUCT< inner_arr ARRAY<FLOAT64>>>) RETURNS FLOAT64 LANGUAGE js AS """ function calc_distance(x,y) { if(x.length != y.length) { return null } else { var d = 0 for(var i = 0;i<x.length;i++){ d += Math.pow(x[i] - y[i],2) } return Math.sqrt(d) } } var ret = null if(part_arr_arr == null){ return ret } for(var i = 0 ; i < part_arr_arr.length;i++){ var distance = calc_distance(part_arr,part_arr_arr[i].inner_arr) if(distance!=null){ if(ret==null || ret > distance){ ret = distance } } } return ret """; WITH sample1 AS ( #およそ周期が7のデータを生成する SELECT x AS id, 3 + 10 * SIN( x * 3.14 /3.5) + RAND() AS x FROM UNNEST(GENERATE_ARRAY(0,100)) AS x ), sample2 AS ( #異常値を載せたデータを作成する SELECT *, IF(id = 50,x + 10,x) AS abnomal_x FROM sample1 ), ##################### #k近傍法による異常検知の実装 ##################### t1 AS ( #部分系列の生成 SELECT *, ARRAY_AGG(abnomal_x) OVER(ORDER BY id asc ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS part_arr # 6 PRECEDINGを、使用したい系列の長さにより変更してください FROM sample2 ), t2 AS ( #比較したいウィンドウで、部分系列の系列を生成します。 SELECT *, ARRAY_AGG(STRUCT(part_arr)) OVER(ORDER BY id asc ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING) AS part_arr_arr # 30 PRECEDINGを、比較したいウィンドウサイズにより変更してください FROM t1 ) SELECT id, abnomal_x, calc_min_distance(part_arr,part_arr_arr) AS min_distance FROM t2
解説
上から順に見ていく。
UDF部分
まず、UDFで異常度の計算部分する関数を定義している。javaScriptで書いているおり詳細は省略するが、ざっくりした処理のイメージは下記である。
ざっくりした処理のイメージ
引数として、part_arrとpart_arr_arrを受け取る
part_arr = [1,2,3,2,1,4,3]
part_arr_arr = [
{inner_arr : [2,3,4,5,6,4,1]},
{inner_arr : [3,4,5,6,4,1,5]},
{inner_arr : [4,5,6,4,1,5,2]},
{inner_arr : [5,6,4,1,5,2,3]},
{inner_arr : [6,4,1,5,2,3,3]},
{inner_arr : [4,1,5,2,3,3,2]},
{inner_arr : [1,5,2,3,3,2,4]},
{inner_arr : [5,2,3,3,2,4,3]},
...
]
part_arr([1,2,3,2,1,4,3])と、part_arr_arrの1つめの要素の配列([2,3,4,5,6,4,1])を比較、距離はd1,
part_arr([1,2,3,2,1,4,3])と、part_arr_arrの2つめの要素の配列([3,4,5,6,4,1,5])を比較、距離はd2,
...
距離の最小値は dn
dnを関数の処理結果として返す※なお、part_arr_arr内の要素をオブジェクトとして格納しているのは、BigQueryがネストしたARRAYを扱えないためである。
ARRAY<ARRAY<INT64>>
（サポート対象外）これは無効な型の宣言です。マルチレベル ARRAY の作成方法を調べるためにこのページにアクセスした場合に備え、ここに記載しておきます。ARRAY には直接 ARRAY を含めることはできません。代わりに次の例をご覧ください。
ARRAY<STRUCT<ARRAY<INT64>>>
64 ビット整数の ARRAY からなる ARRAY。ARRAY には直接他の ARRAY を含めることができないため、2 つの ARRAY の間に STRUCT が挿入されています。
サブクエリsample1,sample2
sample1 : サンプルデータを生成しています。周期がおよそ7になるようなデータを生成しています。
sample2 : sample1で生成したデータの真ん中あたりに、異常値を乗せています。アウトプットのイメージ画像の、青い線の真ん中あたりのはねているところです。
サブクエリt1
ARRAY_AGGをウィンドウ関数で使って、直近7日(7行)の部分配列を、各行に対して生成しています(part_arr)。
cloud.google.com
サブクエリt2
t1で生成した部分配列をSTRUCTに格納したものを、再度ARRAY_AGGをウィンドウ関数で使って、その行より前の直近30行のpart_arrを更に配列に格納します(part_arr_arr)。
アウトプット部分
サブクエリt1,t2で生成したpart_arrとpart_arr_arrを、UDFに渡し、異常度として距離の最小値を得ます。
クエリ結果画面で、データポータルで調べるを押し、グラフを作成します。
異常です。
BigQueryのSTRUCTを活用して、複数列をまとめて処理する
概要
STRUCTは慣れると便利です。今回は複数列をまとめて処理する流れとサンプルクエリを書きます。
例
SQLを書いて昨対比を出すことを考える。
あるタイミングの実績集計に、昨年の実績集系列もあるようなものを作成し、成長率などを計算したい。
例えばwebサイトのセッションについて、以下のようなデータを作成する。
| month | session | session_1year_ago |
|---|---|---|
| 2017-08-01 | 216 | 136 |
| 2017-07-01 | 213 | 126 |
| 2017-06-01 | 224 | 118 |
| 2017-05-01 | 208 | 116 |
| 2017-04-01 | 208 | 100 |
| 2017-03-01 | 183 | 94 |
| 2017-02-01 | 208 | 81 |
| 2017-01-01 | 182 | null |
| 2016-12-01 | 172 | null |
| 2016-11-01 | 160 | null |
| 2016-10-01 | 155 | null |
| 2016-09-01 | 146 | null |
| 2016-08-01 | 136 | null |
| 2016-07-01 | 126 | null |
| 2016-06-01 | 118 | null |
| 2016-05-01 | 116 | null |
| 2016-04-01 | 100 | null |
| 2016-03-01 | 94 | null |
| 2016-02-01 | 81 | null |
おおよその作成の流れとしては、
- 日毎あるいは月ごとの集計をサブクエリで作成
- 1で作成したサブクエリの日付を1年後に修正したサブクエリを作成
- 1と2をLEFT JOIN
- 2の方のカラム名を修正
というふうにすれば、目的のデータを作成できる。
あるいは、LAGやLEADなどのウィンドウ関数の利用によっても実現可能である。
この時カラム数の増加に応じて、4のカラム名の修正が手間になる。
また、ウィンドウ関数を使用した場合、カラムごとにウィンドウ関数を書く必要がある。
こんな感じである。
#月次データで、1年前のデータを出す LAG(session,12) OVER(order by month ASC) AS session_1year_ago, LAG(user,12) OVER(order by month ASC) AS user_1year_ago, LAG(pv,12) OVER(order by month ASC) AS pv_1year_ago, LAG(transaction,12) OVER(order by month ASC) AS transaction_1year_ago,
BigQueryではSELECT句にテーブル名だけを書くと、テーブル名のSTRUCT型の列を生成してくれる。
今回はこれを活用し、構造が同じ2つのSTRUCTを並べることで、目標のデータを作成する。
クエリ例
いつものようにGoogle Analyticsのサンプルデータセットを使う。
なお、データが1年分と少ししかないため、昨年の代わりに昨月の実績集計を出している。
#StandardSQL WITH base AS ( SELECT parse_DATE("%Y%m%d", CONCAT(SUBSTR(_TABLE_SUFFIX,1,6),"01")) AS _date, #_TABLE_SUFFIXを20190124 ->20190101 に変換し、日付型に変換 COUNT(*) AS session, COUNT(DISTINCT fullVisitorId ) AS user, SUM(totals.pageviews) AS pageviews, SUM(totals.bounces) AS bounces, SUM(totals.transactions) AS transactions, SUM(totals.transactionRevenue) AS transactionRevenue, SUM(totals.newVisits) AS newVisits FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` GROUP BY 1 ), add_1month AS ( SELECT DATE_ADD(_date, INTERVAL 1 MONTH) AS _date, #1ヶ月後の日付に変換 #昨年対比にする場合は、ここを以下のようにする #DATE_ADD(_date , INTERVAL 1 YEAR) AS _date * EXCEPT(_date) FROM base ) SELECT base, add_1month AS previous_month FROM base LEFT JOIN add_1month USING (_date) ORDER BY base._date DESC
ウィンドウ関数を使用する場合は、以下のように1行だけ記述すれば良くなる。(※以下のようにLAGが使えるのは、すべての行が連続したデータである場合のみ。)
#standardSQL WITH base AS ( SELECT parse_DATE("%Y%m%d", CONCAT(SUBSTR(_TABLE_SUFFIX,1,6),"01")) AS _date, #_TABLE_SUFFIXを20190124 ->20190101 に変換し、日付型に変換 COUNT(*) AS session, COUNT(DISTINCT fullVisitorId ) AS user, SUM(totals.pageviews) AS pageviews, SUM(totals.bounces) AS bounces, SUM(totals.transactions) AS transactions, SUM(totals.transactionRevenue) AS transactionRevenue, SUM(totals.newVisits) AS newVisits FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` GROUP BY 1 ) SELECT base, LAG(base) OVER(ORDER BY base._date ASC) AS previous_month FROM base ORDER BY base._date DESC
クエリ実行結果をデータポータルで以下のように可視化してみる。例えばセッションのMoMは以下のようになる。
以上です。
BigQueryでエクセルのLENBを計算する(半角を1、全角を2として文字数を数える)
例えばAdwords(Google広告)へのkwや広告文の入稿データの作成を行う場合、文字数のカウントをエクセルのLENBで行うことが多いと思う。BigQuery上でも同様の文字数カウントを実現する。
LENBを出したいカラム名がkeywordの場合、以下のように書けば良い。
LENGTH(keyword)+ LENGTH(REGEXP_REPLACE(keyword,r'[ -~｡-ﾟ]',''))
keywordの文字数(LENGTH(keyword))
と、
半角文字を削除(REGEXP_REPLACE(keyword,r'[ -~｡-ﾟ]',''))したあとに残った文字(=全角文字)の数
を足している。
確認用sql
#StandardSQL WITH test_data AS ( SELECT * FROM UNNEST( [ "あいうeo", "ｶき く", "keko", "Google 広告" ]) AS keyword ) SELECT keyword, LENGTH(keyword), #単純な文字数 REGEXP_REPLACE(keyword,r'[ -~｡-ﾟ]','') , #半角を空文字に置換 LENGTH(REGEXP_REPLACE(keyword,r'[ -~｡-ﾟ]','')), #半角を空文字に置換した文字列の長さ LENGTH(keyword) + LENGTH(REGEXP_REPLACE(keyword,r'[ -~｡-ﾟ]','')) # 半角を空文字に置換して、残った文字数を更に足す。 AS lenB FROM test_data
BigQuery ML ロジスティック回帰で、次のセッションの注文を予測する
概要
以下のチュートリアルをやってみた。
Getting started with BigQuery ML for data analysts | BigQuery | Google Cloud
これを少しアレンジして、
Google Analyticsのデータから、次のセッションでcv(transaction)が発生するか、BigQuery ML のロジスティック回帰で 予測を行ってみる。
モチベーションとしては、例えばリマーケティング広告や販促メールの送付先を検討するには、次のセッションでコンバージョンが発生しそうかどうかに意味があると考えたためです。
ステップ1: 準備
変数として追加出来そうなデータを探索する
チュートリアルで使用された変数以外に、google_analytics_sample で使用できそうなデータを探す。ぱっと思いつくのは
- 特定のページを見たかどうか(あるいは見た回数)
- 特定のイベントが発生したかどうか(あるいは発生した回数)
など
ページについては、サイト構造をよく理解していないので今回は使用しない。
発生しているイベントを調べてみる。
#StandardSql SELECT hits.eventInfo.eventAction, hits.eventInfo.eventCategory, hits.eventInfo.eventLabel, COUNT(*) AS event_cnt FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` AS ga, ga.hits AS hits GROUP BY 1, 2, 3 ORDER BY 4 DESC
結果がこちら
EventActionにAdd to Cart などそれっぽいものがある。
他のEventActionも含め、セッションで特定のイベントが発生したがどうか、bool型のカラムを作成することにする。
ステップ2: モデルを作成する
次のセッションでtransactionが発生するかどうかが今回の目的変数なので、LEAD関数を使用する。
Standard SQL Functions & Operators | BigQuery | Google Cloud
また、今回は次のセッションが発生しなかったユーザーは除外した。
モデルの格納先は、bqml_tutorial.model001_nextSessionTransaction。
#standardSQL CREATE MODEL `bqml_tutorial.model001_nextSessionTransaction` OPTIONS(model_type='logistic_reg') AS ( SELECT #次のセッションが発生したセッションに絞る * FROM ( SELECT #今回の目的変数。次のセッションでtransactionが発生するかどうか。 LEAD(IF(totals.transactions >0,1,0 )) OVER(partition by fullVisitorId ORDER BY visitStartTime) AS label, #以下が組成。このセッションでの行動から作成する。 IF(totals.transactions IS NULL, 0, 1) AS transaction, IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(geoNetwork.country, "") AS country, IFNULL(totals.pageviews, 0) AS pageviews, IFNULL(totals.bounces, 0) AS bounces, IFNULL(totals.timeOnSite , 0) AS timeOnSite, IFNULL(totals.newVisits , 0) AS newVisits, #session内で、特定のeventActionが存在するかどうかを判定 EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Quickview Click") AS is_qc, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Add to Cart") AS is_ac, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Product Click") AS is_pc, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Remove from Cart") AS is_rc, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Promotion Click") AS is_prmc FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga WHERE _TABLE_SUFFIX BETWEEN '20160801' AND '20170630' ) WHERE label IS NOT NULL )
ステップ3: モデルを確認する
bqml_tutorial データセットに移動し、先ほど作成したモデル model001_nextSessionTransaction を選択肢する。
損失が徐々に減っていっていることが確認できる。
係数のweightを確認する。
また、ML.WEIGHTS関数を使用して、各係数に振られている重みを確認できる。カテゴリ変数と数値変数のweightをまとめ、weightが大きい順に並べてみる。
WITH base AS ( SELECT * FROM ML.WEIGHTS(MODEL `bqml_tutorial.model001_nextSessionTransaction`)), num_var AS ( #数値変数のみ SELECT processed_input, "---" AS category, weight FROM base WHERE weight IS NOT NULL ), cat_var AS ( #カテゴリ変数のみ SELECT processed_input, category_weights.category, COALESCE(base.weight, category_weights.weight) AS weight FROM base, base.category_weights AS category_weights ) SELECT * FROM num_var UNION ALL ( SELECT * FROM cat_var) ORDER BY weight DESC
結果がこちら。
上の方だけ見ると、
というセッションについて、次のセッションのtransaction発生率が高いと言うモデルになっているみたい。
ステップ4: モデルを評価する
ML.EVALUATE 関数を使用して、性能を評価する。
#standardSQL SELECT * FROM ML.EVALUATE(MODEL `bqml_tutorial.model001_nextSessionTransaction`, ( SELECT #次のセッションが発生したセッションに絞る * FROM ( SELECT #今回の目的変数。次のセッションでtransactionするかどうか。 LEAD(IF(totals.transactions >0,1,0 )) OVER(partition by fullVisitorId ORDER BY visitStartTime) AS label, #以下が組成。このセッションでの行動から作成する。 IF(totals.transactions IS NULL, 0, 1) AS transaction, IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(geoNetwork.country, "") AS country, IFNULL(totals.pageviews, 0) AS pageviews, IFNULL(totals.bounces, 0) AS bounces, IFNULL(totals.timeOnSite , 0) AS timeOnSite, IFNULL(totals.newVisits , 0) AS newVisits, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Quickview Click") AS is_qc, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Add to Cart") AS is_ac, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Product Click") AS is_pc, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Remove from Cart") AS is_rc, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Promotion Click") AS is_prmc FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170801' ) WHERE label IS NOT NULL ) )
結果がこちら
precisionがNaN、recallが0.0 となっている。
これはすべてのデータについて、目的変数(次のセッションでtransactionが発生するかどうか)が最終的には0で予測されているためです。
一方で、roc_aucは0.822となっている。目的変数(次のセッションでtransactionが発生するかどうか)が1である確率が高いデータ順に並べてデータを取り出すとき、実際にtransactionが発生しているデータを(無作為に取り出すより)効率よく取り出すことができている。
なお、各カラムの詳細については以下をご確認ください。
https://cloud.google.com/bigquery/docs/bigqueryml-analyst-start#run_the_mlevaluate_query
また、transactionするか(=1)しないか(=0)の2値分類ではなく、transactionする確率、しない確率は、以下のクエリで確認できる。predicted_label_probs.prob に、それぞれのラベルの確率が格納されている。
SELECT * FROM ML.PREDICT(MODEL `bqml_tutorial.model001_nextSessionTransaction`, ( SELECT #次のセッションが発生したセッションに絞る * FROM ( SELECT #今回の目的変数。次のセッションでtransactionするかどうか。 LEAD(IF(totals.transactions >0,1,0 )) OVER(partition by fullVisitorId ORDER BY visitStartTime) AS label, #以下が組成。このセッションでの行動から作成する。 IF(totals.transactions IS NULL, 0, 1) AS transaction, IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(geoNetwork.country, "") AS country, IFNULL(totals.pageviews, 0) AS pageviews, IFNULL(totals.bounces, 0) AS bounces, IFNULL(totals.timeOnSite , 0) AS timeOnSite, IFNULL(totals.newVisits , 0) AS newVisits, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Quickview Click") AS is_qc, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Add to Cart") AS is_ac, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Product Click") AS is_pc, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Remove from Cart") AS is_rc, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Promotion Click") AS is_prmc FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170801' ) WHERE label IS NOT NULL ) )
ステップ5: ML.ROC_CURVE 関数を実行し、閾値を調整する
ステップ4でroc_aucが0.822であることを確認した。これはこのモデルを使えば、次のセッションでtransactionが発生しそうなセッションを効率よく抽出できることを意味する。
ML.ROC_CURVE関数を実行し、実行結果をデータポータルで調べてみる。
The ML.ROC_CURVE Function | BigQuery | Google Cloud
#standardSQL SELECT * FROM ML.ROC_CURVE (MODEL `bqml_tutorial.model001_nextSessionTransaction`, ( SELECT #次のセッションが発生したセッションに絞る * FROM ( SELECT #今回の目的変数。次のセッションでtransactionするかどうか。 LEAD(IF(totals.transactions >0,1,0 )) OVER(partition by fullVisitorId ORDER BY visitStartTime) AS label, #以下が組成。このセッションでの行動から作成する。 IF(totals.transactions IS NULL, 0, 1) AS transaction, IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(geoNetwork.country, "") AS country, IFNULL(totals.pageviews, 0) AS pageviews, IFNULL(totals.bounces, 0) AS bounces, IFNULL(totals.timeOnSite , 0) AS timeOnSite, IFNULL(totals.newVisits , 0) AS newVisits, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Quickview Click") AS is_qc, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Add to Cart") AS is_ac, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Product Click") AS is_pc, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Remove from Cart") AS is_rc, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Promotion Click") AS is_prmc FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170801' ) WHERE label IS NOT NULL ) )
例えば閾値を 0.03837951 とすると、次のセッションでtransactionするセッションの82%、transactionしないセッションの31%が抽出される。
ステップ6: 採用した閾値で予測をし直す
ML.PREDICTを使って、再度予測をする。
なお、2018-12-31現在日本語ページには書いていないが、ML.PREDICT関数には引数として閾値(threshold)を渡すことができる。また、閾値(threshold)のデフォルト値は0.5となっているらしい。
The ML.PREDICT Function | BigQuery | Google Cloud
#standardSQL SELECT * FROM ML.PREDICT (MODEL `bqml_tutorial.model001_nextSessionTransaction`, ( SELECT #次のセッションが発生したセッションに絞る * FROM ( SELECT #今回の目的変数。次のセッションでtransactionするかどうか。 LEAD(IF(totals.transactions >0,1,0 )) OVER(partition by fullVisitorId ORDER BY visitStartTime) AS label, #以下が組成。このセッションでの行動から作成する。 IF(totals.transactions IS NULL, 0, 1) AS transaction, IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(geoNetwork.country, "") AS country, IFNULL(totals.pageviews, 0) AS pageviews, IFNULL(totals.bounces, 0) AS bounces, IFNULL(totals.timeOnSite , 0) AS timeOnSite, IFNULL(totals.newVisits , 0) AS newVisits, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Quickview Click") AS is_qc, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Add to Cart") AS is_ac, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Product Click") AS is_pc, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Remove from Cart") AS is_rc, EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Promotion Click") AS is_prmc FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170801' ) WHERE label IS NOT NULL ) , STRUCT(0.03837951) #先程採用した閾値をML.EVALUATEの引数として渡す )
以上です。
UNNESTせずに特定のページビューやイベントが発生したセッションのみ抽出する
概要
特定の要素を持つ配列全体を、EXISTS句を使って抽出します。
BigQuery Exportのデータなど、構造化されたBigQueryのデータを処理する際、元の構造を保ったまま特定の条件にあった行だけを抽出したいことがあるかと思います。
例
- 特定のページビューが発生したセッションの直帰率を調べたい
- 特定のイベントが発生したセッションのコンバージョン率を調べたい
今回はGoogleAnalyticsのサンプルデータを使用して書いていきます。
support.google.com
/google redesign/apparel/mens/mens t shirtsを閲覧したセッションのみ抽出する
#standardSQL
WITH base AS (
SELECT
*
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga
WHERE
_TABLE_SUFFIX >= "20170701"
#以下が絞り込みを行っている部分です。
AND EXISTS (
SELECT
h.page.pagePath
FROM
UNNEST(ga.hits) AS h
WHERE
h.type = "PAGE"
AND h.page.pagePath = "/google redesign/apparel/mens/mens t shirts" ) )
SELECT
*
FROM
base確認のため、サブクエリbaseに対して、fullvisitorId,visitId,hitNumber順に、hits.page.pagePath を見てみましょう。
#StandardSQL
WITH base AS (
SELECT
*
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga
WHERE
_TABLE_SUFFIX >= "20170701"
#以下が絞り込みを行っている部分です。
AND EXISTS (
SELECT
h.page.pagePath
FROM
UNNEST(ga.hits) AS h
WHERE
h.type = "PAGE"
AND h.page.pagePath = "/google redesign/apparel/mens/mens t shirts" ) )
SELECT
fullVisitorId,
visitId,
h.hitNumber,
h.page.pagePath
FROM
base AS ga,
ga.hits AS h
WHERE
h.type = "PAGE"
ORDER BY
1,
2,
3 ASC確かに、今回絞り込みたかったpagePathを含むセッションがそのまま残っているようです。
これをもとに、 `/google redesign/apparel/mens/mens t shirts`を閲覧したセッションの日毎の直帰率を調べるには、以下のように書けばよいです。
WITH
base AS (
SELECT
*
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga
WHERE
_TABLE_SUFFIX >= "20170701"
#以下が絞り込みを行っている部分です。
AND EXISTS (
SELECT
h.page.pagePath
FROM
UNNEST(ga.hits) AS h
WHERE
h.type = "PAGE"
AND h.page.pagePath = "/google redesign/apparel/mens/mens t shirts" ) )
SELECT
date,
COUNT(*) AS session,
SUM(totals.bounces) AS bounce_cnt,
SAFE_DIVIDE(SUM(totals.bounces),COUNT(*)) AS bounce_rate,
FORMAT("%3.2f %%",SAFE_DIVIDE(SUM(totals.bounces),COUNT(*) ) * 100 ) AS bounce_rate_formatted
FROM
base
GROUP BY
1
ORDER BY
1 ASC同様に、eventAction Add to Cartイベントが発生したセッションを抽出してみましょう。
以下のように書けます。
#standardSQL
WITH base AS (
SELECT
*
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga
WHERE
_TABLE_SUFFIX >= "20170701"
#以下が絞り込みを行っている部分です。
AND EXISTS (
SELECT
h.page.pagePath
FROM
UNNEST(ga.hits) AS h
WHERE
h.type = "EVENT"
AND h.eventInfo.eventAction = "Add to Cart" ))
SELECT
*
FROM
base以上です。
BigQueryで全角英数を半角英数に変換する
以下の流れで処理しています。
- TO_CODE_POINTS 関数を使って文字列をコードポイントの配列に変換します。
- コードポイント配列をUNNESTします。
- コードポイントが全角英数にマッチする場合、対応する半角英数のコードポイントになるようにずらします。
- コードポイントを配列にまとめ直し、CODE_POINTS_TO_STRING 関数を使って配列を文字列に戻します。
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#to_code_points
クエリ
#StandardSQL
WITH
sample AS (
#サンプルデータを用意
SELECT
dat
FROM
UNNEST( ["ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ","ＡＢＣＤEFGH１２３４","ＵＳA","２９８0０円"] ) AS dat )
SELECT
dat,
CODE_POINTS_TO_STRING( ARRAY(
SELECT
CASE
# 全角大文字を半角大文字に変換
WHEN code_point_arr BETWEEN 65313 AND 65338 THEN code_point_arr-65248
# 全角小文字を半角小文字に変換
WHEN code_point_arr BETWEEN 65345 AND 65370 THEN code_point_arr-65248
# 全角数字を半角数字に変換
WHEN code_point_arr BETWEEN 65296 AND 65305 THEN code_point_arr-65248
ELSE code_point_arr END
FROM
#
# TO_CODE_POINTS の引数に、半角変換を行いたい列を指定する
#
UNNEST( TO_CODE_POINTS(dat) )AS code_point_arr )) as formatted_dat
FROM
sample以上です。
BigQueryでサイト内検索データや検索語句レポートから、よく使われる単語を抽出する
想定する場面
BigQueryに格納されている、サイト内検索のデータや、Adwordsの検索クエリレポートなどから、よく使われる単語を抽出します。
例えば、
「Big Query run」
「j Query run」
という2行のデータがある時
| word | count |
|---|---|
| Query | 2 |
| run | 2 |
| j | 1 |
| Big | 1 |
のように、各単語に分解してその出現回数を数えます。
やり方
今回は、split関数を使い、単語を空白で分割してarrayに変換し、unnestした後に数え上げます。
以下のデータを用意します。
| searchTerm | click |
|---|---|
| j query | 4 |
| j リーグ | 6 |
| adwords | 8 |
| adwords blog | 10 |
| google analytics | 3 |
| sql | 4 |
サブクエリを使用してデータを用意するやり方はこちらで解説しています。
sem-aa-bq.hatenablog.com
完成したクエリはこちら
WITH temp_table AS (
SELECT
*
FROM
UNNEST(
(
SELECT
ARRAY<STRUCT<searchTerm STRING,click INT64>>[
("google big query",2),
("j query",4),
("j リーグ",6),
("adwords",8),
("adwords blog",10),
("google analytics",3),
("sql",4)
] AS sample_data_arr
)
)
),
splitted AS (
#このサブクエリで単語分割
SELECT
split(searchTerm," ") as splitted_st_arr
FROM
temp_table )
#出現回数で集計
SELECT
splitted_st,
COUNT(*) AS cnt
FROM
splitted CROSS JOIN UNNEST(splitted.splitted_st_arr) AS splitted_st
GROUP BY 1
ORDER BY 2 descなお、上記のクエリで使用したデータで、click数で集計するということもできます。
例えば、adwordsという単語は、"adwords"というsearchTermで8回、"adwords blog"で10回clickが発生しているので、集計値として18を出したいです。
これは広告屋検索の部分一致除外を検討する際のデータとして役にたつかと思います。
click数で集計するsqlは下記の通りです。
WITH temp_table AS (
SELECT
*
FROM
UNNEST(
(
SELECT
ARRAY<STRUCT<searchTerm STRING,click INT64>>[
("google big query",2),
("j query",4),
("j リーグ",6),
("adwords",8),
("adwords blog",10),
("google analytics",3),
("sql",4)
] AS sample_data_arr
)
)
),
splitted AS (
#このサブクエリで単語分割
SELECT
split(searchTerm," ") as splitted_st_arr,
*
FROM
temp_table )
#click回数で集計
SELECT
splitted_st,
SUM(click) AS click
FROM
splitted CROSS JOIN UNNEST(splitted.splitted_st_arr) AS splitted_st
GROUP BY 1
ORDER BY 2 desc以上です。
