Looker Studio × BigQuery カスタムSQLでGA4レポートを自在に操る
はじめに
Looker StudioとGA4（Google Analytics 4）を接続してレポートを作成するとき、多くの方はまずLooker Studioの標準コネクタ（GA4コネクタ）を利用するかと思います。手軽に始められる反面、「ディメンションと指標の組み合わせが思い通りにならない」「サンプリングがかかってしまって正確なデータが取れない」「複数のプロパティを横断して集計したい」といった壁にぶつかった経験はないでしょうか。
そうした課題を解消する有力な手段が、BigQueryエクスポートとLooker Studioのカスタムクエリの組み合わせです。GA4はBigQueryへのデータエクスポート機能を標準で備えており、エクスポートされた生データをSQLで加工したうえでLooker Studioに渡すことができます。
本記事では、BigQueryコネクタのカスタムSQLとは何かを整理したうえで、GA4レポーティングにおいてカスタムSQLを使うことで得られる具体的なメリットと、よく使われるSQLパターンを紹介します。
1. Looker StudioのBigQueryコネクタとカスタムSQLとは
Looker Studioのデータソースとして「BigQuery」を選択する際、データの取得方法は以下の3種類から選べます。
| 取得方法 | 概要 |
|---|---|
| マイプロジェクト （テーブルを直接指定） |
BigQueryのテーブル・ビューを直接指定して接続する |
| カスタムクエリ | SELECT文を直接記述して、その結果をデータソースとして使う |
| 共有ドライブ | BigQuery上の共有テーブルを参照する |
このうち「カスタムクエリ」が本記事で扱う機能です。テーブルをそのまま読み込むのではなく、事前にSQLで整形・集計・結合した結果をLooker Studioに渡せるため、レポートの設計自由度が大幅に上がります。
2. GA4標準コネクタとBigQueryカスタムSQLの比較
まず、GA4標準コネクタとBigQueryカスタムSQLの主な違いを整理します。
| 比較軸 | GA4標準コネクタ | BigQuery カスタムSQL |
| 比較軸 | GA4標準コネクタ | BigQuery カスタムSQL |
|---|---|---|
| セットアップの手軽さ | ◎ すぐ使える | △ BigQueryエクスポート設定が必要 |
| サンプリング | 大量データ時に発生する場合あり | なし（全件処理） |
| ディメンション/指標の自由度 | GA4の仕様に依存 | SQLで自由に定義できる |
| イベントパラメータの取り出し | 一部制限あり | UNNEST で完全に取り出せる |
| 複数プロパティの横断集計 | 不可 | UNION ALL で可能 |
| データ加工・前処理 | 限定的 | SQLで自由に実施 |
| コスト | 無料 | BigQueryのクエリ費用が発生 |
GA4標準コネクタは手軽さに優れていますが、レポートに求める粒度や柔軟性が増してくると、BigQueryカスタムSQLの優位性が際立ってきます。
3. カスタムSQLを使う主なメリット
① サンプリングなしで全データを集計できる
GA4標準コネクタでは、対象期間が長かったり、データ量が多かったりするとサンプリング（全データの一部を抽出して推計する処理）が自動的に適用される場合があります。キャンペーン効果の正確な検証や月次・四半期レポートなど、数値の精度が求められる場面では、サンプリングによる誤差が問題になることがあります。
BigQueryに格納されたGA4の生データはサンプリングなしの完全なデータです。カスタムSQLで集計することで、常にサンプリングのかかっていない正確な数値をレポートに反映できます。
② イベントパラメータを自由に取り出せる
GA4のBigQueryエクスポートデータでは、イベントパラメータが
event_params という配列型のカラムに格納されています。標準コネクタでは取り出せるパラメータに制限がありますが、SQLの UNNEST 関数を使うことで任意のカスタムパラメータを列として展開できます。たとえば、カスタムイベントに仕込んだ
plan_type（料金プランの種別）や content_id（コンテンツID）などを取り出してディメンションとして使う場合、カスタムSQLでなければ対応できません。
SELECT
event_date,
event_name,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'plan_type') AS plan_type,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content_id') AS content_id,
COUNT(*) AS event_count
FROM
`your_project.analytics_XXXXXXXXX.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20240101' AND '20241231'
GROUP BY
1, 2, 3, 4
③ 複数プロパティ・複数期間を横断集計できる
ECサイトとコーポレートサイトなど、複数のGA4プロパティのデータを1つのレポートにまとめたいという要件は実務でよく発生します。標準コネクタでは1つのプロパティにしか接続できませんが、カスタムSQLでは
UNION ALL を使って複数プロパティのテーブルを結合できます。
-- コーポレートサイトのデータ
SELECT 'corporate' AS site, event_date, event_name, COUNT(*) AS event_count
FROM `your_project.analytics_111111111.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20241231'
GROUP BY 1, 2, 3
UNION ALL
-- ECサイトのデータ
SELECT 'ec' AS site, event_date, event_name, COUNT(*) AS event_count
FROM `your_project.analytics_222222222.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20241231'
GROUP BY 1, 2, 3
④ 独自の指標・セグメントを定義できる
GA4の標準コネクタでは定義できない独自の指標やセグメントをSQLで作成できます。たとえば以下のようなケースです。
- 初回訪問フラグ:
is_new_user相当の値をセッションや日付をもとに自分で定義する - ファネル完了率: 「イベントAを発火したユーザーのうち、イベントBまで到達した割合」をSQLで算出する
- ルックバックウィンドウの調整: 標準のアトリビューション設定と異なる期間でのコンバージョン計測
-- 購入ファネルの完了率を計算する例
WITH funnel AS (
SELECT
user_pseudo_id,
MAX(CASE WHEN event_name = 'view_item' THEN 1 ELSE 0 END) AS viewed_item,
MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS added_to_cart,
MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS purchased
FROM `your_project.analytics_XXXXXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20241231'
GROUP BY user_pseudo_id
)
SELECT
COUNTIF(viewed_item = 1) AS users_viewed_item,
COUNTIF(added_to_cart = 1) AS users_added_to_cart,
COUNTIF(purchased = 1) AS users_purchased,
ROUND(SAFE_DIVIDE(COUNTIF(added_to_cart = 1), COUNTIF(viewed_item = 1)) * 100, 1) AS cart_rate_pct,
ROUND(SAFE_DIVIDE(COUNTIF(purchased = 1), COUNTIF(added_to_cart = 1)) * 100, 1) AS purchase_rate_pct
FROM funnel
⑤ データを事前に集計してクエリコストを抑制できる
BigQueryはスキャンしたデータ量に応じて課金されます。Looker Studioのレポートを表示するたびにイベントテーブル全体をスキャンしていると、コストが積み上がる可能性があります。
カスタムSQLを活用する際には、BigQueryのビュー（View）やマテリアライズドビュー（Materialized View）を事前に作成しておき、Looker StudioからはそのビューをSELECTするだけにする構成が効果的です。これにより、レポート表示のたびに生テーブルをフルスキャンすることを避けられます。
また、Looker Studioには**「クエリキャッシュ」**機能があり、同じクエリを一定時間内に再実行した場合はBigQueryへのリクエストをスキップします。カスタムSQLを使う場合はこのキャッシュ設定を適切に調整することも重要です。
4. カスタムSQLを使う際の注意点
Looker Studioの日付フィルタとの連携
Looker Studioのレポート上の日付フィルタをカスタムSQLに連携させるには、クエリ内に以下のパラメータを記述します。
WHERE
_TABLE_SUFFIX BETWEEN
FORMAT_DATE('%Y%m%d', @DS_START_DATE) AND
FORMAT_DATE('%Y%m%d', @DS_END_DATE)
@DS_START_DATE と @DS_END_DATE はLooker Studioが自動的に差し込むパラメータです。これを使わないと、レポート上で日付を変えてもSQLの集計期間が変わらないため注意が必要です。BigQueryエクスポートの有効化が前提
BigQueryカスタムSQLを利用するには、あらかじめGA4プロパティの管理画面からBigQueryへのリンクとデータエクスポートを設定しておく必要があります。設定後、データが反映されるまで24〜48時間程度かかります。
クエリコストの管理
カスタムSQLを使い始める前に、**BigQueryのドライラン（dry run）**でクエリのスキャン量を確認する習慣をつけることをお勧めします。意図せず大量のデータをスキャンするクエリを本番のLooker Studioに接続してしまうと、コストが想定外に膨らむ可能性があります。
まとめ
Looker Studio × BigQueryのカスタムSQLを活用することで、GA4の標準コネクタでは対応できなかった以下の課題を解消できます。
- サンプリングなしの完全なデータで正確なレポートを作成できる
UNNESTでカスタムイベントパラメータを自由にディメンション化できる- 複数GA4プロパティの横断集計が
UNION ALLで実現できる - ファネル完了率など独自の指標・セグメントをSQLで定義できる
- ビューやマテリアライズドビューを活用してクエリコストを最適化できる
GA4の活用が進み、レポートに求める要件が高度になってきた段階では、BigQueryカスタムSQLへの移行を検討する価値があります。初期設定やSQLの学習コストは発生しますが、一度仕組みを作ってしまえばレポーティングの柔軟性は大きく向上します。
＜ご参考＞
GA4 BigQueryエクスポートの設定方法（Google公式）:
GA4 BigQueryエクスポートのスキーマ仕様:
Looker Studio BigQueryコネクタ カスタムクエリについて:
Implement DigitalではGoogle Marketing Platformの導入・実装・運用を支援するサービスを提供しています。
ご興味がありましたらお問い合わせください。