这些留存分析的计算方式，你知道吗（6）

万能数据的小草

发布于 2024-07-23 15:23:35

890

发布于 2024-07-23 15:23:35

文章被收录于专栏：万能的小草

一、互联网企业面临的留存问题

1. 流量红利见顶、拉新成本高

在极易同质化的今天，流量竞争着实激烈，提高用户留存的重要性不言而喻，且存量用户的获客成本远远低于拉新成本。

2. 新用户较易流失

企业大规模投广告、做活动，高额支出锁不住新用户，长期可持续发展有些无力，只顾眼前利益治标不治本。

留存分析是一种用于衡量一个固定用户群体（如某日或某月新注册用户）在一段时间内的活跃情况的数据分析方法。它能帮助公司了解用户在使用产品或服务过程中的行为模式和忠诚度。

二、常见的留存分析使用场景：

应用分析：开发者利用留存分析可以了解用户下载应用后的使用情况，评估应用的吸引力和粘性。例如，次日留存率可以反映新用户对应用的第一印象，而月度留存率则有助于理解用户长期的满意度和依赖性。
电子商务：通过留存分析了解用户购物的频率和习惯，并评估营销活动和优惠券等策略的效果。同时，也可以帮助发现可能的问题，例如如果新用户的留存率低，可能说明平台的新用户体验需要改进。
在线教育：通过留存分析了解学生对课程的参与度和完成度，可以利用此模块优化课程内容和提高教学质量。
游戏行业：开发者通过留存分析了解玩家的活跃程度、游戏的热度等信息，以此进行游戏设计的调整和优化。

简单来说，留存分析是任何希望理解和优化用户行为的产品或服务的重要工具。

三、以实际case案例分享如何实现留存分析：

该查询使用了DATEDIFF()函数来计算注册日期和事件日期之间的天数差异，然后筛选出两个日期相差一天的用户。接着，它统计了总用户数、次日留存用户数，并计算了次日留存率。

-- 假设表名：fact_table 
-- user_id:表示用户ID
-- registration_date:注册日期
-- event_date:用户实际访问日期。

-- 可以根据自己的表结构和需求进行适当的修改和调整。

SELECT
COUNT(DISTINCT user_id) AS total_users,
COUNT(DISTINCT CASE WHEN DATEDIFF(day, registration_date, event_date) = 1 THEN user_id END) AS retained_users,
    (COUNT(DISTINCT CASE WHEN DATEDIFF(day, registration_date, event_date) = 1 THEN user_id END) / COUNT(DISTINCT user_id)) * 100 AS retention_rate
FROM
    fact_table
WHERE
DATEDIFF(day, registration_date, event_date) >= 1;

四、次日留存3种实现方式对比

方案一：使用子查询

-- 计算成本：高。子查询在每一行数据上都会执行一次，导致查询效率降低。
SELECT
    t1.registration_date
    ,COUNT(DISTINCT t1.user_id) as total_users
    ,(
SELECT
COUNT(DISTINCT user_id)
FROM
            fact_table t2
WHERE
DATEDIFF(day, t1.registration_date, t2.event_date) = 1
    ) as retained_users,    -- 留存人数
    (
SELECT
COUNT(DISTINCT user_id) / COUNT(DISTINCT t1.user_id) * 100
FROM
            fact_table t3
WHERE
DATEDIFF(day, t1.registration_date, t3.event_date) = 1
    ) as retention_rate   --留存率
FROM
    fact_table  t1
GROUP BY
    registration_date;

方案二：使用窗口函数和CASE WHEN结构：

-- 计算成本：中等。虽然窗口函数可以避免重复的全表扫描，
-- 但仍需要对所有用户的事件数据进行排序，这可能会引入较大的计算成本。


SELECT
    registration_date,
COUNT(DISTINCT user_id) as total_users,  -- 总用户数
SUM(CASE WHEN DATEDIFF(day, registration_date, LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date)) = 1 THEN 1 ELSE 0 END) as retained_users,
    (SUM(CASE WHEN DATEDIFF(day, registration_date, LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date)) = 1 THEN 1 ELSE 0 END) / COUNT(DISTINCT user_id))*100 as retention_rate
FROM fact_table
GROUP BY registration_date;
-- 使用lag 函数

方案三：自连接

-- 计算成本：低。自连接只需要对表进行一次扫描，
-- 并且可以利用索引来提高JOIN操作的效率。


SELECT
    a.registration_date,
COUNT(DISTINCT a.user_id) as total_users,
COUNT(DISTINCT b.user_id) as retained_users,
    (COUNT(DISTINCT b.user_id) / COUNT(DISTINCT a.user_id))*100 as retention_rate
FROM fact_table a
LEFT JOIN fact_table b ON a.user_id = b.user_id AND DATEDIFF(day, a.registration_date, b.event_date) = 1
GROUP BY a.registration_date;

五、执行结果

registration_date（注册日） | total_users（用户总数） | retained_users（留存人数） | retention_rate
（留存率）
------------------|-------------|----------------|---------------
2022-01-01        | 1000        | 300            | 30.00
2022-01-02        | 950         | 250            | 26.32
2022-01-03        | 1200        | 360            | 30.00

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2023-10-14，如有侵权请联系 cloudcommunity@tencent.com 删除

函数