step1: 需求分析(知道有哪些主题,哪些指标,哪些维度
step2: 建模分析(是否要压缩,行/列存储,索引,分区,分桶
step3: 建模操作,建库建表
step4: 数据采集(MySQL业务数据库 -> Hive(ODS层 如何验真: 总量校验,条件校验,抽样校验
step5: 数据清洗转换(空值过滤,空值转换,列值裁剪,切割
step6: 数据分析操作,结合主题,维度指标完成各种需求
step7: 数据导出操作,导出分析后的结果数据到mysql中,之后对接FineBI进行可视化展示

需求分析

  • 需求一: 计期内，新增意向客户（包含自己录入的意向客户）总数。
1
2
3
4
5
6
7
8
9
10
11
12
13
指标: 意向客户数
维度:
	时间维度:  年 月  天 小时
    新老维度:
    线上线下:

涉及到表:  customer_relationship (意向表)

涉及到字段:
   时间维度:  create_date_time
      注意: 此处有转换操作  需要将 create_date_time 转换 yearinfo monthinfo dayinfo
   指标字段: customer_id
       注意: 计算指标方式 先去重 然后统计个数 (DWM层不能进行提前聚合操作了)
  • 需求二: 统计指定时间段内，新增的意向客户，所在城市区域人数热力图。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
指标: 意向客户数 
维度:
    时间维度: 年 月 天 小时
    新老维度:
    地区(区域):
    线上线下:

涉及到表:  customer_relationship (意向表) (事实表)  和  customer(客户表)

涉及到字段:
	时间维度: 意向表.create_date_time
	地区(区域)维度: 客户表.area
	指标字段: 意向表.customer_id
	

表的关联条件:
    意向表.customer_id = 客户表.id
  • 需求三: 统计指定时间段内，新增的意向客户中，意向学科人数排行榜。学科名称要关联查询出来
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
指标: 意向客户数
维度:
    时间维度: 年 月 天 小时
    新老维度:
    线上线下:
    学科维度:

涉及到表: 
    customer_relationship(意向表  事实表)  
    customer_clue(线索表) 
    itcast_subject(学科表)
    
注意：学科id，同步时，0和null转换为统一数据，都转换为-1

涉及到字段: 
   customer_relationship(意向表  事实表)  :
   		线上线下:   origin_type (从此字段中可以得到线上和线下用户)
   			注意:  当这个字段的值为 NETSERVICE 或者 PRESIGNUP 认为线上 其余认为线下  
   			    将此字段转换为新的字段 专门用来判断线上和线下的维度: origin_type_stat 此字段只有 0(线下)  1(线上)
   	    时间维度: create_date_time
   	    学科维度: itcast_subject_id
   	    指标字段: 意向表.customer_id
   customer_clue(线索表)
		新老维度:  clue_state
			此处应该有转换操作:  将 clue_state --> clue_state_stat(新老维度)
			0: 老客户(值:  VALID_PUBLIC_NEW_CLUE)  1 新客户(值为 VALID_NEW_CLUES)   -1 表示无效客户
        清洗操作: delete  
            注意: 要将依据标记为删除的数据 过滤掉
   itcast_subject(学科表)
   		学科名称: name
关联条件:
    意向表.itcast_subject_id = 学科表.id
    意向表.id = 线索表.customer_relationship_id
  • 需求四: 统计指定时间段内，新增的意向客户中，意向校区人数排行榜。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
指标: 意向客户数
维度:
	时间维度:  年 月 天 小时
	新老维度:
	线上线下:
	校区维度:
	
注意：学校id，同步时，0和null转换为统一数据，都转换为-1

涉及到表: 
	customer_relationship(意向表  事实表)  
    customer_clue(线索表) 
    itcast_school(校区表)
涉及到字段:
   customer_relationship(意向表  事实表)  
        线上线下:   origin_type (从此字段中可以得到线上和线下用户)
   			注意:  当这个字段的值为 NETSERVICE 或者 PRESIGNUP 认为线上 其余认为线下  
   			    将此字段转换为新的字段 专门用来判断线上和线下的维度: origin_type_stat 此字段只有 0(线下)  1(线上)
   	    时间维度: create_date_time
   	    校区维度:  itcast_school_id
   		指标字段: customer_id
   customer_clue(线索表) :
        新老维度:  clue_state
			此处应该有转换操作:  将 clue_state --> clue_state_stat(新老维度)
			0: 老客户(值:  VALID_PUBLIC_NEW_CLUE)  1 新客户(值为 VALID_NEW_CLUES)   -1 表示无效客户
        清洗操作: delete  
            注意: 要将依据标记为删除的数据 过滤掉
   itcast_school(校区表)
		校区名称: name

关联条件:
    意向表.itcast_school_id = 校区表.id
    意向表.id = 线索表.customer_relationship_id
  • 需求五: 统计指定时间段内，新增的意向客户中，不同来源渠道的意向客户占比。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
指标: 意向客户数
维度: 
	时间维度:  年 月  天 小时
	新老维度
	线上线下
	来源渠道

涉及到表: 
   customer_relationship(意向表 事实表)
   customer_clue(线索表)
涉及到字段:
	customer_relationship(意向表 事实表)
	    时间维度: create_date_time
	    线上线下: origin_type --> origin_type_stat
	    来源渠道: origin_type
	        说明: 线上线下维度 本质上就是来源渠道维度的上卷操作
	    指标字段:  customer_id
	customer_clue(线索表):
	    新老维度: clue_state --> clue_state_stat
	    清洗操作: delete
	    

关联条件:
    意向表.id = 线索表.customer_relationship_id
  • 需求六: 统计指定时间段内，新增的意向客户中，各咨询中心产生的意向客户数占比情况。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
指标: 意向客户数
维度:
	时间维度: 年 月  天 小时
	新老维度:
	线上线下:
	各咨询中心:

涉及到表:
	customer_relationship(意向表 事实表)
	customer_clue(线索表)
	employee(员工表)
	scrm_department(部门表)
涉及到字段:
   customer_relationship(意向表 事实表)
   		时间维度: create_date_time
   		线上线下: origin_type --> origin_type_stat
   		指标字段: customer_id
   customer_clue(线索表):
       新老维度: clue_state --> clue_state_stat
       
	employee(员工表):
	    各咨询中心: tdepart_id
	scrm_department(部门表)
	    咨询中心名称: name

关联条件:
    意向表.creator = 员工表.id
    员工表.tdepart_id = 部门表.id
	意向表.id = 线索表.customer_relationship_id
  • 需求七: 统计期内，访客咨询产生的有效线索的占比。有效线索量 / 咨询量，有效线索指的是拿到电话且电话有效。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
指标:  有效线索量   
维度: 
	时间维度: 年  月   天
	线上线下
	新老维度
涉及表:
    customer_clue(线索表  事实表)
    customer_relationship(意向表 维度表)
    customer_appeal(投诉表 维度表)

涉及字段:
	新老维度: 线索表.clue_state
		此处应该有转换操作:  将 clue_state --> clue_state_stat(新老维度)
			0: 老客户(值:  VALID_PUBLIC_NEW_CLUE)  1 新客户(值为 VALID_NEW_CLUES)   -1 表示无效客户
	
	线上线下: 意向表.origin_type 
		此处应该有转换操作:  origin_type  --> origin_type_state
			0 线下(其余为线下)   1 线上 (值为:  NETSERVICE  和 PRESIGNUP)
	
	时间维度:  线索表.create_date_time
		此处应该有转换操作: create_date_time 转换为  yearinfo , monthinfo,  dayinfo ,hourinfo
	
	指标字段: 直接统计个数即可
		描述; 数据没有重复
	
	过滤清洗操作:   线索表.deleted
	 	将线索表中标记为删除的数据给清洗掉
	
	维度退化过滤操作:
		线索表.customer_relationship_id  not in (
		   select   ca.customer_relationship_first_id
		   from  customer_appeal   ca
		   where   ca.appeal_status = 1 and ca.customer_relationship_first_id != 0
		)
	 
关联条件:
  线索表.customer_relationship_id = 意向表.id

  • 需求八: 统计期内，1-24h之间，每个时间段的有效线索转化率。横轴：1-24h，间隔为1h，纵轴：每个时间段的有效线索转化率。
1
2
3
4
指标: 有效线索量
维度:
	时间维度: 年  月   每天 小时
	线上线下
  • 需求九:统计期内，新增的咨询客户中，有效线索的数量。
1
2
3
4
5
指标:  有效线索量
维度:
	时间维度: 年 月 天
	新老维度:
	线上线下:

总结:

本次主要涉及到两大指标, 在总结的时候, 需要进行分开探究每个指标所涉及的相关内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
指标:  意向客户数 (DWS层只需要一个表)
维度: 
	固有维度:
		时间维度: 年  月  天 小时
		新老维度:
		线上线下
	
	产品属性维度:
		总意向量
		地区(区域)维度
		学科维度
		校区维度
		来源渠道
		各咨询中心
涉及到表: 
	 customer_relationship(意向表 )    ---  事实表
	 customer(客户表)                  ---  维度表
	 customer_clue(线索表):            ---  维度表
	 itcast_subject(学科表)            ---  维度表
	 itcast_school(校区表)             ---  维度表
	 employee(员工表)                  ---  维度表
	 scrm_department(部门表)           ---  维度表
	 
涉及到字段:
	 customer_relationship(意向表 )    ---  事实表
	 	时间维度: create_date_time
   		线上线下: origin_type --> origin_type_stat
   		来源渠道: origin_type
   		校区维度:  itcast_school_id
   		学科维度:  itcast_subject_id 
   		指标字段: customer_id,
   		关联条件的字段: creator,id
   		
	 customer(客户表)                  ---  维度表
	    地区(区域)维度: 客户表.area
	    关联条件的字段: id
	 customer_clue(线索表)            ---  维度表
	 	新来维度: clue_state --> clue_state_stat
	 	清洗字段: delete
	 itcast_subject(学科表)            ---  维度表
	 	学科名称: name
	 	关联字段: id
	 itcast_school(校区表)             ---  维度表
	 	校区名称: name
	 	关联字段: id
	 employee(员工表)                  ---  维度表
	    关联条件: id
	    咨询中心: tdepart_id
	 scrm_department(部门表)           ---  维度表
		关联条件: id
		部门的名称(咨询中心): name


关联条件:
 	意向表.creator = 员工表.id
  员工表.tdepart_id = 部门表.id
	意向表.id = 线索表.customer_relationship_id
	意向表.itcast_school_id = 校区表.id
	意向表.itcast_subject_id = 学科表.id
	意向表.customer_id = 客户表.id

需要清洗内容: 
	将标记为删除的数据进行过滤掉 

需要转换内容:
   1) 将create_date_time 转换为 yearinfo  monthinfo dayinfo hourinfo
   2) 将origin_type 转换为 origin_type_state (用于统计线上线下)
   		转换逻辑: origin_type的值为: NETSERVICE 或者 PRESIGNUP 认为线上 其余认为线下
   3) 将clue_state 转换为 clue_state_stat (用于统计新老维度)
        转换逻辑:clue_state的值为 VALID_NEW_CLUES 为新客户  其余暂定为老客户
   4) 将校区和学科的 id字段, 如果为 0 或者 null 转换为 -1



指标:  有效线索量   
    维度: 
        时间维度: 年  月  天 小时
        线上线下
        新老维度

    涉及表:
        customer_clue(线索表  事实表)
        customer_relationship(意向表 维度表)
        customer_appeal(投诉表 维度表)

    涉及字段:
        新老维度: 线索表.clue_state
            此处应该有转换操作:  将 clue_state --> clue_state_stat(新老维度)
                0: 老客户(值:  VALID_PUBLIC_NEW_CLUE)  1 新客户(值为 VALID_NEW_CLUES)   -1 表示无效客户

        线上线下: 意向表.origin_type 
            此处应该有转换操作:  origin_type  --> origin_type_state
                0 线下(其余为线下)   1 线上 (值为:  NETSERVICE  和 PRESIGNUP)

        时间维度:  线索表.create_date_time
            此处应该有转换操作: create_date_time 转换为  yearinfo , monthinfo,  dayinfo ,hourinfo

        指标字段: 直接统计个数即可
            描述; 数据没有重复

        过滤清洗操作:   线索表.deleted
            将线索表中标记为删除的数据给清洗掉
		
    关联条件:
      线索表.customer_relationship_id = 意向表.id

      投诉表.customer_relationship_first_id = 意向表.id
      
线索量需求涉及的清洗转换操作: 
	过滤清洗操作:   线索表.deleted
         将线索表中标记为删除的数据给清洗掉
    过滤无效线索:  
    	此部分尝试关联投诉表, 专门转换出一个新的字段, 表示数据是否是有效还是无效 clue_appeal_state
	新老维度: 线索表.clue_state
            此处应该有转换操作:  将 clue_state --> clue_state_stat(新老维度)
                0: 老客户(值:  VALID_PUBLIC_NEW_CLUE)  1 新客户(值为 VALID_NEW_CLUES)   -1 表示无效客户
    线上线下: 意向表.origin_type 
        此处应该有转换操作:  origin_type  --> origin_type_state
            0 线下(其余为线下)   1 线上 (值为:  NETSERVICE  和 PRESIGNUP)
    时间维度:  线索表.create_date_time
        此处应该有转换操作: create_date_time 转换为  yearinfo , monthinfo,  dayinfo ,hourinfo

建模分析

ODS层: 源数据层

  • 作用: 对接数据源, 一般和数据源保持相同粒度 (直白: 将数据源中拷贝到ODS层中)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
放置事实表:
     customer_relationship(意向表 )  -- 本次主题的事实表
     customer_clue(线索表)  -- 这是下次主题的事实表
放置维度表: 
	6张表
	 customer(客户表)                  ---  维度表
	 itcast_subject(学科表)            ---  维度表
	 itcast_school(校区表)             ---  维度表
	 employee(员工表)                  ---  维度表
	 scrm_department(部门表)           ---  维度表
	 customer_appeal(投诉表)           ---  维度表
建表操作:   
     表中字段与数据源中字段保持一致, 只需要多加一个 抽取时间的字段即可
请注意:
    意向表  和 线索表中数据存在数据变更操作, 请解决

DW层: 数据仓库层

  • DWD层: 数据明细层

    • 作用: 1) 清洗转换处理工作 2) 拉链实现等
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    需要清洗内容: 
    	将标记为删除的数据进行过滤掉 

    需要转换内容:
       1) 将create_date_time 转换为 yearinfo  monthinfo dayinfo hourinfo
       2) 将origin_type 转换为 origin_type_state (用于统计线上线下)
       		转换逻辑: origin_type的值为: NETSERVICE 或者 PRESIGNUP 认为线上 其余认为线下
       3) 将clue_state 转换为 clue_state_stat (用于统计新老维度)
            转换逻辑:clue_state的值为 VALID_NEW_CLUES 为新客户  VALID_PUBLIC_NEW_CLUE为老客户  否则为无效客户
       4) 将校区和学科的 id字段, 如果为 0 或者 null 转换为 -1
       5) 过滤无效线索:  
        	此部分尝试关联投诉表, 专门转换出一个新的字段, 表示数据是否是有效还是无效 clue_appeal_state
    意向表和线索表需要构建拉链表
    • 建模内容
    1
    2
    3
    4
    5
    6
    清洗和转换以及拉链表的构建都只发生在意向表和线索表两个表中, 故其他表没有任何操作, 此层不需要对其单独处理, 后续DWB层直接读取ODS层对应的数据即可

    意向表和线索表在构建的时候, 要再次基础上构建拉链表, 多增加两个字段: start_time 和 end_time

    为了实施方便: 
    	在DWD层,我们构建两层操作, 一层用于进行拉链表实现, 一层用于清洗转换操作

  • DWB层:

    • 作用: 维度退化操作
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    此层后期处理的时候, 需要进行七表关联的操作

    DWB层表的构建: 指标字段  + 各个表维度相关的字段

    维度: 
    	固有维度:
    		时间维度: 年  月  天 小时
    		新老维度:
    		线上线下
    	
    	产品属性维度:
    		总意向量
    		地区(区域)维度
    		学科维度
    		校区维度
    		来源渠道
    		各咨询中心

    DWB表的字段(此部分仅仅是列出必须字段, 实际中应该将每个表的字段都应该涵盖进来, 除非确信未来一定不需要可以直接干掉): 
    customer_id,
    create_date_time, yearinfo  monthinfo dayinfo hourinfo
    clue_state_stat
    origin_type_stat
    clue_appeal_state
    area,
    itcast_subject_id,itcast_subject_name
    itcast_school_id,itcast_school_name
    origin_type
    tdepart_id,tdepart_name

  • DWS层:

    • 作用: 提前聚合统计操作
    1
    相关指标需要进行去重统计,虽然线索没涉及的去重, 但是我们一旦合并后, 整个操作依然是需要去重统计的, 所以无法进行提前聚合操作, 顾 DWS层不需要

