With the Content Marketing Institute’s 2018 Benchmarks, Budgets, and Trends report showing that 91% of B2B companies actively use content marketing to drive clients, while of the nonusers, 54% plan to join the rest, developing a powerful keyword strategy has become a life-and-death matter.
Naturally, this has been leading more and more companies and content creators towards seeking out effective keyword research tools.
However, as incredible as paid keyword research tools are, the price tag, usually a couple hundred bucks a month, might be insurmountable for small business owners operating on a shoestring budget. But you’re not stuck with no other choice but to pay.
In this post, I’m going to walk you step by step through the process of creating a professional, viciously efficient keyword strategy that will end up costing you next to nothing—even if you’re a complete newbie. And, yes, this tutorial contains NO affiliate links.
By the end of this 4000-word crash course, you’ll get a solid grasp of:
- Compiling a keyword seed list
- Collecting keyword suggestions
- Weeding out duplicates and irrelevant terms with spreadsheet functions and formulas
- Fetching data on search volume (the average number of monthly searches for a particular keyword in a search engine) and cost per click (CPC) superior to that of Google Keyword Planner
- Analyzing the traffic potential and competition for a search phrase
- Grouping keywords into clusters.
For just one search term picked as a running example, the end product (2618 highly relevant phrases boiled down from 14400 suggestions) cost me a mere 73 cents.
Sounds too good to be true? See it for yourself.
Step 1: Create a keyword seed list
Any worthwhile keyword research starts with compiling a seed list—the initial pool of broad, generic terms, aka seed phrases, most relevant to your topic.
Let’s say you plan to start an online gift shop. Unless you’re launching the new Amazon of the gift market, your list would be comprised of 20 to 30 terms categorizing your niche—e.g., “birthday gifts,” “anniversary gifts,” “gifts for her,” etc.
But how do you pull it off? That’s where Ubersuggest and Wikipedia come into play.
If you want to compile a seed list in less than 10 minutes without lifting a finger or having to brainstorm ideas on your own, Ubersuggest is the best thing since sliced bread.
In addition to being a free tool for generating keyword suggestions, it shows you a quick view of the largest competitors in just a few clicks.
Head to Ubersuggest, enter the keyword defining your niche—in my case, “gifts”—in the search bar, and hit Search.
On the left, go to the Keyword Ideas tab.
Once there, you’ll get a bunch of suggestions. Skim through the list to pick the terms which might be a good fit for your seed list.
However, as the saying goes, “Good artists copy, great artists steal.”
You see, having invested heavily in keyword research, your competition, if analyzed properly, can provide you with a roadmap to the target market, showing you which seed phrases pay off and which don’t.
Fortunately, doing reconnaissance on your rivals is easier than shooting fish in a barrel. Oftentimes, seed keywords constitute the backbone of a navigation structure, meaning that examining menus—especially submenus—may reveal to you the seed terms a website aims for.
Now, this simple trick works extremely well in some niches while being impractical in others. But when it does, the payoff can be tremendous.
If you ever find yourself working in a new niche filled with industry jargon and buzzwords, study the relevant Wikipedia pages as if your life depended on it.
Despite me being totally clueless about the gift market, Wikipedia provided me with a myriad of potentially lucrative seed phrases, highlighted in the screenshot, which would’ve never crossed my mind.
Once you’ve crafted your seed list, move to the next step.
Step 2: Collect keyword suggestions
Before we get started, a few ground rules on the methodology: You should rinse and repeat the whole process outlined in the rest of the article for every seed term in your list—from now on, the phrase “gifts for girlfriend” will be used to showcase the blueprint.
Also, unless a repo man has his own bedroom in your house, do not hesitate to pay for a subscription plan to Ahrefs, SEMrush, or Moz. This tutorial shows what to do when your budget for content marketing was cut to the bone and, by no means, claims to outperform the cutting-edge premium tools.
This step involves collecting as many keyword suggestions as possible to wring out maximum value out of the free alternatives suggested below.
But, with the lion’s share of them being so utterly useless it hurts, the following three will be your bread and butter: Google Keyword Planner, Keyword Sheeter, and Answer The Public.
One last thing: professional keyword research involves working extensively with spreadsheets, so to avoid any compatibility issues, everything will be done in Google Sheets.
Let’s dive right in.
Google Keyword Planner
For years, Google Keyword Planner (GKP) has been the go-to tool for free keyword research.
However, ever since Google started throttling the data from GKP for low-spending AdWords accounts – or even worse, was caught hiding some keywords – relying on it as the sole source of data can backfire when you trust it too much.
To use the tool, you need to set up a Google Ads account. But as hundreds of articles covering the process in detail have already been written, there’s no need to waste your time by beating a dead horse.
Once you’ve signed up and logged in to Google AdWords, click on the wrench tool and select “Keyword Planner”.
Then, choose the “Discover New Keywords” section, enter your seed keyword, and hit “Get results”.
The search returned 1597 suggestions but let me show you a way to dig deeper by adding auxiliary phrases that are highly relevant to the initial seed keyword–you can use up to 10 terms in any one go.
First things first, click on “Avg. monthly searches” to sort the results by search volume in descending order.
Now, copy the first nine keywords into the search field—along with the initial term—and repeat the search.
The simple technique bagged me an extra 1670 relevant keywords, double the amount GKP had initially returned.
Once there, click the “Download Keyword Ideas” button at the top left corner of your screen and move only the suggestions to Google Sheets.
After that, wave goodbye to GKP and meet the next tool— Keyword Sheeter.
Being a free and ridiculously easy-to-use tool, Keyword Sheeter (yes, that’s the name) makes it possible to dramatically expand your keyword list in just one fell swoop.
First, create a simple spreadsheet in Excel and save it as a CSV file. Then, compile a list which includes the seed keyword, plus its variations: the singular and plural forms, abbreviations, acronyms, initialisms, jargon, slang, etc.
Now, head to Keyword Sheeter, upload the CSV to the website, hit the search button to run the tool, and leave the tab open for an hour or two.
The tool won’t stop parsing data by itself, so once a few hours have passed, hit the “Stop Sheeting” button, download the suggestions by clicking on “Export Data” and move them to your Google Sheet.
For the record, the tool managed to unearth 6077 suggestions within just forty minutes.
Finally, proceed to Answer The Public.
Answer The Public
Besides being an astounding tool for analyzing customer intent, Answer The Public may aid in spotting extra search phrases which might have slipped through the cracks.
Just go to the website, type in your seed keyword, and click the “Get Questions” button.
Once there, click on “Download CSV,” and copy the results into your spreadsheet.
The raw data collected with these three tools should be more than enough.
However, if you’re dying for more, check out Sonar for gathering phrases from Amazon and KeywordTool.io for fetching search terms from Bing, eBay, App Store, and Instagram—don’t forget Ubersuggest as well.
At this point, provided you followed the guidelines, your list may range from a few hundreds to tens of thousands of keyword suggestions (14400 in my case):
Now, it’s time to sort this giant mess out.
To streamline the research and optimize your expenses, purge the list of the duplicates and irrelevant keywords which have no practical value whatsoever.
Step 3: Remove duplicate keywords
Collecting suggestions from multiple sources will inevitably result in your list being laced with duplicates.
Typically, most articles online would go only so far as to suggest you applying the Unique function in Google Sheets, which creates a list of unique values, discarding repeated entries.
But this approach doesn’t cut the mustard as it leaves out the hidden duplicates which, if left unaddressed, will end up devouring countless of your precious hours in the long run.
That being said, you need first to expose the completely invisible layers of the recurring phrases, followed by the Unique function doing the dirty work.
First, let’s deal with the date-based duplicates—e.g, “Christmas gifts for girlfriend 2017” and “Christmas gifts for girlfriend 2016”—using the Find and Replace tool (the name’s self-explanatory).
In Google Sheets, click the “Edit” button to see a dropdown menu and hit “Find and replace”.
Once there, do the following:
- Copy “2010|2011|2012|2013|2014|2015|2016|2017|2018” into in the Find field
- Enter “2019” next to “Replace with”
- Highlight the range containing all the keywords or type in “A2:A”—given your list starts in cell A2
- Choose the “Search using regular expressions” option—that’s crucial
- Hit “Replace all”.
But that was just a warm-up.
Singular and plural nouns
Compare the phrases “gift for your girlfriend” and “gifts for your girlfriend.” Despite being completely identical, except for the different forms of the noun “gift,” the Unique function fails to recognize them as duplicates.
The issue can be tackled by turning all the singular nouns in the seed phrase into their plural counterparts, or the opposite—for instance, turning “gifts” into “gift,” etc.
To turn all the plural nouns into the singular, again, simply use the Find and replace tool.
Enter the plural form into the Find field while the singular next to “Replace for”. Specify the range and hit “Replace All”.
However, the tool lacks the flexibility to pull off the reverse swap, something you might need, considering that most keyword research tools completely omit apostrophes—in your list, the phrases such as “gifts for girlfriend’s parents” and “gifts for girlfriend’s birthday” won’t have one.
That being said, here’s the custom formula tailored to turn the singular nouns into their plural forms and vice versa (sorry in advance):
Syntax: =ARRAYFORMULA(IF(REGEXMATCH(keyword column range, "(\s|^)(singular form)(\s|$)"), SUBSTITUTE(keyword column range, "singular form", "plural form"), keyword column range))
If you’ve never worked with spreadsheets, give me sixty seconds to clue you in before you close the article in a blind panic.
Given the keyword list is located in column A and starts in cell A2, the formula looks like this:
=ARRAYFORMULA(IF(REGEXMATCH(A2:A, "(\s|^)(gift)(\s|$)"), SUBSTITUTE(A2:A, "gift", "gifts"), A2:A))
In plain English, you tell it to sift through the list, find every single occurrence of a phrase containing the word “gift,” and replace it with “gifts.”
For the curious, the symbols around “gift” in the REGEXMATCH function help specify that we need to pick “gift” as a separate noun and leave out the cases of it being a root word—for instance, “gifts”.
Back to the trenches.
For convenience, select the empty cell next to the first keyword in the list. Then, type in the formula and hit enter.
Now, to separate the results from the formula, highlight the column by right-clicking on its header and click “Copy”.
Then, right-click on the header of an adjacent empty column, click on “Paste special,” and choose “Paste values only”.
For the uninitiated, to erase the redundant columns, highlight them, right-click anywhere on the selected area, and choose “Delete columns”.
Whew, that was intense but well worth the effort.
Purge your list of duplicates
Finally, after you’re done laying the groundwork, weed out the duplicates altogether by applying the Unique function.
Syntax: =UNIQUE(keyword column range)
Again, given the range remains the same, here’s how it looks:
Now, type the function in the empty cell next to your first keyword in the list and hit enter.
After that, extract the results from the function and remove the useless columns following the process outlined above.
Congrats on delivering the coup de grace to the first line of defense—my list got narrowed down by 1806 phrases.
Step 4: Remove unrelated keywords
If you think we’re done with the hardest part, you’re about to be severely disappointed as your next step involves removing unrelated keywords to the seed phrase.
As the built-in filters lack flexibility, let me show you a few simple, yet incredibly powerful functions for filtering data while doing keyword research.
Remove unrelated keywords using the REGEXMATCH function
Without beating around the bush too much, in conjunction with filters, the function returns only the phrases which include the specified words (case sensitive):
Syntax: =REGEXMATCH(keyword column range, "keyword 1|keyword 2|keyword 3…")
Translation into human: you tell the function to run through the list within the defined range and show only the phrases containing keyword 1, or keyword 2, or keyword 3—the beauty of it is that you can use as many keywords as you want simultaneously using the separator (|), which stands for “or”.
Right off the bat, eliminate all the navigational, branded terms implying that a user intends to visit only a particular website (for example, Amazon, Pinterest, Reddit, Ebay):
Now, let’s break down applying functions with filters:
- Click the filter icon on the toolbar
- Go to the dropdown menu at the top of the keyword column
- Choose “Filter by condition” and pick “Custom formula is”
- Enter the formula and click “OK”.
Once there, remove all the filtered rows.
For the novices, in order to do that, highlight all the rows: click the first row, hold down the Shift key, and click the last row.
Then, right-click anywhere on the highlighted area and choose “Delete rows”.
That was merely one way to apply the function. Now, harness its potential fully by creating your own variations tailored to your niche.
But before leaving you in the mean streets of spreadsheets, here’s a few universal examples.
Let’s say the imaginary gift shop will target solely the US market. In that case, the function comes in handy to eliminate irrelevant location keywords:
=REGEXMATCH(A2:A, "australia|ireland|canada|india|zealand|philippines|pakistan|malaysia|germany|spain|sri lanka|(\s|^)(gb|nz|uk)(\s|$)")
By the same token:
=REGEXMATCH(A2:A, "ottawa|toronto|calgary|montreal|london| birmingham|liverpool|sydney|wellington|dehli|mexico|paris|berlin|prague")
And once again:
The cherry on top: erase the keywords with no commercial intent signaling to you that a user won’t buy a thing from you:
=REGEXMATCH(A2:A, "free|diy|homemade|handmade|at home")
But there’s more to that.
Remove unrelated keywords by pairing the NOT and REGEXMATCH functions
Simply put, the NOT function reverses what REGEXMATCH does, returning only the results which don’t contain the specified keywords.
Syntax: =NOT(REGEXMATCH(keyword column range, "keyword 1|keyword 2|keyword 3…"))
With my seed phrase being “gifts for girlfriend,” given that the range remains the same, my version looks like this:
Now, run the function by repeating the steps outlined above.
Once there, go through the filtered results line-by-line to separate the wheat from the chaff as some of the terms which have nothing to do with the seed phrase will, surprisingly, be highly relevant—that applies especially to informational websites.
For newbies: hold down the Command key (for Mac) or the CTRL key (for PC) and single-click on a row to select multiple rows.
Yes, the whole process might feel like getting blood from a stone, yet working with a cluttered list eats up disproportionally more time—for the record, the two functions have almost halved my list, down to 7337 phrases.
Step 5: Collect search volume data
Since your list has no value without search volume data, finding a free, or at most reasonably affordable, way to gather it becomes a paramount priority.
Provided you feel reluctant to shell out a hundred dollars for Ahrefs or the like—which you should—you’re basically left with just two decent options: SearchVolume.io and Keywords Everywhere.
Being one of the few 100% free tools left on the market, SearchVolume.io should be considered only as the last resort if, for some bizarre reason, even dropping a few bucks on Keywords Everywhere seems unaffordable.
To use the tool, copy your keywords into the field – but only up to 800 keywords at one go – and click “Submit”.
Once there, hit the “Export” button to download the results.
Rinse and repeat if your list exceeds 800 keywords and move the data into a fresh spreadsheet.
As you may already have noticed, the results look pretty messed up. However, no issue can’t be surmounted with a drop of spreadsheet magic.
In Google Sheets, highlight the column, click on the Data tab, and pick “Split text to columns…”.
Once there, next to “Separator,” hit the dropdown menu and choose “Semicolon”.
There you have a neat list.
Now, it’s time to bring in the heavy artillery.
Keywords Everywhere, an extension for Google Chrome and Mozilla Firefox, is, hands down, the most cost-effective way to gather search volume as of this writing. Unfortunately for you, on October 1, 2019, the tool ceased to be free.
However, with the laughably low price tag of just one dollar for search volume and CPC data for every 10000 keywords, it makes it possible to conduct in-depth, exhaustive keyword research for just ten to twenty bucks—my seed keyword ended up costing me a measly $0.73.
First, head to the official website, install the add-on, and click “Get API Key”.
Sign up using your email, follow the simple instructions in the letter you’ll receive, and buy some credits.
Next, in your web browser, click the extension icon and choose “Import Keywords”.
After that, dump all the keywords—up to 10000 at one go—from your list into the field.
Export the results and move the data to a new Google Sheet.
Before we proceed to the last step, grouping keywords into clusters, eliminate the keywords with zero search volume, which indicate no searches for any given phrase.
Step 6: Remove keywords showing zero search volume
Full disclosure: as ridiculous as it might sound, zero search volume keywords can actually be a source of high-quality traffic, oftentimes overlooked by the competition—provided you’ve learned how to spot the subtle cues, but even then, it’s still quite hit-or-miss.
But that’s a topic for another day.
As for now, let’s ditch those keywords for good using a simple filter.
First, open filters. Then, in the dropdown menu of the search volume column, choose “Filter by condition,” pick “Less than,” enter “10,” and hit “OK”.
Now, remove all the filtered rows—my list was slashed by 4661 keywords, down to just 2618.
Step 7: Group the keywords into clusters
Finally, with the finish line in sight, you’re only left with fishing out the keywords from the list and grouping them into clusters (a group of phrases capable of standing as a separate page on your website).
Basically, grouping keywords means skimming through the list and picking the phrases which stand out using the two criteria:
- It makes sense to create a page based on the keyword
- Little or no competition.
Sure, there are as many opinions on how to do that as there are experts, so let me show you a simple method to group keywords into clusters.
To streamline the process, sort the search volume column in descending order.
If you’re starting out with no budget to face the pitiless rivalry, focus on the least popular keywords (aka long-tail phrases) with the lowest search volume.
On the screenshot below, the highlighted search terms have the potential to make up a cluster:
Next, pick a keyword and scrutinize whether it can lead to something. Let’s check the phrase “welcome back gifts for girlfriend.”
First, filter the keyword column using our good old friend, the REGEXMATCH function, to capture similar phrases:
Analyzing traffic potential
Now, assess the traffic potential. Realistically, for a new website, shooting for the stars equals getting slaughtered, so aim for the low end.
However, if securing visitors takes elbowing your way through the strife competition, it’s not worth the candle.
As the data on the competition offered by Keywords Everywhere lacks the objectivity to accurately reflect the real picture, you’ll have to evaluate it on your own.
Analyzing the competition for low-volume keywords
It’s simple as ABC: Look up the term in Google and meticulously study the page one results, paying special attention to the headlines which indicate the main keyword a page targets. In my case, with virtually no competition on the horizon, the phrase got the green light.
Analyzing the competition for high-volume keywords
Trying to rank for popular terms often entails having some company. To avoid fighting a steep uphill battle you’re bound to lose, examine the backlink portfolios (the quantity and quality of the links referring to a page or domain) of your competition to see if you can realistically challenge the status quo in Google.
To illustrate the point, let’s check the seed keyword “gifts for girlfriend” which users search, on average, 27100 times per month.
How do you do that without paying a dime? Quite simply: the Ubersuggest’s Backlinks checker tool.
First, once you’ve spotted a threat in Google, assess the page itself. In the Backlinks tool, copy the link to a competitor’s page into the field. Then, choose “URL” from the dropdown menu next to the search field and run the tool.
Hmm, the 74 backlinks and 10 referring domains don’t seem like anything intimidating. But that’s until you inspect the domain itself.
Switch to “domain/*” to see the backlink portfolio of the whole domain.
Basically, just as a rising tide lifts all the boats, the giant website portfolio boosts the position of the examined page in Google, making it painstakingly difficult for you to reach the top—even with superior content.
And I know you’re thinking, “That sounds like too much trouble.” Sure, but try to answer a simple question: what’s better – spending five minutes on testing the waters or wasting your precious resources on crafting doomed-to-fail content? Exactly.
Having run the background check, move the keyword group to your spreadsheet for clusters. With the headlines jotted down, you get the end product for one seed keyword:
Note: Don’t forget to remove the phrases from the keywords list.
So, there you have it.
Sure, the guide didn’t even scratch the surface of the pure voodoo magic that is achievable with spreadsheets, but you now have all the information to develop a professional, market-driven keyword strategy to propel your online business to new heights for the cost of just a few avocado toasts.
In addition, wave goodbye brainstorming content ideas for years to come.
Sure, it takes some time to climb the short learning curve, but from then on, you’re golden.