《大型综合项目-基于大数据平台的数据仓库》学习笔记（12）：埋点日志报表篇3

本项目教程笔记源自多易教育《Titan综合数据仓库与数据运营系统》，在CSDN学院有相关视频教程购买链接，大数据企业级项目实战–Titan大型数据运营系统
本项目课程是一门极具综合性和完整性的大型大数据项目实战课程，课程项目的业务背景源自各类互联网公司对海量用户浏览行为数据和业务数据分析的需求及企业数据管理、数据运营需求。
学完本课程，你将很容易就拿到大数据数仓建设或用户画像建设等岗位的OFFER

本课程项目涵盖数据采集与预处理、数据仓库体系建设、用户画像系统建设、数据治理（元数据管理、数据质量管理）、任务调度系统、数据服务层建设、OLAP即席分析系统建设等大量模块，力求原汁原味重现一个完备的企业级大型数据运营系统。

跟随项目课程，历经接近100+小时的时间，从需求分析开始，到数据埋点采集，到预处理程序代码编写，到数仓体系搭建…逐渐展开整个项目的宏大视图，构建起整个项目的摩天大厦。

---

多易教育，专注大数据培训； 课程引领市场，就业乘风破浪
多易教育官网地址
https://www.51doit.cn
多易教育在线学习平台
https://v.51doit.cn

一、【App分析】app版本升级分析

1、需求分析

源表: dws_flw_agg_s

gid,sessionid,appver,dt
gid01,s01,1.01,2019-11-27
gid01,s01,1.01,2019-11-27
gid01,s01,1.01,2019-11-27
gid01,s01,1.01,2019-11-27
gid01,s02,2.01,2019-11-27
gid01,s02,2.01,2019-11-27
gid01,s02,2.01,2019-11-27
gid01,s02,2.01,2019-11-27
gid02,s21,2.01,2019-11-27
gid03,s31,2.01,2019-11-27
gid03,s32,2.01,2019-11-27
gid03,s33,2.5,2019-11-27
gid04,s41,1.01,2019-11-27
gid04,s42,2.01,2019-11-27
gid04,s43,2.5,2019-11-27

==>  预期的结果：
gid01,1.01,2.01
gid03,2.01,2.5
gid04,1.01,2.01
gid04,2.01,2.5

解释：只要一个用户在一天之内发生了一次升级，则在升级报表中有一条记录：
        日期，用户id，升级前版本，升级后版本
        6.09 ，a , 1.0 1.2
        6.09 ，a ， 1.2 2.0

2、ADS模型：ADS_APP_UPG

3、计算

1)、计算逻辑

将后一行记录中的版本，提升到前一行，来进行版本比较

create table t2(uid string,day string,ver string) 
row format delimited fields terminated by ',';

load data local inpath '/root/t2.dat' into table t2;

uid, day,    ver
a,2019-06-20,1.02
a,2019-06-20,1.02
a,2019-06-20,1.02
a,2019-06-20,2.0
a,2019-06-20,2.0
a,2019-06-20,2.5
a,2019-06-20,2.5
b,2019-06-20,1.2
b,2019-06-20,1.2
b,2019-06-20,2.5
c,2019-06-20,2.0
c,2019-06-20,2.0

==> 期待的结果
uid, 日期       升级前  升级后
a   ,2019-06-20  ,1.02  ,2.0
a   ,2019-06-20  ,2.0   ,2.5
b   ,2019-06-20  ,1.2   ,2.5

with tmp as(
select
uid,
day,
ver,
lead(ver,1,null) over(partition by uid order by ver) as ver2
from t2
)
select * from tmp where ver2>ver

2)、ETL计算开发

《详见项目代码》

二、【事件分析】交互事件概况分析

1、交互事件概念介绍

交互事件： 就是用户在app上或者网页上所做的交互行为
比如：点赞，收藏，评分，转发，点击，添加购物车，提交订单，去支付……………

2、交互事件分析整体建模

我们可以在DWD层设计一个交互事件明细表dwd_apl_itr_dtl（已建好，见8.2节）
在DWS层做两个聚合表（一个按会话聚合，一个按用户聚合）

3、DWD交互事件明细表：DWD_APL_ITR_DTL

