But without the right strategy, leveraging search for business can feel a lot like trying to capture lightning in a bottle.
In this post I am going to step through creating a keyword opportunity model to project estimated revenue from SEO.
I am going to do this 2-fold:
- With an simplified model to help develop the initial concept, and then
- With a detailed, assumption-based model as an example
This approach is meant to help you weigh different keyword opportunities and uncover where your SEO priorities should be focused. Make sure you use this in conjunction with SEO competitive analysis and are leveraging on-page optimization for your target URL’s.
The Simple Keyword Opportunity Model
The simple model is meant to get you thinking about the implications of cost versus revenue in terms of return on SEO.
For revenue I will be using the following formula, where monthly search volume is representative of [exact] searches from Google’s keyword tool.
Conversion value will vary depending on the goal of your SEO campaign, for example; E-commerce sites could use average order value where lead generation sites might use their average lead value, in any case make sure you use an actual dollar amount.
The value of a keyword is pretty subjective based on your conversions, and therefore you need to utilize goal-specific conversion rates to customize this formula to fit your business.
For average SERP click-through I prefer to use an aggregate measure to project returns from larger scenarios, for example if I designed a model to target page 1 of Google I would use a 6.1% click-through rate, but if the model was specifically focused on top 5 rankings I would use 12.1%. These are just my starting point figures.
If you want to use more approximate measures of average click-through for exact rankings, a very helpful case study from Slingshot SEO on SERP CTR produced this lovely graph (click to enlarge).
18% lines up much closer to my historical SERP performance data than the previously reported data from Optify, which posited a potential of a 36.5% CTR for position #1.
Using just the four metrics from the simplified model, I have created a Google Spreadsheet that you can access below.
Important Note; once in the document click File > Make a Copy so you can edit the file and add your own data.
Within the spreadsheet you will see there are 2 sheets; Simple and Advanced, and all values that drive the model are highlighted in yellow.
Use the simple model to get a basic sense of how things work; how different values for different metrics will impact your total monthly revenue, and don’t be afraid to make adjustments and get creative.
Try inputting the lifetime value of a customer to forecast far into the future or bake in a measure for time on site and how certain thresholds may lead to higher variable conversion rates. If your goal is to drive newsletter signups, measure it; think outside the box! Then share what you did.
For cost assumptions I’m going to use average costs associated with developing one URL targeting one primary keyword. In this scenario I’m assuming the website already has a baseline of steady traffic, but not much, say 5,000 visits per month or so. This is important to note because it reduces the need for additional link building or paid
advertising promotion for posts to gain enough rank signals to make it to page one.
For this model, the costs associated with content development are:
This represents the average cost to do post-level keyword research and compile a matrix for the URL.
This represents the actual ideation and writing of all of the content for the URL.
This is representative of the editing and actual production of the content; final proofreading, formatting images, and loading into the content management system for publication.
Adjusting For Competitiveness
A critical part of any good business model is adjusting for relative competition.
I use a relative measure of keyword difficulty to do this, either average domain authority or competitiveness index, but you could just as easily use keyword difficulty score.
In the simple model I call it ‘Average SERP CI,’ but it doesn’t have to be, feel free to throw average SERP DA in there and everything will still work, just make sure you keep your metrics consistent between column G and the competitive weightings in H2 and H3 (click to enlarge).
About Those Competitive Weightings
Cell H2: Cost Multiplier – The cost multiplier is pretty much exactly what it sounds like; it multiplies your cost based on the competitiveness threshold you set.
This represents how much more expensive it is for you to create content that can compete for a first page ranking based on your website’s relative authority. If your website has a DA of 60, it is going to be easier for you to compete in SERP’s where the average is only 50 versus 70.
Cell H3: CI Threshold – This is the authority or competitiveness threshold where it becomes more expensive for you to compete.
This threshold should be relatively close to your website’s authority score, so if you have a DA of 40, you might want to set this at 50.
PLEASE NOTE: As is the case with this model in general, both of these measures that drive the cost side of this evaluation are extremely subjective. You will need to dial them in over time, so start conservatively and try to glean data from your website so these are as accurate as possible.
Let’s Build A Test Case
For this example I am going to use an SEO vertical market and focus on a set of closely related keywords both in terms of semantic relevance and related user intent.
Just for fun I’m including tools, services, and consultant queries (click to enlarge).
If you haven’t already opened the spreadsheet, do that now ›
You will see that I have loaded all of the above keyword data into the model and plugged in some test conversion data.
I created a column titled (Optional) where you can enter in a specific SERP rank between 1 and 5 and it will adjust the potential visitors based on the CTR values entered in F2 through F6, currently it’s configured with the CTR data from the Slingshot SEO case study.
To make this applicable to your website:
- Replace the SEO keywords with your keywords and their estimated [exact] monthly search volume
- Enter your average costs (or total cost into D6) for creating a new URL
In case you’re not sure what your costs are, here’s an approach to estimate them:
Estimating Your Development Costs
Everyone’s costs are different and some are not straightforward.
Do your best to average out what it costs to produce a piece of content from start to finish, and try not to leave out any piece of the development life-cycle.
For example, a company’s content development process might look like this:
Try to boil down all of your costs to an hourly rate and then average them across each functional department, so you end up with a representative cost for each piece of your process.
However your process works, try to capture all of the costs. If all of your resources are salary, figure out what their hourly rate is and estimate their average production time for all of their tasks on one piece of content.
Once you have a handle on the costs, input them into the model in D3 through D5, or place your total cost into D6.
Estimating Your Revenue
Revenue is going to be a bit easier to estimate, especially if you already have analytics or tracking in place and you know exactly how much you make per visitor or per conversion.
The model is setup to take care of estimating how many visitors you can acquire each month and your revenue per conversion, all you need to provide is your:
- Average conversion rate, and
- Average conversion value
Input these into cells B4 and B5, respectively.
The Advanced Keyword Opportunity Model
Hopefully by now you’ve taken some time to test out the simple model within the spreadsheet. Now it’s time to drill down into more specific costs, adjusting for a wider range of variables based on more specific heuristics.
In the simple model we used a base cost and a multiplier, for the advanced model we will still assume a base cost, adding in an average cost per link and discount rates based on some additional competitive factors.
The competitive factors we will use to augment costs in the advanced model are:
- Domain Authority (DA), and
- Page Authority (PA)
To take into account both the logarithmic nature of DA and PA I will be using discount rates for each of these metrics, to compute an estimated net present value to be used as our multiplier.
First it is important to understand a little bit about the discount rates.
The purpose is to allow for the model to be adjusted for volatility and the specific nuances of your website(s).
The best way to get started is to set a conservative cost multiplier (higher versus lower) for both DA and PA and then dial in discount rates to help represent variable ranking factors such as temporal ranking factors, query deserves freshness, or even brand bias.
Newer, less established websites can outrank their older, higher authority competitors – the SERP’s are different on an almost case by case basis.
As your website authority grows, your link profile gains velocity, and builds trust – you should be able to adjust the discount rates downward to reflect less cost to reach page 1.
I have ranked pages with a DA/PA of 50% less than the SERP average, sometimes with only a few links and within a few short days or weeks. This type of volatility is not predictable, but that’s the not the end-goal here.
The reason you are making assumptions and dialing in ranking variables is to develop a measure within an ‘order of magnitude‘ so you can begin to project cost and
performance return from SEO.
Adjusting Metrics to Capture Cost
Now that you have your average cost per URL, you need to figure out the adjustments for the model based on additional external factors and work these into your discount rates.
Average cost per link is a tough one, so my recommendation for the purposes of this model is to use a minimum DA threshold of 30, i.e. link cost is representative of a link from a website with DA 30+.
Discount rates are going to take you the most time to develop, where as your threshold and multiplier you should be able to figure out in a few weeks.
If you would like help getting started, I will be sending out some example discount rates to my subscribers – so if you haven’t already, take a moment and sign up for updates »
Measuring Potential Return – Evaluating Based on MER
MER stands for mean efficiency ratio, and is the metric I use to evaluate keyword opportunities.
It is a simple computation done by dividing total revenue by total cost, which provides you an at a glance metric for gauging profitability. An MER of 1.0 means that you broke even and made 100% of your initial investment back – so anything greater than 1.0 is
If you’re tracking all of your costs and revenue at the keyword level, this is a great simple metric for measuring ROI. I have set up some simple conditional formatting within the spreadsheet so positive MER cells render green.
Adjusting Based on Actual Performance
Your SEO business model is only as good as the data it is built on, so if you want it to be usable, it needs to be accurate. Set aside time to update and refine your model to be as accurate and representative of your business as possible.
Treat it honestly and with respect; if your costs go up, change them. If you find that your carry costs for getting to page 1 are higher, increase your discount rates.
This is by no means a perfect system or evaluation platform. It is, however, a lesser discussed element of SEO and keyword evaluation that I would like to discuss.
What are your thoughts?