A simple (mostly) way to keep on track with smalll business marketing keywords

(photo credit: http://www.flickr.com/photos/ralphunden/)

Here's a simple way to keep on track with keywords
while doing your small business marketing.

But it's a little left-brained, so be warned.

If you've swallowed my efforts to teach you how to find really good keywords, and have fallen in love with the idea of spreadsheets - I've now figured out how to make it simpler to use the results.

The other caveat is that this is a bit tougher to set up originally- but when you do, it's easy to set up everything else.

Your goal is keywords with high KEI, good traffic, and low competition. And not spend forever extracting them. And you are really only using affordable (or free) tools to determine your natural SEO keywords.

The recipe:

1. Your raw ingredients are Google's Adword KeywordExternalTool, RankTracker, OpenOffice Suite (haven't tried this with the Word Suite), and Web1Marketing's Keyword Competition Estimator.
2. You research your keywords from Google, dump these into RankTracker to find their KEI and WordTracker associated terms, and then dump these results into an OpenOffice spreadsheet (as linked above). This new version means you don't have to do anything to them until after you have the spreadsheet - all big and raw and filled with stuff you don't need.
3. OpenOffice has a database (called Base, of all things) which you can use to do all your calculations. Simply set up queries with your parameters (I'll remind you what they need to be in a second - hang on there) and then set up reports which the queries produce.
   
4. The queries:
1. Sort by KEI: less than 2.0 - these keywords you won't be using, but need a list to check when you're copywriting.
2. Sort by Competition: greater than 4 million, KEI: greater than 2.0, and Length: less than 3 - these results give you likely major keywords.
   
3. Sort by KEI: greater than 2.0, Competition: less than 4mil, # of Searches (traffic): greater than 100 - these results are your likely niche keywords. This is what you will author content for.
4. Sort by KEI: greater than 2.0, Competition less that 4mil, # of Searches less than 100 and greater than 20 - this gives you your lower traffic sub-niche which you work on after you've filled the top niche keywords with content (or if that is all you get for this niche...)
5. Now, you can take the queries at this point and have fun. But, I'd suggest you garnish them with their actual competition:
1. Find your desired keywords by searching for them with "Find All" and then give their background a color. (It's a neat trick - once you've done it once, your hooked.) Remember, you're working from the good-KEI-but-too-much-competition words through your niche and sub-niche terms.
   
2. Take these one by one (sorry I haven't found a tool for this yet) and run them through Web1Marketing's tool to get their "QAT" value - in quotes, in anchor, and in text.
   
3. Then plug these in by the appropriate keyword you are looking for - all in their own column on the spreadsheet.
   
4. Repeat 2 and 3 until you have all your (colored) keywords done.
   
5. Update your queries and reports to include the QAT column.
6. Now you can print your reports and have fun. If you want, you can save the report in native OpenOffice Writer format and then highlight all those terms with QAT you've found - then print it off in color so you can find them easily.
   

With these reports, it's easy to simply work up your publishing schedule and also see what other keywords you can sprinkle into any content you have in order to get gains on those, too.

Now, I still prefer spreadsheets myself - at least at this point. But I'll probably move over to simply doing the database route, as I don't have to do all these queries manually every time I create a new spreadsheet.

The real advantage is that you can update your spreadsheet by simply running all the keywords through RankTracker any time you want to update their usefulness - and then just re-run your queries to get updated reports. The query automatically dates the report, so you're set - particularly if you run your results and print your reports the same day.

The point of all this left-brained exercise is to save you time and make your life less of a hassle.

And make it easier for you to work at getting these top keywords by grabbing their lower-end niches. Plus, be able to update your work quickly to see what you should be targeting this week.

Obviously, if you are doing this as an SEO consultant, for an Internet viral marketing campaign, then you have an incredibly valuable tool here.

(But since this blog has few subscribers right now, our little secret is safe...)

- - - -

Update (about 4 hours later):
Found out how to search for terms with wildcards (had to figure there was a way).

So now you can add to the above to look for certain phrases within your spreadsheet base.

Meaning, you take the top single- and double-term items to winnow out just the top keywords you want to rank for. Then put these as part of the search so you wind up with lists of only the ones you want.

What this is then great for is combining several searches and using the database to crunch the terms instead of RankTracker (which runs on Java and so is only as fast as the amount of memory and CPU size you have onboard.) In other words, basically - slow. OpenOffice Base is remarkably fast with it's queries.

Now, the idea is that you take a spreadsheet and find maybe four keywords which are appropriate to your product line. Then get all the similar words and related words which help you describe it. Get RankTracker to boil these down for you and then OpenOffice to distill them into a ready package. Update that basic database once a month or once a quarter and you're set to run your campaign and keep it accurate.

[Additional: Just to give you an idea of what I'm working with, my RankTracker output rolls up to 1280 keywords right now (soon to be expanded). Within literally seconds, I can sort these down to the 14 major keywords, 50 or so niche and sub-niche KW, plus the 8 pages of alphabetical listings for terms I don't want to use. Nice. The whole cycle takes probably an hour or two to do the first time, then updating maybe 1/2 hour once a month.

Sounds like I could really open up a paid service if I wanted to... Because all I have to do to take any other set of keywords from a spreadsheet is to set up another database and then copy the SQL formulas over. Sounds far more nerdy/geeky than it is.]

Great stuff, eh? Let the computers do your work for you, so you can simply concentrate on polishing your natural SEO content.

Cheers - and Good Hunting!