GA4のイベントパラメータと永続的UDF

[mediba Adventカレンダー](https://qiita.com/advent-calendar/2023/mediba) 5日目です。
ビジネス5Gの苅部からはGA4向けのSQLについて書いていきます。

## 背景

GoogleAnalytics4（GA4）のデータをBigQuery経由で取り出す際、イベントパラメータ周りの処理が煩雑になる印象がありました。例えば、Universal Analytics 360ではページURLとページタイトルの抽出に以下のようなクエリを使用します。
### 

```sql
SELECT
  hits.page.pagePath,
  hits.page.pageTitle
FROM
  `projectId.analytics_propertyId.events_*`
```
この方法ではフィールド名を覚えていれば、簡単にデータを取り出せます。

しかし、GA4で同じデータを抽出しようとすると、以下のようにUNNESTを使う必要があります。

```sql
SELECT
(
  SELECT
    value.string_value -- string_valueを指定
  FROM
    UNNEST(event_params)
  WHERE
    KEY = 'page_location') AS page_location,
(
  SELECT
    value.string_value -- string_valueを指定
  FROM
    UNNEST(event_params)
  WHERE
    KEY = 'page_title') AS page_title
FROM
	`projectId.analytics_propertyId.events_*`
```


この方法は見づらく、記述も面倒です。UAでは2行のSELECT文で済んでいましたが、GA4ではサブクエリでイベントパラメータをUNNESTして取り出す必要があり、コードが冗長になります。

また、この方法ではvalueがint型の場合、以下のように明示的にint_valueを指定する必要があります。

```sql
SELECT
(
  SELECT
    value.int_value -- int_valueを指定
  FROM
    UNNEST(event_params)
  WHERE
    KEY = 'ga_session_id') AS ga_session_id
FROM
	`projectId.analytics_propertyId.events_*`
```

もちろんfloatであればfloat_valueを指定することとなります。

```sql
SELECT
(
  SELECT
    value.float_value -- float_valueを指定
  FROM
    UNNEST(event_params)
  WHERE
    KEY = 'hogehoge') AS ga_session_id
FROM
	`projectId.analytics_propertyId.events_*`
```

さらに、GA4では値の型が自動的に判定されるため、同じイベントパラメータであっても値によってはstring_valueやint_valueに格納されることがあります。これにより、事前に値の型が分からない場合、上記の方法ではデータの取りこぼしが発生する可能性があります。

そこで、GA4でのイベントパラメータの取得における「型指定の問題」と「コードの可読性の問題」の2点を解決する方法を考えます。

## COALESCEを使って型指定を回避

型指定の問題を解決するためには、COALESCE関数が役立ちます。この関数は、与えられた引数の中で最初のNULLでない値を返します。

https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions#coalesce

**例1： NULLが無い場合**
```SQL
SELECT COALESCE('A', 'B', 'C') as result

/*--------*
 | result |
 +--------+
 | A      |
 *--------*/
```

**例2： NULLがある場合**
```SQL
SELECT COALESCE(NULL, 'B', 'C') as result

/*--------*
 | result |
 +--------+
 | B      |
 *--------*
```



つまり以下のような形にすれば、string_valueがあれば、string_valueを、string_valueがなければ（NULLであれば）int_valueを、と続く形でNULLではない引数を返します。

```sql
SELECT
  (SELECT
    COALESCE(
      value.string_value,
      CAST(value.int_value AS string),
      CAST(value.float_value AS string),
      CAST(value.double_value AS string)
    )
  FROM
    UNNEST(event_params) AS x
  WHERE
    x.key = "page_location")
FROM
  `projectId.analytics_propertyId.events_*`
```

NULLではないいずれかの値が返却されるため、事前に型を意識する必要がなくなります。

ただ型指定の問題は解決したとしても、毎回イベントパラメータごとにCOALESCEを記述していたらコードがより冗長的になってしまいます。

## UDFとして関数定義

そこでUDF（ユーザー定義変数）を使って一時的な関数として定義します。
### 定義

```sql
CREATE TEMPORARY FUNCTION get_event_value(params ANY TYPE,name STRING) AS (
  ( 
    SELECT
      COALESCE( 
        value.string_value,
        cast(value.int_value AS string),
        cast(value.float_value AS string),
        cast(value.double_value AS string) 
      ) 
    FROM
      UNNEST(params) AS x 
    WHERE 
      x.key = name 
  )
);
```

このように事前に定義することで、同一SQL文の中で関数として利用することができます。

### 呼び出し

関数化することでUAのように簡潔になり可読性も高まりました。

```sql
SELECT
  get_event_value(event_params, 'page_location') AS page_location,
  get_event_value(event_params, 'page_title') AS page_title
FROM
  `projectId.analytics_propertyId.events_*`
```

ただこの方法の場合クエリを記述する度に、先頭で関数定義（CREATE TEMPORARY FUNCTION）する必要がありちょっと面倒です。どうせならグローバルに定義したいですよね。

## Persistent UDFとして関数定義

BigQueryには永続的な関数定義としてPersistentUDFが用意されていますので、これを利用することにします。

https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_function_statement

### 定義

プロジェクトIDとデータセット名、関数名を明示的に指定します。
OPTIONSでは関数の説明を指定できます。

```sql
CREATE OR REPLACE FUNCTION
  `projectId.datasetName.get_event_value` (params ANY TYPE, name STRING) AS (
  (
    SELECT
      COALESCE(
		value.string_value,
    	CAST(value.int_value AS string),
		CAST(value.float_value AS string),
		CAST(value.double_value AS string)
	  )
    FROM
      UNNEST(params) AS x
    WHERE
      x.key = name
  )
)OPTIONS(description = "GA4のイベントパラメータの抽出")
```

上記のクエリを実行するとCloudConsole側でもデータセットの中のルーティン配下に関数が定義されていることが確認できます。

![スクリーンショット 2023-10-29 15.48.31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/147689/7af4fabb-b8bd-ae3a-e602-a6e6f1d69c00.png)

![スクリーンショット 2023-12-03 13.59.20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/147689/b83f442e-5e13-ab28-9afd-094ab1b1d007.png)


### 呼び出し

```sql
SELECT
  `projectId.datasetName.get_event_value`(event_params, "page_location") AS page_location,
  `projectId.datasetName.get_event_value`(event_params, "page_title") AS page_title
FROM
  `projectId.analytics_propertyId.events_*`
```

一時的なUDFよりもわずかに長いコードになってしまいましたが、関数自体は再利用可能な形となりシンプルになりました。

以下のコードをIMEか何かに登録しておけばイベントパラメータの取り出しも楽になりそうですね。

```sql
`projectId.datasetName.get_event_value`(event_params, "") AS 
```

#### ※備考

PersistentUDFは任意のデータセットの中で定義しているので、データセットのリージョンの影響を受けます。
つまり、定義された関数とクエリ実行がクロスリージョンの場合にはエラーとなります。

そのためBigQueryを複数リージョンで運用している場合には、 region_tokyo、region_usのような形でUDF用のデータセットを用意してもいいかもしれません。
※もし他に良い方法があれば教えてください。

## まとめ

GA4のイベントパラメータにおける型の問題とコードの冗長性の問題は、COALESCEによる条件式と永続的UDFを組み合わせることで解決できました。




## 参考URL

https://zielinsky.alejand.ro/querying-ga4-event-parameters-in-bigquery-with-function-9f75eb0a2f89

https://tech.nri-net.com/entry/check_point_of_data_type_of_ga4_from_bq