第二版数仓demo2.0(未完成

peoplename 姓名

dwd_tl_call主键字段：设备ID主要展示内容：设备ID呼出类型开始时间结束时间时长

生成零时表 a1

项目维度customer_status状态customer_category分类creator创建人first_class_item一级项目second_level_project二级项目product_name商品名称apply_time报名时间school校区

dwd_tl_order

名片维度card_status名片状态card_create_time名片创建时间province省份area地域url着陆页

当月或历史数据

使用etl工具直接在源系统直接获取

学员维度client_id客户IDstu_id学员IDstu_name学员姓名class_phone上课手机号qqQQwechat微信id_number证件号school校区apply_school报名分校province省份education学历age年龄wechat2微信2headteacher班主任exam_period考期

dm层表的字段数量是不固定的，根据报表和业务需要决定展示字段的多少，因为场景更针对性的原因所以可以剔除大量当前需求无用的数据来保证数据读取的效率

临时表c11

dwd_tl_orderapplicant_person 报名人数

记录名片属于在线名片

是

临时表c1

云dwd_tl_card主键字段:客户ID主要展示内容本地的首创，首分，首咨三张表的主要展示内容

ods_ak_phone 艾克通话时长

dws_重播统计

url = url

ods_silent_datas 电销静默表

判断是否还款

时间维度apply_time报名时间card_create_time名片创建时间pre_pay_date预补缴日期fa_time首次分配时间void_date作废时间contract_date签约完成时间

dwd_tl_first_consult首咨分配表fc_time首次分配时间fc_time as ny client_id 客户IDname_id 员工IDfc_teachar 首次分配人

首次拒绝即进入二次电联

订单环节开始

统计维度should_attend应出勤人数attend_reality实际出勤人数attend_rate出勤率return_watch回访观看次数practise随堂练习practise_pn随堂练习人数avg_score平均得分

主要字段：client_ID 客户IDalloc_attri分配归属人card_create_time名片创建时间url着陆页dialog_date对话日期dialog_status对话状态visitor_sign访客标识

dwd_ak_client_addaux_urldim_peopledim_project_peopleods_mantis_export.account_acting

dws层字段name_id人员IDseat_name坐席姓名call_date通话日期call_duration通话时长call_type通话类型call_status通话状态call_phone手机号码date日期model一级部门division二级部门legion三级部门dept四级部门group_name五级部门attri_people人名call_person_time会话人次reception_person_time接待人次return_visit_person_time回访人次kefu_response_num客服回复条数avg_response_second平均响应时长（秒）response_num响应条数

Container

产生退费工单

client_id=client_id

主要字段:creator创建人creator_institution创建人机构create_time创建时间alloc_time分配时间attri_people归属人

dwd层生成字段course_id来源IDcourse来源stu_name学生信息chat_time时间chat_content聊天信息interval_time弹幕间隔时长

url=url

①对字段进行清洗和转换，保留其主要字段

未分配的

dwd_tl_paytime

生成临时表T2

ods层生成字段first_class_item一级项目second_level_project二级项目course课程course_number课号module模块lesson课次platform直播平台teacher授课老师live_time直播时间should_attend应出勤人数attend_reality实际出勤人数attend_rate出勤率return_watch回访观看次数practise随堂练习practise_pn随堂练习人数avg_score平均得分

是否退费

课程维度platform直播平台live_time直播时间should_attend应出勤人数attend_reality实际出勤人数attend_rate出勤率return_watch回访观看次数practise随堂练习practise_pn随堂练习人数avg_score平均得分

事业部流量池

展示表中name字段，对ods表中的teacher字段进行清洗，获取正确的名字.

主要字段date日期callout_type呼出类型start_time开始时间end_time结束时间time_span时间长度download_addr下载地址

①：对fa_time和fc_time拼接成新的日期字段，清洗name字段②：model，division，legion，dept，group_name字段的清洗③：限制获取的数据为当月的

建立项目修改表，学员姓名映射表，课程映射表

临时表c9

剩余未加入维度的字段crowd_pack人群包carrier载体key_id关键字IDkey_name关键字名称search搜索词account_name账户名称match_way匹配方式unit_id单元IDunit_name单元名称

url 着陆页url 着陆页编码province 省份attributorsource account source_type item 学历

营收维度price售价should_pay_money应缴金额pay_reality已缴金额is_owe欠费标志child_status状态child_number子订单编号product商品

用people映射表获得正确的姓名

后端环节结束

项目维度first_class_item一级项目second_level_project二级项目agent代理商

课程维度course来源chat_time时间chat_content聊天信息

项目维度client_id客户IDfirst_class_item一级项目second_level_project二级项目q_pool_type题库类型q_pool_name题库名称subject科目

date日期client_id客户idprovince城市attributor推广人source渠道account账户source_type渠道item项目real_cost实际消费bill_cost平摊消费

名片

学员维度stu_name学生姓名class_phone上课手机号client_id客户id

客户维度name_idname姓名client_id客户IDcard_type名片类型phone联系电话intention意向度feedback_status反馈状态reason未接通原因l_dial_date最后一次拨打日期qqQQ

ods_tl_payment 螳螂财务支付明细表

咨询师/坐席维度attri_people归属人call_group呼叫组call_department呼叫部legion事业部project项目c_outcall_num当日外呼次数c_connect_num当日呼通的次数connect_time_day当日接通时长remark备注F_att_call首次分配归属呼叫部site_attribution现场归属l_feedback_time最后一次反馈时间outbound_sign智能外呼标识expiration_time过期时间final_consultation_time最后咨询时间invaild_type无效数据类型

dwd层生成字段stu_name学员client_id客户IDclass_phone上课手机号first_class_item一级项目subject科目test_name试卷名称start_time答题开始时间true_num正确数目false_num错误数目

订单维度main_number主订单编号child_status子订单状态child_number子订单编号school_ord_institution教务订单机构apply_school报名分校should_pay_money应缴金额pay_reality已缴金额afdis_money折算后金额pay_way支付方式remark备注ipipurl着陆页apply_area报名地域contract_status签约状态void_date作废时间confirm_money确认金额contract_date签约完成时间ord_create_time订单创建时间

根据ods源数据生成字段

ods_tl_order

客户维度client_id客户IDphone联系方式name姓名intention意向度qqQQwechat微信

dwd_tl_paytimepay_time支付时间url urlproduct_information商品信息name_id花名册idafdis_capitalflow折算后流水first_class_item一级项目pay_money支付金额(不含定金)fa_time首次分配时间card_create_time名片创建时间source_way来源方式client_id客户IDacc_revenue到账金额

fc_teacher = seat

financial_confirmation财务确认pay_record_status支付记录状态pay_type支付类型main_number主订单编号first_class_item一级分类second_level_project二级分类product_information商品名称ord_t_ampayable订单总应缴金额ord_t_ampaid订单总已缴金额pay_record_number支付记录编号pay_t_money支付总金额afdis_capitalflow折算后流水deposit使用定金pay_money支付金额(不含定金)service_charge手续费interest利息arrive_account_money到账金额spac_service_charge分账手续费spac_recevier_money分账接收方金额spac_surplus_money分账剩余金额pay_detail本次支付明细pay_channel付款渠道pay_time支付时间pay_remark支付备注confirmor确认人confirm_time确认时间receipt收据号financial_remark财务备注receipt_number收据编号receipt_status收据状态receipt_failure收据失败原因

生成临时表q5

dwd层生成字段customer_status状态stu_name姓名class_phone上课手机号client_id客户IDcustomer_category分类creator创建人customer_time客服单时间customer_content客服单内容principal_institution负责人机构principal负责人process_time处理时间processor处理人process_result处理结果first_class_item一级项目second_level_project二级项目product_name商品名称apply_time报名时间should_pay_money应缴金额pay_reality已缴金额refund退费金额apply_information报名信息attri_people咨询师attri_institution咨询师所属机构school校区refund_type退费类型

学员绑定公众号

从ods层中找出名片的原始客户ID，原始账户，原始创建时间，原始分配时间，原始首资时间字段用来做判断。①:通过判断原始创建时间和原始分配时间来判断分配至军团的时间。②:通过对原始创建时间和原始首资时间来判断名片分配至军团长的时间。③:通过对原始创建时间，原始分配时间，原始首资时间做条件判断来获得名片分配状态是已分配还是未分配，已分配的条件下是分配到军团还是直接分配到咨询师

展示表中name字段，对ods表中的teacher字段进行清洗，获取正确的名字

渠道维度url着陆页province省份

主键字段：client_ID 客户ID

分期还款订单

根据主题划分是以展示首次分配维度下的名片

名片分配维度：fc_time首次咨询时间fc_teacher首次咨询师l_counselor最后咨询师fa_attri_institution首次分配归属机构fa_attri_people首次分配归属人fa_time首次分配时间attri_people归属人call_group呼叫组call_department呼叫部legion事业部

投退环节结束

判断是否是直播课

云上的架构与本地不同，云上的tl_card名片表中囊括了三种不同主题的内容，更多的时候可以将dwd_tl_car名片表当成一张映射表来看待，所有涉及到名片的时间，三个主题下的归属人，三个主题下的所属机构都可以在这张表内获得验证

主要字段call_type呼叫类型dial_type拨打类型number_phone呼叫号码call_status通话状态call_date通话日期start_time开始时间ringing_time振铃时长call_duration通话时长recording录音

dwd_tl_online

dwd_cost

24h_refund 24小时退费表

dwd_allot_time名片分配表fa_time首次分配时间fa_time as ny client_id 客户IDname_id 员工IDfa_attri_people 首次分配人

还款

主键字段暂定stu_name评分者

获取逻辑

咨询师/坐席维度device_IMEI设备IMEIdevice_remark设备备注device_subordinate设备所属device_group设备分组

咨询师/坐席维度attri_people归属人attri_institution归属机构attri_legion归属事业部attri_call_dept归属呼叫部ord_operator订单操作人ord_agency订单操作机构apply_school报名分校school校区

名片维度province省份area地域project项目creator_institution创建人机构fc_teacher首次咨询师fc_teacher_institution首次咨询师机构fc_time首次咨询时间source_way来源方式url着陆页fa_attri_institution首次分配归属机构source来源

需要注意的是，create_time表中会包含首分和首咨的维度字段，但主要维度是以首创为主，名片字段没有其他不同

ods层生成字段agent代理商stu_name学员class_phone上课手机号client_id客户IDclass_type课程类型first_class_item一级项目second_level_project二级项目course_number名称module模块lesson课次start_time答题开始时间q_time做题时长is_hand是否交卷t_exercise题目总数true_num做对false_num做错score得分is_pass是否合格

时间维度apply_time报名时间

生成全款单

ods_tl_sale_detail 电销详细动作表

筛选customer_content客服单内容中出现的关键字来生成新字段。筛选customer_content客服单内容中出现关于测试数据的关键字将符合剔除条件的数据剔除。

名片维度fa_time首次分配时间fa_attri_people首次分配人fa_attri_institution首次分配归属机构source来源creator_institution创建人机构

payment_record_amend 修改表1pay_annal_ID  支付记录编号pay_annal_statusattributorattribution_grouppay_time  支付时间pay_conversion

否

dws_cost2生成字段date日期model归属人一级部门division归属人二级部门legion归属人三级部门dept归属人四级部门group_name归属人五级部门attri_people归属人model_s推广人一级部门division_s推广人二级部门legion_s推广人三级部门dept_s推广人四级部门group_name_s推广人五级部门director推广人source_type推广渠道类型item项目source推广渠道account账号province推广城市card_distribute_num 名片数cost单日消费

dwd_tl_call

dws_project

达到拉黑标准的名片

项目维度first_class_item一级项目second_level_project二级项目

时间维度start_time答题开始时间q_time做题时长

ods生成字段stu_name学生姓名class_phone上课手机号client_id客户idfirst_class_item一级项目second_level_project二级项目course录播课程course_number录播课号module模块lesson课次platform录播平台video_time视频时长enter_time进课时间leave_time离课时间study_time观看时长practise随堂练习practise_pn随堂练习人数avg_score平均得分

课程维度subject科目q_type做题类型test_name试卷名称start_time答题开始时间q_time做题时长is_hand是否交卷t_exercise题目总数true_num正确数目false_num错误数目score得分agent代理商

主要字段stu_name学员class_phone上课手机号client_id客户IDfirst_class_item一级项目second_level_project二级项目lesson课次start_time答题开始时间q_time做题时长score得分is_pass是否合格

dwd_ak_service

课程维度module模块lesson课次start_time答题开始时间q_time做题时长is_hand是否交卷t_exercise题目总数true_num做对false_num做错score得分is_pass是否合格

输出字段date日期model归属人一级部门division归属人二级部门legion归属人三级部门dept归属人四级部门group_name归属人五级部门attri_people归属人first_class_item一级项目class_type班型类别major专业up_type类型revenue营收undergraduate_num本科次数junior_num专科次数applicant_person报名人数applicant_order报名单数

ods_tl_student_order 螳螂订单管理

不需要额外关联表，对ods表中enter_time进课时间和leave_time离课时间做计算得出新字段

名单分配维度creator创建人creator_institution创建人机构create_time创建时间fc_time首次咨询时间fc_teacher首次咨询师l_counselor最后咨询师fa_attri_institution首次分配归属机构fa_attri_people首次分配归属人fa_time首次分配时间l_fc_time最后一次首咨分配时间

dwd_tl_plakback 螳螂录播考勤

payment_record_amend3

二次添加

dwd_tl_afterclass 螳螂题库练习

ods_tl_payment

attri_people = service

客户维度name_idclient_id客户IDcard_type名片类型phone联系电话intention意向度feedback_status反馈状态reason未接通原因l_dial_date最后一次拨打日期name姓名wechat_add_status微信添加状态qqQQprovince省份area地域project项目

生成临时表a3

dws表

ods_tl_work 螳螂工单表

时间维度call_date通话日期date日期

渠道维度source来源advertisers广告商url着陆页search搜索词key_word关键词ipIPprovince省份

dwd层生成字段name_id人员IDseat_name坐席姓名call_date通话日期call_duration通话时长call_type通话类型call_status通话状态call_phone手机号码

是否有意向

名片维度alloc_date分配日期fc_date首咨日期card_create_time名片创建时间

表中字段已经清洗完，关联完base_name辅助表后清洗出正确的五级架构，再根据时间获取

咨询师/坐席维度name_idattri_people归属人attri_institution归属机构receiver接待人

渠道维度source来源url着陆页province省份source_way来源方式

客户维度client_id客户IDname_idphone联系电话wechat微信project项目chat_duration聊天时长service客服phone2联系电话2wechat2微信2qqQQevaluat评价terminal终端

Client_ID = Client_ID

关联people姓名映射表获取正确的坐席姓名。清洗stu_name学员姓名字段中出现的测试数据

云dwd_tl_call主键字段：设备ID主要展示内容：设备ID呼出类型开始时间结束时间时长

展示所有字段，对表中start_time字段进行加工得到正确的开始答题时间

名片维度card_status名片状态card_create_time名片创建时间visitor_id访客IDsource来源ipIpalloc_attri分配归属人

名片分配维度alloc_date分配日期fc_date首咨日期card_create_time名片创建时间

ods_tl_plakback 螳螂录播考勤

dwd层主要字段stu_name学生姓名class_phone上课手机号client_id客户idfirst_class_item一级项目lesson课次enter_time进课时间leave_time离课时间act_study上课时长

改进逻辑

ods_tl_online

主要字段stu_name姓名class_phone上课手机号client_id客户IDcreator创建人urgency_degree紧急程度customer_time客服单时间principal负责人carbon_copy抄送人processor处理人process_time处理时间process_result处理结果first_class_item一级项目second_level_project二级项目product_name商品名称stu_id学员Idapply_time报名时间should_pay_money应缴金额pay_reality已缴金额refund退费金额attri_people咨询师school校区

dim/aux表

people peoplepeoplenamename_id

结束开学典礼

dwd_cost 消费表1date日期client_id客户idprovince城市attributor推广人source渠道account账户source_type渠道item项目real_cost实际消费bill_cost平摊消费

渠道维度source来源source_way来源方式advertisers广告商url着陆页ip IP地址ip_province IP省份ip_city IP城市attri_people归属人legion事业部province省份area地域project项目visitor_id访客IDalloc_type分配类型plan_id计划IDplan_name计划名称

dws数据来源于dwd

项目维度client_id客户IDclass_type课程类型first_class_item一级项目second_level_project二级项目

二次沟通转换

投退

退费维度partner_settle_charge合作方结算费用our_cost我司结算费用refund_type退费类型refund_reality已退费金额refund_underway退费中金额deduction_reality已扣费金额deduction_underway扣费中金额stu_id学员IDpre_pay_date预补缴日期

sale_name 姓名映射表attri_people 修改名name  被修改名name_id  员工ID

主要字段date日期model归属人一级部门division归属人二级部门legion归属人三级部门dept归属人四级部门group_name归属人五级部门attri_people归属人model_s推广人一级部门division_s推广人二级部门legion_s推广人三级部门dept_s推广人四级部门group_name_s推广人五级部门director推广人source_type推广渠道类型item项目source推广渠道account账号province推广城市cost单日消费

从分配和首资表中生成临时表T1和T2，根据表分别按照分配时间和首资时间两个维度获取当月数据。从消费表1中获取实际消费和平均消费两个字段后，关联表T1并关联销售架构表带出每条消费数据对应的销售人员和其五级架构，以此生成表T3。在最后的插入表阶段以t3表为主表连接t2表和项目架构表，得到首咨人和销售架构，然后插入

根据主题划分是以展示首次咨询维度为主

判断是A还是B模式

订单流失

tl_first_time取数逻辑从ods中映射carte_amend辅助表清洗出表中正确的首分机构，首分人，首分时间，然后连接sale_name映射第一次清洗人名，最后连接url着陆页清洗出正确的省份字段，将结果中的测试数据和手机号字段空值的数据剔除再将所有名字中包含B9·.的清洗，得到的结果既是dwd_tl_first_time表

课程是否满意

dwd_dim_people

展示所有字段，对表中headteacher字段判断，剔除掉所有测试数据和无效数据

流失

ods_tl_course 螳螂课程出勤ods_tl_playback 螳螂录播考勤ods_tl_study_record 螳螂上课记录表ods_tl_afterclass 螳螂题库练习ods_tl_inclass 螳螂随堂练习ods_ht_barrage欢拓弹幕ods_ht_grade欢拓评分ods_tl_student_order 螳螂订单管理

客户维度client_id客户IDphone手机号class_phone上课手机号NAME姓名

咨询师电联

是否通过好友

dwd层生成字段child_status状态child_number子订单编号apply_time报名时间stu_name学员姓名gender性别client_id客户IDcard_number证件号class_phone手机号phone1电话1phone2电话2first_class_item一级项目second_level_project二级项目product商品school校区should_pay_money应缴金额pay_reality已缴金额name_id人员IDheadteacher班主任is_public绑定公众号

dwd_tl_create_time主键字段:客户ID主要展示内容：首次创建时间create_time创建人creator创建归属机构creator_institution

建立Dim_stu_sale学生映射表，Dim_course课程映射表保证架构正确

①：将创建，分配，首咨三张表当月及上月数据并集②：匹配cate_amend，sale_name，url三张表③：清洗并集表中的creator，fa_time，fa_attri_institution，fc_time，creator_institution，url等字段，并剔除phone字段中所有的'139****0000'值和空值

客户维度stu_name学员client_id客户IDclass_phone上课手机号

项目维度first_class_item一级项目second_level_project二级项目product商品

对dwd_ak_phone和dwd_tl_call表做并和处理，两张表互相缺失的统计字段用0代替

主要字段call_phone通话号码client_id客户IDclient_name客户姓名seat_name坐席姓名number_phone呼叫号码call_status通话状态call_date通话日期start_time开始时间ringing_time振铃时长call_duration通话时长recording录音legion事业部call_department呼叫部call_group呼叫组

项目维度call_phone手机号码call_date通话日期call_duration通话时长call_type通话类型call_status通话状态call_person_time会话人次reception_person_time接待人次return_visit_person_time回访人次kefu_response_num客服回复条数response_num响应条数avg_response_second平均响应时长（秒）

ods_tl_call

attri_people=attri_people

ods_tl_afterclass 螳螂题库练习

关联条件people = seat_name

云dws_tl_card主要展示字段：归属人信息推广人信息渠道信息名片信息机会信息

dwd_tl_create_time

销售维度seat_id坐席分机seat_name坐席姓名call_type呼出类型dial_type拨打类型number_phone呼叫号码call_status通话状态call_date通话日期call_department呼叫部call_group呼叫组

客户维度client_id客户IDcard_type名片类型phone联系电话name姓名wechat微信wechat_add_status微信添加状态qq QQqq_add_status QQ添加状态reason未接通原因submit_information是否提交报考资料submit_time提交资料时间birth出生日期age年龄gender性别education学历phone2电话2wechat2微信2customer_status客户状态apply_num报名次数

事实维度agency机构principal负责人processor处理人process_time处理时间work_content工单内容process_result处理结果work_content工单内容process_result处理结果product_name商品名称stu_id学员IDreason_category原因大类reason_inclass原因中类（新）

dws_tl_student 螳螂学员管理

展示所有字段，对course加工得到course_id和course两个字段

投退老师处理工单

项目维度first_class_item一级分类second_level_project二级分类product商品classification_goods商品分类classification_belongs所属分类school校区source来源marketing_project营销项目advertisers广告商url着陆页search搜索词key_word关键词ipipcard_area名片地域card_creator名片创建人check_status审核状态l_allocation_type最后一次分配类型intention意向度

再次尝试转换

工单是否违规

销售模式通道

客户维度client_id客户IDclass_phone上课手机号process_status状态stu_id学员IDstu_name学员姓名

渠道维度project项目promotion_project推广项目province省份area地区visit_url访问网址url着陆页advertisers广告商promotion_account推广账户promote_plan推广计划Promote_unit推广单元match_way匹配方式key_word关键词first_click首次点击元素obtain_way获客方式if_success是否匹配成功qrcode_exposure_date二维码曝光日期wechatid_copy_num微信号复制次数origin_page来路页conversation_id会话IDrequest_method请求方式bcp_account_IDbcp推广账户IDreceive_type接待类型hosting_method托管方式escrow_account托管客服账号Robot_account机器人账号

临时表c10

dwd_tl_allot_time

展示所有字段，对表中enter_time和leave_time进行处理得到具体的act_study上课时长字段

date日期device_IMEI设备IMEIdevice_remark设备备注device_subordinate设备所属device_group设备分组opposite_number对方号码call_type呼叫类型start_time开始时间end_time结束时间time_span时间长度download_addr下载地址

dwd_tl_first_time主键字段:客户ID主要展示内容：首次咨询时间fc_time首次咨询师fc_teacher首次咨询机构fc_teacher_institution

ods_ak_call

订单

学员维度course来源list序号stu_name评分者

关联people姓名映射表获取正确的姓名。清洗device_subordinate设备所属字段获得正确的值

生成临时表T5

dwd_tl_attendance 螳螂教学通知stu_name学员姓名phone1电话1phone2电话2class_phone上课手机号firstproduct一级项目course_number课号lesson课次module模块teacher授课老师live_date直播时间f_time开始时间l_time结束时间

时间维度chat_time时间

ods_tl_orderdwd_tl_orderdwds_tl_order

产生订单

合表插入

临时表n

判断是否缴纳定金

根据principal负责人字段与people人名映射表来关联获得新字段并清洗

主键字段：client_id 客户ID

dwd_tl_course 螳螂课程出勤

wechat_add_status微信添加状态

ods_card_create_mon

费用维度child_number子订单编号apply_time报名时间price售价should_pay_money应缴金额pay_reality已缴金额is_owe欠费标志headteacher班主任

判断是否被捞起

项目维度client_id客户idfirst_class_item一级项目second_level_project二级项目

根据teacher授课老师与people人名映射表表关联获得新字段

客户维度call_phone呼叫号码client_id客户Id

date日期model一级部门division二级部门legion三级部门dept四级部门group_name五级部门sale_name人名cost单日消费revenue营收accounting_revenue账单分期营收cash_flow流水cur_week_revenue当周营收last_week_revenue上周营收big_term_revenue大跨期营收online__revenue在线营收message_revenue留言营收other_revenue其他营收card_distribute_num名片分配数new_card_distribute_num新名片分配数card_distribute_num_b_typeB模式名片分配数online_card_num在线名片数message_card_num留言名片数other_card_num其他名片数phone_card_num电销名片数web_card_num网销名片数applicant_person报名人数cur_weeek_applicant_person当周报名人数last_weeek_applicant_person上周报名人数cross_term_applicant_person跨期报名人数applicant_order报名单数cur_week_applicant_order当周报名单数last_week_applicant_order上周报名单数cross_term_applicant_order跨期报名单数online_order在线报名单数message_order留言报名单数other_order其他报名单数sale_change销售机会card_change_b_typeB模式名片机会数phone_change电销机会phone_online_change电销在线机会phone_message_change电销留言机会phone_other_change电销其他机会web_change网销机会web_online_change网销在线机会web_message_change网销留言机会web_other_change网销其他机会refund_24H24H退费call_person_time会话人次reception_person_time接待人次return_visit_person_time回访人次kefu_response_num客服回复条数avg_response_second平均响应时长（秒）response_num响应条数unresponse_num未响应条数return_visit_yes回访有回复tl_call_in_time螳螂呼入次数tl_call_in_duration螳螂呼入时长tl_call_out_time螳螂呼出次数tl_call_out_duration螳螂呼出时长ak_call_in_time艾克呼入次数ak_call_in_duration艾克呼入时长ak_call_out_time艾克呼出次数ak_call_out_duration艾克呼出时长

归属人/坐席维度operator操作人operator_agency操作人机构attribution_group归属机构名称

考试维度q_pool_type题库类型q_pool_name题库名称subject科目q_type做题类型test_name试卷名称

临时表a4

dwd_tl_student_order    螳螂订单管理

咨询师/坐席维度seat_id坐席分机seat_name坐席姓名legion事业部call_department呼叫部call_group呼叫组

当月及上月数据综合

客户维度client_id客户IDstu_name学员姓名class_phone上课手机号id_number证件号qqQQwechat微信wechat2微信2education学历age年龄

云dwd_ak_call主键字段：设备ID主要展示内容：设备ID呼出类型开始时间结束时间时长

完成录播课

dwd_dim_project_name  项目架构表ny年月name_id工号model一级部门division二级部门legion三级部门dept四级部门group_name五级部门name名字

ods_tl_inclass 螳螂随堂练习

输出字段：client_id客户IDcard_type名片类型phone联系电话intention意向度feedback_status反馈状态reason未接通原因l_dial_date最后一次拨打日期name姓名wechat_add_status微信添加状态qqQQprovince省份area地域project项目creator_institution创建人机构creator创建人create_time创建时间source_way来源方式url着陆页fa_time首次分配时间fa_attri_people首次分配人fa_attri_institution首次分配归属机构fc_time首次咨询时间fc_teacher首次咨询师fc_teacher_institution首次咨询机构source来源

未被拉黑待转化的名片

临时表a3

dwd_tl_payment

渠道维度agent代理商

名片维度create_time名片创建时间fc_time名片首咨时间alloc_date分配日期

ods_tl_student 螳螂学员管理

生成字段：call_phone呼叫号码client_id客户Idclient_name客户名称seat_id坐席分机seat_name坐席姓名legion事业部call_department呼叫部call_group呼叫组call_type呼叫类型dial_type拨打类型number_phone呼叫号码call_status通话状态call_date通话日期start_time开始时间ringing_time振铃时长call_duration通话时长recording录音

咨询师/坐席维度attri_people归属人attri_institution归属机构

展示所有字段，对表中teacher字段判断，剔除掉所有测试数据和无效数据

事实维度score评分content内容method方法effect效果message留言

dwd_dim_people  人员架构表

判断是否强制回购

违规处罚

二次沟通是否有意向

dwd_tl_work 螳螂工单表

dwd_dim_project_name

dwd_tl_student 螳螂学员管理

事实维度chat_time时间chat_content聊天信息

云dwd_tl_card获取逻辑将首分，首创，首资当月及历史共六张表限制日期为系统日期当月，然后将结果全部并集加起来得到一个初步汇总的结果集，再对这个结果集连接aux_card_amend和aux_tl_sale两张表，其中aux_tl_sale连接了三次，确保表中的员工ID，创建人，分配人，首资人都是正确的映射，最后清洗表中的url着陆页并剔除测试号码和号码为空的数据

主键字段client_id客户ID

①：匹配，ak_call_sale表

关联条件principal负责人 = people被修改人

统计维度enter_time进课时间leave_time离课时间study_time上课时长

此表在2021年12月以后无更新，需要确认是否使用

ods_tl_allot_time

临时表t4

date日期model归属人一级部门division归属人二级部门legion归属人三级部门dept归属人四级部门group_name归属人五级部门attri_people归属人model_s推广人一级部门division_s推广人二级部门legion_s推广人三级部门dept_s推广人四级部门group_name_s推广人五级部门director推广人source_type推广渠道类型source推广渠道account账号province推广城市item项目cost单日消费revenue营收accounting_revenue账单分期营收cash_flow流水cur_week_revenue当周营收last_week_revenue上周营收big_term_revenue大跨期营收online__revenue在线营收message_revenue留言营收other_revenue其他营收card_distribute_num名片分配数new_card_distribute_num新名片分配数card_distribute_num_b_typeB模式名片分配数online_card_num在线名片数message_card_num留言名片数other_card_num其他名片数phone_card_num电销名片数web_card_num网销名片数applicant_person报名人数cur_weeek_applicant_person当周报名人数last_weeek_applicant_person上周报名人数cross_term_applicant_person跨期报名人数applicant_order报名单数cur_week_applicant_order当周报名单数last_week_applicant_order上周报名单数cross_term_applicant_order跨期报名单数online_order在线报名单数message_order留言报名单数other_order其他报名单数sale_change销售机会card_change_b_typeB模式名片机会数phone_change电销机会phone_online_change电销在线机会phone_message_change电销留言机会phone_other_change电销其他机会web_change网销机会web_online_change网销在线机会web_message_change网销留言机会web_other_change网销其他机会refund_24H24H退费call_person_time会话人次reception_person_time接待人次return_visit_person_time回访人次kefu_response_num客服回复条数avg_response_second平均响应时长（秒）response_num响应条数unresponse_num未响应条数return_visit_yes回访有回复tl_call_in_time螳螂呼入次数tl_call_in_duration螳螂呼入时长tl_call_out_time螳螂呼出次数tl_call_out_duration螳螂呼出时长ak_call_in_time艾克呼入次数ak_call_in_duration艾克呼入时长ak_call_out_time艾克呼出次数ak_call_out_duration艾克呼出时长

时间字段create_time创建时间plan_time计划时间process_time处理时间

主要展示内容：分配人信息微信好友信息所属人信息省份通过日期等客户等级

url着陆页carte_amend名片修改表sale_name 姓名映射表

pay_record_number=pay_record_number

ods_paytime

完成分期还款单

dwd_tl_first_consult

ods_ak_service 艾克销售回访表

课程维度course录播课程course_number录播课号module模块lesson课次platform录播平台video_time视频时长enter_time进课时间leave_time离课时间

dm.B

ods_tl_customer_order 螳螂客服单表customer_status状态stu_name姓名class_phone上课手机号client_id客户IDcustomer_category分类creator创建人customer_time客服单时间urgency_degree紧急程度customer_content客服单内容principal_institution负责人机构principal负责人carbon_copy抄送人process_time处理时间processor处理人process_result处理结果first_class_item一级项目second_level_project二级项目product_name商品名称stu_id学员Idapply_time报名时间should_pay_money应缴金额pay_reality已缴金额refund退费金额apply_information报名信息attri_people咨询师attri_institution咨询师所属机构school校区

是否参加开学典礼

客户维度stu_name学员姓名class_phone上课手机号client_id客户ID

探需

客户维度stu_name学员class_phone上课手机号client_id客户ID

dwd_tl_call 螳螂通话表

不做逻辑判断，ods以获取全量数据为主，获取从爬虫端口上传的数据

生成临时表q4

客户维度stu_name学员姓名gender性别client_id客户IDclass_phone手机号card_number证件号phone1电话1phone2电话2is_owe欠费标志stu_label学员标签is_public绑定公众号pre_education_major前置学历专业

url

临时表t3

渠道维度province省份area地域project项目source来源

后端院校

account_plan

临时表a7

ods_ht_barrage 欢拓弹幕

ods_tl_customer_order 螳螂客服单表

dwd_tl_firs_consult

Client_ID = customer_ID

dm_tl_card_B获取逻辑：限制所有数据为B模式的数据，根据业务需要可以剔除大部分字段，或连接辅助表修改字段

ods_tl_study_record 螳螂上课记录表

dwd_tl_allot_timedwd_tl_create_timedwd_tl_first_time

①：在进入dwd_payment表中，进行对字段的清洗和转换，匹配修改表1中需要修改的数据②：保留字段为前端呈现报表时需要使用的字段，去除多余暂不启用字段

展示所有字段，对表中stu_name字段判断，剔除掉所有测试数据和无效数据

生成字段：visitor_sign访客标识phone手机号phone2电话2card_status名片状态card_create_time名片创建时间wechat微信wechat2微信2qqQQvisitor_id访客IDevaluat评价source来源project项目promotion_project推广项目province省份area地区service客服reservation是否预约ipIpvisit_url访问网址url着陆页chat_duration聊天时长visitor_message_num访客消息数service_message_num客服消息数dialog_date对话日期dialog_time对话时间terminal终端search搜索词advertisers广告商

项目维度agency机构principal负责人processor处理人work_category工单分类creator创建人creator_institution创建机构product_name商品名称stu_id学员ID待定model一级部门division二级部门legion三级部门dept四级部门group_name五级部门

ods层生成字段course来源list序号user_id用户IDstu_name学生信息chat_time时间chat_content聊天信息

dws_project_sale

员工维度platform直播平台teacher授课老师position职位name姓名family家族college学院department部门

生成定金单

临时表c5

对坐席分机或者坐席姓名进行聚合分组，统计每个坐席每天的通话人次，接待人次，回访人次以及每天的总回复数，平均响应时长和总响应条数

dwd_tl_paymentpay_record_number 支付记录编号

Client_ID = name_ID

咨询师/坐席维度attri_people归属人attri_institution归属机构call_department呼叫部legion事业部ord_operator订单操作人ord_agency订单操作机构ord_operate_institution教务订单归属Education_agency教务订单机构receiver接待人apply_school报名分校marketing_project营销项目school校区

dws_cost2

其他端口

dws_直播统计

base_namepositionnamereal_namefamilycollegedepartmentnote

dwd表

主要字段client_id客户IDstu_id学员IDstu_name学员姓名principal负责人processor处理人create_time创建时间plan_time计划时间process_time处理时间work_content工单内容process_result处理结果aging_create时效（按创建时间）aging_plan时效(按计划时间)work_category工单分类

①：与sale_name表进行匹配

时间维度lesson课次live_date直播时间f_time开始时间l_time结束时间

在考虑到订单的信息所需要的庞大信息量和需要信息的准确性的问题，在ods层获取之后，进行字段的清洗和转换

逾期

名片放入公海

ods层生成字段stu_name学员姓名phone1电话1phone2电话2class_phone上课手机号firstproduct一级项目course_number课号lesson课次module模块teacher授课老师live_date直播时间f_time开始时间l_time结束时间

对enter_time进课时间和leave_time离课时间判断得出act_study上课时长。匹配people姓名映射表获得正确的老师名字。匹配ods_tl_course课程出勤表得到正确的课程直播时间。

各开始分配名片

订单环节结束

临时表t1

ods_create_timeclient_id客户IDcreate_time 创建时间province 地区source 渠道account 账户source_type itemurl

attri_people = creator

课程维度class_type课程类型course_number名称module模块lesson课次

项目维度agent代理商first_class_item一级项目second_level_project二级项目

记录名片属于留言名片

主要字段：silent_time_l最后一次静默时间silent_num静默次数silent_status静默状态card_create_date名片创建时间distribution_logo分配标识label标签remark备注

后端环节开始

学员维度stu_name学员姓名gender性别client_id客户IDcard_number证件号class_phone手机号phone1电话1phone2电话2product商品school校区exam_period考期college学院名称stu_label学员标签is_public绑定公众号pre_education_major前置学历专业

dwd_tl_allot_time主键字段:客户ID主要展示内容:首次分配时间fa_time首次分配归属人fa_attri_people首次分配归属机构fa_attri_institution

生成临时表q7

dws_analyse_project

输出字段日期推广计划展现量点击量消费计划名插码返点财务消费账户渠道渠道类型城市负责人项目

直播课

学生维度stu_name学员姓名class_phone上课手机号client_id客户ID

dws_tl_work 螳螂工单表

渠道维度customer_source客户来源url着陆页province省份

艾克员工映射表ak_call_saledevice_IMEI 设备device_subordinatenamename_id

dwd_ak_call

ods_tl_call 螳螂话务查询

1.找出当月创建的名片，对应到消费计划表里按照日期，城市，渠道维度进行匹配，算出每个名片的消费2.之后找出无消费名片，按城市，渠道维度算出一月的平均值，和相同城市渠道的消费相比，谁小取谁作为无消费名片的消费，3.最后把有消费无名片的消费减去有名片无消费的消费，得到该月有消费无名片的消费，除以总名片数，分摊到每个名片，4.一张名片的消费即为对应的名片消费和分摊消费相加

dm_tl_card_A获取逻辑：限制所有数据为A模式的数据，根据业务需要可以剔除大部分字段，或连接辅助表修改字段

ods_tl_card云ods_tl_card

涉及辅助表及关联的表

客户维度opposite_number客户号码client_id 客户id

关联条件people = teacherlesson = lesson

生成临时表T

主键字段:client_ID 客户ID

时间维度teacher授课老师live_date直播时间f_time开始时间l_time结束时间

ods_tl_playback 螳螂录播考勤

stu_name学生姓名class_phone上课手机号client_id客户idfirst_class_item一级项目second_level_project二级项目course课程course_number课号module模块lesson课次platform直播平台teacher授课老师attend_way出勤方式enter_time进课时间leave_time离课时间study_time上课时长live_time直播时间should_attend应出勤人数attend_reality实际出勤人数attend_rate出勤率return_watch回访观看次数practise随堂练习practise_pn随堂练习人数avg_score平均得分

dws_cost

可生成Dim_stu_sale对学生ID和名字进行修改

项目维度stu_name学员姓名class_phone上课手机号client_id客户IDfirst_class_item一级项目second_level_project二级项目

产生学员

是否观看录播

主键字段

主键字段client_id 客户ID

carte_amendcustomer_ID 客户IDprovince 省份founder_agencyfirst_apportion_time 首次分配机构时间first_apportion_agencyadvertisers_URL 着陆页fc_time 首次咨询时间

流失并退费

课程老师

展示url用url中的网址来对主表数据进行过滤

ods层生成字段call_phone通话号码client_id客户IDclient_name客户姓名seat_id坐席分机seat_name坐席姓名call_type呼出类型dial_type拨打类型number_phone呼叫号码call_status通话状态call_date通话日期start_time开始时间ringing_time振铃时长call_duration通话时长recording录音legion事业部call_department呼叫部call_group呼叫组

判断是否按时还款

生成字段：client_id客户IDcard_type名片类型phone联系电话intention意向度feedback_status反馈状态reason未接通原因l_dial_date最后一次拨打日期name姓名wechat_add_status微信添加状态qqQQprovince省份area地域project项目creator_institution创建人机构creator创建人create_time创建时间source_way来源方式url着陆页fa_time首次分配时间fa_attri_people首次分配人fa_attri_institution首次分配归属机构fc_time首次咨询时间fc_teacher首次咨询师fc_teacher_institution首次咨询机构source来源

ods_conversation_mon

主要字段stu_name学员client_id客户IDclass_phone上课手机号first_class_item一级项目second_level_project二级项目subject科目q_pool_type题库类型q_pool_name题库名称test_name试卷名称

客户维度client_ID 客户IDvisitor_sign访客标识phone手机号phone2电话2wechat微信wechat2微信2qqQQevaluat评价empty_number空号号码number_status号码状态

开学典礼活跃动作

主要字段stu_name学员姓名phone1电话1phone2电话2class_phone上课手机号firstproduct一级项目course_number课号module模块lesson课次teacher授课老师live_date直播时间f_time开始时间l_time结束时间

生成临时表q6

对live_time字段截取后拼接获得进入时间和推出时间

dwd_tl_order revenue   营收 undergraduate_num  本科次数     junior_num   专科次数      

时间维度start_time开始时间end_time结束时间time_span时间长度

dwd_tl_attendance 螳螂教学通知

员工维度position职位name姓名family家族college学院department部门note备注

主要字段course来源list序号stu_name评分者mark_time评分时间score评分content内容method方法effect效果message留言

payment_record_amend2修改表2pay_record_numberattri_people

ods_tl_course 螳螂课程出勤

临时表a2

判断是否退费

项目维度first_class_item一级项目second_level_project二级项目course录播课程course_number录播课号module模块lesson课次platform录播平台

观看开学典礼

学员结课

课程维度school校区college学院名称headteacher班主任stu_label学员标签exam_period考期class_status开课状态apply_time报名时间

获取当月及上月的部分数据

77个字段全量获取

取消退费

课程维度course课程course_number课号module模块lesson课次teacher授课老师attend_rate出勤率

名片池

dwd_ak_call主键字段：设备ID主要展示内容：设备ID呼出类型开始时间结束时间时长

展示所有字段，对表中stu_name字段进行判断，剔除掉所有测试数据和无效数据

获取当月及上月部分数据

ods_tl_sale_detail 电销坐席详细动作表seat 坐席call_group 呼叫组

员工维度attri_people归属人attri_institution归属机构attri_legion归属事业部attri_call_dept归属呼叫部ord_operator订单操作人ord_agency订单操作机构school_ord_belong教务订单归属receiver接待人headteacher班主任fc_people首次咨询人

时间维度video_time视频时长enter_time进课时间leave_time离课时间study_time观看时长practise随堂练习practise_pn随堂练习人数avg_score平均得分

主要字段stu_name学员姓名class_phone上课手机号client_id客户IDfirst_class_item一级项目course课程course_number课号lesson课次enter_time进课时间leave_time离课时间study_time上课时长

客户维度call_phone通话号码client_id客户IDclient_name客户姓名

用户ID 序号来源课次时间课程类型上课手机号时长一级项目二级项目

临时表a1

学员维度user_id用户IDstu_name学生信息list序号

关联条件people = teacher

主键字段:Client_ID 客户ID

输出字段name_idseat_name坐席姓名call_type呼叫类型call_status通话状态call_date通话日期start_time开始时间ringing_time振铃时长call_duration通话时长call_group呼叫组

主要字段child_number子订单编号child_status状态stu_name学员姓名gender性别client_id客户IDclass_phone手机号phone1电话1phone2电话2card_number证件号first_class_item一级项目second_level_project二级项目school校区product商品should_pay_money应缴金额pay_reality已缴金额is_owe欠费标志headteacher班主任stu_label学员标签

dwd层字段course_id来源IDcourse来源stu_name学生信息chat_time时间chat_content聊天信息interval_time弹幕间隔时长

咨询师/坐席维度service客服reservation是否预约chat_duration聊天时长visitor_message_num访客消息数service_message_num客服消息数dialog_date对话日期dialog_time对话时间terminal终端search搜索词dialog_status对话状态custom_para自定参数add_to是否添加add_date添加日期stop_duration发起前停留时长probe_id探头Idprobe_name探头名称label_name标签名称l_search_keyword上一次搜索词is_preread是否预读satisfaction满意度welcome_send_num欢迎语发送次数

sale_name销售映射表attri_peoplenamename_id

学员维度client_id客户IDstu_name学员姓名class_phone上课手机号

名片分配维度creator_institution创建人机构creator创建人create_time创建时间source_way来源方式url着陆页fa_time首次分配时间fa_attri_people首次分配人fa_attri_institution首次分配归属机构fc_time首次咨询时间fc_teacher首次咨询师fc_teacher_institution首次咨询机构source来源

改进建议:生成一张dim层表，每天定时任务对ods原表进行获取，然后dws表存储过程直接调用dim表，不再跨级调用ods表，保证存储过程的风险承受能力

项目维度first_class_item一级项目second_level_project二级项目course课程course_number课号module模块lesson课次platform直播平台teacher授课老师

咨询师首次沟通

ods_tl_allot_time 首次分配表name_idclient_id客户IDcard_type名片类型phone联系电话intention意向度feedback_status反馈状态reason未接通原因l_dial_date最后一次拨打日期name姓名wechat_add_status微信添加状态qqQQprovince省份area地域project项目creator_institution创建人机构creator创建人create_time创建时间source_way来源方式url着陆页fa_time首次分配时间fa_attri_people首次分配人fa_attri_institution首次分配归属机构fc_time首次咨询时间fc_teacher首次咨询师fc_teacher_institution首次咨询机构source来源

dm_tl_card_A

dwd_ak_calldwd_tl_call云dwd_tl_call云dwd_tl_call

项目维度first_class_item一级项目second_level_project二级项目platform直播平台

渠道维度url着陆页province省份city城市customer_source客户来源

生成分期订单

ods_tl_attendance 螳螂教学通知

投退环节开始

首次拒绝进入二次沟通

dm_tl_card_B

24h_refund

统计维度start_time答题开始时间q_time做题时长is_hand是否交卷t_exercise题目总数true_num正确数目false_num错误数目score得分

生成临时表q3

ods_tl_first_consult

项目维度course来源list序号user_id用户ID

项目维度course来源list序号

二次电联

ods_ht_grade 欢拓评分

意向判断

tl_create_time取数逻辑从ods中映射carte_amend辅助表清洗出表中正确的首分机构，首分人，首分时间，然后连接sale_name映射第一次清洗人名，最后连接url着陆页清洗出正确的省份字段，将结果中的测试数据和手机号字段空值的数据剔除再将所有名字中包含B9·.的清洗，得到的结果既是dwd_tl_create_time表

ods_tl_create_time

需要注意的是虽然叫通话表但是整张表在ods-dwd两层中都没有涉及被通话的信息，备注，被通话人姓名，省份哪些在这张表中都没有体现，这张表在整个环节中用于统计和记录。

未出勤

生成临时表a2

制作课件

对会话人次在dwd层做SUM求和处理。对接待人次在dwd层做SUM求和处理。对回访人次在dwd层做求和处理。对客服回复条数在dwd层做求和处理。对响应条数在dwd层做求和处理。对平均响应时长在dwd层根据每天的总条数做AVG平均处理

生成临时表q2

时间维度client_id客户IDenter_time进课时间leave_time离课时间study_time上课时长

统计维度study_time观看时长practise随堂练习practise_pn随堂练习人数avg_score平均得分

课程维度firstproduct一级项目course_number课号lesson课次module模块

客户维度client_id客户IDstu_name学员姓名

dwd层字段client_id客户IDstu_name学员姓名class_phone上课手机号process_status状态agency机构name_id人员IDprincipal负责人processor处理人create_time创建时间plan_time计划时间process_time处理时间work_content工单内容process_result处理结果work_category工单分类creator创建人creator_institution创建机构product_name商品名称create_date创建日期plan_date计划日期process_date处理日期

报表

客户维度visitor_sign访客标识phone手机号phone2电话2wechat微信wechat2微信2qqQQevaluat评价empty_number空号号码number_status号码状态

dws_loan

教师维度course课程course_number课号module模块lesson课次teacher授课老师platform直播平台live_time直播时间

old_mantis_export.account_acting

插入表

表更新选择truncate table全量更新

stu_name学生姓名class_phone上课手机号client_id客户idfirst_class_item一级项目second_level_project二级项目course录播课程course_number录播课号module模块lesson课次platform录播平台video_time视频时长enter_time进课时间leave_time离课时间study_time观看时长practise随堂练习practise_pn随堂练习人数avg_score平均得分暂定，可能取消position职位name姓名family家族college学院department部门note备注

临时表c6

dwd层字段stu_name学生姓名class_phone上课手机号client_id客户idfirst_class_item一级项目lesson课次enter_time进课时间leave_time离课时间act_study上课时长

判断是否支付全款

绑定公众号

临时表p

未明确拒绝即返回意向判断

坐席维度device_IMEI设备IMEIdevice_remark设备备注device_subordinate设备所属device_group设备分组opposite_number对方号码call_type呼叫类型download_addr下载地址

根据headteacher班主任字段关联people姓名映射表获得正确班主任名字

用name_id匹配上payment表后，清洗Payment表中的五级架构和销售人员的正确姓名

是否观看直播

模式环节结束

dwd_dim_people 销售架构表ny年月name_id工号model一级部门division二级部门legion三级部门dept四级部门group_name五级部门name名字

时间维度live_time直播时间

name_idchild_status子订单状态crad_province名片省份first_class_item一级项目second_level_project二级项目class_type班型apply_class_type报名班型apply_time报名时间up_type提升类型should_pay_money应缴金额pay_reality已缴金额afdis_money折算后金额pay_type支付类型marketing_project营销项目intention意向度contract_status签约状态contract_date签约完成时间ord_create_time订单创建时间child_number子订单编号creator_institution创建人机构

dwd层主要字段first_class_item一级项目second_level_project二级项目course课程course_number课号module模块lesson课次name_id人员IDteacher授课老师live_time直播时间f_time开始时间l_time结束时间

用于记录所有通话记录，以设备IMEI号和所属人来统计以日为时间单位下所有的通话记录，但仅记录通话，不记录备注，通话目标的信息

咨询师微信沟通

dwd层字段child_status状态child_number子订单编号apply_time报名时间stu_name学员姓名gender性别client_id客户IDcard_number证件号class_phone手机号phone1电话1phone2电话2first_class_item一级项目second_level_project二级项目product商品school校区should_pay_money应缴金额pay_reality已缴金额name_id人员IDheadteacher班主任is_public绑定公众号

dwd_tl_inclass 螳螂随堂练习

对course来源字段进行计算，得到来源和新字段来源ID

dws_call

child_status状态child_number子订单编号apply_time报名时间stu_name学员姓名gender性别client_id客户IDcard_number证件号class_phone手机号phone1电话1phone2电话2first_class_item一级项目second_level_project二级项目product商品school校区should_pay_money应缴金额pay_reality已缴金额name_id人员IDheadteacher班主任is_public绑定公众号

获取当月数据

评价维度mark_time评分时间score评分content内容method方法effect效果message留言

课程维度client_id客户IDfirst_class_item一级项目second_level_project二级项目course课程course_number课号module模块lesson课次platform直播平台teacher授课老师attend_way出勤方式enter_time进课时间leave_time离课时间study_time上课时长

ods_tl_order 螳螂订单表

判断是否取消退费

统计维度practise随堂练习practise_pn随堂练习人数avg_score平均得分

dwd_tl_study_record 螳螂上课记录表

不需要关了其他表，对ods表中start_time答题开始时间字段做MID截取后插入dwd层表

开学典礼

pay_annal_ID=pay_record_number

判断是否有效

取消投诉

ods_tl_allot_timeods_tl_create_timeods_tl_first_time

判断是否可以直接转换

dws建表dws_录播表dws_直播表dws_tl_student_order 订单管理dws_tl_payment 支付确认表dws_tl_call 话务查询 dws_tl_work  工单表dws_tl_student 学员管理需要新增三张表订单修改表学员映射表架构修改设立学员综合信息表课程总和信息表

退款维度client_id客户IDstu_id学员Idprincipal负责人carbon_copy抄送人processor处理人should_pay_money应缴金额pay_reality已缴金额refund退费金额

account_plan日期推广计划展现量点击量消费计划名插码返点财务消费账户渠道渠道类型城市负责人项目

判断是否取消投诉

临时表b

dwd_ak_client_add云dwd_ak_client_add

临时表c4

dws_sale_info

dwd_paytime

临时表a6

dm.A

生成字段：name_idclient_id客户IDcard_type名片类型phone联系电话intention意向度feedback_status反馈状态reason未接通原因l_dial_date最后一次拨打日期name姓名wechat_add_status微信添加状态qqQQprovince省份area地域project项目creator_institution创建人机构creator创建人create_time创建时间source_way来源方式url着陆页fa_time首次分配时间fa_attri_people首次分配人fa_attri_institution首次分配归属机构fc_time首次咨询时间fc_teacher首次咨询师fc_teacher_institution首次咨询机构source来源

指标：dialog_time对话时间visitor_message_num访客消息数service_message_num客服消息数

项目维度agency机构principal负责人processor处理人create_time创建时间plan_time计划时间process_time处理时间work_content工单内容process_result处理结果aging_create时效（按创建时间）aging_plan时效(按计划时间)work_category工单分类creator创建人creator_institution创建机构product_name商品名称reason_category原因大类reason_inclass原因中类（新）

生成临时表q8

分配班主任

dwd_create_time 名片创建表date日期

client_id客户IDstu_name学员姓名class_phone上课手机号process_status状态agency机构principal负责人processor处理人creator创建人creator_institution创建机构work_content工单内容process_result处理结果aging_create时效（按创建时间）aging_plan时效(按计划时间)work_category工单分类product_name商品名称stu_id学员IDreason_category原因大类reason_inclass原因中类（新）待定model一级部门division二级部门legion三级部门dept四级部门group_name五级部门

seat_name=attri_people

项目维度legion事业部call_department呼叫部call_group呼叫组

拉黑名片

未上课

模式

课程回访

主键字段user_id用户ID

关联条件teacher授课老师 = people被修改名

生成字段client_id客户IDphone联系方式name姓名intention意向度qqQQwechat微信province省份area地域project项目source来源silent_time_l最后一次静默时间silent_num静默次数silent_status静默状态operator操作人operator_agency操作人机构attribution_group归属机构名称card_create_date名片创建时间distribution_logo分配标识label标签remark备注

carte_amendsale_nameurlods_tl_sale_detail

时间维度mark_time评分时间

dwd_tl_orderapplicant_order 报名单数

时间维度call_date通话日期start_time开始时间ringing_time振铃时长call_duration通话时长

生成临时表q11

ods表

考试维度start_time答题开始时间q_time做题时长is_hand是否交卷t_exercise题目总数true_num做对false_num做错score得分is_pass是否合格

临时表c3

dwd层主要字段client_id客户IDstu_name学员姓名class_phone上课手机号process_status状态agency机构name_id人员IDprincipal负责人processor处理人create_time创建时间plan_time计划时间process_time处理时间work_content工单内容process_result处理结果work_category工单分类creator创建人creator_institution创建机构product_name商品名称create_date创建日期plan_date计划日期process_date处理日期

main_number主订单编号child_status子订单状态child_number子订单编号first_class_item一级项目second_level_project二级项目apply_class_type报名班型apply_time报名时间receiver接待人should_pay_money应缴金额pay_reality已缴金额afdis_money折算后金额pay_way支付方式remark备注marketing_project营销项目scene_card_belong现场名片归属scene_ord_group现场订单机构scene_ord_belong现场订单归属check_status审核状态partner_settle_charge合作方结算费用our_cost我司结算费用refund_type退费类型refund_reality已退费金额refund_underway退费中金额deduction_reality已扣费金额deduction_underway扣费中金额stu_id学员IDpre_pay_date预补缴日期expire_date到期日期l_allocation_type最后一次分配类型apply_area报名地域intention意向度headteacher班主任fa_time首次分配时间contract_status签约状态void_date作废时间confirm_money确认金额contract_date签约完成时间exam_period考期ord_create_time订单创建时间

sp_dws_project_salecostsale_info

生成临时表q9

主键字段list序号 /stu_name评分者

dwd生成字段stu_name学员姓名class_phone上课手机号client_id客户IDfirst_class_item一级项目second_level_project二级项目course课程course_number课号module模块lesson课次name_id人员IDteacher授课老师attend_way出勤方式enter_time进课时间leave_time离课时间phone手机号act_study上课时长

关联people姓名映射表获取正确的坐席姓名

根据条件生成dwd层字段

完成直播课

机会

financial_confirmation财务确认pay_record_status支付记录状态pay_type支付类型main_number主订单编号product_information商品信息ord_t_ampayable订单总应缴金额ord_t_ampaid订单总已缴金额pay_record_number支付记录编号pay_t_money支付总金额afdis_capitalflow折算后流水deposit使用定金pay_money支付金额(不含定金)service_charge手续费interest利息arrive_account_money到账金额spac_service_charge分账手续费spac_recevier_money分账接收方金额spac_surplus_money分账剩余金额pay_detail本次支付明细charge_type费用类型pay_way支付方式staging分期数detail_pay_money明细支付金额detail_service_charge明细手续费detail_interest明细利息dearac_money明细到账金额despac_service_charge明细分账手续费despac_recevier_money明细分账接收方金额despac_surplus_money明细分账剩余金额pay_channel付款渠道pay_time支付时间pay_remark支付备注confirmor确认人confirm_time确认时间receipt收据号financial_remark财务备注receipt_number收据编号receipt_status收据状态smallreceipt_number小票单号receipt_failure收据失败原因partner_settle_charge合作方结算费用our_cost我司结算费用pay_channel_1付款渠道1overrule_reason驳回原因void_cause作废原因product_classification商品分类

ods_tl_call 螳螂话务查询

ods_ht_barrage 欢拓弹幕

咨询师/坐席维度ID_Card身份证号attri_people归属人attri_institution归属机构

学生维度stu_name学员client_id客户IDclass_phone上课手机号subject科目score得分

device_IMEI = device_IMEI

dwd层生成字段course_id来源IDcourse来源stu_name评分者mark_time评分时间score评分content内容method方法effect效果

ods层生成字段customer_status状态stu_name姓名class_phone上课手机号client_id客户IDcustomer_category分类creator创建人customer_time客服单时间urgency_degree紧急程度customer_content客服单内容principal_institution负责人机构principal负责人carbon_copy抄送人process_time处理时间processor处理人process_result处理结果first_class_item一级项目second_level_project二级项目product_name商品名称stu_id学员Idapply_time报名时间should_pay_money应缴金额pay_reality已缴金额refund退费金额apply_information报名信息attri_people咨询师attri_institution咨询师所属机构school校区

date日期model一级部门division二级部门legion三级部门dept四级部门group_name五级部门director负责人account账户source渠道province城市source_type渠道类型item项目acting代理商cost单日消费revenue营收accounting_revenue账单分期营收cash_flow流水cur_week_revenue当周营收last_week_revenue上周营收big_term_revenue大跨期营收online__revenue在线营收message_revenue留言营收other_revenue其他营收display_num展现数hits_num点击数consume_num消费数finance_consume财务消费applicant_person报名人数cur_weeek_applicant_person当周报名人数last_weeek_applicant_person上周报名人数cross_term_applicant_person跨期报名人数applicant_order报名单数cur_week_applicant_order当周报名单数last_week_applicant_order上周报名单数cross_term_applicant_order跨期报名单数online_order在线报名单数message_order留言报名单数other_order其他报名单数card_num名片数online_card_num在线名片数message_card_num留言名片数other_card_num其他名片数incoming_line进线conversation_eff有效对话phone_card_num手机名片数wechat_card_num微信名片数qq_card_numQQ名片数visitor_message_num访客消息数service_message_num客服消息数get_num获取数second_get_num二次获取数chat_duration聊天时长sale_change销售机会phone_change电销机会phone_online_change电销在线机会phone_message_change电销留言机会phone_other_change电销其他机会web_change网销机会web_online_change网销在线机会web_message_change网销留言机会web_other_change网销其他机会

云dwd_tl_card云dws_tl_card

分配投退老师

判断是否进入流失

判断属性是否为在线数据

产生营收

判断是否绑定

客户维度call_phone呼叫号码client_id客户Idclient_name客户名称

判断24小时是否退费

判断课件是否违规

base_nameposition职位name姓名family家族college学院department部门

主要字段first_class_item一级项目second_level_project二级项目course课程course_number课号module模块lesson课次teacher授课老师live_time直播时间（拆分后得到下面两个时间）f_time开始时间l_time结束时间

退费

渠道维度first_class_item一级项目second_level_project二级项目customer_source客户来源url着陆页province省份city城市

判断是否分期支付

名片环节结束

主要字段stu_name学生姓名class_phone上课手机号client_id客户idfirst_class_item一级项目lesson课次enter_time进课时间leave_time离课时间study_time观看时长

客户维度call_phone手机号码

咨询师/坐席维度device_IMEI设备IDdevice_subordinate设备所属

ods层生成字段client_id客户IDstu_name学员姓名class_phone上课手机号process_status状态agency机构principal负责人processor处理人create_time创建时间plan_time计划时间process_time处理时间work_content工单内容process_result处理结果aging_create时效（按创建时间）aging_plan时效(按计划时间)work_category工单分类creator创建人creator_institution创建机构product_name商品名称stu_id学员IDreason_category原因大类reason_inclass原因中类（新）

分配的

员工维度name_id人员IDseat_name坐席姓名model一级部门division二级部门legion三级部门dept四级部门group_name五级部门attri_people人名

学生维度stu_name学生姓名class_phone上课手机号client_id客户id

dwd层生成字段stu_name学员class_phone上课手机号client_id客户IDfirst_class_item一级项目course_number名称module模块lesson课次start_time答题开始时间true_num做对false_num做错

输出字段：name_iddate 日期device_IMEI 设备IDdevice_subordinate 设备所属callout_type 呼出类型start_time 开始时间end_time 结束时间time_span 通话时长

生成临时表T1

上课时长在dwd层就处理完，班主任架构在dws的存储过程中关联

员工维度customer_time客服单时间urgency_degree紧急程度customer_content客服单内容principal_institution负责人机构principal负责人carbon_copy抄送人process_time处理时间processor处理人process_result处理结果apply_information报名信息attri_people咨询师attri_institution咨询师所属机构school校区

记录名片属于自营名片

在线客服接待

学生维度stu_name学员class_phone上课手机号client_id客户IDmodule模块lesson课次start_time答题开始时间score得分

对customer_content字段中的值进行筛选，剔除测试，重复和错误数据。对process_result字段进行判断，剔除字段中的NULL值对customer_content字段进行判断来更新数据的分类是24小时还是非24小时

ods层生成字段stu_name学员client_id客户IDclass_phone上课手机号first_class_item一级项目second_level_project二级项目q_pool_type题库类型q_pool_name题库名称subject科目q_type做题类型test_name试卷名称start_time答题开始时间q_time做题时长is_hand是否交卷t_exercise题目总数true_num正确数目false_num错误数目score得分agent代理商

ods层生成字段course来源list序号stu_name评分者mark_time评分时间score评分content内容method方法effect效果message留言

判断是否投诉

61个字段全量获取

对表中项目人员姓名做清洗

自营渠道添加

二次沟通任拒绝

展示表中name字段，对ods表中的headteacher字段进行清洗，获取正确的名字.

dwd层字段stu_name学员姓名class_phone上课手机号client_id客户IDfirst_class_item一级项目second_level_project二级项目course课程course_number课号module模块lesson课次name_id人员IDteacher授课老师attend_way出勤方式enter_time进课时间leave_time离课时间phone手机号act_study上课时长

客户维度client_id客户IDphone手机号class_phone上课手机号name姓名id_number证件号wechat微信qqQQwechat2微信2stu_id学员ID

date   日期     name_id  员工id     attri_people       咨询师     first_class_item   一级项目     class_type   班型类别 major  专业     up_type  类型

学生维度user_id用户IDstu_name学生信息

pay_record_status支付记录状态pay_type支付类型first_class_item一级项目second_level_project二级项目product_classification商品分类product_information商品信息ord_t_ampayable订单总应缴金额ord_t_ampaid订单总已缴金额pay_record_number支付记录编号pay_t_money支付总金额afdis_capitalflow折算后流水deposit使用定金pay_money支付金额(不含定金)service_charge手续费interest利息arrive_account_money到账金额pay_detail支付明细pay_way支付方式staging分期数detail_pay_money明细支付金额detail_service_charge明细手续费detail_interest明细利息dearac_money明细到账金额despac_service_charge明细分账手续费despac_recevier_money明细分账接收方金额despac_surplus_money明细分账剩余金额pay_time支付时间pay_remark支付备注void_cause作废原因voider作废人charge_type费用类型

需要注意首咨表中会存在首次分配人和创建机构两个字段用于辅助确认表中名片和咨询师准确性

展示所有字段，对表中stu_name字段和headteacher字段进行判断，剔除掉所有测试数据和无效数据

dws_tl_student_order 订单管理表

客户维度call_phone通话号码client_id客户IDclient_name客户姓名seat_id坐席分机seat_name坐席姓名call_date通话日期start_time开始时间

ods_tl_allot_time 首次分配表ods_tl_firost_time首次咨询表carte_amend名片修改表sale_name 姓名映射表

判断是否可以被转换

dwd_ht_grade 欢拓评分

dwd_tl_create_timeclient_id客户ID

用学员映射表映射，项目映射表映射。表中存在多个时间字段和负责人，确认人员名单时可以使用映射表清洗

pay_time支付时间pay_record_status支付记录状态pay_type支付类型first_class_item一级项目second_level_project二级项目product_information商品信息up_type提升类型ord_t_ampayable订单总应缴金额ord_t_ampaid订单总已缴金额pay_record_number支付记录编号pay_t_money支付总金额afdis_capitalflow折算后流水deposit使用定金pay_money支付金额(不含定金)service_charge手续费interest利息arrive_account_money到账金额pay_detail支付明细pay_remark支付备注receiver接待人void_cause作废原因

ods_ak_client_add

坐席维度seat_id坐席分机seat_name坐席姓名call_type呼出类型dial_type拨打类型number_phone呼叫号码call_status通话状态call_date通话日期start_time开始时间ringing_time振铃时长call_duration通话时长recording录音legion事业部call_department呼叫部call_group呼叫组

dwd_ht_barrage 欢拓弹幕

建立循环，利用循环来处理时间并计算弹幕的间隔时长

流量渠道推广

生成临时表q10

ods生成字段stu_name学员姓名class_phone上课手机号client_id客户IDfirst_class_item一级项目second_level_project二级项目course课程course_number课号module模块lesson课次platform直播平台teacher授课老师attend_way出勤方式enter_time进课时间leave_time离课时间study_time上课时长

渠道维度visitor_message_num访客消息数service_message_num客服消息数dialog_date对话日期dialog_time对话时间stop_duration发起前停留时长promotion_project推广项目

统计维度call_person_time会话人次reception_person_time接待人次return_visit_person_time回访人次kefu_response_num客服回复条数response_num响应条数avg_response_second平均响应时长（秒）

并合成临时表q

模式环节开始

名片维度card_create_time名片创建时间card_creator名片创建人fa_time首次分配时间fc_date首咨日期alloc_date分配日期

课程维度course课程course_number课号module模块lesson课次platform直播平台teacher授课老师attend_way出勤方式

上课类型

客户维度stu_name姓名class_phone上课手机号client_id客户IDstu_id学员Idprincipal负责人

录播课

班主任回访

urlurlattributor 负责人

结束

并和临时表q，生成临时表t

非事业部流量池

ods_ht_grade 欢拓评分

dwd层字段first_class_item一级项目second_level_project二级项目course课程course_number课号module模块lesson课次name_id人员IDteacher授课老师live_time直播时间f_time开始时间l_time结束时间

对payment表中的staging，pay_way做CASE WHEN条件判断，判断后根据结果聚合detail_pay_money字段获得值。根据对staging，pay_way的不同判断，得到no_card_cash_flow等9个字段。

临时表t2

名片环节开始

pay_record_number=pay_annal_ID

主要字段course来源list序号user_id用户IDstu_name学生信息chat_time时间chat_content聊天信息

名片维度card_create_time名片创建时间card_area名片地域card_creator名片创建人fc_date首咨日期alloc_date分配日期fc_people首次咨询人

dwd层字段call_phone通话号码client_id客户IDclient_name客户姓名seat_id坐席分机seat_name坐席姓名call_type呼出类型dial_type拨打类型number_phone呼叫号码call_status通话状态call_date通话日期start_time开始时间ringing_time振铃时长call_duration通话时长recording录音legion事业部call_department呼叫部call_group呼叫组

临时表c7

是否支付尾款

截杀

临时表m

并和表T

关联条件people = device_subordinate

正式上课

dwd_ak_client_add

dwd_tl_customer_order 螳螂客服单表

根据主题划分是以展示首次创建维度为主

课程维度client_id客户idcourse录播课程course_number录播课号course录播课程course_number录播课号practise随堂练习practise_pn随堂练习人数avg_score平均得分

ods_tl_firost_time首次咨询表name_idclient_id客户IDcard_type名片类型phone联系电话intention意向度feedback_status反馈状态reason未接通原因l_dial_date最后一次拨打日期name姓名wechat_add_status微信添加状态qqQQprovince省份area地域project项目creator_institution创建人机构creator创建人create_time创建时间source_way来源方式url着陆页fa_time首次分配时间fa_attri_people首次分配人fa_attri_institution首次分配归属机构fc_time首次咨询时间fc_teacher首次咨询师fc_teacher_institution首次咨询机构source来源

生成字段：opposite_number客户号码client_id 客户iddevice_IMEI设备IMEIdevice_remark设备备注device_subordinate设备所属device_group设备分组date 通话日期callout_type呼出类型start_time开始时间end_time结束时间time_span时间长度download_addr下载地址

临时表c2

主键字段stu_name学员姓名

生成临时表q1

dws_class_type

ods层生成字段child_status状态child_number子订单编号apply_time报名时间stu_name学员姓名gender性别client_id客户IDcard_number证件号class_phone手机号phone1电话1phone2电话2first_class_item一级项目second_level_project二级项目product商品school校区exam_period考期college学院名称price售价should_pay_money应缴金额pay_reality已缴金额is_owe欠费标志headteacher班主任class_status开课状态stu_label学员标签is_public绑定公众号pre_education_major前置学历专业

ods_tl_order 与自身相连接求出客户的报考类型

dwd_cost 消费表1client_id 客户IDreal_cost 实际消费bill_cost 平均消费

DWD生成字段course_id来源IDcourse来源stu_name学生信息chat_time时间chat_content聊天信息interval_time弹幕间隔时长

dwd层生成字段date日期device_IMEI设备IMEIname_id人员IDdevice_subordinate设备所属start_time开始时间end_time结束时间call_duration通话时长call_type设备分组opposite_number对方号码

时间维度enter_time进课时间leave_time离课时间study_time上课时长live_time直播时间

课程维度firstproduct一级项目course_number课号lesson课次module模块teacher授课老师live_date直播时间f_time开始时间l_time结束时间

预留映射修改，适当将表中辅助字段拓宽

后端

建立循环，对ods_ht_barrage中的chat_time时间做处理，得到每一条弹幕的准确出现时间

学员维度stu_name学员姓名phone1电话1phone2电话2class_phone上课手机号

是否预约单

URL着陆页sale_name姓名映射表

产生投诉工单

项目维度agency机构principal负责人processor处理人work_category工单分类creator创建人creator_institution创建机构product_name商品名称stu_id学员ID

选择分期工具

生成字段：promotion_account推广账户promote_plan推广计划Promote_unit推广单元match_way匹配方式key_word关键词first_click首次点击元素dialog_status对话状态custom_para自定参数empty_number空号号码number_status号码状态alloc_attri分配归属人add_to是否添加add_date添加日期obtain_way获客方式if_success是否匹配成功qrcode_exposure_date二维码曝光日期wechatid_copy_num微信号复制次数stop_duration发起前停留时长probe_id探头Idprobe_name探头名称label_name标签名称origin_page来路页client_id客户IDconversation_id会话IDrequest_method请求方式l_search_keyword上一次搜索词bcp_account_IDbcp推广账户IDreceive_type接待类型hosting_method托管方式escrow_account托管客服账号Robot_account机器人账号is_preread是否预读satisfaction满意度welcome_send_num欢迎语发送次数

咨询师首次咨询

tl_allot_time取数逻辑从ods中映射carte_amend辅助表清洗出表中正确的首分机构，首分人，首分时间，然后连接sale_name映射清洗正确的人名，最后连接url着陆页清洗出正确的省份字段，将结果中的测试数据和手机号字段空值的数据剔除，得到的结果既是dwd_tl_allot_time表

date日期callout_type呼出类型start_time开始时间end_time结束时间time_span通话时长

dwd层生成字段stu_name学员姓名phone1电话1phone2电话2class_phone上课手机号firstproduct一级项目course_number课号lesson课次module模块name_id人员IDteacher授课老师live_date直播时间f_time开始时间l_time结束时间

dwd层字段stu_name学员class_phone上课手机号client_id客户IDfirst_class_item一级项目course_number名称module模块lesson课次start_time答题开始时间true_num做对false_num做错

临时表a5

二次电联任拒绝进入流失

关联条件people = headteacher

时间维度create_time创建时间plan_time计划时间process_time处理时间aging_create时效（按创建时间）aging_plan时效(按计划时间)

peoplename

临时表xs

临时表c8

时间维度video_time视频时长enter_time进课时间leave_time离课时间study_time观看时长

留存投诉