碎语

• 学习的正态曲线：入门容易，精通难
• 积累的正态曲线：先越读越多，后越读越少

星中有数

对于企业来说，留存的主要性不言而喻。在互联网中，大家经常会看次日/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登陆了，那么我们就能得到如下的表格

• 这样我们看每天有多少个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的效率

结束语

如果我不能让您看懂，那是我的问题，如果有疑问可以关注我，然后私聊我，我会尽最大的努力帮助你。如果觉得对你有帮助，请帮忙点赞/关注，谢谢！