FURYU Tech Blog - フリュー株式会社

フリュー株式会社の開発者が技術情報を発信するブログです。

GA4×BigQuery：スキャン量を99%削減するテーブル設計

BigQuery Google Cloud

この記事はフリューAdvent Calendar 2025の21日目の記事となります。

みなさまこんにちは。
プリントシール機やピクトリンクのデータ分析をしている堀江です。

もう年末ですね～、2025年もたくさんのデータと向き合いました。
日々、ユーザーの行動データを分析してサービス改善に活かしているのですが、データ量が増えるにつれてある問題が大きくなってきました。

そう、BigQueryの実行コストです。

今日はGA4データの集計で行ったコスト削減について、実際にやってみた内容を共有します。

はじめに

分析環境

GA4のイベントログをBigQueryへエクスポートし、そのテーブルを使ってデータ分析を行っています。

困っていたこと

GA4の生テーブル、使いづらくないですか？
いろんなイベントが混在してるし、列も多いし、event_paramsは入れ子構造だし…

実際、こんな課題に直面していました：

  • クエリが複雑：入れ子構造を展開するのに毎回UNNESTが必要
  • スキャン量がデカい：使わない列・イベントまで全部スキャンしちゃう
  • 実行コストが爆増：数カ月間のデータを集計するだけで数百GB単位に...

例えば、単純に「2年間のscreen_view件数を日別で集計」するだけで、260GBもスキャンしてしまうような状況でした。

そこで今回、コスト削減＋可読性向上を両立させるための取り組みを試してみました。

やったこと

今回やったことは大きく3つです。

1. テーブル設計のパターン

多くのケースでこれだけでも効果が出ます。
構成は色々あると思いますが、よくやるパターンを紹介します。

1-a. 頻出イベントに絞る（汎用性：高）

GA4の生テーブルっていろーーーんなイベントが混在してるし、列も多いしで、正直使わない項目が多い。なので、まず必要な分に絞るだけでもクエリが見やすくなり、分析もしやすくなると思います。
（ただし、コスト削減にはパーティション・クラスタリングの設定が必須です。後述します）

/* eventsテーブルを必要な列のみに絞り込む -> テーブルに結果保存 */
CREATE TABLE `project.dataset.event_logs`
SELECT
    parse_date("%Y%m%d", event_date) AS event_date,
    event_timestamp,
    timestamp(format_timestamp("%Y-%m-%d %H:%M:%S", timestamp_micros(event_timestamp), "Asia/Tokyo")) AS event_datetime,
    event_name,
    (SELECT max(CASE WHEN key = "parameter" THEN value.string_value END) FROM UNNEST(event_params)) AS event_params_parameter,
    (SELECT max(CASE WHEN key = "id" THEN value.string_value END) FROM UNNEST(event_params)) AS event_params_id,
    user_pseudo_id,
    app_info.version AS app_info_version,
    platform
FROM
    `project.analytics_123456789.events_YYYYMMDD`
1-b. 特定の機能・ページに絞る（汎用性：中）

例えば「マイページ関連のイベントだけ」のように、特定の用途に絞った抽出テーブルを作るパターンです。汎用性は下がりますが、頻繁に集計する範囲が決まっているなら効率的。

💬event_paramsの扱い方
特定ページの抽出テーブルを作ったとき、「後から特定のパラメータが欲しくなる」ことがよくあります。例えば「マイページだけのuser_levelパラメータを使って分析したい」とか。

これに対応するため、以下のように使い分けるのがおすすめ：

  • 頻出キー → カラムとして独立
  • それ以外 → event_params列をそのまま保存

event_params列を持たせておけば、後から必要になったパラメータもサブクエリで取り出せます。

💡JSON形式にするとさらに便利
event_paramsをJSON形式で保存すると、json_value()で簡潔に取り出せて可読性が上がります。

