培训业务流程图

dws_follow_up_customer_count

查找最近一个月的可续约数据

insurance_customer_follow_up

dwd_otr_insurance_insurance_customer_follow_up

dws_otr_insurance_insurance_renewal_start_date

1. 查询当天otr_insurance_insurance_customer_follow_up表中follow_up_status = 'DEFEAT'并且follow_up_type = 'SYSTEM_FOLLOW_UP'的数据2. copy数据，如果数据在1中并且commercial_insurance_status = 'DEFEATED'或者statutory_automobile_liability_insurance_status = 'DEFEATED'，则把这两个字段设置为空

dws_otr_insurance_follow_up_three_day_indicator_point

dws_otr_insurance_is_renewable_customer_this_month

dwd-to-dws

source-to-ods

insurance_city_renewal_period_mapping

dws_otr_insurance_follow_up_tomorrow_indicator_point

数据流程图

insurance_customer_base

dws_defeat_count_this_month

待调查

月度跟进报表

ods-to-dwd

dwd_otr_insurance_insurance_customer_base

dws_otr_insurance_deal_this_month

dws_otr_insurance_insurance_renewal_start_date_monthly

dws_is_renewable_before_today

dws_renewable_customer_next_month

dws-to-dm

过滤dws_otr_insurance_insurance_renewal_start_date表中商业险，交强险的开始续约日期是下一个月的数据

1. 用当月续保表数据（is_renewal_this_month为新建字段，设置为1）和下月数据（is_renewal_this_month为新建字段，设置为0）合并为基础数据

过滤条件：1. 商业险可续保是当前月2. 或者交强险可续保是当前月3. 或者商业，交强险的开始日小于当天并且可续保日在可续保区间并且跟进状态不是为：战败或者成功4. 根据签约数据再过滤一下客户信息

dws_otr_insurance_follow_up_first_day_indicator_point

关键表

1. 查询dws_otr_insurance_is_renewable_customer_this_month表，查可续保月份为当月的数据2. 查跟进月份为当月，并且跟进类型为人工跟进3. 根据客户id聚合

copy

dwd_otr_insurance_insurance_vehicle_underwriting

1. copy主表：dwd_otr_insurance_insurance_vehicle_underwriting2. 根据主表的保险过期日和dw_insurance_dim_dealer_renewal_period_mapping表中的可续保时间计算，交强和商业的可续保日

otr_insurance_insurance_city_renewal_period_mapping

insurance_vehicle_underwriting

dm_follow_up_monthly_report