― 生データ活用の自由と引き換えに背負うもの、そしてその畳み方 ―

0. まえがき：記事が少ない領域で、同じところに何度もつまずいた

現場でやっているのは「GA4をそのまま使う」ことではない

まず、私の担当領域から簡単に。お客様のデータ分析・データ活用をサポートすることが、主な業務のひとつです。そしてその現場で、ほぼ必ず登場するのが GA4（Google アナリティクス 4） です。

ただし、我々が案件でやることは「GA4の画面を一緒に眺める」ことではありません。多くの場合、

GA4の生データを BigQuery にエクスポートし、SQLで加工・カスタマイズして、そのお客様のニーズに合った集計・ダッシュボードを作る

という進め方になります。なぜなら、GA4の標準レポートでは答えられない問いに答えるには、結局これが一番早いからです。

参考になる記事が、当時ほとんど無かった

最近はさすがに慣れてきました。しかし、活用し始めた当初は、本当に何度も壁にぶつかりました。 しかも厄介だったのが、GA4単体の記事、BigQuery単体の記事はいくらでもあるのに、「GA4 × BigQuery」の組み合わせで実務的にハマるポイントを書いた記事が、当時ほとんど見つからなかった ことです。

もちろん公式のスキーマ定義はあります。ところが、そこには「この項目は何のためにあるのか」までは書かれていません。結果として、手戻りと請求書で学ぶことになりました。

そこで本記事では、私が実際につまずいた2つの壁 を、自分の整理も兼ねて書き残しておきます。

そして読者の皆様には、ぜひ 私の苦労を屍として踏み越えていってほしい と思っています。

想定読者

具体的には、次の3タイプを想定しています。

1. GA4とは ― 「イベント単位で記録するアクセス解析」

まず前提の確認から。ご存知の方は2章まで飛ばしてください。

GA4（Google アナリティクス 4） は、Googleが提供する無料のアクセス解析ツールです。2023年7月1日には前世代の UA（ユニバーサルアナリティクス） が計測を停止しました。そのため、現在Googleアナリティクスと言えば実質GA4を指します。

UAとの最大の違いは「計測モデル」

では、UAと何が違うのか。いちばん大きいのは、計測モデルの考え方 です。

【UA】ヒット／セッション中心のモデル
  「セッション」という箱があり、その中に
  ページビュー・イベント・トランザクションという
  種類の違う“ヒット”が入る

【GA4】イベント中心のモデル
  すべてが「イベント」。ページ閲覧も page_view というイベント。
  スクロールも scroll、購入も purchase。
  セッションやユーザーは、イベントに付随する“属性”として扱う

つまりGA4では、サイト上で起きたことすべてが「イベント」という1種類のレコードとして、時系列に積まれていく という構造になっています。

このモデルの整理は地味ですが重要です。というのも、BigQueryにエクスポートされるデータは、この「イベントの行列」がそのまま出てくるもの だからです。

一方、GA4の画面で見ている「セッション数」や「エンゲージメント率」はどうでしょうか。これらは生イベントを集計・加工した 結果 であって、生データにそのまま入っているわけではありません。したがって、画面の指標と生データの項目は1対1で対応しません。 ここが3章の壁に直結します。

その他の特徴 ― BigQueryエクスポートが無料版でも使える

次に、GA4のその他の特徴を簡単に挙げておきます。

そして最後の項目が、実は非常に大きな変化でした。無料版のGA4でも、生データをBigQueryに吐き出せる。 これによって「GA4の画面でできることの外側」に、誰でも手が届くようになったわけです。

2. GA4のデータをBigQueryで活用する優位性

では、なぜわざわざBigQueryに出すのか。理由はひとつです。

GA4の画面は「集計結果を見る場所」であって、「データを自由に扱える場所」ではないから。

BigQueryにエクスポートすると、GA4のイベントが 1行1イベントの生データ（テーブル） として手に入ります。つまり、SQLが書ける状態になる、というのが本質です。そしてここから先は、集計もJOINも機械学習も自由になります。

では、具体的にどう効くのか。ここからは代表的なものを挙げていきます。

2-1. データ保持期間の制約から解放される

まずこれが、意外と一番効きます。

GA4の無料版では、ユーザー単位・イベント単位の詳細データの保持期間が最長14か月（デフォルトは2か月）です。つまり 設定を最長にしても、2年前との比較ができません。

一方、BigQueryにエクスポートしてしまえば、そのデータは 自分のプロジェクトの資産 です。保持期間はこちらで決められます。

したがって「昨年の同じキャンペーン期間と比べたい」という、実務でごく普通に出てくる要望に答えるだけでも、エクスポートしておく価値があります。

2-2. 「(other)」とサンプリングがなくなる ― 全量の正確な集計

GA4の標準レポートは、あらかじめ集計されたテーブルを見ています。そのため ディメンションの値の種類（カーディナリティ）が上限を超えると、あふれた分が (other) という行にまとめられてしまいます。

実際、ページ数の多いメディアサイトや、商品点数の多いECサイトでは、これが日常的に起きます。「上位20ページは分かるが、ロングテールがすべて (other) に吸い込まれて分析できない」という状態です。

また、自由度の高い探索レポートのほうにも、サンプリング という制約があります（無料版では1クエリあたりのイベント数に上限があり、それを超えると抽出計算になります）。

一方、BigQueryの生データは そのどちらとも無縁 です。そのため、全イベントに対して正確に集計できます。

2-3. 社内の他データとJOINできる ― 「売上に繋がったのか」が言える

そして、これがおそらくお客様に一番刺さる価値です。

