网络知识 娱乐 2021_lg_03.SQL实战:如何用sql计算用户留存问题

2021_lg_03.SQL实战:如何用sql计算用户留存问题

如何用sql计算用户留存问题

一、留存

1 .留存的含义:

留存:指基准日到APP的用户在之后的n日当天返回APP的人数;

留存率 = 基准日之后的n天当日返回的用户数 / 基准日的用户数 * 100%

或者指基准日产生某个行为的用户在之后的第一天,第二天,第三天……第n天的当天再次产生该行为的 人数。

2.留存的意义

留存代表一个用户愿意再次使用你的产品;而一个产品能够被用户再次使用,意味着这个产品是能够满 足用户长期需求的,能够让用户产生一定粘性的产品。

如今互联网产品大多为免费产品,依靠持续的广告转化、用户持续的购买转化、用户持续的会员付费来 维持收益。

那么,怎样才能达到持续的广告转化?持续的购买转化?持续的会员付费?

广告转化、购买、会员付费,我们都需要用户来完成。那么持续的广告转化、购买、会员付费,我们就 需要每天都有一定量的用户来完成以上过程,也就是说我们需要我们的产品每天都是有活跃用户的。

在这里插入图片描述

那么从活跃用户的构成,我们就知道怎么维持我们的活跃用户规模:一方面持续有新用户流入,另一方 面持续有留存用户留存;

那么为什么留存用户重要?部分有过从业经验的同学应该知道,产品的自然新增用户是比较少的,如果 我们仅靠产品自然用户增长,那么必然我们的活跃用户量级会非常小,虽然能够获得收益,但是这部分 收益很可能养不起我们的公司;但是如果我们想要用购买新用户的方式获得持续的较大规模的用户池, 又会产生比较大的成本,这样做容易亏本。所以我们需要新增留存两手抓;想方设法留住我们新增的用 户,让其长期地在我们的产品内产生价值,这样做,比起单做新增,在成本和效果上都是要好的。

3.留存的实际应用

留存的主要应用场景有两个:
一个是产品整体视角的持续留存情况;也就是我们需要定期分析用户在产品的留存是否维持在一个正常 的范围,有没有突然地涨跌情况,如果有的话问题出在哪里,为什么会出现这样的问题,如何解决留存 异常的问题。遇到这类问题首先需要确定的是整体用户的留存异常还是个别群体用户的留存异常,如果 是整体用户的留存异常,我们需要分析我们的产品是否发生异常,可以通过用户的行为路径确定异常产 品位置;如果是个别用户的留存异常,需要通过用户拆解的方法来定位有异常的用户群体,然后通过这 类用户的特征来分析问题原因;

一个是新增用户视角的每批新增用户留存情况。一般出现在计算投放ROI的场景,也就是我们投放一批 用户,能够给我们带来多少收益。我们会用这些用户的“整个生命周期能够带来的收入”(LTV)去除以 “投放时的支出”来计算我们的ROI。“整个生命周期能够带来的收入”计算的是引入一批新用户,当用户完 全流失时,这部分用户所带来的所有收入。但是有时我们需要提前预估新用户在整个生命周期能够带来 的收入,我们就需要首先用留存来估计用户的生命周期,然后用估计出的生命周期再去计算用户的整个 生命周期能够带来的收入。

4.什么企业不关心留存

需要用户产生持续活跃,持续转化,持续消费的企业都关心用户的留存。那么,什么样的企业不关心用 户留存?

部分线下企业存在数据难以获取的问题,所以对留存关注比较少,比如线下的商场,除非用户产生购买 行为,否则很难探知用户在什么情况下有回流;
部分存在一锤子买卖的企业也不太关心留存,比如说用户在一次买断商品后再也不会产生后续购买或价 值转化行为,这类企业也不怎么关注留存。比如PC单机游戏;比如一次付费的工具类产品(线上线下类 工具都存在这个情况);

问题:

1.计算某APP的每日留存数据。用SQL分解留存计算,每步用视图建立
如何计算留存:

