大数据-236 离线数仓 - 会员活跃度 WDS 与 ADS 导出到 MySQL 与 广告业务 需求分析

点一下关注吧！！！非常感谢！！持续更新！！！

Java篇开始了！

目前开始更新 MyBatis，一起深入浅出！

目前已经更新到了：

• Hadoop（已更完）
• HDFS（已更完）
• MapReduce（已更完）
• Hive（已更完）
• Flume（已更完）
• Sqoop（已更完）
• Zookeeper（已更完）
• HBase（已更完）
• Redis （已更完）
• Kafka（已更完）
• Spark（已更完）
• Flink（已更完）
• ClickHouse（已更完）
• Kudu（已更完）
• Druid（已更完）
• Kylin（已更完）
• Elasticsearch（已更完）
• DataX（已更完）
• Tez（已更完）
• 数据挖掘（已更完）
• Prometheus（已更完）
• Grafana（已更完）
• 离线数仓（正在更新…）

章节内容

上节我们完成了如下的内容：

• 会员活跃度 数据测试
• 完整加载 ODS DWD 层

数据测试

活跃会员DWS

sh dws_load_member_start.sh 2020-07-21
-- 依次执行其他的 过于重复 这里跳过

执行结果如下图

活跃会员ADS

（之前这里打错名称了，多打了字母m，大家根据实际情况修改）

sh ads_load_member_active.sh 2020-07-21
-- 依次执行其他的 过于重复 这里跳过

执行结果如下图：

新增会员DWS

sh dws_load_member_add_day.sh 2020-07-21
-- 依次执行其他的 过于重复 这里跳过

执行结果如下图所示：

新增会员ADS

sh ads_load_member_add.sh 2020-07-21
-- 依次执行其他的 过于重复 这里跳过

执行结果如下图所示：

会员留存DWS

sh dws_load_member_retention_day.sh 2020-07-21
-- 依次执行其他的 过于重复 这里跳过

执行结果如下图所示：

会员留存ADS

sh ads_load_member_retention.sh 2020-07-21
-- 依次执行其他的 过于重复 这里跳过

执行结果如下图所示：

导出测试

HDFS
现在数据有了，在我们的HDFS上，可以看到：

DataX

我们运行刚才的脚本：

sh /opt/wzk/hive/export_member_active_count.sh 2020-07-21
-- 其他日期也执行一次，重复性高，这里略过

运行结果如下图所示：

等待运行结束：

MySQL

这里我们运行了 07-21 到 07-31 的数据，我们查询数据库内容，可以看到如下的结果：

广告业务 ODS DWD ADS

基本介绍

互联网平台通行的商业模式是利用免费的基础服务来吸引大量用户，并利用这些用户开展广告或其他增值业务实现盈利从而反哺支撑免费服务的生存和发展。广告收入不仅成为互联网平台的重要收入之一，更决定了互联网平台的发展程度。
电商平台本身就汇聚了海量的商品、店铺的信息，天然适合进行商品的推广。对于电商和广告主来说，广告投放的目的无非就是吸引更多的用户，最终实现营销转换。因此非常关注不同位置的广告的曝光量、点击量、购买量、点击率、购买率。

需求分析

事件日志数据样例：

{"wzk_event": [{"name": "goods_detail_loading","json": {"entry": "3","goodsid": "0","loading_time": "80","action": "4","staytime": "68","showtype": "4"},"time": 1596225273755}, {"name": "loading","json": {"loading_time": "18","action": "1","loading_type": "2","type": "3"},"time": 1596231657803}, {"name": "ad","json": {"duration": "17","ad_action": "0","shop_id": "786","event_type": "ad","ad_type": "4","show_style": "1","product_id": "2772","place": "placeindex_left","sort": "0"},"time": 1596278404415}, {"name": "favorites","json": {"course_id": 0,"id": 0,"userid": 0},"time": 1596239532527}, {"name": "praise","json": {"id": 2,"type": 3,"add_time": "1596258672095","userid": 8,"target": 6},"time": 1596274343507}],"attr": {"area": "拉萨","uid": "2F10092A86","app_v": "1.1.12","event_type": "common","device_id": "1FB872-9A10086","os_type": "4.1","channel": "KS","language": "chinese","brand": "xiaomi-2"}
}

采集的信息包括：

• 商品详情页加载：goods_detail_loading
• 商品列表：loading
• 消息通知：notification
• 商品评论：comment
• 收藏：favorites
• 点赞：praise
• 广告：ad

在广告的字段中，收集到的数据有：

• action 用户行为 0曝光 1曝光后点击 2购买
• duration 停留时长
• shop_id 商家id
• event_type “ad”
• ad_type 1JPG 2PNG 3GIF 4SWF
• show_style 0静态图 1动态图
• product_id 产品id
• place 广告位置 1首页 2左侧 3右侧 4列表页
• sort 排序位置

需求指标

点击次数统计（分时统计）

• 曝光次数、不同用户ID数、不同用户数
• 点击次数、不同用户ID数、不同用户数
• 购买次数、不同用户ID数、不同用户数

转化率-漏斗分析

• 点击率 = 点击次数/曝光次数
• 购买率 = 购买次数/点击次数

活动曝光效果评估
行为（曝光、点击、购买）、时间段、广告位、产品、统计对应的次数
时间段、广告位、商品，曝光次数最多的前N个

ODS层

建立新表

Hive启动之后，切换到 ods层，然后我们继续创建外部表，将数据映射到Hive中：

use ods;
drop table if exists ods.ods_log_event;
CREATE EXTERNAL TABLE ods.ods_log_event(`str` string
) PARTITIONED BY (`dt` string)
STORED AS TEXTFILE
LOCATION '/user/data/logs/event';

执行结果如下图所示：

编写脚本

vim /opt/wzk/hive/ods_load_event_log.sh

编写脚本，写入内容如下：

#!/bin/bash
source /etc/profile
if [ -n "$1" ]
then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
alter table ods.ods_log_event add partition (dt='$do_date');
"
hive -e "$sql"

写入的内容如下所示：