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.

Keyword Seeds Germinating

Germinating your Keyword Seeds

Using the example of Artisan Construction Services again (thanks!), we’ll walk through the process that I use to grow a single seed.

In this case, let’s use “deck”. To begin with, I want to think of the types of decks my client might build:

  • 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

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

Next time: Categorizing and mapping keywords.

Photo (http://www.women24.com/HomeAndAway/Gardening/Growing-seedlings-20090216) from Abalimi Bezekhaya.

Opinions expressed in the article are those of the guest author and not necessarily Search Engine Land.

Related Topics: Channel: SEO | How To | How To: PPC | How To: SEO | Intermediate | Keywords & Content | SEM Tools: Keyword Research | SEO: General

Sponsored


About The Author: is the President of an online marketing consulting company offering SEO, PPC, and Web Design services. She's been in search since 2000 and focuses on long term strategies, intuitive user experience and successful customer acquisition. She occasionally offers her personal insights on her blog, JLH Marketing.

Connect with the author via: Email | Twitter | Google+ | LinkedIn



SearchCap:

Get all the top search stories emailed daily!  

Share

Other ways to share:

Read before commenting! We welcome constructive comments and allow any that meet our common sense criteria. This means being respectful and polite to others. It means providing helpful information that contributes to a story or discussion. It means leaving links only that substantially add further to a discussion. Comments using foul language, being disrespectful to others or otherwise violating what we believe are common sense standards of discussion will be deleted. Comments may also be removed if they are posted from anonymous accounts. You can read more about our comments policy here.
  • lestarte

    Hi. Thanks for this. I’d like to add something to the formula, but all data in the “E”-cells are protected by a password. Do you have a link to the original article?
    My idea would be, that we use an additional IF() for the first concat-function.
    Then, the last two-pair-keywords wouldn’t have a space in front of them.
    Just a cosmetical thing, of course.
    Cheers

  • http://www.jlh-marketing.com Jenny Halasz

    Hi lestarte,

    If you want to modify the spreadsheet, the password to unlock it is on the right side of the sheet. If you want to remove a space from a concat-style function, you can either write the formula this way: A1&B1&C1, removing the ” “, or you can use the TRIM function after the fact.

    By the way, this worksheet could have been infinitely more complicated, with nested IF functions for every possible scenario, but my goal was to keep it as simple as possible for people who may not be familiar with excel.

    Hope that helps!

  • http://www.jlh-marketing.com Jenny Halasz

    I should clarify – the formula A1&B1&C1 removes spaces in the middle of the keyword. So [build composite deck] would become [buildcompositedeck]. The TRIM function will remove any extra spaces before or after the keyword, which would happen if you had a value in two out of the three cells and ended up with extra spaces as a result.

  • http://www.franchisenigga.com Gurugi

    But the mods can be handled in another system because there would be a reason for doing it. We’re a bit more dynamic than this illustration but you gave me what I needed to build my garden.

    Thanks, I’ll give you footnote credit when I kill Facebook.

  • http://www.body-buildin.com/ Rhadoo

    Now this is really interesting! I never thought that I could use Excel for my blog :)

  • http://twitter.com/jennyhalasz Jenny Halasz

    Glad you enjoyed it! Thanks for reading. :)

  • xpike_its

    jenny thanks so much its really great info you have shared
    thanks again.

  • http://snapdrawcode.com/ Bibiano Wenceslao

    Awesome writeup Jen! Absolutely helpful, specially for those new to keyword research, and a great refresher as well. For generating somewhat more long-tailed seed keywords using modifiers, I use MergeWords (http://mergewords.com/). Pretty much works the same as your excel function, just with some extra options like a custom separator and term wrapper. ;)

    Cheers, 
    @SnapDrawCode:twitter 

  • http://twitter.com/jennyhalasz Jenny Halasz

    Oh how funny! I had no idea a site like that existed. Still, for some clients, I’d be uncomfortable putting their keywords (especially anything branded) into a web based tool. That’s why I created the one above. I’m probably just paranoid, but I’ve worked for several “top secret” companies!

 

Get Our News, Everywhere!

Daily Email:

Follow Search Engine Land on Twitter @sengineland Like Search Engine Land on Facebook Follow Search Engine Land on Google+ Get the Search Engine Land Feed Connect with Search Engine Land on LinkedIn Check out our Tumblr! See us on Pinterest

 
 

Click to watch SMX conference video

Join us at one of our SMX or MarTech events:

United States

Europe

Australia & China

Learn more about: SMX | MarTech


Free Daily Search News Recap!

SearchCap is a once-per-day newsletter update - sign up below and get the news delivered to you!

 


 

Search Engine Land Periodic Table of SEO Success Factors

Get Your Copy
Read The Full SEO Guide