Tom Pool, the Technical & Training Director of Blue Array, led a webinar on the ideal crossover of automation and SEO.
He covers his favorite Google Sheet automations and shortcuts before sharing how to start using Python (anyone can learn) and where ChatGPT is handy.
Join our weekly live webinars with the marketing industry’s best and brightest. It’s 100% free. Sign up and attend our next webinar.
Watch the full webinar
Listen to the webinar as audio only on your favorite podcast platform:
And check out the Tom's slide deck and other resources shared below:
About Tom Pool:
Tom is Blue Array's Technical & Training Director. He oversees the technical strategy for the agency's clients, as well as running numerous training sessions both for members of staff and the wider industry. He really enjoys building and running training sessions and improving others' knowledge. Outside of work, Tom runs ridiculously long distances for fun (and also to eat more cookies).
Follow Tom on LinkedIn: https://www.linkedin.com/in/tom-pool/
About Blue Array:
Blue Array is an award winning UK based SEO Agency, with a wide range of clients. Our small and dedicated team is led by Simon Schnieders, who’s spent his career achieving great SEO results for big names like Zoopla and Mail Online. Bringing this experience to his management of Blue Array, he’s built a hard-working team of SEO enthusiasts, many of whom are shareholders in the company. That means it’s in all our interests to get the best results for our clients, every time. We do that within a culture of transparency and accountability. We're also a certified B Corp, meaning we’re committed to being part of a movement of companies verified to meeting the highest standards of social and environmental criteria and using our company as a force for good in the world – doing business with us supports this movement.
Read the transcript
Tom: Oh happy days. Let's get cracking then.
Be nice to everybody. As mentioned, there's also gonna be a bit of time for a more general discussion and a Q and A at the end of the session today. And I believe this session is also gonna be available on YouTube later on, which is awesome.
So, Yeah, just an introduction, Travis. You absolutely smashed it. I don't really need to say anything else. Other than that, frequently speaker industry events run a number of training sessions, so both for blue array clients and for the wider industry. Outside of work, I run ridiculously long distances for fun and also so I can eat more cookies.
I actually bake a lot of cookies myself. I cook a lot as well. I also have a young Springer door puppy called Ivy. She's about six and a half months old. She might make an appearance today. She might just randomly walk in. So if you see that, feel free to say hi to her too. Feel free to follow me on Twitter, LinkedIn slides, share, and GitHub if you are so inclined.
That's enough about me. Let's get into the real reason why we're all here today. We're gonna be exploring a pretty epic and wide ranging topic of automation, and then also how we can use it to help with ss e o, and kind of tasks beyond that. We're gonna start by going over a few definitions. As well as kind of introduction, introducing the topic of automation and how we can apply it to the world of s e o.
We're gonna look at a number of real world examples as well as a few further ideas, and then talk about relevant automations that you can help with various process improvements. We're gonna cover Google Sheets, Python Chat, G P T amongst a bunch of other stuff. So let's just dive right in. So introduction to automation, what is it?
Let's start with the definition according to Google. So you just type in what is automation into Google. You'll get something that looks pretty much exactly what is shown on the left there, and apparently automation describes a wide range of technologies that reduce human intervention in processes.
Effectively, this means giving stuff, boring stuff to a machine to do for you. And ever since we've been working, ever since it's been the world of work, or even before then, we've been trying to make our processes more efficient and easier for us to carry out. If you look around your house, there will be many items that are automations of processes.
So your washing machine, for instance, or your vacuum cleaner or your fridge. Computers, of course, are, you know, the ultimate automation. Currently, the internet, it's automation event information retrieval. Automation has come a long way from where we first started, you know, many thousands of years ago, and there's still so much further to go.
And a lot of people worry about automation taking everyone's jobs. This is quite a heavily Googled topic. Will robots take my job? Will automation take my job? Particularly with the recent advances in large language models people are, you know, concerned about losing their jobs to robots. Something worth bearing in mind is prior to computers and prior to, you know, computers kind of becoming more mainstream, accountants had to use pen and paper to file taxes, you know, work out payroll with calculators and do all sorts of other tasks.
When computers came along, they effectively revolutionized the industry with spreadsheet software amongst many other improvements. That allowed us to work faster and with so much more data than we were used to. That allowed us to improve. That process. Accountants still didn't have jobs. They just had to update the way they did things, and I imagine it'll be much the same for the short to midterm future.
We'll just have to adapt the way we do things the way our current processes work, we'll just have to adapt the way we carry them out. And the point of automation, the overall point of that as kind of touched on is to automate the boring stuff, but it's also to make your life easier, allowing you to deliver on the same amount of work or more work in a much shorter space of time.
As I've mentioned, yeah, automating the boring stuff is the name of the game here. Like why should you spend a large portion of your time wasting your valuable time wrangling data when you can automate a process to just do that for you? This is gonna allow you to spend much more time on the aspects of your job that you actually enjoy, enabling you to do more of the work that you actually care about, rather than messing around with numbers in the spreadsheet.
For us at Blue Array, this means being able to spend more time building meaningful relationships with our clients and creating bespoke strategies without having to worry about doing the boring stuff. And automation doesn't have to be complex either. When we're first thinking about automation, you know, complex and multi-step processes come to mind.
And if we go back to that definition, It's a range of technologies that reduce human innovation in process, and there's a huge amount of potential automations that are relatively simple to start using. I'm gonna share a number of these today and how they can help with, but of course, not limited to kind of work in the SS e O world as an example.
Almost all images. I think all but three images in this deck are AI generated, and this saves me a huge amount of time. Normally I'd look for really specific imagery that matches a slide. You know, I'll be googling for a really specific topic. I'll be looking for a specific image and I won't find exactly what I want.
Now I can just create almost exactly what I want. The images in this deck are created with Bing image generator. I'd highly recommend checking it out if you're interested in just creating imagery. There's some really interesting kinds of prompts that you can kind of put in.
But all of the prompts, if you are kind of interested in that, are included below the picture. So you can kind of copy and paste them in if you are, if you're, you know, wanna see what Bing image generator kicks out for you. Cool. So that's kind of the groundwork there. Let's do a little bit of building on that.
So how does automation, how does it fit into the world of ss e o as SEOs? There are a large number of potential tasks that we can carry out to help build and, you know, reinforce a long-term ss e o strategy. Keyword research is probably the one that most SEOs and even non SEOs will think of when it comes to ss e o tasks.
This includes things such as analyzing keywords and grouping them together into buckets. Having a look at the competition, so seeing what we are ranking for, what our competitors are ranking for, and kind of the gaps between them. You know, are there any keywords that our competitors are ranking for that we are not?
And vice versa. Kind of analyzing that and kind of coming up with opportunities, relevancy, so the keywords we're looking at, are they relevant to our specific niche? Are they relevant to our specific company, or are we targeting stuff that just isn't relevant at all? The intent as well. So what's the intent behind keywords?
Are users looking to convert immediately? Are they looking to, you know, get information? Are they looking to complete a transaction? Are they looking to navigate to a specific section? And that's also something that comes out when we do keyword research and it can be quite an intense process as part of SS e o.
Then technical work. And this is the stuff that I really enjoy. It's the stuff that I do on a day-to-day basis. It'll include things such as audits. So seeing where we currently are in regards to the kind of a technical health of a site, having a look at page speed or structured data or schema markup, having a look at core web vitals, even having a look at the expertise experience, authority, and trustworthiness of specific content performing, internal linking reviews.
Crawling websites, doing crawl analysis, kind of understanding how Google might perhaps portray a website doing duplicate content checking. So making sure that any content we've got in place is unique, and if not, we can kind of perform analysis to identify that competitor analysis. So having a look at how they're appearing within SERPs and you know where the gaps are.
Doing digital PR, creating content. There's a huge amount of different potential tasks and the kind of the expertises that require to complete all these effectively is wide ranging. And that list is pretty long and that is not exhaustive. There's a lot of other stuff outside of that, and there is always something to do, thankfully.
We don't have to do all of this manually, and there are a number of tools that we can use already to help make our jobs that little bit easier. So for example, if we're doing a crawl, you know, we're crawling a site to kind of understand how Google might perceive it, there's a tool called Screaming Frog that helps hugely with crawling and data gathering on the current state of the site and how it might present itself to bots.
If we're doing a page analysis, there's a few tools out there, page Insights, GTmetrix, webpage test. They're all really useful tools that can provide really deep level information into how a web page loads and how fast it can and currently is. And while there are a lot of tools already available that can automate some of what we currently do, we should always be looking to take this further and create better process improvements.
Each of these tasks can be automated to some degrees, the ones we mentioned earlier, to help ensure we're able to carry them out in a timely manner. And the things we should always look for when we're thinking about automation are as follows. Is there a defined process carried out? So are there any steps to follow?
Is it a step-by-step process? Is there a large amount of data analyzed? If, for instance, you're analyzing a lot of data in Google Sheets or Excel, there will come a time when you'll be sitting looking at a loading symbol. Automation can help with that and we'll touch on this a little bit later on. Is this something that is repeatable?
Is the task that you're doing, is it repeatable for more than this specific use case or this specific client that you are looking at? If it is, then it's likely to be something that is more valuable to be automated. There's no point or there's less point automating something that is a single use single use case.
Then something that is kind of applicable across all of our clients. And is this something that will actually save time? If I'm doing a process every day that takes five minutes or even less than that, it takes one or two minutes. There's limited value in me kind of automating that. Whereas if there's something I do every day that takes an hour and I can automate that and get it down to five minutes, that's a much greater time saving.
Over the next few slides, we're gonna go into a bit more detail on specific automation and how we can get started with it. So having a look at kind of automation 1 0 1, and we're gonna recap a little bit of what we just covered a second ago. So, while automation can significantly streamline SS e o tasks, it's essential to note that SS e O requires a human touch.
It requires an SS e o to build a relationship with a client. Automation tools. They provide data and insights, but interpreting those, actually understanding the nuances of that, and of course, building that genuine relationship with clients is always gonna require a personal touch and connection.
Automation should be viewed as a way to help in the kind of the wider SS e o process. You know, making those repetitive tasks more manageable, of course, but not replacing the need for skilled ss e o professionals. They're still gonna need to be someone on the other side of the data kind of interpreting what it is that these tools and these automations effectively spit out.
So why should you even care about it? Everyone should care about automation and using it effectively should absolutely be a key consideration. There's number of reasons why, so I've touched on these briefly, but the time savings, if you automate something well, can handle repetitive tasks, freeing up time for more valuable activities, so to do that stuff, Tasks are always gonna be completed the same way.
So if you have a specific automation, it's gonna carry out those tasks or those steps that you've given it the exact same way every single time. It's gonna help reduce human error. I know if I do something in the morning and I do something in the evening, there's gonna be a difference in quality between those two pieces of work.
Scalability. So effective automation allows for pretty much unlimited scaling without also having to linearly increase the amount of effort or money that you spend on it. So a good example is kind of automating an email campaign. If you know, as an individual, I wanted to send out 10 emails. Okay, cool.
I can write 10 emails, that's fine. But then your boss comes to you and says, Hey, I need to write a thousand emails. You're probably not gonna do that all manually. You're gonna get a process to do that. The process to write those 10 or a thousand using automation. It's basically the same cost efficiency.
So some automations are gonna require an initial cost. You're gonna have to spend some money or investment to kick things off over time. Automation helps reduce the costs that are associated with human error, inefficiencies, and labor. And of course we've got employee empowerment, even without deep level technical expertise.
And you know, that deep level knowledge, a lot of tools and technology and, you know, therefore automations help allow anyone to kind of set these up, empowering them to become more productive and more efficient. And there's a few basic principles that we should have a look at. So we've touched on these briefly, but first up is to identify repetitive tasks.
Then we've gotta choose the right tool for the job. So how we're gonna kind of automate it. Then of course we've gotta implement and then monitor, and then ongoing optimization, and we'll touch on these a little bit more in a. So when it comes to identifying repetitive tasks, we need to identify anything that we do on a regular basis to start automating effectively.
Extra points are awarded, of course, for those tasks that consume considerable time, or those that require minimal decision making. So a couple of examples include if we're doing just general data entry and manipulation. Perhaps we're sending reminder emails to our clients generating reports. And a good way to approach this is to document everything that you do in a week or a fortnight or a month, or over a specific amount of time, and any tasks that repeat within that timeframe or tasks that follow a clear set of steps is something that we should consider automating.
And once we've identified those tasks that we believe that we can automate, it's time to identify the tools that can help us achieve the automation that we're looking for. We need to be able to take a couple of factors into account. So of course, including budget, how much can we actually spend? If we've got an unlimited budget, we can go and buy the most expensive tools and everything's great.
That isn't necessarily the kind of case with a lot of situations. Sometimes you have a very limited budget to be able to kind of automate stuff, at least to start with before you can start proving the benefit of it. And of course, the current skill level. Depending on whether or not our colleagues ask nicely to do things for us, we're gonna be able to take different approaches.
In addition to that, does that tool that you are thinking of getting integrate well? Integrate well with other systems that you are currently using? So for example, if you use spreadsheets and you import C S V reports from various locations, use that tool. That you are kind of thinking about doesn't actually provide the correct c S V export that you are looking to kind of pull into that spreadsheet software.
Does it integrate well with your existing systems? Then we've of course gotta set up the automation. This might involve, you know, defining the workflow, so working out exactly how to define the process, the exact process that you follow. Create a diagram for this. Actually write out step-by-step if, for example, you are doing keyword research.
Write out every single step that you currently carry out. And then you can work out exactly what automation or what technology can help you with each individual step setting triggers. So when you want this automation to be running we'll touch on this a little bit more within like Google App Script a little bit later on.
How, how often do you want this to run? Do you want it to kind of trigger on a specific day? Do you want it to run every day? Do you want it to run every month, every week, every time you send an email, for instance, or any other kind of trigger? And you've gotta set those up effectively. It might also involve creating scripts or creating code.
We'll then have to test this kind of code or this automation in real world situations to make sure that it works. As expected. There's no point in just creating an automation, putting it out there, sending it to your colleagues and saying, yeah, this works without actually testing to make sure it actually does work as expected.
And when an automation is set up, we need to review it and then ensure that it runs smoothly and serves its intended purpose. This will involve regularly checking it and making sure that there are no errors. You might notice. The errors that you come across when you kind of release it to the wider world or to your colleagues that don't necessarily appear in initial testing.
And something worth bearing in mind is that people are exceptionally good at breaking things in ways that you might not necessarily expect. So when that process is set up and it's just time to step back and then basically let it save you time, however, we don't wanna leave it forever. We don't wanna leave that kind of automation alone as we can always improve on things.
As an example, that first iteration of an automation might only do a single step of a multi-step process. So that workflow that you might have defined earlier on You might only automate the first step with that workflow. You know, it's important to always refine and improve for better results and overall efficiency.
So you can do the first step, then move on to the second, then the third, then the fourth, and then you can tie it all together into this one big kind of tool or automation. It's important just to start small, to be honest. Just start with the small little things and then you can kind of build on that.
In addition to that, collect as much data as you can about using that specific automation. So how long did that specific task take before the automation? You know, how long did keyword research take you, or the specific kind of task as, or specific step of this task? How long did that take you? How long does it take now?
Does it take longer? Ideally, it shouldn't. 'cause the whole point of automation is that it does it automatically and you don't have to kind of spend as much time on it. How much time and money is that automation then gonna save over a week or a month or a year? Work out your kind of, your hourly rate, work out how much time it's saved you Multiply those two numbers together to work out how much you are saving.
Cool. So that's kind of automation 1 0 1, the basic kind of information. We're gonna skip onwards to automation within Google Sheets. So there's a wide range of potential automations at our fingertips within Google Sheets or Excel too, if that's your kind of game. We've got shortcuts, so, you know, the, the basic, you know, copy and paste, but there's some other ones as well that are really useful that you might not kind of use that might be absolute game changers.
We've got formulas, so formulas, macros, Google App Script, and then of course we have add-ons as well. I'm gonna touch on a couple of practical examples of each of these areas that are instantly useful for a wide range of applications. First up, shortcuts are one of the easiest ways to start automating workflows, and it's likely you are already using them, knowing how to navigate around a spreadsheet as well as perform a number of tasks or with a few taps of your keyboard.
How do you feel like an absolute wizard? And of course, you know, if you can do a lot of different tasks without moving, touching the mouse, it's gonna impress anyone who's watching. I dunno who's gonna be watching now that we're all working from home, but there we go. And we should all be familiar with copy and Paste.
It was actually invented in the 1970s. But that's a different, that's a different talk for a different time. But there's some other awesome shortcuts. So I use a Mac. There are kind of equivalents for these, for Windows. If you're on Mac. Command backslash clears any formatting that's in place on a cell.
Great. If you wanted to remove a link, you wanted to remove a link or you wanted to remove any kind of other formatting that's in place, you can do that. Command Ctrl rs. If you don't wanna you don't wanna click on a specific filter and open up that menu, use that shortcut and it'll open up the menu for you.
You can then start kind of typing away in there command shift h find, and replace. We all should kinda understand that one. Then command, and then the semicolon inserts the current date into the cell you are working on, which is awesome. Really cool. Quick shortcut and highly recommend that one.
Then we've got an option and forward slash that opens the menu. If, like me, you forget where things are within certain menus and Google sheets, just type in option slash. And you'll then be able to type in exactly what it is you're looking for. So if you wanted to strike through text, just type in option slash and then type in strikethrough and then enter and it will apply that strike you to sell your own currently.
Really useful 'cause I just forget where stuff is in menus. And that can really help there. Then if you want to have a look at any other shortcut that's in place, have a look at a command slash that will open up the shortcut menu and show you the plethora of options that are available for you for any shortcuts that you are interested in.
That shortcuts out the way. And if you look at any big spreadsheet, so particularly big reports or keywords, universes that have a lot of different tabs and a lot of different kind of a lot of different sources of data in them, there will probably be a number of formula used to help calculate relevant values based off of the data that's in place.
So a couple of common formulas include count, if so, if you wanted to identify duplicates, for instance, you can use that. V lockup, an index match that can be used to pull in data from multiple sources using a common key RegX replace, which is an absolute lifesaver when it comes to migrations and redirect mapping.
Google Translate. There's no explanation required there. Just allows you to translate text into a different language and then query, which is a basic version of S Q L or SQL in your sheet, which again can be a real game changer. I've delivered a talk at London, s e o XL back in 2022. On Google Sheets formula for s e o, I'd highly recommend checking out if you want more information on any of those.
Of course time is limited today, so I'm not able to go into those in too much detail, but I highly recommend checking that out for more information if you are interested. We've then got named functions and these are absolutely awesome. So, taking a step up from the standard formula, we can create our own, well, our own formula using something called named functions.
If we have a large spreadsheet, perhaps one that's being used on a rolling basis so a monthly report for instance we might have a number of long custom formulas in place that might look something like this. At a first glance, that's not pretty. I don't enjoy looking at that. And this would be in place in a cell and it can look really messy.
With named functions, you are able to effectively convert that into something that looks like this. So we've taken that entire kind of bunch of text and put it into a single little formula. So if I wanted to run that kind of long custom formula in a different cell, all I've gotta do is type in this kind of.
Named function and then the values, I'm looking to kind of perform that on and then it'll do all that for me. In the setup process, you are able to kind of describe Provide an example, write about, so just kind of provide a little bit further information, you know, if you'll kind of come back to it in the future and you forget what the kind of custom or the named function was.
Then also a definition of it as well which is awesome. That's available within the kind of relevant menu within Google Sheets, and I've actually forgotten which menu it's in to use option slash to find that. We've then got macros and these have been around for a while. And they were initially written using V B A within Excel.
And they basically allow you to repeat the same actions multiple times. You can still write a macro manually if you'd like using V B A or if you're using Google Sheets, you can use App Script. Or you can use the record, record macro function that's available within Google Sheets. And this will record exactly what you do, which you can then save to use later on.
I personally use a formatting macro with all spreadsheets that I use for client facing work to change the header, change the head of the font and then the font size bold that header so it looks a little bit different, change the kind of color of that as well. Change the font and then the font size of all other text.
Add a custom color background to the head row and then resize column, so it's a little bit easier to read and. So beyond macros, once you've kind of dealt with those, taken a little bit, step further, we've got apps script. And the best way to kind of get started with apps script is to actually create a macro, so record a macro like you'd do for something.
So just record a macro to, I don't know, like change the size of a set of a column and then bold the text. Then you can edit it within the relevant menu option that's available in Google Sheets. So just go onto extensions, macros, manage macros, then click on the three dots next to the one you just made.
Then you can edit the script and it will then show you the code that you have just created through your actions. App script is like a superpowered formula. So if you then have a look at the code that's created, you'll be able to kind of understand, okay, this click what I did here, you know, resize the column translates to this piece of code, this bit, you know, where I bolded it translates this piece of code.
Outside of that, you can also do a lot of other stuff. You can. Show individual cell edit history and notes. You can create custom menus and functions. You can create scheduled tasks based on time events, so you can add a trigger within the app script. For instance, say for instance, you wanted to send emails to all of the emails that you have in a spreadsheet on a specific date, at a specific time.
We wanted to send reminder emails every two days until a seller is updated. You can do that. Within there, there's like a trigger option. You can just select that and then kind of change your options there. Send email notifications when a seller has changed. So if you want to see who's meddling with your spreadsheet, you can put in an app script that allows you to kind of send yourself email notifications when a seller has changed and who's changed it.
You can connect to APIs. So if you wanted to send Slack notifications based on, you know, sales updates, you know, if you're tracking all of your clients and you wanted to send specific notifications to the specific Slack channels, you can do that via app script, update a Google Calendar based on spreadsheet data.
There's basically unlimited options. The only limit is kind of your imagination when it comes to app script and there's a really low, or there's a much lower barrier to entry than there used to be, which we'll touch on a little bit in a while. Yeah, as I mentioned, yeah, potential here is endless and it's got a really low barrier to entry.
Finally for Google Sheets, we've also got add-ons. And these allow us to do pretty much anything we'd like instead of creating your own script. So if you can't be bothered or you just wanted to see if there's something out there that does it already, there might be an add-on out there that does the job for you.
There's a lot available, and that list is always growing. You can also make your own add-ons if you're so inclined and share them with your organization. So if you've created something really good. Kind of add-on that does a certain amount, it does like certain data wrangling, or it pulls in like a p i data from other places.
You can create that add-on yourself and then share it within your organization so you can share it with your colleagues or you can share it with wider industry or you know, and beyond if you'd like. So one popular add-on that you might've heard of is super metrics that allows you to pull data from various platforms into Google Sheets, basically allowing you to streamline your current workflows and of course automate some of your processes.
So that's Google Sheets, completed it. Next we've got Python, and this is where it gets a little bit fun. So, If you spend any sort of time looking for automations or process improvement for SS e o, you'll probably come across many mentions of Python and it comes in handy in a number of different situations and you don't have to be able to code to be able to use it.
And we'll touch on this in a little while, and it's a great way of performing a kind of large scale data manipulation and can massively speed up a lot of different processes. So a short list of some stuff that you can do. You can filter data sets by specific criteria. Say for instance, you have a spreadsheet with 300 million 300,000 rows.
Those of you who have dealt with that will probably have some kind of form of P T S D because that is never fun. You can do this with Python in seconds as opposed to minutes or potentially hours within a web-based interface for Google Sheets. You can merge data together using APIs to pull data from various sources.
You can use natural language processing to analyze keywords and then determine the intent of those keywords. You can scrape the web, you can do basically anything. And to be able to start running Python on your local machine easily. If you are interested, I would recommend getting an I D E or an integrated development environment.
This will allow you to basically write, debug, and run code really easily on your local machine. Before IDs, before they were a thing, we would have to write code in the text editor and then run it using the command line. We can still do this if we want, but using an I D E just makes it a little bit easier, particularly for kind of, you know, entering into it.
I personally use pie charm. One of my colleagues used a Spider. The choice is completely up to you. There's hundreds of them out there. Most of them work in a similar way, so if you're working in one and you get an issue, someone working in another can probably help. You can also use an online one as well, so like Google Colabs or Jupyter Notebooks if you'd like.
And they offer similar kind of functionality as what you'd get on your desktop. When it comes to writing code, it's relatively straightforward. You don't need to be an expert in computer science to write code, and it is completely fine to copy and paste other examples that you might have seen. With a lot of code.
We'll also be importing libraries to allow us to carry out a number of more complex functions. And a library is effectively a bundle of code that can be used repeatedly, and they help make Python a lot, lot simpler. There are libraries for a lot of different applications, including machine learning, data science, data visualization, and natural language processing.
There's libraries for pretty much everything and it effectively saves you from having to write that kind of code yourself. When you write code, you typically import those libraries first, and then you call, or you use them later on within your code. So you import the natural language processing library, for example, and then you just call it saying, Hey, natural language processing a p I do this specific task for me.
And that just makes it a hell of a lot easier. So let's have a quick look at a problem and show how Python can help, you know, can help automate and improve our current process. So the problem or the task we have, we have a large list of keywords and we would like to get monthly search volume on a rolling basis.
We'd like to get monthly search volume on, you know, the, I don't know, the middle of the month, every single month. You just wanna see some kind of trend, like trend level data of keywords. So the pre automation solution to this large list of keywords could be anything from a hundred keywords to, I don't know, a hundred thousand Preau automation.
We would have to copy and paste that list of keywords into an online tool, SEMrush or, or a similar tool to get the data that we're looking for. We'd then have to export that and then kind of format it. And then we'd have to do this every single month on the same day, every single month. When we have to do that for a number of keyword lists, for a number of clients, it can take a significant amount of time. Doing it for a hundred keywords, cool, might take like 20 minutes, 10 minutes, 20 minutes.
But then when you, you know, do it manually, but then when you get to lists of thousands of keywords and you've gotta do it for 10 clients, it gets repetitive, it gets boring, and it's not enjoyable. And the whole point of automation is to automate the boring stuff. So that post automation solution, we will use a Python script to take those keywords.
Query the SEMrush apI then manipulate it so it's in an ideal format, so it follows the exact formatting that I want, that we want, and then sync it directly with Google Sheets. And then we can even sync that sheet that Google Sheets with BigQuery and then sync that with like data studios.
We can then display that data on a monthly basis. We then set up a trigger that runs that Python script at the same time every month. And then all we have to do is go into the report and change the date. And that is it. There is code for that available on my GitHub, so if you're interested, have a look at that.
You can pretty much just copy and paste it and it will do pretty much exactly what I've just mentioned there. There's also a talk I did at Brighton ss e o earlier this year that goes into that in a little bit more detail. So again, if you're interested, check that out. Highly recommend it. And there's another quick example.
Instead of taking 10 minutes to process a hundred keywords, it takes 3.3 seconds which is awesome. And another example of how we can use Python to help us in our day-to-day work is using natural language processing. To compare page copy, to identify duplicate content or perhaps duplicate content sections.
So split the page into sections. Have a look at the text and identify other pages that use that same text. The list is pretty much endless to be honest, of what you can automate. Whether you, whenever you find a process that's quite repetitive or it's taken a while to complete, you should consider whether Python is able to assist you.
Bear in mind that it may take a while to create any scripts to start with. However, when they're created, you'll be able to reuse these scripts as many times as you like, and the output is gonna remain the same, allowing you to cut out human error and allows you to make some considerable time savings in the long term.
It might take a little bit longer to kind of create that script. So in that example where I spoke about. The querying the Semrush API took maybe like five or six hours to be able to create this code, to be able to do that. I'd been able to do a, probably a whole keyword list in that time.
But then when I multiply that by the number of clients, I have to do that for. This makes considerable time savings and that again, is what it's all about. Cool. Unfortunately, I don't have much time for any further Python stuff. If anyone has any further questions on Python, pop 'em in the chat. I'm happy to give a crack at answering them.
Or if you'd like a little bit more information, reach out to me. I'm always happy to help. My contact details were on that kind of initial slide and they'll be at the end as well. Finally, we're gonna have a look at generative ai. Now, of course, no automation talk would be complete without mention of everyone's favorite chat bots.
I'm, of course, speaking about chat, G P T or Bing Chat if you'd like, or Google Bard, whichever one takes your fancy. It's a great tool to use to help with automation and it's made a lot of people's jobs a lot, lot easier. The barrier to entry to automation is now a lot lower, as we can effectively ask generative AI to help us build whatever we can imagine or even help guide us to better solutions.
It can provide further information or suggest approaches that you might not necessarily have considered before.
As a side note, G PT three is free. Bing Chat is also free. If possible, I'd recommend a G P T plus license. It's definitely worth it. Again, reach out to me if you'd like to discuss that further. And I've recently asked chat g p t to create code for me. So for that N L P example I'm not a prof.
Like, I'm not that proficient coder. I can write code, all right? But I'm not very quick. This managed to create that code that I gave an example for the N L P to analyze content in place on a website. Not only did it create that code in about a minute, it also explained that code to me so I could fully understand what it was doing, so I could treat it as a learning experience and actually understand exactly how it was doing, what it was doing.
Beyond kind of getting a chat g p t to write code for you. You can use it to analyze data sets, provide recommendations on how to approach problems, and give further information on specific topics. Again, that list is pretty endless. Chat. G P T is like a junior developer. They're really keen. But one thing worth bearing in mind is that it will make mistakes.
Sometimes it'll have things called hallucinations where it basically just makes stuff up. Treat it like you would any tool, right? All generative AI is, it's a tool that you can use, use it like you would any other, and just be aware of any potential limitations. Don't blindly trust what it creates, and it will probably be okay.
As an example just to kind of show again what, what it can help with. On the left hand side, we've got a question that I had related to Google Sheets. So I asked how I can get the week beginning date in sheets based on a date. So I had a list of dates in a sheet, and I wanted to get kind of.
What week, what date that week fell in, began with. So I just asked chat G P T and it gave me the formula. I just copy and pasted that in, changed the cell reference and it worked perfectly, which is awesome. Similarly if I had a really complicated formula, like the one I showed earlier, you can just paste that into chat G P T and ask it to explain it to you and it will do that as well.
Again, really awesome stuff. Cool. So that's most of the content that I'm gonna be going through today. But kind of one final thing taking it further. So I've shared a number of kinds of automations and ideas and there's a few that I use in the day-to-day that can provide a starting point for you if you are interested in taking automation into your own role.
So one that is probably the easiest to do if you are interested, is creating automatic screaming frog crawls and automatic report building in Looker Studio. There's an article in place on the Screaming Frog website that kind of provides you with all the information you need for that. It can be really useful for understanding a website's health over time.
Any sort of large or medium scale data manipulation, like if you are kind of editing data, you know, filtering it a little bit, taking stuff away, adding stuff in, and it's taking you a little bit of time. Consider using Python or kind of just, I just recommend taking a sample of data pasting it into chat G P T and asking it to help you with it, saying, you know, how can I do X with it?
And it'll then provide you with an approach that you may or may not follow. If you use a Mac, have a look at the Automator app. This is a cool one. I mentioned it in that talk I mentioned about Bright Brighton, s e o. But if you wanted to kind of integrate apps with other apps, say for example you receive an email and that comes through your mail app and you want it to take and take a specific action based off of that, you can do that.
Recommend checking that out Zaia as well. That's one I didn't mention in here but I should have added a section on it. In retrospect. Using zaia for a wide range of automation tasks can be an absolute game changer. So if you have an email come in and you want to do a specific example, you want, you know, do specific stuff based off of that or a spreadsheet update and you want to send an update, you can do Zaia for that.
Connect apps together. Really cool. Use the page P insights, a p i to build a speed dashboard for you and your clients and your competition to compare them against each other. If anyone would like to discuss any further automation ideas or you wanna know more about anything we've discussed, pop it in the chat.
We've got time now for q and a or reach out afterwards. I'm Tom, you can find me here. That's it. Awesome. Q and a. Anybody have any questions? I don't know if anything. Awesome job,
Travis: Tom. Yeah, we had a couple questions we could kind of kick it off from there, but that was a great presentation. I think everybody took a lot of notes.
But Anna kind of started off with what are some of your favorite and most used Google spreadsheet add-ons that you use on a daily
Tom: basis? Super metrics is one of them. I believe there's also a keyword for sheets. That kind of pulls data in from Google Search Console pulls that into sheets.
That can be quite useful. I think there's also a search console connector as well. Yeah, they're ones that I, I think there's also like a, there's like a chat g p t for sheets or a similar one that can put G P T insights in your sheet. So you can type equals chat G B T for instance. And then type on the sale.
Do you want to get insights on what this means? For instance, you can kind of get insights straight away there. Oh, awesome.
Travis: And the next question would be, would there be a way to automate schema audits along with generating applicable schema found from the audits?
Tom: Absolutely. Yeah. So creating schema is one thing that g p t is actually bloody good at.
It follows very specific rules. It's really easy to kind of, for, for a computer to understand, you know, you can give it the content of a page. Say, I wanna add this schema to this page, and then it'll create the schema for you. In addition when it comes to auditing I would consider using Screaming Frog to crawl a website, extracting the structured data that's in place.
It will then find loads of issues or it might find issues. With those I would perhaps analyze it in Python to extract our top level trends. So to find the ones, you know, the kind of errors that are seen the most. Then put that into chat g p t to understand a little bit more about, you know, get a bit more verbose kind of description of the issues that are happening.
And then provide recommendations, give it the content that you've started with and then kind of say, you know, these are the issues I'm finding. What do I need to do to fix it? And then it'll then give you an output. Nice.
Travis: That's a, that's a good little workflow. And then Joshua asked, what are the benchmarks used to measure the performance and accuracy of these tools in diagnosing SS e o issues?
And he referenced a post on LinkedIn from Gary lls. I'm trying to see what his question was.
Looks like it's kind of referencing what you had mentioned as well
Tom: to always double check. Joshua. Oh, hi Josh. All right, quick one. Benchmarks used to measure the performance and accuracy of these tools in diagnosing SS e o issues. I dunno. I will get back to you on LinkedIn later. I'll have a look at that and then I'll get back to it directly.
Awesome.
Travis: Then Anna, kind of just by wrapping it up, asked, are there any other videos of you walking through sits and kind of demonstrating your workflows,
Tom: At the moment, not that is available for the wider community. So if you become a client of Blue Arrays or if you are an internal staff, we have a lot of internal documentation on that.
However, if there's anything that you want a demonstration of, Pinging me a message on LinkedIn or Twitter and I'll gladly create a video for you for that and I'll post it as well.
Travis: Oh wow. Yeah. Thanks for that. Awesome. That's all the questions we have, Tom, so thanks again for your time. Oh, actually we just had one more drop in.
Oh. What are your recommendations on SS e o page, having content developed through Chat
Tom: g pt? Alright, if you wrap, this is a great question wrap. Recommendation on S E O pages, having content developed through chat gt, I would not use fully AI generated content in a competitive market. The thing with AI generated content, specifically, those that are using chat g p t, it's effectively recycling content that's already out there.
It's not gonna produce anything unique. It's not gonna produce anything that's particularly valuable. I would, however, consider using it to create perhaps a content template that provides a few key talking points about the content. So say for instance, you're looking to create an article on.
I don't know how to become a yoga teacher. It will then give you examples. You say, you know, I want a content template based on this. Give me six headings and some topics I should include. It will then cr, you know, create kind of a, a bare bone structure for you. You can then kind of use that, send that to a content writer, or use that yourself to create that content yourself.
I would never kind of recommend exclusively using AI generated content in any kind of way.
Travis: Awesome. Yeah, I agree with that as well. We got two more questions. First one is, what Python libraries would you use for N L N L P?
Tom: Bear with me. So the ones that I currently am using are N L T K and Spacey.
So that's S P A C Y. There are loads of them out there. Like you can, there's a lot there all for, there's a lot for specific examples. The one that I used for the duplication checking was N L T K. That did a lot of that, that was really valuable for that.
Travis: Awesome. Perfect. We'll check that out.
