Watch a short demo video (3 mins)
You may have pages on your site ranking in the top positions on Google for search queries closely related to your content topics.
For example, this page titled, ‘Forearm pain when curling? 3 reasons why!‘ appears in the No.1 position on Google for the search query “forearm pain when curling”.
However, that same article appears on Google for 500+ other search queries in positions 4 through 20.
Google is not only looking for articles with titles exactly matching the search user’s query. It will also show pages on closely related topics or where the search query may be mentioned in a sub-heading or deep within the body of the article.
Most Google users ignore search results below the top three positions. They may also regard the article irrelevant to their search if the title doesn’t correspond closely to their search query.
Therefore, we want to identify keywords with a high number of impressions but with a weak match to our page titles. We can then create new content that closely matches the topic of the search query to drive more traffic to our site.
Evaluating this data manually is a slow and painstaking process. We would have to analyse every keyword for which the site appears on Google and judge the degree to which it matches the page title. Until now, this task has been almost impossible to achieve at scale.
This free Google Sheets script uses GPT-3 to automatically classify keywords by the degree to which they match your content topics. You then filter any weak keyword matches with a high number of impressions and create new content dedicated to that topic.
The script will also create content outlines for the new articles based on the search queries identified as keyword opportunities.
V2.1 – 28th Nov 2022
Removed row limit
Added the ability to run processes for any number of rows. Rows are now processed in batches of 30 to avoid Google’s script execution limits.
Added a Process Log
Added a “Log” sheet to show the status of processed requests.
Added ‘Cancel process’ feature
New menu item to terminate any requested processes before completion. This is handy if you have mistakenly requested API requested for thousands of rows in error.
Added email notifications
Receive an email notification once a process has completed by entering your email address in the ‘Settings’ sheet.
You can use this sheet with any data source as long as it includes a list of keywords and the ranking page URLs. You can obtain this data for any site (e.g. a competitor’s site) using Ahrefs’ Site Explorer or other similar keyword research tool.
The Google Sheet script is free to use. The only cost is in OpenAI tokens which, by my calculation, works out to approximately GBP £2.80 (USD $3.30) per 1,000 keyword matches or content outlines.
I have now released V2.1 of this script capable of processing any number of rows. Google imposes a limit of 10 million cells in a Google Sheet which should be more than sufficient for most uses cases.
Only page titles are shown in Google’s search results. The fact that you have an H1 heading that is a better match for the ranking keyword is not much help to Google’s users. They will be evaluating the relevancy of your content primarily on the title alone. That is why the script does not use H1 headings.
Google will display an “unsafe script” warning when using the script for the first time. This is a standard message for any script Google has not pre-approved. I can assure you the script is entirely safe to use. Nor will I have any access to your keyword data or API key.
If you still have concerns regarding privacy or security, please do not use this script.
Unfortunately, I am not able to provide any support for free scripts. However, I am happy to answer any questions via Twitter. If you find the script useful, I would also love to hear from you.
Download my free script and make a copy of the sheet to your own Google account. Full instructions are provided in the sheet named “Instructions”
This is a free extension for Google Sheets that uses the Search Console API to import your keyword data into Google Sheets. You will only require a paid plan if you wish to import more than 25,000 rows of data. Alternatively, you can use any other source of keyword data.
Looker Studio (Free – formerly Google Data Studio)
Looker Studio provides a free connector to Search Console API with unlimited data. Simply create a table with the data you need and export it to Google Sheets.
You will need to register an API account with OpenAI to gain access to GPT-3. OpenAI provide $18 worth of free credits to new users.
Check out some of my other scripts and tools for marketers using OpenAI’s technology.
Watch this video (33 mins) for detailed instructions on how to use the script or read the full transcript below.