SEO: Getting More from Google’s Keyword Planner

Keyword research is the foundation of search engine optimization. Keyword data allows us to peek into consumers’ desires and analyze what they really want.

Google AdWords Keyword Planner opens that window for us. It is free, open to everyone with a Google account, and it’s tied to the largest direct source of search data — Google. There are certainly other keyword research tools: Wordtracker, WordStream, Searchmetrics, and many vendors that provide all-in-one SEO management and reporting solutions. I prefer Keyword Planner because Google drives the most organic search traffic to all of the sites I work with, hands down.

Why Keywords Matter to SEO

Before we can optimize, though, we have to know keywords and phrases what we’re optimizing for. What do we need to rank for to drive organic search traffic and sales? It may not be what you think it is.

If your industry uses a lot of jargon or your company is mired in internal marketing-speak, the carefully crafted messages applied to your site may not match the words real searchers use to find what they want at all. Keyword research can help you shake off the shackles of internal language and give you ammunition to show management why some of the language on your site needs to change.

When you see how few people search for fancy jargon and how many search for simple everyday language, you’ll have a much better idea how to optimize your site to drive more customers.

It’s all about relevance. Using the words real searchers use to find products like yours increases the relevance on the pages of your site, which in turn increases the likelihood that those pages will rank and drive customers.

Listing Keyword Stems

The quality of the information you input into Keyword Planner determines the quality and depth of the keyword research you can extract from it. If you just type in three words and hit go, your results will not be of any use to your SEO efforts.

The first step to effective keyword research is brainstorming. Stop and think about the products you sell. What brands do you offer? How do you arrange your products into categories and subcategories? What filters do you apply to help customers pick their way through your product catalog? Each of these is a keyword stem, a piece of a larger keyword phrase.

For example, if you sell shoes, your site might be organized like this.

• Three categories: women, men, kids.
• Three subcategories within the casual category: athletic, casual, dress.
• Three filters you can apply: black, white, red.

Each of these nine words represented on your site is a keyword stem, or a prompt to suggest a set of keyword stems. They’re not keywords by themselves because, if you sell shoes, there’s no way your site will be relevant for the subcategory keyword “dress.” It is relevant, however, for “dress shoes.”

Your goal at this stage is to collect as many keyword stems as you can. Dig through your site. Dig through your competitors’ sites. Search Google and see which words the sites that rank are using. Think about the problems your products solve. Can you translate those into keywords as well?

Concatenating Keyword Phrases

Stems are half the battle. We need to turn those stems into keyword phrases that we can input into Keyword Planner to extract quality keyword research.

Paste all those keyword stems into a single column in an Excel spreadsheet. Use the Remove Duplicates tool in Excel (Data Menu > Remove Duplicates) to make sure you have each stem listed only once or you’ll waste time researching keywords more than once.

You’ll probably end up with a list of hundreds of keyword stems, unless your product offering is very small. Looking through them, most of the stems are likely modifiers to your main offering. For example, the category word “dress” is really just a modifier for “shoes.” You need to add “shoes” to make it a relevant keyword phrase. That’s where concatenation comes in to play.

Concatenation refers to joining two or more things together. In Excel, the concatenation formula allows you to string together as many cells or words as you like. Because this is a formula, it can be copied instantly down a column of hundreds of cells to create a list of keyword phrases from your stems.

For example, concatenating our nine stems with the product offering of “shoes” using this formula =CONCATENATE($A2,” “,B$2) creates the result below, in the “Concatenation Result” column.

Concatenation result in Excel.

Go crazy with concatenation. It’s as easy as creating a formula and pasting it down the column next to your stems. With our nine-stem example, we’d definitely want to concatenate additional phrases that included both subcategory and filter to create keyword phrases like “men’s black shoes” and “red women’s shoes.”

Plural versus singular versions of words can trigger different results in the keyword tool, as can putting the same words in a phrase in a different order. Add as many of these variations as you can to get the maximum number of keywords from the keyword tool at the end.

Some of the phrases you concatenate won’t make any sense, such as “athletic kids’ red shoes.” It’s faster to concatenate some useless phrases and paste them all into the Keyword Planner than it is to try to sift through the many phrases to weed out the ones that don’t make sense.

Inputting Keywords into the Tool

Now we have a spreadsheet full of keyword phrases, likely hundreds or even thousands of them. But don’t worry. You can paste up to 200 phrases into the Keyword Planner at once. As daunting as this seems at the beginning, when you get into a rhythm with the tool you can probably export around 10,000 keywords an hour.

Log in to Keyword Planner and click on “Search for new keywords and ad group ideas” (labeled as 1 below). In the “Your product or service” field (labeled as 2 below), paste in up to 200 of the keyword phrases you concatenated. Then click the blue “Get ideas” button (labeled as 3 below) to generate a list of keywords.

Google Keyword Planner

