电商指标之用户留存率

别这么骄傲

已于 2022-10-24 00:12:15 修改

阅读量1k

点赞数 2

分类专栏： hive SQL实战文章标签： java 开发语言 1024程序员节

于 2022-10-23 23:57:12 首次发布

本文链接：https://blog.csdn.net/weixin_43753599/article/details/127476435

版权

hive 同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

SQL实战

4 篇文章 0 订阅

订阅专栏

5.3 实际开发的做法（思路和上面差不多，不过更灵活）

1.前言

计算原则：一般看到什么什么率啥的，指标率=a/b,都要明确分子，分母代表的含义，然后在相除。

新增留存率具体是指留存用户数与新增用户数的比值。

2、用到的函数

1.datediff(time1，time2)：日期比较函数，time1-time2，返回一直int（整数）

select datediff('2020-06-14 11','2020-06-10');

返回结果：  4

2.if(表达式,a,b):当表达式条件为真，返回a，否则返回b的值

 select if(1=2,100,200);  -->200
 select if(1=1,100,200);  -->100

if(time='2020-06-14',1,0): 表达式为真，返回1，否则返回0；

3.  cast():类型转换函数

select cast(1.0001 as int)  -->1

cast(id as string)    -->将id字段的类型改成 string类型

3、指标理解

留存率理解：2020-06-14新增100个用户，

1日之后（2020-06-15）这100人中有80个人活跃了，

那2020-06-14的1日留存数则为80，2020-06-14的1日留存率则为80%（80/100）。

4、创建表和导入数据

4.1目标表

1.建表（目标表）
DROP TABLE IF EXISTS ads_user_retention;
CREATE EXTERNAL TABLE ads_user_retention
(
    `dt`              STRING COMMENT '统计日期',
    `create_date`     STRING COMMENT '用户新增日期',
    `retention_day`   INT COMMENT '截至当前日期留存天数',
    `retention_count` BIGINT COMMENT '留存用户数量',
    `new_user_count`  BIGINT COMMENT '新增用户数量',
    `retention_rate`  DECIMAL(16, 2) COMMENT '留存率'
) COMMENT '用户留存率'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_user_retention/';

4.2 数据来源表

数据来源表：见另一篇博客

留存率数据来源_别这么骄傲的博客-CSDN博客

4.2.1 用户登录历史表

dws_user_user_login_td：是用户域用户粒度登录历史至今汇总表，

如图所示：以为是历史累计表，当我们拿14号分区数据时，最后登录时间为10号的用户数据也包括在里面。

4.2.2 用户注册表

dwd_user_login_inc：用户域用户注册事务事实表，

5、sql解析

5.1一般思路做法

下面SQL中求06-14留存的用户数的图解

2020-06-13日 的 1 日留存率:在13号新增的用户，而且在14号活跃（有登录）的用户
留存率= 当天的留存用户数/昨天新增用户数
步骤：
（1）求06-13新增用户的数据 a
（2）求06-14留存的用户数  b
（3）做差 b/a


1.求06-13新增用户的数据
select user_id 
from dwd_user_register_inc
where dt='2020-06-13'

2.求06-14留存的用户数
select count(*)
from
(
	select user_id 
	from dwd_user_register_inc
	where dt='2020-06-13'
)t1
join 
(
	select user_id
	from dws_user_user_login_td
	where dt='2020-06-14' and login_date_last='2020-06-14'
	--拿到当天的分区，而且用户最后登录时间是 06-14号的数据
)t1
on t1.user_id=t2.user_id
--两个表连接，拿到既是昨天新增，又是有今天登录的用户数据


3. 

select 
 '2020-06-13' ,
  1 ,-- 06-13号的1日留存率,
  b.cnt ,  --留存用户数,
  a.cnt ,  -- 新增用户数
  cast(b.cnt/a.cnt as decimal(16,2)) --留存率
from 
(
    select count(user_id) as cnt1
    from dwd_user_register_inc
    where dt='2020-06-13'
)a,
(
    select count(*) as cnt
    from
        (
	    select user_id 
	    from dwd_user_register_inc
	    where dt='2020-06-13'
    )t1
    join 
    (
	    select user_id
	    from dws_user_user_login_td
	    where dt='2020-06-14' and login_date_last='2020-06-14'
	--拿到当天的分区，而且用户最后登录时间是 06-14号的数据
    )t1
    on t1.user_id=t2.user_id
    --两个表连接，拿到既是昨天新增，又是有今天登录的用户数据
)b

5.2 减少部分重复计算方法

方法1中求新增用户数计算了两次

所以我们可以减少一个子查询

5.3 实际开发的做法（思路和上面差不多，不过更灵活）

思路：
(1)where筛选出用户注册表（dwd_user..）中有注册的用户。
(2)在用户历史登录表中求14号分区的数据。
(3)根据 dt 时间分组，就可以知道用户注册表中每个用户是在哪一天注册（新增）的了，
   所以count(*)时，也是统计当天分区 有用户注册的数据（新增的用户数）。
(4)if()筛选14号留存的用户数， 然后留存率=某天用户留存数/用户新增数。

！这里有个小细节：
group by dt （分组时只用来dt）, 但是在select中使用了不是分组字段的
datediff('2020-06-14',dt)，也没有报错的原因是
 -->
group by 分组后 ，一般select可以查询分组字段和max()和sum()等聚合函数，
还可以查询 使用了udf函数的分组字段，满足一进一出即可，如datediff('2020-06-14',dt)。




select
    '2020-06-14' ,
    dt ,
    datediff('2020-06-14',dt) ,
    sum(if(login_date_last='2020-06-14',1,0)) retention_count,
    count(*) new_user_count,
    cast(sum(if(login_date_last='2020-06-14',1,0))/count(*)*100 as decimal(16,2)) retention_rate
from
(
    select
        user_id,
        dt
    from dwd_user_register_inc
    where dt>=date_add('2020-06-14',-7)
    and dt<'2020-06-14'
)t1
join
(
    select
        user_id,
        login_date_last
    from dws_user_user_login_td
    where dt='2020-06-14'
)t2
on t1.user_id=t2.user_id
group by dt;