Boost SEO ROI by 400% With MS Excel

I'm not kidding. The way to do this is by calculating the present value of future cash flows you are generating for a client. If a client is running a PPC or banner campaign they see a big boost in traffic and then when the campaign ends, most of the traffic disappears. However, SEO campaigns work very differently. If you boost a client's average monthly traffic level by 20% that growth would likely be sustained into the future if they ended the campaign. There could be a slight drop off, but for the most part the site would hold at the new traffic level.

So why aren't you taking any credit for cash flow being generated in the future? If you've created more traffic, and in turn more revenue, you are selling yourself short by not integrating the concept of present value into your ROI calculations.

Here's how it works:

• Find the site's average level of traffic before the campaign.
• Forecast this forward. If you've got the skills use a straight line or weighted regression model to do this more accurately.
• Use the site's average conversion rate to forecast "flat" revenue moving forward. You can also just start by forecasting revenue instead of traffic. Either way, you need to arrive at a relatively accurate revenue forecast.
• Now, you've been running your campaign for 9 months, and if you know what you are doing, you have some traffic or revenue growth to show for it. Let's assume this growth is 20% both in traffic and revenue.
• When you're putting the next report together you need to use future present value calculations and/or a terminal value to calculate the present value of those future cash flows. I'll give an example below.

How To Calculate Present and Terminal Values

We will now create up fictitious campaign for an example. We are going to assume the site we are working with received 50K visits a month and converted at 4% before the campaign. The value of each conversion is 30$. This means the site was making 60K a month in revenue.

We are going to assume that we have been optimizing for 9 months and have grown traffic by 20%. Traffic is now at 60K per month. We'll assume that if the client we're to cancel today, the traffic would remain at 60K per month for the foreseeable future. That means at the 4% conversion rate, with $30 conversions the new level of revenue is 72K per month. This is an increase of 12,000 over the pre-campaign revenue level

Usually the SEO would simply say that they increased ROI by 20% (as long as they are not being shady and taking credit for the client's existing traffic) or 12K in revenue. This is wrong. Let's apply the present value calculation to this scenario and really crank up the ROI. The basic PV (Present Value) calculation for this is:

PV = FV / (1 + i)^n

Using this method would require this calculation to be applied to each future cash flow and then sum them together. The good news is that Excel makes a handy formula that makes this calculation much simpler.

In Excel, just use this formula: =Present Cash Flow + NPV(Interest Rate, Range of Cells Containing future cash flows)

The result of this calculation is $59,332 vs the $12,000 calculation performed previously. That's about a 400% increase in ROI and is much easier than building links :)