GA4/Firebaseのログをフラット化する汎用クエリ

GA4（Firebase）のログを扱う際、ネストされているイベントパラメータやユーザープロパティをフラット化しないと使いにくい。ところが格納されているパラメータやプロパティは決まっているわけではないため、通常はそれをハードコーディングで指定することが多い。つまり使用しているパラメータやプロパティに応じてその都度クエリを手動作成することになる。

しかしそれでは面倒なので、どんなイベントパラメータやユーザープロパティを使っていても、それがどんな型であっても、オールマイティにフラット化するクエリを作る。存在するパラメータやプロパティに基づいて動的にクエリを作って実行する。このクエリひとつあればどんなケースにも対応できる、汎用的なものである。

イベントパラメータ（ユーザープロパティ）名を使った動的クエリ

フラット化の詳細はこちらを参照。

たとえばネストされたイベントパラメータga_session_id（整数型）とpage_location（文字列型）をフラット化するクエリは

select
  user_pseudo_id,
  event_name,
  (select p.value.int_value from unnest(event_params) p where p.key = "ga_session_id") ga_session_id,
  (select p.value.string_value from unnest(event_params) p where p.key = "page_location") page_location
from `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`;

だが、この中で動的になるのは以下の部分

(select p.value.【動的：型】 from unnest(event_params) p where p.key = "【動的：パラメータ名】") 【動的：パラメータ名】,

ここに入る値は

• ga_session_idの場合、p.value.int_valueと"ga_session_id"
• page_locationの場合、p.value.string_valueと"page_location"

である。なおBigQueryではマルチバイト文字を列名にできないため、この列名の部分に日本語パラメータ名を入れるとエラーになる。動的に実行する場合はパラメータ名をそのまま列名にすることになるため、パラメータ名に日本語を入れると不都合である。このような動的処理をすることを踏まえるとパラメータ名には日本語を使わないほうがいい。

イベントパラメータではなくユーザープロパティの場合も同様にできる。つまり

1. イベントパラメータ（ユーザープロパティ）名と型の一覧を取得し、ループで文字列として代入し、SQL文を生成する
2. そのSQL文をクエリとして実行する

ことが求められる。

同じイベントパラメータに複数の型が混在する対策

変数の型によって格納されるカラムは異なる。

変数の型	列名
string	event_params.value.string_value
int64	event_params.value.int_value
float64	event_params.value.double_value

（タイムスタンプやdateなど、これ以外の型はイベントパラメータでは扱われない）

同じパラメータに複数の型が混在するケースがある。例えば16進数として生成された値では、たまたま数字だけで構成された値は整数（int64）　になるし、A-Fの文字が入ると文字列（string）になる。それぞれp.value.int_valueとp.value.string_valueという別の列に格納される。つまりそれぞれの列から値を取得することになる。

さらに出力を「1パラメータ＝1列」として格納するため型を合わせる（キャスト）必要がある。

select
  user_pseudo_id,
  event_name,
  (select
    case
      when p.value.string_value is not null then safe_cast(p.value.string_value as string)
      when p.value.int_value is not null then safe_cast(p.value.int_value as string)
      when p.value.double_value is not null then safe_cast(p.value.double_value as string)
      else null
    end
  from unnest(event_params) p where p.key = "param1") param1
from `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`;

ところがどの型にキャストすればいいのかが問題になるが、

• string型が1行でも含まれていれば（p.value.string_valueに一つでも値が含まれていれば）string型
• stringを含まずint64とfloat64が混在している場合（p.value.int_valueとp.value.double_valueの両方に値があれば）は値を損なわないようにするためnumeric型
• float64型のみであれば（p.value.double_valueにしか値がなければ）float64型
• int64型のみであれば（p.value.int_valueにしか値がなければ）int64型

となる。これを自動判定するためには、各パラメータについてstring_value, int_value, double_valueにどれだけ値が入っているかをカウントする必要がある。

なおこのクエリの形式になるとp.value.***_valueの部分は動的ではなくなり、動的部分はパラメータ名とキャスト先の型のみとなる。

カウントした上でキャスト先の型を分類するクエリは

