Harnessing ChatGPT for SEO Automation: A Beginner-Friendly Tutorial

Author:

Roxana Stingu

Last updated:

01/05/2024

In the ever-evolving world of SEO, staying ahead of the curve is crucial. If you’re like me, you’ll have found that as search algorithms become increasingly complex and competition grows fiercer, SEO resources stay the same or even diminish. As a result, we have to continually seek innovative ways to streamline our workflows and boost productivity just to stay afloat. 

We all know that automation can help us get more done without having to increase headcount or budgets. I’m sure many of you have asked yourself if you should just dive into the world of coding in order to learn how to automate certain tasks. Or, some of you might have even tried asking for some development resource to achieve this. 

If you’re still reading, it means you recognise yourself in above. This article is for you, my fellow sufferer!

While learning how to code is not necessarily a solution, there is still a beacon of hope: enter ChatGPT. 

With its remarkable ability to handle various tasks, ChatGPT can come to the rescue and, in this article, we will explore how ChatGPT can revolutionise the way you approach SEO automation, empowering you to achieve remarkable results without the need for extensive coding knowledge. 

But first, let's delve a bit deeper into the concept of automation and its significance in the field of SEO.

What is automation? 

Automation is the process of using technology and tools to streamline and simplify repetitive or time-consuming tasks. It can allow you to allocate your valuable time and energy towards more strategic initiatives, while mundane and repetitive activities are seamlessly handled by other systems. 

While not all tasks are suitable for automation, many processes can be broken down into manageable components that can be automated successfully. By identifying these components, you can save significant time and effort in the long run.

It involves understanding the task at hand, identifying pain points, and defining desired outcomes. A well-defined problem statement lays the foundation for writing effective prompts for ChatGPT and ensures that the automation process aligns with the desired goals.

Prompts act as instructions or queries provided to the model to generate desired responses. Writing effective prompts requires a balance between being specific enough to guide the model and allowing flexibility for it to generate creative and accurate responses.

How do I select a project for automation?

1. Start by listing the tasks you perform on a daily or weekly basis. These repetitive tasks can range from data analysis and report generation, to keyword research or content optimisation and pretty much anything else you can think of.

2. Split your list into tasks you have a general idea of how to automate and those that seem challenging or unfamiliar. The former may involve using existing tools, scripts, or plugins, while the latter might require more research or learning.

3. Prioritise the tasks that offer the most significant benefits when automated. Consider things like the amount of time you’ll save, likely accuracy, scalability, and potential ROI. Or simply consider if it saves your sanity. Choose a task that aligns with your goals and can deliver tangible improvements to your everyday work.

Seeing it in practice

I’ve had a think about what repetitive tasks I might want to automate and came up with a very short list – the truth is I’ve been automating as much work as possible for as long as I can remember. However, there are a few things I’d rather not have to do, even if they only happen a few times per month. 

From my list, I’ve identified one that will save me some time, and also has the potential to elevate my reporting: adding Google Updates annotations to charts in Looker Studio.

For the example that will follow, I’ve just used the following free tools and services:

• Google’s Search Status Dashboard - I will use this as my data source for annotations

• Google Sheets - I can keep the data here and blend it in Looker Studio with my Analytics data

• ChatGPT - the free version is enough as we don’t require any Plus functionality. Just please remember not to share any sensitive information since your input will be fed back into the learning algorithm

As my next step, I’ve started thinking about how this might work. I do have past automation experience and can sometimes come up with a good solution on the spot. 

But don’t worry if you’re tackling a problem where solutions don’t come easy. Here’s where you can either use ChatGPT to ask for potential solutions or you can search (via your favourite search engine) to find a solution someone else has proposed to the same problem. As ChatGPT can sometimes be a bit too creative, I prefer sticking to the classical “just Google it”. I’m a firm believer that I am never the first person to encounter a specific problem. 

Here are my thoughts on automating my proposed task:

• I can get the update data directly from Google’s update page. It’s not just trustworthy but Google will keep the list fresh anyway so I know it’s a good resource.

• If I can get that data into list form in Google Sheets then I can blend it easily in Looker studio and overlay it with GA or GSC data. 

• I know I can use the IMPORTHTML function in Google Sheets to scrape information off of web pages so all I need to find out is the format this information comes in. Using Chrome Developer Tools, I can inspect the code and see that the updates are added in a table. Each year has its own table so I have multiple tables that I need to bring into Google Sheets. 

