在这里，我可以在Firebase分析中找到平均会话时间。如何通过Bigquery提取此度量

Question

1. 去哪儿找阿夫格。火基分析中的会话持续时间度量？
2. 如何提取阿夫格。来自Bigquery的会话持续时间度量数据？

平均会话持续时间度量，这是以前在Firebase分析仪表板中可用的。但是现在，它没有在Firebase分析仪表板上使用。现在，我们只看到“每个用户的参与”。是每个用户和Avg的订婚。会话时间和时间是一样的吗？如何提取阿夫格。来自Fiebase分析的会话持续时间？如何在Bigquery中查询提取Avg。来自Firebase的会话持续时间度量。在这里输入图像描述

Answer 1

每个用户的约定与Avg不一样。会议期间。每个用户的参与度是指用户每天在应用程序中花费的时间，而不是会话。

1. 你能找到阿夫格。最新版本下Firebase Analytics中的会话持续时间。
2. 下面是一个计算avg的查询。BigQuery中的会话长度：

​

with timeline as
(
  select 
    user_pseudo_id
    , event_timestamp
    , lag(event_timestamp, 1) over (partition by user_pseudo_id order by event_timestamp) as prev_event_timestamp
  from 
    `YYYYY.analytics_XXXXX.events_*`
  where
    -- at first - a sliding period - how many days in the past we are looking into:
    _table_suffix
           between format_date("%Y%m%d", date_sub(current_date, interval 10 day))
           and     format_date("%Y%m%d", date_sub(current_date, interval 1 day))
) 
, session_timeline as 
(
  select 
    user_pseudo_id
    , event_timestamp
    , case 
        when 
           -- half a hour period - a threshold for a new 'session'
           event_timestamp - prev_event_timestamp >= (30*60*1000*1000)
             or
           prev_event_timestamp is null 
          then 1
          else 0 
      end as is_new_session_flag
  from 
    timeline
)
, marked_sessions as
(
  select 
    user_pseudo_id
    , event_timestamp
    , sum(is_new_session_flag) over (partition by user_pseudo_id order by event_timestamp) AS user_session_id
  from session_timeline
)
, measured_sessions as
(
  select
    user_pseudo_id
    , user_session_id
    -- session duration in seconds with 2 digits after the point
    , round((max(event_timestamp) - min(event_timestamp))/ (1000 * 1000), 2) as session_duration
  from 
    marked_sessions
  group by
    user_pseudo_id
    , user_session_id
  having 
    -- let's count only sessions longer than 10 seconds
    session_duration >= 10
)
select 
  count(1)                          as number_of_sessions
  , round(avg(session_duration), 2) as average_session_duration_in_sec
from 
  measured_sessions

​

有关如何获取event_date和app_info.id的附加问题，请参见以下查询：

​

with timeline as
(
  select 
     event_date,app_info.id,user_pseudo_id
    , event_timestamp
    , lag(event_timestamp, 1) over (partition by user_pseudo_id order by event_timestamp) as prev_event_timestamp
  from 
    `<table>_*`
  where
    -- at first - a sliding period - how many days in the past we are looking into:
    _table_suffix
          between format_date("%Y%m%d", date_sub(current_date, interval 10 day))
          and     format_date("%Y%m%d", date_sub(current_date, interval 1 day))
) 
, session_timeline as 
(
  select 
    event_date,id,
    user_pseudo_id
    , event_timestamp
    , case 
        when 
           -- half a hour period - a threshold for a new 'session'
           event_timestamp - prev_event_timestamp >= (30*60*1000*1000)
             or
           prev_event_timestamp is null 
          then 1
          else 0 
      end as is_new_session_flag
  from 
    timeline
)
, marked_sessions as
(
  select 
     event_date,id, user_pseudo_id
    , event_timestamp
    , sum(is_new_session_flag) over (partition by user_pseudo_id order by event_timestamp) AS user_session_id
  from session_timeline
)
, measured_sessions as
(
  select
     event_date,id, user_pseudo_id
    , user_session_id
    -- session duration in seconds with 2 digits after the point
    , round((max(event_timestamp) - min(event_timestamp))/ (1000 * 1000), 2) as session_duration
  from 
    marked_sessions
  group by
     event_date, id, user_pseudo_id
    , user_session_id
  having 
    -- let's count only sessions longer than 10 seconds
    session_duration >= 10
)
select 
   event_date, id, count(1)                          as number_of_sessions
  , round(avg(session_duration), 2) as average_session_duration_in_sec
from 
  measured_sessions
  group by event_date, id

​

Answer 2

每个会话(自2019年12月以来定义的：https://firebase.googleblog.com/2018/12/new-changes-sessions-user-engagement.html)都有一个session_id (除了其他参数)。我认为计算平均会话持续时间的最安全和最健壮的方法是将数据提取到BigQuery，然后按会话计算第一个时间戳和最后一个时间戳之间的平均差值。为此，需要对event_params数组进行扁平化处理。例如，AWS雅典娜就是这样做的：

WITH arrays_flattened AS 
    (SELECT params.key AS key,
         params.value.int_value AS id,
         event_timestamp,
         event_date
    FROM your_database
    CROSS JOIN UNNEST(event_params) AS t(params)
    WHERE params.key = 'ga_session_id'), duration AS 
    (SELECT MAX(event_timestamp)-MIN(event_timestamp) AS duration
    FROM arrays_flattened
    WHERE key = 'ga_session_id'
    GROUP BY  id)
SELECT AVG(duration)
FROM duration