目录
简单介绍:
一.项目背景介绍
二.项目架构介绍
三.项目内容
3.1访问和咨询分析主题:
3.1.1 表与表之间的关联
3.1.2访问咨询主题需求汇总:最终需在ADS层制作六张表
3.1.3 访问咨询DWS大宽表建表与导入数据
3.2意向线索主题需求分析
3.2.1意向线索主题需求汇总:最终需在ADS层制作8张表
3.2.2 .表与表之间的关联
3.2.3 意向线索主题DWS大宽表建表与数据导入
简单介绍:
(本项目数据来源,环境搭建都可以从黑马程序员b站视频"在线教育数仓实战"中免费获取)
整个项目的目的,就是将大量原始杂乱的数据,合并到一张大表中,再从大表中抽取需求里要用的数据,成为多个ADS表,最后将ADS数据使用可视化工具进行展示.
一.项目背景介绍
在线教育公司的主要产品是课程，为了提高公司的课程销量、提升公司的口碑、帮助公司做更好的运营推广，该公司对公司的访问与咨询系统、客户服务系统、教学管理系统的数据进行了数据分析。通过分析用户的访问量、咨询量、意向量、报名量等指标计算用户从访问到报名阶段的报名率，反映公司的运营情况，支持运营决策。通过分析学员的出勤人数、迟到人数、请假人数等指标计算学员学习课程时的出勤率、迟到率等指标来监控学员的学习情况，进而实现学员的学习管理，保证学员的学习质量，提升公司口碑。
但早期公司完成各项数据统计工作主要是基于web业务系统实现的, 通过SQL直接在业务数据库RDBMS中实施统计分析操作的。随着时间推移, 公司中数据量会越来越多, 此时原有的传统的数据库无法支撑庞大数据的存储, 需要解决数据存储的问题, 同时当数据量变大后, 原有的分析效率下降,不能及时的构建统计分析的结果，也造成大量的资源占用, 从而导致正常的数据支撑业务无法使用。
为了解决上述问题，提高公司的课程销量、提升公司的口碑、帮助公司做更好的运营推广，本项目通过对公司的访问与咨询系统、客户服务系统、教学管理系统三个系统四个主题的数据进行数据分析。
主要分析内容如下： 分析线上用户的各时间段各地区访问量、总咨询量以及咨询率等访问与咨询系统指标，分析线上线下新老学员各时间段各地区各学科的意向量、报名量等客户服务系统指标，计算用户从访问、产生意向线索到报名阶段的咨询率，有效线索转化率和有效线索报名转换率，反映公司的运营情况，了解市场需求，支持运营决策支持运营决策。
二.项目架构介绍
CDH （ Cloudera Distribution Hadoop ）是 Cloudera 公司提供的包含 Apache Hadoop 及其相关项目的软件发行版本。CDH （ Cloudera Distribution Hadoop ）是 Cloudera 公司提供的包含 Apache Hadoop 及其相关项目的软件发行版本。
Cloudera manager : CM是一个拥有集群自动化安装、中心化管理、集群监控、报警功能的一个工具（软件）,使得安装集群从几天的时间缩短在几个小时内，运维人员从数十人降低到几人以内，极大的提高集群管理的效率。
HDFS :hadoop的组件之一, 分布式文件存储系统,一次写入多次读取,不支持追加修改,具有高容错性,实时性,面向大数据集.
YARN: hadoop的组件之一,分布式资源调度,支持多个数据处理框架（MapReduce Spark Storm等）。具有资源利用率高、运行成本底、数据共享等特点
HIVE:基于hadoop的数据仓库处理工具,将SQL语句转化成MapReduce程序,进行分布式SQL计算
Zookeeper :分布式协调服务, 分布式的小文件存储系统,用来解决分布式集群中应用系统的一致性问题.
DATAx : 阿里巴巴开源的一个异构数据源离线同步工具,本项目使用它将mysql的数据与hive的数据互相传导同步,在这个过程中字段需要能对得上
三.项目内容
-
项目行业： 项目所处行业 , 背景(访问与咨询系统、客户服务系统、教学管理系统) ,遇到的问题已经对应的需求:
-
==项目业务：== 访问与咨询流程, 意向和与线索流程,报名流程,考勤流程
-
==项目需求==: 访问与咨询主题, 意向和与线索主题,报名主题,考勤主题
-
基于之前的数据分析业务，构建大数据数据仓库平台，将原有的业务基于大数据数据仓库平台来实现
3.0整体业务流程
3.1访问和咨询分析主题:
客户访问和咨询主题，顾名思义，分析的数据主要是客户的访问数据和咨询数据。但是经过需求调研，这里的访问数据，实际指的是访问的客户量，而不是客户访问量。原始数据来源于咨询系统的mysql业务数据库。
| 表名 | 维度 | 指标 | |
| 总访问客户量 | 年、季度、月 、日、小时 | 访问客户量 | |
| 地区独立访客热力图 | 年,季度,月 | 访问量(按地区聚合) | |
| 访客咨询率趋势 | 年,月,城市 | 咨询率=发起咨询的人数/访问客户量 | 区分咨询人数:msg_count 必须 >= 1 |
| 客户访问量和访客咨询率双轴趋势 | 年,季度,月,天,小时 | 访问量,咨询人数 | 区分咨询人数:msg_count 必须 >= 1 |
| 时间段访问客户量趋势 | 天,小时 | 访问量 | |
| 来源渠道访问量占比 | 年,季度,月,天,小时 | 各个渠道字段:origin_channel 时间维度:create_time 指标:sid | 访问量和咨询量的划分: |
| 搜索来源访问量占比 | 年 季度 月 天 小时 | 搜索来源:seo_source 时间维度:create_time 指标字段:sid | |
| 活跃页面排行榜 | 年 季度 月 天 小时 | 各个页面:from_url 指标字段:count(1) |
3.1.1 表与表之间的关联
3.1.2访问咨询主题需求汇总:最终需在ADS层制作六张表
ads_area_total_we_chat_2019_7_day, 地区总访问量天表
ads_channel_total_web_chat_ems_2019_07_day, 来源渠道总访问量客户天表
ads_china_area_consultation_rate_day, 地区咨询率比率天表
ads_from_url_total_web_chat_2019_07_day, url来源页面总访问客户量天表
ads_source_total_web_chat_ems_2019_07_day, 搜索来源总访问客户量天表
ads_total_we_chat_2019_7_hour, 总访问客户量小时表
每小时/每天/每月/每个季度/每年总访问用户量、总访问IP个数、总访问Session个数
每天/每月/每个季度/每年全国各个地区访问用户量、访问IP个数、访问Session个数
每天/每月/每个季度/每年每个来源渠道的访问用户量、访问IP个数、访问Session个数
每天/每月/每个季度/每年每个搜索来源的访问用户量、访问IP个数、访问Session个数
每天/每月/每个季度/每年每个来源页面的访问用户量、访问IP个数、访问Session个数
每天/每月/每个季度/每年全国各个地区咨询率：咨询率 = 咨询人数 / 访问人数
3.1.3 访问咨询DWS大宽表建表与导入数据
分层得到了大宽表后,之后的需求就可以直接from这个大宽表,提升了效率,事实上本主题也只有2个表而已,轻轻松松
-- 创建DWS层
create database if not exists
zxjy_dws;
use zxjy_dws;
-- 创建访问与咨询信息表
drop table if exists zxjy_dws.dws_we_chat_2019_07;
create table zxjy_dws.dws_we_chat_2019_07(
id BIGINT COMMENT '主键',
create_date_time timestamp COMMENT '数据创建时间',
session_id string COMMENT '七陌sessionId',
sid string COMMENT '访客id',
create_time timestamp COMMENT '会话创建时间',
seo_source string COMMENT '搜索来源',
seo_keywords string COMMENT '关键字',
ip string COMMENT 'IP地址',
area string COMMENT '地域',
country string COMMENT '所在国家',
province string COMMENT '省',
city string COMMENT '城市',
origin_channel string COMMENT '投放渠道',
`user` string COMMENT '所属坐席',
manual_time timestamp COMMENT '人工开始时间',
begin_time timestamp COMMENT '坐席领取时间 ',
end_time timestamp COMMENT '会话结束时间',
last_customer_msg_time_stamp timestamp COMMENT '客户最后一条消息的时间',
last_agent_msg_time_stamp timestamp COMMENT '坐席最后一下回复的时间',
reply_msg_count BIGINT COMMENT '客服回复消息数',
msg_count BIGINT COMMENT '客户发送消息数',
browser_name string COMMENT '浏览器名称',
os_info string COMMENT '系统名称',
referrer string comment '上级来源页面',
from_url string comment '会话来源页面',
landing_page_url string comment '访客着陆页面',
url_title string comment '咨询页面title',
platform_description string comment '客户平台信息',
other_params string comment '扩展字段中数据',
history string comment '历史访问记录'
)comment '访问与咨询信息表'
partitioned by (dt STRING COMMENT '录入日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');
-- 访问与咨询信息表插入数据
-- 主表与附属表left join
insert overwrite table zxjy_dws.dws_we_chat_2019_07 partition(dt)
select
t1.id,
t1.create_date_time,
t1.session_id,
t1.sid,
t1.create_time,
t1.seo_source,
t1.seo_keywords,
t1.ip,
t1.area,
t1.country,
t1.province,
t1.city,
t1.origin_channel,
t1.`user`,
t1.manual_time,
t1.begin_time,
t1.end_time,
t1.last_customer_msg_time_stamp,
t1.last_agent_msg_time_stamp,
t1.reply_msg_count,
t1.msg_count,
t1.browser_name,
t1.os_info,
t2.referrer,
t2.from_url,
t2.landing_page_url,
t2.url_title,
t2.platform_description,
t2.other_params,
t2.history,
date_format(t1.create_time, 'yyyy-MM-dd') as dt
from zxjy_ods.ods_web_chat_ems_2019_07_i t1
left join zxjy_ods.ods_web_chat_string_ems_2019_07_i t2 on t1.id =t2.id;
3.2意向线索主题需求分析
3.2.1意向线索主题需求汇总:最终需在ADS层制作8张表
ads_relationshaip_channel_customer, 来源渠道意向用户数天表
ads_relationshaip_department_customer, 咨询中心意向用户数天表
ads_relationshaip_school_customer, 学校意向用户数天表
ads_relationshaip_subject_customer, 学科意向用户数天表
ads_relationshaip_vaild_customer, 有效线索数天表
ads_relationshaip_valid_percent_customer, 有效线索数转化率小时表
ads_relationship_area_day, 地区统计意向用户个数天表
ads_relationship_day, 意向用户个数天表
每天/每月/每年线上线下以及新老学员的意向用户个数
每天/每月/每年各地区的线上线下以及新老学员的意向用户个数
每天/每月/每年各学科线上线下以及新老学员的意向用户个数Top10
每天/每月/每年各校区线上线下以及新老学员的意向用户个数Top10
每天/每月/每年各来源渠道线上线下以及新老学员的意向用户个数
每天/每月/每年==各咨询中心==线上线下以及新老学员的意向用户个数
以传智咨询为例 分为总部咨询 各个分校咨询
相当于计算传智每个咨询部门的一些指标
每天线上线下及新老学员的有效线索个数
是否可以联系成功
每小时线上线下及新老学员的有效线索转化率 = 有效线索个数 / 总线索个数
3.2.2 .表与表之间的关联
3.2.3 意向线索主题DWS大宽表建表与数据导入
(这里的从ODS 8表合一,DWS建设是存在问题的,最后会导致clue线索表里的地区数据丢失,因此直接从ods-dws并不适合这个主题,请其他同学还是先dwd-dwm-dws这样进行分层)
create database zxjy_dw;
-- 创建客户意向与线索大宽表
create table zxjy_dw.dw_customer_relationship_clue (
--客户意向表字段
id bigint comment '客户关系id',
create_date_time timestamp comment '数据创建时间',
update_date_time timestamp comment '最后更新时间',
customer_id bigint comment '所属客户id',
first_id bigint comment '第一条客户关系id',
belonger bigint comment '归属人',
belonger_name string comment '归属人姓名',
distribution_handler bigint comment '分配处理人',
rs_origin_type string comment '数据来源',
creator bigint comment '意向数据创建人',
current_creator bigint comment '意向数据当前创建人：初始==创建人，当在公海拉回时为 拉回人',
creator_name string comment '创建者姓名',
origin_channel string comment '意向数据来源渠道',
itcast_clazz_id bigint comment '所属ems班级id,对应报名课程表的id',
itcast_clazz_time timestamp comment '报班时间',
ems_student_id bigint comment 'ems的学生id',
course_id bigint comment '课程ID',
course_name string comment '课程名称',
rs_follow_type bigint comment '意向数据分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取', -- 意向表分配类型
transfer_bxg_oa_account string comment '转移到博学谷归属人OA账号',
-- 客户线索表字段
session_id string comment '七陌会话id',
sid string comment '访客id',
platform string comment '平台来源 （pc-网站咨询|wap-wap咨询|sdk-app咨询|weixin-微信咨询）',
s_name string comment '用户名称',
seo_source string comment '搜索来源',
ip string comment 'IP地址',
msg_count bigint comment '客户发送消息数',
area string comment '区域',
country string comment '所在国家',
province string comment '省',
city string comment '城市',
valid bigint comment '该线索是否是网资有效线索',
clue_state string comment '线索状态',
tf_origin_channel string comment '投放渠道',
zhuge_session_id string comment '诸葛会话id',
is_repeat bigint comment '是否重复线索(手机号维度) 0:正常 1：重复',
tenant bigint comment '租户id',
clue_follow_type bigint comment '线索数据分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取',
-- 静态信息表
use_name string comment '客户姓名',
bxg_student_id string comment '博学谷学员ID，可能未关联到，不存在',
-- 线索申诉表
customer_relationship_first_id bigint comment '第一条客户关系id',
appeal_status bigint comment '申诉状态，0:待稽核 1:无效 2：有效',
-- 员工表
employee_id bigint comment '员工id 对应客户意向表的创建人id',
real_name string comment '员工的真实姓名',
tdepart_id bigint comment '直属部门 对应部门表的部门id',
-- 部门表
department_name string comment '部门名称',
-- 校区表
school_name string comment '校区名称',
-- 学科表
subject_name string comment '学科名称'
)comment '客户意向与线索大宽表'
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');
--
insert overwrite table zxjy_dw.dw_customer_relationship_clue
select
-- 意向表字段
t1.id, -- 客户关系id
t1.create_date_time, -- 原客户意向数据创建时间
t1.update_date_time, -- 原客户意向数据最后更新时间
t1.customer_id, -- 所属客户id
t1.first_id, -- 第一条客户关系id
t1.belonger, -- 归属人
t1.belonger_name, -- 归属人姓名
t1.distribution_handler, -- 分配处理人
t1.origin_type, -- 数据来源渠道(两表一样,合并)
t1.creator, -- 意向表创建人
current_creator, -- 意向表该条数据当前创建人
creator_name, -- 意向表该条数据当前创建人：初始==创建人,当在公海拉回时为拉回人
t1.origin_channel, -- 来源渠道
itcast_clazz_id,
itcast_clazz_time,
ems_student_id,
t1.course_id,
t1.course_name,
t1.follow_type, -- 意向表分配类型
transfer_bxg_oa_account,
-- 线索表字段
session_id,
sid,
platform,
s_name,
seo_source,
ip,
msg_count,
t2.area,
country,
province,
city,
valid,
clue_state,
t2.origin_channel as oc2, -- 投放渠道
zhuge_session_id,
is_repeat,
t2.tenant, -- 线索表租户id
t2.follow_type as ft2, -- 线索分配类型
-- 静态信息表
t3.name as use_name,
bxg_student_id,
-- 线索申诉表
customer_relationship_first_id,
appeal_status,
-- 员工表
t5.id as eid ,
t5.real_name ,
t5.tdepart_id,
-- 部门表
t6.name as department_name, -- 部门名字
-- 校区表
t7.name, -- 校区名称
-- 学科表
t8.name as subject_name -- 学科名称
from (select * from zxjy_ods.ods_customer_relationship where deleted='false') t1
left join (select * from zxjy_ods.ods_customer_clue where deleted ='false' and is_repeat= 0)t2 on t1.id = t2.customer_relationship_id
left join (select * from zxjy_ods.ods_customer_i where deleted ='false')t3 on t1.customer_id = t3.id
left join (select * from zxjy_ods.ods_customer_appeal where deleted ='false') t4 on t1.id = t4.customer_relationship_first_id
left join (select * from zxjy_ods.ods_employee_i where deleted ='false') t5 on t1.creator = t5.id
left join (select * from zxjy_ods.ods_scrm_department where deleted ='false') t6 on t5.tdepart_id = t6.id
left join zxjy_ods.ods_itcast_school_i t7 on t1.itcast_school_id = t7.id
left join zxjy_ods.ods_itcast_subject_i t8 on t1.tenant = t8.tenant;
