【BigQuery】中間テーブルを作成して、コスト削減し、クエリ処理速度を速める

はじめに

Google Analytics 4 (GA4) からエクスポートされたデータをBigQueryで扱う際、データの効率的な管理はコスト削減とパフォーマンス向上の鍵となる。

毎日のバッチ処理で全データセットに対してクエリを実行することは、特に大容量のデータが関わる場合、膨大なコストがかかる可能性がある。加えて、GA4からエクスポートされたデータには、過去のデータも含まれており、これらのデータは時間が経過しても変化しない。

この不変の特性を利用して、過去のデータを中間テーブルに保存し、日々のバッチ処理ではこの中間テーブルから必要なデータを取得することが、コスト削減と処理速度の向上につながる。

中間テーブルには一度集約されたデータが保持されるため、毎日のクエリは新たに追加されたデータのみを対象とすることができ、全体のデータ量が大幅に減少する。これにより、BigQueryでのデータ処理にかかるコストを効果的に削減しつつ、データ取得のレスポンス時間も短縮することが可能になる。

したがって、GA4からのデータを扱う際には、過去の静的なデータを中間テーブルに分離し、日々のクエリでは最新のデータにのみ焦点を当てるという戦略が、コストとパフォーマンスの最適化に非常に有効。

前回こちらの記事の最後に記載した、GA4データを使って会員登録イベントの発生した日ごとに、流入元（ソース）別のユニークユーザー数をカウントするSQL文を例に、中間テーブルの作成を進めていく。

【GA4 × BigQuery】GA4のBigqueryでのイベント集計：user_pseudo_idとga_session_idの違い

今回は、ユーザーの新規会員登録（イベント名：signup_complete）の流入経路を分析する例を記載する。GA4のデータをBigQueryでクエリする場合、event_name に基づいて特定のイベントをフィルタリングし、event_params 内の個々のパラメータを抽出して分析することができる。

kazulog.fun

2024.04.23

SELECT
　　　　DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Tokyo") AS event_date,
  traffic_source.source AS source, -- トラフィックソースを選択
  traffic_source.medium AS medium, -- トラフィックメディアを選択
  traffic_source.name AS campaign, -- トラフィックキャンペーンを選択
  COUNT(DISTINCT user_pseudo_id) AS users -- ユニークなユーザー数をカウント
FROM
  `analytics_XXXXXXXXX.events_*` -- データセットとテーブルの指定
WHERE
  event_name = 'event_name_signup_complete' AND -- 会員登録イベントをフィルタリング
  _TABLE_SUFFIX BETWEEN '20230801' AND '20240225' -- 分析する日付範囲の指定
GROUP BY
  event_date,
  source,
  medium,
  campaign
ORDER BY
  event_date ASC;　-- 会員登録日昇順で並び替え

BigQueryでのコスト確認（概算）

先ほどのSQL文では、約半年にわたる、新規会員登録イベントの流入元を計測している。BigQuery上でこのコードを実行しようとすると、下記のようにクエリの右上に、クエリがスキャンするデータの推定量が表示される。これに基づいて、BigQueryの料金が計算される。

今回のクエリでは、クエリがスキャンするデータ推定量が1.65GBと表示されている。

BigQueryの料金体系は変更される可能性があるため、最新の情報を得るにはGoogle Cloudの公式料金ページを参照する必要があるが、2024年2月時点での概算は下記。

BigQueryの料金計算ツールを使って推定することもできる。

費用の見積もりと管理  |  BigQuery  |  Google Cloud

cloud.google.com

今回のクエリで約1GBのデータを処理する場合、その料金はさほど高額にはならない。しかし、このクエリを毎日実行するとなると、過去の不変なデータに対しても毎回クエリを実行することになり、無駄が生じる。なので、効率化の観点から、GA4からエクスポートしたデータに基づいて中間テーブルを作成し、必要なデータのみを定期的に更新し、クエリの処理量を削減してコストを節約することを試みる。

BigQueryでの中間テーブル作成

中間テーブルを作成するには、BigQueryの CREATE TABLE ステートメントを使用して、上記のクエリの結果を新しいテーブルに格納する。

your_project_id、your_dataset_id、your_new_table_name を、それぞれ自分のプロジェクトID、データセットID、新しいテーブル名に置き換える。

