[ad_1]
分析您自己的链接资料以及竞争对手的信息对于 链接建设者，链接审核员或其他 诊断流量下降。
但是，很多工作都受到链接爬网软件自身UI功能的限制，并且无法像在电子表格中那样容易地进行操作。
因此，通常在分析链接配置文件时，我们导出所有数据，并使用一些过滤器和数据透视表分析电子表格中的链接，但是我们知道必须有一种更有效的方法。
典型的电子表格或链接软件方法的示例限制和问题：
- 如何有效地在电子表格中一次将多个过滤器应用于链接配置文件？
- 您如何在过滤规则中保存逻辑以重复使用？
- 您如何轻松地每月更新一次链接配置文件而不覆盖过去的工作？
- 如何将多个链接配置文件彼此堆叠以提取常用模式？
我发现，学习和使用Google的查询公式在Google表格中构建MVP仪表板是弥合标准分析与成熟的定制软件之间的鸿沟的绝佳方法。
查询功能基础
如果您有中级经验 电子表格功能，并且对vlookup和索引匹配感到满意，您会发现查询功能是一个非常强大的公式，比经过数小时的练习后想像的要容易。
但是查询功能是什么？
Google表格专家Ben Collins 完美地说：
“它允许您使用功能强大的数据库代码（伪SQL，结构化查询语言，用于与数据库通信的代码）来操纵Google表格中的数据，并且具有超强的功能。
可以说，它是Google表格中最强大的功能。”
遵循本指南，您将能够在没有以前的查询知识的情况下构建仪表板，但是如果您想快速入门一下，这里是我的一些最爱：
- Google表格查询功能：Google表格中最强大的功能
- Google表格查询功能：掌握大规模懒惰的武器
- Google表格QUERY功能教程 （视频）
- Google的QUERY函数参考 （了解其工作原理后用作参考）
第1步：设置Google表格
我知道我总是喜欢跳到模板，但是我建议您自己执行此过程，以便在不超过一个小时的时间里锻炼自己的知识并学习基础知识。
如果您现在必须拥有模板，那么我已将其链接到最后，但请尝试抵制！
首先，只需创建一个新的Google表格即可， 新的 在浏览器中输入。
然后，创建一个名为“信息中心”的标签，并为您要分析的第一个网站的链接配置文件的名称创建一个标签。
我对要使用哪个网站作为示例的想法太过费力，并且想要一个链接配置文件可以相对关联的网站（又名，而不是《纽约时报》或YouTube），所以我选择了纽曼的自己的网站，因为他们捐赠了 100％ 利润用于慈善事业。
因此，在这种情况下，我们既可以分析纽曼公司的联系，就像我们自己为公司工作一样，也可以作为进入市场的新贵慈善食品公司。
对于链接研究工具，我使用的是Ahrefs，但是您可以使用喜欢的任何链接爬网软件，例如Moz，Majestic，SEMrush等。
请注意，您必须根据所使用的链接软件来调整过滤公式，但是基本逻辑是相同的。
步骤2：导出第一个网站的链接数据
稍后我们将讨论分析多个网站，但现在让我们从第一个网站开始，无论它是您自己的网站还是竞争对手。
在这里，我们在Ahrefs网站资源管理器中查找www.newmansown.com，并将过滤器设置为每个域一个链接，Dofollow和Live。
“每个域一个链接”设置是保持数据整洁的唯一关键条件。它仍然可以与组相似或全部一起使用，但是它会给您太多重复，最终会带来很多噪音。
如果您也希望分析nofollow链接，则可以取消设置。如果您愿意，也可以设置为“RecentorHistorical''链接。
然后，我们导出所有行。如果您使用大型网站，则可以选择将链接限制为更易于管理的数量，例如前10,000个。
您需要将Excel列调整为过窄的列，例如“首次看到”和“最后检查”。
然后转到您创建的标签（在本例中为“ newmans”），然后将数据导入工作表。
小提示：您可以复制和粘贴，但是在某些配置文件中遇到了一些错误，由于某些格式问题使粘贴变得混乱，因此某些链接行在单元格中混杂在一起。
例如：
因此，文件->导入更安全。
步骤3：构建初始简单查询
现在，我们可以构建第一个查询公式来测试一切正常。
查询函数的格式如下：
QUERY（数据，查询，（标题））
因此，我们首先从newmans标签中选择数据范围：
作为初始测试，这将导致提取所有数据：
如果您正在使用的网站有大量链接，则可能会出现此错误。
如果Google表格没有自动为您添加行，请继续添加所需的行数：
步骤4：建立初始筛选器
现在我们知道查询公式有效并且可以提取所有数据，让我们添加一个过滤器以仅提取选择的行。
我浏览了链接配置文件，发现其中一些引荐网页标题包含“道德”字样，因此我将其作为过滤器进行测试。
在公式的第二部分中，我们开始编写“select *其中G包含'道德'''。
这意味着我们要选择G列包含“道德”的所有数据。
现在我们可以看到显示了标题中带有道德的7行。太酷了！
通读Google的 查询功能指南 了解编写查询语法的基本选项。然后，您可以查阅完整的查询语言参考以获取完整的语法。
一旦学习了一些基本知识，例如“包含”，大于等于（>，<）以及另外一两个，您很可能会用80％的时间，因此不必觉得自己必须阅读完整的参考开始。
第5步：使用其他过滤器进行扩展*下拉菜单
现在，我们可以使用查询公式了，我们将创建一个公式列表，并在一个简单的查找表中为其命名。这将使我们能够构建一个下拉列表，以选择不同的快速查看数据的方式。
首先，我们将添加“道德”及其名称，并将其放在带有标题的I和J列中。
我们正在做的只是在查询公式中添加每次都会更改的子句。
除了提供下拉菜单外，这还使我们可以添加多个公式，并更清楚地阅读如何创建它们以便将来进行调试。
让我们添加更多一些参数。
我们将创建一个用于链接域评级（DR）高于70的域。然后，我们将创建第三个结合这两个域的域：
然后，我们将在A2中创建一个下拉框，并在B2中创建一个vlookup，它将查找该下拉选项的名称并在我们的参考表中进行查找：
我们将为vlookup创建公式：
= vlookup（A2，I1：J5,2，false）
现在它可以正常工作了，我们可以在B2中看到与查询公式相同的子句：
Nex，在A2中，我们将创建下拉列表。我们转到数据->数据验证：
然后为下拉选项选择I2：I4：
现在，当我们回顾A2时，可以看到带有3个选项的下拉列表：
使用DR> 70选项进行测试。但这不会更改下面的数据表，因为我们仍然必须更新原始查询功能才能将信息提取到B2中。
因此，我们回到查询公式，然后将其调整为引用B2信息。
我们从where区域开始提取查询语法的硬编码部分。现在我们通过添加“＆B2＆”来进行连接，如下所示：
而且看起来运行良好！
测试下拉菜单中的第三个选项，该方法同样有效：
步骤6：根据目标构建其他过滤器
内置的其他过滤器应确实反映此项目的目标。
如果您要分析竞争对手的链接配置文件，则需要考虑自己想知道的关于他们的配置文件的信息，以及您想对这些信息做什么。
在这种情况下，假设您想了解网站从何处获取链接。
您应该创建链接类型分组列表：
- 新闻文章
- 资源页面
- 来宾帖子
- 信息图表
- 论坛
然后，您将考虑可以帮助您识别这些链接组的逻辑规则和过滤器：
-
新闻文章：引荐域与您的已知新闻网站列表匹配。
-
资源页面：在参考页面标题标签或参考页面URL中包含“资源”或“链接”。
-
访客帖子：在引荐页面标题标签或锚文本或引荐页面URL中包含“访客帖子”。
-
信息图表：在引荐页面标题标签或锚文本或链接URL（例如纽曼的页面URL）中包含“信息图表”。
-
论坛：在引荐页面标题标签或引荐页面URL中包含“论坛”或“主题”。
这些逻辑规则会一直有效并完美地抓住所有链接吗？
没有。
但是您可以优化公式，直到您足够满足您的需求并帮助您了解市场为止。
如果您正在为链接审核项目构建此仪表板（例如，在诊断潜在的链接构建损失时），则您可能会关注其他指标，例如锚文本中的垃圾邮件关键字或黑名单中来自黑名单域的链接。
在这种情况下，查看纽曼的自己的链接，我们会看到大量的优惠券链接：
因此，我们将建立一个优惠券链接过滤器：
并且我们要确保将下拉列表和vlookup扩展到我们在公式查找表中添加的新行，无论是一行还是20行！
为了进一步帮助您，我们将创建一个汇总表，以查看每个过滤器中有多少个链接，并添加一些平均值：
Looking good!
Step 6: Efficiently Updating Dashboard on Schedule
We talk about a way to automate this later in the article, but in this version of the dashboard, you’ll be manually updating your link profile tabs on a scheduled basis, such as monthly.
This is as simple as replacing the data on the existing tabs with the freshest data from your link crawling software, such as Ahrefs.
With your existing tabs, you should duplicate them, add the date into the tab name, and then hide the tab so you have an archive. If your sheet gets too large you can move these archive tabs to another sheet made just for archiving.
That’s it on this level 1 version of the dashboard. I think it’s a huge step up from standard spreadsheets, and allows you to quickly filter through thousands and thousands of links in a link profile all at once.
Copy the Template
As promised, here’s the Google Sheet template。
Make a copy and plug in your own data.
Next, we’ll look at some ideas on how to take this to the next level and make it more efficient.
Next Level: How to Combine Multiple Website Link Profiles
Here’s another problem.
Let’s say you’re looking at 20 competitors and you want to understand all of their link profiles at once.
How do you do this without copying and pasting any time you want to update the link data?
The answer is to stack the queries together.
You bring in all the links from the separate tabs, one per competitor website, and stack them together in the dashboard.
The reason you would do this is to filter down to types of links and see where competitors are getting their links from and see if there are common patterns.
For example, if 12 of your top 20 competitors are outranking you and all tend to get a high percentage of their links from guest posts on sites over a domain rating of 50, you’ll want to pay attention and bake that into your strategy.
This is similar to a link intersect tool that link crawler tools provide, but allows more flexibility and faster filtering and clustering.
Here’s how to do that.
Step 1: Find Competitor Domains & Import Data
Within Ahrefs, you can go to Competing Domains and see the top competitors for a website.
For this case, we’ll choose Annie’s and Brianna’s.
Same as before, export the data and then import it as new tabs.
Step 2: Create Table with Tab Names
Next, we’re going to create a very small table with the tab names and a concatenation formula.
This will be helpful to assist with our edited query formula.
If you’re only comparing two other competitors, this part is a bit of overengineering, but when you have 20 competitors that may change frequently, it’s worth laying it out in this format.
Step 3: Change Query Formula to Include Indirect
So now what we’re doing is we’re referencing the green tab table above, and using the indirect function to reference the cell contents as a string.
In addition, since we’re combining multiple tabs, we have to use the curly braces {} around our indirect references and separate them with semicolons.
这个 video breaks down using curly braces for bringing in data from multiple tabs:
It’s complicated, but this works for our goal here!
Step 4: Editing Column References
When bringing in data from multiple tabs/tables, what I call “query stacking”, the typical column references A, B, C, etc. don’t work and you have to change these to Col1 for A, Col2, for B, etc.
Here’s an example of how that breaks as soon as we stack the queries:
So we edit the formula in the “formula for Query” line in column J, changing ‘G’ to ‘Col7’:
And we’re back in business!
Step 5: Expanding the Formula
So that’s all you have to do for combining link data from multiple domains into a dashboard using the Google query function.
If you want to expand the green reference table with a ton more competitors, just be sure to update the query formula accordingly.
I’m sure you can refactor the code even more, but this is a happy medium for this example.
You can add more features to this sheet which we don’t have time for today, such as sorting the query table, adding in lookup tables to filter in and out sets of domains, and using evergreen master lookup databases that expand over time for link blacklists.
Advanced: How to Automate Further with APIs
We’ve detailed a fairly straightforward process for analyzing link profiles in a way that makes updating your analysis more evergreen and efficient.
The logical next step is to reduce the manual work of exporting link profiles every time you want to analyze.
Although it’s beyond the scope of this article (let me know if you’d like me to cover this in a future one), connecting the link crawler software API with Google Sheets set to a scheduled update would be the best way to make this more efficient. The companies mentioned earlier – Ahrefs, Moz, and Majestic – all have link APIs that can get us the data we need, faster.
You can use software such as Supermetrics or Tray.io to connect the data to your Google Sheet, or roll your own with API Connector for Google Sheets。
For those of us not yet ready to dive into creating our own fully-fledged custom SEO software to fit unique needs, spinning up a Google sheet using the query formula is an extremely powerful way to level-up your data analysis in spreadsheets, and focus more time on finding insights rather than crunching through raw data haphazardly.