2020年10月にGoogle アナリティクス プロパティ4（GA4）がGoogle アナリティクスの最新版となりました。このGA4ではGA17年の歴史の中で初めて、収集したデータをBigQueryにエクスポートする機能が実装されました（以前より有償版のGA360では実装されていました）。
このBigQueryへのエクスポート機能は、マーケターが以下のメリットを受けることができるという点で画期的な出来事だと思っています。
GA4→BigQueryのエクスポート機能を使用する5つのメリット
- GA4のブラウザUIで作成する「探索レポート」の数字の検証に使える
- GA4のブラウザUIに実装されているレポート以外の分析ができる
- BigQueryに実装されているBigQueryMLのような機械学習エンジンにウェブのデータを流しやすくなる
- オフラインデータ等他のデータとの結合が断然やりやすくなる
- TableauなどのBIツールで高度な分析をしやすくなる
本稿では「BigQueryを分析して上記5点のメリットを活用したい」マーケターに向けて、BigQueryに記録されている「ユーザー単位での分析に便利な3つの値」を紹介します。
BigQueryの基礎知識：テーブル構造
GA4のデータをBigQueryにエクスポートする場合、一般的には、「日別に1つのテーブル（表）」を作成します。すると、例えば、1年分のデータは、BigQuery上では365個のテーブルに蓄積されます。
※以下の画面は2020年11月1日から2021年1月31日までのデータが提供されているDEMOアカウントのものです。2021年1月27日から1月31日のテーブルをドロップダウンにより選択できるようになっているのが読み取れると思います。
今回想定するユーザー分析
1年前からGA4でトラッキングを行っているECサイトで、今日、トランザクションを発生させてくれたとあるユーザーがいたとします。そしてそのユーザーについて、
- 初回訪問時の日時
- 初回訪問時のメディア
- 累積購入金額(LTV)
を知りたくなったとします。
真っ先に思いつく方法
そこで真っ先に考える方法は、365個のテーブルを対象に、該当するユーザーを識別するID(user_pseudo_id)でフィルタを掛けて、上記ユーザーのサイト利用履歴を確認することです。
BigQueryの『便利な3つの値』を使った方法
確かに上記の方法でも確認できますが、BigQueryに記録されている以下の3つの値を利用すればもっと楽に、低コストで知ることができます。
BigQueryの『便利な3つの値』
BigQueryのテーブルには以下の「ユーザーの履歴」を保存しているカラムがあります。
- user_first_touch_timestamp：
ユーザーの初回訪問時のタイムスタンプ（GA4のブラウザUI上のディメンション名は「初回訪問日」 - trafficsouce.medium：
ユーザーの初回訪問時のメディア（GA4のブラウザUI上のディメンション名は「ユーザーの最初のメディア」 - user_ltv.revenue：
ユーザーの累積購入金額（GA4のブラウザUI上の指標名は「LTV:合計」
これらのカラムを利用し、今日のテーブルだけを対象にSQL文を発行すれば、上記の知りたいことを確認することができます。
分析の例
例えば、DEMOアカウント（2020年11月1日～2021年1月31日までの3ヶ月のデータが92の日別テーブルに保存されています）に記録されている以下のユーザーは、2020年11月25日に初回訪問をして、12月13日までに4セッション訪問しています。
この結果を得るために、92日分の日別データを対象に以下のSQLを発行しました（真っ先に思いつく方法）。
一方、user_first_touch_timestampを利用すると、このユーザーの初回訪問日時は、2020年12月13日のテーブルだけを対象にSQL文を発行すれば、取得できます（BigQueryを活用した方法）。
同様に、「初回のユーザーのメディア」は、traffic_source.mediumカラムを利用すれば、ユーザーの「LTV:合計」は、user_ltv.revenueカラムを利用すれば過去の日付のテーブルを対象としてSQL文を発行する必要なしに取得できます。
複雑なSQL文を書く必要もなくなり、課金対象となるスキャン量も大幅に減らすことができますので、知っておいて損はない知識だと思います。
その他の分析例
この知識を使うと、以下の問いに対しても、比較的単純なSQL文を発行することで簡単に答えを得ることができます。
- 今日訪問したユーザーのうち、今日はじめてサイトをユーザーしたユーザーは何人いるのだろうか？リピーターは何日ぶりにサイトを訪問してくれたのだろうか？
- また、何日ぶりに訪問したユーザーのコンバージョン率が高いのだろうか？
おまけ
traffic_source_mediumカラムにユーザーの初回訪問時のメディアが記録されているとすると、セッションごとに切り替わるメディア（GA4のブラウザUI上のディメンション名は「セッションメディア」）はどこに記録されているのだろうか？と疑問に思う読者の方もいらっしゃるかもしれません。
セッションメディアは、event_paramsカラムの、mediumというキーに紐付いて記録されています。最初のユーザーのメディアが記録されているtraffic_source.mediumと混同しないようにしてください。
以上
