【BigQuery_2】GA4データ詳細分析のためのクエリ

## テストパターンとページを指定したセッション数、CV件数、CVRのクエリ
このクエリは、**特定のパターン振り分けられ、特定のページへの訪問を行ったセッション数、CV件数、CVを抽出するクエリ**。
次項から各サブクエリに対して詳細な説明を行う。
```sql
WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS string)) AS sessions,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location' AND event_name = 'page_view') AS pages,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'variant_id' AND event_name = 'experiment_impression') AS testid,
    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) OVER(PARTITION BY CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS session_cv
  FROM `PROJECT.analytics_XXXX.events_*`
)

, master AS (
SELECT *,
  COUNT(DISTINCT IF(pages = 'https://XXXX.com/', sessions, NULL)) OVER(PARTITION BY sessions) AS page_count,
  COUNT(DISTINCT IF(testid = 'XXXXXXX.〇', sessions, NULL)) OVER(PARTITION BY sessions) AS test_count,
FROM base
ORDER BY session_cv DESC
)

SELECT
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)) / COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, user_pseudo_id, NULL))
FROM master
```

## base サブクエリについて
baseサブクエリは、ネストされたイベントパラメータなどの値や、セッション数の算出に必要なフィールドを抽出し、並べるためのクエリ。
このサブクエリをメインクエリとして実行した場合は、以下のようなテーブル形式となる。

pagesフィールドに値が入力されている場合、testidは「NULL」になる。
また、testidフィールドに値が入力されている場合、pagesフィールドは「NULL」になる。
これは、**GA4上で従属するイベントが異なるパラメータをディメンションに加えて「(not set)」が発生することと同じ現象**。

この現象を回避するために、このクエリをサブクエリとして上位のクエリでパラメータ同士が紐づくよう設定している。

| sessions | user_pseudo_id | pages | testid | session_cv |
|:-:|:-:|:-:|:-:|:-:|
|111-999|111|https://~.com|NULL|1|
|222-888|222|NULL|XXXX.0|1|
|333-777|333|https://~.com|NULL|1|
|333-777|333|NULL|XXXX.0|1|

