Category: Spreadsheets

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:

In cell C3, enter this formula to import the US rank:

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:

and

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