Close
Close

Log in

Not a member yet? Sign Up

Forgot your password?

Processing your login ...

Logging out ...

Item saved successfully.
Not a member yet?
Sign up ... it's free.
Join Now

A Step-by-Step Guide for Using Excel to Disavow Poor-Quality Referring Domains and Boost Your Search Rank

Although Google quite effectively detects low quality links and manages negative SEO, the disavowing process is still an important task for marketers to do to keep up with the search engine's quality guidelines.

As Google explains, "If you've done as much work as you can to remove spammy or low-quality links from the web, and are unable to make further progress on getting the links taken down, you can disavow the remaining links.

In other words, you can ask Google not to take certain links into account when assessing your site.

To protect your website from a potentially negative impact to its search result rankings, you need to conduct maintenance on its link profile to make sure it's clean. Here's a six-step process for how to do that.

1. Export backlink profiles from various SEO tools

The disavow file can be structured with referring domains, single URLs, or both. However, a link pointing from a low-quality domain usually tends to have little value, so it makes the most sense to include the entire referring domain instead of every URL separately.

Several tools provide detailed backlink profile overviews, such as MajesticSEO, SEMrush, Ahrefs, and Moz. Each of those provides slightly different information, so you will want to use multiple tools at the same time. That will make it easier to detect most of the referring domains and decide which of them should be included in a disavow file.

The important metrics for each tool specifically are the following:

MajesticSEO: TrustFlow and CitationFlow

SEMrush: TrustScore and DomainScore

Ahrefs: Domain Rating

Moz: Domain Authority

In the settings of each tool, make sure you select to export only referring domains providing "do-follow" links; "no-follow" links won't harm your site anyway.

After exporting the list into Excel or Google Sheets, delete all the unnecessary columns that don't contain the actual domain names and relevant metrics. You won't need that info to create the disavow file.

2. Aggregate all the exports into one Excel sheet

Once the unnecessary columns are removed, copy everything into one blank sheet. At this point, you'll have a lot of duplicates, which you can delete. Because of the different sources used to pull data into each tool's database, you'll still have a considerable number of unique domains even after deleting the substantial chunk of duplicates.

The next step is calculating a ratio for relevant pairwise metrics, such as Trust Flow and Citation Flow (TF/CF) or Trust Score and Domain Score (TS/DS).

For the referring domains where the CF (or DS) is zero, the calculation won't work because division by zero is not defined. Fortunately, the IFERROR function will embed "0" in those cells, which fixes the issue:

=IFERROR(TrustFlow/CitationFlow*100,0)

Also, multiplying the ratio by 100 and rounding it up will make it comparable to the single-value metrics, such as Domain Authority (DA) or Domain Rating (DR), which are already integers with no decimal places.

3. Normalize all the metrics, and filter the results

When TF (or TS) is higher than CF (or DS), the calculated ratio won't fit the desired scale between 1 and 100. You can normalize the metrics with the following formula:

=1+(OLD_VALUE-MIN(RANGE))*99/(MAX(RANGE)-MIN(RANGE))

Now everything will be on the same scale, so you can compare the results from all the tools together.

Then you can display only the low-quality candidates for disavowing, such as the ones in the bottom 5%. To filter those in Excel, use the "Top 10" option:

4. Remove domains that contain spam keywords

The presence of spam keywords in the domain name can also be a good indicator of poor-quality referring domains. For example, candidates for disavowing might be domains with keywords such as "free" or "casino." You may also want to remove domains with adult-content related words. Even domains with words such as "article" may be worth considering for removal if they are repository sites created only for SEO purposes (those no longer work).

You can customize the following formula with spam keywords to automatically label those domains in an additional column:

=IF(SUM(COUNTIF(A2,{"*Spam1*","* Spam2*",…})),"Might be spam","")

5. Finish and save the file

By now, your list should be filtered and ready to be explored more closely. It should show only domains with the lowest final score (or spam keyword), but it might be useful to go through the list once more and make sure it doesn't contain any relevant sites that might have growth potential.

If a domain has been included in the final list erroneously (or if you have any doubt), it should be left out of the file; it can be added later after a detailed analysis. However, all the links that have little chance of providing a positive contribution and are likely to have a negative impact on rankings should be disavowed.

One way to be sure whether to keep the referring domain or not is to inspect the list manually. The following questions are helpful with this process:

  • Are the links pointing from the domain natural and editorial?
  • Are the websites thematically connected and relevant?
  • Would it be necessary to start searching Google for more related information if the link weren't there?

After the final check, several adjustments will need to be made to match the disavow file requirements. The first step is to remove all five "http" and "www" combinations: https://www, http://www, https://, http://, and www. You can do this in two steps with the "find and replace" option:

You'll also need to add the "domain" to the beginning of every domain name, which you can do with a simple formula:

="domain:"&A2

At this point, the Excel part is finalized and the disavow list is ready to be saved and uploaded.

6. Upload to Google Search Console

The easiest way to save the file is to copy the relevant column containing the low-quality domains, paste it in a text editor, such as Notepad, and save it in ".txt" UTF-8 format. The final list should look something like this:

To upload the file, go to the disavow links tool page, select the relevant website from the drop-down list, and complete the procedure in a few simple steps under the guidance Google provides.

* * *

Although this process can be time-consuming, it will pay off. Bad links that are overlooked can affect rankings in an unpredictable way.

The good news is that you don't have to run through this process very frequently. Daily or weekly disavowing wouldn't make much sense, because the desired effect might take up to several weeks.

Google also says on its support page that "disavowing is an advanced feature and should only be used with caution."

If anything is unclear, or if you want to learn more, feel free to connect with me via my contact info, below.