如何在Excel中构建高级关键字分析报告

作者的观点完全是他或她自己的(不包括催眠这种不太可能发生的事件),可能并不总是反映墨子的观点。

当你有数以千计的关键词需要回顾时,分析关键词的表现,发现新的关键词机会,并确定哪些关键词需要重点关注是非常困难的。关键字指标来自各地(分析、广告词、网站管理员工具等)。),在不进行大量手动数据操作的情况下,定期在一个地方分析所有数据是一项挑战。此外,根据网站的商业模式,将收入指标与关键词数据捆绑在一起是另一场战斗。

这篇文章将带你了解这些关键词分析问题的解决方案,并提供一些如何以美妙的方式分割数据的技巧。

使用Microsoft Excel,我们可以在一个地方创建一个包含您需要的所有关键字数据的报告,并且每周或每月更新都相当容易。然后,有了这些数据,我们可以很容易地对其进行分类,从而更快地确定性能更好的关键字集。

我们需要做的是推动百度分析,网站管理员工具,广告词,排名数据,收入数据都到一个excel电子表格。然后,我们将把它们放在一个主报告和一个分类数据透视表报告中。

首先,您应该特别熟悉数据透视表、百度 Adwords API、百度 Analytics API和关键词研究当然了。利用这些API并保持放入电子表格的数据格式一致,将使更新变得容易。如果您不熟悉这些工具,我在下面提供了资源和一些组织这些数据的步骤。

以下是一些学习在Excel中使用数据透视表的资源:

搜索引擎优化ExcelMicrosoft数据透视表概述

现在让我们去获取数据。

我有99个问题,但关键字访问不是一个

首先,我们需要通过百度分析API获得我们的关键字流量指标。我建议使用Mikael Thuneberg的GA数据获取电子表格。您可以按照说明,阅读操作指南,并下载文件这里.

确保构建GA数据获取文件或其副本,因为它为API调用安装了适当的VBA函数(允许API工作的Visual Basic代码)。一旦有了API令牌和电子表格设置,就可以执行第一次API调用了。

我们将使用更复杂的查询来提取特定日期字段的有机关键字访问,并根据访问次数进行过滤。例如,我使用的查询将输出在过去30天内访问次数达到或超过5次的任何关键字的访问次数、平均停留时间、页面浏览量和反弹次数。但是,您可以根据自己的喜好修改参数。要查看可以使用的其他指标,请查看分析API文档.

您的分析数据应该如下所示:

百度 Analytics通过Excel中的API调用数据。

现在选择整个关键字列,并在另一个工作表中创建关键字列表的数据透视表。在相邻的列中创建一个表,其中的单元格等于数据透视表列中的值。给这个表格贴上“关键词列表”或者任何你喜欢的标签。我们现在有了提取Adwords数据时要参考的关键字表。

数据透视表不具有与常规表相同的引用能力,因此B列中的表是您将在后续步骤中引用的表。

被搜查,还是不被搜查,这是个问题

接下来是为我们的关键字表拉搜索量。感谢Richard Baxter,有几篇关于使用和安装Adwords API插件的文章。一个打开SEOmoz一个打开Seogadget.

我知道Adwords API的访问对一些人来说是一个问题,所以如果你不能使用API,利用百度 Adwords关键字工具(从这个工具收集数据将不幸需要更多的工作)。

在一个新的工作表中,使用名为“arrayGetAdWordsStats”的Adwords API数组公式来获取关键字表的平均和季节性月度搜索量。您的公式应该如下所示:

=arrayGetAdWordsStats(关键字列表,“精确”,“美国”,“网络”)

你现在应该有12个月的历史搜索量和所有关键词的平均值。

Adwords API调用的结果通常如下所示。

注意:如果您的关键字列表超过800个关键字,您必须将列表分成几个单独的表,以便对这些关键字执行API调用。如果是这种情况,请确保搜索卷的每个数组都在相同的列中对齐。

我得到的印象是

这里不需要API,百度的网站管理员工具提供了一种非常简单的方法来下载搜索查询数据。如果你在网站管理员工具中打开搜索查询报告,在底部有一个“下载表格”的选项。下载您之前使用的相同日期范围的表格,并将其放入一个新的工作表中。

从网站管理员工具下载的报告。注意“-”用于零值,在黄色的列中,我简单地用IF语句清除了它。

印象,点击率和平均排名现在可以添加到我们的指标。

如果你不是第一页,你就是最后一页

既然我们都知道从网站管理员工具得到的平均排名有多准确,那就让我们把一些当前的排名放进这份报告吧。从电子表格中抓取你的主要关键词列表,用你选择的应用程序对它们进行排名。我通常使用等级跟踪器,但我肯定每个人都有自己的偏好。一旦你有了你的排名,把它放到一张新的纸上。

你知道的越多

我们可以添加到报告中的指标数量是无限的,但是添加太多会增加更新报告的工作量或者造成分析瘫痪。如果你有一个专业帐户,我建议加入的唯一的其他度量是SEOmoz关键字难度。同样,添加大量关键词可能非常耗时,希望你有一个实习生.

Mo资金Mo指标

