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:
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:
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.
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
Set up and format your Google Sheet
Create a new sheet with four tabs, and format it like this:
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”.
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.)
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.
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:
Got it so far? Sweet! Time to run this!
Go to Add-ons -> Google Analytics -> Run reports.
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!
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 🙂
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.
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.
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!
29 comments
Very nice article. Thanks for sharing.
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.
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?
Now I can check my blog backlink easily and quickly. Thanks for this blog.
Hi. Thanks for such valuable information. It is showing error. Unable to proceed. Please guide me.
Hi, What’s the formula to put in confirmed links to my site?
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….
Does it also track internal links? Like abc.de/abc links to abc.de/xyz
If not do you know any quick solution?
Hi,
I can’t get it to work. can you please help me out here…getting still same error
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
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.
Thanks for sorting this Haris! 😀
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”
Hi Chris, we’ll release a fix for you shortly. Please join our newsletter to receive notice of fixes and new releases.
Scripterror: invalid arguments: timezone. should be of type: String
Hi Thierry, please tweet or DM me @jaco_van_wyk a link to your sheet and I will check this.
I’m also having a problem with this same error 🙁 Would love to get this figured out! Can you email me at [email protected]?
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.
Hi Steve, this makes sense, thanks for sharing the solution.
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
Wow mate this is fantastic!!
How could I tweak this to find unlinked brand mentions?
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?
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 ?
Try replacing the ‘curly’ characters “” and ” if you have copied and pasted. Mine works now
Great thanks Lizzi 🙂
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”
Is this one you can make and then we can just copy over and add our API key into?
Hey Marco, Google should ask you to verify when you first use the sheet. No API required!
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?