Use a Spreadsheet Program to Generate High Traffic Keywords for Your Site - dummies

Use a Spreadsheet Program to Generate High Traffic Keywords for Your Site

To optimize your Web site for search engines, you can use a spreadsheet program to find high traffic keywords. With a high traffic keyword, the goal is not only to bring people to your Web site, but also to keep them there.

If your keyword brings in a lot of traffic, but there’s also a high bounce rate (people who stay at the landing page only briefly, and then hit Back on the browser), you have a problem. A high bounce rate indicates one or more of the following issues:

  • The keyword is not relevant for your Web page.

  • The text on the Web page is not relevant enough to the keyword.

  • The content or layout of the Web page doesn’t hold a user’s interest.

  • The page loads too slowly and users lose patience and abandon the page before it fully renders.

In any case, you want to look closely at the page with the particular keyword in mind and make appropriate improvements. Keywords that have a high bounce rate do not yield many conversions, and therefore do not generate any revenue (unless you have a Web site where you make money based on page views alone). If anything, high bounce-rate keywords can cost you money by requiring a lot of site hardware and bandwidth (the speed data moves to and from your Web site) to support all the extraneous traffic.

You can analyze your keywords in a spreadsheet program like Microsoft Excel, Google Docs, or PlanMaker. To do this, you copy your entire keyword list and paste it into column A of a spreadsheet, so you end up with a simple list of keywords, one per row. Depending on how big your list is, you may want to create a new tab for each subject category, separating their keywords into more manageable spreadsheets. Setting up a keywords spreadsheet comes in handy when you’re keeping track of what keywords are working and which ones aren’t.

Now you can use the remaining columns (B, C, and so on) to store data about each keyword. The first piece of data you need to find is an estimate of how many times people search for the keyword each day.

You can use free tools like Bruce Clay, Inc.’s Search Engine Optimization/KSP tool to measure daily search activity for specific keyword phrases on the Internet across the major search engines. It’s not just guesswork; you can see actual counts!

The following tools are available online for checking search activity by keyword (and many other search engine optimization-related tasks). They are listed in no particular order:

  • Bruce Clay, Inc. offers a free keyword activity tool on its Web site. Use the Search Engine Optimization/KSP tool to find search activity counts, category information, and demographic data. The full toolset is also available for $39.95 per month and features more robust versions of the free tools.

  • Wordtracker is a paid tool that measures keyword traffic. Wordtracker offers both annual plans and monthly plans. The annual plan runs about $329 a year, and the monthly plan costs $59 per month. They also offer a free trial version.

  • Keyword Discovery offers a subscription service that runs about $49.95 a month.

Keep in mind that the figures are only estimates and should be taken as general guidelines. However, they give you a general indication of activity levels. For instance, if the keyword research tools say that keyword A supposedly has 20,000 searches a day and keyword B only 200, you can look at the numbers proportionally and trust that while the actual counts may vary, relatively speaking, keyword A is searched 100 times more frequently than keyword B.

A keyword spreadsheet lets you compare data for each keyword.

A keyword spreadsheet lets you compare data for each keyword.

On your spreadsheet, make column B Searches or Activity. Using one of the tools mentioned above, enter your keywords and fill in the daily search activity count in column B for each keyword (shown in the above figure). You may find it tedious to try out each keyword and copy the resulting activity number into your spreadsheet, but this data will be extremely useful for you in evaluating your keywords and improving your search engine optimization. You need benchmarks and figures, not just guesses, to make sure you’re optimizing your site for the right keywords.