如何计算Firebase分析原始数据中的会话和会话持续时间？
如何计算链接到BigQuery的Firebase分析原始数据中的会话持续时间？
我使用下面的博客对每个记录中嵌套的事件使用flatten命令计算用户，但我想知道如何按国家和时间计算会话和会话持续时间。
(我配置了许多应用程序，但是如果您可以帮助我使用SQL查询来计算会话持续时间和会话，这将有很大的帮助)
回答 4
Stack Overflow用户
发布于 2017-03-02 07:12:08
首先，您需要定义会话--在下面的查询中，每当用户不活动超过20分钟时，我将中断会话。
现在，要使用SQL查找所有会话，您可以使用https://blog.modeanalytics.com/finding-user-sessions-sql/中描述的技巧。
以下查询查找所有会话及其长度：
#standardSQL
SELECT app_instance_id, sess_id, MIN(min_time) sess_start, MAX(max_time) sess_end, COUNT(*) records, MAX(sess_id) OVER(PARTITION BY app_instance_id) total_sessions,
(ROUND((MAX(max_time)-MIN(min_time))/(1000*1000),1)) sess_length_seconds
FROM (
SELECT *, SUM(session_start) OVER(PARTITION BY app_instance_id ORDER BY min_time) sess_id
FROM (
SELECT *, IF(
previous IS null
OR (min_time-previous)>(20*60*1000*1000), # sessions broken by this inactivity
1, 0) session_start
#https://blog.modeanalytics.com/finding-user-sessions-sql/
FROM (
SELECT *, LAG(max_time, 1) OVER(PARTITION BY app_instance_id ORDER BY max_time) previous
FROM (
SELECT user_dim.app_info.app_instance_id
, (SELECT MIN(timestamp_micros) FROM UNNEST(event_dim)) min_time
, (SELECT MAX(timestamp_micros) FROM UNNEST(event_dim)) max_time
FROM `firebase-analytics-sample-data.ios_dataset.app_events_20160601`
)
)
)
)
GROUP BY 1, 2
ORDER BY 1, 2
Stack Overflow用户
发布于 2019-03-12 08:19:41
使用BigQuery中的Firebase新模式，我发现@Maziar的答案对我没有用，但我不知道为什么。相反，我使用以下方法来计算它，其中会话被定义为用户使用应用程序至少10秒，如果用户在30分钟内不使用应用程序，会话就会停止。它提供了会话总数和会话长度(以分钟为单位)，它基于以下查询：https://modeanalytics.com/modeanalytics/reports/5e7d902f82de/queries/2cf4af47dba4
SELECT COUNT(*) AS sessions,
AVG(length) AS average_session_length
FROM (
SELECT global_session_id,
(MAX(event_timestamp) - MIN(event_timestamp))/(60 * 1000 * 1000) AS length
FROM (
SELECT user_pseudo_id,
event_timestamp,
SUM(is_new_session) OVER (ORDER BY user_pseudo_id, event_timestamp) AS global_session_id,
SUM(is_new_session) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS user_session_id
FROM (
SELECT *,
CASE WHEN event_timestamp - last_event >= (30*60*1000*1000)
OR last_event IS NULL
THEN 1 ELSE 0 END AS is_new_session
FROM (
SELECT user_pseudo_id,
event_timestamp,
LAG(event_timestamp,1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS last_event
FROM `dataset.events_2019*`
) last
) final
) session
GROUP BY 1
) agg
WHERE length >= (10/60)
Stack Overflow用户
发布于 2018-08-18 07:17:53
如您所知，Google已经更改了BigQuery防火墙数据库的架构：https://support.google.com/analytics/answer/7029846
由于@Felipe回答，新格式将更改如下：
SELECT SUM(total_sessions) AS Total_Sessions, AVG(sess_length_seconds) AS Average_Session_Duration
FROM (
SELECT user_pseudo_id, sess_id, MIN(min_time) sess_start, MAX(max_time) sess_end, COUNT(*) records,
MAX(sess_id) OVER(PARTITION BY user_pseudo_id) total_sessions,
(ROUND((MAX(max_time)-MIN(min_time))/(1000*1000),1)) sess_length_seconds
FROM (
SELECT *, SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY min_time) sess_id
FROM (
SELECT *, IF(previous IS null OR (min_time-previous) > (20*60*1000*1000), 1, 0) session_start
FROM (
SELECT *, LAG(max_time, 1) OVER(PARTITION BY user_pseudo_id ORDER BY max_time) previous
FROM (SELECT user_pseudo_id, MIN(event_timestamp) AS min_time, MAX(event_timestamp) AS max_time
FROM `dataset_name.table_name` GROUP BY user_pseudo_id)
)
)
)
GROUP BY 1, 2
ORDER BY 1, 2
)注意:根据项目信息更改dataset_name和table_name
抽样结果：
https://stackoverflow.com/questions/42546815
复制相似问题
腾讯云开发者