/* eventsテーブルを必要なデータのみに絞り込む -> テーブルに結果保存 */
CREATE TABLE `project.dataset.event_logs_mypage`
AS
SELECT
    event_date,
    event_timestamp,
    event_datetime,
    event_name,
    json_value(event_params, "$.parameter") AS event_params_parameter,
    json_value(event_params, "$.id") AS event_params_id,
    user_pseudo_id,
    app_info_version,
    platform,
    event_params
FROM
    (
    SELECT
        event_date,
        event_timestamp,
        event_datetime,
        event_name,
        user_pseudo_id,
        app_info_version,
        platform,
        parse_json( -- event_paramsをJSON形式へ変換
            "{"
            || string_agg(
                format(
                    '"%s":"%s"',
                    ep.key,
                    coalesce(
                    regexp_replace(
                        regexp_replace(ep.value.string_value, r'[\x00-\x1F\x7F-\x9F]', ''),
                        r'(["\\\n\r\t])',
                        r'\\\1'),
                    CAST(ep.value.int_value AS string))),
            ", ")
        || "}") AS event_params
    FROM
        (
        SELECT
            row_number() OVER (ORDER BY event_timestamp) AS rownum,
            parse_date("%Y%m%d", event_date) AS event_date,
            event_timestamp,
            timestamp(format_timestamp("%Y-%m-%d %H:%M:%S", timestamp_micros(event_timestamp), "Asia/Tokyo")) AS event_datetime,
            event_name,
            event_params,
            user_pseudo_id,
            app_info.version AS app_info_version,
            platform
        FROM
            `project.analytics_123456789.events_YYYYMMDD`
        ),
        UNNEST(event_params) AS ep
    GROUP BY
        event_date,
        event_timestamp,
        event_datetime,
        event_name,
        user_pseudo_id,
        app_info_version,
        platform,
        rownum
    )
WHERE
    json_value(event_params, "$.screen") = "mypage" -- 例：マイページのみ

このように、頻出キー「parameter」「id」はカラムとして展開しつつ、event_params列も丸ごと保存しておくと、後から柔軟に対応できます。

キーバリュー形式のままだと、値を取り出すのにサブクエリが必要です。JSON形式にしておくと、以下のような感じで簡潔に書けます。

キーバリューの場合：

SELECT
    (SELECT max(CASE WHEN key = "id" THEN value.string_value END) FROM UNNEST(event_params)) AS event_params_id

JSONにした場合：

SELECT
    json_value(event_params, "$.id") AS event_params_id

⚠️注意点
JSON変換は、特殊文字や長い文字列が含まれるデータでは問題が起きる場合もあるので、まずは小規模で試してみるのがおすすめです。

1-c. 集計結果をテーブル化（汎用性：低）

最終手段かもしれません。
例えば以下のような内容を集計テーブル化する感じです。

/* screen_viewの集計 -> テーブルに結果保存 */
CREATE TABLE `project.dataset.daily_screen_views`
AS
SELECT
    parse_date("%Y%m%d", event_date) AS event_date,
    count(1) AS cnt
FROM
    `project.dataset.event_logs` -- 1-aで作った抽出テーブル
WHERE
    _TABLE_SUFFIX >= "2024-04-01"
    AND event_name = "screen_view"
GROUP BY
    event_date
ORDER BY
    event_date

ただし、この集計結果テーブルも、データ量が増えれば毎回全件スキャンが必要になります。
そこで、次に説明するパーティション・クラスタリングやマテリアライズドビューと組み合わせることで、さらにコストを抑えられます。

2. パーティション・クラスタリングを駆使する

event_dateでパーティション、WHERE句でよく使うカラム（event_nameなど）をクラスタリングすることで、スキャン量を大幅に削減できます。

