Update May 2022:
Sadly, the Alexa service, on which this post is based, has been discontinued, so the techniques shown below no longer work. However, the Apps Script to save the data on a daily basis is still a valid technique, and so I leave this post up for that reason.
Original Post:
This tutorial will show you how to create an Alexa Rank tracker in Google Sheets, using a couple of formulas and a few lines of code.
Alexa Rank is a third-party tool that measures how popular a website is. The lower your ranking, the higher your site traffic is.
For example, Google is ranked #1 and Facebook and Wikipedia also have very low rankings (and giant traffic). The full tool has a host of useful features, but I’ll show you how you can get a website’s Alexa Rank number and build an archive in your Google Sheet.
Here’s my website Alexa Rank over time:
I’ve been running this Sheet since December 2016, about 1 year after my website was created. In that period, my Alexa global ranking has dropped from 320,000 to 30,000, and my Alexa US ranking has dropped from 160,000 to 15,000.
Alexa Rank Tracker Import Formulas
The first step is to setup a small settings Sheet with the formulas to import the Alexa Rank tracking data.
There are two columns: one for the global ranking figure and one for the US ranking figure.
Cells B2 and C2 are the same, containing the URL of the website in the Alexa Rank tracker: https://www.alexa.com/siteinfo/benlcollins.com
Import Formulas
In cell B3, enter this formula to import the global rank:
=VALUE(REGEXEXTRACT(JOIN("|",ARRAY_CONSTRAIN(IMPORTDATA(B2),30,1)),"global.(.+)\|us"))
In cell C3, enter this formula to import the US rank:
=VALUE(REGEXEXTRACT(JOIN("|",ARRAY_CONSTRAIN(IMPORTDATA(C2),30,1)),"us:.(.+)\|\}\|rating"))
These formulas work by importing the content of the Alexa site info for the given website, and them parsing it with a Google Sheets REGEX formula to extract the relevant numbers.
For more information on these formulas, and an alternative Alexa formula, have a look at this post: How to import social media statistics into Google Sheets: The Import Cookbook
Error Handling
On row 4, in cells B4 and C4 are two manually typed values for the ranking, which are just used as backup values in case the import formula fails (which has happened only a handful of times in the past few years).
Periodically, I’ll paste in the latest formula values as text on row 4, to keep the backup as current as possible.
On row 5, use the IFERROR function in Google Sheets to catch errors and use the backup values instead:
=IFERROR(B3,B4)
and
=IFERROR(C3,C4)
That’s it for the settings Sheet.
Archive Sheet
Add another blank Sheet to your Alexa Rank tracker Sheet, with 3 columns: date, global rank and US rank.
Call it alexa_rank.
Apps Script Code To Save Alexa Rank Data
Open your script editor: Tools > Script editor
And add the following code:
function saveAlexaData() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const dataSheet = ss.getSheetByName('alexa_rank'); const settingsSheet = ss.getSheetByName('settings'); // get the url, follower count and date from the first three cells const d = new Date(); const global_count = settingsSheet.getRange(5,2).getValue(); const us_count = settingsSheet.getRange(5,3).getValue(); // append new ranking data to Sheet dataSheet.appendRow([d,global_count,us_count]); // format date string cell dataSheet.getRange(dataSheet.getLastRow(),1).setNumberFormat('MMM-YY'); }
Save and Run this script.
(You’ll be prompted to grant the script permission to access your Sheet files the first time you run it.)
It adds a row of data with the date and ranking data to your Sheet.
Run again if you want to see it add new data (but you’ll want to delete this row to avoid duplication).
Trigger To Run Code Automatically
Now let’s set it up to run on a daily basis.
Under the Triggers option in the left hand sidebar menu, create a new trigger.
Set it to time-driven and run it once a day.
The formulas reflect the value of the Alexa Rank at the current time. The script saves a copy of those ranking values at that point in time. Once the script has been running for a while, you’ll have an archive of historic data.
Chart To Display Ranking Trend
The final step is to highlight your table of ranking data and Insert > Chart
Format it as you wish.
VoilĂ ! You can now see your Alexa Rank over time.
Further Reading
Using Google Sheets as a basic web scraper
How to import social media statistics into Google Sheets: The Import Cookbook
Hi Ben
Thanks for the amazing post. I want to change the country code but not fetching data. How can I add TR instead of US?
Hi Teknojest,
Great question. Unfortunately it looks like the Global and US rankings are shown on the public page, but other countries are hidden behind the drop-down menu under a paid account, which renders them unavailable to the IMPORT formula.
I’m based in the US though, so it could be a different country that shows up elsewhere. You could try swapping out the “us” for “tr” in the formulas, but I’m doubtful it will work.
Cheers,
Ben
Great resource! I really enjoyed reading and implementing it step by step, while trying to customize it based on my needs.
For example, I changed the date format (easy one)
Thank you again, Ben. You rock at this.
I am actually wondering where the number 30 came from in the formula?
Maybe by knowing this, I can get the ranks for another country as well.
Hi Ben
I follow your blogs and tips very closely as they are always very insightful and educational.
I see your alexa ranking formula for google sheets works like a bomb.
I was wondering if you could assist with the same effect from simlarweb.com. I have searched high & low and just hit a brick wall so I am wanting to know if you can assist in extracting into sheets their various ranking and top country of traffic data.
I do note that everywhere I have searched, they same query without solid results arrive so I am hopeful your smartness will be of great help to many people. Thanks in advance
The formula I used but does not work is probably to simple:
tried: =IMPORTXML(G4,”/html/body/div[1]/main/div/div/section[2]/div/ul/li[1]/div[1]/div[2]”)
Also tried: =IMPORTXML(G4,”//span[@class=’websiteRanks-item js-globalRank’]”)
Hi Ben,
I was wondering if it’s possible to setup a simple website/personal portal or google sheet type tracker that can act as a best 3 sites of each category daily checker.
So for example I want to see or search for top 3 travel companies or travel services in my state or city and this can be expanded to restaurants, tech websites, gadgets , weather and so forth so like a master tracker of best 3’s in a category for city or state scope and one can add more categories as needed?
Many thanks.
Regards,
Adam