脱・カスタムレポートのためのSQLスニペット

:::note warn
この記事はGoogle Analytics 4ではなく、Universal Analyticsを対象としています。
:::

## 概要
Google Analytics 360 + BigQueryエクスポートを利用できる環境であれば、以下の理由からカスタムレポートを利用せずSQLでデータ抽出することをオススメします。

- カスタムレポートはビューのヒット数やURL種別が多い場合だったり、複雑なフィルタ条件を掛けた場合に処理が重くなる 
- SQLでコード化することでデータ抽出の再現性が高まる
- SQLでコード化することでレビューが可能になる
- SQL側で文字列処理等が可能になる。
- BigQueryとデータポータルを組み合わせることでアドホックな分析が可能になる
- クエリ発行と同時にGCP上でデータマートが作成できる

というわけで、カスタムレポートの脱却にあたって役に立ちそうなSQLスニペットをメモしておきます。

## SQL文の大枠
まずは大枠として以下のクエリをエディタに貼り付けます。

from_date/to_dateにはそれぞれ開始日/終了日を[スクリプト](https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting)として代入し、データの抽出範囲（[日付別シャード化テーブル](https://cloud.google.com/bigquery/docs/creating-column-partitions?hl=ja)の範囲）を設定します。
※期間の長さやビューのヒット数(BQテーブルの大きさ)に比例して費用も増えていきますのでご注意ください。

```SQL
DECLARE
  from_date,
  to_date string;
SET
  from_date = "20210101";
SET
  to_date = "20210101";
SELECT
  date
FROM
  `projectName.datasetName.ga_sessions_*`,
  UNNEST(hits) AS hits
WHERE
  _table_suffix BETWEEN from_date
  AND to_date
```

この枠の中で、SELECT句とWHERE句にて必要なディメンション・メトリクス・フィルターを定義していきます。


カスタムレポートにおける抽出項目とSQL文の対応関係は以下のようなイメージです。

- **ディメンション**
    -  `SELECT`
        -  必要なディメンションを指定
- **メトリクス**
    - ユーザー数
        - SELECTした`fullVisitorId`の数を集計
    - ページビュー数
        - `WHERE`で `hits.type = "PAGE"`を指定
    - イベント数
        - `WHERE`で `hits.type = "EVENT"`を指定
- **フィルター**
    - `WHERE`
        - `REGEXP_CONTAINS()`で適宜フィルター

意外とシンプルではないでしょうか。これ以外にも集計関数は必要になりますが、慣れてしまえばカスタムレポートよりもSQLの方が確実に楽だと思います。


## 日付・時刻データの処理

データ抽出後の集計や可視化のための日付・時刻データの型変換です。
日付を扱う際には UTC/協定時間時・JST/日本標準時の「タイムゾーン」とSTRING・DATE・DATETIME・TIMESTAMPの「型」を意識する必要があります。

### dateフィールド

Universal AnalyticsのdateフィールドはSTRING型の`YYYYMMDD`となっているため[PARSE_DATE()](https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#parse_date)でDATE型に変換しておくと後々便利です。

```SQL
SELECT
  PARSE_DATE("%Y%m%d",date) AS parsed_date
```

### hit.timeフィールドの変換
各ヒットにおけるhis.timeはvisitStartTime(unixtime)からの相対的なミリ秒となります。そのため単位を秒に変換した上でvisitStartTimeと足し合わせて、[TIMESTAMP_SECONDS()](https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_seconds)でunixtimeからTIMESTAMP型に変換します。

```SQL
SELECT
  TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)) AS hit_timestamp
```

### timestampのタイムゾーン変更とそれぞれの返却結果

timestampはそのままではタイムゾーンがUTCになっているため、日本時間で処理する場合にはJSTに変換する必要があります。例えば以下のような形で変換することが可能です。

```SQL
SELECT
  DATETIME(hit_timestamp,'Asia/Tokyo') AS time_1,
  TIMESTAMP_ADD(hit_timestamp, INTERVAL 9 HOUR) AS time_2,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S',hit_timestamp,'Asia/Tokyo') as time_3
```
型を変更する必要がなければ[TIMESTAMP_ADD()](https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_add)、そうでなければ[DATETIME()](https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions?hl=ja#datetime)を選ぶと良いかもしれません。

それぞれtime1がDATETIME型、time2がTIMESTAMP型、time3がSTRING型として返却されることになります。

|	time_1|time_2|time_3|
|---|---|---|
|2021-09-21T23:00:54|2021-09-21 23:00:54 UTC|2021-09-21 23:00:54|

### TIMESTAMP型の操作

[TIMESTAMP型](https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions)の操作の一例です。EXTRACT()ではINTEGERに変換されます。

| 操作                      | 関数                                    | 出力                           |
|---------------------------|-----------------------------------------|--------------------------------|
| 現在時刻をTIMESTAMPで抽出 | CURRENT_TIMESTAMP()                     | 2021-09-21 14:23:35.550942 UTC |
| 日を抽出                  | EXTRACT(DAY from CURRENT_TIMESTAMP())   |                             21 |
| 週を抽出                  | EXTRACT(WEEK from CURRENT_TIMESTAMP())  |                             38 |
| 月を抽出                  | EXTRACT(MONTH from CURRENT_TIMESTAMP()) |                              9 |
| 年を抽出                  | EXTRACT(YEAR from CURRENT_TIMESTAMP())  |                           2021 |

### DATETIME型の操作

[DATETIME型](https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions?hl=ja)の操作の一例です。EXTRACT()ではINTEGERに変換されます。

| 操作                     | 関数                                   | 出力                |
|--------------------------|----------------------------------------|---------------------|
| 現在時刻をDATETIMEで抽出 | CURRENT_DATETIME()                     | 2021-09-22 13:17:31 |
| 日を抽出                 | EXTRACT(DAY from CURRENT_DATETIME())   |                  22 |
| 週を抽出                 | EXTRACT(WEEK from CURRENT_DATETIME())  |                  38 |
| 月を抽出                 | EXTRACT(MONTH from CURRENT_DATETIME()) |                   9 |
| 年を抽出                 | EXTRACT(YEAR from CURRENT_DATETIME())  |                2021 |

### DATE型の操作

[DATE型](https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions)の操作の一例です。DATETRANC()ではDATE型を維持します。

| 操作                 | 関数                                   | 出力       |
|----------------------|----------------------------------------|------------|
| 現在時刻をDATEで抽出 | CURRENT_DATE()                         | 2021-09-22 |
| 日を抽出             | DATE_TRUNC(DATE(CURRENT_DATE()),DAY)   | 2021-09-22 |
| 週を抽出             | DATE_TRUNC(DATE(CURRENT_DATE()),WEEK)  | 2021-09-19 |
| 月を抽出             | DATE_TRUNC(DATE(CURRENT_DATE()),MONTH) | 2021-09-01 |
| 年を抽出             | DATE_TRUNC(DATE(CURRENT_DATE()),YEAR)  | 2021-01-01 |

## URL文字列の処理

ディメンションとして利用頻度の多いURL文字列の処理ですが、以下のようなクエリを使うことが多いです。

### URLパラメータの抽出

URLパラメータから任意のキーを拾う場合は以下のようにします。

```SQL
SELECT
  REGEXP_EXTRACT(url, r"^.*key1=([^&]*)&?.*") AS key1,
  REGEXP_EXTRACT(url, r"^.*key2=([^&]*)&?.*") AS key2,
  REGEXP_EXTRACT(url, r"^.*key3=([^&]*)&?.*") AS key3,
  REGEXP_EXTRACT(url, r"^.*key4=([^&]*)&?.*") AS key4,
  REGEXP_EXTRACT(url, r"^.*key5=([^&]*)&?.*") AS key5
FROM (
  SELECT
    "key1=val1&key2=val2&key3=val3&key4=val4&key5=val5" AS url )
```

#### 出力

| key1 | key2 | key3 | key4 | key5 |
|------|------|------|------|------|
| val1 | val2 | val3 | val4 | val5 |

### ホスト名を抽出

[NET()](https://cloud.google.com/bigquery/docs/reference/standard-sql/net_functions)利用するとURL文字列からホスト名だけを抽出することができます。

```SQL
NET.HOST(value) 
```

### URLパラメータ除外

[SPLIT()](https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#split)を利用して`?`の区切り文字で分割することで、パラメータを除外する形でURL文字列を取得できます。
例えばカスタムディメンションにフルURLをセットしていて、その文字列からパラメータを除外する場合などに役立ちます。

```SQL
SPLIT(value, '?')[OFFSET(0)] AS pagePath
```


## 結果の保存

クエリの実行と同時に、返却された結果を別テーブルやオブジェクトストレージに保存することが可能です。

### クエリ結果をBigQueryテーブルとして保存する

[データ定義言語（DDL）](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language)を利用することでBigQueryリソースを作成および変更できます。クエリ結果をデータマートとして利用したい場合に便利です。

```SQL
CREATE OR REPLACE TABLE
  `projectName.datasetName.tableName`AS (
    SELECT
     *
    FROM
     hoge
)
```

### クエリ結果をCloudStorageに保存する

[EXPORT DATAステートメント](https://cloud.google.com/bigquery/docs/reference/standard-sql/other-statements)を利用することでオブジェクトストレージへの書き出しも可能になります。
宛先バケットを指定して任意のフォーマットでテーブルをオブジェクトストレージに出力できます。

```SQL
EXPORT DATA
  OPTIONS (uri="gs://mybucket/myfile/*",
    format=CSV) AS (
   SELECT
    *
   FROM
    hoge
)
```

## おわりに
カスタムレポートでできることはSQLでも抽出可能ですので、特に作業速度の面でBigQuery利用を勧めたいです。またカスタムレポートに限らずDataStudioでGAデータを可視化する際にも、DataStudio側で前処理せず可能な限りデータマート側で処理してしまったほうがシンプルです。

カスタムレポート脱却のためにSQLを書いていると、気軽にデータマート構築だったりGA4データの抽出もできるようになる、というメリットもあったりします。
BigQueryの利用を躊躇されているWebアナリストの方もぜひお試しください。

## 参考URL
https://support.google.com/analytics/answer/3437719?hl=ja