Tips For Growing Keyword Seeds With Excel Formulas

Have your seeds germinated yet? Last time, we talked about keywords as “seeds” of ideas; a single keyword to represent an entire keyword cluster. Now it’s time to make our seeds grow.

In this case, let’s use “deck”. To begin with, I want to think of the types of decks my client might build:Using the example of Artisan Construction Servicesagain (thanks!), we’ll walk through the process that I use to grow a single seed.

• Composite
• Wood
• PVC
• Hardwood
• Cedar

Then I am going to use what we learned about the client already to develop a list of verb modifiers:

• Build
• Building
• Builder
• Replace
• Replacement
• Replacing
• Construct (wait, I’m not going to use that one, see below)
• Constructing (not this one either)
• Construction

I was able to generate the list above based on conversation with the client and a working knowledge of the deck building industry. If you’re not familiar with your client’s industry, you may want to research this more – look at forums, the discussions part of Google Search, and Yahoo Answers, for example.

Categorize Keywords By Intent

After I build my list of modifiers, I go back and think about intent. I’m not going to use “construct” or “constructing” as modifiers because I think the intent behind them is DIY. Construction probably has some DIY in it as well, but it might glean several useful keywords. I’ll make a note to filter those after the fact.

By the way, don’t go above 10 total modifiers; it gets to be too much to work with.

Since the focus of this post is how-to research, I won’t go into all the modifier sets I’d use, but normally, after I did this set, I’d go back and do one for decking and maybe also for specific brands (Azek, Nichiha, etc.) When you do this for yourself, keep in mind that de-duplication will be important (see below).

Concatenation

(Yes, it is a word, and a useful one for search!)

Next, I use a tool like the one found here to concatenate (that means put together) the keyword seed with the modifiers. I prefer not to use the “concatenate” function in Excel, because sometimes it is limiting. I also find this formula to be infinitely easier to remember and manipulate:

Let’s say you have the following cells:

A	B	C
build	composite	deck

You want to get the phrase [build composite deck] out of this. Use the formula A1&“ ”&B1&“ ”&C1.

The &“ ”& just adds a space between the two cells’ values. If you want to lock down a value, as in the list below, so that you could get the phrase [building wooden deck]:

A	B	C
build	composite	deck
building	wooden

Use the formula the same way as above, but lock out C1 with dollar signs: A2&“ ”&B2&“ ”&$C$1.

You can “lock out” either a Row or a Column value.

Ok. If you haven’t already clicked to open the spreadsheet I mentioned above, do it now. This will show you my initial list of keywords based on the modifiers I listed above.

Notice that because I didn’t use all 10 possible modifiers, my actual keyword list ends at line 40. Just don’t use the incomplete keywords at the bottom.

Getting Search Frequencies

Go to the Google Keyword Tool. Login if you have an Adwords account; it saves you from having to enter the captcha for each search.

Cut and paste the first section of keywords from your list into the keywords field. Make sure the box that says “only show ideas closely related to my search terms” is NOT checked. Click the box on the left for “exact match” (and turn off “broad”).

Google Keyword Tool Settings

After you click “Search” and get a list of results, don’t even look at them. Just click the “download – all – CSV for Excel” link.

Repeat these steps for each modifier, but when you get the CSV’s for the other modifiers, paste them into the original Excel sheet so that you have one big list of keywords in one Excel sheet.

Once you have your full list, you need to de-duplicate.

Steps to De-duplicate

1. Make sure all of your data is in the same format. I like to find and replace (CTRL-h) all of the brackets with nothing. Find=[, Replace=nothing (leave it blank). Then repeat with “]”.
2. Sort by global monthly searches ascending. Delete any rows where global monthly searches are “0” or “-“.
3. Now sort by Keyword A-Z.
4. Go to Data – Remove Duplicates. Specify (check) only the “Keyword” Column. If you’re using an older version of Excel, it’s a little harder to do this – see these instructions to de-duplicate.

Removing 0-Value Keywords

Now we’re going to look for keyword patterns. The first obvious one I see is “how to”. Anyone using the word “how” is not interested in paying a contractor (at least not at this time).

1. Use the following formula in Column E: =FIND(“how”,A2). This will “find” anywhere in A2 that has the text string “how” and return the character position it starts at. Trust me, this is useful.
2. Now copy/paste the formula all the way down your list.
3. Select Col E and copy it.
4. Paste special for values only in Col F.
5. Sort Col F. ascending.

This brings all of the keywords with “how” to the top of the list. Simply delete out these rows, and then look for another pattern.

Hint: to easily look for another pattern, just select all of Col E and find/replace the string you’re looking for (example “how”) with the next one you need (example “design”).

When you think you’ve removed as much as you can, you will probably be down to under 100 keywords. These are the ones you really care about. Take just a minute to scroll through the completed list to make sure you didn’t miss anything and delete it.

Rinse and repeat as necessary. It takes a lot less time to do it than to explain it. You may also enjoy these easy PC/Excel shortcuts:

Shift+space bar = select an entire row

CTRL+- (that’s the minus sign) = delete an entire row

CTRL+h = open the find and replace window

ALT+e, then s, then v = copy and paste special for values only