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

3 回答

• 0

  首先，您需要定义一个会话 - 在以下查询中，每当用户处于非活动状态超过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
  

  

  回复于 2024-05-03T00:20:51+08:00
• 14

  如您所知，Google已经更改了BigQuery firebase数据库的架构：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

  样本结果：
  

  回复于 2024-05-03T00:20:51+08:00
• 0

  使用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)
  

  回复于 2024-05-03T00:20:51+08:00