1)、建模

交互事件明细表: DWD_APL_ITR_DTL在8.2节已经完成

        4、DWS聚合表：DWS_APL_ITR_AGS

按会话聚合的交互事件数据表

gid	sessionid	eventid	次数	省	市	区	osname	osver	appver	release_ch
g01	s01	favor	2	山西	吕梁	高粱	ios	10.02	2.05	applestore
g01	s01	adshow	5
g01	s02	favor	10

5、DWS聚合表：DWS_APL_ITR_AGU

注：此表也可以作为用户画像标签表来用（用户交互行为标签明细表）

gid	eventid	次数
g01	favor	12
g01	share	10
g02	favor	20
g02	adshow	30
g02	share	25

画像中的应用举例：
比如，画像分析中，需要一份这样的数据：
       每个人在最近3个月中，哪10种行为发生次数最多，及其次数
就可以从上述的“交互事件用户聚合表”中得出：
       将每个人的最近3个月的事件及其次数进行累加，然后对每个人取top10，即可！ |

6、ADS报表示例: 交互事件概况统计报表

需求建模：ADS_APL_ITR_OVW_CUBE
事件概况统计报表

日期	事件id	次数	人数	省	市	区	appver	下载渠道	osname	…
2012-01-13	favor	32	2
	share	35	5

建表语句：
CREATE TABLE ads_apl_itr_ovw_cube(
eventid      string,
province     string,
city         string,
district     string,
osname       string,
osver        string,
appver       string,
release_ch   string,
cnts         int,
user_cnts    int
)
partitioned by (dt string)
stored as parquet
;

《详见项目代码》

7、ADS报表示例: ADS_EVT_TOPN

求每一种事件中，发生次数最高的前100个用户

日期	事件类型	用户标识	发生次数
	adclick	g01	100
	adclick	g05	90

《详见项目代码》

三、【路径分析】访问路径分析

也可以称作：“用户行为轨迹分析”
如下示意：
G01, S01, A -> D -> F -> A -> O

分析用户在使用app/web过程中的访问行为路径规律（更具体则为：事件行为）

        1、需求分析

上图中要统计的指标有：
       某个特定步骤上，特定页面跳转到目标页面的会话数，
       某个特点步骤上，特定页面的会话数
       以及，该目标页面上发生过的会话总数

从上，可以分析出，此报表中所要计算的数据指标如下：


访问路径分析的需求变化多端，当然，最常见的需求是“页面路径”的分析，即不考虑各类交互事件，只分析用户在页面之间如何跳转！

2、DWD访问路径步骤记录表模型

表设计
页面访问路径记录表

gid	sessionid	页面id	前置页面id	访问步骤号
g01	s01	A	NULL	1
g01	s01	B	A	2
g01	s01	C	B	3
g02	s02	F	NULL	1
g02	s02	B	F	2
g02	s02	C	B	3
g03	s03	F	NULL	1
g03	s03	C	F	2
g03	s03	A	C	3

计算逻辑
源表：埋点日志流量明细表 dwd_apl_tfc_dtl
核心逻辑：将每个人的页面访问记录按时间戳排序，标记步骤号，并通过把上一行数据的pgid拉到下一行，作为当前页面的前页面id

3、DWD访问路径步骤记录表开发

源表：
流量明细表（或app埋点日志全局明细表）：DWD_APL_TFC_DTL

计算过程：
假设有如下流量请求记录：

-- 测试数据，对应的真实数据是 ： 流量明细表
g01,137001,s01,pgview,A
g01,137002,s01,pgview,C
g01,137003,s01,pgview,F
g01,137004,s01,pgview,E
g01,137005,s01,pgview,G
g01,137006,s02,pgview,A
g01,137007,s02,pgview,D
g01,137011,s02,pgview,F
g01,137012,s02,pgview,B
g01,137013,s02,pgview,C
g01,137014,s02,pgview,A
g02,137015,s03,pgview,U
g02,137016,s03,pgview,D
g02,137017,s03,pgview,F
g02,137018,s03,pgview,B
g02,137021,s03,pgview,A
g03,137022,s04,pgview,U
g03,137025,s04,pgview,X
g03,137026,s04,pgview,F
g03,137027,s04,pgview,B
g03,137028,s04,pgview,A

