【GA4×BigQuery】個人的な躓きポイントと、GA4の数値取得時によく使いそうなクエリメモ


## はじめに

はじめまして、エンジニア歴半年のGopherくんLoverなペーペーエンジニアです。

訳あって2週連続でQiitaを書くことになったのですが、先週のスクレイピングの続きをする気力はなかったので…

また別の内容にしようと思います。
今回は、最近使用しているBigQueryについて、初めて触った際に躓いたポイントと、よく使うクエリを書き残そうと思います。

メモ的な要素が強いので参考程度にご覧いただけますと幸いです。

なお、BigQueryに関して超入門者なので、そこのところはご容赦ください。

## 個人的な躓きポイント
#### 【躓きポイントその1】GA4のBigQueryのスキーマについて

まず引っかかったのがBigQuery独特のテーブル構造でした。
通常のSQLと違う部分として、テーブルの要素の中にテーブルが入れ子になっている部分があります。

（スキーマについてはこちらを参照：[[GA4] BigQuery Export スキーマ](https://support.google.com/analytics/answer/3437719?hl=ja "[GA4] BigQuery Export スキーマ")）

例えば、events_paramsというフィールドでは、
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2711450/8336d13d-43f3-687b-ac86-6e157e6bfea4.png)
上記のような形で、events_paramsの中に複数の要素が入れ子になっています。
events_paramsの中を取り出すと、例えばこちらのような形です。

| events_params | key | string_value | int_value | float_value | double_value |
|:-:|:-:|:-:|:-:|:-:|:-:|
| user_engagement | page_location | https://qiita.com/ | null | null | null |
|   | engagement_time_msec | null | 35085 | null | null |
|   | ga_session_id | null | 1234567890 | null | null |
|   | session_engaged | 1 | null | null | null |
|   | page_referrer | https://www.google.com/webhp | null | null | null |
|   | engaged_session_event | null | 1 | null | null |
|   | firebase_conversion | null | 0 | null | null |
|   | page_title | Qiita | null | null | null |

このように入れ子になっている値を取り出すときは、各行が独立した形(unnestされた状態)する必要があります。

つまり、先ほどのテーブルを…

| events_params | key | string_value | int_value | float_value | double_value |
|:-:|:-:|:-:|:-:|:-:|:-:|
| user_engagement | page_location | https://qiita.com/ | null | null | null |
| user_engagement | engagement_time_msec | null | 35085 | null | null |
| user_engagement | ga_session_id | null | 1234567890 | null | null |
| user_engagement | session_engaged | 1 | null | null | null |
| user_engagement | page_referrer | https://www.google.com/webhp | null | null | null |
| user_engagement | engaged_session_event | null | 1 | null | null |
| user_engagement | firebase_conversion | null | 0 | null | null |
| user_engagement | page_title | Qiita | null | null | null |

このような感じで各行に情報が入っている状態(nullは問題ないです。)にします。
これにはunnest関数を使用します。

```sql
SELECT
  key,
  value.string_value,
  value.int_value,

FROM
  `analytics_table.events_20221208`, UNNEST(event_params)
```

こんな感じでevent_paramsをunnestして取り出すと…

![unnest 結果.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2711450/3287e697-38c2-3ee9-0463-d5ae51c3b8ae.png)

このような感じでkeyとvalueが取り出せます。
これができれば、あとはwhere句で指定するだけです。



#### 【躓きポイントその2】_TABLE_SUFIXによる日付範囲の指定

ここもなかなかしっくりこなかった部分です。
GA4におけるBigQueryでは、テーブルが日付ごとに分かれています。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2711450/d9a1eb05-3d1e-c182-344b-5286c71f7a9f.png" width="350">

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2711450/9b0689c2-bb29-cb6a-bbb9-c6097bd6476f.png" width="350">

画像でもわかるとおり、例えば2022年の12月6日のイベントは"events_20221206"のテーブルに格納されています。
そのため、12月1日～12月10日など、一定期間で数値を取得したいときは、該当期間のテーブルを全て参照する必要があります。

そこで登場するのが_TABLE_SUFIXです。

```sql
SELECT
  key,
  value.string_value,
  value.int_value,

FROM
  `analytics_table.events_202212*`, UNNEST(event_params)

WHERE
  _TABLE_SUFFIX BETWEEN '20221201' AND '20221210'  -- 12月1日から12月10日までの日付を参照する
```

