万物复苏的季节，各个行业也都加大广告投放，苦了我们广大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\" elseif [日序号] = 2 then \"WD2\" elseif [日序号] = 3 then \"WD3\" elseif [日序号] = 4 then \"WD4\" elseif [日序号] = 5 then \"WD5\" elseif [日序号] = 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查找问题。
这里就大功告成了，具体需要分析的项目，以及如何调整，请在本站自行搜索！