GA4からBQ Exportされたパラメータの値がどのフィールドに格納されているか調べる

BigQueryにエクスポートされたGA4のパラメータの値は、その中身によって格納されるフィールドが変わるので、クエリを書くときにはパラメータがどのフィールドに格納されているかを把握していないと正しい結果を得られない場合がある。
このため、事前に以下のクエリを叩くことで、各パラメータがどのフィールドに格納されたかを調査できる。

## パラメータが格納される先のバリエーション
- イベントパラメータ
    - event_params.value.string_value
    - event_params.value.int_value
    - event_params.value.double_value
    - event_params.value.float_value
- ユーザープロパティ
    - user_properties.value.string_value
    - user_properties.value.int_value
    - user_properties.value.double_value
    - user_properties.value.float_value

https://support.google.com/analytics/answer/7029846?hl=ja

## 調査用のクエリ
※ `PROJECT-NAME.analytics_123456789` と対象日付の部分を変更の上使用

```sql
WITH base AS (
  SELECT 
    event_params
    , user_properties
  FROM `PROJECT-NAME.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20230101' AND '20231231'
)
, param_type_summary AS (
  SELECT 
    'event' AS scope
    , ep.key AS param_key
    , COALESCE(
      IF(ep.value.string_value IS NOT NULL, 'string', NULL)
      , IF(ep.value.int_value IS NOT NULL, 'int', NULL)
      , IF(ep.value.float_value IS NOT NULL, 'float', NULL)
      , IF(ep.value.double_value IS NOT NULL, 'double', NULL)
    ) AS param_type
    , COUNT(*) AS total_events
  FROM base, UNNEST(event_params) as ep
  GROUP BY 1, 2, 3
  
  UNION ALL
  
  SELECT 
    'user' AS scope
    , up.key AS param_key
    , COALESCE(
      IF(up.value.string_value IS NOT NULL, 'string', NULL)
      , IF(up.value.int_value IS NOT NULL, 'int', NULL)
      , IF(up.value.float_value IS NOT NULL, 'float', NULL)
      , IF(up.value.double_value IS NOT NULL, 'double', NULL)
    ) AS param_type
    , COUNT(*) AS total_events
  FROM base, UNNEST(user_properties) as up
  GROUP BY 1, 2, 3
)

SELECT
  scope
  , param_key
  , COUNT(*) AS type_variations
  , SUM(total_events) AS total_events
  , SUM(CASE WHEN param_type='string' THEN total_events ELSE 0 END) AS type_string_events
  , SUM(CASE WHEN param_type='int' THEN total_events ELSE 0 END) AS type_int_events
  , SUM(CASE WHEN param_type='float' THEN total_events ELSE 0 END) AS type_float_events
  , SUM(CASE WHEN param_type='double' THEN total_events ELSE 0 END) AS type_double_events
FROM param_type_summary
GROUP BY 1, 2
ORDER BY 1, 3 DESC, 4 DESC, 2;
```

実行結果の例
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103957/da78bab5-50ed-7ce7-dfcd-31f3324b8ab0.png)



上記スクリーンショットの結果では以下のことが分かる。

- パラメータ`page_group`の値はすべて`event_params.value.string_value`に格納されていること
- パラメータ`search_term`の値は`event_params.value.string_value`, `event_params.value.int_value`の何れかに格納されていること

## 値が入るフィールドが固定されないパラメータの対策

値がどのフィールドに入るか予測できないパラメータは[COALESCE](https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions#coalesce)を使ってどのフィールドに入っても処理できるようにすると安心。

```sql
SELECT DISTINCT
  (SELECT COALESCE(value.string_value, SAFE_CAST(value.int_value AS string),SAFE_CAST(value.float_value AS string),SAFE_CAST(value.double_value AS string)) FROM UNNEST(event_params) WHERE key = 'search_term') AS ep_search_term
FROM              
    `PROJECT-NAME.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20230101' and '20231231'
ORDER BY 1
```

```sql
WITH search_term_events AS (
  SELECT
    event_name
    , ep.value.string_value AS string_value
    , SAFE_CAST(ep.value.int_value AS STRING) AS int_value
    , SAFE_CAST(ep.value.float_value AS STRING) AS float_value
    , SAFE_CAST(ep.value.double_value AS STRING) AS double_value
    , ep.key
  FROM `PROJECT-NAME.analytics_123456789.events_*`,
  UNNEST(event_params) as ep
  WHERE 
    _TABLE_SUFFIX BETWEEN '20230101' AND '20231231'
    AND ep.key = 'search_term'
)

SELECT
  event_name
  , COALESCE(string_value, int_value, float_value, double_value) AS ep_search_term
  , COUNT(*)
FROM search_term_events
WHERE COALESCE(string_value, int_value, float_value, double_value) IS NOT NULL
GROUP BY 1, 2
ORDER BY 1, 3 DESC
```