【BigQuery】Google Analytics のデータ分析で使用するクエリ一覧

はじめに

BigQuery にエクスポートした Google Analytics のデータを使う準備で、event_params カラムの詳細を調査しました。
この記事ではその調査に使ったクエリをまとめました。

Google Analytics のデータ構造について

Google Analytics のデータ構造は、ドキュメントがあります。

データ型 REPEATED, RECORD とは

この記事で扱う event_params というカラムは REPEATED というデータ型です。（他に user_properties カラムがあります）
一般的なカラムでは、１カラムにつき値は1つですが、REPEATED のカラムでは１カラムに配列データが格納されている、というとイメージしやすいと思います。

また、event_params では RECORD というデータ型も使われます。
構造体のように、複数のカラムをまとめて格納します。

画像の例だと、key, value というカラムを持った、データ型 RECORD の params が存在し、1行に ["key1", 100], ["key2", 200]という2つのデータが入っています。

event_params カラムについて

event_params は key と value の2つのカラムを持っています。

key に発生したイベントに関連したパラメータ名、 value にパラメータの値が格納されます。
value はデータ型ごとにカラムが用意されており、value の値が文字列なら string_value、整数値なら int_value に格納されます。

取得できるパラメータやその値については、Google Analytics のカスタムイベントなどの設定によるため、実データから調査する必要があります。

以降の項目で、key と value の値を調べるために使ったクエリを記載します。

key 一覧を出力する

key の一覧を出力し、取得できるパラメータを確認します。

SELECT
  e.key
FROM analytics_<Google Analytics の ID>.events_<日付>, UNNEST(event_params) as e
GROUP BY e.key

event_params そのままだと REPEATED のまま（1レコードに複数データが存在する状態）なので、UNNEST(event_params) で展開し、e.key で key　のみを出力します。

key 毎の value のデータ型を調べる

各 key とそれに対応した value のデータ型を調べます。
いろいろやってみた結果、↓の記事で記載されていた方法がとてもわかりやすかったのでこちらを使用させていただきました。

SELECT
  e.key,
  SUM(case when e.value.string_value is not null then 1 else 0 end) type_str,
  SUM(case when e.value.int_value    is not null then 1 else 0 end) type_int,
  SUM(case when e.value.float_value    is not null then 1 else 0 end) type_float,
  SUM(case when e.value.double_value is not null then 1 else 0 end) type_double
FROM analytics_<Google Analytics の ID>.events_<日付>, UNNEST(event_params) as e
GROUP BY e.key

case when <条件式> then A else B end は、条件式に当てはまればA、そうでなければBを返します。
value の各データ型カラムに値が入っているかを条件式としており、データ型だけではなくプロパティの出現回数も確認することができます。

特定の key の value の値を調べる

この例では ga_session_id プロパティの値を調べます。

WITH ga_session_id_data AS(
  SELECT 
    (SELECT 
      ep.value.int_value FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id'    ) ga_session_id 
  FROM analytics_<Google Analytics の ID>.events_<日付>)
SELECT 
  ga_session_id
FROM ga_session_id_data
WHERE ga_session_id is not null;

WITH <テーブル名> AS() を使って、確認したい key の value だけを格納した一時テーブルを作成します。
以降の SELECT で値が null でないレコードを取得します。

一時テーブルを作らずともできそうですが、以下のクエリだと WHERE 句に指定されたカラムがないというエラーになります。

SELECT 
  (SELECT 
      ep.value.int_value FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id'    ) ga_session_id 
FROM analytics_<Google Analytics の ID>.events_<日付>
WHERE ga_session_id is not null;

参考