Power pivot 助力竞价优化师数据分析 Excel高阶竞价自动分析

1、前期准备

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

URL标记追踪相关文章传送门：

[表格]SEM大搜投放关键词推广URL追踪标记生成工具，助力您的营销效果翻倍

设置360推广竞价关键词url追踪标记方法

line广告投放基础教程 line lap 如何建立追踪码？

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

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

C、分析工具 excel、power query、power pivot，在2016版本中power query不用单独安装，在数据中直接使用，更低的版本就需要单独安装，下载地址：微软官方下载地址。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

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

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

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

3、新建度量值

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

同比：

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

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

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

计数：

=COUNT([是否无效])

聚合：

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

4、建立关系

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

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

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

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

这里就大功告成了，具体需要分析的项目，以及如何调整，请在本站自行搜索！想继续深入的利用Power pivot 进行数据分析的优化师们，可以看看老杨SEM博客的这篇文章：excel数据分析透视表power bi视频教程 高清完整版