首先我们的目的是建立如下的留存表:“用户每日留存率统计表”

在这里插入图片描述

大家可以看到这个留存表由以下几个字段构成:

dates_a 计算留存的基准日日期。这个基准日可以为任意一天,如果基准日就是今天,也就是没有“之后 的第一天”、“之后的第二天”……等等天的数据,那那几天的留存可以以0、或者空值代替;

device_v 基准日当日的活跃用户数。不同产品的活跃用户定义不同,在这里,我们将活跃用户定义为在 当天有过打开APP的用户;

day_1 次日留存率,也就是“基准日之后的第一天”的用户的回访比例。

day_2 2日留存率,也就是“基准日之后的第二天”的用户的回访比例。

day_3 3日留存率,也就是“基准日之后的第三天”的用户的回访比例。

day_7 7日留存率,也就是“基准日之后的第七天”的用户的回访比例。
也就是说:这个表格,表达的是:每天对应多少活跃用户,每天的活跃用户对应的次留率、2日留率、3 日留率、7日留率是多少。我们能从这个表格中可以看出我们产品的用户活跃程度,以及产品对当日活跃 用户的短期、长期粘性情况。

而我们的原数据,长这样:

在这里插入图片描述

是一个每日app活跃用户表。其字段分为两列:

user_id 即当日活跃的用户的id

dates 即用户活跃的日期
那我们如何能够根据这个“每日活跃表”,做出上面那个“用户每日留存率统计表”呢?
关键的两个步骤:

  1. 我们需要将每日活跃的用户数据与之后几日该用户的活跃数据连接起来;这样,我们就能知道这个
    用户在之后的哪一天有回访行为;

  2. 通过SQL的数据列联功能,也就是“聚合函数(case when 条件)"的方式,将满足“基准日之后的第一
    天”、“基准日之后的第二天”……的用户数据统计出来,最后再通过基准日的用户数,计算用户留存
    率;

之后,就是引导大家如何将以上两个过程实现。

1.0.建立用户活跃日期表(5分)

表名:temp_user_act

字段:

字段名 字段类型 字段说明

user_id int 用户id

dates date 活跃日期
1.1.用用户活跃日期表做自连接,连接方式使用左连接,连接字段使用“用户id”字段,保留两表的用户id 与两表的日期(20分)

/*
1.1.用用户活跃日期表做自连接,连接方式使用左连接,连接字段使用“用户id”字段,保留两表的用户id 与两表的日期(20分) 
 */
 
 
DROP TABLE if EXISTS temp_user_act;
CREATE TABLE temp_user_act(
user_id int COMMENT '用户id',

dates date COMMENT  '活跃日期' 

);

-- 
CREATE VIEW temp_user_act_view_tem as
SELECT a.*,b.dates  dates_2 FROM temp_user_act a
LEFT JOIN temp_user_act  b on a.user_id=b.user_id;

在这里插入图片描述

1.2.筛选出右表日期大于或等于左表日期的内容(20分)

-- 1.2.筛选出右表日期大于或等于左表日期的内容(20分) 
CREATE VIEW temp_user_act_view_tem_2 as
SELECT * FROM temp_user_act_view_tem
WHERE dates_2>dates;

在这里插入图片描述

1.3.计算以左表日期为基准日的当日用户数,第二日回访用户数,第三日回访用户数,第四日回访用户 数,第八日回访用户数;(20分)

提示
提示:

datediff(B,A)=1 表示日期B为日期A之后的一天。

count(distinct case when datediff(B,A)=1 then uid else null end) 或者 count(distinct if(
datediff(B,A)=1,uid,null)) 就是求出“日期B为基准日A之后的第一天”的用户数计数

SELECT 
dates,
count(user_id) device_v,
count(distinct case when datediff(dates_2,dates)=1 then  
user_id else null end) day_1,
count(distinct case when datediff(dates_2,dates)=2 then  
user_id else null end) day_3,
count(distinct case when datediff(dates_2,dates)=3 then  
user_id else null end) day_4,
count(distinct case when datediff(dates_2,dates)=7 then  
user_id else null end) day_8

