跳转至

留存报告

您可以使用留存报告来深入了解用户在首次访问您的网站或移动应用程序后与您的网站或移动应用程序互动的频率和时长。 该报告可帮助您了解您的应用在吸引用户首次访问后再次访问方面的表现如何。

注意：本文介绍默认报告。 您可以通过应用筛选器或比较或更改 QuickSight 中的维度、指标或图表来自定义报告。 了解更多

查看报告

  1. 访问您的应用程序的仪表板。 参考【访问仪表板】(index.md)
  2. 在仪表板中，单击名称为 Retention 的工作表。

数据从哪里来

留存报告是基于以下 QuickSight 数据集创建的：

  • lifecycle_weekly_view-<app id>-<project id>，连接到分析引擎（即 Redshift 或 Athena）中的 clickstream_lifecycle_weekly_view 视图。
  • lifecycle_daily_view-<app id>-<project id>，连接到分析引擎（即 Redshift 或 Athena）中的 clickstream_lifecycle_daily_view 视图。
  • 连接到分析引擎中的 clickstream_retention_view 视图。

下面是生成视图的 SQL 命令。

SQL 命令

clickstream_lifecycle_weekly_view.sql
with lag_lead as (
  select user_pseudo_id, time_period_week,
    lag(time_period_week,1) over (partition by user_pseudo_id order by time_period_week),
    lead(time_period_week,1) over (partition by user_pseudo_id order by time_period_week)
  from {{schema}}.clickstream_lifecycle_view_v1
),
-- calculate lag and lead size
lag_lead_with_diffs as (
  select user_pseudo_id, time_period_week, lag, lead, 
    datediff(week,lag,time_period_week) lag_size,
    datediff(week,time_period_week,lead) lead_size
  from lag_lead
),
-- case to lifecycle stage
calculated as (
  select 
    time_period_week,
    this_week_value,
    next_week_churn,
    count(user_pseudo_id) as total_users
  from (
    select time_period_week,
      case when lag is null then '1-NEW'
        when lag_size = 1 then '2-ACTIVE'
        when lag_size > 1 then '3-RETURN'
      end as this_week_value,
      case when (lead_size > 1 OR lead_size IS NULL) then '0-CHURN'
        else NULL
      end as next_week_churn,
      user_pseudo_id
    from lag_lead_with_diffs
    group by 1,2,3,4
  ) t1
  group by 1,2,3
)
select time_period_week as time_period, this_week_value, sum(total_users) as sum
  from calculated group by 1,2
union
select time_period_week+7 as time_period, '0-CHURN' as this_week_value, -1*sum(total_users) as sum
  from calculated where next_week_churn is not null 
  group by 1,2
;
clickstream_lifecycle_daily_view.sql
with lag_lead as (
  select user_pseudo_id, time_period,
    lag(time_period,1) over (partition by user_pseudo_id order by time_period),
    lead(time_period,1) over (partition by user_pseudo_id order by time_period)
  from {{schema}}.clickstream_lifecycle_view_v1
),
-- calculate lag and lead size
lag_lead_with_diffs as (
  select user_pseudo_id, time_period, lag, lead, 
    datediff(day,lag,time_period) lag_size,
    datediff(day,time_period,lead) lead_size
  from lag_lead
),
-- case to lifecycle stage
calculated as (
  select 
    time_period,
    this_day_value,
    next_day_churn,
    count(user_pseudo_id) as total_users
  from (
    select time_period,
      case when lag is null then '1-NEW'
        when lag_size = 1 then '2-ACTIVE'
        when lag_size > 1 then '3-RETURN'
      end as this_day_value,

      case when (lead_size > 1 OR lead_size IS NULL) then '0-CHURN'
        else NULL
      end as next_day_churn,
      user_pseudo_id
    from lag_lead_with_diffs
    group by 1,2,3,4
  ) t1
  group by 1,2,3
)
select time_period as time_period , this_day_value, sum(total_users) as sum
  from calculated group by 1,2
