【BigQuery_1】GA4データ分析のための基本理解

前提

以下記事を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つの表のように格納されている。

また、「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になっている。

ページURL別の表示回数

手順
1. 格納されているpage_locationを抽出するクエリを作成
2. 手順1のクエリをサブクエリとしてSELECT句へ追加
3. 手順2のクエリにてpage_viewイベントのカウントを行い抽出

1. 格納されているpage_locationを抽出するクエリを作成

描画イメージ

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）' 

次は以下必読