在哪里可以找到Firebase Analytics中的平均会话持续时间.如何通过Bigquery提取此指标 [英] Where i can find Average Session Duration in Firebase Analytics. How to Extract this Metrics Through Bigquery

问题描述

1. 哪里可以找到平均Firebase分析中的会话时长指标?
2. 如何提取平均来自Bigquery的会话时长指标数据?

平均会话持续时间指标是Firebase分析仪表板中以前可用的.但是现在，它在Firebase分析仪表板中不可用.现在，我们仅看到每个用户的参与度".是每位用户的参与度和平均会话时长都一样吗?如何提取平均Fiebase分析的会话持续时间?如何在Bigquery中查询以提取平均费用. Firebase的会话持续时间指标. 在此处输入图片描述

Avg. Session Duration Metrics which was previous available in Firebase analytics dashboard. But now, it is not available in Firebase analytics dashboard. Now, we are only seeing "Engagement Per User". Is the Engagement Per User and Avg. Session Duration Both are same? How to extract Avg. Session Duration from Fiebase analytics? How to query in Bigquery to extract Avg. Session duration metrics from Firebase. enter image description here

推荐答案

每位用户的参与度与平均水平不同.会话时长.每个用户的参与度是用户每天在应用中花费的时间，而不是会话中的花费.

Engagement per User is not the same as Avg. Session Duration. Engagement per User is all the time a user spends in the app in a day, not in a session.

1. 您可以找到平均最新版本下的Firebase Analytics中的会话时长.

1. You can find Avg. Session Duration in Firebase Analytics under Latest Release.

这里是用于计算平均值的查询. BigQuery中的会话长度:

Here is a query for calculating avg. session length in 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的其他问题，请参见以下查询:

For your additional question on how to get event_date and app_info.id, see the following query:

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

这篇关于在哪里可以找到Firebase Analytics中的平均会话持续时间.如何通过Bigquery提取此指标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！