22/07/2022, SEO
If so, you will be all too familiar with the burden of having to go through a long list of search queries to determine which terms are likely to result in a sale or enquiry for your business.
Fed up having to trawl through ‘Search Terms’ reports to figure out which queries should be added to your list of negative keywords?
This free script automates that process for you.
1) Specify the type of business e.g. “swimming pool installers”
2) Specify the type of product/service the business offers e.g. “Supplies and installs in-ground, heated swimming pools for customers based in London.”
3) Paste a list of keywords into the Google Sheet.
4) The script then classifies each keyword as having either a “Strong”, “Medium” or “Weak” conversion potential.
I have tested this on many different businesses and keywords and found the results to be accurate. Significantly more accurate than assigning the task to a VA who may not have enough familiarity with the business or its target market to make such determinations.
The only cost is GPT-3 credits. OpenAI provide $18 worth of credits free to new users. This is enough to classify approximately 3,000 keywords. After that, the cost works out at approximately $6 (£5) per 1,000 keywords.
1) The first step is to register an account at OpenAI.com.
Upon registration, you will be given $18 worth of free API credits.
Once registered, grab a copy of your secret API key and save it to your device.
2) Download a copy of my free Google Sheet and script.
Be sure to tick the ‘Copy Comments” box when copying the sheet.
You will see two tabs named, “Keywords” and “Settings”.
3) Enter your API Key from OpenAI in cell B2 of the “Settings” sheet.
4) Enter the type of business you are working on in cell B3 of the “Settings” sheet.
5) Add four example keywords in cells B5, B7, B9 & B11 of the “Settings” sheet.
Be sure to give keyword examples which offer “strong”, “medium” and “weak” conversion potentials. These examples will be used to train the model to ensure it classifies your keywords correctly.
6) Add the corresponding classifications into cells B6, B8, B10 & B12. Enter the values as either “Strong”, “Medium” or “Weak” as appropriate for the keywords you entered in the cells above.
7) In the “Keywords” sheet, paste or upload a list of keywords you wish to categorise into column A.
8) I have already copied the formula into all of the rows in column B, “Conversion Potential”.
After a short delay, you should start to see your keywords being classified by GPT-3.
No API request will be made for empty rows that do not contain any keywords.
If you need to add more rows to the sheet, be sure to copy down the formula, “=GETPOTENTIAL([cell reference])” into the “Conversion Potential” column.
9) I have added an option to “Save Values” in the menu at the top of the sheet.
This feature converts all formulas to plain text values. It avoids making unnecessary API requests each time the sheet is modified or reloaded.
You will need to grant permission when using this feature for the first time. You can safely ignore any messages from Google about “unsafe scripts”.
Do take a look at the script behind this function by clicking Extensions > Apps Script from the main menu and feel free to adapt it to different use cases.
Although I can’t offer individual support for this free script, please let me know if you run into any bugs or have thoughts on how the script could be adapted.
Check out this list of other GPT-3 scripts I have released and feel free to follow me on Twitter to hear about new releases.
I will be giving a talk at BrightonSEO in October 2022 on how to make use of machine learning models for search marketing. I hope to have some new scripts ready to release for those attending the talk.
Enjoy!