Hi folks, Danny Richman here.

I’ve put this video together just to give you the instructions for how to use my free Google Sheet and script which is called “Low Hanging Fruit Keywords AI”.

This is a script powered by GPT-3 to help you find strong new content opportunities for your site or a client’s site. To identify keywords where your site is already ranking in some position on Google, perhaps not in the strongest position, and where if you had a piece of content dedicated to that keyword, that topic, there’s a very good chance that you could drive a lot more traffic to the website.

So let me just first of all explain in a bit more detail this idea of low-hanging fruit keywords with an example.

So if we have a look at this article on this website here, this is a website called lovinglifeco.com, and on this website we have an article which has a title “Forearm pain when curling. Three reasons why.”

Now if you go and do a search on Google for that term “forearm pain when curling,” we can see that this article is performing really well on Google, coming up in the number one organic position, both in the UK and the US. And that’s great.

But if we go and have a look in more detail and we pop over to Google Search Console and have a look at the performance for that specific article, what we can actually see here, if I just move myself out of the way, is that that same article is actually appearing on Google and being seen by people for probably more than a thousand different keywords.

One of the problems with Google Search Console is that it limits what we can see to a thousand rows. So in actual fact, it’s likely to be coming up on Google for even more than 1,000 different keywords.

Now, if you have a look at these keywords here, yes, obviously we’ve got keywords that are very, very closely related to the title of the article, such as forearm pain when curling, forearm hurts when curling.

But once you start kind of going through this data, what you’ll see is that there’s all kinds of variations on this. Let me just dig through to some deeper results here. See if we can find some in lower positions. Here we go. So you’ve got something like here, like forearm splints curls, hammer curls, forearm, all kinds of forearm pain, barbell curl, all these kinds of different variations.

Now, the reason for that is because, you know, Google is not precisely just simply matching search queries to phrases and words that appear on the page. Google is very good at trying to understand the intent behind a search. And so different variations of the same search query will often bring up the same piece of content, even if that phrase isn’t exactly mentioned in the article.

The other reason as well is because if you go and have a look at the article, you can see that, you know, this is a fairly in-depth article. It’s got lots of subtopics within it. Why you may be experiencing forearm pain during a bicep curl, tight and tense forearm muscles, not warming up, weights are too heavy. And so all of these, sections and phrases and words and paragraph within the article also give this a chance to come up on Google when people are performing search queries around these subtopics as well.

Now, the problem is that if somebody does do a search on Google, let’s say they do a search for something like forearm pain because weights are too heavy, let’s just imagine so that kind of relates to this section here. The likelihood is that this article isn’t going to appear in the number one position on Google for that term because it’s not precisely on that topic. It’s not closely related to the to the core title of this topic. And so when people see it coming up in search results, they’re going to see the title of the article and they’re going to see that it’s not exactly what they were looking for.

So if we find that people are searching for these terms such as, you know, a forearm pain due to weights being too heavy. If we then broke that out of this article and actually did a separate piece of content dedicated to that central topic, there’s a very good chance that this new article would now appear in a much stronger position on Google and drive a lot more traffic into this website.

 So identifying these, what I call low hanging fruit keywords is something, it is a process that you can do manually. So you can just go and export all of the keywords for that particular page into a Google sheet. And it will show you all the queries, the clicks, the impressions, the position, all this kind of thing. And what you can do is you can then apply a filter to only show you those search queries that are ranking from position, say, four to 20.

So they’re appearing not in the top three positions but appearing somewhere on the first two pages of Google. And you can then look at each one of these search queries and then decide, well, is there an opportunity there? Is there a good number of impressions so that it’s clear that many people are searching for that term? And then, for example, we’ve got here a pain after bicep curls, is that something that is warrants breaking that off and creating a separate piece of content? Now, the problem with doing this is that, first of all, you’ve got the limitation that a search console is limited to 1,000 rows. And if you remember, there’s more than 1,000 keywords. And the bigger problem is that this is a very slow and painstaking process to have to go through every single one of these keywords and try and figure out how closely it relates to the title of the article, the core topic of the article, and make a decision as to whether it’s warranted to then break this out as a separate piece of content is going to take a long time and require a great deal of concentration.

And remember as well, we’re only looking here at one article on this website. If you then are going to repeat that process for all of the content of this website to look for these keyword opportunities. You’re talking about something that could take weeks, months of work.

