Excel rocks.

I mean yeah its Microsoft and it could be better, but there is tons of mathematical fun to be had with Excel’s formulas and graphs.

Over the past week or so I’ve been working on a table, full of lots of simple calculations designed to help me understand the economic patterns in search marketing. Here’s what I came up with.

First put yourself in the shoes of a merchant, you sell hats and on average you make a 10-dollar profit per sale through your website. You convert about 1% of your visitors to buyers so each ‘average’ visitor is essentially worth 10 cents to you. You want more traffic so you’ve decided to create some new content for your site to build natural SEO traffic. You take the time to write and code the new pages, or you outsource the job, either way lets say each new page of content costs you $100 to create and rank on average (including copy writing, HTML and linking). Each page is targeted at one keyword and your site ranks very well so you generally manage to get about a 1% “clickshare” of the total overall keyword traffic for the keyword you are optimizing (based on something like wordtracker data). You would like to make your investment back in increased sales in a month using the new traffic the pages will generate.

Using these givens there is a formula:

**CostPerPage/KeywordPerPage/ProfitPerVisitor/DaysToBreakEven/Clickshare = TrafficThreshold**

So for our hypothetical situation that is: 100/1/0.10/30/0.01 or 3333.3.

This means that for you to break even in one month with these cost and profit numbers each keyword you optimize for needs to have at least 3334 total searches per day. This is your traffic threshold.

There are lots of ways to improve this number, optimizing each page for more than one keyword, increasing your profit per visitor (by increasing average profit per sale or conversion rate), being willing to wait a bit longer to make your money back or reducing your cost to create new pages. The numbers will change but the formula remains the same.

But lets look at how each individual factor controls your threshold.

First cost per page, starting at $1000 per page and going down to $10 your profitable traffic threshold per keyword looks like this:

As you increase the number of days you’re willing to wait to recoup your initial investment the graph looks like this:

By optimizing for more keywords per page you can bring down your threshold in this curve

and finnally, by increasing your profit per visitor you do this to the shape:

One initial observation that can be made is that the cheaper you can make your pages the less traffic you need per keyword to justify the expense, its a purely zipf curve, no point of diminishing returns. On the other hand with profit per visitor, keywords per page and days to recoup, there can clearly be seen a point of diminish returns past which improvements to your numbers are no longer low hanging fruit. For each of these three metrics there is a sweet spot where you can minimize your traffic threshold with the least amount of effort.

The point of trying to minimize your traffic threshold is to make your business model nimble enough to squeeze way down into the tail of your niche and take advantage of as much of the available keyword traffic as possible.