というのも、GA4が持っているのは サイト内の行動 だけだからです。「この流入経路から来た人が、最終的にいくら売り上げたのか」「解約したのか」「広告費に対して 利益が出たのか」には、GA4単体では答えられません。

ところが、BigQueryに置けばそれができます。

  GA4イベント（BigQuery）
        │  user_id / トランザクションID をキーに結合
        ├──── 基幹システムの受注・売上データ
        ├──── CRM の顧客属性・LTV・解約フラグ
        └──── 広告プラットフォームの費用データ（Google Ads / Yahoo! / Meta …）
                      ↓
        「どのチャネルの、どの属性の顧客が、
          いくらのコストで、いくら利益を生んだか」

具体例を挙げます。

• チャネル別ROAS / CPAの正確な算出：GA4のコンバージョン数ではなく、基幹の確定売上 で割って出す。そのため、キャンセル・返品を差し引いた「本当の数字」になります
• 会員属性 × 行動のクロス分析：「優良顧客はサイト内でどこを見ているのか」。ただし会員ランクはCRM側にしかないので、JOINしないと出せません
• オフラインコンバージョンの接続：資料請求 → インサイドセールスの架電 → 受注、という流れを1本に繋ぐ。なお、BtoBではほぼ必須です

2-4. GA4の画面ではできない集計・分析ができる

生データがあるということは、集計ロジックを自分で書ける ということです。したがって、GA4の定義に縛られません。

• 独自定義のファネル・遷移分析：たとえば「Aを見てから3日以内にBに来て購入した人」。GA4の探索では組みづらい条件も、SQLなら直接書けます
• リードタイム分析：初回訪問から購入までの日数分布。つまり user_first_touch_timestamp と購入イベントの差分を取るだけです
• コホート分析の自由な設計：GA4のコホート探索は粒度が固定です。しかしSQLなら「初回流入チャネル × 初回月」など好きな軸で切れます
• セッション定義の作り替え：GA4のセッションは30分無操作で切れます。ところがSQLなら「1日を1セッションとみなしたい」のような業務要件にも合わせられます
• BigQuery ML による予測：たとえば離脱予測、購買確率、LTV予測などを データを動かさずに そのまま学習・推論できます

2-5. BIツールに直結できる

さらに、BigQueryは Looker Studio・Tableau・Power BI など主要BIツールの標準的な接続先でもあります。GA4のコネクタで直接繋ぐより、加工済みのテーブルを経由させたほうが、表示速度も指標定義の統制も安定します。

……ただし、この「BIツール連携」が、そのまま4章の壁②の引き金になります。 ここは後で詳しく書きます。

3. 苦労したポイント① ― 項目数が膨大で、どれが何のデータか分からない

ここからが本題です。

まず、BigQueryにGA4のテーブルが吐き出されて最初にやることは、当然「スキーマを見る」です。ところが、そこで固まります。

項目が、とにかく多い。 しかもネストしている。そして極めつけは、似た名前の項目が複数ある ことです。

3-1. まず、テーブルの構造を掴む

まず、GA4のエクスポートはこういう形で出てきます。

プロジェクト
└─ データセット（analytics_XXXXXXXXX）
   ├─ events_20260921       ← 日付ごとに1テーブル（日付シャーディング）
   ├─ events_20260920
   ├─ events_20260919
   ├─ …
   └─ events_intraday_20260922  ← 当日分（ストリーミング）

ポイントは 「1つの大きなテーブル」ではなく、日付ごとに別テーブルとして作られる ことです。そのため期間指定は、ワイルドカードと _TABLE_SUFFIX で書きます。

SELECT COUNT(*)
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260901' AND '20260907'

1行は「1イベント」 ― 主な列を押さえる

次に、テーブルの中身です。1行が「1イベント」に対応しており、主な列を整理するとこうなります。

event_params は「key と value の配列」

このうち event_params が独特です。というのも 列が固定されていないので、欲しい値はサブクエリで取り出す 必要があります。

-- ページURLを取り出す
SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = '20260921' AND event_name = 'page_view'

この書き方自体は、慣れれば何でもありません。ただし、「どのキーに何が入っているのか」は、テーブルを見るだけでは分かりません。 そこで、主要なものを載せておきます。

3-2. 最大の罠 ― source / medium が3か所以上にある

さて、いよいよ本題の参照元です。

案件で source / medium（参照元／メディア）を扱う機会は、何度もありました。というのも「どのチャネルから来た人が成果に繋がっているか」は、最も需要が高い分析のひとつだからです。

ところが、スキーマを検索すると 同じような項目がいくつも出てきます。

traffic_source.source
traffic_source.medium
traffic_source.name

collected_traffic_source.manual_source
collected_traffic_source.manual_medium
collected_traffic_source.manual_campaign_name
collected_traffic_source.gclid

session_traffic_source_last_click.manual_campaign.source
session_traffic_source_last_click.manual_campaign.medium
session_traffic_source_last_click.cross_channel_campaign.source
session_traffic_source_last_click.cross_channel_campaign.medium
session_traffic_source_last_click.cross_channel_campaign.default_channel_group
session_traffic_source_last_click.google_ads_campaign. ...

event_params の 'source' / 'medium' / 'campaign'

どれも「参照元」です。 そして当時の私は、一番名前が短くて分かりやすい traffic_source.source を選びました。これが手戻りの始まりでした。

その結果、出した数字がGA4の画面と合わない。合わないどころか、「セッションの参照元別」で出したはずの集計が、直感と大きくズレる。 原因が分かるまで、ずいぶん時間を溶かしました。

正解：スコープ（適用範囲）が違う

しかし調べてみると、これらの項目は 同じ「参照元」を、違う粒度で持っているもの でした。

スコープ早見表

