Most keyword gap analysis tools cost $99/month or more, and you don’t need them. You can run a solid gap analysis using Google Search Console, Ahrefs’ free webmaster tools, and a spreadsheet - the same method I use before recommending anyone pay for software.

A keyword gap analysis compares the keywords your competitors rank for against your own rankings to find what you’re missing. The output is a list of opportunities: terms with real search volume where you have no page or a weak page, and your competitors already rank. That list becomes your next quarter’s content plan.

What you need before starting

Gather three things. First, your own Google Search Console data - you need at least three months of query data to work with. Second, pick two to three competitors. Not aspirational competitors - sites that actually overlap with your content and rank for similar terms. Third, open a blank Google Sheet.

For this walkthrough, I’ll use a real scenario. Say you run a B2B SaaS blog about project management. Your competitors are monday.com/blog, asana.com/resources, and clickup.com/blog.

Step one - export your own keyword data from GSC

Open Google Search Console, go to Performance, and set the date range to the last three months. Click “Export” and grab the Queries report as a CSV.

This gives you every keyword Google showed your site for, with impressions, clicks, CTR, and average position. Import it into your spreadsheet. Filter out branded terms (your company name, product name) - those aren’t useful for gap analysis. You should have somewhere between 500 and 5,000 rows depending on your site’s size.

Add a column and label it “Source: Own Site.” You’ll need this later when you merge datasets.

Step two - pull competitor keywords with Ahrefs free webmaster tools

Ahrefs offers a free webmaster tier that lets you see organic keywords for sites you verify ownership of. But here’s the workaround that most guides skip: you can also use their free Site Explorer to check any domain’s top keywords. The free version limits you to the top 100 results per query, but that’s enough.

Run each competitor domain through Site Explorer. Export the “Organic keywords” report for each. You’ll get keyword, position, volume, keyword difficulty, and traffic estimates. Drop each export into a new tab in your spreadsheet and add a “Source” column with the competitor’s name.

For the project management example, you’d pull keywords from monday.com/blog, asana.com/resources, and clickup.com/blog separately. That gives you three competitor keyword lists plus your own.

Step three - merge and deduplicate in the spreadsheet

Copy all four keyword lists into a single sheet. You now have one master list with columns: keyword, position, volume, source. Some keywords will appear multiple times - once from your site, once or more from competitors.

Create a pivot table (or use COUNTIF formulas) grouped by keyword. For each keyword, flag whether it appears in your data, Competitor A’s data, Competitor B’s data, and so on. The simplest approach:

  1. In a new sheet, get unique keywords using =UNIQUE() on the keyword column.
  2. Add columns for each source. Use =COUNTIF(SourceSheet!A:A, keyword) to mark presence with a one or zero.
  3. Add a “Gap” column. The formula: if your site’s count is zero and at least one competitor count is greater than zero, mark it “GAP.”

Filter the Gap column to show only gaps. Sort by search volume descending. That’s your keyword gap report.

Step four - prioritize the gaps

Not every gap is worth filling. A keyword your three competitors rank for with 5,000 monthly searches and a KD of 75 is a different proposition than one with 400 searches and KD 12. You want the second one.

Filter your gap list by:

  • Volume above 50. Below that isn’t worth a dedicated page.
  • KD below 30. You want gaps you can realistically close in the next three to six months.
  • Multiple competitors ranking. If two or three competitors rank for a term and you don’t, that’s stronger signal than a single competitor outlier.

In the project management example, you might find gaps like “project timeline template free” (volume 720, KD 18) and “resource allocation spreadsheet” (volume 390, KD 14). Those are the kind of terms where a single well-optimized article closes the gap.

For a deeper framework on turning these gaps into a prioritized plan, the keyword gap strategy guide covers scoring and sequencing in detail.

Step five - map gaps to content actions

Each gap falls into one of three buckets:

  • New page needed. You have nothing targeting this keyword. Write a new article or landing page.
  • Existing page needs optimization. You rank on page three or four - the page exists but isn’t competitive. Update the content, improve the targeting, add depth.
  • Existing page, wrong intent. You have a page but it doesn’t match the search intent. A product page ranking for an informational query, for example. You need a separate informational piece.

Go through your top 20 gaps and tag each one. That’s your short-term content roadmap. The full list of gaps feeds into your broader content gap analysis process.

The keyword gap analysis tool limitation nobody mentions

Free methods have a ceiling. Google Search Console only shows keywords where you got impressions - it can’t show you terms you’ve never appeared for. Ahrefs’ free tier caps exports. You’re working with incomplete data on both sides.

This matters less than you’d think. The gaps you find with this method are the highest-signal ones, because they’re terms where competitors actually rank and get traffic. The keywords missing from both your data and the free tool exports tend to be lower volume or highly competitive - not where you should focus anyway.

When your site outgrows this manual process - typically past 200 published pages or when you’re running gap analyses monthly - that’s when a paid keyword gap analysis tool or an automated keyword research workflow starts saving real time. Until then, the spreadsheet method gets you 80% of the value at zero cost.

Absolute Cluster’s keyword clustering features can take your gap keyword list and automatically group the opportunities into topic clusters with volume and difficulty scoring, so you go from raw gaps to a structured content plan in one step.