FROM  temp_user_act_view_tem_2
GROUP BY dates ORDER BY dates;

在这里插入图片描述

1.4.利用上述数据计算出每日的当日用户数以及次日留存率,二日留存率,三日留存率,7日留存率(率需 要使用百分比表示结果);(在下一部分的案例中,我们会给大家介绍一个一次性完成的留存计 算方法,给大家加深留存计算的印象。)

SELECT dates,device_v,
CONCAT(ROUND((day_1/device_v)*100,2),"%") day_1,
CONCAT(ROUND((day_3/device_v)*100,2),"%") day_2,
CONCAT(ROUND((day_4/device_v)*100,2),"%") day_3,
CONCAT(ROUND((day_8/device_v)*100,2),"%") day_7
FROM temp_user_act_view_tem_3;

在这里插入图片描述

1.5(拔高思路)*.求出每日的次留与次留的周环比;

注:
周环比:周环比适用于以周为周期变动的数据,用于观察每天的数据较上周同一天的数据变化。
现有互联网数据多以周为周期变动,一周内每一天的数据都有其特点:

如工作类APP,周一会是每一周的高峰,然后向周五逐渐递减,周五到周六又会有一个锐减的过 
 程,周日与周六趋于平缓; 

如娱乐类APP,周一会较周末锐减,然后向周四逐渐递减,周五会有一个跃升的过程,周六会再次 
 跃升,周日会较周六下降; 

一般遇到这样的变动趋势,如果单纯的分析每一天较上一天的变动情况,就容易分析出错误结论。所 以,在我们分析带有周期类数据时,一定要先将周期提炼出来,使用周期的环比数据做分析,会得出更 有价值,更加准确的结论。

1.计算方式:

假如今日为周一,那么周环比=(本周一数据 - 上周一数据) / 上周一数据 * 100%
2.没有上周对应日期的数据的部分可以为空;


--  假如今日为周一,那么周环比=(本周一数据 - 上周一数据) / 上周一数据 * 100% 
CREATE VIEW temp_user_act_view_tem_week_1 as
SELECT *,WEEK(dates) week_1,WEEK(dates_2) week_2 FROM  temp_user_act_view_tem_2;


CREATE VIEW temp_user_act_view_tem_week_P as
SELECT 
WEEK_1,
count(DISTINCT user_id) WEEK_count  ,
count(distinct case when week_2-week_1=1 then  
user_id else null end) week_p
FROM temp_user_act_view_tem_week_1
GROUP BY WEEK_1 ORDER BY week_1
;

SELECT WEEK_1,WEEK_count , 
CONCAT(ROUND((week_p/WEEK_count)*100,2),"%") week_p
FROM temp_user_act_view_tem_week_P;

在这里插入图片描述

二(拔高)作者活跃行为统计

如今的大多数内容APP都是以平台模式运营的。

什么叫做平台模式?
比如我们现在能接触到的大多数内容产品“抖音”、“Bilibili”、“微博”、“今日头条”等等。我们使用到的这个 APP,是由APP的制作公司提供的,也就是这个APP会有几个主要的页面,每个页面怎么引导用户进入内 容页面,播放内容的方式有哪些、内容间切换会用什么方式等等,都是我们的APP制作公司决定的;而
我们在这个APP里看到的大多数内容,都是由内容作者提供的。

为了能够让我们的平台能够有持续的内容产出,我们就需要分析作者的活跃行为。主要为我们的作者运
营提供有力帮助。一方面让我们找出活跃作者,增加政策扶持;一方面让我们找到快要流失的作者,及
时召回这部分作者、挽回损失。

某短视频公司有作者发布视频统计表如下:
表名:temp_author_act

字段:

字段名 字段类型 字段说明

dates date 发布日期

