はじめに：SQLが散らかって、再現できなくなる問題

BigQueryでGA4のデータを触りはじめると、最初のうちはとても快適です。クエリエディタを開いて、思いついた集計をその場で書いて、結果を見て、また書き直す。この手軽さがBigQueryの良いところで、中小ECの分析でも十分に役立ちます。

ところが、しばらく運用していると、だんだん雲行きが怪しくなってきます。よくあるのが、次のような状態です。

  • 同じような集計クエリが、保存済みクエリやスプレッドシート、誰かのローカルメモに散らばっている
  • 「先月のレポートで使ったあのクエリ」が見つからず、似たものを一から書き直す
  • 列の意味（例：session_default_channel_group をどう分類したか）が人によって微妙に違う
  • 元データの取り込みが変わったときに、どのクエリを直せばいいのか追いきれない

つまり、同じ数字をもう一度作れない状態になってしまうわけです。分析の結論そのものは正しくても、「なぜこの数字になるのか」を後から再現できないと、レポートの信頼性はじわじわ下がっていきます。誰かが退職したり、数か月後の自分が見返したりしたときに、誰も全体像を説明できない、という事故にもつながります。

この記事では、その散らかりを整理するための道具として dbt（data build tool） を紹介します。dbtを使うと、BigQueryのSQLを「再現可能なパイプライン」として組み立てられます。GA4のエクスポートデータを題材に、staging（整える層）から mart（使う層）までを順に組み上げる例を、実際のSQLとともに見ていきます。

なお、データのテストや定期更新といった周辺の仕組みについては、関連記事も用意しています。あわせて読むと、運用の全体像がつかみやすくなります。

dbtとは何か：SQLを「部品」として組み立てる道具

dbtは、ひとことで言うと SQLでデータ変換のパイプラインを書くためのツール です。BigQueryのようなデータウェアハウスに対して、変換（Transform）の部分を担当します。データの取り込み（Extract / Load）はやりませんが、「取り込んだ生データを、使いやすい形に整える」ところを得意とします。

特別なプログラミング言語を覚える必要はありません。基本はSQLの SELECT 文を書くだけです。そのうえで、dbtならではの仕組みがいくつか乗っているだけ、と考えると気が楽になります。中心になるのは次の4つです。

model（モデル）：1ファイル＝1つのSELECT文

dbtでは、SELECT 文を1つ書いた .sql ファイルを model と呼びます。1ファイルが1つのテーブル（またはビュー）に対応します。「どう作るか」だけを書けばよく、CREATE TABLE や CREATE VIEW のような定型文はdbtが自動で補ってくれます。

つまり、これまで頭の中やメモに散らばっていた「あの集計クエリ」を、名前のついたファイルとして1か所に集められる、ということです。

ref：モデル同士をつなぐ参照

ref() は、別のモデルを参照するための書き方です。FROM {{ ref('stg_ga4_events') }} のように書くと、「stg_ga4_events というモデルの出力を入力として使う」という意味になります。

これの何がうれしいかというと、dbtが ref のつながりを読み取って、実行順序を自動で決めてくれる点です。「AはBに依存し、BはCに依存する」という関係を、こちらが手で管理しなくて済みます。テーブル名をベタ書きしないので、データセット名が変わっても一括で追従できます。

test：データの前提を検査する

test は、「この列はNULLであってはいけない」「この列は重複してはいけない」といった前提を宣言する仕組みです。unique や not_null といった標準のテストを設定ファイルに書くだけで、dbt test を実行したときに自動でチェックされます。

データが想定どおりかを毎回SQLで確かめる手間が省け、前提が崩れたときにすぐ気づけます。テストの考え方を深掘りしたい場合は、BigQueryでSQLテストを書いてデータマートの品質を担保する もあわせてどうぞ。

docs：定義をドキュメントとして残す

docs は、モデルや列の説明をその場に書き残し、依存関係を図として見られるようにする機能です。「このテーブルは何か」「この列はどういう意味か」を、SQLのすぐ隣に書いておけます。

属人化を防ぎ、数か月後の自分や他の人が見たときに迷わなくなる、というのがdocsのねらいです。

GA4データをdbtで組む：staging → mart の例

ここからは実際に、GA4のBigQueryエクスポートを題材にパイプラインを組んでみます。設計の基本は、層を分けることです。よく使うのは次の2層構成です。

  • staging層：生データを「整える」だけの層。型をそろえ、列名を読みやすくし、必要なものを選ぶ
  • mart層：staging層を組み合わせて「使える」形にした層。レポートやダッシュボードが直接参照する

