数仓：用户行为类指标一网打尽_sql 统计某个时间段内的新沉默用户

前言

用户行为分析是对用户在产品或触点上产生的行为及行为背后的数据进行分析，通过构建用户行为数据分析体系或者用户画像，来改变产品、营销、运营决策，实现精细化运营，指导业务增长。总之，很重要。

关注公众号，回复关键字 【资料】，获取【10万字大数据框架面试知识点】与【大数据开发的命令手册】

先来看下用户类行为指标说明，然后下面详解常写的指标。

用户活跃

描述

判断业务健康程度和发展趋势的一个重要指标。

业务指标场景举例

最近一个周/月内，有过连续活跃n天的人数。

最近一周/月内，每个用户的平均活跃天数。

最近一周/月内，最大沉默天数超n天的用户数。

最近七天内连续三天活跃用户数。

分析思路

以日明细，获取周，月用户明细。

活跃用户按天明细表 dws_uv_detail_day。

create  table dws_uv_detail_day
(
    `mid_id` string COMMENT '设备唯一标识',
    `user_id` string COMMENT '用户标识', 
    `app_time` string COMMENT '客户端日志产生时的时间',
   	. . . . . . (省略其他字段)
)
partitioned by(dt string)
. . . . . .
1
2
3
4
5
6
7
8
9

活跃用户按周明细表 dws_uv_detail_wk。

create table dws_uv_detail_wk( 
    `mid_id` string COMMENT '设备唯一标识',
    `user_id` string COMMENT '用户标识', 
    `app_time` string COMMENT '客户端日志产生时的时间',
    `monday_date` string COMMENT '周一日期',
    `sunday_date` string COMMENT  '周日日期' ,
    . . . . . . (省略其他字段)
) 
PARTITIONED BY (`wk_dt` string)
. . . . . .
1
2
3
4
5
6
7
8
9
10

案例

最近七天内连续三天活跃用户数思路。

（1）查询出最近7天的活跃用户，并对用户活跃日期进行排名。

（2）计算用户活跃日期及排名之间的差值。

（3）对同用户及差值分组，统计差值个数。

（4）将差值相同个数大于等于3的数据取出，然后去重，即为连续3天及以上活跃的用户数。

select
    '2022-01-04',
    concat(date_add('2022-01-04',-6),'_','2022-01-04'),
    count(*)
from
(
    select uid_id
    from
    (
        select uid_id      
        from
        (
            select 
                uid_id,
                date_sub(dt,rank) date_diff
            from
            (
                select 
                    uid_id,
                    dt,
                    rank() over(partition by uid_id order by dt) rank
                from dws_uv_detail_day
                where dt>=date_add('2022-01-04',-6) and dt<='2022-01-04'
            )t1
        )t2 
        group by uid_id,date_diff
        having count(*)>=3
    )t3 
    group by uid_id
)t4;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

用户（设备）新增

描述

用户第一次打开应用，那这个用户定义为新增用户。

业务指标场景举例

日新增用户。

周新增用户。

月新增用户。

分析思路

每日活跃设备 left join 每日新增产生的结果，每日新增的设备id为空，则为新增。

案例

每日新增用户（设备）

dws_uv_detail_day:每日活跃用户表

dws_new_mid_day:每日新增设备表

select  
    ud.mid_id,
    ud.user_id
from dws_uv_detail_day ud left join dws_new_mid_day nm on ud.mid_id=nm.mid_id
where ud.dt='2022-01-04' and nm.mid_id is null;
1
2
3
4
5
6
7
8
9

用户留存

描述

用户留存：某段时间内的新增活跃用户，经过一段时间后，有继续访问或者使用应用被称为留存用户。

留存可以看成是一个用户在今天出现，在明天也出现，那就是次日留存。

一个用户在今天出现，在后天出现，那就是2日留存。

留存率：留存用户占新增活用户的比例。

业务指标场景举例

次日留存率

3日留存率

7日留存率

分析思路

统计2022-01-04日，以统计2022-01-03日留存率为业务需求分析：

2022-01-03设备留存率= 01-03日新增设备 且 01-04日活跃的 占 01-03日新增设备。

（1） 01-03日新增设备

01-04日活跃表 left join 每日新增表，新增表id为null的为新增设备。

（2）01-03日新增设备 且 01-04日活跃的

01-03日新增 join 01-04的活跃。

（3）代入上面公式

案例

计算前1天新用户访问留存明细

dws_uv_detail_day:每日活跃用户表

dws_user_retention_day：每日留存用户明细表

dws_new_mid_day:每日新增设备表

