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

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

EXCEL竞价自动分析,Power Pivot数据建模分析-开水网络

3、建立关系

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

EXCEL竞价自动分析,Power Pivot数据建模分析-开水网络

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

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

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

EXCEL竞价自动分析,Power Pivot数据建模分析-开水网络这里就大功告成了,具体需要分析的项目,以及如何调整,请在本站自行搜索!