With the Content Marketing Institute’s 2018 Benchmarks, Budgets, and Trends report showing that 91% of business-to-business (B2B) companies actively use content marketing to drive clients, while 54% of nonusers 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 toward seeking out effective keyword research tools.
However, as incredible as paid keyword research tools are, the price tag (typically a couple hundred bucks a month) might be insurmountable for small business owners operating on a shoestring budget. But don’t think you’re 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 nearly 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 final product (2,618 highly relevant phrases boiled down from 14,400 suggestions) cost me a mere 73 cents.
Sounds too good to be true? Read on below and 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 should be comprised of 20 to 30 terms categorizing your niche—for instance, “birthday gifts,” “anniversary gifts,” “gifts for her,” etc.
But how do you put it all together? That’s where Ubersuggest and Wikipedia come into play.
If you want to compile a seed list in less than 10 minutes without breaking a sweat 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, Ubersuggest provides you with a quick view of the largest competitors in just a few clicks.
Head over to Ubersuggest, enter the keyword defining your niche—in my case, “gifts”—in the search bar, and hit the “Search” button.
On the left sidebar, go to the Keyword Ideas tab.
Once there, you’ll get a bunch of keyword suggestions. Skim through the list and 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 given website targets.
Now, keep in mind that this simple trick works extremely well in some niches while being completely useless 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 on to the next step.
Step #2: Collect keyword suggestions.
Before we get started on this step, here are 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 instructions.
Also, unless the 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 is cut to the bone, but by no means does it claim to outperform cutting-edge premium tools.
That being said, this step basically involves collecting as many keyword suggestions as possible to wring the maximum value out of the free alternatives suggested below.
But with the lion’s share of such sources being so utterly useless it hurts, the following three tools will end up being your bread and butter: Google Keyword Planner, Keyword Sheeter, and AnswerThePublic.
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.
In order 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 have signed up and logged into 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 1,597 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 one go.
First things first, click “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.
This simple technique bagged me an extra 1,670 relevant keywords, double the amount GKP had initially returned.
Once you have done this, click the “Download Keyword Ideas” button at the top left corner of your screen and move the suggestions to your spreadsheet.
After that, wave goodbye to GKP and meet the next tool—Keyword Sheeter.
As 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.
To start with, you need to compile a list containing the seed keyword and its variations: the singular along with plural forms, abbreviations, acronyms, initialisms, jargon, slang, etc.
Once there, copy the list into the input field, click the “Sheet Keywords” button, and leave the tool running 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 “Export Data,” and move them to your spreadsheet.
For the record, the tool unsearched 6,077 suggestions within just forty minutes.
Finally, proceed to Answer The Public.
Answer The Public
Besides being an astounding tool for analyzing customer intent, AnswerThePublic 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 “Get Questions.”
When you get your results, click “Download CSV” and copy the results into your spreadsheet.
The raw data collected with these three tools should be more than enough.
At this point, provided you followed the guidelines, your list may range from a few hundred to tens of thousands of keyword suggestions (14,400 in my case):
Now it’s time to sort this giant mess out.
To streamline the research and optimize your expenses, get rid of duplicates and irrelevant keywords with 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 only go so far as to suggest that you apply the UNIQUE function in Google Sheets, which creates a list of unique values while 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 hours in the long run.
That being said, first you need to peel off the invisible layers of the recurring phrases, followed by applying the UNIQUE function to do 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 is self-explanatory).
In Google Sheets, click the “Edit” button and choose “Find and replace” from the drop-down menu that appears.
Once there, do the following:
- Copy “2010|2011|2012|2013|2014|2015|2016|2017|2018|2019” into in the “Find” field.
- Enter “2020” next to “Replace with.”
- Highlight the cell range containing all the keywords or type in “A2:A“—provided your list starts in cell A2.
- Choose “Search using regular expressions”—that’s crucial.
- Click “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 of the seed phrase into their plural counterparts, or vice versa—for instance, converting “gifts” into “gift.”
To transform all the plural nouns into their respective singular forms, again, simply use the “Find and replace” tool.
Type the plural form into the “Find” field and enter its singular counterpart next to “Replace with.” Specify the cell range and select “Replace all.”
However, the tool lacks the flexibility to pull off the reverse swap, something you might be forced to do at times, considering that most keyword research tools completely omit apostrophes—as an example, phrases such as “gifts for girlfriend’s parents” and “gifts for girlfriend’s birthday” will lack one.
To work around the issue, use this custom formula that turns 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 the formula 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 your 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 output from the formula, highlight the column by right-clicking on the header and click “Copy.”
Next, right-click on the header of an adjacent empty column, click “Paste special,” and choose “Paste values only.” This will be helpful when we get rid of the redundant columns, as it is this third column that contains the adjusted data we need to move forward.
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, now that you have laid the groundwork, weed out the duplicates altogether by applying the UNIQUE function.
Syntax: =UNIQUE(keyword column range)
Again, provided the range remains the same, here’s how it should look:
Now, type the function into the empty cell next to your first keyword in the list and hit Enter.
At this point, 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 was narrowed down by 1,806 phrases.
Step #4: Remove unrelated keywords.
If you think we’re done with the hardest part, then think again as our next step involves removing keywords that are unrelated 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, when used in conjunction with filters, this function returns only the phrases which contain 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 specified cell range and return 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 by using the separator (|).
Right off the bat, eliminate all the navigational, branded terms implying that a user intends to visit a particular website (for example, Amazon, Pinterest, Reddit, eBay):
Now, let’s break down the process of applying functions with filters:
- Click the filter icon in the toolbar.
- Go to the dropdown menu at the top of the keyword column.
- Click “Filter by condition” and choose “Custom formula is.”
- Enter the formula and click “OK.”
You can now remove all the filtered rows.
A quick tip: In order to delete rows in Google Sheets, highlight all the rows by clicking on the first row, holding down the Shift key, and selecting 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, it’s time to harness its full potential by creating your own variations tailored to your niche.
But before leaving you in the mean streets of spreadsheets, let me show you a few examples.
Let’s say the imaginary gift shop should 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:
And as 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 cell range remains the same, my formula should look this way:
Now, run the function by repeating the steps outlined above.
Once you have done that, go through the filtered results line-by-line and separate the wheat from the chaff as some of the terms which have nothing to do with the seed phrase will, surprisingly, end up being highly relevant.
For newbies: hold down the Command key (for Mac) or the CTRL key (for PC) and click on multiple rows to select them simultaneously.
Yes, the whole process might feel like getting blood from a stone, but working with a cluttered list eats up disproportionally more time. For the record, the two functions have almost halved my list, down to 7,337 phrases.
Step #5: Collect search volume data.
Since your list has no value without search volume data, finding a free (or at least reasonably affordable) way to gather it becomes a paramount priority.
If you feel reluctant to shell out a hundred dollars for Ahrefs or its alternative—which you should get, honestly—you’re basically left with just two options: SearchVolume.io and Keywords Everywhere.
As one of the few completely free tools left on the market, SearchVolume.io should be considered only as the last resort if dropping a few bucks on Keywords Everywhere seems unaffordable.
Open SearchVolume.io, copy your keywords into the field (only up to 800 keywords in 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 have already 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, navigate to the Data tab, and click “Split text to columns….”
Once there, next to “Separator,” select 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 data as of this writing. Unfortunately, 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 10,000 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 73 cents.
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 message you’ll receive, and buy some credits.
Next, in your web browser, click the extension icon and choose “Import Keywords.”
After that, move all the keywords—up to 10,000 in one go—from your list into the field.
Export the results and move the data to a new spreadsheet.
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.
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 4,661 keywords, down to just 2,618.
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 that form the backbone of a separate page on your website).
Basically, grouping keywords means skimming through the list and picking the phrases which stand out using the following 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 your pitiless rivals, 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 be turned into a cluster:
Next, pick a keyword and find out whether it can lead to something.
As an example, let’s analyze 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 of competition, it’s not worth the candle.
As the data on the competition offered by Keywords Everywhere can’t 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 links that pop up on page one of the search results, paying special attention to the headlines which indicate the main keyword a given page targets.
In my case, with virtually no competition on the horizon, the phrase gets 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 are 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, 27,100 times per month.
How do you do that without paying a dime? Quite simply, take advantage of 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 ten 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 entire website.
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 storing clusters. With the headlines jotted down, you get the final product for one seed keyword:
Note: Don’t forget to remove the phrases from the keywords list.
So, there you have it.
True, the guide hasn’t even scratched the surface of the pure voodoo magic that you can do when armed with spreadsheets, but you now have all the information you need 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 to brainstorming content ideas for years to come.
It may take some time to climb the short learning curve, but from then on, you’re golden.