union
select time_period+1 as time_period, '0-CHURN' as this_day_value, -1*sum(total_users) as sum
  from calculated where next_day_churn is not null 
  group by 1,2
;
clickstream_retention_view.sql
WITH user_first_date AS (
  SELECT
    user_pseudo_id,
    min(event_date) as first_date
  FROM {{schema}}.event
  GROUP BY user_pseudo_id
),

retention_data AS (
SELECT
    user_pseudo_id,
    first_date,
    DATE_DIFF('day', first_date, event_date) AS day_diff
  FROM {{schema}}.event
  JOIN user_first_date USING (user_pseudo_id)
),

retention_counts AS (
  select 
    first_date,
    day_diff,
    COUNT(user_pseudo_id) AS returned_user_count
  from (
    SELECT
      first_date,
      day_diff,
      user_pseudo_id
    FROM retention_data
    WHERE day_diff <= 42 -- Calculate retention rate for the last 42 days
    GROUP BY first_date, day_diff, user_pseudo_id
  ) t1
  GROUP BY first_date, day_diff
),

total_users AS (
  SELECT
    first_date,
    COUNT(user_pseudo_id) AS total_users
  FROM (
    select 
      first_date,
      user_pseudo_id
    from user_first_date group by 1,2
  ) t2
  group by 1
),

retention_rate AS (
  SELECT
    first_date,
    day_diff,
    returned_user_count,
    total_users
  FROM retention_counts join total_users using(first_date)
)

SELECT
*
FROM retention_rate;

clickstream-lifecycle-weekly-query.sql
-- run following command to load latest partition
-- msck repair table {{database}}.{{eventTable}};

with weekly_usage as (
  select 
    user_pseudo_id, 
    DATE_TRUNC('week', event_date) as time_period
  from {{database}}.{{eventTable}}
  where partition_app = ? 
    and partition_year >= ?
    and partition_month >= ?
    and partition_day >= ?
    and event_name = '_session_start' group by 1,2 order by 1,2
),
lag_lead as (
  select user_pseudo_id, time_period,
    lag(time_period,1) over (partition by user_pseudo_id order by user_pseudo_id, time_period) as lag,
    lead(time_period,1) over (partition by user_pseudo_id order by user_pseudo_id, time_period) as lead
  from weekly_usage
),
lag_lead_with_diffs as (
  select user_pseudo_id, time_period, lag, lead, 
    date_diff('week',lag,time_period) lag_size,
    date_diff('week',time_period,lead) lead_size
  from lag_lead
),
calculated as (
  select time_period,
    case when lag is null then '1-NEW'
      when lag_size = 1 then '2-ACTIVE'
      when lag_size > 1 then '3-RETURN'
    end as this_week_value,
    case when (lead_size > 1 OR lead_size IS NULL) then '0-CHURN'
      else NULL
    end as next_week_churn,
    count(distinct user_pseudo_id) as cnt
  from lag_lead_with_diffs
  group by 1,2,3
)
select time_period, this_week_value, sum(cnt) as cnt from calculated group by 1,2
union
select date_add('day', 7, time_period), '0-CHURN', -1*sum(cnt) as cnt
  from calculated 
where next_week_churn is not null 
group by 1,2
clickstream-lifecycle-daily-query.sql
-- run following command to load latest partition
-- msck repair table {{database}}.{{eventTable}};

with daily_usage as (
  select 
    user_pseudo_id, 
    DATE_TRUNC('day', event_date) as time_period
  from {{database}}.{{eventTable}} 
  where partition_app = ? 
    and partition_year >= ?
    and partition_month >= ?
    and partition_day >= ?
    and event_name = '_session_start' group by 1,2 order by 1,2
),
lag_lead as (
  select user_pseudo_id, time_period,
    lag(time_period,1) over (partition by user_pseudo_id order by user_pseudo_id, time_period) as lag,
    lead(time_period,1) over (partition by user_pseudo_id order by user_pseudo_id, time_period) as lead
  from daily_usage
),
lag_lead_with_diffs as (
  select user_pseudo_id, time_period, lag, lead, 
    date_diff('day',lag,time_period) lag_size,
    date_diff('day',time_period,lead) lead_size
  from lag_lead
),
calculated as (
  select time_period,
    case when lag is null then '1-NEW'
      when lag_size = 1 then '2-ACTIVE'
      when lag_size > 1 then '3-RETURN'
    end as this_day_value,

    case when (lead_size > 1 OR lead_size IS NULL) then '0-CHURN'
      else NULL
    end as next_day_churn,
    count(distinct user_pseudo_id) as cnt
  from lag_lead_with_diffs
  group by 1,2,3
)
select time_period, this_day_value, sum(cnt) as cnt
  from calculated group by 1,2
