# with句を利用したテーブル内ネスト構造の集計

## 目的
- BigQueryに保存されたAnalyticsデータを柔軟に抽出して必要なデータを取得する方法として、
レコード内にネストされた情報を分解する方法を説明します。

- ネストしたデータから必要なデータを抽出することで、より詳細な情報を得られるようになり、
独自のレポートを作成する際に非常に効果的になります。

## 課題
- 前回は1つのSQLでBigQueryに保存されたデータから必要な情報を抽出する方法を説明しました。
今回は複数のSQLを組み合わせて実行した結果を用いてより詳細なデータを収集する方法を中心に説明します。

若干複雑な内容になりますが、この方法を理解する事によって、
GAの探索機能やレポートでは再現の難しい、様々なニーズに合わせたデータ分析へと近づいていきます。

## 詳細
### 基本 : 複数SQLの実行結果の集約について
- BigQueryのテーブルは下記のようにレコード内にネストされた情報が多く登録されています。
前回UNNEST関数を利用してネストされたデータを取得する方法を説明しましたが、
それだけでは複数のデータをネストから分解して一覧に並べることはできません。

この問題を解決するためにWITH句というSQL構文を利用して、
副問合せ(サブクエリ)を作り、名前を付与して一時テーブルとして扱うことができます。

主にGA4データのBigQuery分析においては下記の2点のために利用します
- 抽出項目ごとにWITH句作成し最後にまとめて出力する
- 複雑な検索結果ごとにWITH句作成し最後にまとめて出力する

まずは基本的なWith句の使い方を説明します。
BigQueryでは下記のように1レコードにpage_titleとpage_locationがまとまっていて、
それぞれの組み合わせで何軒ずつのレコードが登録されているかがわかりにくい状態です。

下記のSQLを実行することでpage_titleとpage_locationの組み合わせのレコード件数を
集計することが可能になります

### SQL

-- WITHを定義してt1という一時テーブルに情報を取集する
WITH t1 AS (
  SELECT
    -- event_paramsにネストされたデータからkeyがpage_titleの値をtitleという項目名で取得する
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "page_title") AS title,
    -- event_paramsにネストされたデータからkeyがpage_locationの値をlocationという項目名で取得する
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "page_location") AS location,
    event_name
   FROM
    `analytics_264011635.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20211101'
    AND '20211110'
)
SELECT
  title, -- t1で取得したtitle
  location,  -- t1で取得したlocation
  COUNT(*) as count -- レコード件数
FROM
  t1
WHERE
  event_name = "first_visit" --　event_nameがfirst_visitの値を抽出
GROUP BY location, title -- SELECT句に列挙したカラム名をグループ化する

#### SQLの説明
- WITH t1
　- t1 という名称の一時テーブルを生成して下記の値を取得する。
　- ネストされた event_paramsを分解してkey が "page_title"のデータを抽出します。
　- ネストされた event_paramsを分解してkey が "page_location"のデータを抽出します。
　- 取得対象のレコードはeventカラムの値を対象とします。
　- 取得対象のテーブルは2021/11/01から2021/11/10までのものを対象にします。
- WITH以下のSELECT句
- t1で収集したデータに対してSQLを実行する
- t1からSELECTするカラムはグループ化(GROUP BY)する

### 結果例

|行|title|location|count|
|---|---|---|---|
|1|株式会社菊原web解析事務所| https://kikuhara.site/ | 13 |
|2|【GCPエンジニア監修】<br> GA4のデータをBigQueryで分析するための方法| https://kikuhara.site/.../ga4-bigquery-01/ |4|
|3|【ＧＡ】固有の購入数と数量の意味 \|<br> やる夫で学ぶGoogleAnalytics | https://kikuhara.site/.../ec_metrics/ |2|
|4 | googleタグマネージャーのみで<br> 重複ＵＲＬを仮想ページビュー化する方法 – | https://kikuhara.site/.../gtm-kasoupageview/ |2|
|5 |[どっちが人気？]<br> 欅坂46と乃木坂46の人気を分析してみた。 \| <br> やる夫で学ぶGoogleAnalytics |https://kikuhara.site/.../keyakizaka_nogizaka/ |2|

### 応用 : 日単位セッション数の取得
- with句を日単位のセッション数を取得する方法を説明します。
データを取得する上で視認性を向上させるためにevent_timestampのフォーマットをYYYY-MM-DD型に変換します。
ユーザー(ブラウザ)特定IDであるuser_pseudo_idとevent_paramsに含まれているga_sessionを結合した値を1セッションとして扱います

上記の情報から1日あたりのセッション数を集計することが可能になります

#### SQL

WITH
  t1 AS (
  SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS target_date,
    CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid,
  FROM
    `analytics_264011635.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20211201'
    AND '20211210'
    AND event_name = 'session_start' )
SELECT
  target_date,
  COUNT(DISTINCT sid) AS sessions
FROM t1
GROUP BY 1,
ORDER BY 1

