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:
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!