如何在 Firebase Analytics 原始数据中计算会话和会话持续时间? [英] How to calculate Session and Session duration in Firebase Analytics raw data?

问题描述

如何在与 BigQuery 相关联的 Firebase 分析原始数据中计算会话持续时间?

我已经使用以下博客通过对嵌套在每条记录中的事件使用 flatten 命令来计算用户数，但我想知道如何继续计算 Session 和会话时长(按国家/地区和时间).

(我配置了很多应用程序，但如果您能帮助我使用 SQL 查询来计算会话持续时间和会话，那将会有很大帮助)

How to calculate Session Duration in Firebase analytics raw data which is linked to BigQuery?

I have used the following blog to calculate the users by using the flatten command for the events which are nested within each record, but I would like to know how to proceed with in calculating the Session and Session duration by country and time.

(I have many apps configured, but if you could help me with the SQL query for calculating the session duration and session, It would be of immense help)

Google Blog on using Firebase and big query

解决方案

First you need to define a session - in the following query I'm going to break a session whenever a user is inactive for more than 20 minutes.

Now, to find all sessions with SQL you can use a trick described at https://blog.modeanalytics.com/finding-user-sessions-sql/.

The following query finds all sessions and their lengths:

#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

这篇关于如何在 Firebase Analytics 原始数据中计算会话和会话持续时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！