SEO gap analysis spreadsheet

Quickly analyse SEO effectiveness for the keywords you are targeting to improve your "share of search"

  • Author: Dave Chaffey
  • Format: Excel 2003 (.xls) template for use in later versions

About our SEO Gap Analysis Spreadsheet

An SEO gap analysis helps you review your "market share of consumer searches" by comparing the number of searches performed for the keywords you are targeting against the number of visitors you get to your site through natural search in the organic search engine listings.

Where there is high demand for products or searches indicated by the number of searches, but you have a relatively low share of visits to your site from natural search this is a gap that you need to understand and review SEO techniques to visits.

To help with this process we previously published an Excel Gap analysis for search marketing which members used to compare paid and natural search effectiveness in attracting visits to a site for a target list of keywords. Unfortunately this type of powerful analysis is no longer possible since Google introduced secure search leading to a growth in Not Provided Keywords where the search term entered by searchers is unknown.

But Google has provided a new tool to help SEO analysis within Google Webmaster Tools which can still be used for a gap analysis for marketers looking to assess their SEO performance.

Our SEO Gap analysis spreadsheet is a template which can be used to quickly import these search terms and compare actual keywords which drive visits from a site against the keywords you are targeting.

We've used this gap analysis approach on many search consultancy projects in financial services, retail and travel sectors and it's a still a great way to review where your SEO is broken and needs improving.

This template gives you:

  • A master worksheet enabling you to compare the actual number of visits to your site for different keywords you are targeting against the total number of searches (impressions) measured as an exact match. Conditional formatting enables you to quickly review where there is an opportunity to improve position or clickthrough rate (share of search).seo-gap-analysis-keywords
  • A similar keyword comparison worksheet for any search which contains a phrase. We think this is the most useful feature of our template since you can quickly analyse head terms (such as product categories) or long-tail search terms (such as reviews or detailed product features) you are targeting.seo-gap-analysis-excel
  • An import worksheet which can be used to add the data from the Google Analytics Google Webmaster Tools data source.
  • A "Read me" page outlining how to use the spreadsheet.

SEOs who work regularly on keyword analysis will likely have their own Google Docs or spreadsheet for this, but we provide this spreadsheet to save members time and to explore data in more depth than possible in Google Analytics since it uses a fairly advanced VLOOKUP() functions to search for keywords across a list and a conditional wildcard SUMIF() function which occasional Excel users may not be familiar with.

Note that when interpreting and explaining this data you need to be aware of the limitations of the Google Webmaster Tools data explained in this post.

Available to
Expert members
Learn more

What our members say

Smart Insights has helped us take the right decision on a variety of occasions and we actively use its content to justify our marketing initiatives and spend.

Joao da Costa, Marketing Officer at Southbank International School

We are at the early stages of our digital marketing journey, Smart Insights templates and tutorials help us check and learn as we go.

Stuart Sykes, Digital Marketing Manager

Smart Insights is one of the best digital ‘tools’ on the internet today. It has been a great asset as a channel of advice and insight, helping me to deliver an all-encompassing digital strategy.

Jennifer Law, E-Communications Manager, National Pharmacy Association

Get access to this now by purchasing an Individual or Business Expert membership

Plus access 100+ resources in our Digital Marketing Library to Plan, Manage and Optimize your marketing:

  • In-depth Ebooks
  • Online training
  • How to videos
  • Marketing templates