Project: Getting website performance score automatically

The problem

I have a client who runs a digital marketing agency and works with over 45 doctors.

Each doctor has their own website, and the agency is responsible for keeping these websites SEO-friendly and running smoothly.

Part of their job is to send each doctor a monthly performance report.

This is something every agency is expected to do when managing websites on a regular (retainer) basis.

These reports usually include two key things:

1. Page speed
2. SEO Score

Right now, the client creates these reports manually for each of the 45+ websites.

But there are two problems with this manual work.

1) It is slow and boring as usual

To get the data, the client uses the Google PageSpeed Insights tool, and it takes around 30 seconds to analyze each site.

That means it takes over half an hour just to collect the reports for all 45 sites.

I am not even counting the time to organize and send them to each client.

If you watch the above video, you’ll see how much of that time is just waiting for the tool to finish the analysis.

It's not just slow. It's boring too.

Anyway, another problem with doing this by hand is…

2) It doesn’t scale

What if the agency starts working with 100+ doctors?

Doing this manually just won’t work because it takes way too much time and effort.

So, the client had asked for a better solution and that is where automation comes in.

Starting from this lesson, we’re going to build a simple automation that solves this problem automatically and permanently.

Let's begin :D

We will divide building the automation into four steps:

1. Planning the automation
2. Preparing the data for automation
3. Generating API key for the Google PageSpeed Insights tool
4. Implementing the automation

Step 1: Planning the automation

Our goal for this automation is simple.

We have a list of 20 medical websites, and we need to fetch the Page speed and SEO score for each website.

To make this easy to automate, we’ll first set up a spreadsheet to hold the data.

As you can see, the spreadsheet will have the following columns:

• Website URL – the link to the doctor’s website
• Page Speed – how fast the website loads (initially empty)
• SEO Score – how well the website is optimized for search engines (initially empty)

Once our spreadsheet is ready, we’ll use the Google PageSpeed Insights API to automatically check each website and fill in the Page Speed and SEO Score inside their respective columns:

That's all we need to do.

Step 2: Prepare the data for automation

1. Download the CSV file that contains website URLs. You can also access the spreadsheet here.
2. Create a new spreadsheet inside Google Sheets
3. Import the downloaded CSV file into the newly created spreadsheet

After the import is complete, your sheet should look like the screenshot below:

All the website URLs are real, and were collected using ChatGPT.

Feel free to use them or collect a new set of website URLs on your own.

Next…

Step 3: Generating API key for the PageSpeed Insights tool

You must follow a process for creating an API key for any Google service.

The process remains the same for creating an API key for any Google service. You can also use the same API key across multiple services.

Now, the problem is, the process itself is not straight-forward.

So, I have written an in-depth article to make it easy for you.

The good thing, it includes how to generate an API key for the PageSpeed Insights.

So, go through it, create credentials for the PageSpeed Insights API and come back here.

In the next lesson, we will learn how to use one of the HTTP modules to send requests to the PageSpeed Insights API and fetch the performance score of websites at hand.