EXCEL竞价自动分析，Power Pivot数据建模分析

万物复苏的季节，各个行业也都加大广告投放，苦了我们广大sem优化人员。随着广告投放力度的增强，竞价人员面临的问题就是推广渠道多、数据量大。每天做数据分析工作量巨大，延长分析周期的话根本没办法下手调整。excel的强大需要我们不断的学习，其实EXCEL可以自动完成竞价分析，Power Pivot数据建模然后点击刷新，必要的数据全部出来。

1、前期准备

A、数据分析就需要将各个阶段的数据关联起来，客服系统、crm系统、竞价后台的数据都是相对独立的，需要一一对应才能充分发挥数据分析的价值。所以url标记追踪必不可少。

url追踪标记方法

竞价推广如何设置追踪URL？百度推广助手有自带url追踪标记功能，那么360 搜狗推广怎么添加追踪URL标记？ […]

为了数据的细化与完整，url标记中的参数务必包含计划、单元、关键词，百度、360、搜狗推广url标记追踪的参数最好保持一致；将数据延展到后续客户跟进情况就需要与crm关联，可以使用电话或者ip，鉴于部分客服平台不直接提供访客联系方式的数据下载，crm记录电话的同时加上ip会节省更多时间。

B、数据搜集 常用的分析需要搜集一下表格：搜索词报告、时段报告、地域报告、咨询报告、客户跟进报告 （分日）。

C、分析工具 excel、power query、power pivot，在2016版本中power query不用单独安装，在数据中直接使用，更低的版本就需要单独安装，下载地址：https://www.microsoft.com/en-us/download/details.aspx?id=39379。power pivot直接在com加载项加载一下就好。

2、整理数据

A、数据合并 需要分析的数据是不断增加的，所以需要将不同时段的数据合并，使用power query可轻松一次性解决。常用方法 删除空行、删除错误行、筛选等。

B、数据格式 用到计算和日期，对于这部分列要使用正确的数据类型。

C、数据提取 提取咨询的时段（小时）、提取咨询页面的url追踪参数（Uri.Parts("")）。

D、添加维度表 一般需要账户结构、日期、ip等维度表来关联。记录一下常用函数

利用PowerQuery自定义函数法创建日历表：

