碎语

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

星中有数

客户是公司最重要的资源，而想要获得更多的用户，我们可以通过两种方式：要么拉新，要么留存，当然，对于初创公司来说，拉新是必须的。但是，在互联成熟的时期，拉新成本一路攀升，基本拉新的成本是维护老用的成本3倍。所以，留存很重要。

从另外一个角度来说，用户在平台上待的时间越长，对于平台的价值越高，不管是直接价值和间接的价值。并且留存高证明平台的价值越高。

所以，我们必须关注新用户的留存。关于怎么去系统的分析留存，我里不做过多的介绍，主要是给大家从技术的角度讲讲，怎么通过sql来提取次日留存

SQL

select
    reg_date,
    count(reg.user_id) as reg_user_cnt,
    count(case when login.user_id is not null then reg.user_id end ) as back_user_cnt
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-02' and '2020-02-01'
    group by 
        user_id,
        login_date
    )login on reg.user_id = login.user_id and reg.reg_date = date_sub(login_date,1)
group by 
    reg_date

星星详析

原理

• register_data:注册表，login_data:登陆表
• 如果使用了多表连接的朋友们，你们应该可以看出是一个左连接，此处的技巧在于我们在连接条件时使用了date_sub函数，这样我们就以注册表为主表，连接登陆表，与登陆表中第二天登陆用户进行连接，这样：
  • count(reg.user_id)就是求的注册的用户数
  • count(case when login.user_id is not null then reg.user_id end )就是第二天回来的用户

注意

• 一定要保证注册表和登陆表中每天的用户ID是唯一的，因为我们只需要保证能够记录这个用户是否回归登陆了。
  • 在注册表(register_data)中,每天注册的用户ID一定是唯一的，所以不需要去重
  • 而在登陆表(login_data)中,有可能用户这个用户一天登陆几次，所以，需要对user_id和login_date去重，一般去重是使用distinct,此处我使用的是group by用法来保证唯一，这种方法的效率会高一些。
• 注册日期(reg_date)和登陆日期(login_date)一定保证是标准的日期类型，因为我们需要使用日期函数(date_sub),一般处理成"yyyy-MM-dd"("2020-02-01")形式，有的公司日期会记录为"yyyyMMdd"(20200201)，此时我们可以这么做from_unixtime(unix_timestamp(string(login_date),'yyyyMMdd'),'yyyy-MM-dd'),使用时把login_date换成你公司的字段即可
• 大家可以好好理解一下，left join和case when的用法，这种用法需要你进一步的理解左连接，其实就是没有连接上就为空。
• SQL查询最好进行一对一的查询，其次时一对多，尽量避免多对多查询

结束语

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