What would ya say ya do here?

Get A Rough Estimate of Potential Organic Traffic With Excel

Updated: 3/9/2017

I was just wondering earlier on Twitter if it’d be possible for a tool like Ahrefs or SEMrush to take my list of keywords and give me a ~guestimate of just how much traffic you could reasonably expect from them. After all if Gary Vaynerchuck says I need to “figure out how that business makes money in the next six minutes.”[1] then I should get to it, right?

Here’s A Google Spreadsheet That Estimates Organic Traffic Potential

So while the new 2.0 Keywords tool from Ahrefs does do a single keyword traffic estimate of traffic it’s not a bulk or list tool (yet!) I have created a spreadsheet populated with real data for Nashville dentistry with some simple formulas that you can duplicate and then adjust & apply to your own keyword list. ** I created this for some client research but realized it might be cool to put it out there for others to utilize the idea.

Rough Organic Keyword Traffic Projection Spreadsheet

Here Are My Assumptions About Clicks

I was watching an Ahrefs video on keyword research and he shared their assumption about SERP click behavior: They estimate 50% of est. traffic volume per keyword flows to the top 3 ranking domains. I really forget what they sourced that from…maybe they can comment on the post with their source… ANYWHO!

So I took that assumption a little further and estimated:

  • #1 would get about half of overall volume so that’s 25% of traffic
  • #2-3 would get about half each of that half so that’s 15% of traffic
  • #3-10 would get 50% of traffic divided by the 7 rankings
  • #11 and below can assume 0 traffic

Want to learn more about my approach to SEO and the measurable metrics businesses should use to track their online marketing success?

Download My SEO Metrics eBook For Free

My Assumption About a REASONABLE expectation

No. You don’t get to rank #1 across the board. It’s NOT reasonable to expect out of most SEO campaigns.

life-is-pain

So let’s paint a rosy, but maybe reasonable picture:

Let’s say that 25% of your keywords get to #1, 50 are #2-3 and 25% are #3-6

If you are more of a pessimist, or the client isn’t offering that much budget or it’s a rough neighborhood for competition then revise those down on the spreadsheet.

  • For 25 Nashville dentist keywords the estimate volume was: 2,540 per month
  • Using my assumptions above I estimate a reasonable ownage of the SERP would net: 458 Organic sessions per month, not including BRAND or LONGTAIL keywords.
  • Multiply that organic traffic by your site’s Organic Conversion rate (if you don’t have access to Google Analytics or have goals configured then you can assume between 2.5% and 5%) to get your est. # of leads. 22 Leads
  • Multiply your est. leads by your estimated Close rate on leads (differs wildly for every niche, I just used 50% arbitrarily) 11 clients
  • Multiply the number of leads by the average Revenue per new client/sale (I assumed $1000) and BOOM You have the estimated organic revenue increase from a “REASONABLE” result of an SEO campaign.  $11,000
    • You need to keep in mind that some of your revenue from those clients might be upfront and some of it might be recurring or monthly revenue. If you have monthly recurring revenue from your clients, then you should take the needed time to figure out your “churn rate” to come up with an idea of how long you usually keep your customers.

      “When you’re figuring out your churn rate, especially at an early stage in your business, the process of acquisition should be taken into account. I.e. Your friends, family or close business referrals are going to have a different shelf life compared to clients you’ve won on your own merits, so to speak” – Chris Sipe of Gigamunch, an international meal delivery service startup based in Nashville.



      1. Btw that Vaynerchuck quote was from this coworking space‘s blog.

One thought on “Get A Rough Estimate of Potential Organic Traffic With Excel”

  1. Cool idea! Have you compared estimates to reported organic traffic form GA? I would assume that #1 gets closer to 80%, and that of that 80%, I would estimate somewhere around 30% of those visits to the #1 result will also visit #2 at the very least.

Leave a Reply

Your email address will not be published. Required fields are marked *