DM层:

  • 作用: 细化统计操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
DM层表字段构成:  统计的字段 + 各个维度的字段 + 三个用于查询的字段

意向量:
维度: 
	固有维度:
		时间维度: 年  月  天 小时
		新老维度:
		线上线下
	
	产品属性维度:
		总意向量
		地区(区域)维度
		学科维度
		校区维度
		来源渠道
		各咨询中心

有效线索量:
	维度: 
		固有维度: 
			时间维度
			新老维度
			线上线下维度
		产品属性维度:
			无


DM层表字段:

意向量结果表: 
   customerid_total, 
   yearinfo,monthinfo,dayinfo,hourinfo
   clue_state_stat,
   origin_type_stat,
   area
   itcast_subject_id,itcast_subject_name
   itcast_school_id,itcast_school_name
   origin_type
   tdepart_id,tdepart_name
   group_type
   time_type
   time_str


 group_type: 按照那个产品属性维度统计
     1: 地区   2. 学科  3 校区  4 来源渠道  5 各咨询中心  6 总意向量
 time_type: 按照那个时间来统计
     1.小时  2. 天   3. 周 4. 月   5. 年
 time_str: 聚合时间, 便于查询具体时间内容
   
线索量结果表:
	clue_nums,origin_type_stat,clue_state_stat,yearinfo,monthinfo,dayinfo,hourinfo
	time_type,time_str

