データサイエンス

GA4運用ノウハウ③ GA4のローデータをBigQueryで扱う

自社サービスで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がデフォルト

参考:https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions?hl=ja#timestamp_micros

2.DATETIME(関数)でUTCからJSTに変換する

参考:https://note.com/macmuko/n/n20332125126b

 

ざっとですが、以上でTableauを使った分析業務ができる状態になります。