统计用户留存率

原文地址:http://itopic.org/liucunlv-shixian.html

统计留存率之前先弄清一下留存率的概念,百度百科中是这么说的:

用户在某段时间内开始使用应用,经过一段时间后,仍然继续使用应用的被认作是留存;这部分用户占当时新增用户的比例即是留存率,会按照每隔1单位时间(例日、周、月)来进行统计。顾名思义,留存指的就是“有多少用户留下来了”。留存用户和留存率体现了应用的质量和保留用户的能力。

简单点说,第一天新增加了100个用户,第二天这100个人有50个还有登录,第三天这100个人还有30个有登录。。。依次类推 那次日留存率为50%,三日留存为30% 。

在统计系统中经常需要统计用户留存率,这里整理下用户留存率统计的几种实现方式。

1、通过最后登录时间实现

有一张唯一表来记录新增用户,这张表至少包含这三个字段: uid, reg_time, last_visited_time。用户每次访问后更新最后访问时间(last_visited_time),假设3.6号新注册100个用户,需要统计次日留存,则在3.8号凌晨统计reg_time为3.6并且last_visited_time为3.7号即可,参考SQL:

SELECT COUNT(*) FROM TBL_NAME WHERE DATE(reg_time) = '2014-03-06' AND DATE(last_visited_time) = '2014-03-07'

实现起来很简单,但问题也很明显,如果恰好这些用户0点有访问,且先一步更新了访问时间,留存率则记录不到了,这个对整个的结果偏差不会太大,先忽略。有一个更明显的问题就是无法重复统计,如果脚本出错或者需要重新统计则无法实现。当然好处也有,就是统计方便,同时也方便新增N日留存。

2、通过建立独立的字段实现

独立的字段可以这么设计,uid,reg_time,day_2,day_3,day_4…等等,当用户第二天有访问时更新day_2的字段为1,第三日访问更新day_3为1,该系列字段默认为0。同样的统计次日留存,则SQL应该是这样子:

SELECT COUNT(*) FROM TBL_NAME WHERE DATE(reg_time) = '2014-03-06' AND day_2 = 1

该方法可以重复统计了,但又不方便扩展了,如果当前没有考虑到15天流程,则需要修改表结构,新增day_15才行。

3、通过位运算实现

上面的数据表中记录的值就是很多的0和1,可以用这些二进制的0和1来表示当天是否有访问过,1表示有访问过,0表示未访问过。设计表中有这几个字段,uid,reg_time,retension,假设留存用retention记录,则

第一天访问 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 对应十进制的1,retention记录为1

第二天访问 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 第二天有访问后retention更新为3

第四天访问 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 1 第三天没有访问,第四天访问后rentention更新为11

依次类推,接下来就是计算该天的留存,以次日留存为例。将次日的数据与第2位为1其他位为0的值做按位与操作

0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 
&
0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0
=
0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0

按位与是将都为1的设置为1,如果用整数来表示,求次日留存是 3 & 2 ,如果结果为2则表示次日有访问过,如果不为2结果为0则说明没有访问过。所以求第N天的sql应该是(N表示第N天留存,如第3天用第3位来表示就是2的2次方):

SELECT COUNT(*) FROM TBL_NAME WHERE DATE(reg_time) = 'XXXX-XX-XX' AND retention & 2^(N-1)

当然这里的第几天实际表示第几日留存可以自己定,如果第10位表示30日留存,则将retention与2^9求按位与即可求得30日留存。

这里解决了读的问题,还有写的问题,首次注册时值为0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 ,第二天有访问则将前一天的值与第二位为1其他位为0的做按位或操作即可,按位或是将其中任何一个为 1 的位设为 1

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
|
0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0
=
0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1

第三天没有访问,第四天访问则是

0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1
|
0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0
=
0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 1

用SQL来表示就是(N表示第N天访问)

UPDATE TBL_NAME SET retention = retention | 2^(N-1) WHERE uid = 'XX'

而且该更新操作在当天是可以重复操作的,因为按位或只需要有一个为1即可,第2天第一次更新1 | 2 = 3,第二次更新3 | 2 = 3。可见值是相同的。 听到这种方案后也怀疑效率问题,在1000w数据中统计速度与reg_time中索引时间差不多,所以问题不大;一个整形4个字节32位,可以表示32个不同的留存,整形不够也可以用长整型8个字节的。总体看来该方法可扩展,可重新统计,所以可行。