两个指标可以合并到同一张DM数据集市宽表中。

建模操作

ODS层:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
set hive.exec.orc.compression.strategy=COMPRESSION;

CREATE TABLE IF NOT EXISTS edu_ods.`customer_relationship` (
  `id` int COMMENT '客户关系id',
  `create_date_time` STRING COMMENT '创建时间',
  `update_date_time` STRING COMMENT '最后更新时间',
  `deleted` int COMMENT '是否被删除（禁用）',
  `customer_id` int COMMENT '所属客户id',
  `first_id` int COMMENT '第一条客户关系id',
  `belonger` int COMMENT '归属人',
  `belonger_name` STRING COMMENT '归属人姓名',
  `initial_belonger` int COMMENT '初始归属人',
  `distribution_handler` int COMMENT '分配处理人',
  `business_scrm_department_id` int COMMENT '归属部门',
  `last_visit_time` STRING COMMENT '最后回访时间',
  `next_visit_time` STRING COMMENT '下次回访时间',
  `origin_type` STRING COMMENT '数据来源',
  `itcast_school_id` int COMMENT '校区Id',
  `itcast_subject_id` int COMMENT '学科Id',
  `intention_study_type` STRING COMMENT '意向学习方式',
  `anticipat_signup_date` STRING COMMENT '预计报名时间',
  `level` STRING COMMENT '客户级别',
  `creator` int COMMENT '创建人',
  `current_creator` int COMMENT '当前创建人：初始==创建人，当在公海拉回时为 拉回人',
  `creator_name` STRING COMMENT '创建者姓名',
  `origin_channel` STRING COMMENT '来源渠道',
  `comment` STRING COMMENT '备注',
  `first_customer_clue_id` int COMMENT '第一条线索id',
  `last_customer_clue_id` int COMMENT '最后一条线索id',
  `process_state` STRING COMMENT '处理状态',
  `process_time` STRING COMMENT '处理状态变动时间',
  `payment_state` STRING COMMENT '支付状态',
  `payment_time` STRING COMMENT '支付状态变动时间',
  `signup_state` STRING COMMENT '报名状态',
  `signup_time` STRING COMMENT '报名时间',
  `notice_state` STRING COMMENT '通知状态',
  `notice_time` STRING COMMENT '通知状态变动时间',
  `lock_state` STRING COMMENT '锁定状态',
  `lock_time` STRING COMMENT '锁定状态修改时间',
  `itcast_clazz_id` int COMMENT '所属ems班级id',
  `itcast_clazz_time` STRING COMMENT '报班时间',
  `payment_url` STRING COMMENT '付款链接',
  `payment_url_time` STRING COMMENT '支付链接生成时间',
  `ems_student_id` int COMMENT 'ems的学生id',
  `delete_reason` STRING COMMENT '删除原因',
  `deleter` int COMMENT '删除人',
  `deleter_name` STRING COMMENT '删除人姓名',
  `delete_time` STRING COMMENT '删除时间',
  `course_id` int COMMENT '课程ID',
  `course_name` STRING COMMENT '课程名称',
  `delete_comment` STRING COMMENT '删除原因说明',
  `close_state` STRING COMMENT '关闭装填',
  `close_time` STRING COMMENT '关闭状态变动时间',
  `appeal_id` int COMMENT '申诉id',
  `tenant` int COMMENT '租户',
  `total_fee` DECIMAL COMMENT '报名费总金额',
  `belonged` int COMMENT '小周期归属人',
  `belonged_time` STRING COMMENT '归属时间',
  `belonger_time` STRING COMMENT '归属时间',
  `transfer` int COMMENT '转移人',
  `transfer_time` STRING COMMENT '转移时间',
  `follow_type` int COMMENT '分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取',
  `transfer_bxg_oa_account` STRING COMMENT '转移到博学谷归属人OA账号',
  `transfer_bxg_belonger_name` STRING COMMENT '转移到博学谷归属人OA姓名')
comment '客户关系表'
PARTITIONED BY(dt STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='ZLIB');


CREATE TABLE IF NOT EXISTS edu_ods.customer_clue (
  id int COMMENT 'customer_clue_id',
  create_date_time STRING COMMENT '创建时间',
  update_date_time STRING COMMENT '最后更新时间',
  deleted STRING COMMENT '是否被删除（禁用）',
  customer_id int COMMENT '客户id',
  customer_relationship_id int COMMENT '客户关系id',
  session_id STRING COMMENT '七陌会话id',
  sid STRING COMMENT '访客id',
  status STRING COMMENT '状态（undeal待领取 deal 已领取 finish 已关闭 changePeer 已流转）',
  users STRING COMMENT '所属坐席',
  create_time STRING COMMENT '七陌创建时间',
  platform STRING COMMENT '平台来源 （pc-网站咨询|wap-wap咨询|sdk-app咨询|weixin-微信咨询）',
  s_name STRING COMMENT '用户名称',
  seo_source STRING COMMENT '搜索来源',
  seo_keywords STRING COMMENT '关键字',
  ip STRING COMMENT 'IP地址',
  referrer STRING COMMENT '上级来源页面',
  from_url STRING COMMENT '会话来源页面',
  landing_page_url STRING COMMENT '访客着陆页面',
  url_title STRING COMMENT '咨询页面title',
  to_peer STRING COMMENT '所属技能组',
  manual_time STRING COMMENT '人工开始时间',
  begin_time STRING COMMENT '坐席领取时间 ',
  reply_msg_count int COMMENT '客服回复消息数',
  total_msg_count int COMMENT '消息总数',
  msg_count int COMMENT '客户发送消息数',
  comment STRING COMMENT '备注',
  finish_reason STRING COMMENT '结束类型',
  finish_user STRING COMMENT '结束坐席',
  end_time STRING COMMENT '会话结束时间',
  platform_description STRING COMMENT '客户平台信息',
  browser_name STRING COMMENT '浏览器名称',
  os_info STRING COMMENT '系统名称',
  area STRING COMMENT '区域',
  country STRING COMMENT '所在国家',
  province STRING COMMENT '省',
  city STRING COMMENT '城市',
  creator int COMMENT '创建人',
  name STRING COMMENT '客户姓名',
  idcard STRING COMMENT '身份证号',
  phone STRING COMMENT '手机号',
  itcast_school_id int COMMENT '校区Id',
  itcast_school STRING COMMENT '校区',
  itcast_subject_id int COMMENT '学科Id',
  itcast_subject STRING COMMENT '学科',
  wechat STRING COMMENT '微信',
  qq STRING COMMENT 'qq号',
  email STRING COMMENT '邮箱',
  gender STRING COMMENT '性别',
  level STRING COMMENT '客户级别',
  origin_type STRING COMMENT '数据来源渠道',
  information_way STRING COMMENT '资讯方式',
  working_years STRING COMMENT '开始工作时间',
  technical_directions STRING COMMENT '技术方向',
  customer_state STRING COMMENT '当前客户状态',
  valid STRING COMMENT '该线索是否是网资有效线索',
  anticipat_signup_date STRING COMMENT '预计报名时间',
  clue_state STRING COMMENT '线索状态',
  scrm_department_id int COMMENT 'SCRM内部部门id',
  superior_url STRING COMMENT '诸葛获取上级页面URL',
  superior_source STRING COMMENT '诸葛获取上级页面URL标题',
  landing_url STRING COMMENT '诸葛获取着陆页面URL',
  landing_source STRING COMMENT '诸葛获取着陆页面URL来源',
  info_url STRING COMMENT '诸葛获取留咨页URL',
  info_source STRING COMMENT '诸葛获取留咨页URL标题',
  origin_channel STRING COMMENT '投放渠道',
  course_id int COMMENT '课程编号',
  course_name STRING COMMENT '课程名称',
  zhuge_session_id STRING COMMENT 'zhuge会话id',
  is_repeat int COMMENT '是否重复线索(手机号维度) 0:正常 1：重复',
  tenant int COMMENT '租户id',
  activity_id STRING COMMENT '活动id',
  activity_name STRING COMMENT '活动名称',
  follow_type int COMMENT '分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取',
  shunt_mode_id int COMMENT '匹配到的技能组id',
  shunt_employee_group_id int COMMENT '所属分流员工组')
comment '客户线索表'
PARTITIONED BY(dt STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='ZLIB');

