碎语
- 学习的正态曲线:入门容易,精通难
- 积累的正态曲线:先越读越多,后越读越少
星中有数
对于企业来说,留存的主要性不言而喻。在互联网中,大家经常会看次日/7日/30日的用户留存,也有大家公认的40%/20%/10%,也就是次日留存40%,7日留存20%,30日留存10%算是比较正常的。
通过做留存分析,我觉得不应该只看某几天的留存,而是应该看留存曲线,也就是看用户第2-N日的留存。这样我们可以更加清晰的看出留存的好坏,以及可以对留存划分区间,在不同区间的策略不一样。
在前面我们看了用户的次日留存,那么如果我们想看用户的第2-N日的留存怎么样,我们从SQL的角度怎么写呢?
SQL
select
reg_date,
diff,
count(user_id) as user_cnt
from
(select
reg_date,
user_id,
datediff(login_date,reg_date) as diff
from
(select
user_id,
reg_date --注册日期
from
register_data --注册表
where
reg_date between '2020-01-01' and '2020-01-31'
)reg left join
(
select
user_id,
login_date --登陆日期
from
login_data --登陆表
where
login_date bewteen '2020-01-01' and '2020-03-02'
group by
user_id,
login_date
)login on reg.user_id = login.user_id
)a
where
diff <=30
group by
reg_date,
diff
星星详析
原理
- 利用左连接,在注册表(register_data)中,用户是唯一的,对应的注册时间也是唯一的,而登陆表(login_data)中,每天登陆的用户进行去重,不同天之间去重。
- 然后以用户ID进行连接,连接后每一行就会得到用户ID,注册时间,登陆时间。
- 使用datediff函数,让登陆时间减去注册时间,等到时间差。这样我们就能得到注册时间,用户ID,以及时间差,比如用户A注册时间是20200101,他在20200101/20200102/20200104/20200105登陆了,那么我们就能得到如下的表格
用户ID | 注册时间 | 登陆时间 | 时间差 |
---|---|---|---|
A | 20200101 | 20200101 | 0 |
A | 20200101 | 20200102 | 1 |
A | 20200101 | 20200104 | 3 |
A | 20200101 | 20200105 | 5 |
- 这样我们看每天有多少个0,那么就是用多少人注册,有多少个1那么次日留存就用多少,以此类推。。
注意
- 登陆表一定要去重,保证每天一个用户记录一次
- 连接时一定只能是以用户ID进行连接
- 注册日期(reg_date)和登陆日期(login_date)一定保证是标准的日期类型,因为我们需要使用日期相减函数(datediff),一般处理成"yyyy-MM-dd"("2020-02-01")形式,有的公司日期会记录为"yyyyMMdd"(20200201),此时我们可以这么做from_unixtime(unix_timestamp(string(login_date),'yyyyMMdd'),'yyyy-MM-dd'),使用时把login_date换成你公司的字段即可
- 登陆日期取出时,登陆表中最后一天的日期一定时大于注册日期中N天,比如你想看20200101到20200110这段时间注册的用户第2-30天留存,那么,你的登陆日期必须从20200101到20200210,最后,需要对diff控制小于等于30,因为,在20200101这样注册的用户在20200210回归登陆,那就diff大于30了。这样能提高SQL的效率
结束语
如果我不能让您看懂,那是我的问题,如果有疑问可以关注我,然后私聊我,我会尽最大的努力帮助你
网友评论