はじめに
ネット上ではGA4のテーブルを集計するための様々なSQLが紹介されており、参考になる。ただし、記述内容は「コードの簡易さを求めたもの」あれば、「GA4のUI上で表示されるデータに値を極力近づけるもの」、「独自のルールで各種指標を定義したもの」など、SQLを書いた立場によって大きく異なっている。そのため、自分がどのような定義で各種データを集計したいか考える際には、GA4の集計内容とSQLの理解が必須となってくる。この記事では特に「GA4のUI上で表示されるデータに値を極力近づけること」を目的として、参考記事の紹介ならびにその要約と補足を行う。
記事1：ユーザー指標
<参考ページ>
GA4の各ユーザー指標をBigQueryで調査する
https://painted-bakery-ed4.notion.site/GA4-BigQuery-feb1c925ecdc4acb919fca700089cfe3
最初は『GA4の各ユーザー指標をBigQueryで調査する』を紹介する。主要なデータの1つであるユーザー単位の指標を紹介している。具体的には「総ユーザー数」「アクティブユーザー数」「新規ユーザー数」「リピーター数」の集計のためのSQLが紹介されている。
また、エンゲージメントに関するGA4の集計の仕様やテーブルへの反映内容が記載されている。記事もあるように以前はアクティブユーザーの条件の1つとして、エンゲージメントセッションを考える必要があったが、「is_active_user」フラグがついてからはそのフラグに沿って集計できるようになったため、集計観点だけでいうと、エンゲージメントは分けて考えても良い(ただし、アクティブユーザーの厳密な定義を理解するという意味ではエンゲージメントの仕様を知ることは大切である)。
少しだけ記事を補足すると、ここでの総ユーザー数とは集計方法を「デバイスベース」＝Cookie単位での識別を行った場合のユーザー数の集計である。記事で紹介されている「user_pseudo_id」はGA4から付与されるCookieの値である。
※pseudo(擬似)という名前も(会員情報などの)厳密なユーザーの情報ではなく、あくまで機械的にブラウザに対して振られたCookieの識別子に過ぎないことを反映しているといえる。
記事の集計の結論をまとめると、「総ユーザー数」はユニークな「user_pseudo_id」の数、「アクティブユーザー数」は「is_active_user」がTrueなユーザーの数、「新規ユーザー数」はfirst_visitイベントの数である。「リピーター数」はかなり複雑なので、実際の記事を確認のこと。
記事2：セッション(＋新規ユーザー)
<参考ページ>
Why you should avoid using the session_start and first_visit events in GA4
https://tanelytics.com/ga4-session_start-and-first_visit-events/
2つ目の記事は『Why you should avoid using the session_start and first_visit events in GA4』(英語)である。巷のSQLの記事は「user_pseudo_id＋session_id」を使った集計と「session_start」イベントを使った集計の2パターンが多いが、何故後者だと良くないのかを説明した記事である。また記事の後半では、「first_visit」の集計内容を踏まえて、(記事1で軽く触れた)新規ユーザーの定義はGA4のレポートでは「first_visit」のカウントをしているが、果たしてその定義でよいのかという問題提起をしている。
始めに補足を入れると、セッションのカウントとしてはuser_pseudo_idとsession_idを紐づけたもののユニークな数を集計することが望ましい。user_pseudo_idはCookieの値、session_idはセッション開始時のタイムスタンプである。session_idがタイムスタンプであるため、複数のユーザーが同じタイミングでセッションを開始した場合、idが重複するため、user_pseudo_idと紐づけてみる必要がある。更にセッションを細かく見る際には、上記2つを複合主キーとして集計することになる。
ここからが記事の内容になるが、session_startがなぜまずいかというと、一回のセッションでsession_startが抜け落ちたり、複数回「session_start」が計測されることがあるからである。記事にも紹介されているsimoahavaのXのポストによると、複数タブで複数ページを開くとsession_startが複数回計測されるらしい。
続いて新規ユーザーについてみると、Cookieの有り無しで判断しているため、同じドメインに対して、複数GAを導入していると問題が生じる。具体的にはサイト内の一部のみ集計しているGA4プロパティに関して、集計対象外のページに訪問したユーザーに対してもCookieデータが付与されるため、その後の計測対象のページに初訪問しても、初回セッションと認識されない。この記事では「ga_session_number」が1か1より大きいかで新規とリピーターを定義することが提案されている。
記事3：ページの前後
<参考ページ>
SQLでGA4のWebデータを自在に解析する
https://atmarkit.itmedia.co.jp/ait/articles/2306/30/news001.html
こちらに関しては@ITの会員向け記事(無料会員も閲覧可)なので、簡単に紹介だけすると、閲覧順序の前後関係を見るためのクエリが解説されている。GA4からはリファラーを使った前後関係の把握が「探索」で可能になっているが、リファラー情報は取得できる条件がある。(UAのナビゲーションサマリーライクな)純粋な時間軸での前後であれば、この記事で紹介されている方法が有効である。
記事4：流入経路
<参考ページ>
Query the GA4 Session Last Non-direct Traffic Source in BigQuery
https://tanelytics.com/ga4-bigquery-session-traffic_source/
4つ目の記事は『Query the GA4 Session Last Non-direct Traffic Source in BigQuery』(英語)であり、2つ目の記事と同じ著書の記事である。(UAと同様に)GA4はユーザーがDirect(直接流入)で訪問した際、過去の訪問を順番に振り返り、最も直近の非Directの経路をレポート上で集計する仕様となっている。
公式の『[GA4] トラフィック ソースのディメンションのスコープ』の記事の最下部でも「direct / none」が「partner / affiliate」で修正されていることが確認できる。
※紹介されている例はセッションの定義の問題も含んでおり、やや複雑化してしまっているため、注意が必要。
<参考ページ>
[GA4] トラフィック ソースのディメンションのスコープ
https://support.google.com/analytics/answer/11080067?hl=ja
この記事ではそのDirect流入時の経路の修正をSQLで実現する方法が記載されている。この記事では直近30日分振り返り、流入情報がない場合のみDirectとする記述となっている。このルックバック期間に関しては自分の環境だと90日程度がGA4のデータと近かった。先延ばしにしていたが、いいタイミングなので、検証を行い、追って記事化する。
終わりに
今回はSQLを使ったGA4データのクエリを解説している記事を4つ紹介した。導入文でも記載したようにクエリの記述は多様なので、自分がBQで集計したデータとGA4で見られるデータに乖離が生じた場合、ここに戻ってきて欲しい。また、SQLの基礎に関しては「SQLの入門(書籍の紹介)とGA4のクエリの特徴」の記事で紹介している。
<関連ページ>
SQLの入門(書籍の紹介)とGA4のクエリの特徴
https://cellardoor.hatenablog.jp/entry/day4-sql-introduction/
