参与度报告

您可以使用参与度报告深入了解用户在使用您的网站和应用时的参与度水平。该报告通过用户触发的会话以及用户访问的网页和应用屏幕来衡量用户参与度。

注意：本文描述了默认报告。您可以通过应用过滤器或比较，或通过在 QuickSight 中更改维度、指标或图表来自定义报告。了解更多

查看报告

1. 访问您应用程序的仪表板。请参阅 访问仪表板。
2. 在仪表板中，单击名称为 Engagement 的表。

数据来源

参与度报告是基于 Session_View-<app id>-<project id> 的 QuickSight 数据集创建的，该数据集连接到分析引擎（即 Redshift 或 Athena）中的 clickstream_session_view 视图。以下是生成视图的 SQL 命令。

SQL 命令

RedshiftAthena

clickstream-session-view.sql

WITH 
session_part_1 AS (
  SELECT 
    es.session_id::VARCHAR,
    user_pseudo_id,
    platform,
    MAX(session_duration) AS session_duration,
    (CASE WHEN (MAX(session_duration) >= 10000 OR SUM(view) >= 1) THEN 1 ELSE 0 END) AS engaged_session,
    (CASE WHEN (MAX(session_duration) >= 10000 OR SUM(view) >= 1) THEN 0 ELSE 1 END) AS bounced_session,
    MIN(session_st) AS session_start_timestamp,
    SUM(view) AS session_views,
    SUM(engagement_time) AS session_engagement_time
  FROM {{schema}}.clickstream_session_duration_attr_view_v1 AS es
  GROUP BY 1,2,3
),
session_page_data AS (
    SELECT 
      session_id::VARCHAR as session_id,
      view,
      ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY event_timestamp ASC) AS rank
    FROM {{schema}}.clickstream_session_page_attr_view_v1
),
min_max_data as (
  select 
    session_id,
    min(rank) min_rank,
    max(rank) max_rank
  from session_page_data
  group by session_id
),
session_part_2 as (
  select 
    session_page_data.session_id,
    max(case when rank = min_rank then view else null end) first_sv_view,
    max(case when rank = max_rank then view else null end) last_sv_view
  from session_page_data join min_max_data on session_page_data.session_id = min_max_data.session_id 
  and (session_page_data.rank = min_max_data.max_rank or session_page_data.rank = min_max_data.min_rank)
  group by session_page_data.session_id
)
SELECT 
  CASE
    WHEN session.session_id IS NULL THEN CAST('#' AS VARCHAR)
    WHEN session.session_id = '' THEN CAST('#' AS VARCHAR)
    ELSE session.session_id 
  END AS session_id,
  user_pseudo_id,
  platform,
  session_duration::BIGINT,
  session_views::BIGINT,
  engaged_session::BIGINT,
  bounced_session,
  session_start_timestamp,
  CASE
    WHEN session.session_engagement_time IS NULL THEN CAST(0 AS BIGINT)
    ELSE session.session_engagement_time 
  END::BIGINT AS session_engagement_time,
  DATE_TRUNC('day', TIMESTAMP 'epoch' + session_start_timestamp/1000 * INTERVAL '1 second') AS session_date,
  DATE_TRUNC('hour', TIMESTAMP 'epoch' + session_start_timestamp/1000 * INTERVAL '1 second') AS session_date_hour,
  first_sv_view::VARCHAR AS entry_view,
  last_sv_view::VARCHAR AS exit_view
FROM session_part_1 session
LEFT OUTER JOIN session_part_2 ON session.session_id = session_part_2.session_id
;

clickstream-session-query.sql

-- run following command to load latest partition
-- msck repair table {{database}}.{{eventTable}};
-- msck repair table {{database}}.{{eventParamTable}};