生データをいきなり集計するのではなく、まず整える層をはさむのがコツです。GA4のエクスポートは event_params がネストされていて少しクセがあるので、この「整える」工程の価値が特に大きく出ます。

staging層：GA4の生データを整える

まず、GA4エクスポートのイベントテーブルから、必要な値を平らに取り出すモデルを作ります。ファイル名を stg_ga4_events.sql とします。GA4特有のネストした event_params から、ページ階層やセッションIDを引き出すのがポイントです。

-- models/staging/stg_ga4_events.sql
-- GA4エクスポートのイベントを「整える」だけの層

select
  -- event_date は 'YYYYMMDD' の文字列なので日付型に変換する
  parse_date('%Y%m%d', event_date) as event_date,
  timestamp_micros(event_timestamp) as event_timestamp,
  event_name,
  user_pseudo_id,

  -- ネストした event_params から値を取り出して列にする
  (select value.int_value
     from unnest(event_params)
    where key = 'ga_session_id') as ga_session_id,
  (select value.string_value
     from unnest(event_params)
    where key = 'page_location') as page_location,

  device.category as device_category,
  traffic_source.medium as traffic_medium,
  traffic_source.source as traffic_source

-- source('ga4', 'events') は schema.yml で identifier: "events_*" と登録してあるため、
-- events_20260101 などの日付別テーブルをまとめて参照するワイルドカードに展開される
from {{ source('ga4', 'events') }}
-- 直近の更新分だけを対象にする（フルスキャンを避ける）。
-- _table_suffix はワイルドカード参照のときだけ使える擬似列で、
-- ここでは events_* の "*" にあたる 'YYYYMMDD' 部分が入る
where _table_suffix >= format_date('%Y%m%d', date_sub(current_date(), interval 90 day))

ここでやっているのは「整える」だけです。集計はしません。event_date を日付型に変換し、ネストした event_params から ga_session_id と page_location を取り出して、ふつうの列の形にしています。source('ga4', 'events') は、生データのテーブルをdbt側に登録しておくための書き方です。この登録は、後述する設定ファイルで行います。

where で使っている _table_suffix は、events_* のようなワイルドカードでテーブルを参照したときだけ使える擬似列です。後述の schema.yml で identifier: "events_*" と登録しているおかげで、source('ga4', 'events') がワイルドカード参照に展開され、ここで _table_suffix（* にあたる 'YYYYMMDD' 部分）で日付の絞り込みができます。単一テーブルを直接指定した場合は _table_suffix が使えないので、この対応関係はセットで覚えておくと安心です。

staging層をこうして1枚作っておくと、以降のモデルはネストの処理を気にせず、平らになったデータだけを使えます。

mart層：日別のセッション指標にまとめる

次に、整えたstaging層を使って、レポートが直接参照する集計テーブルを作ります。ここでは「日別・チャネル別のセッション数とページビュー数」を出すモデルを mart_daily_sessions.sql として作ります。

-- models/marts/mart_daily_sessions.sql
-- staging層を集計して「使える」形にした層

with events as (
  -- ベタ書きせず ref で staging モデルを参照する
  select * from {{ ref('stg_ga4_events') }}
)

select
  event_date,
  traffic_medium,
  traffic_source,
  count(distinct concat(user_pseudo_id, cast(ga_session_id as string))) as sessions,
  countif(event_name = 'page_view') as page_views,
  countif(event_name = 'purchase') as purchases
from events
group by event_date, traffic_medium, traffic_source

ポイントは FROM {{ ref('stg_ga4_events') }} の部分です。生のGA4テーブルを直接参照するのではなく、整えたstagingモデルを参照しています。こう書いておくと、staging層の中身を直しても、mart層のSQLはそのままで済みます。sessions は user_pseudo_id とセッションIDを組み合わせて一意に数える、GA4でよく使う近似的な数え方にしています。

schema.yml：ソース登録とテストを宣言する

最後に、ソースの登録とテストをまとめた設定ファイルを書きます。dbtでは、こうした宣言を schema.yml のようなYAMLファイルに書きます。

# models/staging/schema.yml

version: 2

sources:
  - name: ga4
    # 生データが入っているBigQueryのデータセットを指定する
    dataset: analytics_000000
    tables:
      - name: events
        # ワイルドカードで日付別テーブルをまとめて扱う
        identifier: "events_*"

