BigQueryではじめるWeb解析入門 ④〜BQMLでGA4の訪問者数を予測してみる〜

Published in

google-cloud-jp

11 min readAug 22, 2022

このシリーズではSQL初学者の方向けに、BigQueryでGoogle Analytics4(以下 GA4)のデータを使ってWeb分析を行う方法をご紹介してます。

前回は、GA4 の基本的な指標としてイベント件数をさまざまな切り口で参照してみました。今回は、BQML(BigQuery ML) という BigQuery の機能で、SQL だけで機械学習モデルを作成し、将来の訪問者数を予測してみましょう。

日別の訪問者数を確認

以下のSQLを実行して、日別ユーザー数を参照してみましょう。

SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp)) AS parsed_date,
  COUNT(DISTINCT user_pseudo_id) AS visitors
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE event_name = 'session_start'
GROUP BY 1
ORDER BY 1

このSQLでは、timestamp形式のデータを日付になおすためDATEという関数を使っています。また、3行目のCOUNT DISTINCTでuser_pseudo_idのユニークな数を集計することで訪問者数を計測しています。6行目のGROUP BY 1は1番目にSELECTした列、つまりparsed_date列ごとに集計するという意味です。ORDER BY 1は同様にparsed_date列でソートしています。

結果が表示されたら、「データを探索」というアイコンから「Looker Studioで調べる」という項目を選択してください。これにより、Looker Studioという無償のBIツールを連携してSQLの結果を簡単に可視化することができます。

Looker Studioの画面が開いたら、右側に表示されているチャートをクリックします。

画面右上の「グラフ」という項目の下矢印を展開すると、グラフのタイプを変更することができます。ここでは時系列グラフを選択してみます。

画面右側のディメンションにparsed_dateを選択します。ここで、もし内訳ディメンションという項目に何らかの値が入っていたら、削除してください。

このようにディメンションに日付の項目を選択し、指標にvisitorsを選択すると約3ヶ月分の日別ユーザー数を可視化するチャートが参照できます。このチャートを見ると、クリスマス前に需要が高まるものの、クリスマス当日前後は訪問者が減っていることが確認できます。また、数の増減には一定の周期があるようにもみられますが、週末や平日で参照数に差があるためと考えられます。

BQMLで時系列モデルを作成する

次に、このデータを使って時系列モデルを作成します。BigQueryコンソール左のメニューでプロジェクト名の右にある三つの点から「データセットを作成」を選択し、「bqml」というデータセットを作成してから次のSQLを実行してみてください。

CREATE OR REPLACE MODEL bqml.arima_model
OPTIONS
(model_type = 'ARIMA_PLUS',
  time_series_timestamp_col = 'parsed_date',
  time_series_data_col = 'visitors',
  auto_arima = TRUE,
  data_frequency = 'AUTO_FREQUENCY',
  decompose_time_series = TRUE
) AS
SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp)) AS parsed_date,
  COUNT(DISTINCT user_pseudo_id) AS visitors
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE event_name = 'session_start'
GROUP BY 1
ORDER BY 1

ここでは、BQMLのCREATE MODEL 句で bqml.arima_model という名前の時系列モデルを作成してトレーニングします。具体的には、OPTIONS(model_type=’ARIMA’_PLUS, time_series_timestamp_col=’parsed_date’, …) で ARIMA_PLUS と呼ばれる時系列モデルを作成しています。上記のグラフで横軸に指定されていた日付をtime_series_tmestamp_colに、time_series_data_colに予測対象となる毎日の訪問者数（時系列）を指定しています。

auto_arima=TRUE というパラメータが指定されているため、auto.ARIMA というアルゴリズムによってハイパーパラメータが自動的に調整されます。ハイパーパラメーターとは、機械学習モデルをどのように学習するかという学習効率やモデルの品質に影響するオプションのようなものと考えてください。このアルゴリズムが多数の候補モデルを学習し、AIC という指標が最も低い最適なモデルを選択します。

また、上記のクエリ上には書かれていませんが、デフォルトで指定されているdata_frequency=’AUTO_FREQUENCY’ というオプションによって、入力時系列の季節性が自動的に推定されます。例えば、週末に明らかに訪問者が増える、月毎に変動がある、というようにデータがどのような頻度で変わるかが明らかな場合はこのオプションを指定します。

