当您试图对某个特定术语进行排名或拼凑竞争对手的情况时，当您只想了解一小部分数据时，可能需要查看数万个反向链接。我不知道你怎么样，但当我看到成千上万的反向链接时，我有点哭泣。
这就是Excel中的数据透视表可以派上用场的地方。他们非常擅长将数据合并并分割成小的、可管理的数据块。当我说到数据透视表和Excel时，有些人可能会开始流汗，视野变得狭窄，但不用担心，这是值得学习的——特别是如果你可以为你的客户带来更多的收入。我将向你展示如何使用数据透视表提取重要的、可消化的数据块，这样你就可以更好地理解你的竞争对手的链接景观，并有可靠的证据，垃圾链接(如目录、论坛、博客评论等)仍然有效!
获取数据
让我们从数据开始。你可以把你客户的所有竞争对手的反向链接或集中在一个给定的关键字。我喜欢专注于我想要排名的关键词，因为它更易于管理和操作，更容易知道如何才能排在前3-5位。
假设我在Javamo工作(我不知道这些人是谁，我只是随机挑选的)，我想在“在线咖啡豆”的前三名中排名。
我们必须跳过以下竞争对手来排名在最重要的位置：
- www.coffeebeandirect.com/
- www.coffeereview.com/
- www.damicofoods.com/CoffeeBeansOnline.html
- www.sweetmarias.com/
- www.starbucksstore.com/
现在，让我们拉出它们的反向链接和Javamo的反向链接，这样我们就可以看到我们需要在哪里获得链接。我使用Open Site Explorer下载到根域的外部反向链接。如果您的客户或竞争对手有数百万链接，您可能想要考虑使用Majestic。
注意：我建议在PC或最新版本的Mac中执行此操作。Mac上的旧版本Excel将挫败您超越信仰，因为它并不是真正使用枢轴表和消除重复的东西 - 这正是我们在这里做的事情。
在Excel中堆叠数据
将反向链接复制并粘贴到新的Excel工作簿中的列B中。将数据堆叠在一起。所以我们有www.coffeebeandirect.com的反向链接以上www.damicofoods.com的反向链接等。
清洁脏数据
我们现在拥有成千上万的反向链接 - 18,283的巨大列表。我们希望为每个竞争对手找到最高质量的页面和域，并消除重复。如果我们要将所有链接页面留在域上，则网站链接将膨胀我们的数字。
您可以通过选择B到K，添加过滤器（Control + Shift + L），然后首先排序页面权限到最小，然后域名权限最大到最小。删除过滤器（控制+ Shift + L）。
将B列复制粘贴到A列，将K列粘贴到L列。标签A列为“Clean URL”，L列为“Clean Target URL”。按住控件并单击A和L列，然后分别找到并替换http://，然后www.， /*不带任何内容。请确保在/*之前没有任何内容来替换http://，因为您将删除整个URL!
消除重复的
这将给你干净的url，我们可以使用消除重复功能在最高质量的域上有最高质量的页面。通过选择A到L列并点击“删除重复”按钮，消除“清除URL”和“清除目标URL”列中的重复项。取消选择除“清除URL”和“清除目标URL”以外的所有列。点击确定。现在您有了竞争对手根域的最高质量的页面。
在你做了一次并弄清楚之后，你可能想要再做一次并将它记录为一个宏。对于走捷径的人来说，可以节省2-3分钟，而对于没有捷径的人来说，可以节省3-5分钟。如果你打算经常使用这个工具，这绝对是值得的。
创建数据透视表
我们现在可以对这些数据进行切分，以找到链接的机会。
1）选择数据 - 单击列头（例如A，B，C，D等）选择列至数据的列。
2）创建枢轴表 - 单击“枢轴表”按钮，然后选择“确定”。
3）组织您的领域 - 这应该带上枢轴表的新表。现在我们需要开始弄清楚我们想要看的东西。要开始，我想弄清楚我的竞争对手的锚文本是什么类型的优化。这可以告诉你是否与锚文本有效的真正激进。它也可以为您提供可能想要目标的其他关键字的想法。
在“透视表字段列表”中，将“锚文本”移动到下面的“行标签”和“值”字段。它应该是这样的:
现在，您必须对锚文本的数量进行排序，以查看竞争对手正在优化什么。点击屏幕右下角的“减”按钮，将屏幕缩小到40%。将A列和B列之间的线向左拖动以减小列的宽度。现在增加你的页的大小到100%。
右键单击“锚文本计数”标题下的一个数字。鼠标悬停在“排序”上，点击“从大到小”。您必须再次减小列的大小。见上图。
在这个特定的领域，竞争对手并不咄咄逼人地使用精确匹配的锚文本。然而，在更多的“垃圾”行业，如银行贷款、旅游等，你可能会发现竞争对手真的在优化关键字。
5）分析链接策略的数据 - 现在双击“锚文本数”下的数字之一。这将为新纸张中的特定锚文本吹出所有数据。将这些URL扔进去Ontolo的URL评论家你可以通过一吨网站微风来寻找机会。
只要看看咖啡评论中的图像，我知道他们正在评级咖啡馆的咖啡和回旋网站发布评论。如果我是JavaMo，我会考虑创建一个描述我豆类的信息图表（可能包括黑暗评级，酸性等级，ECT），然后伸出咖啡馆，卖掉我的豆类来添加信息图。
我也注意到甜蜜的玛丽亚有多么从7个域名与锚文本“家庭咖啡烘烤”的联系。令人惊讶的是，甜蜜的玛丽亚正在为“家庭咖啡烘烤”排名第一，与7个站点的“家庭咖啡烘烤”，从7个站点的一个具有类似设计和3个独特的IPS - 也许是一个小的链接网络？这是否意味着您购买一些域并创建自己的链接网络？由你决定。
In less than 5 minutes, we looked at one small slice of data to come up with a better idea of how to rank and even inspiration to rank for other terms (e.g. if Javamo could even get some low-quality, exact match anchor text links, they might be able to knock out Sweet Maria’s #1 position for a keyword with 590 exact match monthly Google visits).
切片反向链接数据的其他方法
查看锚文本只是我们可以用数据透视表分析的众多指标之一。我们可以查看域名和链接类型，其中每个人都得到了链接，而我们没有(这对刚刚推出自己的网站/品牌的初创公司来说非常好，因为你可以从公司简介页面获得链接)。
我们可以提取域类型(例如。edu， .gov.等)和链接类型(例如博客文章，论坛，目录)。我看到Sweet Maria 's和Coffee Review加起来有23个。edus，所以我想看看如何进入这些页面，或者想办法进入其他的。edus页面。您可以使用IF、FIND和IFERROR函数从链接URL中提取此信息。我不打算深入讲解如何做到这一点，但你可以回顾一下蒸馏的Excel的seo博客文章。
提取链接类型时，它将不是精确的。例如，如果“博客”出现在其URL中，我们假设它是博客文章。但是，URL可能是www.seerIntactive.com/Blog-Contributors。所以这不是博客帖子，但我们将其算作博客帖子。
如果我们查看竞争对手链接类型，我们可以看到Sweet Maria在58个论坛中有链接，超过200个博客。因此，在下个月，我们可能希望测试论坛链接如何影响我们的排名。
或者我们可以看看那些链接到我们的竞争对手而没有链接到我们的域名。至少有一百个域名链接到竞争对手而不是我们，所以我们需要问为什么。这可以帮助我们建立策略和获得联系。
使用这个工具来证明你的SEO策略
使用这种数据驱动的方法，您可以围绕serp中发生的事情形成策略，并且对客户更加透明。所以，如果三大竞争对手真的在目录链接方面打得很激烈，你可以告诉你的客户，下个月你将把所有的时间都花在获取目录链接上。为了更进一步，你可以提取收入、交易和目标，让他们知道在关键词前三名的排名对他们的底线意味着什么。这当然有助于如果你没有链接预算。将您的策略与客户的业务绑定数据可以为您的搜索引擎优化工作增加价值和有效性。
请注意，查看反向链接只是图片的一部分。您还必须考虑域权限，标题标记，H1和页面内容。
我希望你有机会坐下来玩这个工具。这是上面的“在线咖啡豆”示例：https://seerinteractive.box.com/s/59v8kxhyfy0mjdda5c5b。从划痕开始的Excel Nubie应该需要大约一个小时和Excel巫师，也许30分钟。您的大部分时间都将花费从开放站点资源管理器中删除数据。
如果您确实有机会玩这个工具，请分享关于这有关如何帮助您的故事以及上面未提及的数据的任何其他方式。