models:
  - name: stg_ga4_events
    description: "GA4エクスポートを整えた、イベント単位のstagingモデル"
    columns:
      - name: event_timestamp
        description: "イベント発生時刻（タイムスタンプ型に変換済み）"
        tests:
          - not_null

  - name: mart_daily_sessions
    description: "日別・流入元別のセッションとページビューの集計"
    columns:
      - name: event_date
        tests:
          - not_null

sources で生データのデータセットを登録し、models の下で各モデルや列の説明とテストを宣言しています。not_null は標準で用意されたテストで、dbt test を実行すると、event_timestamp や event_date にNULLが混ざっていないかを自動で確認してくれます。description に書いた説明は、そのままdocsに反映されます。

スケジュール実行：dbt build で一気に通す

モデルとテストがそろったら、それらをまとめて実行します。覚えておきたいコマンドは、まずは次の1つで十分です。

# モデルの作成とテストをまとめて実行する
dbt build

dbt build は、ref の依存関係を読み取って、正しい順序でモデルを作り、そのつどテストも走らせる コマンドです。今回の例なら、stg_ga4_events を先に作り、そのテストを通し、続いて mart_daily_sessions を作って、またテストを通す、という流れを自動でこなしてくれます。途中のテストが失敗すれば、そこで気づけます。

実運用では、この dbt build を毎日決まった時刻に動かすことになります。dbtにはクラウド上で実行をスケジュールする仕組みもありますが、すでにGoogle Cloudを使っているなら、Cloud SchedulerやCloud Run、あるいはBigQuery側のスケジュール機能と組み合わせて回す方法もあります。「毎日決まった時刻にデータマートを更新する」という考え方そのものは、スケジュールクエリでデータマートを毎日自動更新する で扱っているので、運用イメージを固めたい方はそちらも参考になります。

定期実行にしておくと、毎朝レポートを開いたときには、テスト済みの最新データがすでにできあがっている、という状態を作れます。これが「再現可能なパイプライン」の到達点です。

小さく始める：いきなり全部を作らない

ここまで読むと「導入が大変そう」と感じるかもしれませんが、dbtは小さく始められるのが良いところです。最初から全部のクエリをdbt化する必要はありません。

おすすめの順番は、次のとおりです。

  1. いま一番よく使っている集計クエリを、1つだけmartモデルにする
  2. その手前に、生データを整えるstagingモデルを1枚はさむ
  3. 主キーになる列に not_null と unique のテストを足す
  4. dbt build を手元で動かして、結果がこれまでの数字と合うか確かめる
  5. 問題なければ、定期実行に乗せる

この1サイクルを回すだけでも、「散らかっていたクエリが、名前とテストのついた再現可能な部品になる」という効果は十分に感じられます。慣れてきたら、2つめ、3つめのモデルを少しずつ移していけば大丈夫です。

最初の1枚を作るときに大切なのは、いまの数字と一致することを必ず確かめることです。新しい道具に移すと、知らないうちに集計の定義がずれてしまうことがあります。移行のたびに古い結果と突き合わせる習慣をつけておくと、安心して範囲を広げられます。

まとめ

この記事では、BigQueryで散らかりがちなGA4の集計SQLを、dbtで再現可能なパイプラインに整える入門を見てきました。要点を振り返ります。

  • 課題：その場でSQLを書く手軽さの裏で、クエリが散らかり、同じ数字を再現できなくなる
  • dbtの基本：model（1ファイル＝1つのSELECT）、ref（モデル同士の参照と実行順序）、test（前提の検査）、docs（定義の記録）
  • 層を分ける：生データを整える staging 層と、使える形にする mart 層に分けると見通しが良くなる
  • GA4の勘どころ：ネストした event_params をstaging層で平らにしておくと、後段が扱いやすい
  • 実行：dbt build で依存順にモデルを作り、テストも一緒に走らせ、定期実行に乗せる
  • 進め方：いきなり全部ではなく、よく使うクエリ1つから小さく始める

「同じ数字をもう一度作れる」状態は、レポートの信頼を支える地味だけれど大事な土台です。まずは手元のよく使うクエリを1つ、stagingとmartの2枚に分けてdbt化するところから試してみてください。データの検査と定期更新まで含めた運用像は、関連記事の BigQueryでSQLテストを書いてデータマートの品質を担保する と スケジュールクエリでデータマートを毎日自動更新する もあわせて読むと、より立体的につかめます。