はじめに

今回は BigQuery ML について触れてみたいと思います！

BigQuery ML は、Googleが提供する機械学習を手軽に実行できるサービスです。

最大の特徴は、機械学習の専門知識がなくても、SQL を使うだけで複雑な機械学習プロセスを実行できる点にあります。

この記事では、公式チュートリアルに沿ってGoogle Analytics のデータを活用し、ユーザーが購入するかどうかを予測する回帰モデルを作成し、その結果を分析してみます。

データセットを作成

まず、Google Cloud コンソールで BigQuery ページに移動します。以下の手順でデータセットを作成してください。

1. プロジェクト名をクリック
2. 「データセットを作成」を選択
3. データセット ID を入力

今回はデータセット ID を bqml_tutorial とします。

また、一般公開データセットを使用するため、リージョンはUS マルチリージョン を選択してください。

回帰モデルを作成

次に、ロジスティック回帰モデルを作成します。

ロジスティック回帰モデルは、2値分類（例: 購入するか/しないか）を行うための機械学習モデルです。特徴量（モデルに入力するデータ）を基に、目的変数（今回の場合は「購入の有無」）を予測します。

BigQuery のクエリエディタを開き、以下のクエリを入力してください。

1CREATE OR REPLACE MODEL `bqml_tutorial.sample_model`
2OPTIONS(model_type='logistic_reg') AS
3SELECT
4IF(totals.transactions IS NULL, 0, 1) AS label,
5IFNULL(device.operatingSystem, "") AS os,
6device.isMobile AS is_mobile,
7IFNULL(geoNetwork.country, "") AS country,
8IFNULL(totals.pageviews, 0) AS pageviews
9FROM
10`bigquery-public-data.google_analytics_sample.ga_sessions_*`
11WHERE
12_TABLE_SUFFIX BETWEEN '20160801' AND '20170630'

このクエリは以下のような動作をします。

• CREATE OR REPLACE MODEL: 新しいモデルを作成する、または既存のモデルを上書きします。
• OPTIONS(model_type='logistic_reg'): ロジスティック回帰モデルを指定しています。
• IF(totals.transactions IS NULL, 0, 1) AS label: 購入があった場合は 1、それ以外は 0 としてラベルを設定します。このモデルの目的変数（ターゲット変数）です。
• その他の列（os, is_mobile, country, pageviews）は特徴量としてモデルに入力されます。

特徴量について

• os: ユーザーが利用しているデバイスのOS（例: Android, iOS）。
• is_mobile: ユーザーがモバイルデバイスを使用しているかどうかを示すフラグ（モバイルの場合は true）。
• country: ユーザーの所在地を表す国名（例: Japan, United States）。
• pageviews: ユーザーがセッション中に閲覧したページ数。

これらの特徴量は、ユーザーが購入する可能性に影響を与えると考えられる要素を選択しています。関係のない特徴量を含めるとノイズとなり、モデルの精度を下げる可能性があります。また、特徴量を増やすとモデル作成に要する時間が長くなり、スキャン量も増加するため注意が必要です。

bigquery-public-data.google_analytics_sample.ga_sessions_* サンプルデータセットはテーブルサフィックスで分割されているため、期間を指定してデータを選択します。全期間を対象にするとモデルの精度検証ができないので今回は 2016年8月1日から2017年6月30日までのデータを基にモデルを作成します。

クエリのプレビュー画面では、右上にスキャン量が表示されます。今回のスキャン量は24MBで、無料枠の範囲内に収まります。BigQueryのスキャン量料金は、1TBあたり約 $6.25 です。詳細については公式ドキュメントをご参照ください。

BigQuery 料金について（公式ドキュメント）

「実行」ボタンを押すとクエリが実行されます。クエリが完了するまでに数分かかります。

モデル評価結果と分析

BigQueryの「評価」タブでモデルの評価結果を確認できます。

ML.EVALUATE 関数を使用してモデルのパフォーマンスを評価します！この関数は、モデルが生成した予測値を実際のデータと比較し、ロジスティック回帰に特有の指標を含む結果を返します。

モデルの評価には、学習データで使用していない新しいデータを用います。今回は、2017年7月1日から2017年7月31日までのデータを使用して評価を行います。

評価用クエリ

