One of the hardest parts of SEO is projecting what results you will get out of it. If you’re an SEO service provider you’ll be sure to convert more clients, and if you’re the head of your SEO department at your company, you’ll be sure to please your superiors. Below is our process for producing such projections at FaceySpacey. Each step below describes a column in a spreadsheet you will produce. Here is a sample spreadsheet with all the formulas built in (feel free to download it in Excel format to your own computer):

https://spreadsheets.google.com/spreadsheet/ccc?key=0AoURnKZh8J-SclRCd0NKTWROWlFVTWVtUkUtVXBUX1E&hl=en_US

And here's a Prezi presentaton that will help you see what each column in the spreadsheet means:

Here is the in depth version of what it all means (refer back to that spreadsheet and Prezi presentation as you read this):

1) We list all your keywords potential clients/leads are likely to search for in the first column of the spreadsheet.

2) For each keyword, we go to Google’s “Keyword Tool” previously described in the last SEO tutorial, and discover how many people are searching monthly for each of your keywords and list them in this column.

3) We realize that google isn't the only way people search the web and use the coefficient of 1.5 as a multiplier (across all cells in this column) to determine how much traffic a search phrase gets across all search engines.

4) We prepare what conversion rate we think we'll get out of your visitors, and enter it across all cells in this column. For existing sites, you should have this data. For new sites, you’ll have to give your best guess based on your knowledge of conversion rates in your industry.

5) Next, enter the average conversion amount in all cells in this column. If you sell a service where a client keeps coming back over a long period of time, just estimate how much the average client spends with you per year.

6) Now Before you even get any clients or customers from search engines, you need to determine what percentage of them actually click your link in a google, yahoo or bing search results page. This is called the "Click Through Rate" or CTR. Top listings get the most clicks, and the lower your listing is the lower the CTR percentage is. So if 200 people search for "New York Plumbers" per month, and 23% click the top listing (i.e. the top listing in google has a 23% CTR), then that listing gets 56 visitors per month from google. In three columns here we’ll assume we have a 1st position ranking, a 2nd position and a 3rd position. Industry standards are 23%, 6.5% and 4.6%, respectively. Fill up all cells in each of these columns with those values respectively.

7) Now, that we know the CTR, average conversion rate, and average conversion amount, we can determine the number of visitors you will get from search result pages, the number of conversions and the dollar amount sum of all those conversions. Add 3 columns for these metrics next to each of the 3 columns in step #6 above--this way we can predict these things for each potential ranking position you may get for your keywords.

Over time you can plug in more and more accurate conversion rates and average sale amounts as you see the real thing in google analytics--and the whole spreadsheet will update if you’ve properly used math formulas between cells. To do this, you of course need to make, for example, the total sales column multiply the values of the total visitors times the conversion rate times the average conversion amount.

Also, you'll be able to hone in on the precise metrics you need to improve. For example, you may realize that it's more important to increase the average sale amount than it is to increase your positions in google search result pages or conversion rates.

8) Now, in a separate sheet, we add up the totals for the number of visitors, conversions and total sales amount across ALL your keywords in order to project you’re aggregate results. We break this down into 3 groups based on the the top 3 positions you’ll be shooting to get, like how we produced similar metrics for each position in step #7 above.

9) Finally, you will have your spreadsheet tell you--through the use of formulas across cells--what your monthly and daily totals are. It should also let you put in some coefficients that correspond to the fact that their might be more keywords you're targeting than in this sample pool of keywords. For example, in the sample spreadsheet provided, you're looking at only 30 keywords. More often than not you will be working to rank for hundreds of keyword phrases not here, which means additional traffic. The yellow rows in the sample sheet let us say that there will be 75% more traffic from other high traffic keywords, 50% more traffic from from keywords with a medium traffic level, and 25% more traffic from keywords with a low traffic level. The idea in this example is that the keywords that will get only 25% more traffic are what we call "long tail" keywords and might end up being something like: "free online new york city plumbing." A Short tail keyword would be "new york plumbing." After that in the orange rows, we add the complete aggregate up of all your potential keywords.

So that’s our technique. The following visualization, using Prezi.com’s presentation tools, may help you grasp it better: