SQL-计算留存率cohort

最新推荐文章于 2024-05-15 20:16:36 发布

西里小新

最新推荐文章于 2024-05-15 20:16:36 发布

阅读量1.2k

点赞数

分类专栏： SQL 文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_47198715/article/details/130844484

版权

SQL 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

1、留存率cohort介绍及其业务价值

留存率cohort也叫做同期群留存分析，将同一时间范围内的用户分为一组，计算这批人在之后的留存情况，一般结果的样式如下（这里省略数值，）：

新增留存

日期	新增客户数	次1日留存	次2日留存	次3日留存
20220101	1000	50%	40%	39%
20220102	981	59%	50%
20220103	819	61%

老客留存（数据略）

日期	老客户数	次1留存	次2留存	次3留存	....

根据留存cohort图可以得到两方面的信息

1、从横向看，可以得到一批用户在长时间范围内的衰减情况

以上述示例来看：用户在新增后的前两天是快速衰减的（第一天之后有50%的用户不再来了，第三天又损失了40%），到第四天会逐步稳定下来，可见前三天是产品抓住用户的关键窗口期。

2、从纵向看，可以看出不同时间周期来的用户，他们的留存情况是否有改善

以上述示例来看：用户的次日留存在逐步改善（从50%-59%-61%）这可能是产品上做了优化调整，也可能是投放侧在用户的选择上更精准了（对应的用户新增数量也在减少）

2、计算思路

如果通过SQL得到留存cohort数据呢？

Step1：计算留存率分母数据，即每日的用户量

通常来说，新老用户的留存率差异是非常大的，所以需要进行区分。有现成的用户标签是最好的，如果没有可以用窗口函数计算用户是第几次来，第一次来的用户就是当日新增用户。

step2：计算留存率分子

这里计算的间隔时间可以是日，周，月各种时间粒度

关于SQL时间计算的相关知识点可以参考：

SQL-时间处理汇总_格勒王的博客-CSDN博客常见日期提取，日期格式转换，日期加减计算等时间处理方法汇总https://blog.csdn.net/weixin_47198715/article/details/130823960?spm=1001.2014.3001.5502

日期	间隔时间	留存客户数
20220101	1	500
20220101	2	400
20220101	3	390
20220102	...	...

step3：将上面两步骤数据合并

3、实操

3.1、日对日留存cohort

with tmp as 
(select 
  date,--yyyy-mm-dd格式，如果是yyyymmdd需要转换
  user_id
from table_name
where is_new=1#筛选新用户
)


select 
a.date,
gap,
`客户数`,
`留存客户数`,
round(`留存客户数`/`客户数`,2) as `留存率`
from 
 (--计算留存率分母
    SELECT DATE,COUNT(DISTINCT user_id) AS `客户数`
  FROM  tmp
  GROUP BY DATE)a--计算每日新增客户数
LEFT JOIN 
 (--计算留存率分子
  SELECT
  begin_date,
  gap,
  count(distinct user_id) as `留存客户数`
  from 
    (SELECT
    a.user_id,
    a.date as begin_date,
    b.date as stay_date,
    datediff(b.date,a.date)as gap--计算间隔天数
    from 
      (select *
      from tmp)a
    left join 
      (select *
      from tmp)b
    on a.user_id=b.user_id)a
    where gap>=1--筛选间隔天数大于等于1的记录
  group by begin_date,gap)b
on a.date=b.begin_date

3.2、周对周留存cohort

和日对日留存的区别在于，日对日留存只需要计算两次日期的间隔天数即可

而周对周的留存需要计算两个日期的间隔周

有两种方法可以实现周对周留存率的计算

1、先把每天的日期对应到当周的周一，再计算周一之间的日期差除以7

2、使用weekofyear函数，但是这里如果涉及到跨年，需要在处理的时候注意

weekofyear('yyyy-mm-dd')

两个日期周数相差：公式：（52-新增周）+（留存日年份-新增日年份）*留存日周数

如2022年第51周新增的用户，在2023年第1周的留存，计算两个日期的周数差：

（52-51）+（2023-2022）*1=2（也就是次2周留存）

我们以第一种方法示例


with tmp as 
(select 
  date,--yyyy-mm-dd格式，如果是yyyymmdd需要转换
  date_add(DATE,2-if(dayofweek(DATE)=1,8,dayofweek(DATE))) as monday,
  user_id
from table_name
where is_new=1#筛选新用户
)


select 
a.monday,
gap,
`客户数`,
`留存客户数`,
round(`留存客户数`/`客户数`,2) as `留存率`
from 
 (--计算留存率分母
    SELECT monday,COUNT(DISTINCT user_id) AS `客户数`
  FROM  tmp
  GROUP BY monday)a--计算每日新增客户数
LEFT JOIN 
 (--计算留存率分子
  SELECT
  begin_date,
  gap,
  count(distinct user_id) as `留存客户数`
  from 
    (SELECT
    a.user_id,
    a.monday as begin_week,
    b.monday as stay_week,
    datediff(b.monday,a.monday)/7 as gap--计算间隔周数
    from 
      (select *
      from tmp)a
    left join 
      (select *
      from tmp)b
    on a.user_id=b.user_id)a
    where gap>=1--筛选间隔天数大于等于1的记录
  group by begin_date,gap)b
on a.monday=b.begin_week

3.3、月对月留存cohort

计算两个日期之间的月份差可以使用month_bvetween(date1,date2)函数，但是这里计算出来的月份差非整数，如20230301和20230430之间的月份差也是1

在计算月留存时，我们需要对其向下取整，即floor(month_bvetween(date1,date2))

with tmp as 
(select 
  date,--yyyy-mm-dd格式，如果是yyyymmdd需要转换
  substr(DATE,1,7) AS month,
  user_id
from table_name
where is_new=1#筛选新用户
)


select 
a.month,
gap,
`客户数`,
stay_num
from 
 (--计算留存率分母
    SELECT month,COUNT(DISTINCT user_id) AS `客户数`
  FROM  tmp
  GROUP BY month)a--计算每日新增客户数
LEFT JOIN 
 (--计算留存率分子
  SELECT
  begin_date,
  gap,
  count(distinct user_id) as `留存客户数`
  from 
    (SELECT
    a.user_id,
    a.month as begin_month,
    floor(months_between(b.date,a.date))as gap--计算间隔天数
    from 
      (select *
      from tmp)a
    left join 
      (select *
      from tmp)b
    on a.user_id=b.user_id)a
    where gap>=1--筛选间隔天数大于等于1的记录
  group by begin_month,gap)b
on a.month=b.begin_month