Free Template
Competitor Analysis Spreadsheet.
A side-by-side competitor analysis across 6 SEO dimensions — content, keywords, schema, backlinks, AI mentions, technical. The spreadsheet we use to find gaps before each quarterly content cycle.
6 dimensions · 4-column matrix · Sheets
01 · The 6 dimensions
What you compare. And what you don't.
Most competitor analyses are 30 dimensions deep and used by no one. The 6 below are the ones that map to a roadmap action — every gap exposed here lands as a row in next quarter's content calendar or as a brief in the technical backlog. Anything else is decoration.
Content gap
Topics they cover that you don't. Captured at the cluster level (not individual articles) so the gap drives a roadmap, not a one-off post. Score: clusters covered / clusters relevant.
Keyword gap
KWs they rank for that you don't. Filtered by intent and difficulty so the gap is actionable — losing on KWs you can't realistically rank for is informational, not a priority.
Schema gap
Structured-data types they ship that you don't. Article, Product, FAQPage, HowTo, Course, etc. AI engines parse schema — gaps here directly translate into citation losses.
Backlink gap
Referring domains they have that you don't, weighted by Domain Rating + topical relevance. Not 'they have more links' — specifically which authoritative domains link to them and not to you.
AI mention gap
Citations across ChatGPT, Gemini, Claude, Perplexity and AI Overviews. Per-engine, per-query. The fastest-growing gap dimension — and the one most teams don't measure yet.
Technical gap
Core Web Vitals, hreflang, indexation rate, crawl efficiency. Catches the structural debt that no amount of content will out-run.
02 · The matrix
Side by side. One screen.
Drop your domain into column 2, your top three competitors into columns 3–5. For each dimension, mark Yes (parity), Partial (some coverage), or No (gap). Anywhere you have No vs a competitor's Yes is a roadmap input.
| Dimension | You | Competitor A | Competitor B | Competitor C |
|---|---|---|---|---|
| Content cluster: Category landscape | ||||
| Content cluster: Buyer guides | ||||
| Content cluster: Use cases × persona | ||||
| Keyword: Top 100 commercial KWs | ||||
| Keyword: Long-tail (1k+ tracked) | ||||
| Schema: Article + FAQPage + Breadcrumb | ||||
| Schema: Product + Offer + Review | ||||
| Backlinks: DR 70+ referring domains | ||||
| AI mentions: ChatGPT (top 50 queries) | ||||
| AI mentions: Gemini + AI Overviews | ||||
| AI mentions: Perplexity + Claude | ||||
| Technical: Core Web Vitals (all green) |
Six high-priority gaps emerge from this matrix (You: No / Competitor A: Yes). Those six become the inputs to next quarter's content calendar.
Critical mistakes
The 4 ways competitor analysis actually fails.
Comparing on dimensions that don't lead to action
If a gap doesn't translate into a calendar entry or a technical ticket, the dimension is decoration. Cut it. The 6 above are kept because each one ladders directly to a roadmap input.
Wrong competitors
The competitors that win in your sales deck aren't always the ones that win in the SERP. SERP competitors are whoever ranks for your top 50 KWs — which often includes review sites and aggregators that aren't on sales' radar.
Snapshot once, never again
Competitive position changes monthly. Re-run this matrix every quarter so the roadmap inputs stay current. The gaps from Q1 are not the gaps from Q3.
Ignoring AI mentions
Most teams still skip the AI mention dimension because it's harder to measure. That's exactly why it's the gap with the most leverage — your competitors haven't caught up yet either, and being first on the AI surface compounds.
FAQs
How many competitors should I include?▾
Three is the sweet spot. Fewer than three and the matrix lacks depth; more than five and the visual collapses into noise. Pick 1 incumbent, 1 emerging challenger, and 1 wildcard (review site, aggregator, or adjacent category).
How do I score AI mentions across the 5 engines?▾
Pick a representative query set — 30 to 100 queries that span your category. Manually run each query in ChatGPT, Gemini, Claude, Perplexity, and check Google for AI Overview presence. Mark whether you're cited, whether each competitor is cited. The Track AI Traffic in GA4 template gives you the click-side; this matrix gives you the citation-side.
What if a gap is real but I can't realistically close it?▾
Mark it but don't roadmap it. Some gaps are real and structural — a competitor with 10x your domain authority will out-rank you on head terms regardless of effort. The matrix tells you the truth; what you do with it depends on resource reality.
How does this connect to the content calendar?▾
Every gap where You = No and a competitor = Yes becomes a candidate for next quarter's calendar. Filter by intent and funnel stage to prioritise. The 12-week content calendar is downstream of this matrix; the matrix runs once a quarter, the calendar runs weekly.
Should I share this matrix with the client / leadership?▾
Yes — share the dimensions and the gap summary. Don't share the raw matrix unless leadership wants the granular view. The headline 'we have 6 priority gaps and here's the plan to close them' is more useful than 12 rows of yes/no/partial.
Auto-track the AI gap
Stop checking AI engines manually each quarter.
The AI mention dimension takes the longest to fill manually — running 30–100 queries across 5 engines and recording who's cited. SEOTopSecret's AI Visibility Tracker does this continuously, per-engine and per-query, with weekly deltas. The matrix dimension auto-fills.
See AI Visibility TrackingYour Growth Operating System Starts Here
Connect GSC + GA4. See your data in the OS. Your first content brief, technical audit, and rank baseline — ready within hours.