得到每个人的每次页面请求的顺序号：
按同一个人的同一次会话中请求时间的顺序打上row number

得到该次请求的上一个页面：
用lag over窗口函数将上一次请求的页面，拉到当前行

g01,s01,pgview,A,1 ,\N
g01,s01,pgview,C,2 ,A
g01,s01,pgview,F,3 ,C
g01,s01,pgview,E,4 ,F
g01,s01,pgview,G,5 ,E

g01,s02,pgview,A,1 ,\N
g01,s02,pgview,D,2 ,A
g01,s02,pgview,F,3 ,D
g01,s02,pgview,B,4 ,F
g01,s02,pgview,C,5 ,B
g01,s02,pgview,A,6 ,C

实现代码：

/*
事件分析主题ads层：用户访问路径记录表：dws_acc_route

结构：
  用户    会话     页面     访问顺序号     前一页面


@Author HUNTER
@Date 2019-07-26

@源表：ods_traffic_log
0: jdbc:hive2://localhost:10000> select imei,sessionid,event['url'],commit_time from ods_traffic_log t where eventtype='pg_view' limit 5;
+---------+---------------+------------------------+----------------+
|  imei   |   sessionid   |          _c2           |  commit_time   |
+---------+---------------+------------------------+----------------+
| BHGPN7  | hs4NasTIrAAA  | http://www.51doit.com/index.html  | 1560599141549  |
| D9OGFD  | OBYsMxtYFcVh  | http://www.51doit.com/learn  | 1560599141556  |
| V6WKTX  | ayRyJcjG3e9S  | http://www.51doit.com/job  | 1560599141573  |
| DXJWCX  | QCUNyRBAICuQ  | http://www.51doit.com/hadoop  | 1560599141585  |
| VLKQOP  | KvpnX3uj72Ao  | http://www.51doit.com/spark  | 1560599141608  |
+---------+---------------+------------------------+----------------+



@目标：dws_acc_route

@计算逻辑：
    顺序号： 按时间排序标记row nunber
    前一页： 按时间排序，取lag over

*/

-- 建表：
drop table if exists  dws_acc_route;
create table dws_acc_route(
uid string,
sessionid string,
url string,
sno int,
pre_url string
)
partitioned by (dt string)
stored as parquet
;


-- etl计算
insert into table dws_acc_route partition(dt='2019-06-16')
select
imei as uid,
sessionid,
event['url'] as url,
row_number() over(partition by imei,sessionid order by commit_time) as sno,
lag(event['url']) over(partition by imei,sessionid order by commit_time) as pre_url

from ods_traffic_log where dt='2019-06-16' and eventtype='pg_view'
;

4、ADS访问路径分析报表模型

页面访问路径分析表

步骤号	页面id	前页	路径会话数	步骤页面会话数	页面会话数
3	C	B	2	100
1	A	null	1
1	F	null	2
2	B	A	1
2	B	F	1
2	C	F	1
3	A	C	1

解释：以第一行为例：
该页面总会话数：比如C，是所有访问了C的会话数——100
该路径会话数：第3步访问的C，且前一页是B，符合这种路径的会话个数——2

5、ADS访问路径分析报表开发

计算逻辑：
第一步，算出 “步骤号，页面id，前页面”组合下的会话数
第二步，算出每一个页面上的总会话数
第三步，将前2步的结果 join起来即可！

建表：ADS_APL_ACC_PATH

CREATE TABLE ADS_APL_ACC_PATH(
step     int,
pgid     string,
pre_pg   string,
path_se_cnts int,
step_se_cnts int,
page_se_cnts int
)
stored as orc;

计算

select
step,
pgid,
pre_pg,
count(1) as path_se_cnts,
sum(count(1)) over(partition by step,pgid rows between unbounded preceding and unbounded following)  as step_se_cnts,
sum(count(1)) over(partition by pgid rows between unbounded preceding and unbounded following)  as page_se_cnts
from demo_path_dwd_dtl
group by step,pgid,pre_pg
;

四、【转化分析】转化漏斗统计

1、转化相关核心概念

公司有很多很多的各种类型的业务
而每一项业务往往能分成若干个操作环节

