Keyword Gap Analysis Template
Keyword Gap Analysis Template
Find the queries your competitors rank for that you don't, and size each gap in potential clicks.
Which competitor-ranked queries you have no coverage for, how big each opportunity is, and which ones need a new page.
- Excel workbook (.xlsx), opens in Google Sheets
- PDF field guide, 9 to 11 pages
- Blank and worked CSV templates
- DataForSEO import script
Free, no email required. Version 3.0, updated November 2026.
=IF(I13="","",IF(I13<>"Ready","n.a.",IF(AND(C13=0,J13>0),"Missing coverage",IF(J13=0,"Check demand","Existing coverage"))))The formula above is the real one from cell K13. Scroll it sideways to read the whole rule.
Connected to the DataForSEO API
This template is designed around one DataForSEO endpoint. Pull the data, convert it with one command, and the workbook does the rest.
DataForSEO Labs: Ranked KeywordsEvery keyword a domain ranks for, with its ranking URL, position and volumedataforseo_labs/google/ranked_keywords/live
Save the JSON responseThe full API response, kept as your source of truth.response.json
Run the import scriptOffline. No API calls, no credentials.dataforseo_import.py import --template 04
Paste at A13Dashboard, checks and action list update instantly.Keyword Gap Analysis Template.xlsx
Column map for this template:Filled by the APIAudit trail (automatic)You add
What the keyword gap analysis template decides
A keyword gap analysis answers a simple question: what are my competitors getting found for that I'm not? The answer is usually a short list of high-intent topics you never built a page for, hiding inside a long list of noise.
This keyword gap analysis template takes each query with your rank and up to two competitors' ranks, then labels it Missing coverage (competitors rank, you don't), Existing coverage (you rank somewhere) or Check demand (nobody in your set ranks, so validate the opportunity first). Each gap is sized as potential monthly clicks at a target CTR, and the Dashboard ranks the biggest gaps.
Who it's for
- SEOs planning new content from competitor research
- Ecommerce teams finding missing category and product pages
- Agencies building a pitch around missed opportunities
- Teams entering a new market or product line
Where it fits in your workflow
The keyword gap analysis template answers one question. These three templates answer the questions around it:
- Keyword Prioritization Template: which keywords deserve your next hours of work, ranked by the monthly profit each could add per hour invested, with every assumption visible and editable.
- Keyword Mapping Template: which single URL should own each keyword, and where your map assigns the same query to two different pages.
- SERP Overlap Keyword Clustering Template: whether two queries should be targeted by one page or separate pages, based on how many ranking URLs their SERPs share.
What's inside the download
One ZIP with the workbook, an 8-column input template (blank and filled), the PDF field guide and an offline script that converts saved DataForSEO responses into the input CSV. The workbook has five tabs.
How the keyword gap analysis calculation works
Competitors found counts how many of the two competitors rank (rank > 0). Gap type is Missing coverage when you don't rank and at least one competitor does, Check demand when neither competitor ranks, and Existing coverage otherwise. Potential clicks = search volume × target CTR.
The key formula, exactly as it appears in the workbook
=IF(I13="","",IF(I13<>"Ready","n.a.",IF(AND(C13=0,J13>0),"Missing coverage",IF(J13=0,"Check demand","Existing coverage"))))
| Result column | Plain-English formula | What it tells you |
|---|---|---|
| Competitors found | IF(Competitor A rank>0,1,0)+IF(Competitor B rank>0,1,0) | Count of the two named competitors found. |
| Gap type | IF(AND(Own organic rank=0,Competitors found>0),"Missing coverage",IF(Competitors found=0,"Check demand","Existing coverage")) | Classifies observed coverage, not content quality. |
| Potential clicks / month | Monthly searches × Target CTR | Gross opportunity at the assumed CTR. It is not an incremental forecast. |
The results you will see
Missing coverageCheck demandExisting coverage
Green meets the checks, red needs fixing first, amber needs a human decision and blue marks an opportunity. Rows with missing or out-of-range inputs are held back in the Input check column until they're fixed, so a half-filled row never sneaks into a total.
How to use the keyword gap analysis template
- Pick two real competitorsChoose the sites that actually rank for your money keywords, not your business competitors by name.
- Export the intersectionPull 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.
- Set a target CTR and effortEnter the CTR you'd expect at the position you can realistically reach and the hours to build or improve the page.
- Work the Missing coverage listSort by potential clicks. Map each Missing coverage keyword to a planned URL in the keyword mapping template.
The worked example in the Workspace tab. Clear the lime input cells, paste your CSV at A13 (values only) and every result recalculates.
Fill the keyword gap analysis template with DataForSEO
Every template in this library is built to run on DataForSEO data. This one uses DataForSEO Labs: Ranked Keywords: every keyword a domain ranks for, with its ranking URL, position and volume.
Most of the data columns fill automatically. Add the business inputs listed below, then paste.
- Request the dataSend this request with your DataForSEO API login (HTTP Basic auth).
POST https://api.dataforseo.com/v3/dataforseo_labs/google/ranked_keywords/live [ { "target": "yourdomain.com", "location_code": 2840, "language_code": "en", "limit": 1000 } ]
- Save the full JSON responseSave the complete response, not just the task ID, next to the template folder.
- Convert it with the included scriptIt runs offline, makes no API calls and never sees your credentials.
python3 Automation/dataforseo_import.py import --template 04 --input response.json --output my-inputs.csv
- Add what the API can't knowCompetitor A rank, Competitor B rank, Target CTR, Work hours
- Paste at A13Clear the demo rows, paste the CSV values at Workspace cell A13 and the dashboard updates.
| Fills automatically from DataForSEO | You add |
|---|---|
| Keyword, Monthly searches, Own organic rank | Competitor A rank, Competitor B rank, Target CTR, Work hours |
Source reference fills automatically with the task ID, endpoint, location, language, device and snapshot time, so every row is traceable. Before a big pull, estimate the cost with the DataForSEO pricing calculator. The same steps, plus an example request file, are in the download's Automation folder.
Worked example: Northbeam Outdoor
Every template ships filled with data from Northbeam Outdoor, a fictional outdoor-gear retailer that appears across all 40 workbooks, so you can see what useful output looks like before you add your own. This workbook has 20 example rows.
Nine of the 20 example keywords are true gaps, worth about 13,600 potential clicks a month combined. One more is flagged Check demand because neither competitor ranks for it either.
Top 10 by potential clicks / month
| # | Keyword | Potential clicks / month |
|---|---|---|
| 1 | mens down jacket | 3,258.0 |
| 2 | trail running shoes | 3,240.0 |
| 3 | waterproof hiking boots | 2,220.0 |
| 4 | trekking poles | 2,178.0 |
| 5 | rain jacket womens | 2,168.0 |
Input fields
Keep the columns in this order when you prepare a CSV. A missing observation stays blank; use 0 only for a measured zero or a verified No. Fields marked DataForSEO can be filled automatically by the included script.
| CSV field | Column | Meaning and rule | Source |
|---|---|---|---|
| keyword | Keyword | Exact query in the selected country and language. | DataForSEO: keyword |
| search_volume | Monthly searches | Provider monthly search-volume estimate. Use one market, language and source date. | DataForSEO: search_volume |
| own_rank | Own organic rank | Organic rank in the selected depth. Use 0 only for confirmed not found. | DataForSEO: rank |
| competitor_a_rank | Competitor A rank | Best organic rank for named competitor A. Zero means not found. | Your records |
| competitor_b_rank | Competitor B rank | Best organic rank for named competitor B. Zero means not found. | Your records |
| target_ctr | Target CTR | Assumed organic click-through rate as a decimal. Calibrate by device, brand and SERP layout. Decimal: 0.08 = 8%. | Your records |
| effort_hours | Work hours | Estimated implementation hours, greater than zero. | Your records |
| source_ref | Source reference | Task ID, export filename and observation scope. Retain the untouched source response separately. Optional. | DataForSEO: source_ref |
Reading the result honestly
Existing coverage may need an update instead of another URL. Gross potential does not deduct current traffic.
Limits. The starter importer fills one domain at a time. Peer joins and absence confirmation require a deliberate merge.
Keyword gap analysis mistakes to avoid
Tips from practice
- DataForSEO Labs' domain intersection endpoint returns exactly this table. The included automation script converts the JSON to the input CSV.
- Add a third competitor by copying the workbook and running it twice; keep the comparison set stable over time.
- Re-run quarterly to catch new gaps as competitors publish.
Frequently asked questions
What is keyword gap analysis?
It's comparing the keywords your competitors rank for with the ones you rank for, to find topics where they're visible and you're not.
How do I find competitor keywords for free?
Search Console shows your own queries, and you can check competitor rankings manually for a short list. For scale, use a keyword tool or the DataForSEO API and paste the export into this template.
What's the difference between a keyword gap and a content gap?
A keyword gap is a query you don't rank for. A content gap is the missing page or section behind it. This template finds the first so you can plan the second.
Why does it say Check demand?
Neither competitor ranks in the tracked depth, so the query may have low commercial value or an intent nobody serves well. Validate before you build.
Use it with these templates
Each template answers one decision. These are the ones that feed into, or follow from, the keyword gap analysis template.
01Keyword Prioritization TemplateLabs: Keyword Overview
Rank every keyword by the profit it could add per hour of work, not by search volume alone.
02Keyword Mapping TemplateLabs: Ranked Keywords
Assign every keyword to exactly one URL and catch conflicting assignments before they become cannibalization.
03SERP Overlap Keyword Clustering TemplateSERP API: Google Organic
Decide whether two keywords belong on one page or two by measuring how much their Google results overlap.
31SEO Forecast TemplateLabs: Keyword Overview
Project added clicks and contribution per keyword cluster with CTR, attainment and seasonality you can defend.
27AI Share of Voice CalculatorAI Optimization: LLM Mentions
Compare citation rate and share across competitor domains inside one fixed set of AI answers.
36Ecommerce SEO Prioritization TemplateLabs: Keyword Overview
Rank product pages by potential orders, unit contribution and profit per hour, and catch out-of-stock or noindex SKUs.
Part of the Keyword Research Toolkit
Get all 6 Keyword Research templates in one ZIP, or the complete set of 40 SEO and GEO templates with the 401-page field handbook.
Further reading on Optimization Theory
Built by Jourdan Rombough, technical SEO and GEO consultant with 15+ years in search. About Jourdan or get an SEO plan.






