This worksheet is similar to the Missed Opportunity Matrix but lets you estimate traffic based on the rank you think you can achieve and the buy cycle of the keyword phrase. This is an Excel Version of the more sophisticated model in Back Azimuth’s Rank to Click analysis that uses multiple segments of the keyword data to help you understand conversion opportunities.
This process takes into account a number of factors for determining that “Just Right” keyword phrase. As we have told you in the book it is critical to match the searcher’s interest with the right content. The better you do that the higher the potential for conversion.
You can download the Rank to Click Conversion Worksheet and follow along to make your predictions. (Note this file will not display but will be downloaded to your downloads folder)
Step 1: Keyword – Add a list of keywords to the worksheet. It currently is set for 20 but if you need to add more words just copy the rows down and the formulas should work.
Step 2: Buy Cycle – Set the buy cycle phase for each keyword phrase. Note, on the right the table “Buy Cycle Phrase Multiplier” you can change the names to match your phase of the buy cycle. The conversion percentages can be updated to match your actual data. This is an average from Back Azimuth’s assortment of clients. If you change this table and use those names in the Buy Cycle Column they will match.
Step 3: Demand – Add in the Search Demand from Google Keyword Planner or your favorite tool for generating demand.
Step 4: Current Traffic – Add in the current traffic for each keyword phrase as found in Google Webmaster Tools. (Don’t use your web analytics system for this metric because the search engines now block accurate measurements.)
Step 5: Current Rank – this can also come from Google Webmaster Tools or your favorite rank checking tool.
Step 6: Set Realistic Rank – Based on your review of the ranking factors, your content and the competition what rank do you think you can realistically achieve? This should be set as conservatively as possible especially if you will be held accountable for these numbers.
That is all you need to change and the calculations will do the rest. From this list you can see which phrases have the greatest potential and where you should focus your efforts.