(optional 请输入开始年份 as number,optional 请输入结束年份 as number)=>let
    x = 请输入开始年份,
    y = if 请输入结束年份 = null then 请输入开始年份 else 请输入结束年份,
    begin_date = if x = null then #date(Date.Year(DateTime.LocalNow()),1,1) else #date(x,1,1),
    end_date = if y = null then #date(Date.Year(DateTime.LocalNow()),12,31) else #date(y,12,31),
    list = {1..Number.From(end_date)-Number.From(begin_date)+1},
    dates = List.Transform( list , (item)=> Date.AddDays(begin_date,item-1) ),
    table = Table.TransformColumnTypes(Table.RenameColumns(Table.FromList(dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "日期"}}),{{"日期", type date}}),
    date_id = Table.TransformColumnTypes(Table.AddColumn(table, "日期序号", each Date.Year([日期])*10000+Date.Month([日期])*100+Date.Day([日期])),{{"日期序号", type number}}),
    year_id = Table.AddColumn(date_id, "年序号", each Date.Year([日期]), type number),
    year_name = Table.AddColumn(year_id, "年份名称", each "Y"&Text.From([年序号])),
    quarter_id = Table.AddColumn(year_name, "季度序号", each Date.QuarterOfYear([日期]), type number),
    quarter_name = Table.AddColumn(quarter_id, "季度名称", each "Q"&Text.From([季度序号])),
    month_id = Table.AddColumn(quarter_name, "月份序号", each Date.Month([日期]), type number),
    month_name = Table.AddColumn(month_id, "月份名称", each "M"&Text.From([月份序号])),
    week_id = Table.AddColumn(month_name, "周序号", each Date.WeekOfYear([日期]), type number),
    week_name = Table.AddColumn(week_id, "周名称", each "W"&Text.From([周序号])),
    year_quarter_id = Table.AddColumn(week_name, "年季序号", each Date.Year([日期])*10+Date.QuarterOfYear([日期]), type number),
    year_quarter_name = Table.AddColumn(year_quarter_id, "年季名称", each "YQ"&Text.From([年季序号])),
    year_month_id = Table.AddColumn(year_quarter_name, "年月序号", each Date.Year([日期])*100+ Date.Month([日期]), type number),
    year_month_name = Table.AddColumn(year_month_id, "年月名称", each "YM"&Text.From([年月序号])),
    year_week_id = Table.AddColumn(year_month_name, "年周序号", each Date.Year([日期])*100+ Date.WeekOfYear([日期]), type number),
    #"year_week-name" = Table.AddColumn(year_week_id, "年周名称", each "YW"&Text.From([年周序号])),
    day_in_week_id = Table.AddColumn(#"year_week-name", "日序号", each Date.DayOfWeek([日期],0), type number),
    day_in_week_name = Table.AddColumn(day_in_week_id, "周天名称", each if [日序号] = 1 then "WD1" else
if [日序号] = 2 then "WD2" else
if [日序号] = 3 then "WD3" else
if [日序号] = 4 then "WD4" else
if [日序号] = 5 then "WD5" else
if [日序号] = 6 then "WD6" else
"WD7"),
    work_day = Table.AddColumn(day_in_week_name , "工作日", each if [日序号] = 6 or [日序号] = 0 then "休息日" else "工作日" )
in
    work_day

利用Uri.Parts()函数解析追踪url

= Table.AddColumn(重命名的列, "Custom", each Uri.Parts([咨询页面]))

替换部分值（如果A列等于a，则替换B列的c（部分字符）替换为b 否则不替换）

= Table.ReplaceValue(替换的值, each [账户], each "计划-1", (x,y,z)=>if y="账户名" then Text.Replace(x, "计划-", z) else x, {"计划"})

List.Contains  判断a查询中的A列的值 是否在b查询的B列存在

= Table.AddColumn(已插入分隔符之间的文本, "是否无效", each if List.Contains(无效ip[无效ip], [ip]) then "是 " else null)

地域名称不规则的情况下提取省市

//调用地域中的省,去重后使用
省源=地域,
删除的其他列 = Table.SelectColumns(省源,{"省"}),
省 = Table.Distinct(删除的其他列),
//在更改的类型中添加省份列,显示region中包含省的省
已添加自定义 = Table.AddColumn(扩展的表格列1, "省份", each Text.Combine(Table.SelectRows(省,(x)=>Text.Contains([region],x[省]))[省]," ")),

2、新建度量值

度量值在power pivot中新建，常用语计算或者统计，在数据透视中交互使用，随着筛选值的变化而产生不同的结果。

同比：

=CALCULATE([转化数],DATEADD('dim日期'[日期],-1,DAY))

执行除法运算，并在被 0 除时返回备用结果或 BLANK：

=DIVIDE([以下项目的总和:消费 2],[转化数])

计数：

=COUNT([是否无效])

聚合：

=CALCULATE(,,...)第一个参数是计算表达式，可以执行各种聚合运算从第二个参数开始，是一系列筛选条件，可以为空；如果多个筛选条件，用逗号分隔所有晒选条件的交集形成最终的筛选数据集合

3、建立关系

账户数据如何与咨询转化挂钩？每天每时每个地域数据是怎样的？消费与收款的roi如何具体到账户计划甚至是关键词？这里就需要对各个表之间建立关系。

4、使用数据透视展示数据模型

A、依据时段、地域、关键词等新建不同标签页并插入数据透视，需要注意关系是否建立正确。

B、后期数据添加到对应文件夹后刷新即可，注意后期数据格式 列等要一致，出错需要到powerquery查找问题。

这里就大功告成了，具体需要分析的项目，以及如何调整，请在本站自行搜索！