つまり、私が最初に選んだ traffic_source は 「このイベントの参照元」ではなく「このユーザーを最初に連れてきた参照元」 でした。しかも、名前からはそれがまったく読み取れません。

実務での使い分け ― 「何を再現したいのか」から選ぶ

したがって、選び方は「どの項目が便利そうか」ではなく 「どのレポートを再現したいのか」 から入るのが正解です。

Q. GA4の「トラフィック獲得」レポートを再現したい
   → session_traffic_source_last_click を使う（セッションスコープ）

Q. GA4の「ユーザー獲得」レポートを再現したい
   　／ 初回流入チャネル別のLTVを見たい
   → traffic_source を使う（ユーザースコープ）

Q. 特定キャンペーンのUTMが正しく飛んでいるか検証したい
   → collected_traffic_source を使う（イベントスコープ・生値）

実際のSQL ― 参照元/メディア別のセッション数

では、実際に書いてみます。GA4の「トラフィック獲得」レポートに相当する、セッション単位の参照元/メディア別セッション数です。

SELECT
  COALESCE(session_traffic_source_last_click.cross_channel_campaign.source, '(direct)') AS session_source,
  COALESCE(session_traffic_source_last_click.cross_channel_campaign.medium, '(none)')   AS session_medium,
  COUNT(DISTINCT CONCAT(
    user_pseudo_id, '-',
    CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
  )) AS sessions
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260901' AND '20260907'
GROUP BY 1, 2
ORDER BY sessions DESC

3-3. 同じように混乱した、その他の項目

参照元は代表例ですが、「名前から中身が読めない」「似た項目が複数ある」系の罠は他にもあります。 私が実際にやらかしたものを挙げておきます。

① セッション数：session_id という列は存在しない

GA4の主要指標であるセッション数を数えようとして、まず列を探しました。ところが ありません。 代わりに event_params の ga_session_id を使います。

そしてもう一段の罠があります。ga_session_id は、そのユーザーの中でしかユニークではありません。 別のユーザーが同じ値を持ち得ます。

-- ✗ 間違い：ユーザー間で衝突するため、GA4より少ない数字になる
COUNT(DISTINCT (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))

-- ○ 正しい：user_pseudo_id と組み合わせて初めてセッションを一意に識別できる
COUNT(DISTINCT CONCAT(user_pseudo_id, '-',
  CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)))

実際、私は最初は前者で数えて「GA4の画面より少し少ないけど、まあ近いから合ってるだろう」と流しかけました。危ないところでした。

② session_engaged の値は、整数の1ではなく 文字列の ‘1’

次は「エンゲージのあったセッション数」を出そうとしたときの話です。

-- ✗ これだと全部 NULL になり、エンゲージセッションがゼロになる
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'session_engaged')

-- ○ 文字列側に入っている
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged')

ところが engagement_time_msec は int_value なのに、session_engaged は string_value。同じ event_params の中で型が揃っていません。 これはハマると原因に気づきにくいタイプの罠です。エラーにならず、静かにゼロになる のが悪質です。

なお、実装や時期によって int_value 側に入るケースもあるため、私は保険をかけています。

COALESCE(
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged'),
  CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'session_engaged') AS STRING)
) AS session_engaged

③ 日付が2種類あり、タイムゾーンが違う

なお event_date と event_timestamp は、基準が違います。

そのため、こう書くと日付がズレます。

-- ✗ UTCで日付を作っているので、JSTの深夜帯が前日に寄る
DATE(TIMESTAMP_MICROS(event_timestamp)) AS dt

-- ○ タイムゾーンを明示する
DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS dt

しかも _TABLE_SUFFIX はテーブル名（＝event_date 相当）です。そのため 「suffixで9月1日を指定したのに、timestampから作った日付列では8月31日の行が混じる」 という現象が起きます。私はこれで日次グラフが1日ズレたレポートを一度出しました。

④ items を UNNEST すると売上が二重計上される

これはEC案件の定番です。items は商品の配列なので、UNNEST すると 1つの購入イベントが、商品数ぶんの行に増えます。

-- ✗ 3商品買った1件の購入が3行になり、購入金額が3倍になる
SELECT SUM(ecommerce.purchase_revenue)
FROM `my-project.analytics_123456789.events_*`, UNNEST(items)

-- ○ 売上合計はイベント単位で集計する
SELECT SUM(ecommerce.purchase_revenue)
FROM `my-project.analytics_123456789.events_*`
WHERE event_name = 'purchase'

-- ○ 商品別の内訳が欲しいときだけ items 側の金額を使う
SELECT i.item_name, SUM(i.item_revenue)
FROM `my-project.analytics_123456789.events_*`, UNNEST(items) AS i
WHERE event_name = 'purchase'
GROUP BY 1

つまり UNNEST は行を増やす操作 であることを、指標ごとに意識する必要があります。

⑤ user_id と user_pseudo_id は別物

なお「ユーザー数」を COUNT(DISTINCT user_pseudo_id) で出すと、GA4画面の「アクティブユーザー」とは一致しません。 GA4側は独自の判定（is_active_user 列が参考になります）やモデル化を含んでいるためです。

⑥ page_location はフルURL

クエリパラメータが付いたままなので、そのまま GROUP BY するとページが無限に分裂します。したがって、パスだけ欲しいなら加工が必要です。

REGEXP_EXTRACT(
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
  r'^https?://[^/]+([^?#]*)'
) AS page_path

3-4. 私が行き着いた対処法

正直なところ、項目の暗記は無理です。そのため私は、次の4つで対処しています。

いちばん効いたのは「②の逆引き」

このうち、特に効いたのは ② です。スキーマを上から眺めて「使えそうな項目」を探すアプローチをやめ、「GA4の画面で言うとこの数字」から入る ようにしました。それだけで、手戻りが激減しています。

