如何计算Firebase Analytics原始数据中的会话和会话持续时间？

问题描述

如何在Firebase analytics 原始数据中计算会话持续时间，并链接到BigQuery？

使用以下博客计算用户通过使用flatten命令嵌套在每个记录中的事件，但我想知道如何继续计算会话和会话持续时间按国家和时间。 （我有很多应用程序配置，但如果你可以帮助我的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原始数据中的会话和会话持续时间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！