union
select date_add('day', 1, time_period) as time_period, '0-CHURN', -1*sum(cnt) as cnt 
  from calculated 
  where next_day_churn is not null 
  group by 1,2;
clickstream-lifecycle-daily-query.sql
-- run following command to load latest partition
-- msck repair table {{database}}.{{eventTable}};

with base as (
  select 
    *
  from {{database}}.{{eventTable}}
  where partition_app = ? 
    and partition_year >= ?
    and partition_month >= ?
    and partition_day >= ?
),
user_first_date AS (
  SELECT
    user_pseudo_id,
    min(event_date) as first_date
  FROM base
  GROUP BY user_pseudo_id
),

retention_data AS (
SELECT
    user_pseudo_id,
    first_date,
    DATE_DIFF('day', first_date, event_date) AS day_diff
  FROM base
  JOIN user_first_date USING (user_pseudo_id)
),

retention_counts AS (
  SELECT
    first_date,
    day_diff,
    COUNT(DISTINCT user_pseudo_id) AS returned_user_count
  FROM retention_data
  WHERE day_diff <= 42 -- Calculate retention rate for the last 42 days
  GROUP BY first_date, day_diff
),

total_users AS (
  SELECT
    first_date,
    COUNT(DISTINCT user_pseudo_id) AS total_users
  FROM user_first_date
  group by 1
),

retention_rate AS (
  SELECT
    first_date,
    day_diff,
    returned_user_count,
    total_users
  FROM retention_counts join total_users using(first_date)
)

SELECT
*
FROM retention_rate

维度和指标

该报告包括以下维度和指标。 您可以通过在 QuickSight 数据集中创建“计算字段”来添加更多维度或指标。 了解更多。

字段 类型 这是什么 如何填充
每日活跃用户 (DAU) 指标 每个日期的活跃用户数 QuickSight聚合
每周活跃用户 (WAU) 指标 近7天活跃用户数 QuickSight 中的计算字段
每月活跃用户 (MAU) 指标 过去 30 天内的活跃用户数 QuickSight 中的计算字段
user_pseudo_id 维度 SDK 为用户生成的唯一 ID 来自分析引擎的查询
用户id 维度 通过SDK中的setUserId API设置的用户ID 来自分析引擎的查询
日活跃用户/月活跃用户 指标 用户粘性的 DAU/WAU % QuickSight 中的计算字段
月活跃用户/月活跃用户 指标 用户粘性的 WAU/MAU % QuickSight 中的计算字段
日活跃用户/月活跃用户 指标 用户粘性的 DAU/MAU % QuickSight 中的计算字段
事件用户类型 维度 执行事件的用户类型，即新用户或现有用户 QuickSight 中的计算字段
用户首次触摸日期 指标 用户首次使用您的网站或应用程序的日期 QuickSight 中的计算字段
留存率 指标 不同的活跃用户数量 / 按用户首次接触日期划分的不同的活跃用户数量 QuickSight 中的计算字段
时间周期 维度 用户生命周期的周或天 来自分析引擎的查询
本周值 维度 用户生命周期阶段，即新用户、活跃用户、返回用户和流失用户 来自分析引擎的查询
this_day_value 维度 用户生命周期阶段，即新用户、活跃用户、返回用户和流失用户 来自分析引擎的查询

示例仪表板

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

仪表板留存