4. 苦労したポイント② ― クエリ容量が膨れ上がり、課金額が問題になる

こちらは、手戻りではなく 請求書 で殴られるタイプの壁です。

私は実際に、「クエリ料金が高すぎるので改善してほしい」という依頼を、何度も受けました。 しかもこれは、設計を間違えたから起きたというより、GA4のデータ構造上、素直に作ると必ずそうなる 類の問題です。

4-1. なぜGA4は、こんなにクエリ容量を食うのか

理由は単純です。つまり 粒度が極端に細かいから にほかなりません。

というのも、GA4はサイト上のあらゆる行動を イベント単位 で記録するからです。1人のユーザーが1回訪問して5ページ見れば、それだけで session_start、first_visit、page_view × 5、scroll、user_engagement …… と、軽く10行以上が積まれます。

ユーザー1人の1セッション ≒ 10〜30イベント（＝10〜30行）
        ↓
1日1万セッションのサイト  ≒ 10万〜30万行 / 日
        ↓
1週間                    ≒ 70万〜200万行

そして行数もさることながら、効いてくるのは 1行あたりの重さ です。event_params、user_properties、items といった REPEATED RECORD（繰り返し構造） が入っているため、1行が驚くほど太い。結果として、

1週間分を集計するだけで、数GB規模のスキャンが発生する ―― これがGA4では普通に起きます。

4-2. BigQueryの課金の仕組み ― 「スキャンしたバイト数」で決まる

ただし、ここを正確に押さえないと対策の方向を間違えます。BigQueryのオンデマンド課金は、

クエリが読み取った（スキャンした）データ量のバイト数 で決まります。

クエリの実行回数でも、返ってきた行数でもありません。 そして重要なのは、BigQueryが 列指向 データベースであることです。

そしてGA4特有の必須テクニックが、_TABLE_SUFFIX での期間絞り込み です。

-- ✗ 全期間の全テーブルをスキャン
FROM `my-project.analytics_123456789.events_*`

-- ○ 7テーブル分しか読まない
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260901' AND '20260907'

つまり 日付シャーディングされているので、_TABLE_SUFFIX で絞れば、その日数分のテーブルしか読みません。 これは必ず入れます。

4-3. 爆発するのは「BIツールに繋いだ瞬間」

ただし、ここまではまだ問題になりません。なぜなら、数GBのクエリを1日に数回投げるだけなら BigQueryには月1TiBの無料枠があるので、実質ゼロ円で済む からです。

しかし問題が起きるのは、BIツールと連携して、複数ユーザーがダッシュボードを使い始めたとき です。

【ダッシュボードの利用実態】

  ユーザーが期間フィルタを「先月」に変更   → クエリ1回
  チャネルを「Organic」に絞る              → クエリ1回
  デバイスを「Mobile」に絞る               → クエリ1回
  ページを見比べるためタブを切り替える      → クエリ1回
  ...

  ダッシュボードのフィルタ操作 1回 = クエリ 1回

BIツールは、ユーザーが操作するたびに裏でクエリを投げます。 もちろんこれは当たり前の挙動です。ところが、GA4の生テーブルに直結していると、その1回1回が数GBのスキャンになります。

そして、こうなります。

  利用者 10人 × 1日20操作 × 月20営業日 = 4,000クエリ / 月

試算してみる ― 読ませるテーブルを変えるだけで2桁変わる

想像どおり、とんでもないスキャン量 になります。実際に試算してみましょう（オンデマンド課金・1 TiB あたり $6.25、月1 TiB の無料枠あり、という条件での概算です）。

金額の絶対値以上に見ていただきたいのは、同じ分析内容でも、読ませるテーブルを変えるだけで2桁変わる という点です。しかも利用者が増えるほど、この差は線形に開いていきます。

要するに、「データ活用を社内に広めましょう」と言った矢先に、広まるほど課金が増える という構造になっているわけです。

4-4. 解決策：マートテーブルを作る ― ただし「粒度」が命

答えはシンプルです。つまり あらかじめ加工・軽量化したテーブル（マートテーブル）を作り、BIツールにはそちらを読ませる ことです。

ただし、やみくもに作っても効果は出ません。 ここが本題です。前提として押さえるべきなのは、

クエリ容量は、読ませるテーブルのデータ量に比例して膨らむ。

という一点です。つまり、マートテーブルの価値は「どれだけ削れたか」で決まります。 加工してあることではなく、軽いことが本質です。

その前に ― パーティションの設定は「絶対条件」です

ただし、削り方の話に入る前に、ひとつだけ絶対に外せない条件があります。

マートテーブルには、必ずパーティションを設定すること。これは「やったほうがよい工夫」ではなく、絶対条件です。

なぜなら パーティションを切っていないテーブルは、WHERE で期間を絞っても、その列が全期間ぶんスキャンされる からです。

【パーティションなしのマート】
  WHERE event_date BETWEEN '2026-09-01' AND '2026-09-07'
    → 絞り込み自体はできる。が、event_date 列は全期間ぶん読まれる
    → 1年分溜まっていれば、7日分を見るだけで 1年分の課金

【パーティションあり（PARTITION BY event_date）】
  WHERE event_date BETWEEN '2026-09-01' AND '2026-09-07'
    → 該当する 7 パーティションだけを読む
    → 課金は 7/365 に落ちる

「作った当初は安かったのに、半年で高くなった」の正体

どれだけ列を削って軽いマートを作っても、毎回全期間がスキャンされるなら、データが溜まるほど課金は線形に増え続けます。 実際、「作った当初は安かったのに、半年経って高くなった」という相談は、ほぼこれが原因です。