位运算之前只在权限中见过,这里用法也是一种不错的方式,期待更多的思考,下面是位运算的基本操作:

<think>好的,我需要解决用户关于如何计算或分析用户留存率的问题。首先,我得回忆一下用户提供的引用内容,看看里面有哪些有用的信息。根据引用[1],用户留存率的计算方法是基于新增用户的次日、第三日等时间的登录情况。例如,第一天新增100人,第二天有50人登录,那么次日留存率就是50%。引用[4]中的SQL代码示例展示了如何使用datediff函数和casewhen语句来计算不同时间间隔的留存数及留存率。这说明SQL中通常需要自连接用户表,并根据时间差来分组统计。另外,引用[2]提到留存率计算时可能遇到的时间问题,比如未来数据不可知,可能需要回刷数据。这提示在实现时要考虑时间窗口的处理,可能需要动态调整或分批计算。引用[3]则强调了留存率反映用户从新用户转化为忠诚用户的过程,分析时需要关注不同时间点的留存情况。接下来,我需要整理出计算步骤。首先确定时间窗口,比如次日、7日、30日留存。然后需要关联用户首次登录和后续登录的时间,计算时间差,统计每个时间点的留存用户数,最后除以初始用户数得到留存率。在SQL实现部分,引用[4]的代码结构是一个很好的参考。自连接用户表,区分首次登录时间a_t和后续登录时间b_t,计算datediff,再用casewhen按时间间隔分组统计。可能需要使用子查询和聚合函数,比如countdistinct来避免重复用户。同时,要注意性能问题,比如如何处理大数据量下的自连接,可能需要索引优化或分区处理。此外,用户可能需要不同粒度的留存分析,比如周留存、月留存,所以SQL需要灵活调整时间间隔。 最后,用户可能需要了解留存率的应用场景和优化方法,比如如何结合AARRR模型,或者如何处理回刷问题。这部分可以作为相关问题提出,帮助用户深入理解。总结下来,回答的结构应该是:定义留存率,分步骤讲解计算方法,提供SQL示例,并指出注意事项和相关应用。同时,生成相关问题,引导用户进一步学习。</think>### 用户留存率计算方法与分析步骤 #### 1. **留存率定义** 用户留存率是衡量新用户在特定时间窗口内返回应用的比例,反映用户粘性和产品吸引力。例如,$次日留存率 = \frac{次日活跃用户数}{首日新增用户数} \times 100\%$[^3]。 #### 2. **计算步骤** **步骤1:确定时间窗口** 选择需要分析的留存周期(如次日、7日、30日),例如: $$次日留存率 = \frac{第2天活跃用户数}{首日新增用户数}$$ $$7日留存率 = \frac{第8天活跃用户数}{首日新增用户数}$$ **步骤2:关联用户行为数据** 通过自连接表关联用户的首次登录时间($a_t$)和后续活跃时间($b_t$),并计算时间差: ```sql SELECT a.用户id, a_t, b_t, DATEDIFF(b_t, a_t) AS 时间间隔 FROM (SELECT 用户id, 登陆时间 AS a_t FROM 用户表) a LEFT JOIN (SELECT 用户id, 登陆时间 AS b_t FROM 用户表) b ON a.用户id = b.用户id AND a_t < b_t ``` **步骤3:分组统计留存率** 使用`CASE WHEN`按时间间隔分组统计留存用户数: ```sql SELECT a_t, COUNT(DISTINCT CASE WHEN 时间间隔=1 THEN 用户id END) / COUNT(DISTINCT 用户id) AS 次日留存率, COUNT(DISTINCT CASE WHEN 时间间隔=7 THEN 用户id END) / COUNT(DISTINCT 用户id) AS 7日留存率 FROM 关联表 GROUP BY a_t ``` #### 3. **注意事项** - **数据回刷问题**:若需实时计算留存率,需通过动态时间窗口或分批次处理避免未来数据依赖[^2]。 - **性能优化**:对用户ID和登录时间建立索引,减少自连接时的扫描量[^4]。 - **去重逻辑**:使用`COUNT(DISTINCT 用户id)`避免同一用户单日多次登录干扰统计[^1]。 #### 4. **应用场景** - **AARRR模型**:通过留存率分析用户从激活(Acquisition)到留存(Retention)的转化效率[^1]。 - **产品迭代验证**:版本更新后,对比新旧版本的留存率变化评估改进效果[^3]。 --- ###
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值