DIY SEO Tools

Build Your Own Free Backlink Checker Tool With Google Sheets

Jaco29
Original article contributed by: Sean Malseed
Get in touch should you wish to contribute content to RankTank.

A step-by-step guide to building a free automated backlink checker and monitor, using nothing but Google Sheets formulas & the official Google Analytics add-on.

This is how your finished tool will look and work:

Just want the finished tool?

Ugh, fine 🙂 Don’t say I never tried to teach you anything!

Get this SEO tool free now!
Get the free SEO tool!
Click below to open Google Sheets and “Make a Copy”!
Open in Google Sheets

There’s tons of great tools out there to check backlinks, monitor backlinks and discover new links to your site. I use a ton of them: Majestic, AHREFS,SEMrush, Linkody and more. They all work great when I’m working with tons of clients and need to make sure I’m not missing anything. However, there’s a free method to find new backlinks that’s free and right at your fingertips: Google Analytics referrer data.

Using this thinking, let’s say that any interesting link to your site will send at least one visitor by way of referral. If you get a link from somewhere and it doesn’t send anyone to your site, what good is that link? Does that linking site even have any traffic?

Good thing Google Analytics shows us a ton of info on referral traffic. If a site sends us at least one visitor, Google Analytics can tell us everything about it: referring domain, full referring URL, landing page on our site, geo data, and way more. We can even say that the date the first referral visitor came to our site is the date we first got the link. Sounds like the guts of a link monitor.

Digging into Google Analytics referral data to find links to your site

If you’ve never used Google Analytics referral data to check for links to your site, it’s really quite easy and quite cool. Basically, we’re going into our GA referral traffic report, adding Full Referrer as the secondary dimension, and filtering out social and SPAM. Then we get something that looks like this:

Need a refresher on how to find backlinks in Google Analytics Referral data?

Step 1:

In Google Analytics, on the left menu, go to Acquisition -> All Traffic -> Referrals

Step 2:

Add a secondary dimension for Full Referrer by clicking the Secondary Dimension button, typing in Full Referrer and selecting it in the list.

Step 3:

Exclude referrers like link shorteners, social media, spam, etc. Click on Advanced. Then, for each referrer, do Exclude -> Full Referrer -> Containing -> thing-to-filter

Step 4:

Look at all those links we have! Full referrer is the linking URL. If you want to see where the links are pointing, change Secondary Dimension to Landing Page like we did in step 2.

How to Get Data from the Google Analytics API in Your Spreadsheet

If you’ve ever wondered how to extract from Google Analytics into a spreadsheet, the answer is via the API. Google Analytics has an API that’s totally free an accessible by anyone with an Analytics account. You can use the API to access literally any and all data that you can through the web interface.

Also, using the API avoids sample sizes. No more of this annoying nonsense:

You might be saying “Use the API? That sounds way too hard!” If you are, shame on you. The whole point of this site is to learn and try new things 🙂 Using an API isn’t that hard, but Google has gone and made using the Analytics API extremely freaking easy. In Google Sheets, at least!

Enter the Free Google Analytics Spreadsheet Add-on

\

Google actually built a completely free plugin for Google Sheets that does all of the hard work for you. It couldn’t be easier to use. If you’ve never tried it out before, I think you’re really going to get a lot of use out of it. It takes care of all the nitty-gritty API stuff with a convenient Sheets-based interface. Further, it allows you to schedule the report sheet to run automatically. No fuss, no hassle.

Ok cool. Let’s make a backlink monitor!

Building the Backlinks Checker / Monitor, Step-by-step

Step 1:

Set up and format your Google Sheet

Create a new sheet with four tabs, and format it like this:

Format your sheet like this:
Open a copy of this in Google Sheets
Step 2:

Grab the official Google Analytics add-on

Go the the main menu and click Add-ons -> Get Add-ons.

Search for Google Analytics, and add it to your sheet.

Important!