逆に言えば、パーティションさえ切ってあれば、「直近28日だけ見る」という普通の使い方が、そのまま課金の削減として効きます。 そして何より大きいのは、利用者に節約を意識させずに済む ことです。

削る対象には、効く順番がある

そのうえで、何から削るかを考えます。効果の大きさには、はっきりした順番があります。

配列（REPEATED RECORD）が特に高くつく理由

そして列が1位なのには、配列特有の理由 があります。

UNNEST は行数を変える演算です。 そのため、配列を UNNEST しているクエリでは、たとえ配列の中身を1つも参照していなくても、BigQuery は行数を確定させるために配列を読まなければなりません。

これが何を生むかというと、「配列のフィールドを使わないクエリのほうが、使うクエリより高い」という逆転 です。実際、マートに配列列を持たせ、利用側のビューで常時 UNNEST して展開していたケースを計測（dry run）すると、こうなりました。

「内訳を配列で持たせておけば、必要なときだけ展開すればいい」――これは直感的には正しく見えます。ところが、利用側が常時 UNNEST する作りになっていると、逆に全クエリを重くします。

対処はシンプルで、配列をやめて、素直にフラットな行に展開してしまう ことです。もちろん行数は増えます。ただし、配列の中身が1件だけの行が大半なら、行数の増加はごくわずか で済みます（上のケースでは約1.13倍でした）。したがって 「配列で圧縮する」より「フラットにして列を減らす」ほうが、BigQuery では安くなる と考えておくのが実務的です。

原則は「最小構成」 ― ただし、そのままでは終われない

以上をふまえると、原則としては 目的に合わせて最小構成にするのが、クエリ容量としては最安 です。例えば「チャネル別の日次セッション数だけ見たい」なら、日次集計テーブルを作れば1クエリ数MBで済みます。

ところが、それで終わりにはできませんでした。

4-5. 「最小構成」と「汎用性」のトレードオフ ― 私が汎用マートを選んだ理由

目的別に作り続けると、2つの問題が出る

たしかに理屈はそうです。ところが、目的別に最小構成のマートを作っていくと、マートテーブルが増え続けます。 その結果、2つの問題が出ました。

【問題1：管理が大変になる】
  マートが30本あれば、定義30本・更新ジョブ30本・依存関係30本。
  項目追加のたびに「どれを直すのか」を追う作業が発生する。

【問題2：ユーザーがテーブルを探せなくなる】
  「チャネル別のCVRを見たい」
    → ga4_daily_channel? ga4_session_summary? ga4_cv_by_source?
      ……どれ？
  分析を始める前に、テーブル探しで止まる。

しかし、より深刻なのは 問題2のほう でした。

膨大なマートテーブルの中から、自分がやりたい分析に最適なものを探す作業は、それ自体がデータ活用のボトルネックになります。 そして、ボトルネックがある基盤は使われません。つまり、使われないデータ基盤は、どれだけ綺麗に設計されていても価値がゼロです。

つまり データ活用の最優先事項は「使ってもらうこと」 なのです。そして、ここを外すと、コスト最適化に成功して事業価値をゼロにする、という笑えない結果になります。

私の落としどころ ― event_params だけを落とした汎用マート

そこで私は、ある程度の汎用性を確保したマートを1本立てる 運用にしています。具体的には、

event_params などの「最も粒度の細かいイベント単位の項目」だけを排除し、それ以外は残した、イベント単位の汎用テーブル。

まず event_params から必要なキー（ga_session_id、page_location、session_engaged など）を あらかじめ平坦な列として抽出しておきます。 そのうえで、REPEATED RECORD 本体は落とす。こうすれば 容量の大部分を削りながら、イベント単位の柔軟性は維持できます。

結果的に落ち着いた構成 ― DWHを土台に、マートを横に並べる

実運用では、結果的にこういう構成に落ち着いています。

【L0】GA4生エクスポート（analytics_XXXXXXXXX.events_*）
   │
   ▼
【L1】DWH ― イベント粒度を保持したテーブル
   │      ★ すべてのマートが参照する土台
   │
   ├──▶ 【L2】汎用マート（イベント粒度のまま、配列だけを排除）
   │
   └──▶ 【L2】目的別マート／日次集計（粒度も落とした最小構成）

ここで大事なのは、L2の2種類は積み重なっているのではなく、L1（DWH）の上に「並列に」並んでいる という点です。つまり どのマートも、DWHという同じ土台から作られます。

そして 汎用マートをきちんと1本用意しておくと、目的別マートは「本当に頻繁に見られるものだけ」で済みます。 目的別マートを無限に作らなくてよくなる、という意味でも、この層は効いています。

DWH層（L1）の役割 ― 派生計算を1回で済ませる

さらに、土台であるDWH層には重要な役割があります。それは 「重い派生計算を、ここで1回だけ済ませておく」 ことです。

特に効くのは「分類ロジックの前倒し」

このうち、特に2番目が効きます。なぜなら 分類ロジックをダッシュボード側に書くと、クエリのたびに巨大な構造体を読んで CASE を評価し直すことになる からです。逆に、これをDWH構築時の1回に寄せるだけで、そのタイルのスキャン量が劇的に落ちることがあります。つまり 「計算を、実行時から構築時に移す」 ―― これもコスト削減の立派な手段です。

4-6. 実装 ― 汎用マートのつくり方

では、実際のSQLを見ていきます。ポイントはコメントに書き込んでいます。

掲載しているSQLの前提

