前提
以下記事を90%参考にしているので、より詳しく知りたい方はこちらを確認ください。
当記事は、以下記事を補足したり、付け足したりしたものとご理解ください。
基本的なクエリ
表示回数
ページビューイベントの発火回数のカウントで抽出。
COUNTはNULLを含まないという性質があるため、COUNTへCASE式をネストさせ、THENでカウントさせるフィールドを指定。
フィールドは何でも良い。DISTINCTしてなければ「行数のカウント」とほぼ同じため。
DISTINCTさせたuser_pseudo_idのCOUNTだと、「page_viewを発火させたユーザー数」になるため注意。
ちなみに指標に対して「〇〇した条件」を付与したい場合、COUNT関数へCASE式を入れて条件指定する方法がログ分析では一般的とされている。
SELECT
COUNT(
CASE
WHEN event_name = 'page_view' THEN user_pseudo_id
ELSE NULL
END) AS pageviews
FROM `PROJECT.analytics_XXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'
セッション
セッションIDは「ga_session_id」となる。
しかし「ga_sessions_id」というフィールドは用意されてない。
「event_params」フィールドの下層にネストされた 「key」フィールドの値として入力されている。
BigQueryでは、下記2つの表のように格納されている。
| event_name | event_params.key | event_params.value.int_value | event_params.value.int_value |
|---|---|---|---|
| page_view | page_location | https://~.com | NULL |
| - | page_title | トップページ | NULL |
| - | ga_session_id | NULL | 111111.555555 |
| フィールド名 | - | - |
|---|---|---|
| ▼ event_params | - | - |
| - | key | |
| - | ▼ value | |
| - | string_value | |
| - | int_value |
また、「ga_session_id」はユーザーごとに複数紐づいてるIDなので、ユーザーIDである「user_pseudo_id」と結合させる。
このユーザーIDとセッションIDを結合させた値のユニークカウントを「セッション数」と定義する。
結合にはCONCATを利用するが、CONCATA関数は結合する両方のフィールドのデータ型がSTRING型でなければならない。
そのため、INTEGER型である「ga_session_id」をSTRING型に変換するための、CAST関数を利用している。
データ型についてはこちら
SELECT
COUNT(DISTINCT
CONCAT(user_pseudo_id,
'-',
CAST(
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS string))) AS sessions
FROM `PROJECT.analytics_XXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'
ユーザ
「user_pseudo_id」のユニークカウント。
Google公式ヘルプでは、下記のように記載されている。
ユーザーの仮の ID（アプリ インスタンス ID など）
GA4で「UserID機能」を導入している場合は、「user_id」フィールドを利用。
setUserId API によって設定されるユーザー ID。
SELECT
COUNT(
DISTINCT user_pseudo_id) AS users
FROM `PROJECT.analytics_XXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'
CVの基本的なクエリ
「event_cv」は、purchaseイベントの発火をカウントさせた（=イベント数）CV件数。
「user_cv」は、purchaseイベントが発火したユーザーIDを重複を除いてカウントさせた（=ユーザーイベント数）CV件数。
「session_cv」は、purchaseイベントが発火したセッションIDを重複を除いてカウントさせた（=セッションイベント数）CV件数。
「session_cv」がやや複雑だが、上記セッション数のカウントと同じようにユーザーIDとセッションIDを結合させたIDと定義している。
SELECT
COUNT(event_name) AS event_cv,
COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id ELSE NULL END) AS user_cv,
COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) ELSE NULL END) AS session_cv,
FROM `PROJECT.analytics_XXXX.events_*`
WHERE event_name = 'purchase'
ネストされた「event_params」を抽出する方法
GA4データはネストされたフィールドが多く存在するため、UNNESTが必須。
UNNESTを知らない方はこちらを必読
ページURL別に表示回数を抽出したい場合、GA4ではディメンションにページURLを、指標に表示回数をドラック&ドロップすれば済む。
しかし、BigQueryでは、ページURL（page_location）というパラメータはネストされているため、UNNESTでネスト型を解除する必要がある。
パラメータとその値が格納されているフィールド
下記表にて説明する。下層にあるフィールドを「event_params.key」とピリオドをつけて表現。
page_locationというパラメータであれば、そのパラメータ名と値は以下フィールドで表示される。
- event_params.key「page_location」
- event_params.value.string_value「https://XXXXX.com/~」
string_valueフィールドは「STRING型」の値が入り、int_valueフィールドは「INTEGER型」の値が入る。
page_loctionは文字列でSTRING型であるため、string_valueに値が格納され、int_valueはNULLになっている。
| フィールド名 | - | - |
|---|---|---|
| ▼ event_params | - | - |
| - | key | |
| - | ▼ value | |
| - | string_value | |
| - | int_value |
ページURL別の表示回数
手順
1. 格納されているpage_locationを抽出するクエリを作成
2. 手順1のクエリをサブクエリとしてSELECT句へ追加
3. 手順2のクエリにてpage_viewイベントのカウントを行い抽出
1. 格納されているpage_locationを抽出するクエリを作成
描画イメージ
| 行 | string_value |
|---|---|
| 1 | https://XXXX.com/ |
| 2 | https://XXXX.com/price/ |
SELECT
value.string_value
FROM `PROJECT.analytics_XXXX.events_*`,
UNNEST(event_params)
WHERE key = 'page_location' AND event_name = 'page_view'
2. 手順1のクエリをサブクエリとしてSELECT句へ追加
手順1のクエリをサブクエリとした際の構文は以下。
()で囲む。この構文を一つのカラムとして、SELECT句の中に設置する。
(SELECT
value.string_value
FROM `PROJECT.analytics_XXXX.events_*`,
UNNEST(event_params)
WHERE key = 'page_location' AND event_name = 'page_view')
3. 手順2のカラムをGROUP BYしpage_viewイベントをカウント
サブクエリのFROM句にテーブル名が入力されていないが、このサブクエリはメインクエリであるFROMを参照する点、FROM句はUNNESTを参照としている点から入力していない。
SELECT
-- 表示回数を抽出するクエリ ※本ページの上部「表示回数」を参照
COUNT(
CASE
WHEN event_name = 'page_view' THEN user_pseudo_id
ELSE NULL
END) AS pageviews,
-- 手順1のクエリをサブクエリとしてSELECT句へ追加
(SELECT
value.string_value
FROM UNNEST(event_params)
WHERE key = 'page_location' AND event_name = 'page_view') AS pages
FROM `PROJECT.analytics_XXXX.events_*`
GROUP BY pages
ORDER BY pageviews DESC
最後に
以下2つの応用で、多くのGA4データの抽出は可能。
COUNT( CASE WHEN 条件 THEN カウントさせる値（user_pseudo_idなど） ELSE NULL END)
(SELECT 値（value.string_valueなど） FROM UNNEST(event_params) WHERE key = '目的のパラメータ（page_location）'
次は以下必読