Author: Bengü Sarıca Dinçer
As an SEO expert, I’ve witnessed firsthand the power of backlinks to propel websites to the top of search engine results pages. However, it’s not enough to acquire backlinks once and assume they will continue to benefit your website indefinitely. The quality, relevance, and status of your backlinks can change over time and failing to monitor them can hurt your SEO.
By regularly monitoring your backlinks, you can identify potential issues and remedy them before they impact your search visibility—or as a way to showcase your success to stakeholders.
Although there are probably hundreds of SEO tools, Google Sheets’s flexibility makes it handy for many SEO applications—particularly for backlink monitoring (and especially for those working with a tight budget). To get started with the process I developed, you’ll also need a data source (i.e., Google Analytics 4) to pair with Google Sheets.
Once I show you how to connect these two elements, you’ll be able to reference your quantity of backlinks, specific target pages, their referring domains, and their statuses (follow/nofollow)—all from one Google Sheet, and for free if you’re willing to put in a few minutes of work.
Table of contents:
Why use Google Sheets for backlink monitoring?
You could simply track your backlinks using a third-party SEO tool or within Google Analytics, but chances are you’re going to export that data into a Sheet or Excel file to work with or report on later anyway.
And, Google Sheets is flexible and free, making it far more accessible and ultimately likely to save you time. Here’s how that flexibility and freedom play out in an SEO setting:
Unlike dedicated SEO tools that generally require paid subscriptions, Google Sheets only requires a Google account. This makes it an appealing option for those on a limited budget (while maintaining effective backlink monitoring capabilities). Even for businesses with a generous SEO budget, you might still need to show a proof of concept before you can ask for a sliver of that budget for more paid tools. While dedicated SEO tools provide extensive features and additional data, they generally come at a significant cost. These tools are designed for in-depth SEO analysis, typically encompassing far more than backlink monitoring. However, if your primary focus is monitoring and managing backlinks, Google Sheets can offer a free, collaboration-friendly alternative without compromising essential monitoring capabilities.
Google Sheets enables you to import backlink data from various sources, including Google Analytics, Google Search Console, other SEO tools, or manually. Once you import your data, you can leverage Google Sheets’ functions, formulas, and formatting options to organize and analyze the information. This is crucial because, while you may monitor backlink data directly via a third-party tool or GA4, it’s not always easy to analyze it within those platforms—especially if you need to cross-reference other data points.
As a cloud-based application, Google Sheets allows you to access your backlink monitoring data from any device with an internet connection. This enables you to monitor your backlinks even on the go and facilitates easy collaboration with other team members. This particular feature might be appreciated by professionals that compulsively check their backlink profile every time they launch a new product, publish a new blog post or press release, etc.
How to set up Google Sheets for efficient, up-to-date backlink monitoring
This workflow process involves exporting backlink data (either from GA4 or another SEO tool; but for this article, I’m sticking to just GA4) and setting up a Google Sheet as a shareable, easy-to-reference resource for more efficient backlink monitoring and collaboration.
01. Create a new Google Sheet to store your backlink data
Start by visiting Google Drive (you’ll need to log into your Google account).
Next, create a fresh spreadsheet to manage your backlink data. For those unfamiliar with Google Sheets: Click the + New button and select “Google Sheets” from the dropdown menu. Then, give the spreadsheet a title (the title doesn’t matter for this workflow).
02. Import your backlink data into Google Sheets from GA4
One of the easiest ways to export backlink data from Google Analytics is by using a Google Sheets extension. I prefer SyncWith’s GA4 Google Analytics add-on for this purpose because it’s free and allows me to automatically refresh the data within the Google Sheet via a scheduling option.
To get started, first, install the extension by clicking here or by navigating to it from within the Google Sheet you just created:
1. Click Extension on the toolbar at the top of your Google Sheet and then select Get add-ons.
2. Next, search for [GA4 Google Analytics add-on] in the Google Workspace Marketplace pop-up window. Find the one by SyncWith (which is what I’ll use for this backlink monitoring technique) and install it. Then, connect your Google Analytics account by allowing the required permissions.
3. Now, in the top toolbar, go to Extensions>GA4 Google Analytics addon by SyncWith>Launch sidebar.
4. Select Google Analytics v4 as your connection.
5. Finally, fill in the required fields for the data you want to export.
Report title: The new Sheet tab to which backlink data will be exported (by default, the text in the field reads “Untitled report,” as shown above). Don't use any spaces between words in the title to ensure your Google Sheets formulas work properly. For the Google Sheets formulas I use in this article to work properly, title your report “LinkingPages”.
Select a login: Select your GA4 account.
Select a Google Analytics v4 property: Choose the property you want to export backlink data for.
Time period: Choose from options on the dropdown window to list links from a specific time period on your website.
Fields: Select the following —
“Page referrer” to show the source page that linked to your web page
“Landing page” to see which of your web pages received the backlink from the source page
“Date” to see when your web page got the backlink
“Sessions” as a metric
Sort: Select “Page referrer.”
Dimension Filters: You can use these fields to eliminate sources you do not follow as backlinks. For example, I filtered out links from search engines such as Google and Bing because search engines are organic traffic sources for us, not referrals.
Update mode:
“Replace contents of sheet” will overwrite all backlinks in your current Sheet when your data automatically updates.
“Append rows to end of sheet” adds new backlinks to the bottom of your current Sheet when your data automatically updates.
“Update or add rows as needed” is the option I prefer, because it helps prevent backlinks from being listed more than once.
As an example, here’s what the GA4 add-on with the desired parameters looks like:
Click the Next button to set up your automatic reporting schedule.
6. You can set the report to automatically refresh:
Daily
Weekly
Monthly
Hourly
Every five minutes
I find that daily updates are sufficient for my situation. While there are other options on this screen (“Status row” and “Insert location”), it’s not necessary to change them for this example.
7. Hit the Insert button and voilà! The add-on starts exporting your backlink data from your Google Analytics property. This process should take a few minutes to complete.
After the data finishes exporting, you should see your backlinks list in the new Sheet (that you titled “LinkingPages” at the first step of working with the GA4 add-on).
03. Customize your backlink monitoring spreadsheet
In order to make the data we imported more meaningful, we need to create a new spreadsheet and customize it with some columns and formulas. The starting point here should be what the spreadsheet needs to monitor.
Here are the basic columns you’ll need to create for your backlink monitoring spreadsheet:
Linking pages: These are the pages linking to yours (which we already had a list of in our “LinkingPages” tab). Enter the following formula into the cell below the “Linked pages” column (A2):
=(LinkingPages!A3)
The “LinkingPages” part of the formula references data from the LinkingPages tab. So, if you give your sheet a different title, you should write the corresponding sheet title in the formula instead.
The second part in the formula, “A3,” refers to the first cell of the source page (data exported from Google Analytics via the add-on). To apply the formula to the other cells below, hold the blue dot at the bottom-right corner of the first cell you entered the formula in and drag it to the cells beneath. You will repeat this process for each column.
Linking page titles: This refers to the linking page’s title tag, which can help you get a better idea about the content on the referring page. To gather this info, use this formula on the first cell of this column (B2):
=IMPORTXML(A2,"//title")
Apply the formula to the other cells below by dragging the blue dot at the bottom-right corner of the cell you entered this formula in.
Target pages: This refers to your web pages that receive the backlink. Enter the following formula into the cell below the “Target pages” column to extract URLs from the “Landing page” column of the “LinkedPages” report sheet.
Important: Enter your own domain name instead of “https://domain.com” in the formula.
=ARRAYFORMULA("https://domain.com" & LinkingPages!B3)
Apply the formula to the other cells in that column as needed.
Lost status: This allows you to see whether you still have a backlink on the referring page (or, alternatively, have lost the link). Use this formula on the first cell of this column:
=iferror(importxml(A2,"(//a[contains(@href, '"&$C2&"')]/@href)[1]"),"No link")
Apply the formula to the other cells in that column as needed.
Nofollow status: This is where we can see if the backlink has a nofollow tag. Use this formula on the first cell of this column:
=IF(ISNUMBER(SEARCH("nofollow",LOWER(IMPORTXML(A2,"(//a[contains(@href, ’"&$C2&"’)]/@rel)[1]")))),"Yes","No")
Again, apply the formula to the other cells in that column as needed.
To give you an idea of how the final spreadsheet looks, look at this example I created for QRCodeDynamic, a dynamic QR code generator—one of the micro-projects I’ve been working on.
You can customize the spreadsheet with additional columns (such as for Domain Rating, your own notes, etc.), but that will be a manual process.
With your Google Sheets backlink document set up and your data imported, you’re now ready to automate the monitoring process so that you can reference the most up-to-date data and get notified of any changes to your backlink profile.
Automate the backlink monitoring process
At this point, you have a spreadsheet (showing your backlinks and their current statuses) that is scheduled to update at the frequency you specified during configuration. But, it needs a few last touches to ensure that it always shows the most up-to-date data.
Automatic data recalculation
By default, whenever changes are made to the underlying data, Google Sheets automatically recalculates formulas and functions. However, for larger spreadsheets or complex calculations, automatic recalculation can sometimes cause performance issues or slow down the responsiveness of your Google Sheets document.
To make the process flawless, click Settings from the File tab’s dropdown menu in your Sheet’s top toolbar. Then, in the Calculation > Recalculation section of the window that opens, select “On change and every minute,” and save the setting.
This should ensure that your formulas recalculate whenever there’s a change in the data. For example, a change regarding nofollow status in a cell should trigger a recalculation on the spreadsheet.
Email updates about new backlinks
How can you keep tabs on whether there’s been a change in your spreadsheet (e.g., gaining or losing a backlink)?
A lot of different solutions can notify you of these changes, but for the purpose of this article, I’m going to use Make.com (freemium).
With a simple scenario consisting of only Google Sheets and Gmail modules, you can get notified of every change to your monitoring spreadsheet via email. To configure the scenario, choose Google Sheets as the starting module and select “Watch Changes” from the menu.
After connecting your Google Sheets with Make.com, choose Gmail as the second module and select “Send an email” from among the options. After connecting your Gmail account, you can define the title and content of the email that will be sent to you after changes occur on your backlink monitoring spreadsheet.
Troubleshooting common Google Sheets backlink monitoring issues
Even with the most streamlined backlink monitoring processes and advanced tools, it’s not uncommon to encounter a few bumps along the road. In this section, I will mention some common issues that may arise during this process and provide tips for troubleshooting and resolving them.
Performance issues: Google Sheets becomes slow or unresponsive, especially when working with a large amount of backlink data or complex calculations. To solve this issue, consider working with smaller data ranges. This can help improve performance by reducing the amount of data being processed. In addition, disabling unnecessary add-ons can also help to improve performance.
Formulas are returning errors or incorrect results: To handle this, double-check the syntax of your formulas. Even a minor error can lead to incorrect calculations. Also, ensure that the cell references in your formulas are accurate and point to the correct data range. Incorrect references cause formulas to return incorrect results. In addition, keep in mind that sometimes a linked page may not use an element written in the formula. For example, the formula we set to find the title element for the page title column will return an error if the linked page does not have a title tag.
Data discrepancies: If you notice inconsistencies between the data in Google Sheets and the actual data from your data source (i.e., GA4), ensure that you’ve configured your spreadsheet correctly. Incorrect settings can lead to mismatched or incomplete data.
Automated backlink monitoring—not automated digital marketing
Whether you’re automating your backlink monitoring using my process or a third-party SEO tool, the data is just URLs and words in spreadsheet columns until you make it work for your SEO.
Consider how your link-building efforts factor into your business’s overall goals. If you haven’t already, clearly define your objectives. Determine what specific metrics or insights you want to gather from the data. This will help you structure your monitoring approach and focus on the most relevant aspects of your backlink profile.
To that end, you should also establish a baseline for your backlink profile before initiating any link-building campaigns. This baseline will be a reference point for future analysis and enable you to contextualize changes and progress over time.
Now, give my backlink tracking template a try to discover insights that can help drive your website’s search visibility over time. Best of luck!
Bengü is the SEO team lead at Popupsmart, where she consistently seeks innovative strategies to enhance website performance, increase organic rankings, and maximize conversion rates. She also enjoys staying up-to-date on the latest developments in the SEO world. Twitter | Linkedin