-- 【L2】汎用マート：イベント単位のまま、event_params 等の REPEATED RECORD を排除する
CREATE OR REPLACE TABLE `my-project.mart.ga4_events_base`
PARTITION BY event_date                                -- ① 日付でパーティション分割【絶対条件】
CLUSTER BY event_name, session_source, device_category -- ② よく絞る列でクラスタリング【強く推奨】
OPTIONS (require_partition_filter = TRUE)              -- ③ 期間指定なしのクエリを禁止する【強く推奨】
AS
SELECT
  -- ===== 日付・時刻（タイムゾーンを明示して揃える） =====
  PARSE_DATE('%Y%m%d', event_date)                          AS event_date,
  DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS event_datetime,

  -- ===== イベント・ユーザー =====
  event_name,
  user_pseudo_id,
  user_id,

  -- ===== セッションキー（user_pseudo_id と組み合わせて一意にする） =====
  CONCAT(user_pseudo_id, '-', CAST(
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING
  )) AS session_key,

  -- ===== event_params から「使うキーだけ」平坦な列として取り出す =====
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  REGEXP_EXTRACT(
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
    r'^https?://[^/]+([^?#]*)'
  ) AS page_path,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title')            AS page_title,
  (SELECT value.int_value    FROM UNNEST(event_params) WHERE key = 'engagement_time_msec')  AS engagement_time_msec,
  (SELECT value.int_value    FROM UNNEST(event_params) WHERE key = 'entrances')             AS entrances,
  COALESCE(
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged'),
    CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'session_engaged') AS STRING)
  ) AS session_engaged,

  -- ===== 参照元（スコープごとに、名前で区別できるようリネームしておく） =====
  COALESCE(session_traffic_source_last_click.cross_channel_campaign.source, '(direct)') AS session_source,
  COALESCE(session_traffic_source_last_click.cross_channel_campaign.medium, '(none)')   AS session_medium,
  session_traffic_source_last_click.cross_channel_campaign.campaign_name                AS session_campaign,
  session_traffic_source_last_click.cross_channel_campaign.default_channel_group        AS session_channel_group,
  COALESCE(traffic_source.source, '(direct)') AS first_user_source,   -- ユーザースコープ（初回接触）
  COALESCE(traffic_source.medium, '(none)')   AS first_user_medium,

  -- ===== 端末・地域（RECORD はフラット化。必ず別名を付ける ※後述） =====
  device.category                 AS device_category,
  device.operating_system         AS device_os,
  device.web_info.browser         AS device_browser,
  geo.country                     AS geo_country,
  geo.region                      AS geo_region,
  geo.city                        AS geo_city,

  -- ===== EC（イベント単位の金額。items は載せない） =====
  ecommerce.transaction_id        AS transaction_id,
  ecommerce.purchase_revenue      AS purchase_revenue,

  platform,
  stream_id

-- ★ event_params / user_properties / items は「意図して」載せない。ここが容量削減の本体
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND FORMAT_DATE('%Y%m%d', CURRENT_DATE('Asia/Tokyo'))

パーティション・クラスタリング・期間フィルタ強制の3点セット

繰り返しになりますが、① PARTITION BY は絶対条件 です。ここを書き忘れると、列を削った効果が期間の伸びで打ち消されていきます。一方 ② CLUSTER BY は強く推奨、という位置づけになります。

③ require_partition_filter ― 「絶対条件」を仕組みで守る

ただし、パーティションを切っても 利用者が期間を指定してくれなければ意味がありません。 これを性善説に任せないためのオプションが require_partition_filter です。

そして有効にしておくと、期間で絞っていないクエリは 実行される前に こう弾かれます。

Cannot query over table 'my-project.mart.ga4_events_base' without a filter
over column(s) 'event_date' that can be used for partition elimination

課金される前に止まる のがポイントです。そのため「全期間を舐めるクエリを、うっかり誰かが投げる」という、いちばん高くつく事故を構造的に防げます。要するに、パーティションを切ることが絶対条件なら、これはその絶対条件を運用に定着させるための設定 です。したがって、セットで入れることをお勧めします。

増分更新（毎日フル再作成はしない）

ここでひとつ注意があります。上のDDLを毎日 CREATE OR REPLACE で回すと、毎回全期間をスキャンすることになり、本末転倒です。 そのため、直近数日だけを作り直す形にします。

-- 直近4日ぶんだけ作り直す（スケジュールされたクエリで毎日実行）
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 4 DAY);

-- ① 再処理ウィンドウのパーティションを削除
--    （event_date で絞っているのでパーティション単位の削除になり、スキャンは発生しない）
DELETE FROM `my-project.mart.ga4_events_base`
WHERE event_date >= start_date;

-- ② 同じ範囲を入れ直す
INSERT INTO `my-project.mart.ga4_events_base`
SELECT /* 上のDDLと完全に同じ SELECT 句 */
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date)
                        AND FORMAT_DATE('%Y%m%d', CURRENT_DATE('Asia/Tokyo'));

素のSQLの弱点と、Dataform / dbt という答え

ただし、この INSERT INTO … SELECT には弱点があります。列の並び順と型が、テーブル定義と完全に一致している必要がある のです。つまり DDL側とINSERT側で同じSELECT句を二重管理することになる わけで、ここが素のSQLで運用する場合の一番の弱点になります。

そこで実務では、この二重管理を避けるために Dataform や dbt のような変換ツールを使うのが現実的な答え になります。Dataform なら type: “incremental” を指定するだけで、「初回はCREATE、以降は差分だけINSERT」をSELECT句1本のまま面倒を見てくれます。 パーティションと再処理ウィンドウも設定として書けます。

config {
  type: "incremental",
  bigquery: {
    partitionBy: "event_date",
    updatePartitionFilter: "event_date >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 4 DAY)"
  }
}

確定済みの過去日を直すときは、作り直さず MERGE で