with temp_1 as (
  select 
    event.event_id,
    event.event_name,
    event.event_date,
    event.platform,
    event.user_id,
    event.user_pseudo_id,
    event.event_timestamp,
    event_parameter.event_param_key,
    event_parameter.event_param_double_value,
    event_parameter.event_param_float_value,
    event_parameter.event_param_int_value,
    event_parameter.event_param_string_value
  from {{database}}.{{eventTable}} as event
  join {{database}}.{{eventParamTable}} as event_parameter 
  on event.event_timestamp = event_parameter.event_timestamp and event.event_id = event_parameter.event_id
  where event.partition_app = ? 
  and event.partition_year >= ?
  and event.partition_month >= ?
  and event.partition_day >= ?
),
temp_2 as 
(
  SELECT 
     user_pseudo_id
    ,event_id
    ,platform
    ,max(case when event_param_key = '_session_id' then event_param_string_value else null end) as session_id
    ,max(case when event_param_key = '_session_duration' then event_param_int_value else null end) as session_duration
    ,max(case when event_param_key = '_session_start_timestamp' then event_param_int_value else null end) as session_st
    ,max(case when event_param_key = '_engagement_time_msec' then event_param_int_value else null end) as engagement_time
    ,(case when max(event_name) in ('_screen_view', '_page_view') then 1 else 0 end) as view
  FROM temp_1
  group by 1,2,3
),
temp_3 as (
  select 
     event_name
    ,event_id
    ,event_timestamp
    ,max(case when event_param_key = '_session_id' then event_param_string_value else null end) as session_id
    ,(case when max(event_name) in ('_screen_view', '_page_view') then 1 else 0 end) as view
    from temp_1 where event_name in ('_screen_view','_page_view')
    group by 1,2,3
),
session_part_1 as (
  SELECT
     session_id 
    ,user_pseudo_id
    ,platform
    ,max(session_duration) as session_duration
    ,(case when (max(session_duration)>10000 or sum(view) >1) then 1 else 0 end) as engaged_session
    ,(case when (max(session_duration)>10000 or sum(view) >1) then 0 else 1 end) as bounced_session
    ,min(session_st) as session_start_timestamp
    ,sum(view) as session_views
    ,sum(engagement_time) as session_engagement_time
  FROM temp_2
  GROUP BY 1,2,3
),
session_part_2 as (
  select session_id, first_sv_event_id, last_sv_event_id, count(event_id) from (
    select 
      session_id
      ,event_id
      ,first_value(event_id) over(partition by session_id order by event_timestamp asc rows between unbounded preceding and unbounded following) as first_sv_event_id,
      last_value(event_id) over(partition by session_id order by event_timestamp asc rows between unbounded preceding and unbounded following) as last_sv_event_id
    from temp_3 
  ) group by 1,2,3
  ),
  session_f_sv_view as (
    select 
      session_f_l_sv.*,
      t.view as first_sv_view
    from session_part_2 as session_f_l_sv left outer join
    temp_3 as t on session_f_l_sv.first_sv_event_id=t.event_id
), 
session_f_l_sv_view as (
    select 
      session_f_sv_view.*,
      t.view as last_sv_view
    from session_f_sv_view left outer join
    temp_3 as t on session_f_sv_view.last_sv_event_id=t.event_id
)
select 
    CASE
      WHEN session.session_id IS NULL THEN CAST('#' AS VARCHAR)
      WHEN session.session_id = '' THEN CAST('#' AS VARCHAR)
      ELSE session.session_id 
    END AS session_id
    ,user_pseudo_id
    ,platform
    ,cast(session_duration as bigint) as session_duration
    ,cast(session_views as bigint) as session_duration
    ,engaged_session
    ,bounced_session
    ,session_start_timestamp
    ,session_engagement_time
    ,CASE
       WHEN session.session_engagement_time IS NULL THEN CAST(0 AS BIGINT)
       ELSE session.session_engagement_time 
     END AS session_engagement_time
    ,DATE_TRUNC('day', from_unixtime(session_start_timestamp/1000)) as session_date
    ,DATE_TRUNC('hour', from_unixtime(session_start_timestamp/1000)) as session_date_hour
    ,first_sv_view as entry_view
    ,last_sv_view as exit_view
from session_part_1 as session left outer join 
session_f_l_sv_view on session.session_id = session_f_l_sv_view.session_id

维度和指标

报告包括以下维度和指标。您可以通过在 QuickSight 数据集中创建 calculated field 来添加更多维度或指标。了解更多。

字段	类型	是什么	如何填充
session_id	维度	用户在使用您的网站和应用时触发的会话的 SDK 生成的唯一 ID	从分析引擎查询
user_pseudo_id	维度	用户的 SDK 生成的唯一 ID	从分析引擎查询
platform	维度	用户在会话期间使用的平台	从分析引擎查询
session_duration	维度	会话的持续时间（毫秒）	从分析引擎查询
session_views	指标	会话内的屏幕视图或页面视图数量	从分析引擎查询
engaged_session	维度	会话是否参与。 参与的会话定义为会话持续时间超过 10 秒或有两个或两个以上的屏幕视图页面视图	从分析引擎查询
session_start_timestamp	维度	会话的开始时间戳	从分析引擎查询
session_engagement_time	维度	会话的总参与时间（毫秒）	从分析引擎查询
entry_view	维度	用户在会话中查看的第一个屏幕或页面的屏幕名称或页面标题	从分析引擎查询
exit_view	维度	用户在会话中查看的最后一个屏幕或页面的屏幕名称或页面标题	从分析引擎查询
Average engaged session per user	指标	所选时间段内每个用户的平均会话数	QuickSight 中的计算字段
Average engagement time per session	指标	每个会话的平均参与时间（毫秒）	QuickSight 中的计算字段
Average engagement time per user	指标	每个用户的平均参与时间（毫秒）	QuickSight 中的计算字段
Average screen view per user	指标	每个用户的平均屏幕视图数	QuickSight 中的计算字段

示例仪表板

以下图片是一个示例仪表板供您参考。