After clicking “Get ideas,” select the “Keyword Ideas” tab (labeled as 4 below). This tab displays the keyword phrases from actual users when they search for the words you entered into the tool. The “Search terms” column (labeled as 5 below) shows the search phrases you entered into the tool. The “Avg. Monthly Searches” column (labeled as 6 below) shows how many times on average per month that that keyword was searched for in Google, averaged over a period of 12 months. The “Competition,” “Suggested Bid,” and “Ad impression share” columns are relevant to paid search. It’s helpful to know how much the competition is willing to pay for ad placement, though, so you know how stiff the competition is likely to be for organic rankings as well.

“Get ideas” page in Google Keyword Planner.

The “Keyword” column (labeled as 7 above) lists the keywords that Keyword Planner determines are at least vaguely relevant to the list of keywords you entered into the tool. In this example, the tool provided 800 keyword suggestions in addition to the nine already entered at the beginning.

Keyword Planner will return a maximum of 800 keyword suggestions, which is why it’s so important to make sure the keyword phrases you concatenate are hitting all of the niches that are important to you. With each variation in phrasing, you’ll get another set of keywords that are unique. If you just input a couple of phrases, you’ll miss the unique variances in the way that your customers search, possibly missing out on an important trend you could capitalize on.

Glance through the keywords and determine for yourself if you feel that they’re relevant. You should be able to tell within the first page whether they’re on target for your site or not.

If they’re relevant enough, click “Download” (labeled as 8 above) to export the keywords in CSV format, which can be opened in Excel.

If the keywords suggested feel too broad — such as “nike outlet” and “payless shoes” — Keyword Planner offers a couple of different ways to filter the list and narrow the scope. In the “Keyword options” filter (labeled as 9 below), turn the “Only show ideas closely related to my search terms” option on. This option is similar to phrase match in paid search — all of the keywords listed will include the words you pasted into the tool at the beginning of this process.

The image below shows the difference in results with the “Only show ideas closely related to my search terms” option selected. See how the words may be in a different order or the phrases may include additional words? That’s the beauty of keyword research: discovering keywords that you hadn’t considered.

Keyword Planner results with the “Only show ideas closely related to my search terms” option selected.

If only a few keywords were irrelevant to your product offering in the broad results, use the Include/Exclude feature (labeled as 10 above) to list words that must be included or excluded from the keyword list. For example, if you don’t sell Nike shoes, you might add the word Nike to the exclude list. If you want the keywords to at least contain the word “shoe,” you’d add “shoe” to the include list.

Don’t bother reading through every keyword the tool lists. As you continue the process and paste more and more keywords into Keyword Planner, you’ll start to see repeats where the tool returns some of the same keyword suggestions for many of the phrases you enter. If you try to screen out all the duds at this point, you’ll waste time repeating work that can be done more quickly at the end in Excel. Just download everything once you’ve got the relevance filtered to where you want it and repeat the process of inputting keyword into the tool.

Managing Keyword Data

Depending on the size of your concatenated keyword phrase list, you could have many CSV files sitting in your computer’s download folder. Merge these into a single file, either by manually copying and pasting or using this handy Windows command line method for merging CSV files.

Your CSV file undoubtedly contains many duplicate keyword phrases. Use the Remove Duplicates tool in Excel again, and begin the process of reviewing the keywords for relevance. After cleaning up the formatting and removing some unnecessary data columns, the keyword research looks like the image below, only much longer.

Sort keywords by relevance in Excel.

When you’re staring at a list of 50,000 keywords it seems impossible to manage. Try these tips for making the process easier.

• Use the “Filter” tool in Excel (Data Menu > Filter) to turn the top row of your table of keyword data into a series of filters. As you filter or sort the data, the cells in each row will stay in sync so you don’t have to worry about the data in each column getting mismatched.
• Sort the data by average monthly searches, from largest to smallest. The larger the number of searches, the broader the search phrase is likely to be. This is a good way to remove keywords that are too broad for you to target quickly.
• Sort the data by keyword from A to Z. Keyword phrases that start with the same irrelevant word will be grouped together for fast removal.
• Use a text filter in the “Keyword” column for cells containing a word that’s irrelevant. For example, filter for “flip flops” if your online shoe store doesn’t carry that kind of shoe. All of the phrases containing flip flops will appear and can be deleted at once.

Ongoing Process

Keyword research is an ongoing process. You’ll need to do it regularly, perhaps quarterly, to identify new trends and determine if some groups of keywords are increasing in demand and some are decreasing.

As you return to keyword research, consider how consumers think, to help you answer questions and to organize the content on your site. As you do, you’ll likely think of new pockets of keywords that you hadn’t explored before. Keep adding new data to your research over time and it will become a valuable tool not just for SEO, but also for areas like content planning, market research, and production development. I’ve addressed those additional uses previously, at “Using Keyword Research beyond SEO.”

Editor’s Note: See the follow-up to this article, at “Google Updates Keyword Planner with Dates, Devices.”