SQL之用户留存计算问题

数分小白龙 2021-11-14

1153

上篇文章写到用户新增和用户活跃的一些指标如何用SQL计算，如DNU、DAU、WAU以及MAU。这篇文章聊一聊用户留存问题，那首先什么是用户留存呢？

从最简单的意义上讲，用户留存是指让用户提高粘性，长期使用你的产品。

营销、广告、活动这些都可以让你获得新客户，但是我们要明白，留存对于产品来说，某些程度上远比获客重要得多。一个好的产品加上有效的用户留存策略，这才是长期保持用户参与、活跃和盈利的原因。

最常用的衡量留存率的方法是多日留存分析(N Day Retention)，它可以衡量在特定日期有多少用户仍在使用你的产品。
用户留存分析指南 - 蝌蚪互娱的文章 - 知乎 https://zhuanlan.zhihu.com/p/49050001

一般运营产品都会提用户留存相关的需求，拿最近的一次需求来说，运营方提出想看下我们的产品用户留存情况，需要数据分析师提供以下相关字段的数据，有日期，城市，总访问uv，首次访问uv，次日留存、2日留存、3日留存、4日留存、5日留存、6日留存，7日留存，14日留存以及30日留存。

看到这个需求后，需要明确下所涉及到的表单，既然与留存相关，肯定需要流量表，一般流量表里城市字段都是存储的城市编码，所以肯定需要一个城市表来进行连接，方便取城市的中文名称，为了简单起见，这里只主要展示下用户留存的计算，城市字段的话，只需要连接下就可。

一般公司的流量表都是很大的，基本每日数据量再百万以上，每一个用户的不同操作都会进入到流量宽表里，涉及的字段很多，所以实际场景中，我们还需要根据具体需求筛选出来哪些页面、哪些操作，比如业务想看产品某一个首页的留存信息，我们首先需要从流量表里筛选出来用户在首页的操作，然后在计算留存问题。这里假设已经筛选出来流量数据符合需求，大概表单如下。

表名：liuliang_detail

recordtime	unionid
2021-11-13 20:05:44	fgetrf2erxjk54jijas

下一步就是如何计算用户留存了，首先我们需要对流量表按照用户id进行分区，然后分区同时对用户每一次访问时间进行升序或者降序排序。

create table if not exists liuliang_partition as
select a.*
       ,row_number() over(partition by unionid order by recordtime) rn_asc
       ,row_number() over(partition by unionid order by recordtime desc) rn_desc
from liuliang_detail a
where date(recordtime) >= '2021-01-01' -- 最好根据产品上线时间确定，要不然流量表太大，影响运行效率
;

分区排序的目的就是找到每个用户每次访问时间的顺序，方便后续进行自连接计算两次访问时间的时间间隔。分区排序后，接下来需要进行自连接，按照unionid以及rn_asc，然后再统计用户不同时间的留存uv。

select recorddate
       ,count(distinct unionid) total_uv
       ,count(distinct case when rn_asc = 1 then unionid else null end) new_uv -- 首次访问uv
       ,round(100*count(distinct case when rn_asc = 1 then unionid else null end)/count(distinct unionid), 1) new_uv_ratio -- 首次访问uv占比
       ,count(distinct case when rn_asc <> 1 and diff_days = 1 then unionid else null end) lastday_uv -- 次日留存
       ,count(distinct case when rn_asc <> 1 and diff_days = 2 then unionid else null end) last2day_uv -- 2日留存
       ,count(distinct case when rn_asc <> 1 and diff_days = 3 then unionid else null end) last3day_uv -- 3日留存
       ,count(distinct case when rn_asc <> 1 and diff_days = 4 then unionid else null end) last4day_uv -- 4日留存
       ,count(distinct case when rn_asc <> 1 and diff_days = 5 then unionid else null end) last5day_uv -- 5日留存
       ,count(distinct case when rn_asc <> 1 and diff_days = 6 then unionid else null end) last6day_uv -- 6日留存
       ,count(distinct case when rn_asc <> 1 and diff_days = 7 then unionid else null end) last7day_uv -- 7日留存
       ,count(distinct case when rn_asc <> 1 and diff_days = 14 then unionid else null end) last14day_uv -- 14日留存
       ,count(distinct case when rn_asc <> 1 and diff_days = 30 then unionid else null end) last30day_uv -- 30日留存
from
(
  select a.*
         ,date(recordtime) recorddate
         ,datediff(cast(a.recordtime as date), cast(b.recordtime as date)) diff_days -- 留存天数
  from liuliang_partition a
  left join liuliang_partition b on a.unionid = b.unionid and a.rn_asc = b.rn_asc+1
) x
group by recorddate;

留存的计算逻辑稍微有点绕，比较不容易理解的就是在自连接那里，为什么第一个表rn_asc要等于第二个表的rn_asc+1，主要是要计算每个用户第一次与第二个的访问间隔，第二次与第三次的访问间隔.......

好了，以上就是关于如何用SQL计算用户留存问题，如果哪里有疑问的地方，私信或者留言@我，本人也是一位小小白，还有很多需要学习，随时欢迎探讨，共同进步。

作者：龙小仔

一位零基础正在努力学习数分的小小白

数据库

文章转载自数分小白龙，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL之用户留存计算问题

评论