Another potential challenge with doing this task manually is that you could be working on a client’s website where you’re not very familiar with these topics, with the search queries. It may be something quite technical or relate to an industry with which you don’t have a huge amount of familiarity. So having to go through these keywords and work out whether that search query is closely related to the core topic of the article may be something that’s extremely challenging for you to do without having a topic expert sitting and going through it with you.

So this is why I’ve created this Google Sheet. This is a way to identify all of these low-hanging fruit keywords for a whole website all in one go and to let GPT-3, which is a machine learning language model, identify those keywords for you.

So I’m now going to go through and show you exactly how to use the sheet. So if you click on the link in my article that accompanies this video, what that’s going to do is open up a copy of my Google Sheet. So you’re going to be asked whether you’d like to make a copy of low-hanging fruit keyword “AI”, and all you need to do is just make sure that you click the blue copy here. So you want to get a copy of this into your own Google account rather than working on my sheet, which you won’t have access to. So just click on “make a copy” and that will bring you into Google Sheets.

Now there’s a couple of other things you’re going to need before we get started. Now this isn’t essential, but this is certainly going to help you through this process is to go and install a Google Sheets add-on which is called Search Analytics for Sheets. Now what Search Analytics for Sheets does is a very popular add-on. Anyone that works in SEO has probably already got this installed, but what this does is it allows you to connect to the Google Search Console API and then pull in all the search query data with no restrictions. So you’re not limited to one 1,000 rows. And you can also do cool things like set a date range, add filters. So if you want to exclude certain rows or only look at one particular country’s data, you can do all that kind of thing. Now, search analytics for Sheets is free for up to 25,000 rows of data. They do now have a paid plan if you require more than that, but for most websites, 25,000 rows of data is going to be plenty. So it’s not essential. There are other ways of getting keyword data, which I’ll talk about later. But if you’re trying to use this for a website that you own and have access to a Google Search Console property, I would recommend installing Search Analytics for Sheets.

Now, the last thing you’re going to need is you need to go and create an account with openai.com. because what you’re going to need to make this sheet work is one of OpenAI’s API keys. Now, if you’ve never used GPT-3 before, which is their machine learning language model, they will actually give you $18 worth of free credits the first time you register to use this. But if you’ve already used up all your free credits and you’ve got this, then there is going to be a small cost involved. and I’ll talk about the costs in a moment.

But once you’ve registered on the OpenAI website, all you need to do once you’ve finished registration is then just go and get an API key. So you just need to click on this little button here, create new secret key, and then copy and save that key. It’s just like a kind of a secret password. Copy and save that somewhere so that you’ve got that safe. Just to let you know, once you have generated that API key, There’s no way of viewing it again on the OpenAI website. So if you haven’t saved it, stored it somewhere, you’re going to have to create a new one if you want to use their API.

Okay, so back to the copy of my sheet that you’re going to create. First thing we’re going to do here is click Make a Copy. And that will take a couple of moments. And what you’ll find when you open that sheet up is that you’ve got two tabs here. One is called GSC data. And the other tab here is called settings. Now what we’re going to do is first of all, we’re going to put the settings in so that we can be ready to use this.

I’m going to pop over to the settings tab here. And what we need to do is you’ll see here in cell A2, there is a box for you to put your API key. So we’re going to take our API key, just going to use mine here, I’ve got the burner one. I’m going to put that into cell A2 there. Now the next thing we need to do is to train the model to understand where a search query is a strong match, a medium match or a weak match for the title of an article. Now I’ve put three examples in here using that article I referred to earlier, Forearm Pain When Curling. And you should actually find that this will work even if you just leave my examples here, even if your website has nothing to do with health matters or forearm pain or any of these topics, the fact that I’ve put this training data in here should work quite well for a website on any topic. But if you find, once you’re using the sheet, that you’re not getting good matches, you’re not getting good classifications, then you might want to change these examples to something more relevant.

So just to kind of show you what we’ve done here, So we’ve got an article as you saw before with the title forearm pain when curling three reasons why love in life And then we’re putting in an example query here forearm pain with curling now as you can see that is a search query that is very closely related to the title of the article and So what I’ve done here is I’ve selected and said that’s a strong match Then we’re moving over just shuffle this over a bit to example query to buy set pain with curling. And that’s for the same article for on payment cutting search queries by set payment. So this this is somewhat related to the title of the article, but it’s not an exact match. This is what I’ve classified as a media match. And then the last example here, example query three, we’ve got leg pain with curling. Well, I would consider and have classified that as a weak match. for an article that’s on the topic of forearm pain when curling. Here it’s talking about legs, here it’s talking about arms. So I would regard that as a weak match. So if you prefer to use your own examples, the only requirement here is to make sure that you’ve got one strong, one medium and one weak example.

