How to Use the SEO Projection Template

Key metrics covered in the KOB dashboard

Before we look at how to set this template up, let’s first take a look at the dashboard it’ll provide. I’ll also give you a quick rundown on why each of these is important and how to interpret the information it’s giving you.

For some quick context, this data is driven by the following info that you enter during setup:

• Monthly search volume for competitor keywords
• Your website’s average conversion rate from visitor to lead
• Your sales team’s conversion rate from lead to customer
• Average client LTV (lifetime value)

It also factors the average Google organic click through rate into this modelling. In other words, if you rank #3 for a given keyword, what percentage of those searchers will click on your website.

Monthly organic traffic forecast

This chart shows your best case scenario if you were to improve your ranking to position 1, 2, 3 or 4 for all the keywords you’ve entered.

What you’re looking at here isn’t attainable metrics, it’s a snapshot of your absolute ceiling if you were to target the keyword set you’ve entered. If the numbers you see here are lower than you would deem to be a feasible end result, you’ll need to look at a different strategy or at least a new keyword set.

Monthly organic leads forecast

Following on from the previous chart, this one gives us those same metrics but for organic leads rather than traffic.

These metrics are based on the average conversion rates you entered.

(Projected Traffic * Website Conversion Rate) * Lead Conversion Rate

Monthly revenue forecast

In the end, marketing is about revenue. The third chart in your dashboard shows you what these ceiling metrics look like as bottom line figures—the total amount of revenue you could possibly make from this keyword set.

Note that this metric takes your natural attrition into account—from visitor to lead, then lead to customer.

((Projected Traffic * Website Conversion Rate) * Lead Conversion Rate) * LTV

Realistic monthly traffic projections

Now that we have our ceilings to verify that the project is viable, it’s time to look at some more realistic expectations—the volumes you can compare your actual results against.

We do this by looking at the percentage of total keywords you rank in a given position for.

For example, if you ranked #4 for 10% of the keywords entered, you would get X traffic. This can be compared against ranking #3 for 20% of those keywords to see just how much of an influence you can have with relatively mild improvements.

(Total Monthly Search Volume * Avg Google Click-Through Rate) * % of Terms Ranked

Realistic monthly revenue projections

Naturally, the next step is to look at what that traffic means as a revenue figure to make those business decisions easier.

Again, this is based on the average LTV you entered and accounts for that natural attrition from website visitor to paying customer.

((Total Monthly Search Volume * Avg Google Click-Through Rate) * % of Terms Ranked) * LTV

Actual organic performance vs spend

Last but not least, this chart is a visualization of the traffic and leads you’ve actually generated through Google search. This is compared against your monthly marketing spend to see where that ‘break even’ point occurred or how far you are from achieving it.

While it’s normal to run at an initial loss, SEO should represent an asset long term, running you a net profit.

An overview of columns in the KOB sheet

There’s a lot of data visible in the KOB sheet which can be overwhelming at first. The good news is, you don’t need to get familiar with this sheet until you’ve got proof of concept and are ready to start planning your campaign.

Once you reach that point, here’s a quick look at each of the columns in the sheet.

Priority Score

This score factors in keyword search volume, value and intent as a way of scoring terms by opportunity. Of course, this is just one factor that should be taken into account but it can be a good jumping off point.

Generally, if a keyword has high value, high search volume and high intent, it likely represents a lot of value to your brand.

Note: This score relies on search volume, traffic value and keyword (kw) intent. If any of these fields are blank or 0, the score will also be blank.

KW Intent

In simple terms, this column represents how close the user is to making a purchase, based on the intent of the search term.

Someone searching for a broad term like ‘Cancun’ could be looking for any number of things and would represent a very low intent. 

On the other hand, someone searching for ‘all inclusive resort accommodation cancun 2023’ clearly knows what they want and are probably a few steps away from booking. This could be considered high intent.

KW intent is factored into the above priority scores as it’s a crucial part of the puzzle. All the search volume in the world won’t bring you an ROI if you’re ranking for generic terms that might just be used by school children while they research an assignment.

Volume

This is the average monthly search volume for that keyword over the last 12 months.

KD (Keyword Difficulty)

This metric represents the amount of effort required to rank in the top 10 positions for each term.

Exactly how this figure is calculated will depend on the tool you used to gather this data. The exact mechanics of the score don’t matter much as it’s all relative. A KD 20 requires far more effort than a KD 5, no matter how it’s calculated.

CPC (Cost Per Click)

CPC is the average amount advertisers are paying per click for that term in Google Ads.

Generally, cost per click will increase with the value that term represents. Put simply, if a given search term wasn’t making advertisers money, they wouldn’t keep paying for that term and the CPC would decrease.

Note that a 0 or blank entry in this column is not an error, it just means there’s either no competition or not enough CPC data for that search term.

Traffic Potential

This number isn’t quite as self-explanatory as it sounds. It’s an estimation of the total search traffic the top-ranking page is getting. This is not to be confused with the amount of traffic you could expect from that one keyword alone.

Let me explain.

If a page ranks #1 for a given term, it will undoubtedly be strong enough to rank well for a host of other terms as well. The traffic potential metric includes traffic that page is expected to receive across all the terms it ranks for.