用户在业务的各个操作环节上进行操作，一步步走向业务目标（比如买单，比如注册成功，比如充值完成，比如进入充值页）
那么，一个业务的操作环节链条，就叫做这个业务的转化路径！
而路径中，每一个环节上的事件发生次数或人数，都会不同，一般是前面的环节上人数多，越往后越少，这样就引出一个概念：转化率，漏斗模型
漏斗模型： 是用来衡量一个业务转化路径效率的计算模型；它的主要要素有：业务转化路径的每一个步骤的定义！每一步上的人数、次数、金额等统计；

转化率： 漏斗模型中，一个业务步骤相对于上一个业务步骤的人数变化率！
转化率分为相对转化率和绝对转化率；
相对转化率： 一个步骤相对于上一个步骤的人数变化率
绝对转化率：一个步骤相对于初始步骤的人数变化率


强大的分析平台，应该是允许分析师自定义漏斗模型！

2、转化漏斗分析表模型

建模和计算思路

漏斗名称	步骤序号	完成人数	相对转化率	整体转化率
多易寻宝促销	1	100
多易寻宝促销	2	50	50%	50%
多易寻宝促销	3	20	40%	20%
呼朋唤友拉新	1	1000
呼朋唤友拉新	2	800
呼朋唤友拉新	3	600
呼朋唤友拉新	4	200
呼朋唤友拉新	5	5

3、转化漏斗案例1：多易寻宝-促销活动

分析需求：
多易寻宝促销活动的每一个环节的转化率

 “多易寻宝”活动，转化路径定义：
路径步骤定义：
1.第一步 ： 多易寻宝活动广告曝光事件（eventid=’adShowEvent’, ad_id=‘5’）
2.第二步 ： 多易寻宝活动广告点击事件（eventid=’adClickEvent’, ad_id=‘5’）
3.第三步 ： 活动参与（eventid=‘appClickEvent’, element_id=‘14’）
4.第四步 ： 寻宝订单支付页（eventid=‘appviewEvent’,pgid=‘455’）
5.第五步 ： 去支付点击事件（eventid=‘appClickEvent’,element_id=‘26’）

代码实现
《详见项目代码》

4、转化漏斗案例2：呼朋唤友-拉新活动

“呼朋唤友”活动，转化路径定义：
1.用户浏览“呼朋唤友”活动广告（eventid=ad_show,ad_id=5）
2.用户点击“呼朋唤友”活动广告（eventid=ad_click,act_id=5）
3.用户点击呼朋唤友二维码生成按钮（eventid=appClickEvent,element_id=hphy_ewm）
4.用户点击分享按钮事件（eventid=appClickEvent,element_id=hphy_fx）
5.用户打开活动落地页（eventid=appviewEvent,pgid=988）
6.用户填写表单，点击注册（eventid=appClickEvent,element_id=hphy_zc）

代码实现
《详见项目代码》

五、【广告分析】站内广告分析

站内广告，指的是，在本公司网站的各处所投放的广告，有些广告是为自己的产品做宣传，有些广告是为第三方做宣传；
以京东为例，如下图所示：

1、需求：概况分析

广告概况分析报表： ADS_ADN_OVW

日期	广告id	曝光次数	曝光人数	曝光人数最大页	点击次数	点击人数	点击人数最大页

源表：dwd_apl_adv_dtl 广告事件明细表中做

2、DWS层广告事件页面聚合表

通过分析，发现，如果创建一个dws层的中间表，对后面的各种数据分析能提供有力支撑！

-- 建表
create table dws_apl_ad(
pgid string,   -- 页面id
adid string,   -- 广告id
eventid string,  -- 事件类型
cnts int,      -- 发生次数
use_cnts int  -- 发生人数
)
partitioned by (dt string)
stored as parquet;

页面id	广告id	事件类型	发生次数	发生人数
pg002	5	adshow	1000	600
pg002	6	adshow	1000	500
pg002	5	adclick	200	150
pg008	5	adshow	800	700
…

-- 抽取
insert into table dws_apl_ad partition(dt='2019-10-28')
select
event['adId'] as adid,eventid,event['pgId'] as pgid,count(1) as cnts,
count(distinct uid) as use_cnts
from dwd_apl_ev_ad where dt='2019-10-28'
group by event['adId'],eventid,event['pgId']
;

