みなさんご承知の通り、Google アナリティクスの最新バージョンであるGA4では無料でBigQueryにデータをエクスポートできます。（BigQueryの利用料金は、無料枠の範囲を超えれば発生します。）そのデータは、日毎に「テーブル」と呼ばれる「表」の形で出力されます。以下のような姿かたちをしています。
一見、「生データ」に見えるBigQuery上のGA4データ
一見、「生データ」、つまり、ユーザーがサイトを訪問して、ページビューや、スクロールや、サイト内検索をした、その行動を「ヒット」として収集しているように見えます。そのため、GA4がBigQueryにエクスポートした上記のデータを「生データ」と呼ぶことが（一般的に）あるかと思いますし、私も説明が面倒だったり、そのとき話しているテーマがその部分の正確性が必要ないときは「生データ」と呼ぶこともあります。
が、実は、このデータは「ユーザーの行動を加工せず、ヒットとして収集している生データ」ではありません。そう思い込んでしまうともったいないところもあるので、もし、そう思っていた方は少しだけ認識を変えていただくと良いのかな？と思います。
BigQuery上のGA4データの「生」ではない部分
GA4がエクスポートしたデータが「生」ではないのは、加工済のデータが入っているからです。以下の1～6のカラム（表における「列」のことです）には、実はユーザースコープでの最初の値や、累計値といった、「ヒットと同時刻に発生したのではない」データが入っています。7番は、最初から値が入っているものではなく、ユーザープロパティを記録すれば値が入ります。必ず値が入る訳でもないので、カッコに入れています。
※ストリーミングでのエクスポートをオンにしたときの、日中表（テーブル名：events_intraday_yyyyMMdd）ではなく、1日以上経過して確定したテーブル（テーブル名：events_yyyyMMdd）を前提としています。また、ウェブサイトを対象としたデータストリームを前提としています。
- user_first_touch_timestamp
- traffic_source.name
- traffic_source.source
- traffic_source.medium
- user_ltv.revenue
- user_ltv.currency
- (user_properties.value.set_timestamp_micros)
3つのグループに分けて説明します。
ユーザーの「最初の値」が記録されているカラム
user_first_touch_timestampは、ユーザーが初回訪問したUTC(協定世界時）がUNIX時のマイクロ秒で記録されています。UTCというのは日本の時刻からは9時間前の時刻を示します。UNIX時というのは1970年1月1日0時分0秒を起点とする経過時間のことです。○時○分○秒とは違い必ず整数で表現されるので、あるイベントから何秒後に別のイベントが起きたかを調べる場合などは、日付関数ではなく、普通の四則演算（この場合は引き算）で済むので重宝する面があります。
マイクロ秒は100万分の1秒です。手元で手っ取り早く○時○分○秒かを知りたいときは、「UNIX時 変換」で検索するといくつか見つけることのできる変換サイトで変換することができます。私は、カシオが提供している高精度計算サイトを利用しています。その際の注意点は以下の2点です。
- 末尾6桁は削除してマイクロ秒を秒にしてから変換すること
- UTCとの時間差を「+9」と設定すること
traffic_souce.name、traffic_souce_source、traffic_souce.mediumは、それぞれ、GA4のレポートで利用される「ユーザーの最初のキャンペーン」、「ユーザーの最初の参照元」、「ユーザーの最初のメディア」に相当する、ユーザーがサイトへの初回訪問時に利用した方法が記録されています。気をつけることは、traffic_souce.nameには、utm_campaignに値がなかった場合、カッコ書きでメディアが記録されることです。
ユーザーの「累計値」が記録されているカラム
user_ltv.revenueにはユーザースコープの累計の収益、つまり、LTV(LifeTime Value)が記録されています。以下はデモアカウント（プライバシー保護のため？かなりデータが改変されていてキレイに出ているデータは少ないですが）のあるユーザーのデータです。purchase_revenuがトランザクションに紐づく収益、ltv_revenueがライフタイムバリューです。このユーザーの2021年1月23日時点のlTVが、US$278.0ということがわかります。（米ドルの値であることはcurrencyがUSDであることでわかります。）
一方、このユーザーの1月中の2回目のトランザクション発生日である、2021年1月23日だけのテーブルを確認すると、そこにもltv_revenueがUS$278.0として記録されています。つまり、過去のpurchase_revenueの累計値が日別のテーブルにも記録されているのです。
ユーザーの「履歴」が記録されているカラム
user_properties.value.set_timestamp_microsという列には、ユーザープロパティが最後に更新された時刻が、UNIX時のマイクロ秒単位で記録されています。このカラムはユーザープロパティに値を記録しないとnullのままです。そのため、値が入っていることを見る人は少ないかもしれませんが、ユーザースコープの「履歴」が記録されていると考えることができます。
例えば、以下のユーザーは、初めて、member_idという名前のユーザープロパティが記録されたヒット時刻（event_timestamp)である、1661812876598378(UNIX時のマイクロ秒）が、user_properties.value.set_timestamp_microsにも記録されていることがわかります。
メンバーIDがユーザーごとに変わることは基本的にはないはずなので、この場合のuser_properties.value.set_timestamp_microsは、「初めてメンバーIDが付与された状態になった時刻」と解釈できます。
一方、例えばユーザーステータスのように、ユーザーによって値が変わりうる属性については、user_properties.value.set_timestamp_microsは、「最終更新時刻」を示します。以下の画像はuser_statusとしてbronzeが付与された際、そしてbronzeからsilverに変わった際に正しくuser_properties.value.set_timestamp_microsが記録されている状態を示しています。
BigQuery上のGA4データが「生データ」でないことによるメリット
これまで紹介してきた仕様により、非常にやりやすくなる分析があります。例えば、「ユーザーの最初のメディア別のLTV」の分析は、どのようなメディアからユーザーの初回訪問を獲得すればLTVが上がりやすいユーザーを獲得できるのか？を可視化し、合理的な「初回訪問ユーザー獲得戦略」立案の助けとなると思います。
一方その分析におけるLTVは、厳密には「初回訪問から○日以内の」という条件を付与するべきです。例えば、ある3ヶ月間で「ユーザーの最初のメディア別のLTV」を分析するとして、期間中の最初の月にorganicから初回訪問したユーザーが多く、期間中の最後の月にppcから初回訪問したユーザーが多かったとします。すると、organicから初回訪問したユーザーはLTVを増加させ得る期間が長く、ppcから初回訪問したれらユーザーは短い。ということになります。以下の単純化しした表では、3ヶ月間、各月1日に12人のユーザーが初回訪問をしたときの、ユーザーの最初のメディア別の「LTVを上げうる総月数」を比較したものです。
現実にはこれほど激しくユーザーの最初メディアの構成比が変動するとは思えません。しかし、広告を始めた、停止した、増額した、減額したなどは日常的に起きているはずです。したがって、厳密性を求めるためには「初回訪問日から○○日以内のLTV」を分析対象にする方が良いでしょう。
デモアカウントの3ヶ月（厳密には92日分）のデータで初回訪問日から30日以内の、ユーザーの最初のメディア別のLTVは以下のSQL分で取得できます。8行目で初回訪問時刻を、10行目でLTVを利用しています。そのおかげで20行足らずのSQL分でやりたい分析ができました。
select user_first_medium
, count(distinct user_pseudo_id) as users
, sum(ltv_revenue) as user_ltv
, sum(ltv_revenue)/ count(distinct user_pseudo_id) as average_ltv_by_users
from(
select user_pseudo_id
, max(traffic_source.medium) as user_first_medium
, max(cast(datetime_trunc(timestamp_micros(user_first_touch_timestamp),day) as date)) as first_touch_date
, max(parse_date('%Y%m%d', event_date)) as event_date
, max(user_ltv.revenue) as ltv_revenue
from `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
group by user_pseudo_id
)
where date_diff(event_date, first_touch_date, day) <=30 --初回訪問日から30日以内に絞り込み
group by user_first_medium
order by 2 desc
まとめ
思いの外長い記事になってしまいました。^^;
この記事で解説したBigQuery上のGA4データの「生でない」部分についての知識を活用して効率の良い分析をしていただければ幸いです。
宣伝
という訳で、SQLを書籍で勉強したい方はこちらをどうぞ