So, if the top page ranks for 10 different search terms and is expected to receive around 1,000 visits from each of them per month, the ‘traffic potential’ would show 10,000.

Traffic Value

A simple calculation, the traffic value simply multiplies the average CPC by the total traffic value.

Essentially, it gives you an indication of what you’d have to pay through Google Ads to get the same amount of traffic as a #1 organic ranking. This can be helpful for building an informed strategy.

Minimum Links Needed

Grouped into buckets based on KD, this gives you a ballpark estimation of the number of referring domains your landing page will need to compete with the top 10 search results.

Backlinks are just one of the many ranking factors involved but it gives you some idea of how much of a gap you’ll have to make up if you want to be competitive in the long term.

Link Cost ($150 average)

Assuming an average cost of $150 per successful link placement (including your internal costs), this is the approximate spend required to achieve that required link volume.

Monthly Leads

This metric looks at the monthly search volume, average click-through rate and your site’s conversion rate to give an average monthly lead volume for each keyword.

Essentially, if you ranked #1 for this term and did nothing to improve your conversion rate, this is how many leads you could anticipate getting each month.

Monthly Revenue Per Keyword

Last but not least, this column is exactly what it sounds like—the monthly revenue each keyword is worth by itself if you were to rank #1 for it.

Since ranking for 1 keyword also means you’ll rank well for a host of related terms, consider this a lowball estimation of each keyword’s value.

Setting up the KOB template yourself (it’s easier than it looks)

Now that we’ve covered how this template works and what all the charts and data mean, it’s time to start setting it up. Thankfully, there’s not much input required so the complete setup should take a matter of minutes.

Here’s what you’ll need:

• Your top 3-5 competitors*
• Access to either Ahrefs or SEMrush
• Access to your Google Analytics account
• Your average customer LTV
• Your sales team’s average lead -> customer conversion rate

*When choosing your top 3 – 5 competitors, select them based on the strength of their website, not how closely they compete with you in other avenues.

Note: We do not see or collect any of the data you enter into this template. It lives exclusively in your Google Drive / Google Docs.

Enter your base metrics in the ‘Start Here’ tab

This tab sets up the basis of your projection models later on.

All you need to do is enter the required data marked in yellow for both the “SEO Traffic Model Inputs” and “SEO Breakeven Analysis” sections. A brief explanation of each is provided.

 

Export and compile competitor keyword data

Next up, it’s time to export and compile your competitors’ keyword data and import it into this template.

1. Log into your Ahrefs or SEMrush account. Download the complete keyword list for each and compile them all in a single spreadsheet.
2. Deduplicate the keywords so you don’t have double ups
3. Remove any branded terms for those competitors or any terms that might specifically relate to their unique products or services

CTRL+F and filtered tables will save you a lot of time at this step.

Paste this data into your KOB file

With this data compiled and reviewed, it’s time to drop it into your KOB file so you can begin the analysis.

Note: Don’t sort of filter any of this data until all below fields have been entered.

1. Paste these into the KOB section of the sheet. You will need
   1. Keyword
   2. Volume
   3. Keyword Difficulty (KD)
   4. CPC
   5. Traffic Potential

To simplify this part of the process, these column headers are shown in blue.

Identify Keyword Intent

In the KW Intent column, select the most appropriate level of intent for each keyword. Based on the intent behind that search term, you’re looking to identify how close these users are to becoming a paying customer.

Keyword intent examples

Keyword	Intent	Reason
San Jose	Very Low	There are endless reasons someone may search for this generic term. Looking to see where it is in the country, driving directions, accommodation planning, ZIP codes, history etc.
Restaurants	Very Low	Again, there are so many reasons someone might search for such a generic term. They’re not about to make a reservation any time soon.
Restaurants Japantown San Jose	High	They’re looking for restaurants in a very specific area. We can assume they’re actively searching for somewhere to eat, either now or in the near future.
Restaurants Japantown San Jose open now	Very High	The intent for this search term is clear. They’re in or near this area and looking to eat now.

To speed this process up, you can use Ctrl+F or column filters to do this in large blocks rather than having to go 1 row at a time. For example, you could mark all search terms that include the words ‘price’ and ‘cost’ as High Intent.

Take a look at Hubspot’s overview of user intent in SEO for a more detailed look at how to make these decisions.

Your next steps

With these fields filled out, you can head over to the Dashboard page and see what these metrics mean for your brand. They should give you the projections you need to see whether or not this is a viable angle for your business and start prioritizing that workload.

The dashboard is also where you can enter your month on month metrics from Google Analytics, too.

This template works great to survey your industry’s search landscape but it can also be repeated for each specific phase of your project. If you’re launching a new product or feature, just create a new copy of the template and run through the process again with search terms related to that new product. This is a simple way to make sure those projections show the traffic and revenue figures you need before you start.

It’s all about simplifying this early decision making phase and demonstrating the degree of opportunity. This is a process we’re regularly expanding so if there are added features you’d like to see in the template, we’d love to hear about it. Just send us a quick message now and we’ll take a closer look.