Return to site

统治他们的一个方法:在Excel中轻松实现SEO数据分析

在SEO工作,我总是发现自己在研究数据并寻找加快分析过程的方法。分析数据往往是乏味,麻木和无聊的工作,所以任何可以加快在大海捞针中找到针头的事情几乎总是一个好主意。几个月前,我开始在Excel中使用公式对数据进行分类,并且我不断寻找新的方法来使用它。

花了一点时间和练习来记住公式,了解它是如何工作的以及如果它破坏了如何对其进行故障排除,但是学习它的时间和精力与我在使用它时看到的奖励相形见绌。成功。如果你花时间学习这个公式,我保证这是值得的 - 你可以很容易地将Excel中的数千(或更多)行减少到一口大小的块中,以便于洞察和数据呈现。

不用多说,我向你们展示:

= if(isnumber(search(“string 1”,[begin cell])),“Category 1”,if(isnumber(search(“string 2”,[starting cell])),“Category 2”,“Other” )

如果我已经把你弄糊涂了,我道歉。我将更深入地研究这个公式,解释它的含义并提供3个不同的用例,以帮助你加快工作速度。

用例#1:关键字研究

当我正在为客户进行关键字研究时,我正在盯着可能要分析的关键字及其搜索量的列表(可能长达数千行),我尝试将类似的关键字混合在一起以查看相似性模式。在Distilled(我们正在招聘,顺便说一句!),我可能会使用像Brightedge或SEMrush这样的工具来查看网站可见的查询。此外,我可以将一个主题放入Google关键字规划师,并获得每个Google类似字词的输出。将结果导出为CSV文件,您就可以获得数据分析的起点。您甚至可能想知道我之前提到的公式是如何有用的,因为Google关键字规划师提供了“广告组”列,因此应该很容易知道如何划分提供的关键字。

问题是,输出通常在“种子关键字”和“关键字想法”之间划分,这两者都不能用于分割关键字群组。上面的屏幕截图捕获围绕“锻炼补充”的相关术语的查询和搜索量(注意单元格A2中的“种子关键字”与所有其他的相比。)

但是,如果我想分解整个列表(681个查询,显然都未在屏幕截图中显示),以查明有多少查询包含“补充”一词,该怎么办?或许我想知道有多少包含“肌肉”; 我也可以这样做。

我要做的第一件事是删除A列(广告组),因为它完全没用。然后,我将在搜索卷列的右侧添加一列,并将其标记为“类别”。在这一点上,我们将提出我们最初的分类想法,所以让我们选择“补充”和“肌肉”。在单元格C2中,我们将键入公式:

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

翻译后,此公式说:搜索单元格A2,如果找到“补充”,则返回“补充”类别。如果未找到“补充”,请查找“肌肉”,如果找到,则返回“肌肉”作为类别。如果既未找到“补充”也未找到“肌肉”,则返回“其他”作为类别。

我可以继续按照我认为合适的方式添加规格; 当其他字符串被搜索时,“其他”会继续被推回。下面的屏幕截图显示了这个公式:

该公式的真正强大之处在于它可以在整个数据集中使用,无需人员手动浏览并对每个关键字进行分类。双击单元格C2的右下角(我们的工作表现在显示Supplement)将公式应用于C列中的所有单元格,只要在B列旁边有一个值(这是Excel的规则) ,而不是公式)。下面的屏幕截图显示了将公式应用于所有数据的效果。注意公式如何从分析单元格C19中的单元格A2变为单元格A19,其中正在应用公式。

“Muscle”未在屏幕截图中列为类别,但它稍后在数据集中列为类别。我还需要在这一点上指出公式的不足之处。如果特定查询包含我们尝试分类的多个字符串,它将返回它找到的第一个正字符串匹配的类别。第29行就是一个很好的例子。在这个特殊情况下,查询是“肌肉补充”,但因为公式寻找“肌肉” 之前寻找“补充” ,并且在“补充”中找到了积极匹配,它将细胞归类为“补充”。

在没有找到“补充”或“肌肉”的细胞中,它返回“其他”。此时,我们在数据集中添加了一个过滤器,可以过滤掉所有“肌肉”和“补充”查询,以准确显示构成“其他”的内容。

