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

Use our gap analysis spreadsheet to identify opportunities to attract new high quality visitors searching on high intent keywords online. It compares the effectiveness of organic search compared to paid in driving visitors to your site.

When consulting with businesses, we have found that many 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 your performance to help you identify new SEO and content marketing strategies. At Smart Insights, we use this spreadsheet as part of our SEO strategy and it helps us deliver over 80% of the half a million unique visits we get per month to our website, generated through organic search.

What is gap analysis?

Where there's a high demand for products or searches (as indicated by the number of searches/search terms used), but you have a relatively low share of visits to your site from natural search, you can identify this as a 'gap' that you can work to 'fill' through SEO optimization. To do this, you'll need to understand and review the best SEO techniques to increase visits. For support with this, we have a dedicated SEO Learning Path which teaches you how to use a data-driven approach to improving your SEO strategies.

What does the SEO gap analysis template include?

• A master worksheet enabling you to compare the actual number of visits to your site for your various target keywords 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).
• 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 analyze head terms (such as product categories) or long-tail search terms (such as reviews or detailed product features) you are targeting.
• 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.
• Detailed guidance throughout the spreadsheet to help you get the most from your data.
• Three examples of how to create a target keyword list structured around consumer search behavior.

SEOs who work regularly on keyword analysis will likely have their own Google Docs or spreadsheet for this. We created this spreadsheet to save members' time and to help you explore data in greater depth than is possible in Google Analytics since it uses 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. If you are unfamiliar with these techniques, we include information on them in our SEO gap analysis spreadsheet.

Who is this template for?

We’ve used this SEO gap analysis approach on many search marketing consultancy projects in financial services, retail, and travel sectors. It’s a great way to review areas where your SEO performance is poor and see how you can 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.

This template is aimed at:

• Marketing, e-commerce 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 client’s 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.

Resource Details

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

About the author

Dr. Dave Chaffey

Dave is co-founder and content director of Smart Insights. He is editor of the 100+ templates, ebooks and courses in the digital marketing resource library created by our team of 25+ Digital Marketing experts. Our resources used by our premium members in more than 80 countries to Map, Plan and Manage their digital marketing.

For my full profile, or to connect on LinkedIn or other social networks, see the About Dave Chaffey profile page on Smart Insights. Dave is author of 5 bestselling books on digital marketing including Digital Marketing Excellence and Digital Marketing: Strategy, Implementation and Practice. In 2004 he was recognised by the Chartered Institute of Marketing as one of 50 marketing ‘gurus’ worldwide who have helped shape the future of marketing.