[GA4+BigQuery] 連携されたデータを使いやすく整理する

そのままだと使いづらいのでデータを整理する

GA4からBiqQueryに連携されているデータをそのままにしておくと、ちょっとしたデータを出すにも毎回クエリが長く複雑になる。SQLに慣れている人でも結構わずらわしいと感じるだろうが、慣れていない人には難しすぎる。まったく整理していない状態でイベント間の日付の差分を出そうとして何層もの入れ子のクエリを書くなんて事故まっしぐらだ。

なので抽出を迅速かつ正確に行えるよう、事前に整理をしておこう。

GA4+BigQuryのデータを整理する

イベントごとにテーブルを作る

まずはイベントごとにテーブルを分けることを強くお勧めする。イベントごとに格納されるイベントパラメータが違うので、使う度に何が入っているのか、データ型は何かをいちいち調べてクエリを書くのは大変だからだ。

イベントごとにばらすクエリでは具体的なクエリを解説している。

例外として別のイベントを組み合わせることが前提になるイベントのテーブル（例えばpage_viewとscrollやclickとlink_url）は作らずにJOINしてしまうのもありだ。

配列は値ごとにフィールドを作る

traffic.sourceならtraffic_sourceなど。そのままでも使えるが他と微妙に違うと混乱する。大した労力はかからないので行っておく方がいい。

イベントパラメータごとにフィールドを作る

イベントパラメータはデータ型によって別の場所に入っているため正しくないデータ型のフィールドを指定すると値が取れない。であれば先に正しい値を入れたフィールドを作っておく方が良い。フィールド名はevent_params.keyの値を使えばいいだろう。

例えばpage_titleは文字列型なので以下のようになる。

(select value.string_value from unnest(event_params) p where p.key = 'page_title' from ) as page_title

イベントパラメータの値のデータ型に注意する

booleanでも文字列で入っているのでCASTする

event_timestampを使いやすい形にする

event_dateは日付だけで時間が入っていない。時分秒を知りたければevent_timestampを使う必要がある。UNIXTIMEでそのままだとさっぱりわからないのでdatetimeかtimestampに変換する。

event_dateは使わない方がいいかもしれない

日付だけならevent_dateがあるが、タイムゾーンは設定に依存する。そのため設定を間違えていると意図していない結果になる可能性がある。

event_timestampならば変換する際にタイムゾーンに日本時間の指定があるかはクエリを見ればはっきりする。なので日時はevent_timestampを使うようにしてevent_dateは使わないとしてもいいかもしれない。

ただし毎日連携されるデータは設定されているタイムーゾンの日付（つまりevent_time）の0時から24時までなので設定をしなくてもいいとはいかない。

debug_modeでの閲覧を除外する

[GA4+BigQuery] debug_modeでの閲覧も計測されており、除外しないとカウントされる。

大規模なサイトではほとんど影響なくても立ち上げ直後でアクセスが少ないと誤解を生む。

分割テーブルの設定

データ量が大きくクエリにかかるコストや時間が気になるなら分割テーブルを検討しよう。そうでなければあまり気にしなくてもいいだろう。

データを整理する時に一般的に注意すること

ここからはGA4+BigQuryに限らずデータを整理する際にはいつでも気を付ける必要があることなので簡単に紹介する。

全フィールドを使わない

全てのイベントに対して紐づくフィールドやイベントパラメータのフィールドを作るのは大変だ。たとえ作ってもそのうち使われるのは一部でしかない。なので使うことがはっきりしているか、使われることがすぐ思い浮かぶデータを優先する。

フラグの形式を統一する

どれが正しいかは問題にはならなくてもあちこちで形式が違うと混乱するので統一する。例えばデフォルトでは以下のように違いがある

• 文字列型でtrue / falseで入っている
• 数値型で1 / nullで入っている

個人的には数値型で1/0がいいと思っているが、エンジニアには不評な模様。

時間の形式を揃える

整理するときにどれかに揃えること。テーブルによって違うとクエリの使いまわしもできないので面倒。

タイムゾーンを揃える

全てのテーブルのタイムゾーンを揃えておくこと。揃っていることが保証されていないと使う度に確かめなければならない。

まずは必要なデータに取り組もう

データは全てBiqQueryに連携されており後でいつでも追加できる。なので最初に作るのは必要最低限のデータで良い。使わないデータには時間を割かずに次の抽出や分析に進んでいこう。

GA4+BigQueryのまとめ