もうひとつ、運用で必ず出てくる話があります。例えば外部データ（受注実績など）をJOINしているマートでは、GA4側は変わっていないのに、JOIN先の値だけが後から変わる ことがあるのです。返品やキャンセルの反映が典型でしょう。

このとき、その日のパーティションを作り直すとどうなるか。GA4の巨大なテーブルを再スキャンすることになり、非常に高くつきます。 そこで私は、確定済みの日については MERGE で「変わった列だけ」をピンポイントに UPDATE する 形にしています。こうすればGA4側の再スキャンをゼロにしたまま、金額だけを最新化できます。

要するに「作り直す（DELETE + INSERT）」と「部分的に直す（MERGE）」を、再処理ウィンドウの内か外かで使い分ける ということです。これは覚えておくと効きます。

粒度を落とす目的別マートを作るときの定石 ― ANY_VALUE

DWHと汎用マートはイベント単位なので GROUP BY は要りません。ところが、目的別マートでセッション単位・日次に粒度を落とすとき には、ひとつ定石があります。

というのも、集計してしまうと device_category や session_source のような 「セッションの中では絶対に変わらない列」も、集計関数を通さないと SELECT できない からです。そこで使うのが ANY_VALUE です。

セッション内で不変な列は ANY_VALUE で残す

具体的には、こう書きます。なお前節のとおり 目的別マートも汎用マートの上に積むのではなく、DWHから直接作ります。

SELECT
  event_date,
  session_key,
  -- ① セッション内で不変な列は ANY_VALUE で1値だけ残す（GROUP BY を増やさずに済む）
  ANY_VALUE(session_source)    AS session_source,
  ANY_VALUE(session_medium)    AS session_medium,
  ANY_VALUE(device_category)   AS device_category,
  ANY_VALUE(geo_country)       AS geo_country,
  -- ② 加算できる指標は普通に集計する
  COUNTIF(event_name = 'page_view')   AS pv_count,
  COUNTIF(event_name = 'purchase')    AS purchase_count,
  SUM(engagement_time_msec)           AS engagement_time_msec_sum
FROM `my-project.dwh.ga4_events`          -- ★ 汎用マートではなくDWHから直接作る
WHERE event_date BETWEEN '2026-09-01' AND '2026-09-07'
GROUP BY event_date, session_key

GROUP BY に足すか、ANY_VALUE にするか

では、どちらを選ぶのか。判断基準はひとつです。

• セッション内で値が1つに定まる列 → ANY_VALUE（GROUP BY に足しても行は増えません。ただし、そのぶん管理が煩雑になります）
• セッション内で複数の値を取りうる列 → GROUP BY に足す（なぜなら ANY_VALUE にすると、どれか1つが選ばれて 他が消える からです）

そして後者を間違えると、エラーにならずに静かに値が落ちます。 例えば地域（geo_*）は、1セッションが移動中だと複数の値を持ち得ます。にもかかわらず「セッション内で不変だろう」と決めつけて ANY_VALUE にすると、集計が合わなくなります。したがって 迷ったら GROUP BY に足して、行数がどれだけ増えるかを実測してから決める のが安全です。

ウィンドウ関数で「セッション単位に持ち上げる」のは慎重に

最後に、もうひとつ落とし穴があります。エンゲージメントのようなフラグを、MAX(…) OVER (PARTITION BY session_key) でセッション全体に広げたくなることがあるのです。ところが、これをやると指標の定義そのものが変わります。

例えばエンゲージセッションのフラグは session_start イベントに付く値です。そのため、それをセッション内の全行に広げると ページ単位で見たときに「本当はそのページでは発生していないフラグ」が立ちます。 実際、私はこれでURL絞り込み時の数字が3倍になったことがあります。

したがって 広げるのではなく、元の粒度のまま持たせる のが正解です。そのうえでBIツール側の指標定義を COUNT(DISTINCT …) にして重複を吸収する。こちらのほうが、GA4側の定義とズレません。

4-7. それでも張っておく、コストの安全網

ただし、マートを作っても 誰かが生テーブルに SELECT * を投げれば一撃です。 そのため、設計だけに頼らず仕組みで止めます。

何より重要なのは「誰のどのクエリが高いか」の可視化

そして、仕組みで止める以上に重要なことがあります。それは 「誰の・どのクエリが高いのか」を可視化しておく ことです。幸い、これは INFORMATION_SCHEMA で取れます。

-- 直近30日で、課金バイト数が多いユーザーを洗い出す
SELECT
  user_email,
  COUNT(*)                                                    AS job_count,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 3)            AS billed_tib,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 6.25, 2)     AS est_usd  -- 単価は要確認