1SELECT
2*
3FROM
4ML.EVALUATE(MODEL `bqml_tutorial.sample_model`, (
5SELECT
6IF(totals.transactions IS NULL, 0, 1) AS label,
7IFNULL(device.operatingSystem, "") AS os,
8device.isMobile AS is_mobile,
9IFNULL(geoNetwork.country, "") AS country,
10IFNULL(totals.pageviews, 0) AS pageviews
11FROM
12`bigquery-public-data.google_analytics_sample.ga_sessions_*`
13WHERE
14_TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))

結果指標

以下は評価結果の詳細です:

今回のサンプルデータでは「購入したユーザー」が少なく、「購入しなかったユーザー」が非常に多い傾向にあります。（実際のデータでも同様の傾向が見られるかもしれません）

その結果、精度が0.98と高い値を示していますが、これは「購入しなかった」と正しく予測する割合が高いためであり、再現率や適合率は低いままとなっています。

適合率が再現率より高いことから、このモデルは「購入した」と予測する際に慎重で、確実性を重視していることがわかります。

特徴量の調整を行うことで、モデルの再現率や適合率を改善できる可能性はありそうです。

ユーザーごとの購入数を予測

このクエリでは、ウェブサイトの訪問者ごとに予測されたトランザクション（購入）の数を集計し、最も予測購入数が多いユーザーを予想します。

クエリ

1SELECT
2  fullVisitorId,
3  SUM(predicted_label) as total_predicted_purchases
4FROM
5  ML.PREDICT(MODEL `bqml_tutorial.sample_model`, (
6    SELECT
7      IFNULL(device.operatingSystem, "") AS os,
8      device.isMobile AS is_mobile,
9      IFNULL(totals.pageviews, 0) AS pageviews,
10      IFNULL(geoNetwork.country, "") AS country,
11      fullVisitorId
12    FROM
13      `bigquery-public-data.google_analytics_sample.ga_sessions_*`
14    WHERE
15      _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))
16GROUP BY fullVisitorId
17ORDER BY total_predicted_purchases DESC
18LIMIT 10

結果

1fullVisitorId total_predicted_purchases
29417857471295131045　4
3057693500927581077　2
47420300501523012460　2
52158257269735455737　2
65073919761051630191　2
72105122376016897629　2
8489038402765684003　2
98388931032955052746　2
102969418676126258798　2
110456807427403774085　2

モデルの定期更新

Dataform やスケジュールクエリを活用することで、BigQueryモデルを定期的に実行し、精度を向上させながら効率的に分析を行うことができます。

今回は、シンプルにスケジュールクエリを使用した実装方法を紹介します。

モデルの更新後に分析用テーブルを作成したり、Looker Studio などでダッシュボードを表示したい場合、またはチームでの共同作業や複雑なデータパイプラインを管理する必要がある場合には、Dataform の利用を検討することをお勧めします。

スケジュールクエリの設定手順

1. Google Cloud コンソールで BigQuery ページに移動します。
2. クエリエディタに以下のモデル作成クエリを入力します:

1
2CREATE OR REPLACE MODEL `bqml_tutorial.sample_model`
3OPTIONS(model_type='logistic_reg') AS
4SELECT
5  IF(totals.transactions IS NULL, 0, 1) AS label,
6  IFNULL(device.operatingSystem, "") AS os,
7  device.isMobile AS is_mobile,
8  IFNULL(geoNetwork.country, "") AS country,
9  IFNULL(totals.pageviews, 0) AS pageviews
10FROM
11  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
12WHERE
13  _TABLE_SUFFIX BETWEEN '20160801' AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())

3. クエリエディタの上部にある「スケジュール設定」ボタンをクリックします。
4. スケジュール設定画面で以下のオプションを指定します:
   • 名前: クエリの目的を示す名前（例: 定期モデル更新）。
   • 頻度: 更新の間隔を選択（毎日、毎週、またはカスタムスケジュール）。
   • 開始時間: クエリの実行を開始する時間を設定。
5. 「スケジュールを作成」ボタンをクリックして保存します。

まとめ

今回は 今回はbigquery MLについて調べました。

Web アプリケーションで Google Analytics を使用するケースは多い一方で、BigQuery にデータを出力し、AI を活用して予測を行う事例は限られているかもしれません。

しかし、クライアントに予測結果を提示し、それを基にした分析まで行えれば、大きな付加価値を提供できると思います！

また、BigQuery ML では回帰分析だけでなく、分類モデルやクラスタリングなど、さまざまな手法がサポートされています。

実際に、自分が参画しているプロジェクトでは、BigQuery ML を使って商材名の揺れを正規化する「名前寄せ」を行っており、精度も非常に高かったとのことです。

今後もさらに活用範囲が広がる可能性を感じています！