电商数仓(ads 层)

一、ads 层介绍

对电商系统各大主题指标分别进行分析，本项目中分析了四大主题·：设备主题、会员主题、商品主题、营销主题。

二、业务术语

1、用户
用户以设备为判断标准，在移动统计中，每个独立设备认为是一个独立用户。Android 系统根据 IMEI 号，IOS 系统根据 OpenUDID 来标识一个独立用户，每部手机一个用户。

2、新增用户
首次联网使用应用的用户。如果一个用户首次打开某 APP，那这个用户定义为新增用 户；卸载再安装的设备，不会被算作一次新增。新增用户包括日新增用户、周新增用户、月 新增用户。

3、活跃用户
打开应用的用户即为活跃用户，不考虑用户的使用情况。每天一台设备打开多次会被计为一个活跃用户。

4、周(月)活跃用户
某个自然周(月)内启动过应用的用户，该周(月)内的多次启动只记一个活跃用户。

5、月活跃率
月活跃用户与截止到该月累计的用户总和之间的比例。

6、沉默用户
用户仅在安装当天(次日)启动一次，后续时间无再启动行为。该指标可以反映新增用户质量和用户与 APP 的匹配程度。

7、版本分布
不同版本的周内各天新增用户数，活跃用户数和启动次数。利于判断 APP 各个版本之间的优劣和用户行为习惯。

8、本周回流用户
上周未启动过应用，本周启动了应用的用户。

9、连续 n 周活跃用户
连续 n 周，每周至少启动一次。

10、忠诚用户
连续活跃 5 周以上的用户。

11、连续活跃用户
连续 2 周及以上活跃的用户。

12、近期流失用户
连续 n(2<=n<=4)周没有启动应用的用户。(第 n+1 周没有启动过)

13、留存用户
某段时间内的新增用户，经过一段时间后，仍然使用应用的被认作是留存用户；这部分用户占当时新增用户的比例即是留存率。
例如，5 月份新增用户 200，这 200 人在 6 月份启动过应用的有 100 人，7 月份启动过 应用的有 80 人， 8 月份启动过应用的有 50 人；则 5 月份新增用户一个月后的留存率是 50%， 二个月后的留存率是 40%，三个月后的留存率是 25%。

14、用户新鲜度
每天启动应用的新老用户比例，即新增用户数占活跃用户数的比例。

15、单次使用时长
每次启动使用的时间长度。

16、日使用时长
累计一天内的使用时间长度。

17、启动次数计算标准 IOS 平台应用退到后台就算一次独立的启动；Android 平台我们规定，两次启动之间的 间隔小于 30 秒，被计算一次启动。用户在使用过程中，若因收发短信或接电话等退出应用 30 秒又再次返回应用中，那这两次行为应该是延续而非独立的，所以可以被算作一次使用 行为，即一次启动。业内大多使用 30 秒这个标准，但用户还是可以自定义此时间间隔。

三、设备主题

3.1 活跃设备数(日、周、月)

1、需求
日活：当日活跃的设备数
周活：当周活跃的设备数
月活：当月活跃的设备数

2、建表

drop table if exists ads_uv_count; 
create external table ads_uv_count( 
	`dt` string COMMENT '统计日期', 
	`day_count` bigint COMMENT '当日用户数量', 
	`wk_count` bigint COMMENT '当周用户数量', 
	`mn_count` bigint COMMENT '当月用户数量', 
	`is_weekend` string COMMENT 'Y,N是否是周末,用于得到本周最终结果',
	`is_monthend` string COMMENT 'Y,N 是否是月末,用于得到本月最终结果' 
) 
COMMENT '活跃设备数' 
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_uv_count/';

3、导入数据

insert into table ads_uv_count 
select 
	'2020-03-10' dt, 
	daycount.ct, 
	wkcount.ct, 
	mncount.ct, 
	if(date_add(next_day('2020-03-10','MO'),-1)='2020-03-10','Y','N'), 
	if(last_day('2020-03-10')='2020-03-10','Y','N') 
from 
( 
	select 
		'2020-03-10' dt, 
		count(*) ct 
	from 
		dwt_uv_topic 
	where 
		login_date_last='2020-03-10' 
) daycount 
join 
( 
	select 
		'2020-03-10' dt, 
		count (*) ct 
	from 
		dwt_uv_topic 
	where 
		login_date_last>=date_add(next_day('2020-03-10','MO'),-7) and login_date_last<= date_add(next_day('2020-03-10','MO'),-1) 
) wkcount 
on daycount.dt=wkcount.dt 
join 
( 
	select 
		'2020-03-10' dt, 
		count (*) ct 
	from 
		dwt_uv_topic 
	where 
		date_format(login_date_last,'yyyy-MM')=date_format('2020-03-10','yyyy-MM') 
)mncount on daycount.dt=mncount.dt;

3.2 每日新增设备

1、建表

drop table if exists ads_new_mid_count; 
create external table ads_new_mid_count ( 
	`create_date` string comment '创建时间', 
	`new_mid_count` BIGINT comment '新增设备数量' 
) COMMENT '每日新增设备信息数量' 
row format delimited fields terminated by '\t' 
location '/warehouse/gmall/ads/ads_new_mid_count/';

2、加载数据

insert into table ads_new_mid_count 
select 
	login_date_first, 
	count(*) 
from 
	dwt_uv_topic 
where 
	login_date_first='2020-03-10' 
group by login_date_first; 

3.3 沉默用户数

1、需求
沉默用户：只在安装当天启动过，且启动时间是在 7 天前。

2、建表

drop table if exists ads_silent_count; 
create external table ads_silent_count( 
	`dt` string COMMENT '统计日期', 
	`silent_count` bigint COMMENT '沉默设备数' 
) 
row format delimited fields terminated by '\t' 
location '/warehouse/gmall/ads/ads_silent_count';

3、加载数据

insert into table ads_silent_count 
select 
	'2020-03-15', 
	count(*) 
from 
	dwt_uv_topic 
where 
	login_date_first=login_date_last and login_date_last<=date_add('2020-03-15',-7);

3.4 本周回流用户

1、需求
本周回流用户：上周未活跃，本周活跃的设备，且不是本周新增设备。

2、建表

drop table if exists ads_back_count; 
create external table ads_back_count( 
	`dt` string COMMENT '统计日期', 
	`wk_dt` string COMMENT '统计日期所在周', 
	`wastage_count` bigint COMMENT '回流设备数' 
) 
row format delimited fields terminated by '\t' 
location '/warehouse/gmall/ads/ads_back_count';

3、加载数据

insert into table ads_back_count 
select 
	'2020-03-15', 
	'1',
	count(*) 
from 
( 
	select 
		mid_id 
	from 
		dwt_uv_topic 
	where 
		login_date_last>=date_add(next_day('2020-03-15','MO'),-7) 
		and login_date_last<= date_add(next_day('2020-03-15','MO'),-1) 
		and login_date_first<date_add(next_day('2020-03-15','MO'),-7) 
) current_wk 
left join 
( 
	select 
		mid_id 
	from 
		dws_uv_detail_daycount 
	where 
		dt>=date_add(next_day('2020-03-15','MO'),-7*2) 
		and dt<= date_add(next_day('2020-03-15','MO'),-7-1) 
	group by mid_id 
) last_wk on 
current_wk.mid_id=last_wk.mid_id 
where last_wk.mid_id is null;

3.5 流失用户数

1、需求
流失用户：最近 7 天未活跃的设备。

2、建表

drop table if exists ads_wastage_count; 
create external table ads_wastage_count( 
	`dt` string COMMENT