教程 | 关键词标记高效追踪账户转化效果(1/2)
袁雷
导读:最近艾豆们在论坛发帖求助,如何追踪关键词的转化效果、如何给关键词添加标记诸如此类问题。今天蕊屹菌再把本教程翻出来给大家普及下,推荐收藏之。PS:本文教程有附带 Excel 模板,后台回复标记 获取模板下载链接;微信回复Excel学习 SEM 领域常用的 Excel 函数&技能。
但对于竞价数据分析而言,关键词追踪是一项大工程。如何利用关键词跟踪标记,控制无效消费并最大限度地挖掘有效消费关键词,是每位竞价人员都在琢磨的问题。关于关键词标记追踪的方法也有很多,例如有人用vba 生成,但是 url 太长总体来说不算特别友好;还有人可能用 #00001-#99999 这类的方法标记,显得不够直观。
这里分享另外一种批量给关键词添加标记的方法,希望对大家有所帮助。
标记示例:#1300109(其中13为计划的代码 ,001为单元的代码,09为关键词的代码)。整个过程都是利用 Excel 函数批量完成的,非常方便。
主题:利用 Excel 批量完成关键词跟踪
目标:批量修改关键词访问 url,并在尾部加上唯一跟踪标记
价值:
1.通过给关键词访问 url 加上标记,在后期统计数据时,能够精确定位访问 url 对应的消费关键词,从而帮助我们找到核心消费关键词,并在接下来的推广中,着重关注这些关键词的投放情况;2.本文加代码的方式跟 UTF- 8 的优点,一是 url 比较短,二是不容易被同行破译。
Excel 步骤简述
步骤 1:将账户中所有计划复制出来,统一编号。方法:在 B1 单元格中输入#01,然后右下角双击填充序号, 计划代码完成。如图:
步骤 2:添加单元代码
将账户中所有单元复制粘贴至 Excel 新工作表,将除了计划名称、单元名称的其他列删除,计划剪切至 A 列, 单元剪切至 B 列,在 C2 写入单元代码,C2 处插入公式:=TEXT(COUNTIF($A$2:A2,A2),"000"),右下角双击填充,第二步完工。如图:
步骤 3:添加关键词代码
将账户所有关键词复制到新的工作表,将计划名称、单元名称、关键词分别剪切到 A、B、C 三列。在 A、B、C 列后分别插入一空列,命名为计划代码,单元代码,关键词代码,关键词代码添加同单元代码添加相同,在 F2 输入公式:=TEXT(COUNTIF($C $2:C2,C2),"00"),右下角双击填充。然后利用VLOOKUP 函数在 B 列 D 列插入计划单元代码,前期大功告成,就差最后一步。
步骤 4:批量生成终极标记 url
在关键词代码后插入空列,在 G2 输入公式:=N2&B2&D2&F2,右下角双击填充,一切搞定。
接下来的工作,就是在百度推广客户端(助手)里批量更新关键词访问 url,这里就不再详述。
对关键词做跟踪标记是常用的关键词效果追踪的方式,看本文内容可能会有点难理解,建议大家后台下载模板,然后手把手的一边对照本文,一边操作学习,最后把整个逻辑理清楚就可以了。
当然,这对于新入行的同学可能会遇到一些问题,例如后期加词要注意什么?数据哪里去获取?数据统计到了之后如何对接?这些大家可以尝试探索下,我们下篇《关键词标记高效追踪账户转化效果(2/2)》会讲到。
技术贴 | 关键词标记高效追踪账户转化效果(2/2)
导读:前面我们发布了《教程 | 关键词标记高效追踪账户转化效果(1/2)》得到广泛的关注,好评如潮。今天分享关键词标注之后的数据对接方法。本文会偏于技术,如果 Excel 技术还很薄弱,建议大家多学一些,后期我们也会有公开课专门讲解
2013 年的时候, 小磊曾经打破 UTF-8 编码,原创设计了一套通过 Excel 公式迅速标记竞价账户的方法(具体标记效果如:?baidu1300109)。相对于 UTF-8 编码能够更好地隐藏自己的数据不被竞争对手破译,而且利于日常使用和后期的数据分析。
不过,发布之后有很多新入行的朋友,有些疑问,常见的有:加了标记后到底有什么作用?当然,这是为了让前端推广数据与后端资源数据、签约数据的有效对接而做的。
今天借蕊屹 SEM 平台,再次做一个介绍,或者可以理解为说明文档,不是说过程,而是说作用。下面就具体和大家谈谈。
一、日常调整账户使用
1. 区间(某一时段)效果快速查询
例如商务通(PS:当然也可以是 53 等在线沟通软件,商桥目前是无法支持搜索的)中搜索
?baidu0309027,对比助手中的该消费数据。如消费 2000 元的关键词,产生了 10 条留电,资源成本在
200,对比自己平均成本,以及该关键词的核心程度快速预估该关键词该段时间的整体对话效果。
同理,该方法适用于搜索?baidu03090,查看 03 计划 090 单元的效果,不用做报表,就能秒秒钟看到大
类(计划)中的小类(单元)的期间效果。有没有心动?快去加代码吧。(关键词标记设置点击 2013 年小磊原创《教程 | 关键词标记高效追踪账户转化效果(1/2)》)。
能看小类的时段效果,大类也就是计划的时段效果当然也是可以通过搜索?baidu03(03 是计划代码) 去查看效果,小磊就不再赘述,免得浪费大家时间。
2. 当日实时效果查询
区间效果查看的方法,认真思考过的小伙伴可能已经想到,同样的方法不也同时适用于每日实时效果监控吗,还用去数当天类别关键词的效果吗?那就真的out 了。虽然小账户一天资源不多,一眼就能看出类别效果,但是,日消费好几万的大账户,你还能用看吗?!这个方法很多竞价老手可能已经在使用了,只是入门早的大哥大姐们可能并没有告诉你们这些刚入门的新同学。赶紧试试吧。
二、周/月报表汇总使用
竞价是一个严谨的行业,其实竞价工作有些方面可以和一个公司的财务类比,这个我们日不离手的账户就好比一个公司,一个个计划好似公司的一个个业务板块,一个个单元又好似公司的各个部门。
财务离不开预算分配,记账,财务报表编制,财务数据分析,现金流规划等。其实我们竞价人员何尝不 是,说的有点远了,其实就想说说我们我们竞价的这个帐该如何记,我们竞价的这个报表又该如何编制, 如何留存,大数据时代,只有留存的数据才是企业资产。
1. 周月报编制要求
编制基本要求:一维可留存数据,如图,可累积留存,数据做趋势分析,如下图所示(以月报为例):
注:月份列最好使用日期格式,后期分析数据趋势可被自动识别
维度尽可能的细致。数据颗粒细致,精确到计划,单元,关键词,推广渠道,终端等自己分析需求的最细致的要求,后期可做类别汇总分析,数据类别钻取分析等。
2. 编制月报的思路
具体思路,消费报表与商务通报表的链接,这个当然需要交给关键词标记代码这个中间桥梁了。简单的说就是把消费报表和资源报表等通过 sumifs,countifs,vlookup 等函数链接起来。形成我们分析做需要的基础报表。PS:如果 Excel 技术强大的同学,可以制作直接生成超级数据透视表,数据建模所见即所得,我们这就不扯开了。
3. 具体操作方法a、准备数据:
① 下载统计月份的消费数据,可以分 PC、WAP(移动)分别下载
② 商务通统计月份有效数据的(带备注资源,到访等)
③ 助手下载制作一份代码参数表
b、参数报表设计及维护:
① 账户关键词复制一份
② 通过 find+mid 函数提取出关键词代码、计划代码、单元代码如下图:(具体参考示例文档)
③ 将代码移动到首列位置,方便后期其他函数调用参数
c、商务通关键词代码提取及状态标记:
① 导出商务通当月数据
② 通过公式获取客户预约状态(=IFERROR(FIND("Y",F2,1),0)),公式统计标记包含"Y"的为预约
③ 通过初次访问网址截取关键词,如下图:
d、消费报表关键词代码添加:
① 复制消费报表
② 通过 vlookup 函数获取关键词代码,需要给参数列新建一列 公式=计划&单元&关键词
③ 完成后见下图
e、报表合并为天下第一表:
① 消费数据复制到汇总或者直接在消费数据后边新建资源数,预约数
② 通过 countifs 和 sumifs 汇总,数据量特别大用这两个公式卡的朋友可以中间辅助添加一个数据透视表汇总后,在用 sumifs 条件求和(能操作大账户的同学,这个能力肯定是没问题吧)
③ 资源计数公式=COUNTIFS(商务通数据!A:A,消费数据!A2,商务通数据!B:B,消费数据!E2)
④ 预约求和公式=SUMIFS(商务通数据!C:C,商务通数据!B:B,消费数据!E2,商务通数据!A:A,消费数据!A2)
f、透视表分析:
g、制作多次报表留存的数据做趋势分析:(此处数据为虚拟 12 个月/周的数据)
总结:至此,我们所要分析数据的关键词报表便完成了。
三、其他作用
1. 到诊标识符使用
可以使用关键词标记做为电话号码的标记录入公司的 CRM 系统,当然,也可以是 Excel 报表追踪系统,视公司具体情况而定,这么一来便能追踪到该关键词对应资源的后续到院消费情况。
2. 将到数据与前端数据的无缝衔接
要追踪这个数据需要一份成熟的运营体系,有了这部分数据的反馈,其实就相当于再次使用第二步骤将到诊等后期数据追加到我们分析的源数据天下第一表中。
3. 其他作用
其他作用就等待我们小伙伴们自己脑洞大开去补充了。
小磊今天就简单介绍到这儿,有任何 Excel 方面的疑问,欢迎小磊的 Excel 交流群:98086726 探讨。
发表评论 取消回复