ちなみに、このテーブルID（=project_id.dataset_id.table_name）は、作成したテーブル情報の詳細タブのテーブルIDとして表示され、カーソルを合わせるとコピーできる。

CREATE OR REPLACE TABLE を使用すると、すでにテーブルが存在する場合はそのテーブルを置き換える。既存のテーブルを置き換えたくない場合は、CREATE TABLE を使用する。

テーブルとスキーマがクエリの実行によって自動的に作成されるため、事前にGUIでテーブルを設定する必要はない。クエリ内で指定されたフィールド名とデータ型に基づいて、BigQueryが自動的に適切なスキーマを推定し、テーブルを作成する。

CREATE OR REPLACE TABLE `your_project_id.your_dataset_id.your_new_table_name` AS
SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Tokyo") AS event_date,
  traffic_source.source AS source,
  traffic_source.medium AS medium,
  traffic_source.name AS campaign,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM
  `your_dataset_id.events_*`
WHERE
  event_name = 'event_name_signup_complete'
  AND _TABLE_SUFFIX BETWEEN '20230801' AND '20240225'
GROUP BY
  event_date,
  source,
  medium,
  campaign
ORDER BY
  event_date ASC;

クエリを実行すると、テーブルとスキーマが自動で作成された。

また、「プレビュー」タブをクリックすると、対象期間のデータが入ったことがわかる。

中間テーブルに対して定期的に更新データを追加する

BigQueryのTable suffix機能を利用して、特定の日付範囲のデータのみを選択して中間テーブルに挿入することができる。

中間テーブルへのデータ追加には、CREATE TABLE ステートメントではなく、INSERT INTO ステートメントを使用する。

たとえば、毎日前日のデータを中間テーブルに挿入するには、以下のようなクエリをスケジュール実行する。

ちなみにこのクエリは、8.79MBの処理量だった。

INSERT INTO `your_project_id.your_dataset_id.your_intermediate_table_name`
SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Tokyo") AS event_date,
  traffic_source.source AS source,
  traffic_source.medium AS medium,
  traffic_source.name AS campaign,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM
  `analytics_299666849.events_*`
WHERE
  event_name = 'event_name_signup_complete'
  AND _TABLE_SUFFIX = FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY
  event_date,
  source,
  medium,
  campaign;

次に、このクエリを定期事項できるようにスケジュール設定するのだが、その前にクエリ実行ボタンを押して、データが正しく反映されるか確認する。

クエリ結果で、event_name, source, medium, campaign, usersの5個の前日のデータが無事反映されたことを確認できた。

クエリ定期実行のスケジュール設定

作成したINSERT INTO文に自動実行を設定するには、クエリエディタの上にある「スケジュール」ボタンをクリックする。

下記画面が開くので、「スケジュールされたクエリ名」を入力し、クエリの実行頻度、終了日（必要なら）などを入力して、保存ボタンをクリックする。

設定すると、BigQueryの、「スケジュールされたクエリ」の欄に、作成したクエリ名が表示され、転送実行が進行中ですという表示が出る。また、次の実行予定日も表示される。

転送が無事終わると、「転送実行が正常に完了しました」と表示される。

中間テーブルから最新のデータを取得する

中間テーブルから最新のデータを取得するためのクエリは以下。このクエリは、任意のタイミングで実行して最新の分析データを取得するために使用する。

このSELECTクエリは、中間テーブルからすべてのデータを取得し、イベント日付で降順に並べ替えて表示する。必要に応じて、特定の期間や特定のソースに対するフィルタを追加することもできる。

SELECT
  event_date,
  source,
  medium,
  campaign,
  users
FROM
  `your_project_id.your_dataset_id.your_intermediate_table_name`
ORDER BY
  event_date DESC;

あとは、Redashなどビジネスインテリジェンス（BI）ツールを使って、グラフとして可視化するなど。

結果

ユーザー数の前処理が済んだ中間テーブルからデータを引っ張ってくるだけになったので、クエリ処理量が40KBと、元の1GBと比較して、25,000倍ほど抑えられた。

毎日のinsert文の方は9MB程度なので、こちらと合わせても100倍ほど抑えられたことになる。