author_id varchar(5) 作者id
2.1.请求用SQL出作者的最近三个月内的最大断更天数、平均断更天数和最大持续更新天数;(5分) 名词解释:
持续更新天数:如果一个作者在某几天中每一天都有更新,那么这段时间的天数称为这个作者的持续更 新天数。
断更天数:如果一个作者两次更新中间隔了几天没有更新,那么这几天的天数称为这个作者的断更天
数。

	-- 	请求用**SQL**出作者的最近三个月内的最大断更天数、平均断更天数和最大持续更新天数;(**5**分)  
DROP view if EXISTS temp_author_act_1;
CREATE view temp_author_act_1 as
SELECT 
a.author_id,
dates,LEAD(dates,1) over(PARTITION by author_id ORDER BY dates) lead_1  FROM temp_author_act  as a;

DROP view if EXISTS temp_author_act_2;
CREATE view temp_author_act_2 as
SELECT 
author_id,
MONTH,
max(月最大断更更新天数) over(PARTITION by author_id ORDER BY MONTH rows BETWEEN 2 preceding AND current ROW)  三月内最大断更更新天数,
sum(月最大断更更新天数总和) over(PARTITION by author_id ORDER BY MONTH rows BETWEEN 2 preceding AND current ROW)  /
sum(月最断更更新天数数量) over(PARTITION by author_id ORDER BY MONTH rows BETWEEN 2 preceding AND current ROW)  平均断更天数,
max(月最大连续更新天数) over(PARTITION by author_id ORDER BY MONTH rows BETWEEN 2 preceding AND current ROW)  三月内月最大连续更新天数
FROM
	(	
SELECT 
author_id,
MONTH,
max(case WHEN  DATED_IFF=1 then DATED_IFF_sum end) 月最大连续更新天数,
max(case WHEN  DATED_IFF!=1 then DATED_IFF_sum end) 月最大断更更新天数,
sum(case WHEN  DATED_IFF!=1 then DATED_IFF_sum end) 月最大断更更新天数总和,
count(case WHEN  DATED_IFF !=1 then DATED_IFF_sum end) 月最断更更新天数数量
from
		(
		SELECT 
			author_id,
			dates,
			lead_1,
			MONTH,
			DATED_IFF,
			sum(DATED_IFF) over(PARTITION by author_id,MONTH,dated_iff ORDER BY dates) DATED_IFF_sum
		FROM 
		(
		SELECT
		author_id,
			dates,
			lead_1,
		LEFT(dates,7) MONTH,
		DATEDIFF( lead_1, dates ) DATED_IFF
		FROM
		temp_author_act_1 WHERE DATEDIFF( lead_1, dates ) >=1 ) a )b
		GROUP BY author_id,MONTH
		) c
		
		;
		
SELECT 
author_id,
MONTH,
三月内最大断更更新天数,
平均断更天数,
if(ISNULL(三月内月最大连续更新天数),0,三月内月最大连续更新天数) 三月内月最大连续更新天数 FROM temp_author_act_2;

在这里插入图片描述

2.2.运营人员需要对作者做电话访问,需要你用SQL求出每位作者在最大断更天数时对应的日期范围。
用于访问该日期内的断更原因。(5分)


-- 2.2

CREATE view temp_author_act_1 as
SELECT 
a.author_id,
dates,LEAD(dates,1) over(ORDER BY dates) lead_1  FROM temp_author_act  as a;


SELECT a.author_id, max_DATED_IFF ,time_to FROM 
	(SELECT
		author_id,

			max(
			DATEDIFF( lead_1, dates )) max_DATED_IFF 
		FROM
			temp_author_act_1 
		GROUP BY
			author_id ) a
LEFT JOIN (

SELECT
	author_id,
	CONCAT(dates,'———',lead_1) time_to,
 DATEDIFF( lead_1,dates ) DATED_IFF 
FROM 
	temp_author_act_1) b on a.author_id=b.author_id and a.max_DATED_IFF =b.DATED_IFF
	ORDER BY max_DATED_IFF desc;
	

在这里插入图片描述