SEO gap analysis spreadsheet

Use this template to quickly analyse SEO effectiveness for the keywords you are targeting to improve your "share of search"

Search Engine Optimisation (SEO) Toolkit
This guide is part of a Toolkit with 6 other resources. Find out more?

SEO gap analysis spreadsheet

 Look Inside

Who is this guide for?

Our keywords template will let you review the keyword strategy for your business or your clients. It’s designed to be practical and actionable- It will help identify ‘quick wins’ to boost traffic, and help you build a stronger keyword strategy for consistently better SEO results.

It’s aimed at:

  • Marketing, Ecommerce and Digital marketing managers who need to understand and improve their keyword strategy and review the key issues to discuss with their in-house team, agencies or consultants.
  • Agencies and consultants who use it to review their clients current keyword positions and identify areas to improve.
  • Smaller businesses who use our guide as a “do-it-yourself” template to boost their business’ SEO and develop a more effective keyword strategy.

About the SEO gap analysis spreadsheet

  • Author: Dave Chaffey
  • Format: Excel (.xls) template

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.

What does our SEO gap analysis template include?

  • 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.

About the authors of our SEO gap analysis template

Dave ChaffeyDr Dave Chaffey is the editor of all Smart Insights resources. He has advised on SEO since he created his first site in 1997 when Altavista was the leading search engine and he switched to Google in ‘99. Ten years ago he ran the first SEO training courses for the Chartered Institute of Marketing and for several years was the tutor on the Econsultancy Advanced SEO course and also created the original Econsultancy Best Practice guide to SEO. Today his main SEO focus is improving organic search results for Smart Insights and our resources for subscribers.

Look Inside the SEO gap analysis spreadsheet

Warning: Beware of other resources that you can't apply to YOUR business

Like all Smart Insights' resources, our SEO gap analysis spreadsheet includes our "practical" promise to you: Unlike other sites—that just give you research—we only deal in practical, actionable, insight-driven resources that you can use to guarantee success on your projects TODAY.

Guaranteed to digitally transform your organisation

Actionable digital marketing advice, resources and training guaranteed to digitally transform your organisation.


I recently completed the RACE Digital Strategy Course. It was brilliant, and the 7-Steps guides are excellent resources, too. Thanks for providing such a fantastic collection.

Translated complex digital marketing channels into easy-to-understand concepts

Translates complex digital marketing channels into easy-to-understand concepts. Constantly updated with new insights that will help whether you own your own business or work for a large corporation.

Search Engine Optimisation (SEO) Toolkit

The SEO gap analysis spreadsheet is part of Smart Insights Search Engine Optimisation (SEO) Toolkit. With a Smart Insights membership you gain access to, not only this guide, but also the 6 other resources in the toolkit.

..but that’s not all. With an Expert Membership you also gain access to 20 other Toolkits containing 150+ other resources, all available to you. All toolkits are designed to be super- actionable, to help you:

  • Plan - Create a structured plan and roadmap by reviewing your current weaknesses and the opportunities to boost online leads and sales
  • Manage -  Review your marketing activities, so that you know where to focus to get the Quick Wins which will make the biggest difference
  • Optimise - Learn the best practices and success factors to help you compete to get better results

So why wait? Get instant access to this guide and see for yourself how easy it is to take your digital marketing—and your career—to the next level with our Toolkits

Learn more about the Search Engine Optimisation (SEO) Toolkit


Learn more about Expert Membership and get this (and other) toolkits

Instant access. Try it RISK-FREE