Zenn
💪

GA4データの複雑な構造に立ち向かう(BigQuery:ARRAY,STRUCT)

2023/04/12に公開
Google Analytics
SQL
BigQuery
tech

あなたの心にsession_start。どうも、SQL芸人です。

Google Analyticsの最新版「GA4」では、BigQueryにデータがエクスポートできます。
ですが、データ構造が入り組んだ状態になっていて、ちょっと難しいです。
そこで、今回はそんなデータ構造と戦ってみたいと思います。

GA4のサンプルデータを見てみる

設定方法

Googleが公開しているデータ群「bigquery-public-data」の中に
「ga4_obfuscated_sample_ecommerce」というデータセットがあって
そこに「events_」というGA4のサンプルテーブルがあるので、まずはそれを見てみたいと思います。

これを見れるようにする方法は、こちらのページをご参照ください！
https://www.ga4.guide/related-service/big-query/bigquery-demo/

データを見てみる

設定ができたら、サンプルデータを見てみましょう。
デフォルトの「2021-01-31」テーブルを表示します。
※このように、"_"の後はyyyymmdd形式の日付でシャーディング（テーブル分割）されてますが、今回は置いておきます

event_paramsのところを開いていますが、なんかヘンですね。
event_paramsは、種類RECORDの、モードREPEATEDになっていて
event_paramsの中にあるvalueは、複数の項目を持っているように見えます。

データのプレビューも見てみましょう。
行は1,2行目…となっていますが、1行の中にevent_params.keyが複数入っています。
そしてvalueは、string_value、int_value…と分かれています。

つまり、この一見複雑な構造で、↓の2つを表現することができているのです。
【1】1つのイベント(event_name)に対し、複数のイベントパラメータ（オプションの情報）がついていること
【2】イベントパラメータには、string型、int型など複数のデータ型がありうるので、それに対応していること（テーブルは列のデータ型を統一させないといけないことに由来）

【1】を表現するデータ型がARRAY型で、【2】を表現するのがSTRUCT型なのですが、次の項で、これを理解しましょう！

ARRAY型とSTRUCT型

ARRAY型

まずは、event_paramsに設定されているARRAY型を解説します。
これは「配列」と言って、1つの値の中に複数の値を並べて置くことができます。

テストクエリを叩いてみましょう！
そのままBQで実行できます。withを使うとテストテーブルが簡単に作れます。

-- テストテーブルを作っている、ココは深く理解しなくていい
with test_tbl as (select * from unnest([
struct('賢子さん' as user_name, ['サラダ','豆腐ハンバーグ'] as favorite_foods)
,('秀斗さん', null)
,('SQL芸人', ['ラーメン','カレー','ハンバーグ'])
]))
--
-- test_tblはすでにあるものと思って、このクエリから見る
select
  *
from
  test_tbl
;

「test_tbl」では、ユーザーは3人ですが、好きな食べ物(favorite_foods)が複数設定されているのがわかります。
秀斗さんは何でも好きなので、null(好きな食べ物なし)となっています。

===
ただ、このままではデータとして扱いにくいので、この配列をバラすこともできます。
UNNESTとCROSS JOINを使います。

-- テストテーブルを作っている、ココは理解しなくていい
with test_tbl as (select * from unnest([
struct('賢子さん' as user_name, ['サラダ','豆腐ハンバーグ'] as favorite_foods)
,('秀斗さん', null)
,('SQL芸人', ['ラーメン','カレー','ハンバーグ'])
]))
--
-- test_tblはすでにあるものと思って、このクエリから見る
select
  user_name
  , fav_food
from
  test_tbl
  cross join unnest(favorite_foods) fav_food
;

理解としてはおおまかに、favorite_foodsをバラして、新しくfav_foodとして定義しているという感じです。結果は5行になっていますね！
（ちなみに「cross join」を「,」に置き換えても同じです、試してみてください）

===
あれ、秀斗さんがいないですね…！このやり方だと、favorite_foodsがnullの行は切れてしまいます。それを防ぐには、LEFT JOINを使います。

