碎语
- 学习的正态曲线：入门容易，精通难
- 积累的正态曲线：先越读越多，后越读越少
星中有数
对于企业来说，留存的主要性不言而喻。在互联网中，大家经常会看次日/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的效率
结束语
如果我不能让您看懂，那是我的问题，如果有疑问可以关注我，然后私聊我，我会尽最大的努力帮助你。如果觉得对你有帮助，请帮忙点赞/关注，谢谢！