-- 客户表:
CREATE TABLE IF NOT EXISTS edu_ods.`customer` (
  `id` int COMMENT 'key id',
  `customer_relationship_id` int COMMENT '当前意向id',
  `create_date_time` STRING COMMENT '创建时间',
  `update_date_time` STRING COMMENT '最后更新时间',
  `deleted` int  COMMENT '是否被删除（禁用）',
  `name` STRING COMMENT '姓名',
  `idcard` STRING  COMMENT '身份证号',
  `birth_year` int COMMENT '出生年份',
  `gender` STRING COMMENT '性别',
  `phone` STRING COMMENT '手机号',
  `wechat` STRING COMMENT '微信',
  `qq` STRING COMMENT 'qq号',
  `email` STRING COMMENT '邮箱',
  `area` STRING COMMENT '所在区域',
  `leave_school_date` date COMMENT '离校时间',
  `graduation_date` date COMMENT '毕业时间',
  `bxg_student_id` STRING COMMENT '博学谷学员ID，可能未关联到，不存在',
  `creator` int COMMENT '创建人ID',
  `origin_type` STRING COMMENT '数据来源',
  `origin_channel` STRING COMMENT '来源渠道',
  `tenant` int,
  `md_id` int COMMENT '中台id')
comment '客户表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

-- 学科表
CREATE TABLE IF NOT EXISTS edu_ods.`itcast_subject` (
  `id` int COMMENT '自增主键',
  `create_date_time` timestamp COMMENT '创建时间',
  `update_date_time` timestamp COMMENT '最后更新时间',
  `deleted` STRING COMMENT '是否被删除（禁用）',
  `name` STRING COMMENT '学科名称',
  `code` STRING COMMENT '学科编码',
  `tenant` int COMMENT '租户')
comment '学科字典表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

-- 校区表
CREATE TABLE IF NOT EXISTS edu_ods.`itcast_school` (
  `id` int COMMENT '自增主键',
  `create_date_time` timestamp COMMENT '创建时间',
  `update_date_time` timestamp  COMMENT '最后更新时间',
  `deleted` STRING COMMENT '是否被删除（禁用）',
  `name` STRING COMMENT '校区名称',
  `code` STRING COMMENT '校区标识',
  `tenant` int COMMENT '租户')
comment '校区字典表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

-- 员工表
CREATE TABLE IF NOT EXISTS edu_ods.employee (
  id int COMMENT '员工id',
  email STRING COMMENT '公司邮箱，OA登录账号',
  real_name STRING COMMENT '员工的真实姓名',
  phone STRING COMMENT '手机号，目前还没有使用；隐私问题OA接口没有提供这个属性，',
  department_id STRING COMMENT 'OA中的部门编号，有负值',
  department_name STRING COMMENT 'OA中的部门名',
  remote_login STRING COMMENT '员工是否可以远程登录',
  job_number STRING COMMENT '员工工号',
  cross_school STRING COMMENT '是否有跨校区权限',
  last_login_date STRING COMMENT '最后登录日期',
  creator int COMMENT '创建人',
  create_date_time STRING COMMENT '创建时间',
  update_date_time STRING COMMENT '最后更新时间',
  deleted STRING COMMENT '是否被删除（禁用）',
  scrm_department_id int COMMENT 'SCRM内部部门id',
  leave_office STRING COMMENT '离职状态',
  leave_office_time STRING COMMENT '离职时间',
  reinstated_time STRING COMMENT '复职时间',
  superior_leaders_id int COMMENT '上级领导ID',
  tdepart_id int COMMENT '直属部门',
  tenant int COMMENT '租户',
  ems_user_name STRING COMMENT 'ems用户名称'
)
comment '员工表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

-- 部门表
CREATE TABLE IF NOT EXISTS edu_ods.`scrm_department` (
  `id` int COMMENT '部门id',
  `name` STRING COMMENT '部门名称',
  `parent_id` int COMMENT '父部门id',
  `create_date_time` STRING COMMENT '创建时间',
  `update_date_time` STRING COMMENT '更新时间',
  `deleted` STRING COMMENT '删除标志',
  `id_path` STRING COMMENT '编码全路径',
  `tdepart_code` int COMMENT '直属部门',
  `creator` STRING COMMENT '创建者',
  `depart_level` int COMMENT '部门层级',
  `depart_sign` int COMMENT '部门标志，暂时默认1',
  `depart_line` int COMMENT '业务线，存储业务线编码',
  `depart_sort` int COMMENT '排序字段',
  `disable_flag` int COMMENT '禁用标志',
  `tenant` int COMMENT '租户')
comment 'scrm部门表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

CREATE EXTERNAL TABLE IF NOT EXISTS edu_ods.`customer_appeal` (
  `id` int COMMENT 'customer_appeal_id',
  `customer_relationship_first_id` int COMMENT '第一条客户关系id',
  `employee_id` int COMMENT '申诉人',
  `employee_name` STRING COMMENT '申诉人姓名',
  `employee_department_id` int COMMENT '申诉人部门',
  `employee_tdepart_id` int COMMENT '申诉人所属部门',
  `appeal_status` int COMMENT '申诉状态，0:待稽核 1:无效 2：有效',
  `audit_id` int COMMENT '稽核人id',
  `audit_name` STRING COMMENT '稽核人姓名',
  `audit_department_id` int COMMENT '稽核人所在部门',
  `audit_department_name` STRING COMMENT '稽核人部门名称',
  `audit_date_time` STRING COMMENT '稽核时间',
  `create_date_time` STRING COMMENT '创建时间（申诉时间）',
  `update_date_time` STRING COMMENT '更新时间',
  `deleted` STRING COMMENT '删除标志位',
  `tenant` int COMMENT '租户id') 
comment '客户申诉表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY','orc.create.index'='true','orc.bloom.filter.columns'='appeal_status,customer_relationship_first_id');

DWD层

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
-- 拉链表
CREATE TABLE IF NOT EXISTS zz18_edu_dwd_ljw.`customer_relationship_zipper` (
  `id` int COMMENT '客户关系id',
  `create_date_time` STRING COMMENT '创建时间',
  `update_date_time` STRING COMMENT '最后更新时间',
  `deleted` int COMMENT '是否被删除（禁用）',
  `customer_id` int COMMENT '所属客户id',
  `first_id` int COMMENT '第一条客户关系id',
  `belonger` int COMMENT '归属人',
  `belonger_name` STRING COMMENT '归属人姓名',
  `initial_belonger` int COMMENT '初始归属人',
  `distribution_handler` int COMMENT '分配处理人',
  `business_scrm_department_id` int COMMENT '归属部门',
  `last_visit_time` STRING COMMENT '最后回访时间',
  `next_visit_time` STRING COMMENT '下次回访时间',
  `origin_type` STRING COMMENT '数据来源',
  `itcast_school_id` int COMMENT '校区Id',
  `itcast_subject_id` int COMMENT '学科Id',
  `intention_study_type` STRING COMMENT '意向学习方式',
  `anticipat_signup_date` STRING COMMENT '预计报名时间',
  `level` STRING COMMENT '客户级别',
  `creator` int COMMENT '创建人',
  `current_creator` int COMMENT '当前创建人：初始==创建人，当在公海拉回时为 拉回人',
  `creator_name` STRING COMMENT '创建者姓名',
  `origin_channel` STRING COMMENT '来源渠道',
  `comment` STRING COMMENT '备注',
  `first_customer_clue_id` int COMMENT '第一条线索id',
  `last_customer_clue_id` int COMMENT '最后一条线索id',
  `process_state` STRING COMMENT '处理状态',
  `process_time` STRING COMMENT '处理状态变动时间',
  `payment_state` STRING COMMENT '支付状态',
  `payment_time` STRING COMMENT '支付状态变动时间',
  `signup_state` STRING COMMENT '报名状态',
  `signup_time` STRING COMMENT '报名时间',
  `notice_state` STRING COMMENT '通知状态',
  `notice_time` STRING COMMENT '通知状态变动时间',
  `lock_state` STRING COMMENT '锁定状态',
  `lock_time` STRING COMMENT '锁定状态修改时间',
  `itcast_clazz_id` int COMMENT '所属ems班级id',
  `itcast_clazz_time` STRING COMMENT '报班时间',
  `payment_url` STRING COMMENT '付款链接',
  `payment_url_time` STRING COMMENT '支付链接生成时间',
  `ems_student_id` int COMMENT 'ems的学生id',
  `delete_reason` STRING COMMENT '删除原因',
  `deleter` int COMMENT '删除人',
  `deleter_name` STRING COMMENT '删除人姓名',
  `delete_time` STRING COMMENT '删除时间',
  `course_id` int COMMENT '课程ID',
  `course_name` STRING COMMENT '课程名称',
  `delete_comment` STRING COMMENT '删除原因说明',
  `close_state` STRING COMMENT '关闭装填',
  `close_time` STRING COMMENT '关闭状态变动时间',
  `appeal_id` int COMMENT '申诉id',
  `tenant` int COMMENT '租户',
  `total_fee` DECIMAL COMMENT '报名费总金额',
  `belonged` int COMMENT '小周期归属人',
  `belonged_time` STRING COMMENT '归属时间',
  `belonger_time` STRING COMMENT '归属时间',
  `transfer` int COMMENT '转移人',
  `transfer_time` STRING COMMENT '转移时间',
  `follow_type` int COMMENT '分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取',
  `transfer_bxg_oa_account` STRING COMMENT '转移到博学谷归属人OA账号',
  `transfer_bxg_belonger_name` STRING COMMENT '转移到博学谷归属人OA姓名',
  `end_time` STRING COMMENT '有效截止时间')