パーティションやクラスタリングの詳細説明はネットにたくさんあるので省きますが、抽出テーブルや集計テーブルを作るなら、パーティションはマストで設定すべし！
なお、既存のテーブルにもクラスタリングは設定できますが、既に入っているデータには適用されません。
設定後に追加されるデータからしか効かないため、既存データにも効かせたい場合は、テーブルを作り直す必要があります。

3. マテリアライズドビューを使う

マテリアライズドビューとは、クエリの実行結果を事前に計算してテーブルとして保存しておく機能です。集計結果テーブルと似ていますが、BigQueryが自動でデータを更新してくれるのが特徴。

集計結果テーブルと合わせて使うと、非常に効果的です。ただし、更新頻度とコストのバランスは要検討。また、クエリの制約も結構あります。リアルタイム性が不要な日次集計などに向いています。

/* 抽出テーブルを使ったscreen_viewの集計 -> テーブルに結果保存 */
CREATE MATERIALIZED VIEW `project.dataset.daily_screen_views` AS
SELECT
    event_date,
    COUNT(1) AS screen_view
FROM
    `project.dataset.event_logs`
WHERE
    event_name = "screen_view"
    AND event_date >= "2024-01-01"
GROUP BY
    event_date

マテリアライズドビューはどうしても集計テーブルと同様に汎用性が低くなるため、正直スポット的な分析には向いていません。
ただし、ダッシュボードで常に同じ数値を集計していたり、使用頻度の高い集計パターンがあったりするなら、そこで活用することで大幅なコスト削減が期待できます。

また、例えば「3日前までの確定データはマテリアライズドビューで持ち、直近3日分だけ都度クエリで集計」みたいなハイブリッド運用も効果的です。

コスト比較

実際に2年間のscreen_view集計で効果測定してみました。

パターン1：生テーブルをそのまま使用

生テーブルをそのまま使ったパターン

何の工夫もしない場合、261GBもかかります。

パターン2：抽出テーブル（パーティション・クラスタリングなし）

  • Before: 261GB（生テーブル）
  • After: 297GB
  • 結果: 約113%に増加（逆効果）
抽出テーブルを使うパターン（パーティション・クラスタリングなし）

パターン1よりもコストが大きくなってしまいました。

これは、生テーブルと抽出テーブルのパーティション有無が原因です：

  • 生テーブル：日付でパーティション済み → 期間指定で効率的にスキャン
  • 抽出テーブル（設定なし）：パーティションなし → 全データをスキャン

抽出テーブルを作るだけでは、元々あったパーティションの恩恵を失ってしまうんですね～。

パターン3：抽出テーブル（パーティション・クラスタリングあり）

  • Before: 261GB（生テーブル）
  • After: 38GB
  • 削減率: 約86% ↓
抽出テーブルを使うパターン（パーティション・クラスタリングあり）

続いて、パーティション・クラスタリングを設定した場合です。今回はevent_dateにパーティション、event_nameにクラスタリングを設定しています。
この2つの設定だけで、これだけの効果が出ました。

パターン4：マテリアライズドビュー化

  • Before: 38GB（抽出テーブル）
  • After: 11KB
  • 削減率: 約99.99% ↓
マテリアライズドビューにした場合

screen_viewのような定期的に見る指標は、マテリアライズドビューにするとほぼコストゼロになります。

結果まとめ

方法 スキャン量 削減率
生テーブル 261GB -
抽出テーブル※ 38GB 約86%
マテリアライズドビュー 11KB 約99.996%

※パーティション・クラスタリング設定済み

生テーブルで261GBかかっていたクエリが、たった11KBで実行できるようになりました！！！

さいごに

今回は2年間のscreen_view集計という一例でしたが、他のイベントでも同様のアプローチが使えるはずです。

特に、

  • ダッシュボードで毎日見る指標がある
  • 同じような集計を何度も実行している
  • BigQueryの請求額が気になり始めた

みたいな状況なら、効果が出やすいと思います。

BigQueryのコストに悩んでいる方、ぜひ試してみてください。
それでは、良いお年を〜