#### SQLの説明
- WITH t1
　- t1 という名称の一時テーブルを生成して下記の値を取得する。
　- UNIXTIME型のevent_timestampを日本時刻のYYYY-MM-DD型に変換します。
　- user_pseudo_idとネストされた event_paramsを分解してkey が "ga_session_id"のデータを結合して1セッションのデータとします。
　- 上記の組み合わせてセッションの発生した日付のレコードが一つ生成されます。
- 取得対象のレコードはevent_nameがsession_start(セッション開始)を対象にします。
　- 取得対象のテーブルは2021/12/01から2021/12/10までのものを対象にします。
- WITH以下のSELECT句
- t1で収集したデータに対してSQLを実行する
- セッション数を集計したいので、t1で取得したセッションIDを集計(count)します。
- t1からSELECTするカラムはグループ化(GROUP BY)する
　- 過去の記事で説明した通り、実際のカラム名以外でもSELECTの順番の数値を指定することも可能です。

#### 結果例

| 行 | target_date | sessions |
| -------- | -------- | -------- |
| 1 | 2021-12-01 | 6 |
| 2 | 2021-12-02 | 5 |
| 3 | 2021-12-03 | 4 |
| 4 | 2021-12-04 | 3 |
| 5 | 2021-12-05 | 4 |

### 応用 : ページ別ランディング数、離脱数、直帰数、離脱率の集計
- with句を利用してページ別ランディング数・離脱数・直帰数を取得して、離脱率を計算した結果を一覧化する方法を説明します。
このケースではWITH句による一時テーブルを2つ生成します。
一つ目の一時テーブル(t1)ではevent_paramsにネストされたpage_locationのデータと、
それに紐付くセッションIDを取得します。セッションIDの形式は前項で説明した内容と同じものになります。

二つ目の一時テーブル(t2)ではt1で取得したデータに対してSQLを実行してアクセスデータを収集します。

最終的にt2の結果に対してSQLを実行して一覧を出力します。

#### SQL

WITH
  t1 AS (
  SELECT
    event_timestamp,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid
  FROM
    `analytics_264011635.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20211001' and '20211031'
  AND event_name = 'page_view'
  )
  ,t2 AS (
  SELECT
    page_location,
    CASE
      WHEN ROW_NUMBER() OVER(PARTITION BY sid ORDER BY  event_timestamp) = 1 THEN 1
      ELSE 0
    END AS entry,
    CASE
      WHEN ROW_NUMBER() OVER(PARTITION BY sid ORDER BY  event_timestamp DESC) = 1 THEN 1
      ELSE 0
    END AS exit,
    CASE
      WHEN COUNT(1) OVER(PARTITION BY sid) = 1 THEN 1
      ELSE 0
    END AS bounce
  FROM
    t1
  )
SELECT
  t2.page_location AS page_location,
  SUM(t2.entry) AS entry,
  SUM(t2.exit) AS exit,
  SUM(t2.bounce) AS bounce,
  IFNULL(SAFE_DIVIDE(CAST(SUM(t2.entry) AS numeric), CAST(SUM(t2.exit) AS numeric)), 0) AS exit_ratio
FROM t2
GROUP BY 1
ORDER BY 2 DESC

#### SQLの説明
- WITH t1
- t1 という名称の一時テーブルを生成して下記の値を取得する。
- event_paramsにネストされたpage_locationのデータを取得します。
- セッションIDを取得します。セッションIDの形式は前項で説明した内容と同じものになります。
- WITH t1
- t2 という名称の一時テーブルを生成して下記の値を取得する。
- t2ではt1で取得したデータに対してSQLを実行します。
- page_locationに対するセッションIDレコードの順番をもと3つの指標を取得します
- 今回はCASE文を利用して3つのケースに分類しています。
- event_timestamp(イベント発生日)をソートした際に最初に現れるページをランディング、
最後に現れるページを離脱ページ、そしてセッション内にレコードが一件の場合は直帰として扱います。
- WITH以下のSELECT句
- t2で収集したデータに対してSQLを実行する
- entry, exit, bounceをそれぞれSUM関数を使い集計します
- [entryの集計結果 ÷ exitの集計結果]の結果を離脱率として取得します
　- SAFE_DIVIDE関数によって0を割算した際のエラーを回避して、0を設定するようにしています。
- IFNULL関数によってentryまたはexitに値が含まれない場合は計算をしないようにしています。
- t1からSELECTするカラムはグループ化(GROUP BY)する

#### 結果例
|行|page_location|entry|exit|bounce|bounce_ratio|
| -------- | -------- | -------- |-------- |-------- |-------- |
|1|https://kikuhara.site/|10|8|7|1.25|
|2|https://kikuhara.site/work/gtm-kasoupageview/|7|7|7|1|
|3|https://kikuhara.site/yaruo/bunsekishuhou/keyakizaka_nogizaka/|3|3|3|1|
|4|https://kikuhara.site/yaruo/googleanalytics_kinou/utm_cookie-2/|2|2|2|
|5|https://kikuhara.site/work/bouns-contents/|2|2|2|1|

まとめ
複数のSQLを組み合わせてより詳細なレポートを出力する方法について説明しました。
取得したいデータを1回のSQLで取得できない場合はWITH句を利用して一時テーブルを作成することで
データを階層的に収集して使いやすい形にフィルタリングすることが有効になります。

特にBigQueryに登録されるデータの活用は
ネストされているデータをうまく取り扱うことがコツになります。

前回説明したUNNEST関数とWITHを組み合わせて用途に応じたSQLを生成することが、
さらなるデータ活用やニーズに応じたレポートを柔軟に生成するために重要になります。