comment '客户关系表'
PARTITIONED BY(start_time STRING)
clustered by(id) sorted by(id) into 10 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');


CREATE TABLE IF NOT EXISTS zz18_edu_dwd_ljw.customer_clue_zipper (
  id int COMMENT 'customer_clue_id',
  create_date_time STRING COMMENT '创建时间',
  update_date_time STRING COMMENT '最后更新 时间',
  deleted STRING COMMENT '是否被删除（禁用）',
  customer_id int COMMENT '客户id',
  customer_relationship_id int COMMENT '客户关系id',
  session_id STRING COMMENT '七陌会话id',
  sid STRING COMMENT '访客id',
  status STRING COMMENT '状态（undeal待领取 deal 已领取 finish 已关闭 changePeer 已流转）',
  users STRING COMMENT '所属坐席',
  create_time STRING COMMENT '七陌创建时间',
  platform STRING COMMENT '平台来源 （pc-网站咨询|wap-wap咨询|sdk-app咨询|weixin-微信咨询）',
  s_name STRING COMMENT '用户名称',
  seo_source STRING COMMENT '搜索来源',
  seo_keywords STRING COMMENT '关键字',
  ip STRING COMMENT 'IP地址',
  referrer STRING COMMENT '上级来源页面',
  from_url STRING COMMENT '会话来源页面',
  landing_page_url STRING COMMENT '访客着陆页面',
  url_title STRING COMMENT '咨询页面title',
  to_peer STRING COMMENT '所属技能组',
  manual_time STRING COMMENT '人工开始时间',
  begin_time STRING COMMENT '坐席领取时间 ',
  reply_msg_count int COMMENT '客服回复消息数',
  total_msg_count int COMMENT '消息总数',
  msg_count int COMMENT '客户发送消息数',
  comment STRING COMMENT '备注',
  finish_reason STRING COMMENT '结束类型',
  finish_user STRING COMMENT '结束坐席',
  end_time STRING COMMENT '会话结束时间',
  platform_description STRING COMMENT '客户平台信息',
  browser_name STRING COMMENT '浏览器名称',
  os_info STRING COMMENT '系统名称',
  area STRING COMMENT '区域',
  country STRING COMMENT '所在国家',
  province STRING COMMENT '省',
  city STRING COMMENT '城市',
  creator int COMMENT '创建人',
  name STRING COMMENT '客户姓名',
  idcard STRING COMMENT '身份证号',
  phone STRING COMMENT '手机号',
  itcast_school_id int COMMENT '校区Id',
  itcast_school STRING COMMENT '校区',
  itcast_subject_id int COMMENT '学科Id',
  itcast_subject STRING COMMENT '学科',
  wechat STRING COMMENT '微信',
  qq STRING COMMENT 'qq号',
  email STRING COMMENT '邮箱',
  gender STRING COMMENT '性别',
  level STRING COMMENT '客户级别',
  origin_type STRING COMMENT '数据来源渠道',
  information_way STRING COMMENT '资讯方式',
  working_years STRING COMMENT '开始工作时间',
  technical_directions STRING COMMENT '技术方向',
  customer_state STRING COMMENT '当前客户状态',
  valid STRING COMMENT '该线索是否是网资有效线索',
  anticipat_signup_date STRING COMMENT '预计报名时间',
  clue_state STRING COMMENT '线索状态',
  scrm_department_id int COMMENT 'SCRM内部部门id',
  superior_url STRING COMMENT '诸葛获取上级页面URL',
  superior_source STRING COMMENT '诸葛获取上级页面URL标题',
  landing_url STRING COMMENT '诸葛获取着陆页面URL',
  landing_source STRING COMMENT '诸葛获取着陆页面URL来源',
  info_url STRING COMMENT '诸葛获取留咨页URL',
  info_source STRING COMMENT '诸葛获取留咨页URL标题',
  origin_channel STRING COMMENT '投放渠道',
  course_id int COMMENT '课程编号',
  course_name STRING COMMENT '课程名称',
  zhuge_session_id STRING COMMENT 'zhuge会话id',
  is_repeat int COMMENT '是否重复线索(手机号维度) 0:正常 1：重复',
  tenant int COMMENT '租户id',
  activity_id STRING COMMENT '活动id',
  activity_name STRING COMMENT '活动名称',
  follow_type int COMMENT '分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取',
  shunt_mode_id int COMMENT '匹配到的技能组id',
  shunt_employee_group_id int COMMENT '所属分流员工组',
  ends_time STRING COMMENT '有效时间')
comment '客户关系表'
PARTITIONED BY(starts_time STRING)
clustered by(customer_relationship_id) sorted by(customer_relationship_id) into 10 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

-- 清洗转换
CREATE TABLE IF NOT EXISTS edu_dwd.`customer_relationship_trans` (
  `id` int COMMENT '客户关系id',
  `create_date_time` STRING COMMENT '创建时间',
  `update_date_time` STRING COMMENT '最后更新时间',
  `deleted` int COMMENT '是否被删除（禁用）',
  `customer_id` int COMMENT '所属客户id',
  `first_id` int COMMENT '第一条客户关系id',
  `belonger` int COMMENT '归属人',
  `belonger_name` STRING COMMENT '归属人姓名',
  `initial_belonger` int COMMENT '初始归属人',
  `distribution_handler` int COMMENT '分配处理人',
  `business_scrm_department_id` int COMMENT '归属部门',
  `last_visit_time` STRING COMMENT '最后回访时间',
  `next_visit_time` STRING COMMENT '下次回访时间',
  `origin_type` STRING COMMENT '数据来源',
  `itcast_school_id` int COMMENT '校区Id',
  `itcast_subject_id` int COMMENT '学科Id',
  `intention_study_type` STRING COMMENT '意向学习方式',
  `anticipat_signup_date` STRING COMMENT '预计报名时间',
  `level` STRING COMMENT '客户级别',
  `creator` int COMMENT '创建人',
  `current_creator` int COMMENT '当前创建人：初始==创建人，当在公海拉回时为 拉回人',
  `creator_name` STRING COMMENT '创建者姓名',
  `origin_channel` STRING COMMENT '来源渠道',
  `comment` STRING COMMENT '备注',
  `first_customer_clue_id` int COMMENT '第一条线索id',
  `last_customer_clue_id` int COMMENT '最后一条线索id',
  `process_state` STRING COMMENT '处理状态',
  `process_time` STRING COMMENT '处理状态变动时间',
  `payment_state` STRING COMMENT '支付状态',
  `payment_time` STRING COMMENT '支付状态变动时间',
  `signup_state` STRING COMMENT '报名状态',
  `signup_time` STRING COMMENT '报名时间',
  `notice_state` STRING COMMENT '通知状态',
  `notice_time` STRING COMMENT '通知状态变动时间',
  `lock_state` STRING COMMENT '锁定状态',
  `lock_time` STRING COMMENT '锁定状态修改时间',
  `itcast_clazz_id` int COMMENT '所属ems班级id',
  `itcast_clazz_time` STRING COMMENT '报班时间',
  `payment_url` STRING COMMENT '付款链接',
  `payment_url_time` STRING COMMENT '支付链接生成时间',
  `ems_student_id` int COMMENT 'ems的学生id',
  `delete_reason` STRING COMMENT '删除原因',
  `deleter` int COMMENT '删除人',
  `deleter_name` STRING COMMENT '删除人姓名',
  `delete_time` STRING COMMENT '删除时间',
  `course_id` int COMMENT '课程ID',
  `course_name` STRING COMMENT '课程名称',
  `delete_comment` STRING COMMENT '删除原因说明',
  `close_state` STRING COMMENT '关闭装填',
  `close_time` STRING COMMENT '关闭状态变动时间',
  `appeal_id` int COMMENT '申诉id',
  `tenant` int COMMENT '租户',
  `total_fee` DECIMAL COMMENT '报名费总金额',
  `belonged` int COMMENT '小周期归属人',
  `belonged_time` STRING COMMENT '归属时间',
  `belonger_time` STRING COMMENT '归属时间',
  `transfer` int COMMENT '转移人',
  `transfer_time` STRING COMMENT '转移时间',
  `follow_type` int COMMENT '分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取',
  `transfer_bxg_oa_account` STRING COMMENT '转移到博学谷归属人OA账号',
  `transfer_bxg_belonger_name` STRING COMMENT '转移到博学谷归属人OA姓名',
   hourinfo string comment '小时',
   origin_type_state comment '线上线下标识:0 线上  1线下'
)
comment '客户关系表'
PARTITIONED BY(yearinfo STRING,monthinfo string,dayinfo string)
clustered by(id) sorted by(id) into 10 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');


