本記事は
【Advent Calendar 2023】
22日目の記事②です。
🎄
21日目
▶▶ 22日目記事①
▶▶ 本記事 ▶▶
23日目
🎅
坂本です。
昨年に続いてAdvent Calendarに執筆させていただきました。
※本記事は下記の2023-10-31に投稿した記事の続編になるため、ご覧になっていない方はまずこちらを参照していただけると幸いです。
Looker Studioのデータソースで BigQueryのカスタムクエリを使用する場合の注意点 - NRIネットコムBlog
Looker StudioにBigQueryのカスタムクエリを使用した場合の問題点
前回の記事で記載した通り、Looker StudioでBigQueryのカスタムクエリを使用した場合、レポート用にテーブルを追加せずに簡単に設定ができる反面、レポートのグラフで使用しない項目も処理対象になるためBigQueryのオンデマンド料金のコスト効率が悪くなるという問題があります。
本記事ではそれを解決する1つの方法としてカスタムクエリではなくテーブルをデータソースにする方法を解説します。
テーブルの作成
まずはデータソースとして使用するためのテーブルを作成します。
※データセットは事前に作成しておいてください
BigQueryコンソールのクエリエディタで下記のクエリを実行してください。
CREATE TABLE
{プロジェクト名}.{データセット名}.{テーブル名} (
event_date DATE,
event_name STRING,
user_pseudo_id STRING,
page_location STRING,
device_category STRING
)
PARTITION BY
event_date
※{プロジェクト名}、{データセット名}、{テーブル名}の部分はご自身の環境に書き換えてください。
ポイントとして、下記を記述することで日付項目（このサンプルでは「event_date」）を使用した日別のパーティション分割テーブルを作成しています。
PARTITION BY event_date
こうすることでレポートで指定した期間のデータのみをクエリの処理対象にすることが可能となります。
パーティション分割テーブルについては下記の公式ドキュメントを参照してください。
パーティション分割テーブルの概要 | BigQuery | Google Cloud
続いて下記のクエリを実行してデータを追加します。
※データセットは前回の記事と同様にBigQueryの一般公開データセット「ga4_obfuscated_sample_ecommerce」を使用します。
INSERT INTO `{プロジェクト名}.{データセット名}.{テーブル名}` (event_date, event_name, user_pseudo_id, page_location, device_category)
SELECT
CAST(event_date as DATE FORMAT 'YYYYMMDD') AS event_date,
event_name,
user_pseudo_id,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
device.category AS device_category
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
レポートの初期設定
LookerStudioのデータソースに、BigQueryのカスタムクエリではなくテーブルを使用したレポートを作成します。
データソースの設定
[データを追加]からデータソースを設定します。前回の記事ではカスタムクエリを使用しましたが、今回はテーブルを使用します。 手順はこちらのGoogle公式ヘルプをご参照ください。 「最近のプロジェクト」または「マイ プロジェクト」から前の手順で作成したテーブルを選択します。
テーブルを選択すると下記が表示されるので、必ずチェックを入れてください。
チェックを入れることで、レポートの期間の設定に従ってLookser Studioから生成されるクエリのWHERE区に「event_date」が指定されるようになります。
[期間設定]コントロール、グラフの追加
前回の記事と同様の手順で[期間設定]コントロールと総ユーザー数」と同様の値を表示するスコアカードを追加します。
クエリ実行内容の確認
前回の記事 と同様にレポートを表示した際にBigQueryで実行されたクエリを確認してみましょう。
※レポートを更新しても実行されたクエリが確認できない場合があります。これはBI Engineと呼ばれるキャッシュを使用してクエリを高速化するサービスが有効になっているためです。
BI Engine とは | BigQuery | Google Cloud
BI Engineを使用する場合、追加の費用が発生するのですが、Looker Studioのユーザーには1 GB の予約容量が無料で用意されるため、これが自動的に適用される場合があります。
BI Engineが有効になっているかどうかは下記のようにグラフをクリックした際に表示されるアイコンで確認できます。
それでは実行されたクエリを確認してみましょう。
クエリの中身を見ると以下がわかります。
- ①ではデータソースに指定したテーブルから、グラフで使用する項目のみを取得しています。
- ②ではレポートで指定した期間がWHERE区に変換されています。
- ③スコアカードの指標で使用している「user_pseudo_id」を個別件数（ユニーク数）に集計しています。
カスタムクエリではグラフで使用しない項目も含めてクエリの処理対象になりましたが、テーブルを使用した場合はグラフで使用する項目だけが処理対象になるため、 処理対象（≒課金対象）のデータを削減することができます。
サンプルの「総ユーザーを表示するスコアカード」を表示するためのクエリの課金データ量を比較すると以下のようになっており、約10分の1に削減できています。
| カスタムクエリを使用した場合 | テーブルを使用した場合 |
|---|---|
| 1.25 GB | 118 MB |
※ただし、フィルタやコントロールで他の項目を使用した場合はその項目も処理対象になるのでご注意ください。
BigQueryのクエリの実行（分析）の料金については下記をご参照ください。
料金 | BigQuery: クラウド データ ウェアハウス | Google Cloud
まとめ
今回の記事で解説した通り、データソースにテーブルを使用することで、BigQueryのクエリの課金対象を削減することができます。 ただし、テーブルを使用する場合、以下のようなデメリットもあります。
- テーブルのストレージ料金が発生します。
- テーブルの作成およびデータを更新する対応が必要となります。GA4のBigQueryエクスポートデータのように、日々データが追加される場合はそれに合わせてスケジュールされたクエリ等を使用してデータソース用のテーブルを更新する必要があります。
- レポートで使用するデータ項目を追加する場合は過去分データの再作成が必要になります。
テーブルを使用する場合、BigQueryの分析（クエリ）のコストは削減できるのですが、それと引き換えにストレージ料金と、テーブルを作成・維持管理する（人の）コストが増加します。
レポートで使用するデータのデータ量が少ないのであれば、カスタムクエリを使用したほうがよい場合もあるため、カスタムクエリとテーブルどちらを使用するかは要件に応じて最適な方法を使い分けてください。