各カラムは上から、セッションID、ユーザーID、page_location、variant_id、CV件数を抽出する。
セッションIDやCV件数を抽出する構文については、下記を参照。
[【BigQuery_1】GA4データ分析のための基本理解](https://qiita.com/herbz/private/da1cfc7ed1da154f2eb5)
```sql
WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS string)) AS sessions,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location' AND event_name = 'page_view') AS pages,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'variant_id' AND event_name = 'experiment_impression') AS testid,
    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) OVER(PARTITION BY CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS session_cv
  FROM `PROJECT.analytics_XXXX.events_*`
)
```

## master サブクエリについて
このクエリは、**従属するイベントが異なるために紐づかないパラメータを紐づかせるためのクエリ**。
baseサブクエリをすべて抽出し、2つのCOUNT関数を新たに追加している。次はCOUNT関数の細かい説明。
```sql
, master AS (
SELECT *,
  COUNT(DISTINCT IF(pages = 'https://XXXX.com/', sessions, NULL)) OVER(PARTITION BY sessions) AS page_count,
  COUNT(DISTINCT IF(testid = 'XXXXXXX.〇', sessions, NULL)) OVER(PARTITION BY sessions) AS test_count,
FROM base
ORDER BY session_cv DESC
)
```

### COUNT関数について
+ **特定のページURLに合致する「sessions」を「sessions」ごとにカウント**
「PARTITION BY」を「sessions」だとセッション数、「user_pseudo_id」だとユーザー数ごとにCOUNTする。
ユーザー数とセッション数どちらを利用するかで選択が変わる。

また、IF文にある「sessions」の部分は、baseサブクエリのカラムであれば特に指定はない。
とにかく「特定のページURL」という条件を満たした行をカウントしたいため。
ただ、DISTINCTで値が変わる可能性があるため、セッションベースならセッションIDの利用が無難。
```sql
COUNT(DISTINCT IF(pages = 'https://XXXX.com/', sessions, NULL)) OVER(PARTITION BY sessions) AS page_count,
```

### 従属するイベントが異なるパラメータごとにCOUNTさせる理由
pagesとtestidは従属するイベントが異なるので、どちらかの値が入力されていれば、片方は「NULL」となる。
なので、下記テーブル両方を満たしているsessions「333-777」のように、別行ごとにpagesとtestidが入る。

しかし、baseで指定したpages（とtestid）の値に、特定のページとパターンを与えて行ごとにCOUNTすれば、同じ行として抽出される。
この方法で紐づけられないパラメータ同士を紐づけている。
下記テーブルであれば、page_countが1以上、test_countが1以上のセッションID、つまり「333-777」が条件を満たすことになる。

| sessions | user_pseudo_id | pages | testid | session_cv | page_count | test_count |
|:-:|:-:|:-:|:-:|:-:|:-:|:-:|
|111-999|111|https://~.com|NULL|1|1|0|
|222-888|222|NULL|XXXX.0|1|0|1|
|333-777|333|https://~.com|NULL|1|**1**|**1**|
|333-777|333|NULL|XXXX.0|1|**1**|**1**|

## メインクエリについて
このメインクエリは、masterサブクエリで条件を満たすセッションIDをカウントするクエリ。
page_countが1以上、test_countga1以上が今回の対象であるため、それをIF文で指定。
また、CV件数は「session_cvが1以上」とうい条件が必要なので追加。
```sql
SELECT
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)) / COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, user_pseudo_id, NULL))
FROM master
```

## さらに条件を増やして詳細分析する方法
**イメージとしてセグメントに条件を足すことに近い。**
パターンとページの指定から、さらに特定の導線の利用の条件を追加する場合など。

追加条件
・**導線のクリック**

手順
**1. baseサブクエリへ対象パラメータを追加**
**2. masterサブクエリで紐づかせるためのcount関数を追加**
**3. メインクエリで条件を追加**

### 1. baseサブクエリへ対象パラメータを追加
page_location、variant_idの抽出方法と同様にネストされているので、同じ構文で対応。
```sql:before
WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS string)) AS sessions,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location' AND event_name = 'page_view') AS pages,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'variant_id' AND event_name = 'experiment_impression') AS testid,
    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) OVER(PARTITION BY CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS session_cv
  FROM `PROJECT.analytics_XXXX.events_*`
)
```

```sql:after
WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS string)) AS sessions,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location' AND event_name = 'page_view') AS pages,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'variant_id' AND event_name = 'experiment_impression') AS testid,
-- 導線の利用 条件ここから
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'click_label' AND event_name = 'GTM_クリック') AS event,
-- 導線の利用 条件ここまで
    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) OVER(PARTITION BY CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS session_cv
  FROM `PROJECT.analytics_XXXX.events_*`
)
```

### masterサブクエリで紐づかせるためのcount関数を追加
page_location、variant_idの利用と同様の方法。
page_location、variant_id、click_labelがともに1以上のものが条件を満たす。
```sql:before
, master AS (
SELECT *,
  COUNT(DISTINCT IF(pages = 'https://XXXX.com/', sessions, NULL)) OVER(PARTITION BY sessions) AS page_count,
  COUNT(DISTINCT IF(testid = 'XXXXXXX.〇', sessions, NULL)) OVER(PARTITION BY sessions) AS test_count,
FROM base
ORDER BY session_cv DESC
)
```

```sql:after
, master AS (
SELECT *,
  COUNT(DISTINCT IF(pages = 'https://XXXX.com/', sessions, NULL)) OVER(PARTITION BY sessions) AS page_count,
  COUNT(DISTINCT IF(testid = 'XXXXXXX.〇', sessions, NULL)) OVER(PARTITION BY sessions) AS test_count,
-- 導線の利用 条件ここから
  COUNT(DISTINCT IF(event LIKE '導線Bのラベル名', sessions, NULL)) OVER(PARTITION BY sessions) AS event_count
-- 導線の利用 条件ここまで
FROM base
ORDER BY session_cv DESC
)
```

### 3. メインクエリで条件を追加
説明不要。
このメインクエリを実行すると「導線Bをクリックし」特定のページへ訪問した、特定のパターンに振り分けられたセッション数、CV件数、CVRとなる。
```sql:before
SELECT
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)) / COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, user_pseudo_id, NULL))
FROM master
```

```sql:after
SELECT
-- すべてのCOUNT関数に「AND event_count>=1」がある
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND event_count>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND event_count>=1 AND session_cv>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND event_count>=1 AND session_cv>=1, sessions, NULL)) / COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND event_count>=1, user_pseudo_id, NULL))
FROM master
```

## ディメンションを追加し網羅的に確認する方法
**イメージとして、GA4へディメンションを追加することに近い。**
クリックラベル別に各パターンを確認したい場合など。

追加条件
・**クリックラベルのディメンション**

手順
**1. baseサブクエリへ対象パラメータを追加**
**2. メインクエリで条件を追加しGROUP BY**

### 1. baseサブクエリへ対象パラメータを追加
page_location、variant_idの抽出方法と同様にネストされているので、同じ構文で対応。
```sql:before
WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS string)) AS sessions,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location' AND event_name = 'page_view') AS pages,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'variant_id' AND event_name = 'experiment_impression') AS testid,
    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) OVER(PARTITION BY CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS session_cv
  FROM `PROJECT.analytics_XXXX.events_*`
)
```

```sql:after
WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS string)) AS sessions,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location' AND event_name = 'page_view') AS pages,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'variant_id' AND event_name = 'experiment_impression') AS testid,
-- 導線の利用 条件ここから
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'click_label' AND event_name = 'GTM_クリック') AS event,
-- 導線の利用 条件ここまで
    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) OVER(PARTITION BY CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS session_cv
  FROM `PROJECT.analytics_XXXX.events_*`
)
```

### 2. メインクエリで条件を追加しGROUP BY
```sql:before
SELECT
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)) / COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, user_pseudo_id, NULL))
FROM master
```

```sql:after
SELECT
-- クリックラベルをディメンションとする ここから
　label,
-- クリックラベルをディメンションとする ここまで
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, sessions, NULL)) AS sessions,
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)) AS cv,
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)) / COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, user_pseudo_id, NULL)) AS cvr
FROM master
GROUP BY label
ORDER BY 2 DESC
```

### 上記の描画イメージ
| label | sessions | cv | cvr |
|:-:|:-:|:-:|:-:|
|導線A|1000|20|0.02|
|導線B|800|10|0.0125|
|導線C|500|50|0.1|


## テストの効果検証で実際に利用したクエリ
テストパターンごと、ラベルごとで抽出。上記すべてのクエリの応用編的な。

**描画イメージ**
| lael | pattern0_session | pattern0_cv | pattern1_session | pattern1_cv | pattern2_session | pattern2_cv |
|:-:|:-:|:-:|:-:|:-:|:-:|:-:|
|導線A|1000|50|1000|35|1000|70|
|導線B|800|10|800|2|800|20|
|導線C|500|50|500|10|500|70|

```sql
WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS sessions,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_path' AND event_name = 'page_view') AS pages,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'variant_id' AND event_name = 'experiment_impression') AS testid,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'click_label' AND event_name = 'GTM_クリック') AS label,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'click_category' AND event_name = 'GTM_クリック') AS category,
    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) OVER(PARTITION BY CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS session_cv
  FROM `PROJECT.analytics_XXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20220819'AND '20220829'
)

