-- 永続関数となる（他クエリから呼び出せる）
CREATE OR REPLACE FUNCTION `project.dataset.f`()

-- 一時的な関数となる（他クエリから呼び出せないが気楽に作れる）
-- TEMPORARYな関数を含むとViewを作ることはできないので注意
CREATE TEMPORARY FUNCTION f() AS (xxx)

-- タイムスタンプを日本時間に変換する
CREATE FUNCTION
  formatJSTTimestamp(ts timestamp ) AS ( FORMAT_TIMESTAMP('%Y-%m-%d %T', ts, 'Asia/Tokyo') );

-- analyticsのtimestampはINTEGER型で入っているためTIMESTAMPに変換後にFORMATする
CREATE FUNCTION
  formatIntTimestamp(value INT64 ) AS ( formatJSTTimestamp(TIMESTAMP_MICROS(value)) );

-- 利用例
formatIntTimestamp(event_timestamp) AS event_at,

-- event_paramsやuser_propertiesなどのRECORDからスカラーサブクエリでstring_valueを取り出す
CREATE FUNCTION
  extractStringValue(event_params ANY TYPE,
    name STRING ) AS ( (
    SELECT
      value.string_value
    FROM
      UNNEST(event_params) AS x
    WHERE
      x.key = name) );

-- 利用例
extractStringValue(event_params, "firebase_screen") AS firebase_screen,

-- event_paramsやuser_propertiesなどのRECORDからスカラーサブクエリでint_valueを取り出す
CREATE FUNCTION
  extractIntValue(event_params ANY TYPE,
    name STRING ) AS ( (
    SELECT
      value.int_value
    FROM
      UNNEST(event_params) AS x
    WHERE
      x.key = name) );

`project.dataset.f`()

SELECT
  `analytics_xxxxxxxxx.formatIntTimestamp`(event_timestamp) AS event_at,
  event_name,
  `analytics_xxxxxxxxx.extractStringValue`(event_params,
    "firebase_screen") AS firebase_screen,
  `analytics_xxxxxxxxx.extractStringValue`(event_params,
    "firebase_previous_screen") AS firebase_previous_screen,
  `analytics_xxxxxxxxx.extractIntValue`(event_params,
    "ga_session_id") AS ga_session_id,
  `analytics_xxxxxxxxx.extractIntValue`(event_params,
    "ga_session_number") AS ga_session_number,
  `analytics_xxxxxxxxx.extractIntValue`(event_params,
    "engagement_time_msec") AS engagement_time_msec,
  user_id,
  user_pseudo_id,
  `analytics_xxxxxxxxx.extractStringValue`(user_properties,
    "firebase_last_notification") AS firebase_last_notification,
  `analytics_xxxxxxxxx.formatIntTimestamp`(`analytics_xxxxxxxxx.extractIntValue`(user_properties,
      "first_open_time")) AS first_open_time,
  `analytics_xxxxxxxxx.formatIntTimestamp`(user_first_touch_timestamp) AS user_first_touch_at,
  _TABLE_SUFFIX AS table_suffix,
FROM
  `project.analytics_xxxxxxxxx.events_*`
ORDER BY
  event_timestamp DESC

-- データセット`analytics_xxxxxxxxx`に`events_formatted`というViewを作った場合
-- 期間を指定してユーザーの行動履歴が一覧で把握できる
-- `event_name="screen_view"`とすれば画面遷移のみ見ることも可能
SELECT
  *
FROM
  `project.analytics_xxxxxxxxx.events_formatted`
WHERE
  table_suffix BETWEEN "20220101"
  AND "20220401"
  AND user_id="xxxx"

firebase_previous_class: STRING
firebase_screen: STRING
firebase_event_origin: STRING
ga_session_number: INT64
ga_session_id: INT64
session_engaged: INT64
engaged_session_event: INT64
firebase_previous_id: STRING
firebase_screen_id: STRING
firebase_screen_class: STRING

Query usage per day

Query usage per day: プロジェクトごとの1日あたりのクエリ使用量
Query usage per day per user: ユーザーごとの1日あたりのクエリ使用量