Most people who download a content gap analysis template end up with a spreadsheet of 4,000 keywords their competitors rank for and no idea which row to act on. The template is not the hard part. The hard part is the filtering logic you apply after the export lands, and the rules that decide whether a gap becomes a new page, a rewrite, or a deliberate pass.
Below is the structure I use, the columns that earn their space, and the thresholds that turn a raw list into a publishing queue you can hand to a writer on Monday.
What a gap analysis actually measures
You are comparing your keyword footprint against a set of rivals and isolating three states: terms they rank for and you do not, terms you both rank for where they beat you, and terms where you lead and should defend. Those are different problems with different fixes, and a template that lumps them together produces bad decisions.
Pick three to five competitors, no more. Beyond five, the intersection logic gets noisy and you start importing keywords from businesses that only overlap with you on one product line. Choose rivals who sell roughly what you sell to roughly who you sell to. A publisher that outranks you on informational queries is not your competitor if they monetize with ads and you monetize with demos.
The columns that matter
Strip your sheet to these and nothing else. Every extra column is a reason to argue instead of publish.
- Keyword — the query itself.
- Monthly volume — a modeled estimate, usually a rolling twelve-month average. Treat it as an order of magnitude, not a forecast.
- Keyword difficulty — third-party score, useful for sorting, useless as an absolute.
- CPC — the cheapest available proxy for commercial intent. Advertisers do not pay $18 a click for queries that never convert.
- Your position — blank if you have no page.
- Competitor A/B/C position — one column each, so you can see whether a gap is universal or one rival’s fluke.
- Ranking URL (theirs) — the single most useful column. It tells you what format wins.
- Intent — informational, commercial, transactional, navigational.
- Action — new page, expand existing, merge, ignore.
- Target URL (yours) — filled in only after you decide.
Notice what is missing: search volume trend, SERP feature flags, traffic value. Useful in a deep dive, clutter in a first pass. Add them later if the queue stalls.
Filtering rules that cut 4,000 rows to 60
Run these in order. Each one is a hard cut, applied before you look at any individual keyword.
- Drop rows where fewer than two competitors rank. A single rival ranking for a term is often an accident of an old page or a branded association. Two or more means the query genuinely belongs to your category.
- Drop branded queries. You will not rank for “competitor name pricing” and chasing it wastes a writer’s week. The exception is comparison content, which is a separate deliberate project.
- Drop anything under about 30 monthly searches unless CPC is high or the term is a clear buying phrase. Long-tail is good; near-zero-volume is a distraction until you have exhausted the middle.
- Sort by CPC descending and read the top 100 manually. This is the only manual step and it takes twenty minutes. You are looking for phrases with a decision embedded in them: pricing, cost, alternative, for [industry], near me, vs, best.
- Check the weakest page-one result, not the median. Open the SERP for your ten strongest candidates. If position nine is a 700-word page on a domain with forty referring domains, that keyword is available regardless of what the difficulty score says.
Turning gaps into actions, not just a backlog
Every surviving row gets one of four actions, and the rule for each is mechanical.
New page when you have no ranking URL and the intent does not match anything you have published. Expand existing when you rank 11–30 and your page covers the topic but thinly — this is the highest-return action in the whole exercise, because moving 14 to 6 usually costs a day of editing rather than a month of new content. Merge when two of your own URLs both rank weakly for variants of the same query; consolidate and redirect. Ignore when intent is wrong for your business, when a SERP is locked by marketplaces or Wikipedia, or when the query needs a product you do not sell.
Cluster before you assign. Twenty keywords that all mean the same thing become one page, not twenty. Sort your filtered list alphabetically and by head term, then group by the ranking URL your competitors use — if all their positions come from one article, the search engine has already told you that one article covers the cluster.
How often to rerun it
Quarterly is right for most sites. Monthly if you publish more than eight pieces a month, because your own footprint changes fast enough to invalidate the sheet. Annually is too slow; competitor content strategies shift within a season.
Keep the old sheets. The genuinely valuable signal shows up in the diff between quarters: keywords that appeared in Q1’s gap list and are still unclaimed in Q3 either got deprioritized for a good reason or fell through a process crack. Both are worth knowing.
Where a spreadsheet stops being enough
Manual gap analysis breaks down at around three competitors and 2,000 keywords. The exports come from different tools with different date stamps, the VLOOKUPs drift, and by the time you finish the sheet the positions have moved. Nobody wants to do it twice.
Tooling helps here specifically because it collapses the join. SEO Rocket runs a content gap across up to five competitors with per-rival position columns already aligned, alongside volume, difficulty, CPC, and the ranking URL — the same fields the template calls for, without the merge step. Filtered keywords save to a project pool that feeds the writer and the rank tracker, so a gap you decide to close becomes a tracked position rather than a row you forget. It runs on industry-grade search data at a flat $50 a month, which is the part that matters if you were previously paying per seat to export CSVs.
The caveats worth stating out loud
Third-party volume and difficulty numbers are estimates built from modeled averages and periodic crawls. They are directionally reliable and individually wrong. When your Search Console data disagrees with a tool’s volume figure for a query you already rank for, Search Console wins — it is measuring your actual impressions rather than modeling a market.
Position columns are snapshots too. Daily movement of two or three places is normal noise, not a signal, so do not rewrite a page because a competitor jumped from 7 to 5 last Tuesday. And a gap closed is not a ranking earned: publishing against a competitor’s winning URL gets you into the race, not across the line. Links, internal structure, and genuine depth still decide the outcome.
Build the sheet, run the five filters, assign the four actions, and ship the “expand existing” rows first. That single sequencing choice usually produces results a month before the new-page queue does.
