Custom query for firebase event use in google datastudio

latest-user-property.sql

SELECT 
  event_date, event_timestamp, event_name, app_info.id, user_pseudo_id,
  max(
    if(user_property.key = "user_status", user_property.value.string_value, NULL) 
  ) user_status

FROM
  (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC ) as rn 
    FROM `project-id.dataset-id.events_*`
    WHERE 
      _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
  ),
  UNNEST( user_properties ) as user_property
WHERE 
  rn=1
GROUP BY
  event_date, event_timestamp, event_name, app_info.id, user_pseudo_id

unpack-event-param.sql

SELECT 
  event_date, event_timestamp, event_name, app_info.id,
  max(
    if(event_param.key = "item_id", event_param.value.int_value, NULL) 
  ) item_id

FROM
  `project-id.dataset-id.events_*`,
  UNNEST(event_params) as event_param
WHERE 
  _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
GROUP BY
  event_date, event_timestamp, event_name, app_info.id