Includes free spreadsheet with all formulas and charts!

Plug in your own numbers and watch the magic.

If you read part one of my little exposition on PPC vs. SEO ROI, you should have noticed that the numbers were simple and unrealistic. Indeed, for illustrative purposes, those numbers produced a 100 percent return on PPC investment which is absurd.

Since I already had the spreadsheet with most of the formulas, I decided to tweak the assumptions a little bit to see how things might play out more realistically. The view from 30,000 feet on that is here.

In the name of science, I was extremely generous with the PPC assumptions (ROI panned out to 30-some percent, which is darn good for that kind of campaign), and I assumed only a $1.50 cost per click on a product that produced a gross per-unit sale (profit) of $100. A 2 percent conversion ratio for that type of item is generous as well. But I wanted to be fair. The scientist in me said that if the numbers showed PPC to be the better marketing method, by golly, I would report that and retract my previous post on the subject.

In contrast, the SEO assumptions were conservative. In the second part of the series, we are a bit more realistic, assuming a very slight increase in our SEO clicks over time. At their peak, they are barely a third of the PPC total. The biggest factor (and the one that will be disputed) is the higher SEO conversion ratio -- five percent is totally reasonable if you've chosen your keywords wisely. Someone searching for "buy Product X" is likely to buy after finding Product X in a search result, for but one ridiculously obvious example.

Costs were assumed to be constant for the PPC campaign and higher for the SEO campaign in the beginning with a recurring maintenance cost. This is a fairly typical scenario, I believe. You're going to cap your PPC costs (I hope) and your SEO people will require compensation for improving your results over time.

Frankly, I think the traffic assumptions are underestimated for SEO as well. Only 100 clicks per month? What kinds of keywords are those? Anyway, the main point was to show that PPC can deliver more traffic. In the real world, all the numbers would be higher, except the conversion ratios and ROI which are really the keys to this whole thing.

Because the assumptions were so generous for PPC (cheap traffic for a pretty expensive product) and so conservative for SEO (low traffic and, frankly, low conversion), before plugging the numbers into the spreadsheet, I expected PPC to come out even or ahead of SEO over time. It doesn't work out that way, at least with these assumptions. I suppose you could lower the conversion ratio for SEO to 2 percent, and PPC would come out on top. I've never seen a PPC campaign that can deliver SEO-like conversion rates, though. That doesn't mean they don't exist, but it would take Jedi-level PPC skills and totally incompetent SEO to get the same conversion ratio, IMO.

The ROI results are the most surprising: SEO turns out to be the better investment (not that you should not do both, mind you) over the long haul, although the initial returns are negative. PPC does produce bigger sales numbers, but profits and ROI are significantly lower.

I guess the lesson here is that if you need quick sales, you're better off with PPC -- provided that you know what you're doing and can run a PPC campaign profitably -- but if you're in it to win it over the long haul, you had better put a little something into SEO.

Attached for your convenience are the actual spreadsheets used in this experiment in OpenOffice Calc, Excel, and xml formats. (If the xls file doesn't work in your version of Excel, copy the xml file into notepad and save it as "whatever.xml," and it should open in newer versions of Excel.)

You can plug your own real-world numbers in and all the formulas are there for you to calculate your ROI and chart performance. I really should charge for this stuff, but whatever. If you find it useful, feel free to throw a few bucks in my tip jar.

Oh -- and if you want to improve your SEO ROI, try WebCEO -- seriously. I've used a lot of SEO tools, and this one is head-and-shoulders above the rest. Try it for yourself and experience the sweet satisfaction of search engine domination. (Disclosure: In case you couldn't figure it out from the big banner at the top of the page, I stand to gain monetarily if you try WebCEO.)

Thanks!