-- テストテーブルを作っている、ココは理解しなくていい
with test_tbl as (select * from unnest([
struct('賢子さん' as user_name, ['サラダ','豆腐ハンバーグ'] as favorite_foods)
,('秀斗さん', null)
,('SQL芸人', ['ラーメン','カレー','ハンバーグ'])
]))
--
-- test_tblはすでにあるものと思って、このクエリから見る
select
  user_name
  , fav_food
from
  test_tbl
  left join unnest(favorite_foods) fav_food
;

秀斗さんも表示されていますね！

まとめると、こんな感じです！

  • ARRAY型(配列)は、1つの値に複数の値を並べて置ける
  • バラすには、UNNESTとLEFT JOIN(CROSS JOIN)を使う

STRUCT型

では、STRUCT型はどうでしょうか？
STRUCTは1つの値に複数の値を持っておけるものです！子持ちししゃもみたいな感じです！

テストクエリを叩いてみましょう！

-- テストテーブルを作っている、ココは理解しなくていい
with test_tbl as (select * from unnest([
struct('賢子さん' as user_name, struct('英語' as subject, 95 as score) as exam)
,('秀斗さん', struct('数学' as subject, 100 as score))
,('SQL芸人', struct('英語' as subject, 10 as score))
]))
--
-- test_tblはすでにあるものと思って、このクエリから見る
select
  *
from
  test_tbl
;

今回のtest_tblでは、examというカラムの中に「subject」と「score」があるのがわかりますね。
これがSTRUCT型です。BQでは構造体、プログラム用語だとオブジェクトと呼ばれます。

===
これだとSTRUCT型の恩恵が少ないので、ARRAY型と組み合わせてみます。

-- テストテーブルを作っている、ココは理解しなくていい
with test_tbl as (select * from unnest([
struct('賢子さん' as user_name, [struct('英語' as subject, 95 as score), struct('数学' as subject, 80 as score)] as exams)
,('秀斗さん', [struct('英語' as subject, 90 as score), struct('数学' as subject, 100 as score)])
,('SQL芸人', [struct('英語' as subject, 10 as score), struct('数学' as subject, 0 as score)])
]))
--
-- test_tblはすでにあるものと思って、このクエリから見る
select
  *
from
  test_tbl
;

examsというARRAYの中に、「subject」と「score」を持つSTRUCTが複数入っています。

===
これにUNNESTを使えば、バラすこともできるし

-- テストテーブルを作っている、ココは理解しなくていい
with test_tbl as (select * from unnest([
struct('賢子さん' as user_name, [struct('英語' as subject, 95 as score), struct('数学' as subject, 80 as score)] as exams)
,('秀斗さん', [struct('英語' as subject, 90 as score), struct('数学' as subject, 100 as score)])
,('SQL芸人', [struct('英語' as subject, 10 as score), struct('数学' as subject, 0 as score)])
]))
--
-- test_tblはすでにあるものと思って、このクエリから見る
select
  user_name
  , exam.subject as exam_subject
  , exam.score as exam_score
from
  test_tbl
  left join unnest(exams) exam
;

===
SELECTの中でサブクエリを使えば、英語と数学の点数を一気に取り出すことができます。

クエリの理解が難しいですが
SELECT内のfrom unnest(exams)で、examsをバラしてあげて
where subject = 'XX'で、1行(1つの値)にしている感じです！
※exams内のsubjectがダブってないとき限定

-- テストテーブルを作っている、ココは理解しなくていい
with test_tbl as (select * from unnest([
struct('賢子さん' as user_name, [struct('英語' as subject, 95 as score), struct('数学' as subject, 80 as score)] as exams)
,('秀斗さん', [struct('英語' as subject, 90 as score), struct('数学' as subject, 100 as score)])
,('SQL芸人', [struct('英語' as subject, 10 as score), struct('数学' as subject, 0 as score)])
]))
--
-- test_tblはすでにあるものと思って、このクエリから見る
select
  user_name
  , (select score from unnest(exams) where subject = '英語') as english_score
  , (select score from unnest(exams) where subject = '数学') as math_score
from
  test_tbl
;

===
ARRAY型とSTRUCT型がだいぶ分かったかと思います。
では、いよいよGA4のデータ構造にチャレンジしてみます！

GA4のデータを操作してみる

使うデータ