ALT/Caption: Screenshot of Google Updates page and Chrome Developer Tool rendered HTML code showing the page is structured using tables. 

To validate my train of thought, I will just quickly run the formula in Sheets to see if the data comes in as needed:

ALT/Caption: Screenshot in Google Sheets showing the data from the first table

While this formula works, it only brings in the first table on the page (or a specific one but not all of them). If all I wanted was this year’s updates, then using this formula would be enough. Unfortunately, I want historical data in my reports so I need all the tables to be imported.  

This is the moment I can summarise my notes from above into a prompt for ChatGPT:

ALT/Caption: Screenshot of a ChatGPT prompt asking: Hello there, I want to get information from multiple tables on a webpage into Google Sheets using the ImportHTML function. How can I do that if it's multiple tables?

ALT/Caption: Screenshot of a ChatGPT response with details on how to import tables from a web page. 

Interesting - we’re definitely getting in “I need help” territory as I am not versed in AppScript. I also notice that, besides the code, I also get instructions and comments - pretty handy for me to understand the functionality of it. 

I go to Google Sheets > Extensions > AppScript and create a new project here. I will name it “Google Updates” and then replace the code here with the one provided by ChatGPT. 

Looks like ChatGPT created a special function for me that I can call in Sheets - I follow the instructions, and use the function in conjunction with the URL where I want to get the updates from: 

=importMultipleTablesFromURL("https://status.search.google.com/products/rGHU1u87FJnkP6W2GwMi/history")

But I get an error:

ALT/Caption: Screenshot of Google Sheets showing an error

I don’t even read the error message, I just copy it into ChatGPT: 

ALT/Caption: Screenshot of a ChatGPT prompt describing the error from Google Sheets 

Using this message that I don’t even understand, ChatGPT was able to translate that I need Authorisation to overwrite in Sheets. It then updates the code for me to include this. 

Upon running the new code, I do get the Review Permission dialogue:

ALT/Caption: Screenshot of Authorization Required dialogue from AppScript

I am then able to verify this script and allow it to overwrite my Sheets.

I run the code again and, surprise, I get yet another error:

ALT/Caption: Screenshot of AppScript execution log error

Rinse and repeat, I feed back this error to ChatGPT. In fact, I do this a few times with all the errors that pop up and I keep getting new code for me to try. I’m just sharing the errors and top lines of the response here: 

ALT/Caption: Screenshot of ChatGPT prompt describing an error and response

ALT/Caption: A second screenshot of ChatGPT prompt describing an error and response 

ALT/Caption: Yet another screenshot of ChatGPT prompt describing an error and response 

What I am doing here is pretty much debugging. Instead of going through the code myself and trying to figure out what the errors are and how to get them fixed, I feed them back to ChatGPT and it debugs itself. 

There was a bit of back and forth but the whole thing took under 5 minutes. I finally get this code:

ALT/Caption: Screenshot of ChatGPT code snippet 

Copy/Paste. Save. Run. I finally got a successful execution. Switch over to my Sheet and this is what I see:

ALT/Caption: Screenshot of Google Sheets data showing 3 tables

So far so good! All the tables are being imported, time to do some refinements:

• I would like to get this as one list with just one heading instead of multiple headings

• I want to make sure that new data overwrites the old data, so I don’t accidentally end up with a mishmash of old and new information

• I would like to create a new calculated field in column D that is called “Update Type” and extract the main update name

• I would also like a new field in column “E” called “End Date” that uses the data in column B - “Date” and data in column C “Duration” to calculate the last day of the update

Now I can feed all this back to ChatGPT, all at once or some at a time. I feel I need to group the requests by type so I try this first: 

ALT/Caption: Screenshot of ChatGPT prompt asking to keep the headers of just the first table and not to overwrite data as it updates

As you can see from the results below, this request was not clear enough. ChatGPT has put all the data into a single column: 

ALT/Caption: Screenshot of Google Sheets showing all table data in one column

Time to be clearer in my communication, and explain what I am seeing and what I want to get out:

ALT/Caption: Screenshot of ChatGPT prompt asking more clearly what headers to keep

ALT/Caption: Screenshot of Google Sheets showing three tables with two empty rows in between

