I have to begin with a disclaimer – the process described here does require access to the Ahrefs API – however, I’m assuming that Ahrefs will be plugged in to various calculators and sheets for most agencies. However, while the API will probably be used extensively elsewhere, there’s a distinct possibility that your agency will not be using the API in reporting. The reason for this piece is not because it’s super difficult to do – if I can do it, there’s a fair chance you can to – but to encourage you to take the plunge. There’ll also be a pre-filled sheet you can have to save you the bother if you haven’t the time or energy. There may also be a test (there won’t be a test).
What am I talking about?
First of all, I’ll explain a little about what I’m talking about; the Ahrefs tool provides a measurement called Domain Rating (DR) which is a proprietary measurement indicative of the strength of a site’s backlink profile and, therefore, is a great indication of how your work on a site is progressing. Ahrefs describes the measurement thusly:
Domain Rating (DR) shows the relative “backlink popularity” of the target website compared to all other websites in our database on a 100-point logarithmic scale (higher = stronger). We calculate DR based on the number of websites linking to the domain’s URLs and their backlink profile strength.
Unhappily for those of us who enjoy a good line graph (and who among us can resist a good line graph, especially a lovely one that wiggles in the right direction?), this is a dynamic number that refreshes with each log-in and offers no historical performance chart (unless it does, which I’ll no doubt find out when I submit this for review), so what would be nicer than a way to easily track this performance over time and import it in to the Data Studio reports our clients increasingly require? (if you need a some more information on Data Studio or various other Google Sheets adventures, I’ve written a few you can find here)
That’s, in essence, what I’m talking about – a way to track our site’s progress month on month, automatically and with no work required beyond the initial set up and a monthly macro execution. Better still – you can also track your competition in the same chart. To do this, we’re going to set up a few concatenate formulae to automatically construct API calls, strip the XML from the information we want, split the result (the Domain Rating API call also pulls in your Ahrefs Rank) and then build a macro to record these results over time.
What you’ll need
There’re a few bits and pieces needed to set this all up – firstly you’ll need an Ahrefs account which comes with an API token (I’d imagine you’ll already have this if you’re looking for this kind of information, but just in case, there’s some information here), you’ll need access to Google Sheets and also to Data Studio.
The API call
I use Google Sheets almost exclusively these days, simply because the connector is nice and easy to use between Sheets and Data Studio. So the first step is to open up a new Sheet, name it something you’re likely to remember and format yourself six cells (a completely unnecessary step, but I can’t stand a plain spreadsheet).
- Target URL: – This is where you’ll enter the URLs you want to track. Your API token will have a limit to the number of calls, but it’s pretty big even for the basic package, so you can track a few of your competitors as well as your own.
- Concat 1: – This is where you’ll combine part of the API, your token and URL.
- Concat 2: – This is where you’ll combine the result of the previous concatenation with the second half of the API call.
- Import XML Output: – This (if you follow the formulae for the command) will output the DR and Rank for the URLs.
- Ahrefs Domain Rating: – Once you split the two using a formula, this will show the DR and…
- Ahrefs Top Domain Score: – This will show your Ahrefs rank.
Target URL
Simple as you like – and the only thing you’ll need to change manually if you decide to alter your competitor list. Simply enter the full URLs for the sites you want to track.
Concat 1
If you’ve used a concatenate before (a lot of people use concatenates for keyword combinations etc.) then you know it’s a fairly simple formula that joins one string to another. In this case, we’ve split the API call just before the required URL, we’ve populated our API token and we’re going to join it to our URL. It will look something like this:
=CONCAT(“http://apiv2.ahrefs.com?token=YOUR-TOKEN-HERE&target=”,A2)
Concat 2
The second half of our API call is then added using another simple concatenate formula – looking something like this:
=CONCAT(B2,”&limit=1000&output=xml&from=domain_rating&mode=exact”)
Import XML Output
It took me a minute of faffing about to sort this – I’ve tended to use IMPORTDATA for API calls in the past, but that drags in the entire XML, so instead you can use IMPORTXML with a little addition that removes the stuff you don’t need. So, in the following code, we’re calling the completed API URL and the “/*” strips it of the unnecessary bits and bobs. Your formula will therefore be something like:
=IMPORTXML(C2, “/*”)
Which will give you an output looking like this:
On the left is your DR, on the right is your Ahrefs rank, which we need to split up.
Splitting the results
Thankfully the results are separated by a space, so there’s a commonality we can use to separate all results in a column in to their component parts. The formula for this would be something like:
=SPLIT(D2, ” “)
This will split the two figures (in this case in D2) in to two columns – the one in to which this formula is entered and the one immediately to its right. I then tend to hide the middle columns for the sake of neatness; you may do as you will (depending on your level of neuroticism).
Recording the score
You’ll need to add a tab to your sheet for this – leave yourself as many cells as you need, but delete the rest. I’ve essentially used the first to name the series using an =(‘NAME-OF-SHEET’!CELL LOCATION) formula:
=(‘Ahrefs API call’!A4)
The simple formula will call the target URL, while a macro will do the work of summoning the score.
The macro
Creating a macro to log your results is done by clicking ‘Tools’, then selecting ‘Script Editor’. This will open a new window.
I’ll go through line by line, but the purpose of this macro is to check the score in the API call sheet, then create a new column in to which is pasted the value when the macro runs.
Line 1
function domainRankingScores() {
This names the function so you can call upon it in the spreadsheet – I tend to use camel case here for ease of reading.
Line 2
var ss = SpreadsheetApp.getActiveSpreadsheet();
This designates the spreadsheet the macro needs to hold – in this case the ‘active spreadsheet’ – the one in which the macro is being run.
Line 3
var sourceRange = ss.getSheetByName(“Ahrefs API call”).getRange(“E2:E11”);
This names the location of the data – so the name inside the (“”) is that of the sheet you’re making your API call in and the range in the second (“”) is that where the score is called.
Line 4
var targetSheet = ss.getSheetByName(“Recording Output DR”);
This specifies where you want the data stored – with the (“”) containing the name of your second tab.
Line 5
targetSheet.insertColumnAfter(targetSheet.getLastColumn());
This line is where you want room for the data added to the tab – in this case we’re inserting a column after the last one present.
Line 6
var targetRange = targetSheet.getRange(1, targetSheet.getLastColumn()+1);
While this line specifies where you want the data added – here we’re specifying the new range in the new column.
Line 7
sourceRange.copyTo(targetRange, {contentsOnly:true});};
This line simply ensures you store the value of the score cell and not the formula that produces it.
The full macro for use:
function domainRankingScores() { /**this names the function so you can call upon it in the spreadsheet **/
var ss = SpreadsheetApp.getActiveSpreadsheet(); /**this designates the spreadsheet the macro needs to hold **/
var sourceRange = ss.getSheetByName(“Ahrefs API call”).getRange(“E2:E11”); /**this names the location of the data **/
var targetSheet = ss.getSheetByName(“Recording Output DR”); /**this specifies where you want the data stored **/
targetSheet.insertColumnAfter(targetSheet.getLastColumn()); /**and where you want room for the data added **/
var targetRange = targetSheet.getRange(1, targetSheet.getLastColumn()+1); /**and where you want to add it **/
sourceRange.copyTo(targetRange, {contentsOnly:true});/**this ensures you store the value of the cell and not the formula **/
};
Once you’ve entered this macro, make sure you save it. Then, when you run it, you’ll see something like this:
More accurately, you’ll see it start the data from B1 – because I’d forgotten about the peculiar formatting problem I always have. To bring it in to alignment, what I actually had to do was add a couple of bits and pieces to my original table to drop this down a couple of rows. As such, I added a date finder and blank cell to the first sheet so that now it looks like this:
I added the date value simply using =TODAY() and then asked it to pull in a month value above the two scores. This will, in fairness, make the eventual chart easier to put together. I’d go back and edit the beginning, but I think it’s important to know that even when you’ve done this kind of thing a fair few times, you’ll still muck it up in one way or another.
Making the data readable
An irritating step, but once done, it’s done. Data Studio likes data to be imported just so, so to get our lovely line graph, we need to reformat it in a way Data Studio will be able to comprehend. Essentially you’re just importing the values from your output sheet – just in a slightly different order. Your new table will look like this:
As you can see, we’re pulling the date into a column, then providing the scores per URL. To pull in some data for the Data Studio chart, I’ve fudged an example table which looks like this:
Creating your data studio report
Once in your data studio report, you can connect your new sheet:
Once you’ve connected, you’ll need to specify your columns and nominate your date column as a dimension.
You can then select ‘Finished’ and add this to your Data Studio report, selecting line chart as your display. The fudged example data looks like this:
Provided you’ve set up the tables correctly, this will automatically update each time the macro is executed and there you’ll have a lovely wiggly line chart pulling in yours, or your clients and however many competitors, Domain Ranking over time!
Starting from scratch today, it took me about six hours to set up the various reports, take the screenshots and write the article (including the bit where I messed up and had to work out what I’d done wrong), so I’m going to say this should be possible to set up on a quiet afternoon (especially as you get to learn from my mistakes).
If there’s anything you think I’ve missed or need more information on – let me know in the comments!
Oh, and here’s the sheet if you need it.
