“ 上一节，我们说到了用户营销指标体系是根据AARRR漏斗模型来制定的，那么这一节，我们就来看下用户活跃、新增、存留、沉默、回流、流失等主题，相关需求，在数仓中进行计算是如何实现的？”
01 用户行为数仓中常见主题
—
用户活跃主题
用户新增主题
用户留存主题
沉默用户数
本周回流用户数
流失用户数
最近连续3周活跃用户数
最近七天内连续三天活跃用户数
02 ODS层原始数据处理思路
—
ODS层加载数据：
ODS层创建相关分区表，企业每日凌晨30分~1点进行调度，从日志加载数据到
原始数据是一条条json数据
创建分区表(line一个String字段分区）--->从日志文件中加载数据进分区
1）创建输入数据是lzo输出是text，支持json解析的分区表
hive (gmall)>
drop table if exists ods_start_log;
CREATE EXTERNAL TABLE ods_start_log (`line` string)
PARTITIONED BY (`dt` string)
STORED AS
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION '/warehouse/gmall/ods/ods_start_log';
2）加载数据
hive (gmall)>
load data inpath '/origin_data/gmall/log/topic_start/2019-02-10' into table gmall.ods_start_log partition(dt='2019-02-10');
注意：时间格式都配置成YYYY-MM-DD格式，这是Hive默认支持的时间格式
3）查看是否加载成功
hive (gmall)> select * from ods_start_log limit 2;
03 DWD层处理思路
—
创建DWD层分区表--->json对象函数(
get_json_object)解析每行的json数据进每日分区
1）建表语句
hive (gmall)>
drop table if exists dwd_start_log;
CREATE EXTERNAL TABLE dwd_start_log(
`mid_id` string,
`user_id` string,
`version_code` string,
`version_name` string,
`lang` string,
`source` string,
`os` string,
`area` string,
`model` string,
`brand` string,
`sdk_version` string,
`gmail` string,
`height_width` string,
`app_time` string,
`network` string,
`lng` string,
`lat` string,
`entry` string,
`open_ad_type` string,
`action` string,
`loading_time` string,
`detail` string,
`extend1` string
)
PARTITIONED BY (dt string)
location '/warehouse/gmall/dwd/dwd_start_log/';
hive (gmall)>
insert overwrite table dwd_start_log
PARTITION (dt='2019-02-10')
2）插入数据
select
get_json_object(line,'$.mid') mid_id,
get_json_object(line,'$.uid') user_id,
get_json_object(line,'$.vc') version_code,
get_json_object(line,'$.vn') version_name,
get_json_object(line,'$.l') lang,
get_json_object(line,'$.sr') source,
get_json_object(line,'$.os') os,
get_json_object(line,'$.ar') area,
get_json_object(line,'$.md') model,
get_json_object(line,'$.ba') brand,
get_json_object(line,'$.sv') sdk_version,
get_json_object(line,'$.g') gmail,
get_json_object(line,'$.hw') height_width,
get_json_object(line,'$.t') app_time,
get_json_object(line,'$.nw') network,
get_json_object(line,'$.ln') lng,
get_json_object(line,'$.la') lat,
get_json_object(line,'$.entry') entry,
get_json_object(line,'$.open_ad_type') open_ad_type,
get_json_object(line,'$.action') action,
get_json_object(line,'$.loading_time') loading_time,
get_json_object(line,'$.detail') detail,
get_json_object(line,'$.extend1') extend1
from ods_start_log
where dt='2019-02-10';
3）测试检查数据
hive (gmall)> select * from dwd_start_log limit 2;
04 指标计算通用处理思路
—
拿上面01点的8大主题来举个例子：
用户活跃主题：
需求：统计当日、当周、当月活跃设备数
DWS层先统计当日、当周、当月活动的每个设备明细三张表，之后在此数据基础上按表关系来统计当日、当周、当月活跃设备数整合成一张表，8个主题都是这样的思路
一个设备多个账号，会被判为是一个用户，以设备作为维度来聚合。
以用户单日访问为key进行聚合，如果某个用户在一天中使用了两种操作系统、两个系统版本、多个地区，登录不同账号，只取其中之一
将多条concat_ws('|',collect_set(urser_id))这样按mid_id(设备号)聚合后，从多条变为一条；
DWS层
明细层的日、周、月，其实是可以看成每天的账号聚合，只是将处理好之后的每天的数据，分别放当前日的日、周、月相应的分区里。以便后续计算。
计算每日活跃设备明细
1）建表语句
hive (gmall)>
drop table if exists dws_uv_detail_day;
create external table dws_uv_detail_day
(
`mid_id` string COMMENT '设备唯一标识',
`user_id` string COMMENT '用户标识',
`version_code` string COMMENT '程序版本号',
`version_name` string COMMENT '程序版本名',
`lang` string COMMENT '系统语言',
`source` string COMMENT '渠道号',
`os` string COMMENT '安卓系统版本',
`area` string COMMENT '区域',
`model` string COMMENT '手机型号',
`brand` string COMMENT '手机品牌',
`sdk_version` string COMMENT 'sdkVersion',
`gmail` string COMMENT 'gmail',
`height_width` string COMMENT '屏幕宽高',
`app_time` string COMMENT '客户端日志产生时的时间',
`network` string COMMENT '网络模式',
`lng` string COMMENT '经度',
`lat` string COMMENT '纬度'
)
partitioned by(dt string)
stored as parquet
location '/warehouse/gmall/dws/dws_uv_detail_day'
;
2）数据导入
以用户单日访问为key进行聚合，如果某个用户在一天中使用了两种操作系统、两个系统版本、多个地区，登录不同账号，只取其中之一
hive (gmall)>
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_uv_detail_day
partition(dt='2019-02-10')
select
mid_id,
concat_ws('|', collect_set(user_id)) user_id,
concat_ws('|', collect_set(version_code)) version_code,
concat_ws('|', collect_set(version_name)) version_name,
concat_ws('|', collect_set(lang))lang,
concat_ws('|', collect_set(source)) source,
concat_ws('|', collect_set(os)) os,
concat_ws('|', collect_set(area)) area,
concat_ws('|', collect_set(model)) model,
concat_ws('|', collect_set(brand)) brand,
concat_ws('|', collect_set(sdk_version)) sdk_version,
concat_ws('|', collect_set(gmail)) gmail,
concat_ws('|', collect_set(height_width)) height_width,
concat_ws('|', collect_set(app_time)) app_time,
concat_ws('|', collect_set(network)) network,
concat_ws('|', collect_set(lng)) lng,
concat_ws('|', collect_set(lat)) lat
from dwd_start_log
where dt='2019-02-10'
group by mid_id;
3）查询导入结果
hive (gmall)> select * from dws_uv_detail_day limit 1;
hive (gmall)> select count(*) from dws_uv_detail_day;
计算每周活跃设备明细：
其它的和每日一样，不同点就是日期的处理是个范围，而且插入数据时，要将具体日期计算成分区
备注：日期范围等于当前日期的上个一整个星期
3）查询导入结果
hive (gmall)> select * from dws_uv_detail_wk limit 1;
hive (gmall)> select count(*) from dws_uv_detail_wk;
计算每月活跃设备明细：
其它的和每日一样，不同点就是日期的按月格式处理成月分区
3）查询导入结果
hive (gmall)> select * from dws_uv_detail_mn limit 1;
hive (gmall)> select count(*) from dws_uv_detail_mn ;
DWS层
计算活跃设备数，是把日、周、月三个指标全放一张表里加工，且用上面处理出来的三个明细表进行关联
2）导入数据
hive (gmall)>
insert into table ads_uv_count
select
'2019-02-10' dt,
daycount.ct,
wkcount.ct,
mncount.ct,
if(date_add(next_day('2019-02-10','MO'),-1)='2019-02-10','Y','N') ,
if(last_day('2019-02-10')='2019-02-10','Y','N')
from
(
select
'2019-02-10' dt,
count(*) ct
from dws_uv_detail_day
where dt='2019-02-10'
)daycount join
(
select
'2019-02-10' dt,
count (*) ct
from dws_uv_detail_wk
where wk_dt=concat(date_add(next_day('2019-02-10','MO'),-7),'_' ,date_add(next_day('2019-02-10','MO'),-1) )
) wkcount on daycount.dt=wkcount.dt
join
(
select
'2019-02-10' dt,
count (*) ct
from dws_uv_detail_mn
where mn=date_format('2019-02-10','yyyy-MM')
)mncount on daycount.dt=mncount.dt
;
3）查询导入结果
hive (gmall)> select * from ads_uv_count ;
用户新增主题：
ODS层日志表按设备号聚合-->DWS每日活跃设备明细leftjoin 新增表设备 号为空值的---》DWS层(每日新增设备明细表)---》ADS层(每日新增设备表)
用户留存主题：
前1天每日活跃设备号 = 新增表设备号 (join)
前2天每日活跃设备号 = 新增表设备号(join)
前3天每日活跃设备号 = 新增表设备号(join)
用户留存概念：
留存率：
分母：每日活跃left join每日新增 设备号相等
分子：10日的新增 join11日的活跃 (data_add取前一天) 设备号相等
DWS层（1,2,3,n天留存用户明细表） 计算1，2,3天也就是在每日基础上，用日期函数data_add -1 -2 -3这样，然后把三份数据union all
1）建表语句
hive (gmall)>
drop table if exists dws_user_retention_day;
create external table dws_user_retention_day
(
`mid_id` string COMMENT '设备唯一标识',
`user_id` string COMMENT '用户标识',
`version_code` string COMMENT '程序版本号',
`version_name` string COMMENT '程序版本名',
`lang` string COMMENT '系统语言',
`source` string COMMENT '渠道号',
`os` string COMMENT '安卓系统版本',
`area` string COMMENT '区域',
`model` string COMMENT '手机型号',
`brand` string COMMENT '手机品牌',
`sdk_version` string COMMENT 'sdkVersion',
`gmail` string COMMENT 'gmail',
`height_width` string COMMENT '屏幕宽高',
`app_time` string COMMENT '客户端日志产生时的时间',
`network` string COMMENT '网络模式',
`lng` string COMMENT '经度',
`lat` string COMMENT '纬度',
`create_date` string comment '设备新增时间',
`retention_day` int comment '截止当前日期留存天数'
) COMMENT '每日用户留存情况'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dws/dws_user_retention_day/'
2）导入数据（每天计算前1,2,3，n天的新用户访问留存明细）
hive (gmall)>
insert overwrite table dws_user_retention_day
partition(dt="2019-02-11")
select
nm.mid_id,
nm.user_id,
nm.version_code,
nm.version_name,
nm.lang,
nm.source,
nm.os,
nm.area,
nm.model,
nm.brand,
nm.sdk_version,
nm.gmail,
nm.height_width,
nm.app_time,
nm.network,
nm.lng,
nm.lat,
nm.create_date,
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='2019-02-11' and nm.create_date=date_add('2019-02-11',-1)
union all
select
nm.mid_id,
nm.user_id ,
nm.version_code ,
nm.version_name ,
nm.lang ,
nm.source,
nm.os,
nm.area,
nm.model,
nm.brand,
nm.sdk_version,
nm.gmail,
nm.height_width,
nm.app_time,
nm.network,
nm.lng,
nm.lat,
nm.create_date,
2 retention_day
from dws_uv_detail_day ud join dws_new_mid_day nm on ud.mid_id =nm.mid_id
where ud.dt='2019-02-11' and nm.create_date=date_add('2019-02-11',-2)
union all
select
nm.mid_id,
nm.user_id ,
nm.version_code ,
nm.version_name ,
nm.lang ,
nm.source,
nm.os,
nm.area,
nm.model,
nm.brand,
nm.sdk_version,
nm.gmail,
nm.height_width,
nm.app_time,
nm.network,
nm.lng,
nm.lat,
nm.create_date,
3 retention_day
from dws_uv_detail_day ud join dws_new_mid_day nm on ud.mid_id =nm.mid_id
where ud.dt='2019-02-11' and nm.create_date=date_add('2019-02-11',-3);
2）查询导入数据（每天计算前1,2,3天的新用户访问留存明细）
hive (gmall)> select retention_day , count(*) from dws_user_retention_day group by retention_day;
ADS层计算留存比例：
总结：这一节我们讲了关于用户行为数仓中用户活跃主题、用户新增主题、用户留存主题这三个方面的指标计算通用思路。下一节我们来解决剩下的几个主题的相关指标思路。
#我是媛姐，一枚有多年大数据经验的程序媛，早期做过电商也有外贸，打过螺丝搬过砖，关注数仓，关注分析。愿你我走得更远！
