BigQueryでGA4操作の学習メモ

準備

データセットはGA4が提供している下記のサンプルデータを使用
ga4_obfuscated_sample_ecommerce

基本操作

page_viewイベントのevent_paramsから値を取得する方法

この場合はページURL

WITH
  master AS (
    SELECT
      event_date,
      event_name,
      event_params
    FROM
      bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131
    WHERE
      event_name = 'page_view'
  )
SELECT
  event_date,
  event_name,
  (                              
    SELECT value.string_value
    FROM   UNNEST(event_params)  -- event_paramsをUNNESTして展開
    WHERE  key = 'page_location'
  ) AS page_location
  FROM
    master

page_viewイベントのevent_paramsから複数の値を取得する方法

この場合はページURLとタイトル

WITH
  master AS (
    SELECT
      event_date,
      event_name,
      event_params
    FROM
      bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131
    WHERE
      event_name = 'page_view'
  )
SELECT
  event_date,
  event_name,
  (
    SELECT value.string_value
    FROM   UNNEST(event_params)
    WHERE  key = 'page_location'
  ) AS page_location,
  (                                --サブクエリが増えただけ
    SELECT value.string_value
    FROM   UNNEST(event_params)
    WHERE  key = 'page_title'
  ) AS page_title
  FROM
    master

複数のテーブルをまたいでevent_params下の値を集計する

この場合はページタイトル別のページビュー数

SELECT
  page_title,
  COUNT(*) AS page_views
FROM
  (
    SELECT
      (
        SELECT value.string_value 
        FROM UNNEST(event_params)
        WHERE key = 'page_title' 
      ) AS page_title
    FROM  -- ↓の*はワイルドカードとして利用できる ※``でデータセットを囲む必要あり
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE -- ↑で指定した*には↓で指定した_TABLE_SUFFIX(条件)が適用される
      _TABLE_SUFFIX BETWEEN '20201101' AND '20210131' AND event_name = 'page_view'
  )
GROUP BY page_title

実用例

ユーザー数と新規ユーザー数

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      MAX(IF(event_name IN ('first_visit', 'first_open'), 1, 0)) AS is_new_user
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'
    GROUP BY 1
  )
SELECT
  COUNT(*) AS user_count,
  SUM(is_new_user) AS new_user_count
FROM UserInfo;

購入ユーザーあたりの平均トランザクション数

SELECT
  COUNT(*) / COUNT(DISTINCT user_pseudo_id) AS avg_transaction_per_purchaser
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN ('in_app_purchase', 'purchase')
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201231';

購入ユーザーのタイプ別の平均ページビュー数

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      COUNTIF(event_name = 'page_view') AS page_view_count,
      COUNTIF(event_name IN ('in_app_purchase', 'purchase')) AS purchase_event_count
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
    GROUP BY 1
  )
SELECT
  (purchase_event_count > 0) AS purchaser,
  COUNT(*) AS user_count,
  SUM(page_view_count) AS total_page_views,
  SUM(page_view_count) / COUNT(*) AS avg_page_views,
FROM UserInfo
GROUP BY 1

各ユーザーのセッションあたりの平均購入額

SELECT
  user_pseudo_id,
  COUNT(
    DISTINCT(SELECT EP.value.int_value FROM UNNEST(event_params) AS EP WHERE key = 'ga_session_id'))
    AS session_count,
  AVG(
    (
      SELECT COALESCE(EP.value.int_value, EP.value.float_value, EP.value.double_value)
      FROM UNNEST(event_params) AS EP
      WHERE key = 'value'
    )) AS avg_spend_per_session_by_user,
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'purchase'
  AND _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
GROUP BY 1

参考
https://developers.google.com/analytics/bigquery/basic-queries?hl=ja