このように、FROM句で指定するテーブルの日付の範囲をワイルドカードで指定し、その後のWHERE句で_TABLE_SUFIXを使用して日付を指定します。
これにより、任意の範囲の日付で数値を取得することができるようになります。

## GA4の数値取得時によく使いそうなクエリメモ

#### ⓪GA4のスキーマとよく使いそうな項目

クエリをいろいろ列挙する前に、GA4のスキーマの中でよく使いそうな項目も挙げておこうと思います。

詳しいGA4のスキーマに関してはこちらを参照してください。
参照：[[GA4] BigQuery Export スキーマ](https://support.google.com/analytics/answer/3437719?hl=ja "[GA4] BigQuery Export スキーマ")

この中で、個人的によく使用することになりそうだなと感じたものを以下に表にまとめておきます。

<details><summary><b>よく使用しそうなフィールド一覧</b></summary>

| フィールド名 | 説明 | 使用ポイント |
|:-:|:-:|:-:|
| event_name | イベントの名前。 | page_view, session_start, search, clickなど、イベントの種類が格納されている。取りたい情報によって指定する内容が異なり、ここをカウントすることが多い。 |
| event_params | このイベントに関連付けられたパラメータを格納する繰り返しレコード。 | page_titleやpage_locationなど、イベントの基本的な情報が連想配列で格納されている。keyで指定して取り出す。 |
| event_date | イベントが記録された日付（アプリの登録タイムゾーンにおける日付を YYYYMMDD 形式で示したもの）。| タイムゾーンを気にせずに日付を取得したいときに使用可能。(timestampを使う方がより精緻なイメージ) | 220 |
| event_timestamp | 該当クライアントでイベントが記録された時刻（ミリ秒単位、UTC）。| イベント発生時刻を取得したいときに使う。日別での集計などで使用する。 |
| device.category | デバイスのカテゴリ（モバイル、タブレット、PC）。| mobile, tablet, pcのどれか。デバイスごとの数値を取得したいときに使用。|
| device.operating_system | デバイスのオペレーティング システム。| Windows8, iosなど。デバイスのOSごとに数値を取得したいときに使用。 |
| traffic_source.name | ユーザーを最初に獲得したマーケティング キャンペーンの名前。| organic, direct, apppush, その他設定したキャンペーンの名前。流入元ごとに数値を取得したいときに使用。 |

</details>

ここからは、実際に数値を取った際に使用したクエリを少し一般化して書き残していこうと思います。

#### ①記事ごとにページビュー・セッション数を取得

```sql
SELECT
  (select value.string_value from unnest(event_params) where key = 'page_location') as page_location,
  countif(event_name = 'page_view') as page_view,
  countif(event_name = 'session_start') as session

FROM
  `analytics_table.events_*`  -- 自身が参照したいテーブルを選択

WHERE
  _TABLE_SUFFIX BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'  -- 日付指定
AND
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') LIKE 'https://qiita.com/'  -- 取得したいページのURLを指定(ディレクトリごとに取得することも可能)

GROUP BY 
  page_location  -- URLごとに数値をまとめる

ORDER BY
  page_view desc  -- ページビュー数で降順

LIMIT 100
```

記事ごとに情報を取りたい場合に使用しました。
page_locationを指定し、数値を取得した記事を抜粋し、event_nameの"page_view"と"session_start"をページビュー数・セッション数として取得しています。

<details><summary><b>出力結果(数値はデタラメです)</b></summary>

| 行 | page_location | page_view | session |
|:-:|:-:|:-:|:-:|
| 1 | https://qiita.com/ | 146723 | 14230 |
| 2 | https://qiita.com/y-kazawa | 1456 | 1398 |
| 3 | https://qiita.com/y-kazawa/items/01b2983606996c8987dc| 237 | 220 |
</details>

***

#### ②日別でページビュー・セッション数を取得

```sql
SELECT
  date(timestamp_micros(event_timestamp),""Asia/Tokyo"") as event_date,  -- DATE関数でタイムスタンプを日別に集計
  countif(event_name = 'page_view') as page_view,
  countif(event_name = 'session_start') as session

FROM
  `analytics_table.events_*`  -- 自身が参照したいテーブルを選択

WHERE
  _TABLE_SUFFIX BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'  -- 日付指定
AND
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') LIKE 'https://qiita.com/'  -- 特定のページから取得したい場合はURLを指定(ディレクトリごとに取得することも可能)

GROUP BY 
  event_date  -- 日付ごとに数値をまとめる

ORDER BY
  page_view desc  -- ページビュー数で降順

LIMIT 100
```

①同様page_locationを指定すれば特定ページの日別の数値が参照できます。
event_timestampに入っている情報をdate関数で日付化し、event_dateとして集計しています。

<details><summary><b>出力結果(数値はデタラメです)</b></summary>

| 行 | event_date | page_view | session |
|:-:|:-:|:-:|:-:|
| 1 | 2022-12-09 | 36497 | 29569 |
| 2 | 2022-12-10 | 30796 | 23580 |
| 3 | 2022-12-11| 30245 | 23368 |
</details>

***

#### ③流入元ごとにセッション数・アクセス数を取得

```sql
SELECT
  (select value.string_value from unnest(event_params) where key = 'page_title') as page_title,
  (select value.string_value from unnest(event_params) where key = 'page_location') as page_location,
  traffic_source.name as source_name,
  countif(event_name = 'page_view') as page_view,
  countif(event_name = 'session_start') as session

FROM
  `analytics_table.events_*`  -- 自身が参照したいテーブルを選択

WHERE
  _TABLE_SUFFIX BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'  -- 日付指定
AND
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') LIKE 'https://qiita.com/'  -- 特定のページから取得したい場合はURLを指定(ディレクトリごとに取得することも可能)

GROUP BY 
  page_title,          -- タイトルごとに数値をまとめる
  page_location,       -- URLごとに数値をまとめる
  traffic_source.name  -- 流入元ごとに数値をまとめる

ORDER BY
  page_view desc  -- ページビュー数で降順

LIMIT 100
```
流入元ごとのアクセス数を取得するために使用しました。

流入元に関する情報は"traffic_source"というフィールドに格納されているので、
event_paramsからタイトル、URLを取得し、流入元をtraffic_sourceから取得しています。

<details><summary><b>出力結果(数値はデタラメです)</b></summary>

| 行 | page_title | page_location | source_name | page_view | session |
|:-:|:-:|:-:|:-:|:-:|:-:|
| 1 | Qiita | https://qiita.com/ | (organic) | 146723 | 14230 |
| 2 | Qiita | https://qiita.com/ | (direct) | 1456 | 1398 |
| 3 | マイページ｜Qiita | https://qiita.com/y-kazawa/ | apppush | 600 | 489 |
| 4 | マイページ｜Qiita | https://qiita.com/y-kazawa/ | (direct) | 237 | 220 |
</details>

***

#### ④デバイス(デバイス / OS)ごとにセッション数・アクセス数を取得

```sql
SELECT
  (select value.string_value from unnest(event_params) where key = 'page_title') as page_title,
  (select value.string_value from unnest(event_params) where key = 'page_location') as page_location,
  -- device.category,          -- デバイスだけ取得すればよいのであればこちらを使用
  -- device.operating_system,  -- OSだけ取得すればよいのであればこちらを使用
  concat(device.category, ""/"", device.operating_system) as device_source,  -- デバイスとOSを結び付けて表示
  countif(event_name = 'session_start') as number_of_sessions,
  countif(event_name = 'page_view') as page_view

FROM
  `analytics_table.events_*`  -- 自身が参照したいテーブルを選択

WHERE
  _TABLE_SUFFIX BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'  -- 日付指定
AND
  (select value.string_value from unnest(event_params) where key = 'page_location') like 'https://qiita.com/'  -- 特定のページから取得したい場合はURLを指定(ディレクトリごとに取得することも可能)


GROUP BY 
  page_title,          -- タイトルごとに数値をまとめる
  page_location,       -- URLごとに数値をまとめる
  device_source        -- デバイスごとにまとめる

ORDER BY
  page_view desc  -- ページビュー数で降順

LIMIT 100
```

deviceに格納されるデバイスの情報を用いてデバイスごとに数値を取得するときに使用しました。
device.categoryが良く使うかと思いますが、device.operating_systemなどを用いてOSごとに取得することも可能です。

## まとめ

今回は初めて触ったBigQueryについて書いてみました。
今回はページビュー数やセッション数など、アクセスに絞った内容がメインでしたが、今後は回遊率やコンバージョンなど、より後ろの工程の数値も取得できるよう勉強していきたいなという気持ちです。

メモ的な要素が若干強かったかもですが、参考にあれば幸いです。
ここまでお読みいただきありがとうございました！