基于自动节点树的公司广告投放渠道数据异常原因下探分析

1、业务

1.1 业务背景

• 日报、周报、月报等常规性报告是各个公司的基础数据支持形式。在日常报告中，经常会出现很多异常波动的指标，需要分析师找到异常波动的影响因素。但在寻找主要因素时由于需要下探的层级较多，实施起来会非常费时费力。

1.2 前置知识

1.2.1 广告投放渠道划分

• 一级渠道包括SEM、AD、CPS、Social、导航等；
• 二级渠道以SEM为基准包括百度、谷歌、360等；
• 三级渠道以百度为基准包括关键字、网盟等；
• 四级渠道以关键字为基准包括不同的广告计划；
• 五级渠道以广告计划为基准可以细分到不同的广告组；
• 六级渠道以广告组为基础可以细分到不同的关键字。

1.3 解决方案

• 统计全站在一定周期内、特定指标下的数据环比变化量和环比变化率。
• 指定要分析日期并获得该日期及其前1天的数据。
• 以全站数据为基准，下探第一层级维度并对指定日期和其前1天的数据做分类汇总。
• 计算第一层级维度下分类汇总后的两天数据的差值并得到环比变化量和环比变化率。
• 对第一层级维度下的变化量排序，并分别获得环比变化量最大和最小情况下的维度名称、变化量和变化率。
• 计算下一层级变化量与上一层级变化量的比值，变化量最大值和最小值的比例将被定义为正向贡献率和负向贡献率。
• 循环上述步骤，直至所有层级都计算完成。
• 使用树形图展示所有层级下的变化量最大和最小的维度信息包括维度名称、环比变化量、环比变化率、贡献率等信息。

2、数据&开发

2.1 数据概览

字段	描述
date	日期
source	流量一级来源，业务部门定义
site	流量二级来源，业务部门定义
channel	流量三级来源，业务部门定义
media	流量四级来源，业务部门定义
visit	访问量

2.2 计算整体波动量

2.2.1 先将每天的数据与其前1天的数据做环比变化统计

select t2.date,t2.visit_cnt,(t2.visit_cnt-t1.visit_cnt) AS change,(t2.visit_cnt-t1.visit_cnt)/t2.visit_cnt AS change_rate 
from tb_groupby_date_ad t1 JOIN tb_groupby_date_ad t2 ON t1.date = date_sub(t2.date,1) 
order by t1.date

2.2.2 渠道贡献率、环比变化统计

select source,sum(visit) as visit_cnt from tb_ad 
where date = '2017-06-07' 
group by source 
order by visit_cnt desc

2.2.3 下一级渠道贡献率、环比变化统计

select a.site,sum(visit) as visit_cnt from tb_ad a join tb_groupby_source_desc_ad b on a.source = b.source
where date = '2017-06-07'
group by a.site 
order by visit_cnt desc

3、优势

• 自动按照指定的日期找到影响的最大因素，并可层层分解找到对应层级的上一层和下一层关联影响因素以及对应的贡献量。
• 在节点树中除了关注正向影响，还增加了负向影响因素的信息，可以帮助分析师找到被整体波动埋藏的负向规律。