ConvertFirebaseAnalyticsLog.sql

WITH SRC AS (
  SELECT
    _TABLE_SUFFIX ldate,
    *
  FROM `hoge.analytics_123456789.events_intraday_*`
--  WHERE _TABLE_SUFFIX = '2019-07-25'
),
TD_COMPAT AS (
  SELECT
    ldate,
    event_timestamp time,
    (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'tracking_code') tracking_code,
    user_id user_id,
    app_info.id app_id,
    app_info.version app_version,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'firebase_screen_class') activity,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'fragment') fragment,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'params') params,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'category') category,
    event_name action,
    event_params action_params
  FROM SRC
)
SELECT
  *
FROM TD_COMPAT
ORDER BY
  user_id,
  time