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

## 前提
以下記事を90%参考にしているので、より詳しく知りたい方はこちらを確認ください。
当記事は、以下記事を補足したり、付け足したりしたものとご理解ください。

https://www.marketechlabo.com/weblog-metrics-ga4/

## 基本的なクエリ
### 表示回数
ページビューイベントの発火回数のカウントで抽出。
COUNTはNULLを含まないという性質があるため、COUNTへCASE式をネストさせ、THENでカウントさせるフィールドを指定。
フィールドは何でも良い。DISTINCTしてなければ「行数のカウント」とほぼ同じため。

DISTINCTさせたuser_pseudo_idのCOUNTだと、「page_viewを発火させたユーザー数」になるため注意。

**ちなみに指標に対して「〇〇した条件」を付与したい場合、COUNT関数へCASE式を入れて条件指定する方法がログ分析では一般的とされている**。

```sql
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関数を利用している。
[データ型についてはこちら](https://tech.pjin.jp/blog/2020/10/30/%E3%80%90sql%E5%85%A5%E9%96%80%E3%80%91%E3%83%87%E3%83%BC%E3%82%BF%E5%9E%8B%E3%81%A8%E3%81%AF/#:~:text=%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%81%AE%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AF%E5%88%97,%E3%83%87%E3%83%BC%E3%82%BF%E5%9E%8B%E3%81%A8%E5%91%BC%E3%81%B3%E3%81%BE%E3%81%99%E3%80%82)

```sql
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。

[それぞれのフィールドについてはこちら](https://support.google.com/firebase/answer/7029846?hl=ja#:~:text=%E3%83%9E%E3%82%A4%E3%82%AF%E3%83%AD%E7%A7%92%E5%8D%98%E4%BD%8D%EF%BC%89%E3%80%82-,user_id,-STRING)
```sql
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と定義している。

```sql
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を知らない方はこちらを必読](https://qiita.com/uehiro22/items/485bb30ce1a70457d335)

ページ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/ |
```sql
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句の中に設置する。
```sql
(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を参照としている点から入力していない。

```sql
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データの抽出は可能。

+ [抽出させる指標に対して条件付けするための方法](#基本的なクエリ)

```sql
COUNT( CASE WHEN 条件 THEN カウントさせる値（user_pseudo_idなど） ELSE NULL END) 
```

+ [ネストされたパラメータを展開し抽出する方法](#ページURL別の表示回数)

```sql
(SELECT 値（value.string_valueなど） FROM UNNEST(event_params) WHERE key = '目的のパラメータ（page_location）' 
```

次は以下必読

https://qiita.com/herbz/private/11dd1ce67ca3b6c05d14