1. はじめに
業務で GA4（Google Analytics 4）のデータをRedshiftで扱うことになりました。
GA4のデータは JSON 形式で取得できますが、そのままでは Redshift で扱いづらく集計や分析にも向きません。
そのため、GA4のJSONログを、Redshiftで扱いやすい「列展開」の形に変換する処理が必須でした。
本記事では、GA4で取得できるデータ構造を最低限整理した上で、
JSON形式のログを分析しやすい形への方法を解説します。
2. GA4ではどんなデータが取得されているのか
GA4のデータ構造
GA4のログは、イベントベースで構成されています。
実際には、GA4では非常に多くのイベント種別・パラメータが取得されますが、
本記事では 全体像を把握するために一部を切り取って説明します。
- 1行 = 1イベント
- 各イベントには「何が起きたか」「いつ起きたか」が記録される
イベントの種類は event_name で表され、
そのイベントに付随する詳細情報が event_params に格納されます。
event（イベントの基本情報）
イベントには、最低限次のような情報が含まれます。
-
event_name
- 何が起きたかを表すイベント名
- 例：
page_view,click,scroll
-
event_timestamp
- イベントが発生した時刻
- UNIX時間 で記録される
→ これらは すべてのイベントに共通して存在する基本情報です。
event_params（イベントに付随する詳細情報）
event_params は、イベントごとの詳細情報を持つパラメータ群です。
- key-value 形式
- 配列構造
- イベントごとに中身が異なる（＝可変）
代表例：
- ページURL（例：
page_location） - ページタイトル（例：
page_title） - 参照元（例：
page_referrer） - カテゴリIDや検索条件などのカスタム情報
3. 今回扱うJSONデータサンプル
以下は、GA4から取得される Webサイトアクセスログの JSON構造を簡略化したサンプルです。
{
"event_name": "page_location",
"event_timestamp": "1234567890123456",
"event_params": [
{
"key": "gad_source",
"value": {
"string_value": "1",
"int_value": null,
"float_value": null,
"double_value": null
}
},
{
"key": "batch_page_id",
"value": {
"string_value": null,
"int_value": "1234567890123",
"float_value": null,
"double_value": null
}
}
]
}
このJSONには、以下のような特徴があります。
-
event_nameはイベントの種類を表す -
event_timestampは UNIX時間（マイクロ秒） で記録されている -
event_paramsは key-value 形式の配列で、値の型が複数用意されている
4. JSONを列展開に変換する理由とSQL解説
JSONのままではRedshiftで扱いづらい理由
JSONログは柔軟ですが、Redshiftで次のような処理を行うには不向きです。
- WHERE句でのフィルタリング
- GROUP BY を使った集計
- JOIN を用いた後続処理
特にevent_paramsのような配列構造は、そのままではSQLで扱いづらくなります。
列展開への変換方針
今回の変換では、次の方針を取りました。
-
event_nameはそのまま列として抽出 -
event_timestampは JST に変換 -
event_paramsは- 必要な key のみを指定
- 値の型に応じて該当カラムを取得
すべての event_params を展開するのではなく、
要件上必要なものだけを列として持つ 形にしています。
データの変換をするSQL
JSONログをRedshiftに取り込んだ後、行指向のテーブルとして抽出するSQLです。
INSERT INTO ga4_event (
event_date,
event_timestamp,
gad_source,
batch_page_id
)
SELECT
-- ga4_raw_eventsテーブルのraw_jsonに入ったevent_dateの抽出
e.raw_json.event_date::varchar AS event_date,
-- event_timestamp（UNIX time → JST へ変換、マイクロ秒で丸め込み）
-- ga4_raw_eventsテーブルのraw_jsonに入ったevent_timestampの抽出・加工
date_trunc(
'second',
convert_timezone(
'UTC', 'Asia/Tokyo',
TIMESTAMP '1970-01-01 00:00:00'
+ (e.raw_json.event_timestamp::double precision / 1e6)
* INTERVAL '1 second'
)
) AS event_timestamp,
-- ga4_raw_eventsテーブルのevent_paramsに入ったkey・valueの抽出
MAX(CASE WHEN ep.key = 'gad_source'
THEN ep.value.string_value END)::varchar AS gad_source,
MAX(CASE WHEN ep.key = 'batch_page_id'
THEN ep.value.int_value END)::varchar AS batch_page_id
-- jsonデータが入っているテーブル
FROM ga4_raw_events e
5. まとめ
本記事では、GA4から取得したJSON形式のイベントログを、Redshiftで扱いやすい列展開へ変換する方法を、一例として紹介しました。
GA4のログは柔軟な一方、event_params の配列構造やマイクロ秒単位の event_timestamp などにより、そのままでは分析に向きません。
これはあくまで 今回の要件に基づく一つのやり方ですが、
GA4のJSONログをDWHに取り込む際に、
- どこを変換対象にするか
- どう整理すれば扱いやすくなるか
を考える際の キャッチアップの助け になれば幸いです。