在极易同质化的今天,流量竞争着实激烈,提高用户留存的重要性不言而喻,且存量用户的获客成本远远低于拉新成本。
企业大规模投广告、做活动,高额支出锁不住新用户,长期可持续发展有些无力,只顾眼前利益治标不治本。
留存分析是一种用于衡量一个固定用户群体(如某日或某月新注册用户)在一段时间内的活跃情况的数据分析方法。它能帮助公司了解用户在使用产品或服务过程中的行为模式和忠诚度。
二、常见的留存分析使用场景:
简单来说,留存分析是任何希望理解和优化用户行为的产品或服务的重要工具。
三、以实际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