实际工作中常见的业务场景是求次日留存率，还有一些会对次日留存率增加限制，例如求新用户的次日留存率或者求活跃用户留存率。另外，留存率和复购率看起来都是统计重复出现的概率，但实际求解方法是不一样的。
【场景】：次日留存率、复购率
【知识点】：留存率的定义、复购率的定义、多表连接、date_sub(dt1, interval 1 day) = dt2、datediff(dt1, dt2) = 1
一、分析思路
1、留存率如何定义？
次日留存率 = （当天活跃的用户第二天又活跃了的用户数）/（当天活跃的用户数）
常见的场景是求次日留存率，本文以次日留存率为例。
扩展： 第N日留存率 = （当天活跃的用户第N天又活跃了的用户数）/（当天活跃的用户数）
举个例子：如果用户1在1号、2号、3号活跃；用户2在1号、2号活跃。那么次日留存率是多少？
- 先把上面的活跃记录按照日期、活跃用户、次日留存用户数整理成下表：
日期 活跃用户 次日留存用户数 1号 用户1、用户2 2 2号 用户1、用户2 1 3号 用户1 0 总计 5 3 次日留存率 = 3/5。
2、如何判断当日活跃的用户次日又有活跃记录？
同一个表看成两个表做连接，使用
select distinct a.主键, a.活跃日期 as 当日, b.活跃日期 as 次日 from 表名1 a left join( select distinct 主键, 活跃日期 from 表名1 ) b on a.主键 = b.主键 and 次日-当日=一天这样就得到用户当日和次日的活跃记录。
- 用户当日、次日活跃记录表
用户 当日 次日 用户1 1号 2号 用户2 1号 2号 用户1 2号 3号 用户2 2号 None 用户1 3号 None
3、计算次日留存率
次日留存率 = 次日留存用户数 / 当日活跃用户数
select count(次日) / count(当日) as 次日留存率 from 用户当日和次日的活跃记录表
二、实例
下面就以三个实例讲清楚什么是次日留存率、新用户的次日留存率和复购率。
三种问题的区别：
| 问题 | 描述 | 特点 | 使用方法 |
|---|---|---|---|
| 次日留存率 | 次日留存率为当天活跃的用户数中第二天又活跃了的用户数占比 | 固定时间的记录 | 先获取用户、当日和第n日活跃时间记录表，后按照留存率公式进行计算 |
| 新用户次日留存率 | 新用户的次日留存率为当天新增的用户数中第二天又活跃了的用户数占比 | 限制条件的留存率 | 先获取新用户、当日和第n日活跃时间记录表，后按照留存率公式进行计算 |
| 复购率 | 复购率指用户在一段时间内对某商品的重复购买比例 | 一段时间内的记录 | 先获取根据商品、购买人分组，按照购买时间排序后的购买记录表，后按照复购率公式进行计算 |
（1）计算次日留存率
案例来自：SQL29 计算用户的平均次日留存率
题目： 现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。
示例：question_practice_detail
| id | device_id | quest_id | result | date |
|---|---|---|---|---|
| 1 | 2138 | 111 | wrong | 2021-05-03 |
| 2 | 3214 | 112 | wrong | 2021-05-09 |
| 3 | 3214 | 113 | wrong | 2021-06-15 |
| 4 | 6543 | 111 | right | 2021-08-13 |
| 5 | 2315 | 115 | right | 2021-08-13 |
| 6 | 2315 | 116 | right | 2021-08-14 |
| 7 | 2315 | 117 | wrong | 2021-08-15 |
| …… |
根据示例，你的查询应返回以下结果：
| avg_ret |
|---|
| 0.3000 |
求解代码：
select
count(date2) / count(date1) as avg_ret
from(
select distinct
a.device_id,
a.date as date1,
b.date as date2
from question_practice_detail a
left join(
select distinct device_id,
date
from question_practice_detail
) b on a.device_id = b.device_id and date_add(a.date, interval 1 day) = b.date
) c
- 用户当日、次日活跃记录表
device_id date1 date2
1 2138 2021-05-03 None
2 3214 2021-05-09 None
3 3214 2021-06-15 None
4 6543 2021-08-13 None
5 2315 2021-08-13 2021-08-14
6 2315 2021-08-14 2021-08-15
7 2315 2021-08-15 None
8 3214 2021-08-15 2021-08-16
9 3214 2021-08-16 None
10 3214 2021-08-18 None
扩展：
（2）计算新用户的次日留存率
案例来自：SQL164 2021年11月每天新用户的次日留存率
问题：统计2021年11月每天新用户的次日留存率（保留2位小数）
用户行为日志表tb_user_log。（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）
| id | uid | artical_id | in_time | out_time | sign_cin |
|---|---|---|---|---|---|
| 1 | 101 | 0 | 2021-11-01 10:00:00 | 2021-11-01 10:00:42 | 1 |
| 2 | 102 | 9001 | 2021-11-01 10:00:00 | 2021-11-01 10:00:09 | 0 |
| 3 | 103 | 9001 | 2021-11-01 10:00:01 | 2021-11-01 10:01:50 | 0 |
| 4 | 101 | 9002 | 2021-11-02 10:00:09 | 2021-11-02 10:00:28 | 0 |
| 5 | 103 | 9002 | 2021-11-02 10:00:51 | 2021-11-02 10:00:59 | 0 |
| 6 | 104 | 9001 | 2021-11-02 11:00:28 | 2021-11-02 11:01:24 | 0 |
| 7 | 101 | 9003 | 2021-11-03 11:00:55 | 2021-11-03 11:01:24 | 0 |
| 8 | 104 | 9003 | 2021-11-03 11:00:45 | 2021-11-03 11:00:55 | 0 |
| 9 | 105 | 9003 | 2021-11-03 11:00:53 | 2021-11-03 11:00:59 | 0 |
| 10 | 101 | 9002 | 2021-11-04 11:00:55 | 2021-11-04 11:00:59 | 0 |
注：
- 次日留存率为当天新增的用户数中第二天又活跃了的用户数占比。
- 如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过，结果按日期升序。
输出示例：
示例数据的输出结果如下
| dt | uv_left_rate |
|---|---|
| 2021-11-01 | 0.67 |
| 2021-11-02 | 1.00 |
| 2021-11-03 | 0.00 |
解释：
11.01有3个用户活跃101、102、103，均为新用户，在11.02只有101、103两个又活跃了，因此11.01的次日留存率为0.67；
11.02有104一位新用户，在11.03又活跃了，因此11.02的次日留存率为1.00；
11.03有105一位新用户，在11.04未活跃，因此11.03的次日留存率为0.00；
11.04没有新用户，不输出。
分析思路
1、如何计算新用户的次日留存率？
新用户的次日留存率 = （当日活跃且次日仍活跃的新用户数）/（当日活跃的新用户数）
- 先把上面的活跃记录按照日期、活跃用户、次日留存用户数、每天的次日留存率整理成下表：
日期 活跃用户 次日留存新用户 每天的次日留存率 1号 101、102、103 101、103 2/3 = 0.67 2号 101、103、104 104 1/1 = 1 3号 104、105 None 0
2、如何判断当日活跃的新用户次日又有活跃记录？
同一个表看成两个表做连接，使用
select distinct a.主键, a.活跃日期 as 当日, b.活跃日期 as 次日 from 新用户活跃记录表 a left join( select distinct 主键, 活跃日期 from 用户活跃记录表 ) b on a.主键 = b.主键 and a表和b表活跃日期的天数相差1天这样就得到新用户当日和次日的活跃记录。
- 新用户当日、次日活跃记录表
用户 当日 次日 101 1号 2号 102 1号 None 103 1号 2号 104 2号 3号 105 3号 None
3、计算每天的次日留存率
每天的次日留存率 = 次日留存用户数 / 当日活跃用户数在求每天的次日留存率时，注意需要分组，使用group by。
select 当日, count(次日)/ count(当日) as 每天的次日留存率 from 新用户当日、次日活跃记录表 group by 当日
求解代码：
with
temp as(
select
main.uid,
main.min_dt,
attr.dt
from(
#统计新增用户
select
uid,
min(date(in_time)) as min_dt
from tb_user_log
group by uid
) main
left join(
#统计用户的活跃记录
(select
uid,
date(in_time) as dt
from tb_user_log)
union
(select
uid,
date(out_time) as dt
from tb_user_log)
) attr on main.uid = attr.uid and date_sub(dt, interval 1 day) = min_dt
)
#统计2021年11月每天新用户的次日留存率,（保留2位小数）
select
min_dt as dt,
round(count(dt)/count(min_dt),2) as uv_left_rate
from temp
where min_dt like '2021-11%'
group by min_dt
order by min_dt
- 新用户当日、次日活跃记录表
device_id date1 date2
1 101 2021-11-01 2021-11-02
2 102 2021-11-01 None
3 103 2021-11-01 2021-11-02
4 104 2021-11-02 2021-11-03
5 105 2021-11-03 None
（3）计算复购率
问题：请统计零食类商品中复购率top3高的商品。
商品信息表tb_product_info。（product_id-商品ID, shop_id-店铺ID, tag-商品类别标签, in_price-进货价格, quantity-进货数量, release_time-上架时间）
| id | product_id | shop_id | tag | int_ | quantity | release_time |
|---|---|---|---|---|---|---|
| 1 | 8001 | 901 | 零食 | 60 | 1000 | 2020-01-01 10:00:00 |
| 2 | 8002 | 901 | 零食 | 140 | 500 | 2020-01-01 10:00:00 |
| 3 | 8003 | 901 | 零食 | 160 | 500 | 2020-01-01 10:00:00 |
订单总表tb_order_overall。（order_id-订单号, uid-用户ID, event_time-下单时间, total_amount-订单总金额, total_cnt-订单商品总件数, status-订单状态）
| id | order_id | uid | event_time | total_amount | total_cnt | status |
|---|---|---|---|---|---|---|
| 1 | 301001 | 101 | 2021-09-30 10:00:00 | 140 | 1 | 1 |
| 2 | 301002 | 102 | 2021-10-01 11:00:00 | 235 | 2 | 1 |
| 3 | 301011 | 102 | 2021-10-31 11:00:00 | 250 | 2 | 1 |
| 4 | 301003 | 101 | 2021-10-02 10:00:00 | 300 | 2 | 1 |
| 5 | 301013 | 105 | 2021-10-02 10:00:00 | 300 | 2 | 1 |
| 6 | 301005 | 104 | 2021-10-03 10:00:00 | 170 | 1 | 1 |
订单明细表tb_order_detail。（order_id-订单号, product_id-商品ID, price-商品单价, cnt-下单数量）
| id | order_id | product_id | price | cnt |
|---|---|---|---|---|
| 1 | 301001 | 8002 | 150 | 1 |
| 2 | 301011 | 8003 | 200 | 1 |
| 3 | 301011 | 8001 | 80 | 1 |
| 4 | 301002 | 8001 | 85 | 1 |
| 5 | 301002 | 8003 | 180 | 1 |
| 6 | 301003 | 8002 | 140 | 1 |
| 7 | 301003 | 8003 | 180 | 1 |
| 8 | 301013 | 8002 | 140 | 2 |
| 9 | 301005 | 8003 | 180 | 1 |
场景逻辑说明：
- 用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款， status-订单状态- 订单状态为0表示待付款），在订单明细表生成该订单中每个商品的信息；
- 当用户支付完成时，在订单总表修改对应订单记录的status-订单状态- 订单状态为1表示已付款；
- 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，订单状态为2表示已退款）。
注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率
此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数
近90天指包含最大日期（记为当天）在内的近90天。结果中复购率保留3位小数，并按复购率倒序、商品ID升序排序
输出示例：
示例数据的输出结果如下：
| product_id | repurchase_rate |
|---|---|
| 8001 | 1.000 |
| 8002 | 0.500 |
| 8003 | 0.333 |
解释：
商品8001、8002、8003都是零食类商品，8001只被用户102购买了两次，复购率1.000；
商品8002被101购买了两次，被105购买了1次，复购率0.500；
商品8003被102购买两次，被101和105各购买1次，复购率为0.333。
分析思路
1、如何计算每个商品的复购率？
某商品复购率 = 近90天内购买它至少两次的人数 / 购买它的总人数
- 先把上面的购买记录按照商品、购买用户、购买次数、每个商品的复购率整理成下表：
商品 购买用户 购买次数 8001 102 2 8002 101 2 8002 105 1 8003 101 1 8003 102 2 8003 105 1 由表可知每个商品的复购率分别为1.000、0.500、0.333。
2、统计每个商品的购买用户和购买次数
统计每个货物用户的购买记录。利用窗口函数根据货号、用户分组按购买时间排序select 商品, 用户, 利用窗口函数根据货号、用户分组按购买时间排序 from 订单明细表 left join 商品信息表 using(主键) left join 订单总表 using(主键)这样就得到每个商品的用户购买记录排序表。
- 每个商品的用户购买记录排序表
商品 用户 按照购买时间排序 8001 102 1 8001 102 2 8002 101 1 8002 101 2 8002 105 1 8003 101 1 8003 102 1 8003 102 2 8003 104 1
3、统计复购率top3高的商品及复购率
在求商品的复购率时，注意需要分组，使用group by。
select 商品, count(distinct if(排名 >= 2,用户,null))/count(distinct 用户) as 复购率 from 每个商品的用户购买记录排序表 group by 商品 order by 复购率 desc,商品 limit 3
求解代码：
#统计零食类商品中复购率top3高的商品
select
product_id,
round(count(distinct if(ranking >= 2,uid,null))/count(distinct uid),3) rate
from(
#统计每个货物用户的购买记录；利用窗口函数根据货号、用户分组按购买时间排序
#在时间的处理上，使用datediff()和date_sub都可以
select
product_id,
uid,
row_number() over(partition by product_id,uid order by event_time) ranking
from tb_order_detail
left join tb_product_info using(product_id)
left JOIN tb_order_overall using (order_id)
where tag='零食'
and status=1
and datediff(date(
(select
max(event_time)
from tb_order_overall)),date(event_time)) < 90
) main
group by product_id
order by rate desc,product_id
limit 3
- 每个商品的用户购买记录排名表
product_id uid ranking
1 8001 102 1
2 8001 102 2
3 8002 101 1
4 8002 101 2
5 8002 105 1
6 8003 101 1
7 8003 102 1
8 8003 102 2
9 8003 104 1
三、适用场景
（1）留存率
留存率的作用
留存率是用于反映网站、互联网应用或网络游戏的运营情况的统计指标，其具体含义为在统计周期（周/月）内，每日活跃用户数在第N日仍启动该App的用户数占比的平均值。其中N通常取2、4、8、15、31，分别对应次日留存率、三日留存率、周留存率、半月留存率和月留存率。
留存率常用于反映用户粘性，当N取值越大、留存率越高时，用户粘性越高。
如何提高留存率？
- 1、营销活动刺激
营销活动能够刺激用户消费，而一个好的购物体验绝对能够大幅度提升用户的留存和复购率。所以，有很多商家会通过优惠券、满减等营销活动，以此来拉进与顾客之间的距离，实现用户的积累和留存。
常见的活动有拼团、打折等。另外可以设置限时活动，这样除了给人一种紧迫感之外，也能在短时间里聚集起用户，调动用户的活跃性，提升点击率。
- 2、签到打卡机制
签到玩法在很多app上也能看到，让用户连续签到7 天、21 天、40 天等，将会获得不同的奖品福利。
通过签到活动让用户逐渐形成每天打开app看一看的习惯。如果用户使用体验很好，那用户留存率将直线上升，从而逐渐形成稳定的客户群体。
- 3、优质内容互动
优质的内容是留住用户的关键，用户对你的内容有需求才会来使用，如果有一天内容不再优质了，用户得不到想要的东西，自然来的就少了。
- 4、承诺留存奖励：
这种方法常见于一些知识付费类的社群，设置二十天的课程，坚持听完二十天并认真做好笔记的最终退还学费或者赠送一份超值大礼品。
- 5、履行承诺：
为了吸粉，为了引流，你的宣传文案中，设置了悬念也好或是承诺了效果也好，一定抛出了不少噱头，当粉丝真的到来了之后，就是时候还债了，承诺了“进群领红包”的就要发红包了，吹嘘了“十天瘦十斤”的就要运用专业只是为用户进行介绍了。总而言之无论怎样都得让用户觉得你就是文案中宣传的那么回事，不能让用户进群之后发现你是夸大宣传的。
- 6、设置悬念：
对你承诺的内容做一份详细的时间规划，把你能提供的价值做一个详略得当的拆分，并把价值提供的时间战线控制得不长不短，最有吸引力的内容放在最开始展示，这样以来能让用户刚进群就被吸引住，并对日后的内容有很大的期待，这样以来自然不会轻易退群。
（2）复购率
复购率的作用
商品复购率指消费者对该品牌宝贝或者服务的重复购买次数，重复购买率越多，则反应出消费者对品牌的忠诚度和信赖度就越高，反之则越低。
复购率有两种计算技巧：一种是所有购买过宝贝的顾客，以每个人人为独立单位重复购买宝贝的次数；另一种是按交易计算，即重复购买交易次数与总交易次数的比值。
复购率高有哪些作用？
1、提高回头率。当新的消费者对店铺的整体情况有了一些了解，对宝贝的性价比进行分析之后，应该不会排斥对店铺的关注。这样一来，让他们再一次关注店铺，应该不会有哪些难度。
2、提高店铺的权重。老客户对店铺的宝贝有一些了解，对店铺服务也会很满意。第二次再来购买时，几乎不会给差评。多一个好评，对店铺权重的提高会有很大帮助。
3、提高访客价值。平时，店铺需要花钱做直通车、钻展以及淘宝联盟等推广，拼命吸引访客，争取提高转化率。有了老客户，一切都变得不一样了。既然老客户第二次或者第N次进店，一定是有所需求，会带来访客和成交量。说不定还会推荐新客户，带来更多的免费访客。
4、提高客单价。对于新客户来说，不太了解店铺的宝贝以及服务，不敢轻易下手。但是老客户不同，每一次看到套餐设置，复购率真的会提高。
5、如果用户丢失严峻，缺少忠诚度，粘性低，这时候就要考虑“复购率”。
如何提高复购率？
- 1、剖析用户行为数据
从购物行为来说，用户购买行为**45%**的决议都是出于习惯，未曾经过思考。怎么养成用户的购买习惯，这就需要对用户的全体概况进行了解，包括累计用户、新增用户、订单量、阅读量、购买用户数、重复购买率等等。
分析这些信息能够看出：哪些用户只阅读，从未下单，甚至连下单期望都没有；哪些用户是忠实用户：看的多，买的也多；哪些用户有大量的收藏、加购行为，但并未下单；哪些用户会有相似的购买、阅读行为等等，了解这些数据也为制定营销计划提供有利的数据支撑。
- 2、解读用户行为数据，把握用户喜爱
借助数据剖析工具，调查用户的阅读、购买、收藏等重要行为数据，然后分析用户购买了哪些商品，哪些商品看了（或许收藏了）但没购买，明确用户喜欢的商品。
- 3、根据用户行为，进行精准推荐
根据会员行为，寻觅二次出售时机。此外，还能够根据产品之间的相关性，进行产品推荐和套装，提高单个订单的出售额，添加出售时机。
- 4、满意度调查，精准提升质量
对于已经购买过商品的老用户来说，获取用户的联系手段是最简单不过的了，所以需要提升用户对于商品的用后体验，比如定期对老用户进行商品使用满意调查，根据反馈提高商品的质量和客服人员的服务质量；
- 5、关注微信账号
引导用户关注店铺或卖家的微信公众账号，以此可以第一时间获取店铺的最新消息；
- 6、针对老用户的店铺活动
在节假日设置一些假日活动，店铺活动，老用户会有折上折之类的活动；
- 7、建立奖励机制
如果新上线的商品或爆款商品，引导用户参加好评晒照片，后台设置抽奖或赠送超级会员卡等。
对于一些中小商家来说，想维护好老用户，就要送一些优惠券。当他们第一次购买物品时，送一些代金券，可以刺激他们再一次来复购。不用给予太大的优惠力度，几块钱的代金券，会让顾客感到很高兴。
2266
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
