Google SEO 数据分析常用Excel公式

在 SEO 工作，我总是发现自己在仔细研究数据并寻找加快分析过程的方法。分析数据通常是乏味、头脑麻木和无聊的工作，因此任何可以加快在大海捞针中找到针头的方法几乎总是一个好主意。

如果你花时间学习这些EXCEL公式，我保证这将是值得的——你可以轻松地处理 Excel 中的数千（或更多）行，废话不多说，给大家介绍一下：

公式1

=if(isnumber(search("string 1", [beginning cell])),"Category 1", if(isnumber(search("string 2", [beginning cell])),"Category 2", "Other" )

如果我已经让你感到困惑，我深表歉意。我将更深入地研究该公式，解释其含义并提供 3 个不同的用例，说明它如何帮助你加快工作速度。

用例 1：关键字研究

当做关键字研究时，可能你有数千行的数据需要研究搜索量，尝试将相似的关键字放在一起以查看相似性，进行关键词分类是SEO的基本工作，那么如何快速对关键词进行分类呢？

如果你想分解整个列表（681 行数据，显然都没有显示在屏幕截图中）以找出有多少查询包含“supplement”这个词，或者你想知道有多少包含“muscle”。在单元格 C2 中，我们将键入公式：

=if(isnumber(search("supplement",A2)),"Supplement", if(isnumber(search("muscle",A2)),"Muscle","Other"))

翻译后，此公式表示：搜索单元格 A2，如果找到“supplement”，则返回类别“supplement”。如果未找到“supplement”，则查找“muscle”，如果找到，则返回“muscle”作为类别。如果既没有找到“muscle”也没有找到“muscle”，则返回“other”作为类别。

我可以继续在公式中添加我认为合适的规格.下面的屏幕截图显示了这个公式的作用：

这个公式的真正威力在于它可以在整个数据集上使用，无需有人手动浏览和分类每个关键字。双击单元格 C2 的右下角会将公式应用于 C 列中的所有单元格，只要 B 列中它旁边有一个值（这是 Excel 的规则） ，不是公式）。下面的屏幕截图显示了将公式应用于所有数据的效果。请注意公式如何从分析单元格 A2 更改为单元格 C19 中的单元格 A19，其中应用了该公式。

“muscle”未在屏幕截图中列为类别，但在数据集中稍后列为类别。在这一点上，我还需要指出公式中的一个不足之处。如果特定查询包含多个我们尝试分类的字符串，它将返回它找到的第一个正字符串匹配的类别。第 29 行就是一个很好的例子。在此特定场景中，查询是“supplement”，但由于公式在查找“muscle”之前先查找“supplement” ，并且在“supplement”中找到了正匹配，因此它将单元格归类为“supplement”。

在既没有找到“supplement”也没有找到“muscle”的单元格中，它返回“other”。此时，我们向数据集添加了一个过滤器，可以过滤掉所有“muscle”和“supplement”查询，以准确揭示“other”的组成部分。

查看此列表，包含“protein”的查询似乎占列表的相当大的百分比，因此我们也可以将其添加为一个类别。从这里我们可以添加一个数据透视表并按搜索量和关键字数量进行排序。

从这里我们可以了解我们应该将努力的目标定位于何处以及我们需要更多关注的地方。在这一点上，“other”仍然是一个太大的类别，所以我会进一步细化它以创建更多类别，以找出我们如何使其更具可操作性。

用例 #2：拒绝垃圾外链

根据你站点的大小，你可能会处理数千或数百万个外链跟域名，因此你需要从某个地方开始并减少你的列表。一种方法是通过某种指标对域进行排序（我经常使用Majestic 的信任流）。我使用该公式来查找与垃圾邮件域相关的常用词，例如“submit”、“seo”、“directory”、“free”、“drugs”和“articles”，当然还有更多（“. xyz”是我经常看到的另一个）。该公式会在你的链接根域列表中找到任何指定的查询，让你可以快速将它们识别为垃圾邮件并将它们添加到你的拒绝列表中。下面的屏幕截图显示了一个示例站点的链接配置文件，按“Spam”排序，使用上面的过滤器作为标准，然后按信任流的升序排序。本例中使用的公式比我们之前的示例稍长，但遵循相同的模式。

=IF(ISNUMBER(SEARCH("submit",A2)),"Spam",IF(ISNUMBER(SEARCH("seo",A2)),"Spam",
IF(ISNUMBER(SEARCH("directory",A2)),"Spam",IF(ISNUMBER(SEARCH("free",A2)),"Spam",
IF(ISNUMBER(SEARCH("drugs",A2)),"Spam",IF(ISNUMBER(SEARCH("articles",A2)),"Spam",
IF(ISNUMBER(SEARCH(".xyz",A2)),"Spam","Other")))))))

在许多情况下，你的链接配置文件会包含来自听起来合法的域的垃圾链接。此公式无法过滤掉所有垃圾邮件，但它通常有助于从你的列表中删除至少一些域。此外，现在被公式标记为垃圾邮件的某些域可能实际上是合法网站。你应该始终分析此公式的输出，以确保其正常工作。同样，它可以作为你拒绝工作的起点，并有望减少某些域，但这绝不是你应该查看的唯一内容。

用例 #3：网站自然流量分析

这个分类公式的另一个非常酷的用例是来自 Google Analytics 的数据分析。，我经常分析有关从自然渠道到网站的流量的信息。我将显示的结果数从 10 更改为 2,500 并导出数据。导出后，我可能想知道哪些类型的页面往往会获得最多的流量、最高的转化率、最多的收入，或者相反。

由于每个站点都不同，你会在每个站点上寻找不同的内容。理想情况下，该站点将具有已建立的子文件夹结构，例如 example.com/blog/article-1、example.com/supplements/product-1 或 example.com/toys/gadget-1。使用 URL 中的这些常见功能，你可以为它们添加任何你喜欢的标签，可能是“blog”或“supplements”或“toys”，并使用此分类来细分哪些类型的页面最有效以及在哪里可以改进。

我从 Google Search Console 导出了网站的数据，并按“compare”、“reviews”、“altermatives”和“other”划分了他们的页面。由此，我能够确定我们可以改进的地方，确定有效的方法，并向客户展示更具体的数据。

公式2

=REGEXEXTRACT(A2,"^(?:https?:\/\/)?(?:[^@\n] +@ )?(?:www\.)?([^:\/\n]+ )”)

或者

=IFERROR(ARRAYFORMULA(REGEXEXTRACT(A2:A,"^(?:https?:\/\/)?(?:[^@\n] +@ )?(?:www\.)?([^: \/\n]+)”)),“”)

您可能需要查找某些具有HTTP 与 HTTPS 的博客文章或登录页面。

您始终可以使用 Excel 的搜索栏单独查找它们，但如果您有很长的 URL 列表，这可能会很耗时。相反，使用REGEXTRACT 从您的列表中提取特定数据。

=REGEXEXTRACT(A2,"^(?:https?:\/\/)?(?:[^@\n] +@ )?(?:www\.)?([^:\/\n]+ )”)

您可以使用 ARRAY 公式一次将其添加到多行。公式看起来很乱，但它有效。

=IFERROR(ARRAYFORMULA(REGEXEXTRACT(A2:A,"^(?:https?:\/\/)?(?:[^@\n] +@ )?(?:www\.)?([^: \/\n]+)”)),“”)

这是使用我们的示例的样子：

这是最终结果：

概述

Excel基本函数讲起来可能需要分很多篇文章，有幸网上有很多帖子都分享到了，大家可以搜索看看。这里我只整理了一些有难度的，我们工作会用到的分享出来，可能也要分几个篇幅了，今天就先到这里。

分享一个工具SEO in EXCEL（网址：http://seoinexcel.com/）。

附：SEO要掌握的一些基本的函数截图