查看此列表,包含“蛋白质”的查询似乎是列表中相当大的百分比,因此我们也可以将其添加为类别。从这里我们可以添加一个数据透视表,并按搜索量和关键字数量进行排序。单击此处以了解有关透视表的更多信息。

从这里我们可以了解我们应该在哪些方面瞄准我们的努力以及我们需要更多关注的地方。“其他”,在这一点上,仍然是一个太大的类别,所以我进入并进一步完善它以创建更多类别,以找出我们如何使其更具可操作性。

用例#2:拒绝工作

谷歌声称新的企鹅更新 “越来越近”,但实际的发布日期仍然未知。众所周知,监控反向链接配置文件以获取垃圾邮件和操纵链接是一个非常明智的想法。如果您认为某些链接可能是潜在的责任,我建议您积极主动并分析拒绝某些链接的机会。我的同事Sergey Stefoglo最近写了一篇关于如何在30分钟内进行反向链接审核的文章,但是如果你计划手动检查你的引用域(你应该),这个分类公式可以提供帮助。

根据您网站的大小,您可能会处理数千或数百万个链接根域,因此您需要从某处开始并缩减列表。一种方法是通过某种度量对域进行排序(我经常使用来自Majestic的信任流))。我使用公式来寻找与垃圾邮件领域相关的常用词,如“提交”,“搜索”,“目录”,“免费”,“毒品”和“文章”,尽管肯定还有更多(“。 xyz“是我经常看到的另一个)。该公式在链接根域列表中查找任何指定的查询,允许您快速将这些查询识别为垃圾邮件并将其添加到您的拒绝列表中。下面的屏幕截图显示了一个示例站点的链接配置文件,按“垃圾邮件”排序,使用上面的过滤器作为条件,然后按信任流的升序排序。在这种情况下使用的公式略长于我们之前的示例,但遵循相同的模式。

= IF(ISNUMBER(SEARCH(“submit”,A2)),“Spam”,IF(ISNUMBER(SEARCH(“seo”,A2)),“Spam”,
IF(ISNUMBER(SEARCH(“directory”,A2)) ,“垃圾邮件”,IF(ISNUMBER(搜索(“免费”,A2)),“垃圾邮件”,
IF(ISNUMBER(搜索(“毒品”,A2)),“垃圾邮件”,IF(ISNUMBER(搜索(“文章”) ,A2)),“垃圾邮件”,
IF(ISNUMBER(搜索(“。xyz”,A2)),“垃圾邮件”,“其他”))))

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

使用案例#3:解析分析

此分类公式的另一个非常酷的用例是Google Analytics的数据分析。对于我的客户,我经常从有机渠道分析有关客户网站流量的信息。我将显示的结果数从10更改为2,500并导出数据。一旦导出,我可能想知道哪些类型的页面倾向于获得最大流量,以最高速率转换,带来最多钱,或者与所有这些相反。

由于每个客户的网站不同,您将在每个网站上寻找不同的东西。理想情况下,该站点将具有已建立的子文件夹结构,如example.com/blog/article-1,example.com/supplements/product-1或example.com/toys/gadget-1。通过URL中的这些常见功能,您可以将它们标记为任何您喜欢的标签,可能是“博客”或“补充”或“玩具”,并使用此分类来分解哪种类型的页面效果最好以及哪些页面最适合可以改进。

对于一个客户,我从Google Search Console导出了他们的数据,并通过“比较”,“评论”,“替代品”和“其他”来分发他们的网页。由此,我能够确定我们可以改进的地方,确定哪些工作正常,并提供更具体的数据来向客户展示。

结论

分类不会为您解决任何SEO或数字营销问题,但它可以使数据分析更快,视觉上引人注目。您识别机会的速度越快,您实际获得建议的时间就越多,对您的业务或客户产生影响。

这个公式非常通用,几乎可以用于任何事情。我希望您找到聪明的方法,使您的数据分析更容易,更少繁琐。由于每个站点都不同,所以在任何给定的场景中都不可能确切地说出你应该寻找哪些字符串,但如果你能从这篇文章中理解这个公式的力量以及如何重新创建它,那么你我会很快发现它可以用于比梦想更多的任务。

All Posts
×

Almost done…

We just sent you an email. Please click the link in the email to confirm your subscription!

OKSubscriptions powered by Strikingly