And this allows GPT-3 to understand exactly how you want these keywords to be classified. So that’s it. We’re done with the settings sheet. Once you’ve put your API key in and you’ve got these three examples, that’s all we need to do. Now we can focus all our attention here on the main sheet, which is Google Search Console data.

Now, what you’ll notice when you open this sheet is that there’s a new item added to the menu here, which is called Title Match AI. And that’s how you select the functions of the script that I’ve created, but we’ll come to that in a moment. What we need to do first of all, is just to pull in some data into the sheet. So assuming you have already installed Search Analytics for Sheets, all you need to do is click on Extensions and then select Search Analytics for Sheets from the dropdown list here, and then click here to open the sidebar. And then in a short moment, you’ll see that there’s a panel opens up on the right-hand side here. Just going to move myself out of the way. And what we’re going to do is then find that website.

If you’ve got a number of properties here, they’ll all be listed. So lovinglifeco.com. That’s the first thing we need to do is just tell it which website we want to pull the data from. Then we need to select a date range. And my suggestion would be to, assuming you’ve got a website that’s getting a reasonable volume of traffic from Google, I would suggest using a fairly short date range of probably no more than two months. So the most recent two months. So I’m going to put date range here. I’m going to click in here and select 2022, January, not January. Let’s go through. So September the 1st. So we’re going from the 1st of September 2022 to 10th of November. Here under search type, you’ve got options here for different sources of traffic. such as Google News and Discover, but we’re just going to leave that with the default option of Web. In the Group By column, this is important. We need to select Query and then you select Page. And what that’s going to do when it pulls the data in, it’s going to group all of the results first by Query and then by Page.

Now, if you want to, you can also add some filters. So, for example, filters you might want to include is if you’ve got a website that’s getting a lot of branded search traffic, so people searching for the business by name, you may wish to add a filter to exclude that. We’re not really interested in branded search queries, so you may wish to add a filter to exclude those from the results. And the other kind of filter that may be of interest is if you are focused on a particular country, is you can select one or more countries and only look at the data relating to those countries. I’m going to leave the filters blank in this case. Most of this traffic is coming from the UK. Aggregation type we can leave alone.

Rows returned. Now as I mentioned earlier, the free version of Search Analytics for Sheets will allow you to download 25,000 rows on the free plan. If you want to go up to 100,000 rows or more, then you need to go over to one of their very reasonably priced paid plans, which you might need to do if it’s a very big website. In this case, I’m just going to select 1000 rows. I don’t want too much data here. And then the last thing we need to do is tell it where we want that data sent to, which sheet. So in this case, I’m going to point it to the sheet I’m looking at the moment, which is GSC data. Now, there’s no requirement to do that. My script will work perfectly well if you are using a sheet with a different name.

So you can have multiple tabs open up here with different data on there, but in this example I’m just going to send that to this sheet that we’re looking at. Mode, I would leave that as Replace. That means that when the data comes in it’s just going to replace anything that’s already in the sheet. And if you want the most recent search query data then you can tick this little box here that says Include fresh data, and that will pull everything in right up to today. Last step is just to click this button here, Request data, and then we just have to wait for it to pull that data in from Search Console API.

And here we are. So let me just close this side panel and move myself back over there. So let me just format that so we can see that. Okay. There we go. Now what you can see, what we have here, one, two, three, six columns of data here. First column is query. So that’s a search query that’s brought the visitor into the website. Second column B is the page. That’s the URL of the page that is ranking on Google for that query. And then you’ve also got the number of clicks, the number of impressions for the period that we requested. So the last two months, the click through rate and the position in which that page is ranking. Now, there’s likely to be a very large number of rows in this. Let’s scroll all the way down to the bottom here. Okay, so there’s actually just about a thousand rows of data. Oh yes, I limited it to a thousand rows. That’s why we’ve got a thousand rows. Just remember that. But yeah, if you didn’t limit it to a thousand rows, you could have tens of thousands of rows in here. So we really just first of all need to filter this to look for the keywords that are of most interest to us.

