在本系列的第2部分中，我们收集了在Excel中进行搜索查询挖掘练习所需的所有数据，如下所示。现在，我们必须花时间准备数据，以便进行分析。这将包括创建派生字段，将信息带到表面，标记和删除噪声，将计数转换为比例，等等。我们将利用Excel的力量为我们的优势，并推动我们的数据的极限，以提取价值。以下是我们的数据需要能够轻松回答的一些问题:
什么搜索查询有很高的印象但没有点击?(可能是一个很好的负面候选)哪些搜索查询导致了转换?(在你的账户中提升它们以精确匹配关键字)。哪些搜索查询的点击率低于广告组的平均点击率?(消极候选人)哪些搜索查询的每次转换成本高于平均水平?第一步:将数据格式化为Excel表格
突出显示整个SQR数据集，包括标题行。
2。点击” Format as Table “从Home ribbon
3。选择一个你喜欢的风格
4 .单击“确定”
5.单击“确定”。重复这些步骤，为广告组和帐户关键字结构数据集
步骤2:标记重复搜索查询
重复搜索查询是广泛的，短语匹配搜索查询，已经在您的帐户作为精确匹配关键字的目标。换句话说，你的宽泛的和短语匹配的关键词可能是广告窃取你的精确匹配的关键词。
1。打开你的AdWords编辑器关键字数据，剪掉C列(关键字)，把它插入A列，这样“关键字”就是你的第一列。
2。在“sqr_data”选项卡的“N”列中创建一个新列“DUP_CK”。因为您将数据集格式化为表格，所以您只需在单元格“N1”中键入单词“DUP_CK”，就会创建一个新列。
3。在” N2 “中输入这个公式(或使用公式向导):
=VLOOKUP(表1[[#本行]，[搜索项]]，editor_kw_data!A:C,2,FALSE)
4。Excel处理完成后，复制所有列“N”，然后右键单击“粘贴特殊>值”回列“N”。这样，每次你在Excel中修改一些东西时，它就不会重新开始处理你的数据(如果你有100K个搜索查询，这可能会很烦人)。
步骤3:单词计数列
我们将在稍后的分析过程中使用搜索查询单词计数，同时我们正在调查数据。
1。和前面一样，在单元格“O1”中创建一个“WORD_COUNT”列。
2。在单元格” O2 “中输入这个公式:=LEN(A1)-LEN(SUBSTITUTE(A1， ” “， ” “))+1
步骤4:创建一个搜索查询对等CTR列
进行CTR比较(或合理的CTR期望)对我们的分析非常有价值。为了计算“同行点击率”，我们必须从广告组的总点击率和印象中减去搜索查询的点击率和印象，然后计算点击率。
1。在单元格“P1”
中创建一个“PEER_CTR”列。在单元格” P2 “中输入这个公式:
=(VLOOKUP(Table1[[#本行]，[Ad组]]，adgroup_data!A:L,4,FALSE)-Table1[[#本行]，[单击]])/(VLOOKUP(Table1[[#本行]，[Ad组]]，adgroup_data!A:L,5,FALSE)-Table1[[#本行]，[Impressions]])友好公式:(ag_clicks – sq_clicks)/(ag_imp – sq_imp) = PEER_CTR
步骤5:创建一个搜索查询对等转化率列
像” PEER_CTR”，比较(预期转化率)是一个伟大的指标，在你的搜索查询分析。您创建它的方式与刚才创建“PEER_CTR”非常相似。
1。在单元格Q1中创建一个PEER_CVR列(CVR =转化率)
2。在单元格“Q2”中键入此公式，类似于PEER_CTR”公式:
= (VLOOKUP(表1[[#这一行],[广告组]],adgroup_data ! A: L, 10, FALSE) Table1[[#这一行],[Conv。(1-per-click)]]) / (VLOOKUP(表1[[#这一行],[广告组]],adgroup_data ! A: L, 4, FALSE) Table1[[#这一行],[点击]])
友好公式:(ag_conv – sq_conv)/(ag_clicks – sq_clicks) = PEER_CVR
步骤6:创建一个搜索查询对等成本/转换列
为了我们以后寻找一个高CPA搜索查询，我们必须有一个平均或预期的CPA进行比较。
1。在单元格R1中创建“PEER_CPA”列(CPA =每次转换成本)
=(VLOOKUP(Table1[[#本行]，[Ad group]]，adgroup_data!A:L,8,FALSE)-Table1[[#本行]，[Cost]])/(VLOOKUP(Table1[[#本行]，[Ad group]]，adgroup_data!A:L,10,FALSE)-Table1[[#本行]，[Cost / conv.(一次点击1次)]])为了回答这个问题，“我们需要多少印象才能看到点击?”我们必须计算所需的印象指标。我们通过将您希望看到的点击数除以同行点击率来做到这一点。当然，正如我在PPC分析文章中所描述的那样，您可以使用一种更具“统计意义”的方法。关键是要创建一个标志，以便在稍后的分析中使用稀疏数据过滤搜索查询。
1。在单元格” S1 ”
中创建” needded_imp “列。在单元格” S2 “中输入以下公式:=1.5/Table1[[# this Row]，[PEER_CTR]]
步骤8:创建所需点击列
为了回答这个问题，“在我们应该期望看到转换之前需要多少次点击?”我们必须计算所需点击量。我们通过1-2次点击除以对等的CTR来做到这一点。
1。在单元格” T1 ”
中创建” needded_clicks “列。在单元格” T2 “中输入公式:=1.5/Table1[[# this Row]，[PEER_CVR]]
步骤9:创建一个足够的印象列
。在这一步中，我们将使用前面用” IF “语句创建的” needded_imp “列来设置一个标志，我们可以在后面的分析中用作过滤器。
1。在单元格“U1”
中创建一个“ENOUGH_IMP”列。在单元格” U2 “中输入以下公式:
=IF(Table1[[# this Row]，[Impressions]]>Table1[[# this Row]，[needded_imp]]， ” Y “， ” N “)步骤10:创建一个足够点击的列
我们将再次使用我们之前创建的派生字段” NEEDED_CLICKS “来设置一个标志” ENOUGH_CLICKS “。
1。在单元格“V1”
中创建“ENOUGH_CLICKS”列。在单元格” U2 “中输入以下公式:
=IF(Table1[[# this Row]，[Clicks]]>Table1[[# this Row]，[NEEDED_CLICKS]]， ” Y “， ” N “)这是一个非常重要的过滤器，我们稍后将使用它来帮助我们专注于低于平均CTRs的搜索查询。下面描述的公式将为CTR低于同类广告组30%以上的搜索查询设置“LOW_CTR”标志。
1。在单元格” W1 ”
2中创建” LOW_CTR “列。类型这个公式在细胞“W2”:
=如果(Table1[[#这一行],[CTR]] < (Table1[[#这一行],[PEER_CTR]] * 0.7),“Y”、“N”)第12步:创建一个低转化率列
，就像低CTR一样，低于平均转化率可能是一个消极的关键字候选属性。
1。在单元格“X1”
2中创建“LOW_CVR”列。在单元格” W2 “中输入以下公式:
=IF(Table1[[# this Row]，[Conv.]，[Conv.]，[[# this Row]，[Conv.]]，[[# this Row]，[Conv.]率(1-per-click)]] <(表1[[#这一行],[PEER_CVR]] * 0.7),“Y”、“N”)
步骤13:创建高的每转换成本列
。高CPA还可以洞察搜索查询的潜在问题。搜索查询在使用不同的登陆页面时可能表现更好，或者可能需要将其添加为负面关键字。下面的公式为CPA高于其他搜索查询30%以上的搜索查询设置了一个标志。
1。创建一个“HIGH_CP单元格“Y1”
2中的“列”。类型这个公式在细胞“Y2”:
=如果(表1[[#这一行],[成本/ conv。(1-per-click)]] > (Table1[[#这一行],[PEER_CPA]] * 1.3),“Y”、“N”)
这看起来工作量很大，但是一旦您熟悉了这些技术，创建所有12列应该只需要大约5分钟的时间。
我还鼓励您尝试为分析创建自己的派生字段。我上面所描述的内容来自于大量的试验和错误(使用我自己的数据)，这绝不是你可以/应该准备数据的唯一方法。从你要问的数据问题开始。这将帮助你找出你需要回答这些问题的新字段、标志等。我期待着我的下一篇关于高级搜索查询挖掘的文章，在其中我将向您展示如何开始挖掘您准备好的数据以获得见解。我们将创建一个负面候选人列表以及关键字扩展列表。
这是Chad Summerhill的一篇客座文章，他是PPC Prospector博客的作者，PPC教程的提供者，也是移动解决方案公司(UPack.com和MoveBuilder.com)的内部AdWords专家。参见Chad Summerhill的其他文章
更多像这样的文章8种聪明的方法在谷歌广告竞争(不提高出价)这里有8个策略和24个战术，你可以在出价之前做。
如何使用谷歌广告自定义片段
了解如何使用这个未充分利用的谷歌广告功能的力量。
为什么你的非电子商务业务应该设置转换值(及如何做)
揭示了如何在谷歌广告(和其他平台)中提高领先质量和确保最高ROI的关键。
评论
评论前请阅读我们的评论政策。
