Google Sheets Keyword Research
Updated: January 6, 2022 I have a website called You Autocomplete Me.
It's a tool I built that allows online marketers to quickly extract Google's autocomplete data.
It allows you to enter a partial search (like "best dog for") and get a lot of potential keywords in seconds based on extending the search with additional characters:
- "best dog for a"
- "best dog for b"
- "best dog for c"
You can narrow your search using:
- 6 search types in
- 191 geographic regions and
- 228 languages
It does all of this by querying the Google Autocomplete API.
For 'Little' searches it completes 36 requests (A-Z and 0-9).
For 'Big' searches it completes 676 requests (Aa - Zz).
It's a great tool to find keywords quickly… so I made it into a spreadsheet using Google Sheets:
Google Sheets Keyword Research Template
Find thousands of keywords in seconds using Google's Autocomplete API.
Get SheetThis sheet has all of the functionality of the online tool but you get to have your own version on your own Google Drive!
Completing Keyword Research
Complete the five fields pointed to by the ➜ character:
- Enter a partial keyword (let's stick with the example from above: "best dog for")
- Choose a search type (Google, YouTube, Images, News, Shopping, Books)
- Choose a location (from the 191 geographic regions listed)
- Choose a language (from the 228 languages and dialects listed)
- Decide whether you want a 'Little' search or a 'Big' one
There are a lot of dropdown options for 'Location ➜' and 'Language ➜'.
You can quickly find options by typing into the dropdown cell to see a shortlist of matching options:
Then click the AUTOCOMPLETE ME button to fetch the autocomplete data from Google.
The first time you click a button in this sheet you'll need to authorize the underlying script for the following permissions:
Here's a script authorization guide if you're unsure about this process.
While the script runs you'll see a small popup at the top of the screen that you can ignore:
When the script is finished the autocomplete data should appear in the sheet:
If you want to save this data you can click the EXPORT ME button.
This creates a new sheet with just the data from the most recent search and includes the size of the search (LITTLE or BIG) in the name:
This can be exported and uploaded to other tools to determine search volume and other relevant metrics.
The Google Autocomplete API puts a limit on how many searches you can do before it stops providing the results for a while.
Based on my testing this is around 15-20 little searches or 1-2 big searches.
When this happens the sheet will stop working so take a 30 minute break before you try again.
FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.
How It Works
So far you've learned what this tool is and how to use it.
Now comes the technical explanation of the Apps Script code that does all of the hard work.
The most interesting part of the code is fetching the data from the Google Autocomplete API.
The other stuff is getting data out of the sheet and putting it back in based on a specific format. That's cool but it's not the engine of the sheet.
You can review the code in your own version of the sheet, but for this explanation all you need to know is that the searchTerms variable is an array that's either:
- Little = ["a", "b", ..., "8", "9"]
- Big = ["aa", "ab", ..., "zy", "zz"]
With that context, here's the code:
const output = [];
searchTerms.forEach(searchTerm => {
const url = `https://suggestqueries.google.com/complete/search?` +
`q=${query} ${searchTerm}` +
`&client=chrome` +
`&ds=${searchType}` +
`&gl=${country}` +
`&hl=${language}`;
const response = UrlFetchApp.fetch(url, { "muteHttpExceptions": true });
const result = JSON.parse(response.getContentText());
const keywords = result[1];
//For each keyword in the array...
keywords.forEach((keyword, i) => {
const heading = i === 0 ? searchTerm.toUpperCase() : '';
output.push([heading, keyword]);
});
});
You iterate over the searchTerms and make a call to the autocomplete API for each combination of the user-entered query and the current searchTerm with the additional search type, location, and language data points appended to the URL.
After parsing the response the result you get looks like this:
const result = ['best dog for a',
['best dog for apartment',
'best dog for apartment living',
'best dog for allergies',
'best dog for anxiety',
'best dog for a family',
'best dog for autism',
'best dog for apartment australia',
'best dog for australian climate'],
['', '', '', '', '', '', '', ''],
[],
{
'google:clientdata': { bpc: false, tlw: false },
'google:suggestrelevance': [1250, 1050, 800, 601, 600, 552, 551, 550],
'google:suggestsubtypes':
[[Object],
[Object],
[Object],
[Object],
[Object],
[Object],
[Object],
[Object]],
'google:suggesttype':
['QUERY',
'QUERY',
'QUERY',
'QUERY',
'QUERY',
'QUERY',
'QUERY',
'QUERY'],
'google:verbatimrelevance': 851
}];
The keywords are located at index one of the result array.
You then iterate through the keywords adding them (along with the heading) to the final output array which is what is placed back into the sheet for the user to see.
FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.