Google Sheets is going to ask show a Request for Permission pop-up. It’s very important that you make sure you’re logged-in to a Google Account that has access to the Google Analytics data you want to use! Then, click “Allow”.

Step 3:

Create a new Google Analytics report, and connect it.

You can now access Google Analytics under the Add-ons menu!

Go to Add-ons -> Google Analytics -> Create new report. You’re going to see a nice friendly user interface pop in on the right side of the screen. Name your report ReferralData, and select the Property (Website) you’re going to track backlinks for. We want all the data we can get, so you should keep the View as All Web Site Data unless you have a good reason not to.

(We’ll add in formulas later to filter out bad referrers.)

Step 4:

Set Metrics to Sessions, and Dimensions to Full Referrer, Date & Landing Page

We’re going to set set Metrics to Sessions, and Dimensions to Full Referrer, Date and Landing Page. Just start typing in the box, and a list will pop up. Full Referrer is the URL that contains the link, and Landing Page is the page on your site that got linked!

Now, click Create Report and let’s watch the magic happen. You’re going to see a new sheet tab created, called Report Configuration, which contains some of the info you put in. It’s almost ready.

Step 5:

Set up timeframe to check and filter for just Referral sessions

Let’s check everything from the past three years. We’ll enter some formulas so the dates will always be updated, even after we set it to run automatically on a schedule.

Make sure you’re in the report tab, and enter the following formulas:

View the Report tab formulas
Formula for: B5What it does
=(today()-(365*3))
This code uses the Sheets built-in function today() to get todays date whenever the Sheet is opened or automatically run, and the subtracts 3 years from that date using (365*3).
Formula for: B6What it does
=today()
This code uses the Sheets built-in function today() to get todays date whenever the Sheet is opened or automatically run.

Then, set cell B10 to ga:date and set cell B11 to ga:ChannelGrouping==Referral so we filter for only Referral traffic sorted by date. Delete anything in cell B7. Set cell B15 to 100000 (if it’s blank, Google uses a much smaller default).

Got it so far? Sweet! Time to run this!

Go to Add-ons -> Google Analytics -> Run reports.

Step 6:

The Data tab – Parse the data from Google Analytics

You should see a ReferralData tab automatically created in your sheet. Don’t change anything on this tab, ever. This is where Google’s automatic API stuff is. Let’s use our Data tab to parse it!

