firebase - BigQuery Firebase Event Logs Grouped By User Id
问题描述
Question: I want to pull a stream of Firebase event-data grouped by user id and ordered by time of occurrence for my android users. I created the two scripts below, but unfortunately in both I can't seem to get the last part correct, which is to, successfully group all the app_instance_ids first irregardless of timestamp. Should I perhaps look at using distinct user_ids instead?
Unsuccessful attempt 1:
SELECT
d.userid,
c.ev_timestamp,
c.ev_name
FROM (SELECT
user_dim.app_info.app_instance_id as userid
FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS event
WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000
GROUP BY 1) AS d
LEFT JOIN (SELECT user_dim.app_info.app_instance_id as userid,
event.timestamp_micros as ev_timestamp,
event.name as ev_name
FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS event
WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000) AS c
ON d.userid = c.userid
ORDER BY 2 ASC
LIMIT 1000;
Unsuccessful attempt 2:
SELECT
d.userid,
d.ev_timestamp,
c.ev_name
FROM (SELECT
user_dim.app_info.app_instance_id as userid,
event.timestamp_micros as ev_timestamp
FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS event
WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000
GROUP BY 1,2
ORDER BY 2 ASC) AS d
LEFT JOIN (SELECT user_dim.app_info.app_instance_id as userid,
event.timestamp_micros as ev_timestamp,
event.name as ev_name
FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS event
WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000) AS c
ON d.userid = c.userid AND d.ev_timestamp = c.ev_timestamp
#ORDER BY 2 ASC
LIMIT 1000;
Correct answer (Amod's answer converted to New Export Schema):
SELECT user_pseudo_id, event_timestamp, event_name
FROM `xxxx.analytics_xxxx.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20180630' AND '20180702'
AND user_first_touch_timestamp BETWEEN 1530453600000000 AND 1530468000000000
AND platform = "ANDROID"
ORDER BY 1,2 ASC
LIMIT 1000
解决方案
try this
SELECT user_dim.app_info.app_instance_id as userid, X.timestamp_micros,X.name as ev_name,X
FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS X
WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000
and user_dim.app_info.app_instance_id like 'C4%'
order by 1,2 ASC
or
SELECT user_dim.app_info.app_instance_id as userid, X.timestamp_micros,X.name as ev_name
FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS X
WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000
order by 1,2 ASC
推荐阅读
- r - 查找具有特定长度并包含特定字符的字符串
- powershell - 如何获取邮件联系人所属的所有组?
- android - Android admob,onAdFailedToLoad 代码怎么办 == ERROR_CODE_NO_FILL
- encoding - ldapjs 和字符串 base64 格式的问题
- java - Kotlin:继承自嵌套的 Java 类
- python - 从 Tone Analyser 的 JSON 响应中的字典列表中提取数据
- php - ACF 选项添加带有回调函数的子菜单
- devexpress - DevExpress XAF 中的自定义属性
- excel - 如何优化 Excel 中的单元格范围选择?
- django - 不要将此请求 http 保存在 django 的数据库中