公式のサンプルデータでもいいのですが、データ量が多いので、こちらでサンプルデータを作ってみました！9行しかないので楽チンです♪

テスト用eventsテーブル
create temp table events
as select * from unnest([
struct(
  '2023-01-01' as event_date, unix_micros(timestamp('2023-01-01 10:10:00.000001', 'Asia/Tokyo')) as event_timestamp
  , 'u01' as user_pseudo_id, 'session_start' as event_name
  , [struct('ga_session_id' as key, struct(cast(null as string) as string_value, 10000001 as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ,struct('page_location' as key, struct('https://dmy.example.com/aaa' as string_value, cast(null as int64) as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ] as event_params
)
,struct(
  '2023-01-01' as event_date, unix_micros(timestamp('2023-01-01 10:10:00.000002', 'Asia/Tokyo')) as event_timestamp
  , 'u01' as user_pseudo_id, 'page_view' as event_name
  , [struct('ga_session_id' as key, struct(cast(null as string) as string_value, 10000001 as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ,struct('page_location' as key, struct('https://dmy.example.com/aaa' as string_value, cast(null as int64) as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ] as event_params
)
,struct(
  '2023-01-01' as event_date, unix_micros(timestamp('2023-01-01 10:20:00.000002', 'Asia/Tokyo')) as event_timestamp
  , 'u01' as user_pseudo_id, 'page_view' as event_name
  , [struct('ga_session_id' as key, struct(cast(null as string) as string_value, 10000001 as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ,struct('page_location' as key, struct('https://dmy.example.com/bbb' as string_value, cast(null as int64) as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ] as event_params
)
,struct(
  '2023-01-01' as event_date, unix_micros(timestamp('2023-01-01 12:30:00.000001', 'Asia/Tokyo')) as event_timestamp
  , 'u01' as user_pseudo_id, 'session_start' as event_name
  , [struct('ga_session_id' as key, struct(cast(null as string) as string_value, 10000002 as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ,struct('page_location' as key, struct('https://dmy.example.com/ccc' as string_value, cast(null as int64) as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ] as event_params
)
,struct(
  '2023-01-01' as event_date, unix_micros(timestamp('2023-01-01 12:30:00.000002', 'Asia/Tokyo')) as event_timestamp
  , 'u01' as user_pseudo_id, 'page_view' as event_name
  , [struct('ga_session_id' as key, struct(cast(null as string) as string_value, 10000002 as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ,struct('page_location' as key, struct('https://dmy.example.com/ccc' as string_value, cast(null as int64) as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ] as event_params
)
,struct(
  '2023-01-01' as event_date, unix_micros(timestamp('2023-01-01 12:30:00.000003', 'Asia/Tokyo')) as event_timestamp
  , 'u01' as user_pseudo_id, 'view_promotion' as event_name
  , [struct('ga_session_id' as key, struct(cast(null as string) as string_value, 10000002 as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ,struct('page_location' as key, struct('https://dmy.example.com/ccc' as string_value, cast(null as int64) as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ,struct('engagement_time_msec' as key, struct(cast(null as string) as string_value, 500 as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ] as event_params
)
,struct(
  '2023-01-01' as event_date, unix_micros(timestamp('2023-01-01 10:11:00.000001', 'Asia/Tokyo')) as event_timestamp
  , 'u02' as user_pseudo_id, 'session_start' as event_name
  , [struct('ga_session_id' as key, struct(cast(null as string) as string_value, 20000001 as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ,struct('page_location' as key, struct('https://dmy.example.com/aaa' as string_value, cast(null as int64) as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ] as event_params
)
,struct(
  '2023-01-01' as event_date, unix_micros(timestamp('2023-01-01 10:11:00.000002', 'Asia/Tokyo')) as event_timestamp
  , 'u02' as user_pseudo_id, 'page_view' as event_name
  , [struct('ga_session_id' as key, struct(cast(null as string) as string_value, 20000001 as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ,struct('page_location' as key, struct('https://dmy.example.com/aaa' as string_value, cast(null as int64) as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ] as event_params
)
,struct(
  '2023-01-01' as event_date, unix_micros(timestamp('2023-01-01 10:21:00.000002', 'Asia/Tokyo')) as event_timestamp
  , 'u02' as user_pseudo_id, 'page_view' as event_name
  , [struct('ga_session_id' as key, struct(cast(null as string) as string_value, 20000001 as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ,struct('page_location' as key, struct('https://dmy.example.com/ddd' as string_value, cast(null as int64) as int_value, cast(null as float64) as float_value, cast(null as float64) as double_value) as value)
    ] as event_params
)
])
;
-- この下にクエリを置く

今回は、一時テーブルというBQの機能を使って、eventsテーブルを作っています。

一時テーブルは、SELECT文が一緒になってないとエラーになるので…

select
  *
from
  events
order by
  user_pseudo_id
  , event_timestamp
;

こんな感じでSELECTをつけてあげて…

実行すると2つのジョブが動くので、2つ目の結果を表示してください。

eventsテーブルが表示されました。このように、GA4と同じ構造で中身の値は簡易になっています！

以降のクエリも、一時テーブルを作る処理の下に書くようにしてください！

データをバラしてみる

では、event_paramsのデータをバラしてみましょう。
ARRAYをバラすには、UNNESTとLEFT JOINでしたよね。

select
  event_date
  , event_timestamp
  , user_pseudo_id
  , event_name
  , ev_param.key as ev_param_key
  , ev_param.value.string_value as ev_param_string_value
  , ev_param.value.int_value as ev_param_int_value
  , ev_param.value.float_value as ev_param_float_value
  , ev_param.value.double_value as ev_param_double_value
from
  events
  left join unnest(event_params) ev_param
order by
  user_pseudo_id
  , event_timestamp
  , ev_param_key
;

※先ほどと同様にCREATE TABLEの下にSELECTを書いてください…！

きれいにバラせました。

===
ただ、これだとイベントの正確な行数が取れないですね。
手心を加えてみます。

with tmp_events as
(
select row_number() over(order by user_pseudo_id, event_timestamp) as row_num, t.*
from events t
)
select
  row_num
  , event_date
  , event_timestamp
  , user_pseudo_id
  , event_name
  , ev_param.key as ev_param_key
  , ev_param.value.string_value as ev_param_string_value
  , ev_param.value.int_value as ev_param_int_value
  , ev_param.value.float_value as ev_param_float_value
  , ev_param.value.double_value as ev_param_double_value
from
  tmp_events
  left join unnest(event_params) ev_param
order by
  user_pseudo_id
  , event_timestamp
  , ev_param_key
;

row_num(行番号)がついたので、row_numをカウントすれば正確なイベント数も出せる状態でバラせましたね！

決まった項目を列化してみる

↑のようなバラし方もありますが
「ga_session_id」とか使うkey項目が決まっているのなら
列化を事前にしておくような加工もできます！

SELECTにサブクエリを書くやり方です！

select
  event_date
  , event_timestamp
  , user_pseudo_id
  , event_name
  , (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
  , (select value.int_value from unnest(event_params) where key = 'engagement_time_msec') as engagement_time_msec
from
  events
order by
  user_pseudo_id
  , event_timestamp
;

かなり扱いやすい形になりましたね！

※今回は、key項目ごとにstring_valueなのかint_valueなのか選んでいますが、下記リンクのように自作関数(UDF)を使えば、もっと柔軟にできそうです…！
https://note.com/dd_techblog/n/n3e7f8c1212ef

===
応用として、ページ別の閲覧数集計なども、SELECTのサブクエリで一気にできちゃいます！

select
  (select value.string_value from unnest(event_params) where key = 'page_location') as page_location
  , count(*) as imp_count
from
  events
where
  event_name = 'page_view'
group by
  1
order by
  1
;

いかがでしたでしょうか？
複雑なデータ構造も意味と扱い方を理解すれば、思うように加工・集計できるんじゃないかなと思います！
いざ、戦ってみましょう！

参考リンク

ここからは参考になったリンクを記載します。ありがとうございました！
https://zenn.dev/mjunya1030/articles/20210510-analyze-ga4-by-bigquery

===
GA4のイベント・項目に関しては、↓の公式リファレンスが詳しいです！
https://support.google.com/analytics/answer/9322688
https://support.google.com/firebase/answer/7029846

Discussion