自社サービスでGA4を運用してみている中でのメモになります。
今回は、GA4のローデータをBigQueryで扱う際にやったことについてです。
ゴールはTableauで、GA4のデータを扱える状態を目指しました。
導入している弊社サービスはこちらになります。
AI・データサイエンスが学べる学習サイト
https://www.picaca.jp/
1.生成されるカラムを確認する
まずは、生成されるカラムの中身を確認・理解します。
カラムリストは下記をご参照ください。
参照:[GA4] BigQuery Export スキーマ – アナリティクス ヘルプ
補足情報
- user_id::ユーザIDを指す
- user_pseudo_id: ブラウザIDを指す(アプリの場合Instance ID、ウェブの場合cookie),GA4の探索やLookerの場合、”アプリ インスタンス ID”にあたる
- ga_session_id:セッションIDを指す
- stream_id:ストリームIDを指す(webかアプリでストリームidは異なる)
参照:「GA4」の「データ ストリーム」と「ユニバーサル アナリティクス」の「ビュー」の違い|and_a|note
2.エクスポートできるデータ量を理解する
次に無料でエクスポートできるデータ量を理解します。BigQueryにエクスポートできるデータ量は無料版だと、100万イベント/日になります。それ以上のデータ量である場合、有料版を契約する必要があります。
3.ローデータの構造を理解し、データ整形する
GA4のローデータは少しややこしく、RECORDというデータ型が存在します。テーブルinテーブルになっています(1つのセルにテーブルが入っているイメージ)。
ですので、配列となっているところ(入れ子になっているところ)を展開する必要があります。※unnestすると言います。
unnestして新しいテーブルを作成することを目指します。
これを↓、
こうします↓。
出典:【GA4】GA4のローデータをunnestしてさらにデータ整形する
unnestのやり方に関しては、Google検索をすると参考文献がたくさん出てくるため、ここでは詳しく取り扱いません。
参考となった文献のリンクを貼っておきますので、ご参照ください。
BigQuery入門者がはじめに躓くだろうネスト型との付き合い方 – Qiita
GA4のBigQuery SQLを書くときに便利なライブラリ「BigQuery Utils」 | 株式会社プリンシプル
4.event_timestampの整形する
最後に行ったことが、timestamp型のデータを整形することになります。
カラム名:event_timestampはunixtimeでデータが格納されています。
ですのでデータ活用できるようJSTへ変換する処理を行いました。
前提情報としては、GA4のevent_timestampは、マイクロ秒単位(16桁)で整数となっています。整形手順は次の通りです。
1.TIMESTAMP_MICROS(関数)でUNIXTIMEからTIMESTAMP(UTC)に変換
※BQのTIMESTAMPはUTCがデフォルト
2.DATETIME(関数)でUTCからJSTに変換する
参考:https://note.com/macmuko/n/n20332125126b
ざっとですが、以上でTableauを使った分析業務ができる状態になります。