Build Your Own Free Backlink Checker Tool With Google Sheets

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:
backlink-tool-header

Just want the finished tool?
Ugh, fine 🙂 Don’t say I never tried to teach you anything!
Get this SEO tool free now!
Step 1:
Step 2:
Get the free SEO tool!
Click the button below to open Google Sheets and make a copy!
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:
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)))
See code for details.
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!

10 Million Marketers Can't Be Wrong!

When you join the RankTank mailing list, you'll be better than 9.9 million of them. You'll also get first access to cool new tools, tutorials and other neat stuff.

SPAM? Naw. Ain't nobody got time for that.

About the author:

27 thoughts on “Build Your Own Free Backlink Checker Tool With Google Sheets

  1. 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?

    • 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….

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

  3. 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”

  4. 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?

  5. 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 ?

  6. 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”

    • 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?

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.