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.
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.
One of the key steps in leveraging automation effectively is identifying the right task to automate.
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.
Clear problem definition is essential in any automation project.
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.
Once the task has been identified and broken down, it's time to write prompts for ChatGPT.
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.
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.
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:
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:
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.
Always hit Save when doing any changes in AppScript and before running your code. Otherwise, you risk getting errors due to the code you’ve replaced and not saved, and will have to spend time debugging problems that don’t exist.
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:
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.
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:
Things I wish I’d done sooner:
How you can improve this process:
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!