, master AS(
SELECT *,
  COUNT(DISTINCT IF(pages LIKE '/shop/products/alo_%', sessions, NULL)) OVER(PARTITION BY sessions) AS page_count,
  COUNT(DISTINCT IF(testid = 'XXXX.2', sessions, NULL)) OVER(PARTITION BY sessions) AS test_count_2,
  COUNT(DISTINCT IF(testid = 'XXXX.1', sessions, NULL)) OVER(PARTITION BY sessions) AS test_count_1,
  COUNT(DISTINCT IF(testid = 'XXXX.0', sessions, NULL)) OVER(PARTITION BY sessions) AS test_count_0
FROM base
ORDER BY session_cv DESC
)

SELECT
  label,
  COUNT(DISTINCT IF(page_count>=1 AND test_count_0>=1, sessions, NULL)) AS pattern0_session,
  COUNT(DISTINCT IF(page_count>=1 AND test_count_0>=1 AND session_cv>=1, sessions, NULL)) AS pattern0_cv,
  COUNT(DISTINCT IF(page_count>=1 AND test_count_1>=1, sessions, NULL)) AS pattern1_session,
  COUNT(DISTINCT IF(page_count>=1 AND test_count_1>=1 AND session_cv>=1, sessions, NULL)) AS pattern1_cv,
  COUNT(DISTINCT IF(page_count>=1 AND test_count_2>=1, sessions, NULL)) AS pattern2_session,
  COUNT(DISTINCT IF(page_count>=1 AND test_count_2>=1 AND session_cv>=1, sessions, NULL)) AS pattern2_cv
FROM master
GROUP BY label
ORDER BY 2 DESC
```