How to Build a Keyword Worksheet for SEO

Your Keyword Worksheet is a fundamental blueprint for your SEO. Make sure that you build one, and that it becomes a ‘living document’ outlining your SEO strategy.
In this video, I am going to take a random company, Commuter Cleaners of Stamford, Connecticut, and build out their Keyword Worksheet. Along the way, we’ll toggle over to their actual website, and see how the structure of their website reflects their keyword patterns. What you want to realize is that keyword patterns find a one-to-one correspondence in the website itself. (This is very important). Let’s get started!

Watch the Video


Keyword Research: From Brainstorming Keywords to Structural Patterns

I’m assuming you’ve done some keyword brainstorming. In that first exercise, you sit down with your team (perhaps your CEO, sales manager(s), marketing folks, etc.), drink a bunch of coffee or (my favorite) martinis, and get your ideas flowing. Use some of the Google tools such as Google autocomplete, the AdWords Keyword Planner, Ubersuggest.io, etc., and brainstorm your keywords. Be sure to look at competitors, and use RIGHT CLICK > VIEW SOURCE to look at their TITLE TAGS, META DESCRIPTION TAGS, and KEYWORD TAGS.

Keyword Worksheet

Photo credit: Јerry via Foter.com / CC BY

At the end of this exercise, you should have a messy list of possible keywords. For Commuter Cleaners, we’d come up with –

dry cleaning
dry cleaners
wash and fold
home pickup and delivery dry cleaning
laundry services
micro’s like leather cleaning, purse cleaning, etc.
helpers –

cities – such as Stamford, Greenwich, New Canaan, etc.
best, top, top-rated
same day (as in same day cleaners)

Next, we want to pop some of these keywords into the Google AdWords Keyword Planner and look at the volumes and values. We’re looking for high volume / high value keywords. We’ll also touch base with the business owner to see which keyword patterns he thinks are most likely to be “high value” customers. (We learn that the money is in same-day dry cleaning and home delivery services for laundry, by the way).

OK, now it’s time to build out our Keyword Worksheet.

You can download samples –

The Keyword Worksheet

Let’s run down the columns, from left to right. Let’s start with the Column B, called CORE KEYWORDS. These are the ANCHOR phrases around which ALL of your keyword queries are built. So, for example, people want a DRY CLEANER, so they search for dry cleaner, dry cleaning, etc., and then they add helper words like top, top-rated, or the city like Stamford, so we’d get

Dry Cleaning
  Stamford Dry Cleaning
  Stamford Dry Cleaner
  Stamford Dry Cleaners
  Top Dry Cleaners in Stamford

Now, in consultation with the business owner, we realize that the money is in “home delivery” and “same day.” (Column A marks HOT / WARM / COLD in consultation with the business owner). For especially HOT phrases, I recommend you break those out into their own CORE KEYWORD, so we have

Home Delivery Dry Cleaning
Home Delivery Dry Cleaning Stamford
Home Pickup and Delivery Dry Cleaners Stamford

Ditto for same day dry cleaning (also a high value phrase). (Note that the CORE KEYWORD is not same day but same day dry cleaning as you MUST have a phrase that “makes sense” or “makes a complete search” on its own!).

The Customer is Never Wrong

In addition, We note that some phrases like wash and fold are searched by customers but are not technically what we do. In wash and fold, the customer has to bring in his laundry to the shop. In home delivery, we pick it up at their house, and we deliver it. It’s better! But the customer is never wrong, so we want to rank for wash and fold Stamford, for example. Thus we will optimize for wash and fold as a core keyword.

We also note that specialty cleaning, and micros like purse cleaning or wedding dress cleaning that do not have a lot of volume, are high value, so we want to identify them on the spreadsheet.

Where this is going is that a CORE KEYWORD = A LANDING PAGE. Take a look at the Commuter Cleaners website, and you’ll see that a CORE KEYWORD becomes a landing page on the website, with a ONE CLICK access from the home page. In this way, we use ON PAGE SEO to signal to Google our top priority phrases. Also notice that because this is a local business with local addresses, we have specific localized landing pages for key cities like Stamford.

Helper Words

Our research has identified helper words, which are words that customers append to the core keywords. So, a search like home delivery dry cleaning becomes top-rated home delivery dry cleaning in Stamford. Top, top-rated, and best are very common helper words, as are cities (e.g., Stamford, Greenwich, Hartsdale), so we add those to Column C.  We want to “pepper and salt” these helper words on our content, realizing that we want to rank for phrases such as “best specialty cleaner in Greenwich” and that, to do so, we have got to get these phrases onto our content in an unobtrusive way.

Volumes

We add volume data to Column D for the major search queries; if you like, you can indicate the CPC (cost per click) on AdWords as well, although I find that a general “hot / warm / cold” is sufficient, and I try to put the higher value keyword towards the top of the worksheet. We also identify competitors to watch as well.

Other Tabs

Other tabs on the keyword worksheet are for longer-term reporting. So we have a REPORTING tab, a LANDING PAGE tab (which matches keyword queries to the respective landing page on the website), a SAMPLE KW LOCALS page (where we track local rank), and SAMPLE KW (where we can input our rank each month vs. our target keyword queries.

Remember that your KEYWORD WORKSHEET is a LIVING DOCUMENT. You want to use it as your on-going BLUEPRINT to your website structure, organization, blogging, etc., but revisit it periodically to make sure you have optimized for the best keywords. I recommend you use the REPORTING tab each month, as well as chart your RANK on Google using tools like the RANK CHECKER tool or FAT RANK. In addition, the LANDING PAGE tab makes it easy to see as you write content which keyword phrase should be linked to which landing page for LINK SCULPTING PURPOSES.


Photo credit: Јerry via Foter.com / CC BY

How to Build a Keyword Worksheet for SEO
5 (100%) 3 votes
This entry was posted in Blog, How To and tagged , , , on by .
Jason McDonald

About Jason McDonald

Jason McDonald is a top-rated San Francisco SEO Consultant. His consulting services include search engine optimization consulting, social media marketing consulting, and Google AdWords consulting. Jason's motto as a consultant is that he doesn't do SEO 'for you' but rather he does SEO 'with you.' That goes as well for his social media marketing consultant activities and Google AdWords consultant services. Besides serving clients in the San Francisco Bay Area, Jason consults with clients in Silicon Valley (San Jose), Oakland and other cities throughout the Bay Area. Beyond the Bay Area, Jason is available as an SEO consultant, Social Media Consultant, and as an expert witness in litigation involving social media marketing, search engine optimization and pay-per-click advertising.