Looker Studioのデータソースで BigQueryのカスタムクエリを使用する場合の注意点

本記事は【デジマWeek 2023】 7日目の記事です。
📈 6日目 ▶▶ 本記事 📚

坂本です。
早いもので前回の投稿からはや1年となりました。

本記事ではLooker StudioのデータソースとしてBigQueryのカスタムクエリを使用する場合の注意点について解説します。
Looker StudioやBigQueryの詳細については割愛しますので、必要に応じて以下のリンクかこちらの書籍をご参照ください。

Looker StudioでBigQueryをデータソースにする理由
レポートの初期設定
- データソースの設定
- [期間設定]コントロールの追加
グラフを追加
- スコアカードの追加
- クエリ実行内容の確認
グラフを追加（計算フィールドとフィルタを使用した場合）
まとめ

Looker StudioでBigQueryをデータソースにする理由

GA4を活用するためにLooker Studioの導入を検討されている方もいらっしゃるかと思います。
BigQueryを使わなくてもLooker StudioでGA4のデータを表示することは可能なのですが以下のような問題があります。

GA4のレポートで使用できる一部のディメンション・指標が使用できない。
UAと異なりセグメントが使用できない等の理由により、柔軟にデータの抽出・集計ができない。
データ抽出用のAPI（Google Analytics Data API）の上限の割り当てが適用されるため、多数のグラフを表示したレポートを大人数で閲覧するとエラーが発生してデータが表示されなくなる場合がある。

BigQueryをデータソースにすることでこういった問題を回避することが可能なのですが、以下の点にご注意ください。

GA4のデータをBigQueryにエクスポートする設定が事前に必要となる。
BigQueryの費用が発生する。
GA4のレポートで使用できる一部のディメンション（年齢、性別、インタレストカテゴリ等）が使用できない。

レポートの初期設定

それでは実際にLookerStudioでBigQueryをデータソースにしたレポートを作成します。
※本記事でご紹介する設定内容はあくまでも検証用で、実際にレポートを作成する際に推奨している設定ではない旨ご注意ください。

データソースの設定

[データを追加]からデータソースを設定します。BigQueryをデータソースにする場合は以下のいずれかを使用することができます。

テーブルまたはビュー
カスタムクエリ

今回はカスタムクエリを使用します。手順はこちらのGoogle公式ヘルプ]をご参照ください。

データセットはBigQueryの一般公開データセット「ga4_obfuscated_sample_ecommerce」を使用します。

[カスタムクエリを入力]で以下のクエリを入力します。

SELECT
  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_*`
WHERE
  _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE

クエリの内容についてこちらの記事も合わせてご確認ください。今回は本記事に関連する設定のみ説明します。 ①下記の項目を取得します。

フィールド	取得する内容
event_name	イベント名
user_pseudo_id	デバイス ID
page_location	ディメンション「ページロケーション」に相当する値
device_category	ディメンション「デバイスカテゴリ」に相当する値

注意事項として、BigQueryのオンデマンド料金ではクエリで処理するデータ量に応じて課金されます。
またRECORD型のフィールド「event_params」では下記のように複数のパラメータの情報が含まれています。

このカスタムクエリでは「event_params」をUNNESTして「page_location」パラメータのみを取得していますが、「page_location」だけでなく全てのパラメータが課金対象になります。

②のWHERE区では、③「期間パラメータを有効にする」のチェックを入れることで使用できる下記の標準パラメータを使用しています。

パラメータ	設定される値
@DS_START_DATE	レポート期間の開始日（YYYYMMDD）
@DS_END_DATE	レポート期間の終了日（YYYYMMDD）

これにより、レポートの期間の設定に従ってクエリのパラメータに日付が設定されるため、取得するデータの範囲を限定してコストを削減することができます。

[期間設定]コントロールの追加

[挿入]から[期間設定]コントロールを追加して日付範囲を2020/11/01～2023/10/31に設定します。
※「ga4_obfuscated_sample_ecommerce」には2020/11/01～2021/01/31のデータが含まれています。

グラフを追加

まずはGA4の「総ユーザー数」と同様の値を表示するスコアカードを追加します。

スコアカードの追加

「user_pseudo_id（デバイス ID）」の個別件数（ユニーク数）を取得するスコアカードを追加します。
※必須ではないですが、わかりやすいように名前を変更しています。

以下のような値が表示されました。

クエリ実行内容の確認

それでは、レポートを表示した際にBigQueryで実行されたクエリを確認してみましょう。
Looker Studioから実行されたクエリはBigQueryのブラウザコンソールの「個人履歴」または「プロジェクト履歴」から確認できます。

このままだと見づらいので[展開]>[クエリを整形]を選択します。

整形されて見やすくなりました！

クエリの中身を見ると以下がわかります。

①は整形されていますがデータソースのカスタムクエリで設定した内容そのままです。
②でスコアカードの指標で使用している「user_pseudo_id」を取得して、③で個別件数（ユニーク数）に集計しています。

つまりLooker Studioではレポートのグラフで表示するための集計結果を取得するクエリを実行しており、Looker Studioでは集計はしていません。
ここで少し気になったことがないでしょうか？
このスコアカードでは「user_pseudo_id」しか使用しないのですが、実際はカスタムクエリで設定した内容がそのまま実行されるため、グラフで使用しない項目も課金対象になってしまいます。

グラフを追加（計算フィールドとフィルタを使用した場合）

次は計算フィールドとフィルタを使用したらどうなるかを見てみましょう。

計算フィールドの作成

まず、デバイスカテゴリの内容を日本語名に変換する計算フィールドを作成します。

フィルタの作成

次に「page_location」が「https://www.googlemerchandisestore.com/」で始まるデータのみを集計対象とするフィルタを追加します。

円グラフの作成

前述した計算フィールドとフィルタを適用した円グラフを追加します。

円グラフが表示されました。

クエリ実行内容の確認

さきほどと同様にクエリの実行内容を確認します。 ①に計算フィールドの設定、②にフィルタの設定を再現させるための記述がされていることが確認できます。

またクエリの実行履歴では下記の通り2つのクエリが実行されています。

つまりレポートのグラフ（今回の例ではスコアカードと円グラフ）それぞれの値を取得するためにクエリが別々に実行されていることがわかります。

まとめ

今回はLooker StudioのデータソースとしてBigQueryのカスタムクエリを使用する場合に実行されるクエリの内容を検証してみました。
ポイントをまとめると以下になります。

グラフ毎にデータソースのカスタムクエリの処理範囲全てが課金対象となる。
Looker Studioでは集計処理は行っておらず、BigQueryのクエリで集計された結果を取得する。

カスタムクエリはレポート用にテーブルを追加せずに簡単に設定ができる反面、BigQueryのオンデマンド料金のコスト効率が悪くなります。
Looker Studioから実行されたBigQueryクエリ結果は、他のBigQueryクエリと同様にキャッシュに保存され、またLooker Studioにもこちらに記載されているようにキャッシュの仕組みがあるのですが、大量のデータを含むテーブルをデータソースとするレポートを大人数が頻繁に使用するとBigQueryの料金が高額になってしまう可能性があるためご注意ください。
対策については機会があれば別の記事で解説します。

またGoogle公式ページに以下の記載があります。