select  
    nm.mid_id mid_id,
    nm.user_id user_id,
    ud.dt dt,
    1 retention_day // 留存天数
from dws_uv_detail_day ud join dws_new_mid_day nm 
        on ud.mid_id =nm.mid_id 
where ud.dt='2022-01-04' and nm.create_date=date_add('2022-01-04',-1)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

沉默用户

描述

指的是只在安装当天启动过，且启动时间是在一周/月（某段时间）前。

分析思路

（1）按照设备id对日活表分组

（2）过滤只是在启动当天用过的

（3）时间在某个时间前的

案例

7日沉默用户（设备）数。

dws_uv_detail_day:每日活跃用户表

select 
    '2022-01-04' dt,
    count(*) silent_count
from 
(
    select mid_id
    from dws_uv_detail_day
    where dt<='2022-01-04'
    group by mid_id
    having count(*)=1 and min(dt)<=date_add('2022-01-04',-6)
) t1;
1
2
3
4
5
6
7
8
9
10
11
12
13

回流用户

描述

相当于回头客的意思，以前用过某物，过一段时间再次使用某物，这样的用户就是回流用户。用户已经记录n日以及n日以上未有登录（即先认定这部分用户已经流失），而在今天有登录，那在今天这部分用户称之为回流用户。

业务指标场景举例

（时间周期）回流=（时间周期活跃-（时间周期新增-（上个时间周期）活跃

如：本周回流=本周活跃-本周新增-上周活跃。

分析思路

本周活跃 left join 本周新增 left join 上周活跃。

条件是：本周新增id为null，上周活跃id为null。

案例

本周回流

dws_uv_detail_wk:每周活跃用户表

dws_new_mid_day:每日新增设备表

mid_id：设备id

select 
   '2022-01-04' dt,
   concat(date_add(next_day('2022-01-04','MO'),-7),'_',date_add(next_day('2022-01-04','MO'),-1)) wk_dt,
   count(*)
from 
(
    select t1.mid_id
    from 
    (
        select	mid_id
        from dws_uv_detail_wk
        where wk_dt=concat(date_add(next_day('2022-01-04','MO'),-7),'_',date_add(next_day('2022-01-04','MO'),-1))
    )t1
    left join
    (
        select mid_id
        from dws_new_mid_day
        where create_date<=date_add(next_day('2022-01-04','MO'),-1) and create_date>=date_add(next_day('2022-01-04','MO'),-7)
    )t2
    on t1.mid_id=t2.mid_id
    left join
    (
        select mid_id
        from dws_uv_detail_wk
        where wk_dt=concat(date_add(next_day('2022-01-04','MO'),-7*2),'_',date_add(next_day('2022-01-04','MO'),-7-1))
    )t3
    on t1.mid_id=t3.mid_id
    where t2.mid_id is null and t3.mid_id is null
)t4;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

流失用户

描述

最近n天（自己定义）未登录我们称之为流失用户。

案例

流失用户数：最近7天未登录用户。

dws_uv_detail_day:每日活跃用户表

select
     '2022-01-04',
     count(*)
from 
(
    select mid_id
from dws_uv_detail_day
    group by mid_id
    having max(dt)<=date_add('2022-01-04',-7)
)t1;
1
2
3
4
5
6
7
8
9
10
11
12

浏览数与访客数

浏览量（PV）：网站各页面被查看的次数。用户多次打开或刷新同一个页面，该指标值累加。

访客数(UV)：网站各页面的访问人数。所选时间段内，同一访客多次访问会进行去重。

这两个指标没啥说的，count取值或者去重count即可。

用户行为漏斗分析

描述

举例说明订单转化率

访问到下单转化率 = 下单人数 / 日活跃人数

下单到支付转化率 = 支付人数 / 下单人数

SQL

dws_uv_detail_day:每日活跃用户表

dws_user_action:用户行为表
select 
    '2022-01-04',
    uv.day_count 日活跃人数,
    ua.order_count 下单数,
    cast(ua.order_count/uv.day_count as  decimal(10,2)) 访问到下单转化率,
    ua.payment_count 支付人数,
    cast(ua.payment_count/ua.order_count as  decimal(10,2)) 下单到支付转化率
from  
(
select 
        sum(if(order_count>0,1,0)) order_count,
        sum(if(payment_count>0,1,0)) payment_count
    from dws_user_action
where dt='2022-01-04'
group by dt
)ua 
join 
(select 
	dt ,
	count(*)  day_count 
	from dws_uv_detail_day 
	where dt='2022-01-04' group by dt
	)
 uv on uv.dt=ua.dt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27