6

20+ Tips And Tricks About How To Manually Disavow Backlinks (Plus My Spreadsheet Template!)

Update: I have added a spreadsheet template based on the spreadsheet I used. Check it out!

Link disavow sounds dreadful, doesn’t it? At least, it did for me because I know this is not a super fun work… but it must be done once in a while 😒

My new 🦄 site had been up for it for a while… more precisely, since April 2019 when the last disavow file was uploaded 😱

The former owner told me that back then, he and the other website owner spent about 2 days together disavowing bad backlinks 😬 And the disavow file they produced had only 322 entries.

This did not sound very encouraging since I am all by myself here. How long would it take me???😱

I was even thinking of buying a backlink analyzing software but couldn’t bring myself to shell off a few hundred dollars a month 🤯

Eventually, the declining traffic and a surge in backlinks were really making me hurry up with this task.

unicorn_traffic_goes_down

A spike in backlinks 😒

Unicorn backlinks spike

So I finally did it! 😅 Manually! 😅😅

Thanks to some basic automation, it took me about two 7-hour days to go through almost 5600 links. I managed to do 412 backlinks per hour or 6.87 links a minute.

I think this was a pretty good speed!👍😅

More importantly, as a result, the disavow file grew from only 322 entries to 1774!

I alone found 4 times more bad backlinks than the previous owner of the site plus his friend! Under the same amount of time 😉 (yes I am super proud of myself 😁🦚🤣)

So in this post, I want to share with you my tips and tricks I came up with while doing this (highly) mundane work.

I used Google Spreadsheets to process the links so all the info will be about it.

At the end of the post, you can find a spreadsheet template I made based on the spreadsheet I used. It contains all the formulas I will be talking about as well as conditional formatting and a few examples.

What you won’t find in my post

Here, I assume you are already familiar with the link disavow process and how to do it (and why).

These are topics that have been discussed in depth everywhere. So if you are not familiar with it well enough, just google for “disavow links” and read 😉

So, I will talk only about how to manually disavow backlinks without using any paid software.

Collect all the backlinks

First of all, as I am sure you already know that you need to collect as many backlinks as possible 🔢

Go to Google Search Console, Bing Webmaster console, Yandex, Ahref, SemRush – everywhere you can think of and download the backlinks.

Just remember that you need to register your site in advance in search consoles to see the backlinks. For example, Yandex seems to take about two weeks to start showing external links of a newly added site.

Preparations

Now, upload all the links to a Google spreadsheet.

I use only the URLs without any other extra info I got. It made my spreadsheet clearer and easier to process. All the information I needed to make the disavow decision, I got it from the website themselves.

Backup your spreadsheet regularly!

Do it once in a while, ideally, before each big change / cleaning. You may realize you made a mistake way down the road when the spreadsheet history has got overridden already 🤦‍♀️🛑

Helpful formulas