Definitely getting closer! I now just need to remove those empty lines. Again, I will explain what I am seeing and what I want the result to be. 

ALT/Caption: Screenshot of ChatGPT prompt asking to remove the empty rows

Some refinements:

ALT/Caption: Screenshot of ChatGPT prompt asking to remove the empty rows again as one row was still left

And success:

ALT/Caption: Screenshot of Google Sheets showing all three tables listed as one

Now onto the next refinements:

ALT/Caption: Screenshot of ChatGPT prompt asking to extract the Update name from  column A

The result was not what I expected so I decided to give it some examples, covering all patterns that I could see. In the end, we got there:

ALT/Caption: Screenshot of Google Sheets showing the table and the new column with update names

Ain’t that pretty already? Let’s see if we can also get an end date for the updates since that information is not provided directly by Google. 

ALT/Caption: Screenshot of ChatGPT prompt asking to calculate the length of the update

Notice in the reply how ChatGPT knows what values there are in columns B and C? That’s only because we have mentioned them before – ChatGPT can’t actually see our Google Sheet. 

ALT/Caption: Screenshot of Google Sheets showing duration as Invalid Duration Format

Looking at the lines with Invalid Duration Format, we can quickly see they happen whenever the duration doesn’t include hours. I decided to ask ChatGPT to round up the duration to full days before calculating the End Date - this way we bypass the issue of having different types of data in that column. 

ALT/Caption: Screenshot of Google Sheets showing duration as expected

Now that the data looks good, there is just one more thing to do - put this whole thing on a schedule. 

This can be done from AppScript by using a Trigger but, since we’re using ChatGPT, we might as well ask it one last request: 

ALT/Caption: Screenshot of ChatGPT prompt asking to add a weekly trigger

And, voila, here’s the final code with a trigger included:

function importTableAndCalculateUpdateType() {

  var url = "https://status.search.google.com/products/rGHU1u87FJnkP6W2GwMi/history"; // Replace with the URL of the webpage containing the tables

  var response = UrlFetchApp.fetch(url);

  var content = response.getContentText();

  var tables = content.match(/<table[\s\S]*?<\/table>/g);

 var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var lastRow = sheet.getLastRow();

  // Clear the sheet except for the first row (header)

  if (lastRow > 1) {

    sheet.getRange("A2:E" + lastRow).clearContent();

  }

  var data = [];

  tables.forEach(function(table, index) {

    var tempData = [];

    var tableRows = table.match(/<tr[\s\S]*?<\/tr>/g);

    tableRows.forEach(function(rowHTML, rowIndex) {

      var rowData = [];

      var cells = rowHTML.match(/<t[dh][\s\S]*?<\/t[dh]>/g);

      cells.forEach(function(cellHTML) {

        var cellText = cellHTML.replace(/<[^>]+>/g, '').trim();

        rowData.push(cellText);

      });

      if (rowData.length > 0 && !(index > 0 && rowIndex === 0)) {

        tempData.push(rowData);

      }

    });

    if (tempData.length > 0) {

      data.push(tempData);

    }

  });

  var startColumn = 1; // Starting column to paste data

  var startRow = 1; // Starting row to paste data

  for (var i = 0; i < data.length; i++) {

    var numRows = data[i].length;

    var numCols = data[i][0].length;

    sheet.getRange(startRow, startColumn, numRows, numCols).setValues(data[i]);

    startRow += numRows;

  }

  var updateTypeColumn = 4; // Column index for "Update Type" (column D)

  var endDateColumn = 5; // Column index for "End date" (column E)

  // Check and set header for "Update Type" and "End date" if they don't exist

  if (sheet.getRange(1, updateTypeColumn).getValue() !== 'Update Type') {

    sheet.getRange(1, updateTypeColumn).setValue('Update Type');

  }

  if (sheet.getRange(1, endDateColumn).getValue() !== 'End date') {

    sheet.getRange(1, endDateColumn).setValue('End date');

  }

  for (var j = 2; j <= sheet.getLastRow(); j++) {

    var startDate = sheet.getRange(j, 2).getValue(); // Start date from column B

    var duration = sheet.getRange(j, 3).getValue(); // Duration from column C

    var endDate = calculatePotentialEndDate(startDate, duration);

    sheet.getRange(j, endDateColumn).setValue(endDate);

    var text = sheet.getRange(j, 1).getValue();

    var updateTypes = extractUpdateTypes(text);

    if (updateTypes.length > 0) {

      var formattedText = updateTypes.map(word => formatText(word.replace(/^\d{4}\s/, ''))).join(' ');

      sheet.getRange(j, updateTypeColumn).setValue(formattedText);

    } else {

      sheet.getRange(j, updateTypeColumn).setValue('Other');

    }

  }

}

