Get Set for Success in 2020

Improve personal, business and team performance through marketing training, with discounts on all Annual Memberships

FIND OUT MORE Rocket illustration

Offer ends in: Days Hours Mins Secs

Guides and Templates

SEO gap analysis Excel spreadsheet

Identify new opportunities to attract new visitors through organic search

How will the gap analysis template help me and my business?

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 actual 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 increase visits.

When consulting with businesses we have found that many businesses have a poor target keyword list which is either not detailed enough or poorly structured. This spreadsheet will show you how to create a comprehensive target keyword report and review performance to identify new SEO and content marketing strategies. We use it ourselves as part of our SEO strategy and it helps us deliver over 80% of the Smart Insights 1/2 million uniques per month through organic search.

What does the 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
  • Import worksheets which can be used to quickly import data from the Google Analytics, KeyWord Planner and Google Search Console data sources.
  • A "Read me" page outlining how to use the spreadsheet.
  • 3 examples of how to create a target keyword list structured around consumer search behaviour

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 Excel VLOOKUP() functions to search for keywords across a list and a conditional wildcard SUMIF() function which occasional Excel users may not be familiar with. So if you don't know these techniques, you will learn about these too.

Who is this template for?

We’ve used this gap analysis approach on many search marketing consultancy projects in financial services, retail and travel sectors and it’s a great way to review where your SEO is poor and how to tap into new opportunities.

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.

Latest updates

To ensure our example accurate, we have updated our screenshots according to Google AdWords being rebranded to Google Ads, as well as recent layout changes and new statistical categories for other Google products (such as low and high top of page bids).

Resource Details

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

About the author

Dr. Dave Chaffey

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 when it was launched. 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.

Individual memebrship badge icon Individual
Business memebrship badge icon Business

Access this resource now

Individual and Business members get full access to this and 7 other guides in the Search engine optimisation (SEO) Toolkit.

Learn more about membership Already an Individual or Business member?
Login here
Guide cover
Look inside content

Why wait? Start Improving your marketing today

Join as an Individual or Business member to access this toolkit and up to 19 more.

Splitter graphic
Illustration for Individual Membership

Individual Membership

  • Enhance your marketing skills
  • Keep up to date with all the latest techniques
  • Become more effective in your role and develop your career
Illustration for Business Membership

Business Membership

  • Improve your (and your clients') results from marketing
  • Upskill and develop your marketing team
  • Get dedicated support on your journey

Trusted by marketers all over the world

Since 2009 our toolkits have helped thousands of marketers in over 170 countries to hit - and exceed - their targets.
Will you be next?

Globe illustration
Smart Insight partner logos over 165,000 consultants, agencies, entrepreneurs and SMB's in over 170 countries.

Get better results

Make your job easier

Tenfold improvement

Learn more about Individual or Business membership options
Smart insights logo