Full Guide to Spreadsheet-Free Keyword Research
5 steps to no-hassle keyword research, analysis, and targeting for SEO
It's 2016, aka the age of semantic search. These days, keyword research takes time, a combo of clever research methods, a few hacks to spy on your competitors, and dozens of complex metrics for analysis and prioritizing. But one thing it doesn't take is a spreadsheet.
Don't get me wrong, spreadsheets are great. They can calculate things for you. Things like averages and stuff. They even give you graphs and charts and things. And if you get your hands on the formulas, it only takes a click to find the sine and cosine of any given angle. Any given one!
Ok, enough with the sarcasm. What I'm driving at is that a spreadsheet may be the best thing in the universe for some types of work. But keyword research isn't that type of work.
First, if you use a spreadsheet for your keyword research, it's going to take you days. Even worse, you'll be spending 90% of the time just importing, preparing, and sorting the data — and not nearly enough time on the research and analysis itself.
Second, you probably want an accurate, careful analysis for your keywords. The reason you don't get any of that in spreadsheets is simple: your chance of making an error is 9 out of 10 (yup, it's a fact: about 90% of spreadhseets contain errors). Too much manual input = too much space for a human error.
Lastly, there are tools created for keyword research that are much handier than spreadsheets. Tools that'll both save you hours of time and the hassle of manually putting all your data in.
Ready to get down to business? In this article, we'll focus on how you can dig out the best keywords for SEO in any industry to help you outperform competitors in SERPs and get you the visits and sales you're after. But first, let's look at what modern keyword research is about, and do away with the common myths and misconceptions on the matter.
Prologue: The evolution of keyword research
In lingustics, a word is often described as a structure with form and meaning. Before the release of Hummingbird in 2013, Google was only able to detect relevance judging by the presence or absence of the form compontent in a piece of content. With time, this approach made the SERP a bit of a mess for 2 reasons:
First, the same form could have a wild variety of meanings — both by itself and depending on context. Google wasn't doing a lot to figure out neither inherent nor contextual meaning of words, and ended up delivering the results that often did not meet the needs of searchers.
Second, the system was very easy to trick for webmasters. Stuff as many keywords into your content as you can, and you've got a relevance score of 100 out of 100, without even trying to provide any value to the visitors.
All of that changed with Hummingbird. In 2013, Google made a leap forward in the direction of deciphering the meaning behind queries. Keywords evolved into topics, and the semantics behind the query became more important than the phrasing of the query.
In 2016, with machine learning and RankBrain, Google is actually close to being as good at figuring out meaning as a human. It looks at a dozen of things beyond the actual content of the page and the terms used in it. It judges whether or not the page matches the topic of the query by indirect, contextual features of the content. It looks at user behavior, engagement signals, and at something called expertise. That means that if you've done a good job in the past of showing Google you're relevant for a certain topic, a new page you create on a similar topic might rank in Google right away — even if you haven't done a lot to specifically optimize that page, but because of the relevance and authority you've built in the past.
Semantic search is talked about a lot, but not so often from the real world SEO's perspective. If you think about it, Humminbird turned everything we've been used to on its ear. We can do nothing but follow through and adapt our habitual SEO jobs — if we don't want to be stuck forever somewhere around page 8, that is. It's surprsing how many SEOs still rely on the same old-school way of doing keyword research that worked 5 years ago: plug in a few terms into Keyword Planner, get a list of ideas, shove them into your content.
Don't get me wrong — Keyword Planner is still one of the best keyword tools, and you still absolutely should have your main keywords in your content. But this alone will no longer give you a substantial ranking boost.
Instead, you need to research and target entire keyword groups — or topics — and create your content around those. The modern keyword research practices below will let you do just that.
Let's get rollin'!
Tools you'll need
Here are the tools you'll need to research, analyze, and select the best keywords in your niche.
1. Pen and paper
2. Google Search Console
3. Rank Tracker
In this guide, I'll use Rank Tracker to illustrate each of the steps. You'll get all features described below in Rank Tracker's free version; mind that to save or export your data, you'd need a Pro or Enterprise license.
Step 1: Brainstorm
To kick off the process of keyword research, you first need to think about the topics you want to rank for and write down a few groups of keywords that you'd like to target with 2 or 3 example keywords in each group.
If you are researching keywords for an entire website, a good starting point is to click through the navigation of the site and think about the parts you can divide the site into thematically. Don't think of exact, specific keywords at this stage; instead, focus on broader, more general topics you'll use as a base for your research. Typically, you'll end up with up to 5 very broad keyword groups.
If you're optimizing a part of a website (a blog, for example) or a specific page, you'll probably have just one or two broad topics on your list.
Let's say I'm looking for keywords for link-assistant.com; 2 minutes of clicking through the site, and I've got 3 major keyword groups here:
Branded keywords are the name of your company, products, and brand. Product keywords are generic, non-branded descriptions of your products or services. In a group named Education, I've included the terms not directly related to our products, but rather topics that our users may be interested in. It's safe to assume that people looking for SEO software are also interested in SEO education and industry news; if you have a blog in place that addresses those topics, that's a win win.
Surely, each of those keyword groups can have dozens of subgroups. But at the moment, let's only focus on the seed keywords within each group.
Step 2: Get ideas
This is perhaps the most important part of the process — and the one that 80% of SEOs get terribly wrong. They plug in a keyword into Google's Keyword Planner, download the results, and then start sorting through them in a spreadsheet (yuck!).
The problem with that approach isn't just that exact match keywords don't work as well as they used to with Google's shift to semantic search. Perhaps an even bigger problem is that hundreds of other marketers in your niche are doing the same thing, and you all end up targeting the exact same keywords.
Don't get me wrong. You should absolutely use Google Keyword Planner for keyword research — and it's an important part of this guide, too. Just don't make the mistake of using it as your only keyword research tool.
1. Find keywords you already rank for
It's only logical to start building your keyword list with the terms you rank for already. To do that, you'll need to log in to your Google Search Console (formerly Webmaster Tools) account, and go to Search Analytics. This report will show you the keywords your site already ranks for in Google. Scroll down to the bottom of the page, and click Download to save the CSV to your computer.
Next, launch SEO PowerSuite's Rank Tracker, create a project for your site (if you haven't already), and navigate to the Keyword Research module in the left hand menu. Click Suggest keywords and select Google Search Console from the list of available research methods. Select the CSV you just saved to your computer and click Open to import it to Rank Tracker.
Now, you'll see a list of keywords you already rank for in your keyword research module. They'll also get automatically tagged with Google Search Console so you can later tell between the keywords you got from different sources. Well done — on with the next step.
2. Get ideas from Keyword Planner
In Rank Tracker's Keyword Research module, click Suggest keywords once more. This time, pick Google AdWords Keyword Planner as your research method. In the Enter your keywords field, type in the keywords you came up with at the Brainstorm step (it's important that you only enter keywords that belong to the same topic at a time). Don't type in too many seed terms — the longer the list of seed keywords, the longer the search will take. At this step, you can also specify negative keywords if you need to, and change the country and language settings.
Click Next and hang on a few minutes while Rank Tracker is collecting keyword ideas from Keyword Planner.
Pro tip: Depending on the type of your business, you might be interested in keywords above certain search volume. You can filter out the ones with fewer searches right away. When the search is complete, Rank Tracker will show you a list of keywords it found at Step 4. Click Select none, and hit the little filter button in the top right corner. Click Add filter, and select the following conditions for the filter: # of searches -> More than. Enter the minimal number of searches you're interested in, and click OK. Now, click Select all and Finish.
You can also add other filters to only include the keywords that contain a certain word, keywords with a Cost Per Click below a certain rate, etc.
Once the search is complete, select the keywords you'd like to add to your project (it's okay to select all at this point), and click Finish.
Your workspace will now populate with hundreds of keyword ideas from Google AdWords' Keyword Planner. Rank Tracker will also automatically group keywords by topic, and you'll see the list of those keyword groups in the left hand part of your workspace. At this point, it's a good idea to switch to All keywords and review the terms you've found so far. Keyword Planner will give you plenty of suggestions, but not all of those will be relevant. To remove the keywords that aren't relevant to your business, highlight them, right-click the selection, and hit Remove keywords.
Repeat the process from beginning to end if you have other target topics on your mind.
3. Diversify the list with Google Autocomplete, Related Searches, and Trends.
It's time to get a little smarter than most SEOs in your niche. Now that you have your first keyword suggestions from Keyword Planner, let's diversify those using other research tools to reveal some hidden gems.
My top three tools that work best for this are Google Autocomplete, Related Searches, and Trends.
Google Autocomplete will show you suggestions from Google's search box based on the keywords you enter. Doing this manually for anything over a dozen keywords would probably take you hours. In Rank Tracker, you can just type in your seed keywords, and it'll automatically pull Google's search box suggestions for you.
Google related searches can be great for identifying keyword terms people search for along with the keywords you have on your list. This method can give you a broad scope of new topics to target you may have not thought about previously.
Google Trends will show you the most popular searches that are also related to any keyword you type in.
In your Rank Tracker workspace, highlight the keywords you'll base your research on. You'll get dozens of suggestions for each one, so it's a good idea to start with 3 or 4 that describe your business best. Click Suggest keywords and select Google Autocomplete as your research method. Hang on while Rank Tracker is pulling suggestions for you, and go through the process again, this time selecting Google Related Searches as your research method. Do the same for Google Trends.
Review the keyword suggestions you find and remove irrelevant terms, if any.
4. Discover competitors' target keywords.
Here's a little hack that lets you spy on your competitors a little and see which keywords they are targeting. Click Suggest Keywords and select Google AdWords — by Target Page. Type in your competitor's URL and click Next. Rank Tracker will dig out keywords from Google AdWords that are associated with the competitor's page. You might want to tag those right away with the competitor's name; you can do it when Rank Tracker completes the search in the Add tags field, before you click Finish.
Step 3: Analyze keyword efficiency
By now, you probably have hundreds of keywords in your workspace. Time to pick the ones that will bring you most visits and sales, and filter out the ones that are too competitive for you to realistically target.
1. Check the keywords' traffic potential
It's only logical that you're looking for keywords that can bring you a lot of traffic. To tell how many visits a month you can expect if you rank #1 for any given term, check with the Expected Visits column. This metric is closely tied to the number of searches, so it can give you a pretty solid idea on the organic traffic you can expect from each keyword. You can sort the keywords by expected visits by simply clicking on the header of the column.
Remember that keywords with high search volume are also typically very competitive, so it's a good idea to also pay attention to the Competition column. Here, you'll see whether or not a lot of advertisers are bidding on a keyword in Google AdWords. KEI, or Keyword Efficiency Index, is derived from the ratio of a term's search volume and competitiveness — typically, the best terms will have both a high a high Expected Visits value, and a decent KEI, marked with green or yellow.
2. Analyze buyer intent
Usually, you wouldn't just want keywords that'll give you a certain amount of traffic. You'll want ones that'll bring you quality, high-converting traffic. If your final conversion goal is a purchase, you'll need to figure out whether there's any commercial intent behind your keywords.
Go through your keyword list in Rank Tracker and ask yourself: is the searcher ready to buy? If the answer's a definite "yes", then you are looking at a Buy Now keyword — these guys tend to convert like crazy, even if their search volume isn't as high as that of some purely informational terms.
Pro tip: A great clue for analyzing buyer intent is the Cost Per Click data. Google AdWords' Cost Per Click gives you a pretty solid idea of how well a term converts — basically, the higher the cost, the better it'll sell. It's pretty intuitive — the more an AdWords advertiser is paying per click, the more valuable that traffic must be for them. Go to the PPC Analysis tab and click on the header of the Cost Per Click column to sort your keywords by AdWords rate.
It's a good idea to tag your keywords with their commercial intent right away (you can do that by right-clicking a set of keywords and hitting Add tags to selected records). For example, you could divide your keywords into 3 categories based on intent: Buy Now for keywords people use when they're ready to make a purchase (those will often include words like "buy", "coupon", "discount", "deal"), Product for keywords that imply that searchers are looking to make a purchase but haven't completely decided on the product yet ("review", "comparison", "best", "cheap"), and Information for terms that indicate the searcher is just researching a certain topic or industry ("how to", "tips", "what is", etc).
Once you've weighed the traffic potential and purchase intent for the terms, you should be able to easily pick the keywords and groups you'd like to target. One by one, select the groups you'll be targeting and click Move to Target Keywords Module. This will transfer the terms from Keyword Research to Target Keywords. The rest of the keywords you found will stay in your Keyword Research module so you can go back to them at any point later.
Step 4: Group keywords by topic
Combining similar keywords into composite groups may not be the fun part of the process, but it's a very important step. It'll make keyword analysis, selection, and mapping a ton quicker and more effective.
Go to Target Keywords -> Keyword Map to view the keywords you just transferred from the Keyword Research module. As you scroll through the groups, you may be willing to reorganize some of them. To merge several groups, select them, right-click the selection, and hit Merge groups. To rename a group, click on the little pencil to the right of its name. To move keywords from one group to another, select them and click the Move To Another Keyword Group button.
Step 5: Build a keyword map
Now that you have a final list of target keyword groups for your pages, it's time to map those groups to those pages. There's one handy metric to help you decide which keywords belong to which pages: Keyword Difficuly.
Switch to the Keyword Difficulty tab in the top right corner of your Rank Tracker workspace to check the difficulty score of individual terms or entire keyword groups. This difficulty score is derived from analyzing the sites that rank in top 10 results for the term — including their content optimization for the keyword, links, authority, domain quality factors, and social media signals. To check the difficulty of an entire keyword group, select the keywords within that group and click the Update Keyword Difficulty button (be patient here — Rank Tracker will need to analyze 10 top ranking sites for every keyword you've selected, so it may take a while).
When the update is complete, select any of the keywords on your list for a detailed breakdown of the sites that rank in top 10 for the term, and each site's individual difficulty score. This can give you a good idea of the features that are typical for high ranking sites in your niche, such as the number of linking domains, Alexa rank, social signals, and domain age.
You will inevitably find some terms that are pretty difficult to rank for. This doesn't mean that you have to discard those completely, but it's a good idea to target them with the pages that enjoy the most backlinks and traffic (such as your home page). To associate the more difficult terms with the important pages on your site, select a keyword, click Assign Keywords to Landing Pages, and type in the URL of your landing page.
Clearly, when mapping keywords to pages, you also need to look at the relevance of each keyword group to a specific page. Go through all of your target groups, and assign them to landing pages using the same Assign Keywords to Landing Pages button. If you'd like to map several groups to the same page, highlight those groups, click Assign Keywords to Landing Pages, and choose Select all in the Select keywords drop-down menu. When you're done mapping, switch from Keyword Groups to Landing Pages. Here, you can navigate through your keywords by the page they're associated with.
Now, you're probably looking at a good dozen of keywords mapped to each landing page. Clearly, you can't (and shouldn't) use all those terms in your content equally. First, it's technically impossible for some page elements (imagine shoving all those terms into your title or description!), and second, the first thing you want your content to be is valuable to the visitors. That's why before you get down to optimizing your content, you'll need to prioritize your target keywords.
There're a bunch of factors you should consider before determining priority. First, look at your current rankings for the term — it's a lot easier to bump your site up from 15 to 5 on the SERPs than it is to go from unranked to 15. To do that, you'll need to go to the Rank Tracking submodule and check your ranks.
Then, consider the keywords' search volume and expected visits. It's only logical to include the more popular ones in your title, description, and URL.
Don't forget about buyer intent (I hope you did tag those Buy Now lovelies!). The high-converting terms with strong purchase intent should be your top picks — those are the ones you might want to use across your content more sparingly than the others.
There's a handy little hack in Rank Tracker that lets you prioritize those keywords easily. Select the terms that belong to the same priority category (say, top priority), right-click the selection, and hit Set a color marker. Select the color for your top important keywords. Go on and do the same for other priority groups using other colors.
Woo-hoo! Now you can see which terms are the best for your title, meta description, and headings, and which ones you should remember to use in your page's copy as you compose your content.
And you're done! Now you not only have a wide list of keywords your competitors are unlikely to be targeting, and a keyword map with the search terms assigned to specific pages; you also have all your keywords marked with priority to make creating and optimizing content a ton easier. Perhaps the handiest way to do that is through WebSite Auditor's built-in Content Editor where you can edit your pages in live view and get specific tips on keyword usage… But I digress :)
What tips and hacks do you use to stay ahead of competitors with your keyword strategy? What are your ways of finding the high-converting keywords in Keyword Planner? Looking forward to your insights, comments, and questions!
By: Masha Maksimava