function createWeeklyTrigger() {

  var day = 1; // Monday

  var hour = 1; // 1 a.m. GMT

  var timezone = "GMT"; // Set the timezone

  ScriptApp.newTrigger("importTableAndCalculateUpdateType")

    .timeBased()

    .onWeekDay(day)

    .atHour(hour)

    .inTimezone(timezone)

    .create();

}

function extractUpdateTypes(text) {

  var matches = text.match(/\d{4}\s([\w\s]+?)\supdate/ig);

  return matches ? matches.map(match => match.replace(/\supdate$/i, '').trim()) : [];

}

function formatText(str) {

  return str.toLowerCase().replace(/(?:^|\s)\S/g, function(match) {

    return match.toUpperCase();

  });

}

function calculatePotentialEndDate(startDate, duration) {

  var daysMatch = duration.match(/^(\d+)\s+days?/i);

  var hoursMatch = duration.match(/^(\d+)\s+hours?,?\s?[\s\S]*/i);

  if (daysMatch) {

    var days = parseInt(daysMatch[1]);

    var endDate = new Date(startDate);

    endDate.setDate(startDate.getDate() + days);

    return endDate;

  } else if (hoursMatch) {

    return new Date(startDate.getTime() + 1 * 24 * 60 * 60 * 1000); // Adding 1 day

  } else {

    return 'Invalid Duration Format';

  }

}

Is this the best way to do it? Probably not. The upside is that I did it in under 30 minutes, it only took some conversational skills, and I didn’t have to spend months learning a scripting language. However, if I do want to learn in the future, ChatGPT gives me all the explanations that I might need. 

Wrapping up

If you’re only here because you want the finished product, please feel free to create a copy of my Google Updates sheet that you can blend with Analytics data in Looker Studio. 

Here is an example of how your reports could look like with this data overlaid on your traffic timelines:

ALT/Caption: Screenshot of Looker Studio dashboard showing a timeline of a website's traffic annotated with Google Updates

You can also do a three-way blend with Start Date and End Date and then you can select any of the updates and see both the Start and End dates on the graph. You can even give them different colours. 

ALT/Caption: Screenshot of Looker Studio blending panel showing a three-way blend

ALT/Caption: Screenshot of Looker Studio dashboard showing Coe Update annotations over traffic timeline

Or, if you want to, you can create a copy of the Looker Studio dashboard that combines Google Analytics data with Google Update annotations from Google Sheets and see how it was set up. 

And if you’re interested in using ChatGPT to automate a different type of SEO task, please continue reading:

• Tell ChatGPT what URL to use in the code so I wouldn’t have to copy/paste it every time. Same goes for telling it the column and row where I want the formulas to go. 

• If using AppScript (and potentially any other integrated development environment), always save your new code after pasting and before you run it - it will save you having to troubleshoot a lot of errors that should not exist.

• Use examples but be clear that the examples are to be used to create a general pattern and not just used with those specific values.  

• ChatGPT is only as good as you are – if you don’t know what you want, chances are it won’t deliver it to you. 

• Talk to a developer to learn what’s possible and also what’s available to you.

• Try to use pseudocode instructions rather than free speech - this ensures there’s less room for interpretation and wrong results. Don’t know how to do that? Maybe you can ask ChatGPT to help here too. 

• Aim for progressive enhancement – start small and build complexity, don’t try to do everything at once. 

• Look at your data and notice shifts in patterns, then make sure to give ChatGPT examples that cover all patterns. 

• If your code is getting too long (the ChatGPT response is too long), you can focus on specific functions only instead of asking for the full code. ChatGPT will have a tendency to do that anyway. 

Final thoughts

If you decide to give this process a try, please consider sharing your achievements with your fellow SEOs. Your success story might serve as the inspiration someone needs to adopt this approach and enhance their own SEO workflows. 

Additionally, remember to use plenty of “please” and “thank yous” when interacting with ChatGPT – you never know what artificial intelligence might remember when it eventually dominates the world!