GA4をBigQueryで扱う上で困ったこと

GA4をBigQueryで扱う上で困ったことを列挙していこうと思います。

1.REPEATEDなカラムの扱い

このカラムの扱いに関しては、こちらのURL(https://www.technolife.co.jp/big-query%e3%81%aeunnest%e3%81%ab%e3%81%a4%e3%81%84%e3%81%a6/)上に記載しました。

2.itemsカラムの扱い

上のURLのところにも書いたとおり、GA4のデータには、event_paramsとitemsのカラムがREPEATEDなカラムと存在している。UNNESTでitemsを分解すると、UNNESTの使用上、すべてnullのものは、結果から除外されてしまうため、itemに関連するイベント(ex. add_to_cart, purchase, view_item)以外のイベントであるpage_viewやsession_startなどのイベントは、結果に表示されなくなってしまう。実際に下のクエリをたたいてみたところ、

select 
  * 
from
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
  unnest(items)
where 
  event_name = 'page_view'

と出てしまいます。そのため、テーブルを作成する場合は、テーブルを分けることが必要になります。

3.PV数、UU数、新規ユーザー数、セッション数、新規購入者数の出し方

• PV数
  • event_nameがpage_viewの回数
• UU数
  • user_pseudo_idの個数(重複なし)
  • user_idがあるが、これはnullの場合も多く、あまり使えない
• 新規ユーザー数
  • event_nameがsession_startのときのga_session_numberが1のとき、新規ユーザー、2以上のときリピータ
  • ga_session_numberはint型なので、page_locationなどとは違い、valueのint_valueの方にあるので、注意
  • event_nameでfirst_visitがあるが、そちらよりもga_session_numberの方が正確らしい

• セッション数
  • session_idの個数(重複なし)
• 新規購入者数
  • 今までのものとは違い、GA4が新規購入者数を表すようなカラムは作成していないので、自力でクエリを作成し、出す
  • event_typeがpurchaseだけのテーブル(クエリ結果でもok)を作り、そこで、user_pseudoごとにtimestampの最小値をだせば、ユーザーごとの一番最初に商品を買った時刻(マイクロ秒)がわかる。
  • その時刻を利用し、event_nameがpurchaseのときに新規の場合は、1、リピータの場合は0とラベル付けしたカラムの作成
  • ラベル付けしたカラムの合計値が新規購入者数になる。