So the first filter I’m going to apply If you have a look, you can see here that many of these search queries are already ranking in the top position, on the top three positions on Google. Now, if you’ve got a page that’s ranking on Google for a search term and it’s already appearing in the top few positions on Google, that’s not really something where there’s likely to be an opportunity. What we’re looking for are queries that are ranking from position, say, four or five to position 20. So I’m just going to apply a filter to this column. I’m just going to zoom out a little bit here just so I can manage this a bit better. I’m going to click here, filter by condition. I’m going to drop that down. I’m going to say less than 4. So that means everything with a position of less than 4. You can see that’s now filtered it. What I’m going to do is delete those from the sheet. Okay, and then we’ll turn that filter off. And then I’m also going to kick out any search queries where it’s ranking below position 20. So I’m just going to put another filter on here. I’m going to say filter by condition, and then I’m going to say greater than 20. And I’m going to delete those as well. And then get rid of that filter. Okay, so what we now have is a list of all the search queries that this website is ranking for, at least the first thousand anyway, and only those search queries that are ranking between positions four and positions 20 on Google.

Now the next thing I’m going to do is I’m just going to order these results by the number of impressions with the search queries with the largest number of impressions towards the top of the table here. So if I click here at the top of the column, I’m going to go sort sheet Z to A, and you can see it’s now put them in order of impressions. OK, so let me just zoom back into the sheet again so you can see this a bit more clearly. Right, so we’ve got queries, we’ve got our pages, clicks, impressions, and impressions are in order of largest to smallest, click through rate and position. OK, so now we’re ready to start using the script. So you click here on this option Title Match AI. And when you open that up, you’ll see that there’s three different scripts to use here. The first one is where we’re going to start, which is this one here called Fetch Titles. So what we’re going to do, we’re going to click on that.

Now, what you’ll notice is if this is the first time you’re using this script, you’re going to get a message saying “Authorization required. A script attached to this document needs your permission to run.” Now, that is just a standard Google procedure for any script that hasn’t been pre-authorized by Google. So you just click on Continue here, select your Google account, and you’re then going to get another warning from Google. And this could look quite scary. I assure you, and you’ve got a button here, Back to Safety, I assure you this script is not going to do any harm to anything. I don’t have access to any of your data or your Google account. So what you need to do here is just click on Advanced, and then I give you more warnings, and then just click on this option here, go to Title Match AI Unsafe. And then finally, just click Allow.

Okay, now, because we’ve had to go through that authorization process, the script hasn’t actually run. So you now need to go back to open this menu to now actually run the script here. But you will only ever need to do that once for this script. Now that you’ve approved it, you won’t get any more warning messages.

So we’re going to click here on Fetch Titles. And what you can see straight away is that some new columns have been opened up here. So we’ve got a column C title, column D match and a column D outline. Okay, and as you can see some data has just suddenly appeared in column C. So what’s actually happened here is that my script has crawled the page in each of these rows, the URL in each of these rows, and it’s pulled in the page title. Now it hasn’t had to use GPT-3 in order to do that. It’s literally just kind of scraped that off the page and pulled that in from the HTML. So that’s quite a quick process. And you can see as well that in many cases here, it’s the same article. This forearm pain article is ranking for a very large number of different queries. And so the script doesn’t have to crawl the page again to get the same article. If it’s already got the title for a URL, it will just copy that into the other rows here. So that’s quite a quick process that’s just pulling, but we need these titles in order for GPT-3 to now evaluate whether the search query is in fact a strong, medium, or weak match for the title of the page.

Now, just a little warning. If you are doing this with keyword data that has not been imported via search analytics for Sheets, the only requirement of my script that there must be in column A a heading of query, which is your search queries. It must be named query and the second column B must be called page. So if you’re bringing in search query data say from a tool like Ahrefs and you might be doing this looking at a competitor’s website, then you may find that the column headings are slightly different. So you just need to manually just change it to query and page. If you don’t, the script will throw up an error and will not work. Okay, so we now have query, the page, we also have the title of the page. And now what we’re going to do is let GPT-3 work its magic and evaluate all those search queries against all these page titles and classify them as to whether they’re strong, medium, or weak matches.

