Keyword Gap Analysis Template

Keyword Research Toolkit, template 4 of 6

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.

Keyword Gap Analysis Template.xlsx
K13fx=IF(I13="","",IF(I13<>"Ready","n.a.",IF(AND(C13=0,J13>0),"Missing coverage",IF(J13=0,"Check demand","Existing coverage"))))

Start HereDashboardWorkspaceDelivery QAField Guide

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.

Setup steps

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

KeywordMonthly searchesOwn organic rankCompetitor A rankCompetitor B rankTarget CTRWork hoursSource reference
4 of 8 input columns come straight from DataForSEO

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.

Workspace. The working table. 8 input columns, 4 calculated columns, room for 1,000 rows. Dropdowns, Yes/No fields and range checks catch bad inputs as you paste.
Start Here. What it decides, a four-step setup and the colour key.
Dashboard. KPI cards, charts, a ranked Top 10 and an automatic action list.
Delivery QA. Pass, fail or exclude each validation step with evidence, owner and date.
Field Guide. Every field explained, plus each formula in plain English.

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

  1. Pick two real competitorsChoose the sites that actually rank for your money keywords, not your business competitors by name.
  2. 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.
  3. 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.
  4. 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.

  1. 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 } ]
  2. Save the full JSON responseSave the complete response, not just the task ID, next to the template folder.
  3. 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
  4. Add what the API can't knowCompetitor A rank, Competitor B rank, Target CTR, Work hours
  5. 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.

20Records
0Rows with input issues
9Missing-coverage queries
13,628Potential clicks in gaps

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

Choosing competitors by brand, not by SERPYour biggest business rival might not rank for anything you care about. Pick domains that share your SERPs.
Ignoring Existing coverageKeywords where you rank 18th aren't gaps, but they're often the cheapest wins. Feed them into the prioritization template.
Building a page for every gapCluster gap keywords first. Ten gaps often collapse into three pages.

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.

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.

Download the Keyword Research Toolkit

See the complete SEO & GEO toolkit

Jourdan Rombough

Built by Jourdan Rombough, technical SEO and GEO consultant with 15+ years in search. About Jourdan or get an SEO plan.

More on digital marketing in our blog

See all posts