收入数据可能来自不同的地方,这取决于您的业务是如何运作的,因此很遗憾,我没有导入这些数据的一站式解决方案。但是,大多数应用程序通常允许您将数据下载到CSV或Excel。如果您在百度 Analytics中启用了电子商务,您可以使用API来获取这些数据。只要你有一些与关键词相关的指标,如平均订单价值或转换率,把它放在一张新的表格中,你就可以开始了。

你们中的一些人可能会问自己,如果你的收入数据与关键词访问没有关联,该怎么办。这就是关键字分类在本报告中发挥极其重要作用的地方。在这种情况下,我们希望在收入数据和关键字数据之间建立一座桥梁。这可以通过将您的关键字归类到与您的收入数据中的字段相关的类别中来实现。例如,您可以将关键词与产品名称或登录页面相关联。这些产品或登录页面将成为类别。一旦您确定了您的类别,您可以将它们分配给新表单中的关键字,该表单只在一列中包含关键字,在另一列中包含类别标签。您可以了解有关关键字分类的更多信息这里.

对上面的关键词进行分类,不仅让我可以将它们分组以汇总指标进行分析,还可以让我在这个例子中缩小关键词和转换之间的差距。

一份报告统治他们所有人

最后我们有了所有的数据;我们只需要把它们放在一起。创建一个新的工作表,使用=NameOfTheTable将主关键字列表拉进来,向下拖动直到到达列表中的最后一个关键字(如果您想要排序功能,请在后面粘贴值)。现在选择你的关键字并创建一个新的表格。在关键字旁边的列中,您所要做的就是纵向查找函数您希望添加到报告中的每个指标。一旦您填写了每列的第一个单元格,该列将自动添加到表格中,并使用公式填充其他单元格。重复此过程,直到您的所有指标都在此表中。

还需要计算一些指标,如跳出率或转换率,如果你拉收入数据。这些也应添加到相邻的栏中。此外,如果你之前不需要对你的关键词进行分类,我建议现在把它们分类在一个相邻的栏目中。完成后,您的主报告应该如下所示:

主报告。

太神奇了。我们将所有的数据都放在一个简单的表格中,便于排序和使用!等着吧…会好起来的。

关键的成功

现在你可能想知道这份报告怎样才能变得更好。两个字,我的朋友们:数据透视表.

创建主报表的数据透视表将允许您以多种方式分割数据,这在以前是不可能的。在数据透视表字段列表中,行标签、列标签和值将定义报表的布局。我们首先需要做的是将类别和关键字字段分别拖放到行标签中。这将设置您的顶级指标以在类别级别进行总结,并允许您深入每个类别以查看相关的关键字及其各自的指标。

接下来,您将需要开始将您的指标拖动到值部分,这将自动使用值字段填充列标签部分。在中添加指标时,您可以编辑它们的名称以及它们的聚合方式。您将需要仔细考虑如何汇总某些指标,以便在类别级别查看这些汇总的数字是有意义的。

这向您展示了如何最好地设置数据透视表字段及其值设置。

例如,我可能会总结印象和访问,但平均点击率和跳出率。看到一个类别的平均点击率和跳出率将允许我缩小哪组关键词比其他的表现更好。然后查看那些表现良好的类别的总印象和访问量,这将让我看到哪里有更大的潜力来增加我的网站的流量。虽然这可能不是确定关键词焦点的绝对规则,但它是一个很好的经验法则,可以作为优先关注哪些关键词的方法。

数据透视表还允许您添加报告过滤器,允许您通过任何指标甚至多个指标来过滤数据。有了这个,你可以使用当前排名作为过滤器来分析只在SERPs第一页排名的关键词。见鬼,你可以在主报告中添加一个字段,计算每个关键词短语中的字数,然后根据这个和跳出率进行过滤,给你一个表现良好的长尾关键词。发挥创意,放轻松,玩玩指标,你会惊讶于你能对你网站的关键词流量得出什么样的结论。

最终产品。

结论

更新报告很简单。使用新的日期范围重新运行API调用,重新运行新关键字列表的排名,并使用新的日期范围导出您需要的其他报告。只要你保持你的格式和公式不变,排名和其他报告应该放入各自的表中,而不必做任何改变。一旦您更新了关键字列,主报表应该会自动更新,而当您点击pivot table菜单下的refresh时,pivot报表应该会更新。就是这样!

好吧,我可能应该停止说话了,现在让你得到你的小时小时的关键字分析的乐趣。希望这些信息足够丰富,使构建这样的报告变得相当容易。我很乐意听到你的反馈,并乐意回答任何问题或评论。如果你以后有问题,你可以随时通过Twitter联系我。

Excel模板

Keri根据Dan对这篇文章的评论补充道:因为有人对分享我用来更好地理解步骤的数据集感兴趣,所以我在我的个人网站上提供了一个下载。你可以下载excel模板这里。希望这有所帮助

继续阅读:

原创性声明:本文章为本站作者独家原创,未经允许禁止任何形式的采集、转发,若发现违规转载,我站将追究违规者法律责任。

上一篇:小心使用AdWords进行关键字研究

下一篇:寻找新关键词:简化关键词研究