[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.