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

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

概要

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

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

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

SQL文の大枠

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

from_date/to_dateにはそれぞれ開始日/終了日をスクリプトとして代入し、データの抽出範囲（日付別シャード化テーブルの範囲）を設定します。
※期間の長さやビューのヒット数(BQテーブルの大きさ)に比例して費用も増えていきますのでご注意ください。

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()でDATE型に変換しておくと後々便利です。

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

hit.timeフィールドの変換

各ヒットにおけるhis.timeはvisitStartTime(unixtime)からの相対的なミリ秒となります。そのため単位を秒に変換した上でvisitStartTimeと足し合わせて、TIMESTAMP_SECONDS()でunixtimeからTIMESTAMP型に変換します。

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

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

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

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()、そうでなければ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型の操作の一例です。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型の操作の一例です。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型の操作の一例です。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パラメータから任意のキーを拾う場合は以下のようにします。

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()利用するとURL文字列からホスト名だけを抽出することができます。

NET.HOST(value) 

URLパラメータ除外

SPLIT()を利用して?の区切り文字で分割することで、パラメータを除外する形でURL文字列を取得できます。
例えばカスタムディメンションにフルURLをセットしていて、その文字列からパラメータを除外する場合などに役立ちます。

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

結果の保存

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

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

データ定義言語（DDL）を利用することでBigQueryリソースを作成および変更できます。クエリ結果をデータマートとして利用したい場合に便利です。

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

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

EXPORT DATAステートメントを利用することでオブジェクトストレージへの書き出しも可能になります。
宛先バケットを指定して任意のフォーマットでテーブルをオブジェクトストレージに出力できます。

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

おわりに

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

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

参考URL