Here are a few Google spreadsheet formulas that should make your life easier:

  • Extract the domain of a link;  B is the column where your link is located:
    =trim(REGEXEXTRACT(REGEXREPLACE(REGEXREPLACE(B1,”https?://”,””),”^(w{3}.)?”,””)&”/”,”([^/?]+)”))
  • Count duplicates; D is the column where your link domain is located =COUNTIFS(D:D,CELL(“contents”,D1))

Clean the URL list

First, to avoid doing the same work twice, you need to remove duplicates from our list. For that, we need to make the URLs more “uniform”:

  1. Replace “http” with “https”
  2. Remove “www.”
  3. Make sure all the URLs either have a backslash at the end or don’t

Now, you can remove duplicates from the URL column 🧹

Merge with the existing disavow file

If you already have a disavow file, you probably don’t want to go through the links it already disavows (unless you want to double-check if the previous domain disavows still make sense):

  1. Upload the old disavow file into a separate sheet in the same spreadsheet
  2. Use VLOOKUP to match the old entries with new ones
  3. Hide or delete (I recommend just filtering out and not deleting) the rows that contain old links to make sure you won’t accidentally go through them.

Show link metadata

Add extra columns that show metadata of your links.

Here are columns I use:

  1. A column with the domain of a link (see the formula above ☝ if you need it)
  2. A column to count the duplicates of the domains (see the formula above ☝ if you need it)

The Domain Duplicate column, it shows you how many links are coming from the same domain 👯‍♂️👯‍♀️

If u see the number > 1, it means that this particular domain has more than 1 link coming from it. You can filter by the domain and process a bunch of links at once – and also to make a decision if it’s a domain disavow or not.

It’s really worth it when you have dozens of links from the same domains; it’s a clear sign of spam! 📛

You can color-code this column with Conditional Formatting for better visualization. Use red for numbers > 1 and green for numbers <= 1.

Link annotations

Come up in advance with a strategy on how to annotate links ✅❌❔

You will need to know which links to keep, which ones to disavow, and which ones may need a deeper look 🕵️‍♀️

Some links should be disavowed as links and some as domains. When you look at each link, you should decide right away if it’s a domain disavow or link disavow. This will prevent you from going through the list again 👍

I am sure that you know about the need to be careful with the domain disavow.

Use one-letter annotations that are easy type; for example:

  • d – domain disavow
  • l – link disavow
  • n – keep (not for disavow)
  • / – unclear if it should be disavowed; may come back to it later (I used to use “?” but soon realized that it needs two fingers ✌ to type it! 😫)

Color-code everything with Conditional Formatting. It will help you to easily spot mistakes, patterns, etc. For the example above, you could highlight the “d” and “l” cells with red, “/” with yellow, “n” with green.

Checking each link

Now, the “fun” part! Go through the links one by one to check 🕵️‍♀️

A few tips here👇

Dealing with toxic sites

Check the links in an incognito window. While modern browsers are very good at blocking threats, it won’t hurt to have extra protection.

If you have kids (or other people who you do not want to see adult stuff like your parents), keep them away from your screen while checking links; you will most likely stumble upon a lot of adult sites that are not for child’s eyes (me personally, I wish I could unsee some things I saw there 😫)

Speeding up the work

Use the “Open links” shortcut to open multiple links at once. In the incognito window, you need to allow the spreadsheet to open pop-ups to make it work.

For the actions that you repeat often (for example, filtering), record or write spreadsheet macros that you can run using shortcuts 👨‍💻

A couple of tips here:

  • Use absolute references when recording a macro
  • Assign each macro an easy shortcut (they are local to the spreadsheet you are working in)
  • Keep recorded macros as is or modify them to your liking (it’s not that hard)

While performing the check, keep two windows with the spreadsheet next to each other:

  • In the left one with the non-incognito mode, annotate the links
  • In the other one with incognito mode, check the links

You will need to make sure both windows show the same rows in the spreadsheet.

Be aware that the incognito window has several limitations; for example, the spreadsheet macros won’t run in it.

Creating the disavow file to upload

Ok, you went through all the links, congrats!🎉

Now, let’s create a new disavow file!

In the same spreadsheet, create a new sheet for it.

Based on the link annotations (domain disavow, link disavow), you can easily see which links should be disavowed on the domain level.

For these links, copy the Domain column you created in the Preparations step above. 

For the rest, copy the original URL column data. 

Now, use the power of spreadsheets again to create properly formatted disavow rows. 

Create a new column that would contain these rows.

Use this formula (where A is the column where your links are located)  to create a string that is properly formatted for both the domain and URL disavows:

=concat(if(ISERR(search(“http”, A2)), “domain:”, “”), A2)

Submitting

Now, copy-paste the column with the formula into a new empty text file.

For the name, use a version naming approach.
For example: disavow x.y.txt

Here, you’d increment x each time you submit a massive update of all links. Increment y when you submit a smaller update (several links) of the file.

So, for example, the second massive update file would have the name disavow 2.0.txt.

Finally, time to submit:

  1. Go to the Google Disavow tool
  2. Download the old file (just in case as a backup)
  3. Upload the new file

Et voila! 🤓

My spreadsheet template

And now, as promised, here’s the spreadsheet template that you can copy and use for your disavowing process:

(if you have troubles with revealing the link, let me know in the comments!)

You will find there three tabs:

  1. “1 – previous disavow file, ver 1.0” for the exiting disavow file
  2. “2 – all fresh links” for the backlinks you have collected while doing this disavow
  3. “3 – new disavow file, ver 2.0” to create a new disavow file

Tab 1: Previous disavow template

It has two columns.

The first column, this is where you add the lines from your previous disavow file.

The second column will contain the domain of the link from the first column. It is used on the 2nd tab of the spreadsheet.

disavow_spreadsheet_tab_1

Tab 2: All fresh links

Here, you add the fresh backlinks you got for your site.

The tab has multiple columns:

  • Source: where the links come from (Ahrefs, Google, Bing, etc)
  • Link: The actual backlink
  • Disavow?: The column where you mark the link as a disavow or not. It is color-formatted based on 4 options that are specified in the note of the column header (you can see on the screenshot)
  • Domain: A column with a formula that extracts the domain from the link
  • Domain Dup?: It shows if a particular domain shows up multiple times on the list
  • A new link / domain?: It shows if this domain already exists on the list of previously disavowed links; if so, you may want to skip it during the analysis.
  • Previous Link found?: The column shows the link that was found in the previous disavow file
  • Previous domain found?: The column shows the domain that was found in the previous disavow file
  • Cleaning: Added / at the end: A helper column that adds a backslash to the end of the URL if it’s not there; use it at the URL cleaning stage
disavow_spreadsheet_tab_2

Tab 3: New disavow file

This tab will make you lines for your new disavow file.

Add the URLs and domains to the first column and in the second column, you will get the disavow file lines formatted the way they should be.

disavow_spreadsheet_tab_3

Copy my spreadsheet template

You can get it via this link:

Disavow results

Meanwhile, it might be that my hard work has been already paying off 😊

Here’s a screenshot of SERPs of some of the money keywords I am tracking for my 🦄 site:

disavow effect already showing?

I expected to see the effect only in a few weeks.

However, it might be that Google started picking it up much earlier, only in a few days after I uploaded the file 👍

Don’t work hard, work smart!

With my process, I managed to do 412 backlinks per hour or 6.87 links a minute.

After backlink cleaning, my disavow file grew by more than 5 times in size. I also managed to process ~4 times more links for the same amount of time compared to two people who worked on the site previously 🦚😜

Keep in mind that this time, I also spent some time coming up with the procedure and writing macros and formulas.

So I think the next time, my disavow speed will be even faster 😅

I hope my tips and tricks will help you to speed up the fun process of disavowing links manually! 😊

Let me know what you think in the comments below! 👇👍

Click Here to Leave a Comment Below 6 comments