BLOG | categories | page speed

Benchmark Websites: Track Competitors Performance Using Google Sheets

By Steve Founder of PageDart

This post is a hands-on guide on how to use free tools to track and watch your competitor's websites.

We have discussed in Website Performance Benchmarks the importance of tracking your competitors.

Let's look at how we can automate the process.

And did we mention all for free!

We will use the tools Google Sheets and the Google PageSpeed Insights API as our data source.

Don't worry if you aren't a software developer.

This post is a step-by-step guide and is all you need to set up daily web performance monitoring.

Tracking Top 10 US Websites

To give you an idea of what this will look like, I have set up a sheet that is tracking the top 10 US websites.

The top 10 US Sites are:

Every night the Google Sheet collects Lighthouse performance statistics for these sites.

Lighthouse is a free tool from Google that allows us to assess the performance of a website.

The Lighthouse performance stat is a number from 0 - 100. With 100 being the best (and hard to achieve) and 0 being the slowest and worst user experience.

As we can see from the chart below Google has a performance of 100, with Netflix not far behind. I suppose this is not surprising considering the Google homepage is only a search box.

The least performant sites are Youtube and Reddit. I can understand why Youtube would be slow.

After all, Youtube is a video platform and videos are a website performance challenge. So a score above 50 is still very respectable for a video site.

Reddit drops below 50 on occasion, I will dedicate a future post to running an audit on the site. It would be interesting to see what is causing the delay.

Let’s create your version of this chart and track your competitors.

Copy the Google Sheet

To get started, the first thing we need to do is make a copy of the “Benchmark Websites” Google Sheet I made.

Once you have the sheet open click “File” then “Make a copy…".

This will copy the sheet into your Google Drive account.

There are a few steps we need to cover next to make the sheet work for you, these are:

Get an API Key for PageSpeed Insights
Set the “PSI_API_KEY” script property
Set the URL's we want to track
Set a trigger to run the performance check daily

1. Get an API Key for PageSpeed Insights

The first step we need to perform is to get a PageSpeed Insights API key.

This API key will allow the Google Sheet to call the PageSpeed Insights API, fetching the data we need.

I find it easier to get a key from the PageSpeed Insights API documentation.

Once you have visited the page click the “GET A KEY” button.

This opens a popup that will ask you to select a project.

If you have not created a project yet then create a project called “PSI Monitor” and then click “Next”.

If you already have a project then select it from the dropdown and click “Next”.

You are then presented with your unique API key.

Copy this key to your clipboard so that you can use it in the next step.

2. Set the `PSI_API_KEY` script property

Now that we have obtained an API Key we need to set the property on the Google Sheet.

To do that, you need to open the script editor.

On your copy of the Google Sheet select “Tools” then “Script Editor”.

This will open up a new browser tab with a script.

Don’t worry, you don’t need to understand the code, we only need to change a few things. I will walk you through the steps you need to perform.

First, we need to set a script property of “PSI_API_KEY”, so that the sheet can get the PageSpeed Insights data.

To set this up on the “PSI Monitor” script page, click on “File” and then choose “Project properties”.

This will open a pop-up click on the “Script properties” tab.

Click the “+ add row” button and add a new property called “PSI_API_KEY” and then copy in the API key (from step 1) as the value.

Remember this is a unique key to you so don’t share it.

When you have finished click save.

3. Set the URL's we want to track

So now we can call the PageSpeed Insights API from our script which is great. Yet, the script is currently tracking the top 10 US Websites.

Chances are these are not your competitors!

So to change this we need to edit line 7 on the script:

To do this replace the URLs with the URLs you would like to track.

Remember, the last URL does not have a comma at the end.

Once you have completed these steps you are ready to test the script.

Select the “monitor” function from the drop-down.

Then run the “monitor” function by clicking the triangle play button.

It takes a while to run the script, while it is running switch back to the Google Sheet. You will see the collected statistics.

The script can take a little while to run, around 2 mins to complete for 10 URLs.

Once it is complete you will see the data at the end of the “Sheet 1”.

So that we can reset the Sheet it's a good idea to delete all the data. After all, you don't need the top 10 website data.

So go ahead, select all the rows in “Sheet 1” and delete.

“Sheet 1” will now be blank.

Now you have everything ready, we can set a trigger to run the script daily.

4. Set a trigger to run the performance check daily

Now that we have set up the script and checked that it is working, we need to set up a trigger.

The trigger runs your script daily so that you don’t have to.

Once the trigger is set up you can forget about it and come back when you need to grab a new copy of the chart.

Make sure you are back on the script page we edited earlier.

Then, to create the trigger click the “Edit” menu and then select “Current project’s triggers”.

This will open a new page on the “GSuite Developer Hub”.

Click the “+ Add Trigger” button.

Then you want to select the following settings:

Choose the function to run - “monitor”
Choose what deployment should run - “Head”
Select event source - “Time-driven”
Select type of time based trigger - “Day timer”
Select time of day - “Midnight to 1am”

If you want to receive a notification if the trigger fails then set:

Failure notification settings - “Notify me daily”

Once finished click save and the trigger is ready.

Every day the trigger will run and your spreadsheet gets updated.

Benchmark Websites Final Thoughts

This post has given you a great way to track your competitor's website performance over time.

Using the free tools from Google, Sheets and PageSpeed Insights API. You can get useful data daily that creates a useful chart.

This chart is great for presentations to your stack holders. Giving them a clear view of how your site performance compares to your nearest rivals.

For more on the value of tracking your competitors, see Website Performance Benchmarks. This post shows how to create a video that compares two websites side by side. The video is useful to demonstrate the difference between a fast and slow site.

Acknowledgments

As with most software we are standing on the shoulders of giants.

The sheet originally came from Robert Ellison.

It was later modified by Rick Viscomi.

I made modifications to use the new v5 PageSpeed Insights API. Which means we can track the performance statistic from Lighthouse.

Thanks to both of you for sharing your hard work.