with t1 as (
  select
    p.key key,
    sum(case when p.value.string_value is not null then 1 else 0 end) cnt_string,
    sum(case when p.value.int_value is not null then 1 else 0 end) cnt_int64,
    sum(case when p.value.double_value is not null then 1 else 0 end) cnt_float64
  from `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, unnest(event_params) p
  group by 1
)
select
  key,
  case
    when cnt_string > 0 then 'string'
    when cnt_int64 > 0 and cnt_float64 > 0 then 'numeric'
    when cnt_int64 > 0 then 'int64'
    when cnt_float64 > 0 then 'float64'
    else 'string'
  end type
from t1;

これをクエリの動的部分に代入する。

SQL文とループ

パラメータの種類だけループをするわけだが、ループはこのような構造になる。

select
  user_pseudo_id,
  event_name,
  -- ループ1開始 --
  (select
    case
      when p.value.string_value is not null then safe_cast(p.value.string_value as 【動的：type】)
      when p.value.int_value is not null then safe_cast(p.value.int_value as 【動的：type】)
      when p.value.double_value is not null then safe_cast(p.value.double_value as 【動的：type】)
      else null
    end
  from unnest(event_params) p where p.key = "【動的：key】") 【動的：key】,
  -- ループ1終了 --
  -- ループ2開始 --
  (select
    case
      when p.value.string_value is not null then safe_cast(p.value.string_value as 【動的：type】)
      when p.value.int_value is not null then safe_cast(p.value.int_value as 【動的：type】)
      when p.value.double_value is not null then safe_cast(p.value.double_value as 【動的：type】)
      else null
    end
  from unnest(event_params) p where p.key = "【動的：key】") 【動的：key】,
  -- ループ2終了 --
  event_timestamp,
    :
from `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`;

ループの単位は

  (select
    case
      when p.value.string_value is not null then safe_cast(p.value.string_value as 【動的：type】)
      when p.value.int_value is not null then safe_cast(p.value.int_value as 【動的：type】)
      when p.value.double_value is not null then safe_cast(p.value.double_value as 【動的：type】)
      else null
    end
  from unnest(event_params) p where p.key = "【動的：key】") 【動的：key】,

これを動的に生成する。そこで使うのがstring_agg()関数である。レコード（サブクエリ結果）を文字列としてくっつける関数である。

select
  string_agg(
    '(select case when p.value.string_value is not null then safe_cast(p.value.string_value as '
    || type 
    || ') when p.value.int_value is not null then safe_cast(p.value.int_value as '
    || type 
    || ') when p.value.double_value is not null then safe_cast(p.value.double_value as ' 
    || type 
    || ') else null end from unnest(event_params) p where p.key = "'
    || key
    || '") '
    || key
  order by key
  )
from t2;

t2というのがkey（パラメータ名）とtype（キャスト先の型）を含むテーブルになる。

まとめると

with t1 as (
  select
    p.key key,
    sum(case when p.value.string_value is not null then 1 else 0 end) cnt_string,
    sum(case when p.value.int_value is not null then 1 else 0 end) cnt_int64,
    sum(case when p.value.double_value is not null then 1 else 0 end) cnt_float64
  from `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, unnest(event_params) p
  group by 1
), t2 as (
  select
    key,
    case
      when cnt_string > 0 then 'string'
      when cnt_int64 > 0 and cnt_float64 > 0 then 'numeric'
      when cnt_int64 > 0 then 'int64'
      when cnt_float64 > 0 then 'float64'
      else 'string'
    end type
  from t1
)
select
  string_agg(
    '(select case when p.value.string_value is not null then safe_cast(p.value.string_value as '
    || type 
    || ') when p.value.int_value is not null then safe_cast(p.value.int_value as '
    || type 
    || ') when p.value.double_value is not null then safe_cast(p.value.double_value as ' 
    || type 
    || ') else null end from unnest(event_params) p where p.key = "'
    || key
    || '") '
    || key
  order by key
)
from t2;

SQL文中の動的部分の完成

上で作った動的部分を代入して以下のようなクエリを作る。

select
  user_pseudo_id,
  event_name,
  【イベントパラメータのstring_agg()の結果】,
  【ユーザープロパティのstring_agg()の結果】,
  event_timestamp,
    : -- ネストされていない、生ログの列をそのままselectすればいい列
from `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`;

動的に生成した文字列をクエリとして実行する

BigQuery Scriptingのexecute immediate format()を使うと動的部分を変数として代入し、代入した結果得られる文字列をクエリとして実行できる。たとえば

declare str_columns string;
set str_columns = (select string_agg(...) from ...); -- 文字列を返す

execute immediate format("""
select
  %s
from t3
""", str_columns);

• %sの部分に変数str_columnsが値に展開されて代入される
• str_columnsに列名を指定する文字列を入れておけばいい
• その文字列はstring_agg()を使って生成する
• ただしパラメータ名にマルチバイト文字を含むとエラーになるので、バッククォートで囲むことでエスケープする

execute immediate format()構文や変数を使うためにBigQuery Scriptingを使う。イベントパラメータの列名をの部分の文字列をstr_ep_columns、ユーザープロパティの列名の文字列をstr_up_columnsとする。イベントパラメータには必ず値が入る（デフォルトで取得されるパラメータがある）が、ユーザープロパティはタグで値をセットしない限り値が入らない。その場合はstr_up_columnsはNULLになる。またパラメータと違ってプロパティにはuser_properties.value.set_timestamp_microsという列（int64型）が含まれる。