CREATE TABLE IF NOT EXISTS edu_dwd.customer_clue_trans (
  id int COMMENT 'customer_clue_id',
  create_date_time STRING COMMENT '创建时间',
  update_date_time STRING COMMENT '最后更新时间',
  deleted STRING COMMENT '是否被删除（禁用）',
  customer_id int COMMENT '客户id',
  customer_relationship_id int COMMENT '客户关系id',
  session_id STRING COMMENT '七陌会话id',
  sid STRING COMMENT '访客id',
  status STRING COMMENT '状态（undeal待领取 deal 已领取 finish 已关闭 changePeer 已流转）',
  users STRING COMMENT '所属坐席',
  create_time STRING COMMENT '七陌创建时间',
  platform STRING COMMENT '平台来源 （pc-网站咨询|wap-wap咨询|sdk-app咨询|weixin-微信咨询）',
  s_name STRING COMMENT '用户名称',
  seo_source STRING COMMENT '搜索来源',
  seo_keywords STRING COMMENT '关键字',
  ip STRING COMMENT 'IP地址',
  referrer STRING COMMENT '上级来源页面',
  from_url STRING COMMENT '会话来源页面',
  landing_page_url STRING COMMENT '访客着陆页面',
  url_title STRING COMMENT '咨询页面title',
  to_peer STRING COMMENT '所属技能组',
  manual_time STRING COMMENT '人工开始时间',
  begin_time STRING COMMENT '坐席领取时间 ',
  reply_msg_count int COMMENT '客服回复消息数',
  total_msg_count int COMMENT '消息总数',
  msg_count int COMMENT '客户发送消息数',
  comment STRING COMMENT '备注',
  finish_reason STRING COMMENT '结束类型',
  finish_user STRING COMMENT '结束坐席',
  end_time STRING COMMENT '会话结束时间',
  platform_description STRING COMMENT '客户平台信息',
  browser_name STRING COMMENT '浏览器名称',
  os_info STRING COMMENT '系统名称',
  area STRING COMMENT '区域',
  country STRING COMMENT '所在国家',
  province STRING COMMENT '省',
  city STRING COMMENT '城市',
  creator int COMMENT '创建人',
  name STRING COMMENT '客户姓名',
  idcard STRING COMMENT '身份证号',
  phone STRING COMMENT '手机号',
  itcast_school_id int COMMENT '校区Id',
  itcast_school STRING COMMENT '校区',
  itcast_subject_id int COMMENT '学科Id',
  itcast_subject STRING COMMENT '学科',
  wechat STRING COMMENT '微信',
  qq STRING COMMENT 'qq号',
  email STRING COMMENT '邮箱',
  gender STRING COMMENT '性别',
  level STRING COMMENT '客户级别',
  origin_type STRING COMMENT '数据来源渠道',
  information_way STRING COMMENT '资讯方式',
  working_years STRING COMMENT '开始工作时间',
  technical_directions STRING COMMENT '技术方向',
  customer_state STRING COMMENT '当前客户状态',
  valid STRING COMMENT '该线索是否是网资有效线索',
  anticipat_signup_date STRING COMMENT '预计报名时间',
  clue_state STRING COMMENT '线索状态',
  scrm_department_id int COMMENT 'SCRM内部部门id',
  superior_url STRING COMMENT '诸葛获取上级页面URL',
  superior_source STRING COMMENT '诸葛获取上级页面URL标题',
  landing_url STRING COMMENT '诸葛获取着陆页面URL',
  landing_source STRING COMMENT '诸葛获取着陆页面URL来源',
  info_url STRING COMMENT '诸葛获取留咨页URL',
  info_source STRING COMMENT '诸葛获取留咨页URL标题',
  origin_channel STRING COMMENT '投放渠道',
  course_id int COMMENT '课程编号',
  course_name STRING COMMENT '课程名称',
  zhuge_session_id STRING COMMENT 'zhuge会话id',
  is_repeat int COMMENT '是否重复线索(手机号维度) 0:正常 1：重复',
  tenant int COMMENT '租户id',
  activity_id STRING COMMENT '活动id',
  activity_name STRING COMMENT '活动名称',
  follow_type int COMMENT '分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取',
  shunt_mode_id int COMMENT '匹配到的技能组id',
  shunt_employee_group_id int COMMENT '所属分流员工组',
  clue_state_stat STRING COMMENT '新老客户:  0 新  1 老',
  clue_appeal_state STRING COMMENT '有效无效: 0 有效  1无效')
comment '客户关系表'
PARTITIONED BY(dt STRING)
clustered by(customer_relationship_id) sorted by(customer_relationship_id) into 10 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

DWB层

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE IF NOT EXISTS edu_dwb.`itcast_intention_dwb` (
  `customer_id` STRING COMMENT 'id信息',
  `create_date_time` STRING COMMENT '创建时间',
  `area` STRING COMMENT '区域信息',
  `itcast_school_id` STRING COMMENT '校区id',
  `itcast_school_name` STRING COMMENT '校区名称',
  `origin_type` STRING COMMENT '来源渠道',
  `itcast_subject_id` STRING COMMENT '学科id',
  `itcast_subject_name` STRING COMMENT '学科名称',
  `hourinfo` STRING COMMENT '小时信息',
  `origin_type_stat` STRING COMMENT '数据来源:0.线下；1.线上',
  `clue_state_stat` STRING COMMENT '新老客户：0.老客户；1.新客户',
  clue_appeal_state STRING COMMENT '是否有效线索: 0 有效 1无效',
  `tdepart_id` STRING COMMENT '创建者部门id',
  `tdepart_name` STRING COMMENT '咨询中心名称'
)
comment '客户意向dwm表'
PARTITIONED BY(yearinfo STRING,monthinfo STRING,dayinfo STRING)
clustered by(customer_id) sorted by(customer_id) into 10 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');