So we’re going to go back to the menu option here. We now go to item number two in the menu, which is Fetch Matches. And we click on that and then we just let this script run. Now, depending on how many rows of data there are in your sheet, this could take a couple of minutes or so, or maybe even longer if you’re doing this on thousands and thousands of rows. Now, this script will continue to run even if you go away and leave the sheet. So if you are doing this with, say, a thousand rows or more, no need to sit and hang around and wait for it and go off, have a cup of coffee or go and do some work and then come back to this a few minutes later, you should find that your data is in there. OK, and now the data has come in and you can see that this whole sheet has now been classified where each keyword has been evaluated against the page title and it’s been classified as either a strong match, a medium match, or a weak match.

Now, the next thing we’re going to do is to filter this sheet again and kick out any rows that are strong matches. We’re not really interested in keywords that are strong matches for the title of the article, because that doesn’t present an opportunity. So once again, we’re just going to click on this column, add a filter, and I’m going to deselect medium and weak, leaving only the strong ones, and then I’m going to delete all the strong rows from this sheet. Then we’ll turn the filter back on. So okay, so great. So now what we’re left with here, oops, let me just zoom back out again here. So what we’re left with here now are just the medium and weak.

Now let’s have a look at some of these. So, you know, what we’ve got here, actually, just see the search queries. Here we go. So we’ve got, for example, a search query here, will running three times a week help lose weight? the article title is “How many times a week should I run?” and that’s been classified as a weak match. I might have classified that as a medium match, but it doesn’t really matter too much. It’s not a precise match for the search query. Someone is specifically asking whether running three times a week will help to lose weight and that’s not the core topic of the article here. We have a look at another one here, “foam roller for knee pain” in the article here is knee pain, three top tips, stretching, foam rolling, and more. So we can see that that’s a fair classification as medium because it does mention a foam roller for knee pain. But we can see here that a dedicated article specifically about using a foam roller for knee pain would be warranted here and will probably help this help that article rank in a stronger position on Google.

So you know, that is a process going through all those keywords, determining whether they’re a strong match or a medium match or a weak match for the title of the article is something that had you done this on even a thousand rows would have taken a considerable amount of time. GPT-3 has just done that in a matter of moments. And then finally, there’s one other feature here. So if we’re now going to go and create some new articles, based on these keyword opportunities that we’ve got here. Now you can pop over here and you can see this Outline column here.

So we’re just going to use the last option here, which is Fetch Outlines. And if you click on that, and then again, just wait a few moments for GPT-3 to bring in the data. And there we go. And so, you can see that it’s now given us a content outline for that particular search query. So if we go and have a look at example here, so here we go. So we’ve got a search query here. What is deep tissue massage? And if we go and have a look, so it’s got one introduction, two, how does deep tissue massage work? Three, what deep tissue massage treats? Four, things to consider when getting deep tissue massage, and five, a conclusion. So that’s now giving you a kind of a skeleton framework for then going off and creating that article.

Now, as I mentioned earlier, you’re not restricted to only using this on websites that you own yourself or your clients’ websites where you have access to search console data. You can also use this sheet with keyword data from any source, such as Ahrefs, Keyword Explorer or Site Explorer. And so you could bring in all the keywords that a competitor is ranking for on Google. And in that case, if you do that, you obviously won’t have the impressions, but you can then use kind of search volume as a surrogate for the impressions data. The other thing to mention is that this script works equally well for websites that are, say for example, e-commerce websites, where the pages that you’re evaluating are category pages or product pages, it doesn’t have to be articles on topics. You’ll find that this works very well for other types of websites also.

Oh yeah, one other thing I was going to mention was just about the cost of using GPT-3. So as I said earlier, when you register with GPT-3, you’re going to get $18 worth of free credits. But once you’re beyond that, I’ve calculated the cost of using their API to be approximately somewhere in the region of around £2.80 sterling or around $3.20 US currency per 1,000 rows. So to do 1,000 rows of keyword matches will cost around about £2.80, and 1,000 content outlines will also cost around about the same amount. So it’s quite affordable unless you’re going to be doing this on hundreds of thousands of rows, but that’s a good reason to make sure that you are filtering this data before you start running the GPT-3 script because you only really want to do this on keywords where it’s actually going to be helpful to you.

All right, well, I hope that’s explained this to you clearly. Unfortunately, if you do come across any issues, I’m not able to provide individual support for these free scripts. But if you reach out to me on Twitter, if you do have a question, I’m more than happy to help out. All right, take care, bye now.