BigQueryにエクスポートしたGA4のデータの概要を把握する

「GA4のデータを異なるGoogleアカウントからBigQueryにエクスポートする」の操作により、BigQueryにGA4のデータがエクスポートされるようになった。

この記事では、エクスポートされたデータの作りやGAのコンソール上での見え方との比較を行って、概要の把握を進める。

BigQueryでのテーブル構成の概要

BigQueryへのエクスポートには、数分前のデータが都度入ってくるストリーミングエクスポートと、1日1回エクスポートされる2種類があり、今回は後者のみとしている。

この場合、以下の形式でデータが作成される。
・データセット：analytics_<property_id>
・テーブル：日毎のテーブル events_YYYYMMDD

テーブルの中身をpreviewで確認すると、event単位でネストされていることがわかる。例えば下記画像の例だと、session_startとpage_viewのイベントそれぞれに対して、page_title等のパラメータとその値が格納されている。

ユーザーエクスプローラーの結果と見比べて概要を把握する

GA4のコンソール上での表示と、BigQueryのデータを見比べて同じであることを確認してみる。user_pseudo_id を指定して比較する。

BigQuery側

SELECT
  event_date,
  event_timestamp,
  DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Tokyo") as event_datetime,
  event_name
FROM 
  analytics_<property_id>.events_20231010
WHERE 
  user_pseudo_id = <user_pseudo_id>

GAコンソール側
[探索] → [テンプレートギャラリー] → [ユーザーエクスプローラ] より、user_pseudo_idを指定して表示する。

「イベント4件」となっていて、user_engagementはリストに表示されていないものの、右上の「上位のイベント」として表示はされているので、BigQuery側と項目としては同じであることが確認できる。また、日時も合致していることが確認できる。

複数日にまたがってクエリを書くには

events_YYYYMMDD という名称で日毎にテーブルが作成されるため、複数日にわたって集計するクエリを書きたい場合は、複数のテーブルを結合する必要がある。

BigQueryのワイルドカードテーブルを使用すると、テーブル名をワイルドカードで指定してクエリを書くことができる。
　Query multiple tables using a wildcard table | BigQuery | Google Cloud

例えば、2023/10/01〜2023/10/20の20日分を集計したい場合は以下のように書ける。

SELECT 
	…
FROM
  analytics_<property_id>.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN "20231001" AND "20231020"

大まかだが、以上でBigQueryへエクスポートされたデータの作りを確認したので、次に具体的にクエリを書きながら理解を深めていく。
→ BigQueryにエクスポートしたGA4のデータをコンソールと比較する