View the Data tab formulas
Formula for: A2What it does
=query(unique(ReferralData!A$16:A),”SELECT Col1 WHERE NOT Col1 CONTAINS ‘localhost:’ AND Col1 MATCHES ‘^(.*)\.(.*)$’ AND NOT Col1 MATCHES ‘^([0-9].*)\.([0-9].*)\.([0-9].*)\.([0-9].*)$’ AND NOT Col1 MATCHES ‘^(google\.).*’”)
This is pulling out unique referring domains from the Google Analytics ReferralData tab, and at the same time using some REGEX to filter out irrelevant junk like IP addresses and localhost.
Formula for: B2What it does
=arrayformula(if(A2:A=””,””,iferror(regexextract(“:”&A$2:A, “:*\.*([^/]+)”))))
This uses REGEX to pull out the linking domain from the full referrer URL.
Formula for: C2What it does
=arrayformula(iferror(VLOOKUP(A2:A,ReferralData!A:B,2,0)))
This finds the first date that a visitor came through the referral URL. We’ll use this as our ‘discovery date’.
Formula for: D2What it does
=ArrayFormula(iferror(“https://www.ranktank.org” & VLOOKUP(A2:A,ReferralData!A:C,3,0)))
{{description}}
Formula for: E2What it does
=arrayformula(iferror(if(A2:A=””,””, if(vlookup(A2:A,’Confirmed Links to My Site’!A7:A,1,FALSE)=””,””,”YES”))))
This formula checks to see if you’ve put the link into the ‘Confirmed Links to My Site’ tab already. If you have, it won’t show up in the ‘New Link Monitor’ tab.
Formula for: F2What it does
=arrayformula(iferror(if(A2:A=””,””, if(vlookup(A2:A,Ignored!A2:A,1,FALSE)=””,”NO”,”YES”))))
This formula checks to see if you’ve put this linking URL into the ‘Ignored’ tab.
Formula for: G2What it does
=arrayformula(iferror(if(B2:B=””,””, if(vlookup(B2:B,Ignored!B2:B,1,FALSE)=””,””,”YES”))))
This formula checks to see if you’ve put this linking domain into the ‘Ignored’ tab.

At this point, I like to hide the ReferralData, Data, and Report Configuration tabs because they’re just automatic from here on out. Not going to need to touch them again 🙂

Step 7:

The New Link Monitor tab

This one’s easy! We’re going to query the data tab and Confirmed Links tab to find new referring URLs that we’re not tracking or ignoring.

View the New Link Monitor tab formulas
Formula for: A2What it does
=query(Data!A2:G,”SELECT A,B,C,D WHERE E !=’YES’ AND F !=’YES’ AND G != ‘YES’ ORDER BY C DESC”)
This single Query formula checks the data tab for links that aren’t already tracked or ignored.
Step 8:

The Confirmed Links to My Site Tab

Let’s fill in some formulas for the top section, to let us know when the last run was and how many links have been found.

View the Confirmed Links to My Site tab formulas
Formula for: C2What it does
=ReferralData!B2
Gets the date and time of the last run from the ReferralData tab.
Formula for: C3What it does
=countunique(‘New Link Monitor’!A2:A)
Gets the number of new referring URLs found.
Formula for: C4What it does
=countunique(‘New Link Monitor’!B2:B)
Gets the number of unique referring domains found amongst new referring URLs
Step 9:

Set this thing to run automatically!

This is definitely the coolest part. Not only are we finding new links for free with Google Analytics integration, but we can set the sheet to run automatically! That way, every time we open it, it’s got a bunch of new links for us to go through! It’s really easy – check this out:

In the main menu, go to Add-ons -> Google Analytics -> Schedule reports

Check “Enable reports to run automatically”, then choose your schedule. I like to do daily, because I check this every day.

That’s it! It’s now up to you to check it every day, and keep on top of links!

Let me know how this works out for you. If you have any questions, comments or additions, let me know in the comments!

Related posts

SERP Proxy API by SEO Bot

How To Scrape SERP Data At Scale With Proxies

Combining Google Analytics And Ranking Data For SEO Audits

29 comments

diş eti kanaması tedavisi 25 August 2020 - 5:47 am
Very nice article. Thanks for sharing.
Naina Singh 15 June 2020 - 12:37 pm
I truly love this article. This is what I really looking forward to. I'll definitely try this for my website. Keep sharing tips like this more in the future.
Tolga 25 December 2018 - 6:07 pm
I am constantly getting this error message: 1 report failed due to errors ReferralData: We're sorry, a server error occurred. Please wait a bit and try again. Can you please help me fixing this?
Tanmoy 17 July 2018 - 10:01 am
Now I can check my blog backlink easily and quickly. Thanks for this blog.
kumar sree 28 June 2018 - 12:41 pm
Hi. Thanks for such valuable information. It is showing error. Unable to proceed. Please guide me.
Maxime 2 May 2017 - 7:06 pm
Hi, What's the formula to put in confirmed links to my site?
Josh 22 May 2017 - 6:11 am
Same question...seems like there are minimal formulas for the Confirmed links to my site tab and no directions at all for the "ignored" sheet....
Krystian 21 March 2017 - 9:30 am
Does it also track internal links? Like abc.de/abc links to abc.de/xyz If not do you know any quick solution?
Virgil 13 March 2017 - 3:25 pm
Hi, I can't get it to work. can you please help me out here...getting still same error
Haris 31 March 2017 - 11:39 am
hey virgil did you try this fix I put in pastebin, so curly bracket issue will make sense as you will see what I mean - https://pastebin.com/VinfbKDr
Haris 1 March 2017 - 2:07 pm
If any folks are still looking for the fix, issue is curly double quotes in the formulas posted, so all you need to do is replace ” with " (shift+2) if UK keyboard, then replace “ with " and finally single quotes ‘ ’ with ' - simply use notepad or paste formulas in a blank sheet and use CTRL + H and replace and viola the backlink checker works like a treat, enjoy the value provided by jaco and ranktank crew.
Jaco 2 March 2017 - 8:25 am
Thanks for sorting this Haris! :D
Chris 15 December 2016 - 3:12 pm
It seems everyone is getting the same error. Will you please reply with a fix so the "world" will know. "ScriptError: Invalid argument: timeZone. Should be of type: String"
Jaco 28 December 2016 - 8:17 pm
Hi Chris, we'll release a fix for you shortly. Please join our newsletter to receive notice of fixes and new releases.
Thierry 1 December 2016 - 3:53 pm
Scripterror: invalid arguments: timezone. should be of type: String
Jaco 1 December 2016 - 5:23 pm
Hi Thierry, please tweet or DM me @jaco_van_wyk a link to your sheet and I will check this.
Li'el Morris 5 December 2016 - 8:36 pm
I'm also having a problem with this same error :( Would love to get this figured out! Can you email me at liel@hanahlife.com?
Steve 12 March 2018 - 12:20 pm
The solution is simple - the date is set for US time zone (month/day and not the day/month). Just go to File->spreadsheet setting and just change the localization to your own local time.
Jaco 12 March 2018 - 1:18 pm
Hi Steve, this makes sense, thanks for sharing the solution.
Ben 22 January 2016 - 2:29 am
Hey Sean, I can't get this thing to work, all the formulas I am adding keep returning #ERROR! in the cell. How can I fix? Cheers
Ben 19 January 2016 - 1:43 am
Wow mate this is fantastic!! How could I tweak this to find unlinked brand mentions?
Barrie 27 November 2015 - 3:27 pm
I get the following error whenever I am done filling in the configuration options and press 'Run Report': ScriptError: Invalid argument: timeZone. Should be of type: String. Do you know what is causing this error?
Catalin 20 October 2015 - 8:26 am
For me didnt work... i get error when i run google reports : scripterror: invalid argument: timezone. Should be of type: String And when i try to copy formula to A2 -> =query(unique(ReferralData!A$16:A),”SELECT Col1 WHERE NOT Col1 CONTAINS ‘localhost:’ AND Col1 MATCHES ‘^(.*)\.(.*)$’ AND NOT Col1 MATCHES ‘^([0-9].*)\.([0-9].*)\.([0-9].*)\.([0-9].*)$’ AND NOT Col1 MATCHES ‘^(google\.).*'”) I get error: It looks like your formula is missing one or more close parentheses. If you don't want to enter a formula, begin your text with an apostrophe ('). I think need ' here : ”SELECT Col1 ... Can you give us a copy of working doc ?
Lizzi 27 February 2017 - 10:56 am
Try replacing the 'curly' characters "" and '' if you have copied and pasted. Mine works now
Jaco 2 March 2017 - 8:26 am
Great thanks Lizzi :)
Michael 19 October 2015 - 6:19 pm
Hi Sean, I have tried to test this out and already on the process of running the report but I couldn't able to process and gather data as I'm getting an error message. Error Message is "Scripterror: invalid arguments: timezone. should be of type: String"
Marco 9 October 2015 - 3:06 pm
Is this one you can make and then we can just copy over and add our API key into?
Sean Malseed 9 October 2015 - 3:15 pm
Hey Marco, Google should ask you to verify when you first use the sheet. No API required!
Michael 19 October 2015 - 5:49 pm
Hi Marco, I have tried to test this out and already on the process of running the report but I couldn't able to process and gather data as I'm getting an error message > "Scripterror: invalid arguments: timezone. should be of type: String". What do you think is the reason for this?
Add Comment

RankTank uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Read More