declare str_ep_columns string;
declare str_up_columns string;
declare str_dynamic_columns string;

set str_ep_columns = (
  with t1 as (
    select
      p.key key,
      sum(case when p.value.string_value is not null then 1 else 0 end) cnt_string,
      sum(case when p.value.int_value is not null then 1 else 0 end) cnt_int64,
      sum(case when p.value.double_value is not null then 1 else 0 end) cnt_float64
    from `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, unnest(event_params) p
    group by 1
  ), t2 as (
    select
      key,
      case
        when cnt_string > 0 then 'string'
        when cnt_int64 > 0 and cnt_float64 > 0 then 'numeric'
        when cnt_int64 > 0 then 'int64'
        when cnt_float64 > 0 then 'float64'
        else 'string'
      end type
    from t1
  )
  select
    string_agg(
      '(select case when p.value.string_value is not null then safe_cast(p.value.string_value as '
      || type 
      || ') when p.value.int_value is not null then safe_cast(p.value.int_value as '
      || type 
      || ') when p.value.float_value is not null then safe_cast(p.value.float_value as ' 
      || type 
      || ') when p.value.double_value is not null then safe_cast(p.value.double_value as ' 
      || type 
      || ') else null end from unnest(event_params) p where p.key = "'
      || key
      || '") `e_'
      || key || '`'
    order by key
  )
  from t2
);

set str_up_columns = (
  with t1 as (
    select
      p.key key,
      sum(case when p.value.string_value is not null then 1 else 0 end) cnt_string,
      sum(case when p.value.int_value is not null or p.value.set_timestamp_micros is not null then 1 else 0 end) cnt_int64,
      sum(case when p.value.double_value is not null then 1 else 0 end) cnt_float64
    from `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, unnest(user_properties) p
    group by 1
  ), t2 as (
    select
      key,
      case
        when cnt_string > 0 then 'string'
        when cnt_int64 > 0 and cnt_float64 > 0 then 'numeric'
        when cnt_int64 > 0 then 'int64'
        when cnt_float64 > 0 then 'float64'
        else 'string'
      end type
    from t1
  )
  select
    string_agg(
      '(select case when p.value.string_value is not null then safe_cast(p.value.string_value as '
      || type 
      || ') when p.value.int_value is not null then safe_cast(p.value.int_value as '
      || type 
      || ') when p.value.double_value is not null then safe_cast(p.value.double_value as ' 
      || type 
      || ') when p.value.set_timestamp_micros is not null then safe_cast(p.value.set_timestamp_micros as '
      || type 
      || ') else null end from unnest(user_properties) p where p.key = "'
      || key
      || '") `u_'
      || key || '`'
    order by key
  )
  from t2
);

if length(str_up_columns) > 0 then
  set str_dynamic_columns = str_ep_columns || ', ' || str_up_columns;
else
  set str_dynamic_columns = str_ep_columns;
end if;

execute immediate format("""
create or replace table `my-project.my_dataset.log_flatten` as
with t1 as (
  select
    user_pseudo_id,
    min(timestamp_micros(event_timestamp)) over(partition by user_pseudo_id) first_open_timestamp,
    timestamp_micros(event_timestamp) event_timestamp,
    event_name,
    %s,
    platform,
    app_info.install_source
  from `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
), t2 as (
  select *, timestamp_diff(event_timestamp, first_open_timestamp, second) seconds_since_first_open from t1
)
select * from t2
""", str_dynamic_columns);

このようなクエリでどんなイベントパラメータやユーザープロパティが含まれていてもフラット化できる。改めていうが、これを実行する際にはイベントパラメータやユーザープロパティの名前にマルチバイト文字（日本語など）を含むとエラーになるので、これを実行したい場合はパラメータやプロパティの名前に日本語を入れてはならない。

[公開日：2022/1/27]

GCP/Firebase の記事一覧

marketechlabo
 
10 Users
21 Pockets

Googleアナリティクス4のBigQueryエクスポート仕様 | marketechlabo
https://www.marketechlabo.com/ga4-bigquery-export-spec/

marketechlabo
 
2 Users
2 Pockets

機械学習用のサーバの環境構築メモ（特にGCEやEC2などクラウドインスタンス）
https://www.marketechlabo.com/linux-mac-server-settings/

marketechlabo
 
2 Users
7 Pockets

GCPのIAMポリシー | marketechlabo
https://www.marketechlabo.com/gcp-iam-policy/

marketechlabo
 
17 Users
30 Pockets

Googleアナリティクス4のデータをBigQuery出力する | marketechlabo
https://www.marketechlabo.com/ga-app-web-property-to-bigquery/

marketechlabo
 
7 Users
13 Pockets

Google Cloud Functions / Cloud Functions for Firebaseの微妙な使い勝手 | marke...
https://www.marketechlabo.com/google-cloud-functions/