モデルの評価指標を調べる

モデルを作成した後、ML.EVALUATE 関数を使用すると、ハイパーパラメータの調整中に評価されたすべての候補モデルの評価指標を確認できます。次の SQL の FROM 句では、モデル bqml.arima_model に ML.EVALUATE 関数を使用しています。

SELECT
*
FROM
ML.EVALUATE(MODEL `bqml.arima_model`)

結果には次の列が含まれます。

non_seasonal_p
non_seasonal_d
non_seasonal_q
has_drift
log_likelihood
AIC
variance
seasonal_periods

ARIMA(時系列) モデルは、nonseasonal{p,d,q} と has_driftというハイパーパラメータで定義されています。通常、統計的手法で時系列分析を行う場合、これらの p,d,q というパラメータを人が指定する必要がありますが、BQML の場合これらを自動的に決定してくれるという利点があります。

また、その後の 3 つの指標（log_likelihood、AIC、variance）は ARIMA モデルを最適化する際のパラメータです。BQML はさまざまな p,d,q の組み合わせでモデルの様々なパターンを学習し、この中で、 AIC(赤池情報量基準) と呼ばれるパラメータが低いものが最も最適なモデルとして選択されることになります。ここでは、一番最初の行のモデルが保存され、BQML で予測を行う際に使用されます。

予測結果を可視化する

学習したモデルで時系列データの予測をしてみましょう。以下のSQLではML.EXPLAIN_FORECASTというExpainable AIという機能で説明性を付与した予測結果を取得しています。

#standardSQL
SELECT
 *
FROM
 ML.EXPLAIN_FORECAST(MODEL bqml.arima_model,
                     STRUCT(14 AS horizon, 0.8 AS confidence_level))

ML.EXPLAIN_FORECAST の中で以下の指定をしています。
- MODEL: 作成したモデル名
- STRUCT : 構造体としてデータを呼び出す関数
- horizon: 14 日(2週間)予測する期間
- conficence_level (信頼度) : 80% で予測区間を生成
予測精度を厳しくしたい場合はこの値を0.9、0.95のようにより大きな値にします。

結果が表示されたら、「データを探索」というアイコンから「Looker Studioで調べる」という項目で、再度結果を可視化します。Looker Studioの画面が開いたら、先ほどと同様、右側に表示されているチャートをクリックし、画面右のグラフメニューから時系列グラフを選択します。

その後、以下のようにディメンションに時系列の識別子となるtime_series_timestamp(日付)を選びます。次に、指標に以下の内容を選択してください。

time_series_data: 時系列のデータ(ここでは日別訪問数)
prediction_interval_lower_bound: 予測結果の上限
prediction_interval_upper_bound: 予測結果の下限
trend: 時系列データの長期的な増減（トレンド）
seasonal_period_weekly: 1週間の周期性

また、今回 null 値は表示させないため、「スタイル」タブを開き、「全般」の項目で「欠落データ」に「線を途切れさせる」を選択してください。

左側の表は今回使わないため、削除して右側のチャートの幅を広げてみると以下のような表示になります（色などは異なる場合があります）。

今回は約3ヶ月分の学習データしかないため、この結果だけから長期的なトレンドや季節性をとらえることが難しいですが、曜日の周期性は見てとることができます。また、trend の線で年末シーズンの上昇トレンドやその後の落ち着きもとらえられていることがわかります。これ以外の項目も含めたML.EXPLAIN_FORECASTの出力内容詳細についてはこちらを参照ください。

まとめ

このシリーズでは GA4 のデータ構造およびそれをクエリするための SQL の使い方、BQML での時系列モデル学習についてご紹介しました。BQMLを使うと、数行の SQL で機械学習モデルが作成できることを実感頂けたと思います。時系列以外の機械学習モデルにご興味がある方は BQML のヘルプページを参照してください。
また、ここでご紹介した SQL を BigQuery のコンソールではなく、ノートブックというブラウザで実行する Python コードをgoo.gle/bqga4 にもアップデートしていますので、あわせて参照してみてください。

BigQueryではじめるWeb解析入門 ④〜BQMLでGA4の訪問者数を予測してみる〜

日別の訪問者数を確認

BQMLで時系列モデルを作成する

モデルの評価指標を調べる

予測結果を可視化する

まとめ

Written by Miki Katsuragi