1 第一步先新建一个渠道hmsr
2 新建渠道资源映射管理
3 在需要输出的报表里,添加新增的hmsr 渠道名称
4 附上需要明细SQL
--odps sql --********************************************************************-- --description:保姆DSP朋友圈渠道数据报表 --author:shanwei --create time:2018-12-19 11:12:00 --modifier:单伟 --modify reason:改为全量更新数据(2019-01-01以后)，删掉分区 --modify time:2019-06-20 15:20:00 --********************************************************************-- insert overwrite table a_bm_dsp_channel_statistical select dates ,hmsr ,name_2 ,sum(pv_num) pv_num --PV(访问量) ,sum(uv_num) uv_num --UV(用户量) ,sum(clue_num) clue_num --线索数 ,sum(eff_clue_num) eff_clue_num --有效线索数 ,sum(sign_num) sign_num --签单数 ,sum(audit_order) as audit_order ,sum(a_audit_order) as a_audit_order ,sum(b_audit_order) as b_audit_order from ( --计算 PV、UV select to_date(a.dt,'yyyymmdd') dates ,a.hmsr ,b.name_2 ,count(a.cookie_id) pv_num --PV(访问量) ,count(distinct a.cookie_id) uv_num --UV(用户量) ,0 clue_num --线索数 ,0 eff_clue_num --有效线索数 ,0 sign_num --签单数 ,0 as audit_order ,0 as a_audit_order ,0 as b_audit_order from ( select * from f_jz_traffic_base where cate_id like '2%' --取保姆 and dt >= '20190101' and event_type='pageview' ) a left join d_source b on a.source_id = b.id where b.name_2 in ('DSP_微信朋友圈','DSP_快手','DSP_知乎','DSP_闪投','DSP_网易新闻','DSP_搜狐新闻', 'DSP_一点资讯','DSP_粉丝通','DSP_UC头条') group by a.dt,a.hmsr,b.name_2 union all --计算线索 select to_date(substr(a.create_time,1,10),'yyyy-mm-dd') dates ,a.max_hmsr as hmsr ,b.name_2 ,0 pv_num --PV(访问量) ,0 uv_num --UV(用户量) ,count(distinct a.id) as clue_num --线索数 ,0 eff_clue_num --有效线索数 ,0 sign_num --签单数 ,0 as audit_order ,0 as a_audit_order ,0 as b_audit_order from ( select * from f_jz_clue_main where category_id = 212 and substr(create_time,1,10) >= '2019-01-01' ) a left join d_source b on a.original_source_id = b.id where b.name_2 in ('DSP_微信朋友圈','DSP_快手','DSP_知乎','DSP_闪投','DSP_网易新闻','DSP_搜狐新闻', 'DSP_一点资讯','DSP_粉丝通','DSP_UC头条') group by substr(a.create_time,1,10),a.max_hmsr,b.name_2 union all --计算有效线索 select to_date(substr(a.chance_create_time,1,10),'yyyy-mm-dd') dates ,a.max_hmsr as hmsr ,b.name_2 ,0 pv_num --PV(访问量) ,0 uv_num --UV(用户量) ,0 clue_num --线索数 ,count(distinct a.id) eff_clue_num --有效线索数 ,0 sign_num --签单数 ,0 as audit_order ,0 as a_audit_order ,0 as b_audit_order from ( select * from f_jz_clue_main where category_id = 212 and substr(chance_create_time,1,10) >= '2019-01-01' ) a left join d_source b on a.original_source_id = b.id where b.name_2 in ('DSP_微信朋友圈','DSP_快手','DSP_知乎','DSP_闪投','DSP_网易新闻','DSP_搜狐新闻', 'DSP_一点资讯','DSP_粉丝通','DSP_UC头条') group by substr(a.chance_create_time,1,10),a.max_hmsr,b.name_2 union all --计算签单量 select to_date(substr(a.order_date,1,10),'yyyy-mm-dd') dates ,clue.max_hmsr as hmsr ,b.name_2 ,0 pv_num --PV(访问量) ,0 uv_num --UV(用户量) ,0 clue_num --线索数 ,0 eff_clue_num --有效线索数 ,count(distinct a.id) sign_num --签单数 ,0 as audit_order ,0 as a_audit_order ,0 as b_audit_order from ( select * from f_jz_order_main where category_id = 212 and substr(order_date,1,10) >= '2019-01-01' ) a left join f_jz_clue_main clue on a.clue_id = clue.id left join d_source b on a.original_source_id = b.id where b.name_2 in ('DSP_微信朋友圈','DSP_快手','DSP_知乎','DSP_闪投','DSP_网易新闻','DSP_搜狐新闻', 'DSP_一点资讯','DSP_粉丝通','DSP_UC头条') group by substr(a.order_date,1,10),clue.max_hmsr,b.name_2 union all --计算审核单量 select to_date(substr(work_date,1,10),'yyyy-mm-dd') dates ,null as hmsr ,last_name_2 name_2 ,0 pv_num --PV(访问量) ,0 uv_num --UV(用户量) ,0 clue_num --线索数 ,0 eff_clue_num --有效线索数 ,0 as sign_num ,sum(aduit) as audit_order ,sum(label_type_a_ne_aduit) as a_audit_order ,sum(label_type_b_ne_aduit) as b_audit_order from m_bm_adviser_quota_all where dt = '${bdp.system.bizdate}' group by work_date ,last_name_2 ) t group by dates,hmsr,name_2