DM层

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 意向量结果表:
CREATE TABLE IF NOT EXISTS edu_dm.itcast_intention_dm (
   `customer_total` INT COMMENT '聚合意向客户数',
   `clue_nums` INT COMMENT '根据id聚合有效线索数',
   `area` STRING COMMENT '区域信息',
   `itcast_school_id` STRING COMMENT '校区id',
   `itcast_school_name` STRING COMMENT '校区名称',
   `origin_type` STRING COMMENT '来源渠道',
   `itcast_subject_id` STRING COMMENT '学科id',
   `itcast_subject_name` STRING COMMENT '学科名称',
   `hourinfo` STRING COMMENT '小时信息',
   `origin_type_stat` STRING COMMENT '数据来源:0.线下；1.线上',
   `clue_state_stat` STRING COMMENT '客户属性：0.老客户；1.新客户',
   `tdepart_id` STRING COMMENT '创建者部门id',
   `tdepart_name` STRING COMMENT '咨询中心名称',
   `time_str` STRING COMMENT '时间明细',
   `groupType` STRING COMMENT '产品属性类别：1.总累计值；2.区域信息；3.校区、学科组合分组；4.来源渠道；5.咨询中心;',
   `time_type` STRING COMMENT '时间维度：1、按小时聚合；2、按天聚合；3、按周聚合；4、按月聚合；5、按年聚合；'
)
comment '客户意向dws表'
PARTITIONED BY(yearinfo STRING,monthinfo STRING,dayinfo STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

数据采集

业务数据-ODS层

这里是采用Sqoop脚本实现的.

全量导入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
#!/bin/bash

# 全量导入脚本
#  sqoop 脚本(从MySQL导入数据到ODS层)

if [ $# == 1 ]
then
	DATE_DT = $1  # 如果传入有1个参数,则取第一个参数
else
	DATE_DT = `date -d '1 days ago' +'%Y-%m-%d'`  # 如果传入的参数大于1个,或者没有参数,获取昨天的时间
fi  # if判断结束

export SQOOP_HOME=/usr/bin/sqoop

jdbcUrl='jdbc:mysql://106.75.33.59:3306/scrm?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true'
username='bjclass58'
password='bjclass58_itcast'


# 客户关系表 customer_relationship全量导入
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect ${jdbcUrl} \
--username ${username} \
--password ${password} \
--query "select *,'${DATE_DT}' as dt from customer_relationship where 1=1 and (create_date_time  between '1970-01-01 00:00:00' and '${DATE_DT} 23:59:59') and \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table customer_relationship \
-m 1

# 客户线索表 customer_clue全量导入
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect ${jdbcUrl} \
--username ${username} \
--password ${password} \
--query "select *,'${DATE_DT}' as dt from customer_clue where 1=1 and (create_date_time  between '1970-01-01 00:00:00' and '${DATE_DT} 23:59:59') and \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table customer_clue \
-m 1

# 客户表 customer全量导入
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect ${jdbcUrl} \
--username ${username} \
--password ${password} \
--query "select *,'${DATE_DT}' as start_time from customer where 1=1 and (create_date_time  between '1970-01-01 00:00:00' and '${DATE_DT} 23:59:59') and \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table customer \
-m 1

# 学科表 itcast_subject全量导入
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect ${jdbcUrl} \
--username ${username} \
--password ${password} \
--query "select *,'${DATE_DT}' as start_time from itcast_subject where 1=1 and (create_date_time  between '1970-01-01 00:00:00' and '${DATE_DT} 23:59:59') and \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table itcast_subject \
-m 1

# 校区表 itcast_school全量导入
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect ${jdbcUrl} \
--username ${username} \
--password ${password} \
--query "select *,'${DATE_DT}' as start_time from itcast_school where 1=1 and (create_date_time  between '1970-01-01 00:00:00' and '${DATE_DT} 23:59:59') and \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table itcast_school \
-m 1

# 员工表 employee全量导入
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect ${jdbcUrl} \
--username ${username} \
--password ${password} \
--query "select *,'${DATE_DT}' as start_time from employee where 1=1 and (create_date_time  between '1970-01-01 00:00:00' and '${DATE_DT} 23:59:59') and \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table employee \
-m 1

# 部门表 scrm_department全量导入
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect ${jdbcUrl} \
--username ${username} \
--password ${password} \
--query "select *,'${DATE_DT}' as start_time from scrm_department where 1=1 and (create_date_time  between '1970-01-01 00:00:00' and '${DATE_DT} 23:59:59') and \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table scrm_department \
-m 1

# 客户申诉表 customer_appeal全量导入
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect ${jdbcUrl} \
--username ${username} \
--password ${password} \
--query "select *,'${DATE_DT}' as start_time from customer_appeal where 1=1 and (create_date_time  between '1970-01-01 00:00:00' and '${DATE_DT} 23:59:59') and \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table customer_appeal \
-m 1

增量导入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
#!/bin/bash

# 增量导入脚本
#  sqoop 脚本(从MySQL导入数据到ODS层)

# 定义变量获取昨天的时间
DATE=`date -d '1 days ago' +'%Y-%m-%d'`  

if [ $# == 1 ]
then
	DATE_DT = $1  # 如果传入有1个参数,则取第一个参数
else
	DATE_DT = `date -d '1 days ago' +'%Y-%m-%d'`  # 如果传入的参数大于1个,或者没有参数,获取昨天的时间
fi  # if判断结束

export SQOOP_HOME=/usr/bin/sqoop

jdbcUrl='jdbc:mysql://106.75.33.59:3306/scrm?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true'
username='bjclass58'
password='bjclass58_itcast'


# 客户关系表 customer_relationship增量导入
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect ${jdbcUrl} \
--username ${username} \
--password ${password} \
--query "select *,'${DATE_DT}' as dt from customer_relationship where 1=1 and (create_date_time  between '${DATE_DT} 00:00:00' and '${DATE_DT} 23:59:59') and \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table customer_relationship \
-m 1

# 客户线索表 customer_clue增量导入
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect ${jdbcUrl} \
--username ${username} \
--password ${password} \
--query "select *,'${DATE_DT}' as dt from customer_clue where 1=1 and (create_date_time  between '${DATE_DT} 00:00:00' and '${DATE_DT} 23:59:59') and \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table customer_clue \
-m 1

ODS-DWD

客户关系表: customer_relationship_trans(主要是做数据清洗转换的)

学科表,校区表,部门表不需要做拉链表
客户表,员工表,客户申诉表需要做拉链表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
-- map阶段内存不足
set mapreduce.map.memory.mb=10150; 
set mapreduce.map.java.opts=-Xmx6144m;
-- reduce阶段内存不足
set mapreduce.reduce.memory.mb=10150; 
set mapreduce.reduce.java.opts=-Xmx8120m;

insert into zz18_edu_dwd_ljw.customer_relationship_trans partition(yearinfo,monthinfo,dayinfo)
select
    id,
    create_date_time,
    update_date_time,
    deleted,
    customer_id,
    first_id,
    belonger,
    belonger_name,
    initial_belonger,
    distribution_handler,
    business_scrm_department_id,
    last_visit_time,
    next_visit_time,
    origin_type,
    -- 清洗转换
    if(itcast_school_id is null or itcast_school_id=0,-1,itcast_school_id) as itcast_school_id,
    -- 这里由于源数据中itcast_subject_id没有0的存在,但是由于严谨还是写上0的情况
    if(itcast_subject_id is null,-1,if(itcast_subject_id =0, -1,itcast_subject_id)) as itcast_subject_id,
    intention_study_type,
    anticipat_signup_date,
    level,
    creator,
    current_creator,
    creator_name,
    origin_channel,
    `COMMENT`,
    first_customer_clue_id,
    last_customer_clue_id,
    process_state,
    process_time,
    payment_state,
    payment_time,
    signup_state,
    signup_time,
    notice_state,
    notice_time,
    lock_state,
    lock_time,
    itcast_clazz_id,
    itcast_clazz_time,
    payment_url,
    payment_url_time,
    ems_student_id,
    delete_reason,
    deleter,
    deleter_name,
    delete_time,
    course_id,
    course_name,
    delete_comment,
    close_state,
    close_time,
    appeal_id,
    tenant,
    total_fee,
    belonged,
    belonged_time,
    belonger_time,
    transfer,
    transfer_time,
    follow_type,
    transfer_bxg_oa_account,
    transfer_bxg_belonger_name,
    -- 数据转换
    substr(create_date_time,12,2) hourinfo,
    if(origin_type='NETSERVICE' or origin_type = 'PRESIGNUP','线上','线下') as origin_type_state,
    -- 将数据插入到分区字段中
    substr(create_date_time,1,4) yearinfo,
    substr(create_date_time,6,2) monthinfo,
    substr(create_date_time,9,2) dayinfo
from itcast_ods.customer_relationship;

客户关系拉链表

1
2
3
4
INSERT INTO TABLE zz18_edu_dwd_ljw.customer_clue_zipper partition(starts_time)
SELECT
    *
from itcast_ods.customer_clue;

其余表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
INSERT INTO TABLE zz18_edu_dwd_ljw.customer PARTITION (start_time)
SELECT
    *,
    '9999-99-99' as end_time
FROM itcast_ods.customer;

INSERT INTO TABLE zz18_edu_dwd_ljw.customer_appeal PARTITION (start_time)
SELECT
    *,
    '9999-99-99' as end_time
FROM itcast_ods.customer_appeal;

INSERT INTO TABLE zz18_edu_dwd_ljw.employee PARTITION (start_time)
SELECT
    *,
    '9999-99-99' as end_time
FROM itcast_ods.employee;

INSERT INTO TABLE zz18_edu_dwd_ljw.itcast_school PARTITION (start_time)
SELECT
    *
FROM itcast_ods.itcast_school;


INSERT INTO TABLE zz18_edu_dwd_ljw.itcast_subject PARTITION (start_time)
SELECT
    *
FROM itcast_ods.itcast_subject;

INSERT INTO TABLE zz18_edu_dwd_ljw.scrm_department PARTITION (start_time)
SELECT
    *
FROM itcast_ods.scrm_department;

客户线索表(清洗转换)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
INSERT INTO TABLE zz18_edu_dwd_ljw.customer_clue_trans PARTITION (dt)
select
    cc.id as id,
    cc.create_date_time as create_date_time,
    cc.update_date_time as update_date_time,
    cc.deleted as deleted,
    cc.customer_id as customer_id,
    cc.customer_relationship_id as customer_relationship_id,
    cc.session_id as session_id,
    cc.sid as sid,
    cc.status as status,
    cc.users as users,
    cc.create_time as create_time,
    cc.platform as platform,
    cc.s_name as s_name,
    cc.seo_source as seo_source,
    cc.seo_keywords as seo_keywords,
    cc.ip as ip,
    cc.referrer as referrer,
    cc.from_url as from_url,
    cc.landing_page_url as landing_page_url,
    cc.url_title as url_title,
    cc.to_peer as to_peer,
    cc.manual_time as manual_time,
    cc.begin_time as begin_time,
    cc.reply_msg_count as reply_msg_count,
    cc.total_msg_count as total_msg_count,
    cc.msg_count as msg_count,
    cc.comment as comment,
    cc.finish_reason as finish_reason,
    cc.finish_user as finish_user,
    cc.end_time as end_time,
    cc.platform_description as platform_description,
    cc.browser_name as browser_name,
    cc.os_info as os_info,
    cc.area as area,
    cc.country as country,
    cc.province as province,
    cc.city as city,
    cc.creator as creator,
    cc.name as name,
    cc.idcard as idcard,
    cc.phone as phone,
    cc.itcast_school_id as itcast_school_id,
    cc.itcast_school as itcast_school,
    cc.itcast_subject_id as itcast_subject_id,
    cc.itcast_subject as itcast_subject,
    cc.wechat as wechat,
    cc.qq as qq,
    cc.email as email,
    cc.gender as gender,
    cc.level as level,
    cc.origin_type as origin_type,
    cc.information_way as information_way,
    cc.working_years as working_years,
    cc.technical_directions as technical_directions,
    cc.customer_state as customer_state,
    cc.valid as valid,
    cc.anticipat_signup_date as anticipat_signup_date,
    cc.clue_state as clue_state,
    cc.scrm_department_id as scrm_department_id,
    cc.superior_url as superior_url,
    cc.superior_source as superior_source,
    cc.landing_url as landing_url,
    cc.landing_source as landing_source,
    cc.info_url as info_url,
    cc.info_source as info_source,
    cc.origin_channel as origin_channel,
    cc.course_id as course_id,
    cc.course_name as course_name,
    cc.zhuge_session_id as zhuge_session_id,
    cc.is_repeat as is_repeat,
    cc.tenant as tenant,
    cc.activity_id as activity_id,
    cc.activity_name as activity_name,
    cc.follow_type as follow_type,
    cc.shunt_mode_id as shunt_mode_id,
    cc.shunt_employee_group_id as shunt_employee_group_id,
--  if(clue_state='VALID_NEW_CLUES','新客户',),
    case cc.clue_state
        when 'VALID_NEW_CLUES' then '新客户'
        when 'VALID_PUBLIC_NEW_CLUE' then '老客户'
        else '无效客户'
    end as clue_state_stat,
    -- 通过customer_relationship表来关联投诉表customer_appeal,1:无效,1的筛选掉
    ca.appeal_status as clue_appeal_state,
    '2023-06-12' as dt
from itcast_ods.customer_clue cc 
left join itcast_ods.customer_relationship cr on cc.customer_relationship_id = cr.id
left join itcast_ods.customer_appeal ca on ca.customer_relationship_first_id=cc.customer_relationship_id
where ca.appeal_status != 1  AND ca.customer_relationship_first_id != 0;

访问咨询表(拉链表)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
insert into table zz18_edu_dwd_ljw.web_chat_ems partition(yearinfo,quarterinfo,monthinfo,dayinfo)
select
    id,
    create_date_time,
    session_id,
    sid,
    create_time,
    seo_source,
    seo_keywords,
    ip,
    area,
    country,
    province,
    city,
    origin_channel,
    user_match,
    manual_time,
    begin_time,
    end_time,
    last_customer_msg_time_stamp,
    last_agent_msg_time_stamp,
    reply_msg_count,
    msg_count,
    browser_name,
    os_info,
    substr(create_date_time,12,2) as hourinfo,
    substr(create_date_time,1,4) yearinfo,
    quarter(create_date_time) as quarterinfo,
    substr(create_date_time,6,2) monthinfo,
    substr(create_date_time,9,2) dayinfo
from itcast_ods.web_chat_ems;

DWD层-DWB层

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
insert into table zz18_edu_dwb_ljw.itcast_intention_dwb partition(yearinfo,monthinfo,dayinfo)
select
    crz.customer_id as customer_id,
    crz.create_date_time as create_date_time,
    c.area as area,
    crz.itcast_school_id as itcast_school_id,
    sc.name as itcast_school_name,
    crz.origin_type as origin_type,
    crz.itcast_subject_id as itcast_subject_id,
    sj.name as itcast_subject_name,
    substr(crz.create_date_time,12,2) as hourinfo,
    if(crz.origin_type='NETSERVICE' or crz.origin_type = 'PRESIGNUP','线上','线下') as origin_type_state,
    case ccz.clue_state
        when 'VALID_NEW_CLUES' then 1
        when 'VALID_PUBLIC_NEW_CLUE' then 0
        else null
    end as clue_state_stat,
    ca.appeal_status as clue_appeal_state,
    crz.business_scrm_department_id,
    scd.name,
    substr(crz.create_date_time,1,4) as yearinfo,
    substr(crz.create_date_time,6,2) as monthinfo,
    substr(crz.create_date_time,9,2) as dayinfo
from zz18_edu_dwd_ljw.customer_relationship_zipper as crz
left join zz18_edu_dwd_ljw.customer_clue_zipper ccz on ccz.customer_relationship_id=crz.id
left join zz18_edu_dwd_ljw.customer c on c.id=crz.customer_id
left join zz18_edu_dwd_ljw.itcast_school sc on sc.id=crz.itcast_school_id
left join zz18_edu_dwd_ljw.itcast_subject sj on sj.id=crz.itcast_subject_id
left join zz18_edu_dwd_ljw.customer_appeal ca on ca.customer_relationship_first_id=crz.id
left join zz18_edu_dwd_ljw.scrm_department scd on scd.id=crz.business_scrm_department_id;

DWB层-DM层

按照需求分别进行统计.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
-- 需求一: 计期内，新增意向客户（包含自己录入的意向客户）总数。
select
    count(distinct customer_id) as customer_total,
    '-1' as clue_nums,
    '-1' as area,
    '-1' as itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    yearinfo as time_str,
    '1' as grouptype,
    '5' as time_type,  -- 按年进行聚合操作
    yearinfo,
    '-1' as monthinfo,
    '-1' as dayinfo
from zz18_edu_dwb_ljw.itcast_intention_dwb
where clue_state_stat = 1
group by yearinfo,origin_type_stat,clue_state_stat;

select * from zz18_edu_dwb_ljw.itcast_intention_dwb;
-- 需求二: 统计指定时间段内，新增的意向客户，所在城市区域人数热力图。

select
    count(distinct customer_id) as customer_total,
    '-1' as clue_nums,
    area,
    '-1' as itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    -- 固定维度
    origin_type_stat,  -- 判断是否是线上,线下:0.线下；1.线上'
    clue_state_stat,  -- 是否是新增,或者老客户 0.老客户；1.新客户'
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat_ws('-',yearinfo,monthinfo,dayinfo) as time_str,  -- 如果这里有年月日的话要用concat拼接字符串
    '1' as grouptype,
    '5' as time_type,  -- 按年进行聚合操作
    yearinfo,
    '-1' as monthinfo,
    '-1' as dayinfo
from zz18_edu_dwb_ljw.itcast_intention_dwb
where clue_state_stat=1 and area is not null
group by yearinfo,monthinfo,dayinfo,origin_type_stat,clue_state_stat,area;

-- 需求三: 统计指定时间段内，新增的意向客户中，意向学科人数排行榜。学科名称要关联查询出来
select
    count(distinct customer_id) as customer_total,
    '-1' as clue_nums,
    '-1' as area,
    '-1' as itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    itcast_subject_id,
    itcast_subject_name,
    '-1' as hourinfo,
    -- 固定维度
    origin_type_stat,  -- 判断是否是线上,线下:0.线下；1.线上'
    clue_state_stat,  -- 是否是新增,或者老客户 0.老客户；1.新客户'
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    -- 固定维度
    concat_ws('-',yearinfo,monthinfo,dayinfo) as time_str,  -- 如果这里有年月日的话要用concat拼接字符串
    '3' as grouptype,  -- 产品属性类别：1.总累计值；2.区域信息；3.校区、学科组合分组；4.来源渠道；5.咨询中心;
    '2' as time_type,  -- 按天进行聚合操作1、按小时聚合；2、按天聚合；3、按周聚合；4、按月聚合；5、按年聚合；'
    yearinfo,
    '-1' as monthinfo,
    '-1' as dayinfo
from zz18_edu_dwb_ljw.itcast_intention_dwb
where clue_state_stat=1
group by yearinfo,monthinfo,dayinfo,origin_type_stat,clue_state_stat,itcast_subject_id,itcast_subject_name;

-- 需求四: 统计指定时间段内，新增的意向客户中，意向校区人数排行榜。
-- 需求五: 统计指定时间段内，新增的意向客户中，不同来源渠道的意向客户占比。
-- 需求六: 统计指定时间段内，新增的意向客户中，各咨询中心产生的意向客户数占比情况。
-- 需求七: 统计期内，访客咨询产生的有效线索的占比。有效线索量 / 咨询量，有效线索指的是拿到电话且电话有效。
select
        cast(count(1) as int) as clue_nums,
        '1' as group_type,  --'产品属性维度区分: 1 总累计值 2 区域信息 3 校区 4学科 5 来源渠道 6 咨询中心  ',
        '3' as time_type,    -- '时间标记: 1 小时  2 天  3月  4 年',
        '-1' as hourinfo,
        '-1' as dayinfo,
        origin_type_stat,
        '-1' as clue_state_stat,
        clue_appeal_state,
        yearinfo,
        monthinfo,
    cast(
           cast(count(1) as decimal(38,4))/
           cast((select
           count(1)
     from edu_ods.web_chat_ems
     where msg_count>=1)as decimal(38,4)) * 100 as decimal(5,2)
           ) as clue_percentage
from zz18_edu_dwb_ljw.itcast_intention_dwb
group by yearinfo,monthinfo,clue_appeal_state,origin_type_stat
-- 需求八: 统计期内，1-24h之间，每个时间段的有效线索转化率。横轴：1-24h，间隔为1h，纵轴：每个时间段的有效线索转化率。
-- 需求九:统计期内，新增的咨询客户中，有效线索的数量。