FROM `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state    = 'DONE'
GROUP BY user_email
ORDER BY billed_tib DESC

これを見れば、「高いのはダッシュボードなのか、特定の人のアドホッククエリなのか」が一目で分かります。 そして原因の切り分けができれば、対策は具体的になります。逆に、これを見ずに「クエリを減らしましょう」と言っても、何も変わりません。

5. まとめ

GA4 × BigQuery は、間違いなく強力な組み合わせです。ただし、GA4の画面という「親切な世界」から、生データという「自由だが何も保証されない世界」に出る ということでもあります。つまり自由と引き換えに、データの意味づけと、コストを自分で背負う ことになる。そして私がぶつかった2つの壁は、どちらもその代償でした。

壁① 項目が膨大で、どれが何のデータか分からない

• source / medium は3か所以上にあり、違いは「スコープ」
  • traffic_source → ユーザースコープ（初回接触）。ただし名前が短いだけで、「このイベントの参照元」ではない。ここが最大の罠
  • session_traffic_source_last_click → セッションスコープ。GA4の トラフィック獲得レポート を再現するならこれ
  • collected_traffic_source → イベントスコープの生値。UTMの検証用
• セッションを数えるには user_pseudo_id + ga_session_id の組み合わせが必要。なお session_id という列は存在しない
• session_engaged は文字列の ‘1’。int_value で取ると エラーにならず静かにゼロになる
• event_date（プロパティTZ）と event_timestamp（UTC） は基準が違う。そのため、混ぜると日付がズレる
• _TABLE_SUFFIX は BETWEEN で上下を閉じる。 >= だけで書くと events_intraday_* が混ざり、当日分が二重計上される
• items の UNNEST は行を増やす。売上を二重計上しないよう指標ごとに使い分ける。外部データのJOINも同じ（fanout）
• 対処法は 「スキーマから探す」のをやめて「GA4の画面の指標名から逆引きする」。そして 選んだ理由を項目辞書に残す

壁② クエリ容量が膨れ上がる

• 原因は イベント単位という極端に細かい粒度 × REPEATED RECORD の重さ。1週間で数GBは普通に起きる

課金の仕組みと、爆発する場所

• 課金は スキャンしたバイト数。SELECT * は最悪、LIMIT は無意味、_TABLE_SUFFIX での期間絞りは必須
• BIツール × 複数ユーザー × フィルタ操作 で爆発する。つまりフィルタ1操作＝クエリ1回
• 解決策は マートテーブル。ただし価値は「加工してあること」ではなく「軽いこと」
• マートへの PARTITION BY は「絶対条件」。 なぜなら、パーティションが無いテーブルは期間で絞っても全期間がスキャンされるため、列を削って軽くした効果がデータの蓄積とともに消えていく。GA4の生テーブルは日付シャーディングで自動的に効いているぶん、自分で作るマートで書き忘れやすい最大の落とし穴
• 削る優先順位は 列（REPEATED RECORD）→ 行 → 粒度。最も効くのは列。ただし これはパーティションを切ったうえでの話
• require_partition_filter = TRUE をセットで入れる。 期間を指定していないクエリを 課金される前にエラーで弾ける。絶対条件を仕組みで守るための設定

運用でやること

• CLUSTER BY は強く推奨。BIツール側のデフォルト期間を短くする のも同じくらい効く
• 配列（REPEATED RECORD）は特に高い。 というのも UNNEST は行数を変える演算なので、中身を1つも参照しないクエリでも配列は必ず読まれる。「配列で持つ」より「フラットにして列を減らす」ほうが安い
• 分類ロジックは、クエリ実行時ではなくマート構築時に1回だけ計算して列にしておく。つまり計算を実行時から構築時に移すのも、立派なコスト削減
• 増分更新は「直近3〜4日」の再処理ウィンドウを持たせる。なぜならGA4は過去日のデータが後から書き換わるからです。ウィンドウの外の確定済み日は、作り直さず MERGE で部分更新する
• 素のSQLだと DDLとINSERTでSELECT句を二重管理する ことになる。Dataform / dbt の incremental を使うのが現実的な答え
• また、設計に頼りきらず 課金される最大バイト数・カスタム割り当て・予算アラート で仕組みとして止める
• そして INFORMATION_SCHEMA で「誰の・どのクエリが高いか」を必ず可視化する

最後に ― 「最安の設計」と「使われる設計」は違う

要するに、ここまで書いてきたマートテーブルの話は 技術的な最適解と、運用上の最適解が一致しなかった 例です。

たしかに、目的別に最小構成のマートを並べるほうが、クエリ容量は安く、設計としては綺麗に見えます。それは分かっています。それでも私は、event_params だけを落とした汎用テーブルを1本立てる、という「お作法的には議論が分かれる」形を選びました。

理由は、ユーザーが膨大なテーブルの中から最適なものを探す作業が、データ活用そのもののボトルネックになるから です。つまり、テーブル探しで止まる基盤は使われません。そして 使われないデータ基盤の価値は、設計の美しさに関係なくゼロ です。

一番大事なのは、使ってもらうこと

データ活用支援という仕事をしていると、ここは何度も突きつけられます。一番大事なのは、使ってもらうこと。 したがって、コスト最適化も設計の綺麗さも、その手段でしかない。私が汎用マートという運用形態をとっている一番の理由は、結局そこにあります。

もちろん、これが唯一の正解だとは思っていません。組織の規模や、利用者のSQLスキル、案件の性質によって答えは変わるはずです。この記事を「そういうトレードオフがあるのか」と知った状態で設計を始めるための材料 にしていただければ、それで十分です。

最後にもう一度。冒頭に書いたとおり、当時の私はこの2つの壁について書かれた記事をほとんど見つけられず、手戻りと請求書で学びました。この記事が、その屍を踏み越えるための足場になれば幸いです。

参考リンク

GA4側のドキュメント（3章の裏取りに）

• エクスポートされるスキーマの定義（Google アナリティクス ヘルプ）
• BigQuery Export の設定方法と制限事項
• データ保持期間の設定について
• 試すだけなら便利な、GA4のBigQueryサンプルデータセット

BigQueryのコスト管理（4章の裏取りに）

• 料金体系の一覧（Google Cloud）
• クエリのコストを見積もる・制御する
• カスタム割り当てでクエリ使用量に上限を設ける
• ジョブのメタデータを調べる（INFORMATION_SCHEMA.JOBS）
• BI Engine の概要

テーブル設計と運用（4-6の実装まわり）

• パーティション分割テーブルの概要
• require_partition_filter を設定する（パーティション分割テーブルの管理）
• クラスタ化テーブルの概要
• Dataform で増分テーブルを作成する（type: “incremental”）