はじめに
仕事で、BigQueryでGA4のイベント集計をする必要が出てきたので、BigQueryの使い方の基本を備忘録として記載。
今回は、ユーザーの新規会員登録（イベント名：signup_complete）の流入経路を分析する例を記載する。
前提：GA4とBigQueryをリンクする
GA4のデータをBigQuery上で分析するには、GCPでBigQueryプロジェクトを作成したのちに、BigQuery Exportの設定を行なっておく必要がある。
- GA4の管理セクションに移動し、プロパティ列で「BigQueryのリンク」を選択。
- 「リンクを作成」をクリックし、データをエクスポートしたいBigQueryプロジェクトを選択する。
- データセットのエクスポート頻度（毎日、毎時など）とエクスポートするデータの範囲を設定する。
BigQueryのテーブルとフィールド
テーブル
BigQueryプロジェクトへエクスポートされたデータは、analytics_{プロパティID} という名前のデータセットで保存される。
エクスポートの頻度を毎日に設定している場合、このデータセット内にevents_YYYYMMDD という日付毎のテーブルが毎日作成される。
イベントテーブルのフィールド
https://support.google.com/analytics/answer/7029846
いくつものフィールド名があるが、よく使うのは下記4つ。
- event_date：イベントが発生した日
- event_name：イベント名（gtagなどで設定する）
- event_params：イベントに関連する追加情報。この中には「どのページを見たか」や「どの商品を買ったか」といった詳細が含まれる。これは複数の値を持つことができるので、いくつかの異なる情報が入っていることがある（後述）。
- user_pseudo_id：ユーザーのユニークな識別子（後述）
GA4でのイベント設定と、event_name, event_paramsの関係
GA4でイベントを記録する際には、gtag('event', ...) 関数を使う。この関数には主に、イベント名とイベントパラメータの2つのパートがある。
gtagでのGA4イベント記録に関しては下記公式サイトで記載されている。
https://developers.google.com/analytics/devguides/collection/gtagjs/events?hl=ja#send_events
上記公式サイトに記載されている下記例をもとに考える。
gtag('event', 'screen_view', {
'app_name': 'myAppName',
'screen_name': 'Home'
}); event_name は 'screen_view' （gtagの第二引数）。これはGA4に記録されるイベントの種類を示しており、ユーザーが画面を閲覧したことを表している。
event_params は、イベントに関する追加情報を含むオブジェクト。この例では2つのパラメータがある。
'app_name': アプリケーションの名前を示すパラメータ（key）。この場合、その値（value）は'myAppName'。'screen_name': ユーザーが閲覧している画面の名前を示すパラメータ（key）。この場合、その値は'Home'（value）。
GA4のBigQueryエクスポートでは、これらのイベントパラメータは event_params フィールドに格納され、それぞれのパラメータは key-value ペアの形式で記録される。event_params フィールドは RECORD 型であり、複数の key-value ペアを含むことができる REPEATED フィールド。
ちなみに、GA4のデータをBigqueryにExportしたときにeventテーブルの中身は下記のようになる。
GA4のデータをBigQueryでクエリする場合、event_name に基づいて特定のイベントをフィルタリングし、event_params 内の個々のパラメータを抽出して分析することができる。例えば、'screen_view' イベントに関連する 'app_name' や 'screen_name' の値を取得するために、次のようなクエリを書くことができる。
このクエリは、'screen_view' イベントのために設定された 'app_name' と 'screen_name' の値を取得する。
SELECT
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'app_name') AS app_name,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'screen_name') AS screen_name
FROM
`your_dataset.events_*`
WHERE
event_name = 'screen_view'event_paramsのUNNEST演算子による展開と分析に関しては、下記に詳細記載。
今回、会員登録に至る流入元分析を行うが、ユーザーがどのキャンペーンや広告から来たかを追跡するために、URLのクエリパラメータやウェブサイトが提供するその他の追跡情報をevent_paramsとしてGAに送信し、キャンペーンのパフォーマンスを分析するなどが考えられる。
キャンペーンや広告からウェブサイトにアクセスする際に、URLにクエリパラメータを追加する。
https://www.example.com/signup?campaign=Summer_Sale&ad_id=ad_12345このURLから会員登録ページにアクセスしたユーザーがいた場合、ウェブサイトのコードはこのクエリパラメータを読み取り、gtag関数を呼び出す際にこれらの値をイベントパラメータとして含める。
// URLからクエリパラメータを読み取る
const urlParams = new URLSearchParams(window.location.search);
const campaignName = urlParams.get('campaign');
const adId = urlParams.get('ad_id');
// 会員登録が成功したときにGAにイベントを送信
gtag('event', 'signup_complete', {
'campaign_name': campaignName, // キャンペーン名
'ad_id': adId // 広告ID
});UAでのイベントトラッキングは、GA4ではevent_paramsに一元化された
まだGA4が始めってから、それほど期間が経っていないので、プロダクトのコードによってはGAイベント送信で、UAの部分が残っているのもあると思う。UAとGAでイベントトラッキングの方法も変わったため混在していると、頭も混乱してくるので、ここでまとめておく。
UAでは、イベントトラッキングに event category、event action、event label、event value という4つのパラメータを使用していたが、GA4では、これらが event_params に一元化された。
UAでは、イベントを追跡する際には、それぞれのイベントにカテゴリーとアクションを指定する必要があり、オプションでラベルや値も追加できた。しかし、GA4では、イベントモデルがより柔軟になり、任意のパラメータをイベントに追加できるようになった。これにより、より詳細なコンテキストをイベントに関連付けることが可能になった
例えば、GA4では以下のようにイベントを送信できる。
gtag('event', 'video_play', {
'video_title': 'Funny Cats Compilation',
'video_duration': '120',
'video_author': 'CatVids'
});ここでは、video_play がイベント名になり、video_title、video_duration、video_author が event_params として送信されている。これらは UA で言う event label や event category に相当する情報だが、GA4ではより多くの情報を任意のパラメータ名で送信できるようになっている。
UAでのgtag設定でevent categoryやevent labelを明記してイベントを送信していた場合、GA4ではこれらは自動的にevent_paramsとして扱われる。エラーにはならない。
例えば、以下のようなUAのイベント送信コードがあった場合：
gtag('event', 'click', {
'event_category': 'button',
'event_label': 'nav buttons',
'value': 1
});
このコードはGA4では問題なく動作し、event_categoryとevent_labelはイベントパラメータとして記録される。GA4のBigQueryエクスポートでは、これらのパラメータはevent_paramsフィールド内のレコードとして格納される。
公式ドキュメントはこちら
user_pseudo_id（ユーザー擬似ID）とga_session_idの違い
user_pseudo_idはユーザーがウェブサイトを訪れるたびに同じ番号で、ユーザーを長期にわたって識別するのに使う。本当の名前や個人情報を使わずに、ウェブサイトがユーザーが誰かを識別するための番号。
例えば、ユーザーが初めて訪問してから会員登録をするまでの流れや、リピート購入のパターンなどを分析できる。
ga_session_idは、ユーザーがウェブサイトに訪れた個別のセッションを識別するために使われる。ga_session_idは一回の訪問に対して一意のIDが割り当てられる。ユーザー自身の識別子ではない。
セッションごとにどのページが閲覧されたか、どの製品がカートに追加されたかなど、ユーザーのセッション中の行動を詳細に追跡できる。
user_pseudo_idでユーザー全体の傾向を把握し、ga_session_idで短期間の行動やセッション固有の問題を特定することができる。
GA4にuser-idを送信→サービスにログインしたユーザーの動向も分析可能に
GA4では、ユーザーがログインしているかどうかに関わらず、すべての訪問者に対して「user-pseudoid」が自動的に割り当てられる。一方で、サービスにログインしたユーザーのみのサービス内での動向を集計したい場合もある。
その場合には、サービスにログインしているユーザーに割り当てられたユニークなIDであるuser-idをGA4に送信設定するようサービス内に実装する必要がある。
export function setUserId(userId: number | null) {
if (isGtagReady()) {
window.gtag('set', {
user_id: userId,
});
}
}実装上の注意点：ユーザーIDは、個人を特定できる情報（PII）を含まないように生成する必要がある。
GA4でuser-idを設定し、そのデータをBigQueryにエクスポートすると、エクスポートされたデータセット内のeventsテーブルには、各イベントに関連付けられたユーザー情報が格納される。
+-----------------+---------+------------+---------------------+-----------------+
| user_pseudo_id | user_id | event_name | event_timestamp | event_params |
+-----------------+---------+------------+---------------------+-----------------+
| abc123 | 1001 | page_view | 2023-03-15 12:00:00 | {page_location} |
| def456 | 1002 | click | 2023-03-15 12:05:00 | {button_name} |
| abc123 | 1001 | purchase | 2023-03-15 12:10:00 | {transaction_id}|
| ghi789 | 1003 | page_view | 2023-03-15 12:15:00 | {page_location} |
| abc123 | 1001 | scroll | 2023-03-15 12:20:00 | {scroll_depth} |
+-----------------+---------+------------+---------------------+-----------------+特定のuser_id（例えば1001）のユーザー動向を集計するSQLクエリのダミー例
user_idが1001であるユーザーのイベントを集計し、各イベントの発生回数をカウントしている。
SELECT
user_id,
event_name,
COUNT(event_name) as event_count
FROM
`your_project.your_dataset.events`
WHERE
user_id = '1001'
GROUP BY
user_id,
event_name
ORDER BY
event_count DESC;
さらに、GA4のexportイベントと、サービスのデータベースに格納されているユーザー情報を、user_idで結合して詳細な分析を行うこともできる。
以下のクエリは、GA4のイベントデータとサービスのユーザーデータを結合し、年齢ごとにどのイベントがどれだけ行われたかを集計。
- GA4 BigQueryエクスポート:
- プロジェクト名:
ga4_project - データセット名:
analytics_123456789 - テーブル名:
events(GA4からエクスポートされたイベントデータを含む)
- プロジェクト名:
- 元のサービスのデータベース:
- プロジェクト名:
service_project - データセット名:
service_dataset - テーブル名:
users(ユーザー情報、例えば年齢やユーザーIDを含む)
- プロジェクト名:
SELECT
service_u.age,
ga4_e.event_name,
COUNT(ga4_e.event_name) as event_count
FROM
`service_project.service_dataset.users` service_u
JOIN
`ga4_project.analytics_123456789.events` ga4_e
ON
service_u.user_id = ga4_e.user_id
GROUP BY
service_u.age,
ga4_e.event_name
ORDER BY
service_u.age,
event_count DESC;ユーザー獲得経路を追跡するためのBigQuery SQLクエリの作成
今回は、signup_completeイベントが発生したユーザーの獲得ソース（source）を月単位で表示するBigQueryのSQLクエリを作成してみる。
SELECT
traffic_source.source AS source, -- トラフィックソースを選択
COUNT(DISTINCT user_pseudo_id) AS users -- ユニークなユーザー数をカウント
FROM
`ha-ga4.analytics_227084301.events_*` -- データセットとテーブルの指定
WHERE
event_name = 'signup_complete' AND -- 会員登録イベントをフィルタリング
_TABLE_SUFFIX BETWEEN '20220201' AND '20220205' -- 分析する日付範囲の指定
GROUP BY
sourceyour_project_idをあなたのGoogle CloudプロジェクトIDに置き換える。your_dataset_idをGA4データがエクスポートされたBigQueryのデータセットIDに置き換える。'YYYYMM01'と'YYYYMM31'を分析したい月の初日と最終日にそれぞれ置き換える。例えば、2023年1月のデータを分析する場合は'20230101'と'20230131'になる。
このクエリは、signup_completeイベントを持つユニークなユーザー（user_pseudo_id）の数を、それぞれのトラフィックソースごとに集計している。
その月にどのソースが最も効果的だったかを理解することができる。
GA4とBigQueryのタイムゾーンの不一致
GA4とBigQueryでは、タイムゾーンが異なる場合がある。GA4はプロジェクトの設定に基づくタイムゾーンを使用するのに対し、BigQueryではUTCがデフォルト。
これを解決するには、クエリ内でタイムゾーンを変換する関数を使用する。例えば、TIMESTAMP_MICROS関数を使って、マイクロ秒単位で表されるタイムスタンプを特定のタイムゾーンに変換できる。下記の1秒をselect文のevent_dateに加える。
DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Tokyo") AS event_date加えた後のSQLクエリがこちら
SELECT
DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Tokyo") AS event_date, -- イベントの発生日付をJSTで選択
traffic_source.source AS source, -- トラフィックソースを選択
COUNT(DISTINCT user_pseudo_id) AS users -- ユニークなユーザー数をカウント
FROM
`ha-ga4.analytics_227084301.events_*` -- データセットとテーブルの指定
WHERE
event_name = 'signup_complete' AND -- 会員登録イベントをフィルタリング
_TABLE_SUFFIX BETWEEN '20220201' AND '20220205' -- 分析する日付範囲の指定
GROUP BY
sourceさいごに
GA4のBigQueryからよく使いそうなSQLをまとめたクエリ集はこちら。とてもよくまとまっている。
https://www.ga4.guide/related-service/big-query/query-writing/
過去のデータを中間テーブルに保存し、日々のバッチ処理ではこの中間テーブルから必要なデータを取得することが、コスト削減と処理速度の向上につながることに関する記事はこちら。
コメント