前言
剩下的 ADS 层主要就是写 SQL 了,就像我们之前练习的 HQL 题一样,不同的是这里的数据从哪张表读取(DWD 还是 ADS 甚至个别表需要从 DIM 层读取)需要我们自己来分析。
ADS 的建表语句和 MySQL 是对应的,我们到时候需要用 DataX 来进行导出,唯一需要注意的就是字段类型,因为一些字段类型是不匹配的:
1、ADS 层开发
ADS 层,也就是数据应用层,这里主要存放的就是我们指标的结果,能够直接导出到外部系统,供后面的应用来使用(比如 BI 或者其它应用)。
1.1、流量主题
1.1.1、各渠道流量统计
需求:
统计周期 |
统计粒度 |
指标 |
说明 |
最近1/7/30日 |
渠道 |
访客数 |
统计访问人数 |
最近1/7/30日 |
渠道 |
会话平均停留时长 |
统计每个会话平均停留时长 |
最近1/7/30日 |
渠道 |
会话平均浏览页面数 |
统计每个会话平均浏览页面数 |
最近1/7/30日 |
渠道 |
会话总数 |
统计会话总数 |
最近1/7/30日 |
渠道 |
跳出率 |
只有一个页面的会话的比例 |
这里的跳出指的是一个回话中只浏览了一个页面就走了。
建表语句
行:一个统计周期(1/7/30)一个渠道的五个指标。
列:前三个字段共同对应一个渠道的一个统计周期,后五个字段指的是我们的五个指标。
也就是说,如果我们有 5 个渠道,那么这张表的最终结果就只有 15 行,因为我们有 3 个统计周期,所以每个渠道的每个统计周期加起来就是 3 * 5 = 15 .
DROP TABLE IF EXISTS ads_traffic_stats_by_channel; CREATE EXTERNAL TABLE ads_traffic_stats_by_channel ( `dt` STRING COMMENT '统计日期', `recent_days` BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天', `channel` STRING COMMENT '渠道', `uv_count` BIGINT COMMENT '访客人数', `avg_duration_sec` BIGINT COMMENT '会话平均停留时长,单位为秒', `avg_page_count` BIGINT COMMENT '会话平均浏览页面数', `sv_count` BIGINT COMMENT '会话数', `bounce_rate` DECIMAL(16, 2) COMMENT '跳出率' ) COMMENT '各渠道流量统计' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION '/warehouse/gmall/ads/ads_traffic_stats_by_channel/';
ADS 层的表并没有分区,主要原因有两个:
- 分区的目的是为了将来方便我们使用分区进行过滤,比如说要找哪一天的数据就找哪个分区。但是这里并不需要,因为这张表存放的就是数据结果了,我们每天的结果都会导出到外部应用去。
- 即使分区,因为我们这里存放的是最终结果而不是中间大量的事实或者维度这种数据,所以数据量非常小。拿这张表来说,如果我们的渠道只有 5 个,那么最终这张表的记录一共才 15 行。如果按天分区,那么肯定会造成大量的小文件问题。
同时,ADS 层的表我们也不会进行列式存储和压缩,这是因为我们这里已经存放的就是最终结果了,数据量也比较小,我们将来查询也是用所有字段,所以几不需要压缩也不需要列式存储。
数据装载
insert overwrite table ads_traffic_stats_by_channel select * from ads_traffic_stats_by_channel union select '2020-06-14' dt, recent_days, channel, cast(count(distinct(mid_id)) as bigint) uv_count, cast(avg(during_time_1d)/1000 as bigint) avg_duration_sec, cast(avg(page_count_1d) as bigint) avg_page_count, cast(count(*) as bigint) sv_count, cast(sum(if(page_count_1d=1,1,0))/count(*) as decimal(16,2)) bounce_rate from dws_traffic_session_page_view_1d lateral view explode(array(1,7,30)) tmp as recent_days where dt>=date_add('2020-06-14',-recent_days+1) group by recent_days,channel;
对于访客人数,我们可以直接从页面浏览事实表(DWD层)中去把 mid (设备id)count distinct 即可,但是因为我们这里还有统计周期,所以后面还需要聚合,所以我们应该先去 DWS 层看看有没有已经进一步汇总过的汇总表,这样我们就不用再聚合了。
对于会话平均停留时长和会话平均浏览页面,我们同样可以去 DWS 层会话粒度页面浏览最近1日表分别去拿 during_time 和 view_count 然后 avg 一下即可。
会话数我们更是直接对 DWS 层的会话粒度页面浏览最近1日表 count(*) 即可(一行对应一个会话)。
1.1.2、路径分析
用户路径分析,顾名思义,就是指用户在APP或网站中的访问路径。为了衡量网站优化的效果或营销推广的效果,以及了解用户行为偏好,时常要对访问路径进行分析。
用户访问路径的可视化通常使用桑基图。如下图所示,该图可真实还原用户的访问路径,包括页面跳转和页面访问次序。
桑基图需要我们提供每种页面跳转的次数,每个跳转由source/target表示,source指跳转起始页面,target表示跳转终到页面。
建表语句
DROP TABLE IF EXISTS ads_page_path; CREATE EXTERNAL TABLE ads_page_path ( `dt` STRING COMMENT '统计日期', `recent_days` BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天', `source` STRING COMMENT '跳转起始页面ID', `target` STRING COMMENT '跳转终到页面ID', `path_count` BIGINT COMMENT '跳转次数' ) COMMENT '页面浏览路径分析' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION '/warehouse/gmall/ads/ads_page_path/';
数据装载
insert overwrite table ads_page_path select * from ads_page_path union select '2020-06-14' dt, recent_days, source, nvl(target,'null'), count(*) path_count from ( select recent_days, concat('step-',rn,':',page_id) source, concat('step-',rn+1,':',next_page_id) target from ( select recent_days, page_id, lead(page_id,1,null) over(partition by session_id,recent_days) next_page_id, row_number() over (partition by session_id,recent_days order by view_time) rn from dwd_traffic_page_view_inc lateral view explode(array(1,7,30)) tmp as recent_days where dt>=date_add('2020-06-14',-recent_days+1) )t1 )t2 group by recent_days,source,target;
1.2、用户主题
1.2.1、用户变动统计
统计周期 |
指标 |
说明 |
最近1日 |
流失用户数 |
之前活跃过的用户,最近一段时间未活跃,就称为流失用户。此处要求统计7日前(只包含7日前当天)活跃,但最近7日未活跃的用户总数。 |
最近1日 |
回流用户数 |
之前的活跃用户,一段时间未活跃(流失),今日又活跃了,就称为回流用户。此处要求统计回流用户总数。 |
建表语句
DROP TABLE IF EXISTS ads_user_change; CREATE EXTERNAL TABLE ads_user_change ( `dt` STRING COMMENT '统计日期', `user_churn_count` BIGINT COMMENT '流失用户数', `user_back_count` BIGINT COMMENT '回流用户数' ) COMMENT '用户变动统计' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION '/warehouse/gmall/ads/ads_user_change/';
数据装载
insert overwrite table ads_user_change select * from ads_user_change union select churn.dt, user_churn_count, user_back_count from ( select '2020-06-14' dt, count(*) user_churn_count from dws_user_user_login_td where dt='2020-06-14' and login_date_last=date_add('2020-06-14',-7) )churn join ( select '2020-06-14' dt, count(*) user_back_count from ( select user_id, login_date_last from dws_user_user_login_td where dt='2020-06-14' )t1 join ( select user_id, login_date_last login_date_previous from dws_user_user_login_td where dt=date_add('2020-06-14',-1) )t2 on t1.user_id=t2.user_id where datediff(login_date_last,login_date_previous)>=8 )back on churn.dt=back.dt;
1.2.2、用户留存率
留存分析一般包含新增留存和活跃留存分析。
新增留存分析是分析某天的新增用户中,有多少人有后续的活跃行为。活跃留存分析是分析某天的活跃用户中,有多少人有后续的活跃行为。
留存分析是衡量产品对用户价值高低的重要指标。
此处要求统计新增留存率,新增留存率具体是指留存用户数与新增用户数的比值,例如2020-06-14新增100个用户,1日之后(2020-06-15)这100人中有80个人活跃了,那2020-06-14的1日留存数则为80,2020-06-14的1日留存率则为80%。
要求统计每天的1至7日留存率,如下图所示:
建表语句
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/';
离线数仓(十)【ADS 层开发】(2)https://developer.aliyun.com/article/1532450