Keyword Gap Analysis Template (Optimization Theory, v3.0, November 2026)
===================================================================

Find the queries your competitors rank for that you don't, and size each gap in potential clicks.

What it decides
  Which competitor-ranked queries you have no coverage for, how big each opportunity is, and which ones need a new page.

Files
  Keyword Gap Analysis Template.xlsx ............ the workbook (Start Here, Dashboard, Workspace, Delivery QA, Field Guide)
  Keyword Gap Analysis Template - Field Guide.pdf  the illustrated guide
  Input-Template-blank.csv ..... column headers in paste order
  Input-Template-example.csv ... the worked example (Northbeam Outdoor, fictional data)
  HOW-TO-USE-WITH-DATAFORSEO.txt step-by-step: request, save, convert, complete, paste
  Automation/ .................. import script, example request, field mapping (python3 Automation/dataforseo_import.py --help)

Built to run on DataForSEO
  Source: DataForSEO Labs: Ranked Keywords
  Fills automatically: Keyword, Monthly searches, Own organic rank
  Full steps: HOW-TO-USE-WITH-DATAFORSEO.txt (also in the Automation folder), with example-request.json

Quick start
  1. Pick two real competitors: Choose the sites that actually rank for your money keywords, not your business competitors by name.
  2. Export the intersection: Pull keywords where at least one competitor ranks, with search volume, your rank and theirs. Use 0 when a site doesn't rank in the tracked depth.
  3. Set a target CTR and effort: Enter the CTR you'd expect at the position you can realistically reach and the hours to build or improve the page.
  4. Work the Missing coverage list: Sort by potential clicks. Map each Missing coverage keyword to a planned URL in the keyword mapping template.

Works in Microsoft Excel, Google Sheets (upload, then open with Sheets) and LibreOffice Calc.
Paste values only at Workspace cell A13. Lime cells are inputs; blue cells are formulas.

Full tutorial: https://optimizationtheory.com/download-view/keyword-gap-analysis-template/
Part of the Keyword Research Toolkit: https://optimizationtheory.com/download-view/keyword-research-toolkit-6-seo-planning-workbooks/
All 40 templates: https://optimizationtheory.com/download-view/free-seo-geo-toolkit-40-spreadsheets-and-field-guides/

Free to use in your own and client work. Please don't resell the templates themselves.
Built by Jourdan Rombough, optimizationtheory.com