《详见项目代码》

3、ADS层广告概况报表

根据原型页面的展示需求，梳理出如下表格模型：
广告概况统计报表：ADS_ADN_OVW

日期	广告id	曝光次数	曝光人数	曝光人数最大页	点击次数	点击人数	点击人数最大页

广告概况统计报表：ADS_ADN_OVW

日期	广告id	行为类型	次数	人数	最大页

建表语句：
create table ads_apl_ad_ov(
ad_id string,
show_cnts int,
show_users int,
show_max_url string,
click_cnts int,
click_users int,
click_max_url string
)
partitioned by (dt string)
stored as parquet;

1)、梳理计算要素

从报表中，可以梳理出，在计算过程中所需要的要素如下：
        日期
        uid
        页面location
        广告id
        事件类型

2)、计算逻辑

以页面作为分组条件，来计算，在这个页面上：
       页面 某广告 曝光的次数 曝光的人数
       /a ad01 100 80
       /b ad01 80 60
       /b ad02 200 180

       那么，要统计某广告的总次数，则按广告分组，把所有页的曝光次数累加！ 要得到某广告曝光最多的页面，则按广告分组，求次数最大的页即可
       然后，将两部分结果按照adid关联起来即可

《详见项目代码》

六、【广告分析】站外广告分析

站外广告，指的是本公司在第三方媒体上投放的广告；
以淘宝在新浪所投放的广告为例，如下图所示：

在第三方媒体投放广告会耗费资金，当然需要统计这些广告投放所产生的效果；
通常，一个公司在站外投放广告，都是通过一些广告联盟(dsp)进行投放，广告效果相关数据统计由广告联盟负责，并向广告主提供实时或定期的报表；

而广告主自己，也可以自行做一些站外广告投放相关的数据分析，以便于跟广告联盟的数据报表进行比对；自行统计分析，需要用到UTM广告跟踪技术；
《UTM介绍见：3.1.4章节》

需要统计如下报表：广告推广流量分析表
推广流量分析表：ADS_ADE_FLW

广告平台	广告形式	广告内容	营销活动	来访pv	来访会话数	来访uv	来访新客
新浪	banner			10000
新浪	侧边栏			8000
新浪	banner			80000
新浪				200000

该报表的统计，源表可以选择 “流量事件明细表”，表中的日志记录中，如果某pv事件是来自于站外广告的点击，则事件日志中的event字段（map类型字段）中会带有多个utm参数值；

如果一个pageview事件的详情中，带有这些utm参数的值，则说明这个pageview是从站外投放的广告引进来的；根据这些utm参数值（可以当成各种维度），即可统计出上述多维报表；

当然，在现实开发中，上述报表可以单独开发实现，也可以在前面做pv，uv等报表统计时，把utm中的个参数作为维度，一并统计出来！

《详见项目代码》

---

多易教育，专注大数据培训； 课程引领市场，就业乘风破浪
多易教育官网地址
https://www.51doit.cn
多易教育在线学习平台
https://v.51doit.cn

本项目教程笔记源自多易教育《Titan综合数据仓库与数据运营系统》，在CSDN学院有相关视频教程购买链接，大数据企业级项目实战–Titan大型数据运营系统
本项目课程是一门极具综合性和完整性的大型大数据项目实战课程，课程项目的业务背景源自各类互联网公司对海量用户浏览行为数据和业务数据分析的需求及企业数据管理、数据运营需求。
学完本课程，你将很容易就拿到大数据数仓建设或用户画像建设等岗位的OFFER

本课程项目涵盖数据采集与预处理、数据仓库体系建设、用户画像系统建设、数据治理（元数据管理、数据质量管理）、任务调度系统、数据服务层建设、OLAP即席分析系统建设等大量模块，力求原汁原味重现一个完备的企业级大型数据运营系统。

跟随项目课程，历经接近100+小时的时间，从需求分析开始，到数据埋点采集，到预处理程序代码编写，到数仓体系搭建…逐渐展开整个项目的宏大视图，构建起整个项目的摩天大厦。