Competitor Analysis Template for SEO (With Defensibility Scoring)
By Ben, Founder. Several years running SEO for clients and my own businesses. I designed Andy’s brief workflow using Backlinko’s 7-step program and Reforge’s strategic framework, which is where the defensibility logic in this template comes from.
A competitor analysis template for SEO is a structured worksheet that maps each competitor’s keyword positions, content format, and domain authority to a defensibility verdict: can you realistically outrank them for this keyword, or is that position locked? The best templates skip the generic feature-vs-feature grid and connect every row directly to a content brief decision. Fill in competitor, target keyword, search intent, estimated difficulty, content format, word count, and a defensibility classification (transactional, defensible-informational, non-defensible, or commodity). That last column is the one that tells you whether to write the article.
You manage 3 to 10 client accounts. Competitor analysis eats 3 to 4 hours per project, and the output is a slide deck nobody acts on. The template below fixes that. It produces brief inputs, not decoration, and it tells you which competitor positions are worth attacking. Once a row is filled, it should hand you a clear instruction for how to write a content brief that actually guides a writer.
Why generic competitor templates don’t work for SEO decisions
Pull any free template from HubSpot, Notion, or Atlassian. They track the same things: features, pricing tiers, target market, social following. Useful for a product positioning deck. Useless for deciding what to publish next week.
Here is the gap. Those grids tell you who ranks. They never tell you whether you can displace them. A keyword sitting in position one behind a DA 88 domain with 4,000 backlinks is a different fight than a thin 600-word post from a DA 30 blog. The feature grid treats both as “a competitor.” Your SEO decision treats them as opposite calls.
A competitor analysis template that doesn’t classify keyword defensibility tells you who your competitors are, not whether you can beat them. That is the whole problem in one line.
At Andy we refuse to present keyword lists without strategic rationale for why each keyword is worth targeting. The same principle applies here. Analysis without a brief implication is just research. Research is not strategy. If a row in your sheet does not end with “write this” or “skip this,” the row is wasted work.
The 6-column SEO competitor analysis template (and what each field does)
Every column earns its place by feeding a content brief or a keyword decision. No vanity fields. Here is the structure, one row per keyword, not one row per competitor.
Column 1: Competitor URL. The actual page ranking for your target keyword. Not your client’s product rival. The page sitting in the top 5 of the SERP is your competitor for that term, even if it belongs to a media site that sells nothing.
Column 2: Target keyword and search intent. Classify the intent: informational, transactional, or navigational. Everything starts by the search intent. You cannot change what people are typing, so you read what they typed and match it.
Column 3: Keyword difficulty and estimated monthly volume. Pull live numbers. Andy fetches SERP data in real time for every keyword run (volume, difficulty, intent), and you want the same freshness here. Stale KD scores get you killed on competitive terms.
Column 4: Content format and word count. Listicle, how-to, guide, or landing page, plus the live word count. This is where running a competitive content analysis pays off, because format and depth tell you the bar you have to clear.
Column 5: Domain authority or topical authority signal. Backlink count is a rough proxy. Topical depth is the better read. A site that has published 40 connected articles on one subject signals to Google and to LLMs that it is an expert, and that is harder to beat than a high DA with one orphan post.
Column 6: Defensibility verdict. Transactional, defensible-informational, non-defensible, or commodity. This is Reforge’s four-bucket classification, and it is the column that decides everything. I built this logic from years of client briefs, sorting keywords into “go” and “don’t bother” piles. More on each bucket below.
How to fill in the template row-by-row (step-by-step)
Work one keyword at a time. The process is mechanical once you have done it twice.
Step 1: Run the SERP and grab the top 5 URLs. Search your target keyword in an incognito window. Copy the top 5 organic results. Ignore your client’s named brand rivals unless they actually rank. The ranking page is the competitor, full stop.
Step 2: Pull the primary keyword, format, and word count from each live page. Open each URL. Note what the page is built to rank for, whether it is a listicle or a guide, and roughly how long it runs. This takes two minutes per page once you have a rhythm.
Step 3: Classify the intent. Read the title and the structure. A “best X tools” post is transactional. A “what is X” post is informational. Get this right, because the brief depends on it. Here is the deeper method for mapping each competitor keyword to search intent.
Step 4: Assign the defensibility bucket. This is the judgment call, calibrated from real client brief data:
- Transactional. Bottom-of-funnel, comparison, or buying-intent terms. Almost always worth a brief, because intent converts and incumbents are often weak on these.
- Defensible-informational. Informational keywords where you can add original data or first-party experience the incumbent lacks. These are the only informational pieces worth investing in.
- Non-defensible. Plain “what is X” explainers already owned by high-DA incumbents and absorbed by AI Overviews. Skip these. If you do not have a strong opinion, your content is going to be replaced by AI.
- Commodity. Generic content anyone can produce, with no angle and no data. No defensibility, no point.
Score the bucket using the KD, the DA gap, and the content depth together. A low KD plus a thin incumbent plus a topic where your client has real data equals defensible. A high KD plus a deep incumbent plus a generic angle equals non-defensible.
Step 5: Write the brief implication. Every row ends with an instruction. Either “target this keyword with [format] at [word count]” or “skip, non-defensible.” This is the line that decides why this keyword is good or why this keyword is not good. No row leaves the sheet without it.
How to connect the completed template to a content brief
The point of the template is the handoff. A filled sheet should sort itself into a list of articles that you want to write and a list of articles that you do not want to write.
Each defensible-informational row becomes a brief candidate. Take the keyword, the target format, the word count you need to beat, and the angle, then build the full brief. The defensibility verdict already told you the article is worth the spend.
Non-defensible rows do not go in the brief queue. They go in a monitor-only list. You watch them in case the SERP softens, but you do not commission writing against a position Google has already handed to an incumbent.
Transactional rows get brief treatment aimed at bottom-of-funnel intent: comparison pages, alternatives pages, and buying guides. These convert, and they think in clusters and content pillars rather than one-off posts.
The manual version of this works. It also takes hours per client. Andy automates this competitive analysis step inside the brief workflow: the live SERP fetch feeds straight into brief generation, so the spreadsheet step disappears and every competitor row arrives already scored and already attached to a brief. This is not just a tool. This is really an app that handles not only the execution but also the strategy behind which keywords deserve a brief in the first place.
FAQ
How do you write a competitor analysis for SEO?
Map competitors by keyword position in the SERP, not by product similarity. For each target keyword, pull the top ranking pages, classify the search intent, then assign a defensibility bucket before you decide to target it. The defensibility call, not the feature comparison, is what turns the analysis into a publishing decision.
What are the 5 steps of a competitive analysis?
Identify your competitors by who ranks in the SERP (not by brand awareness), pull their ranking keywords, classify each keyword’s defensibility, assess the content format and depth you need to beat, then connect every finding to a content brief. Steps that do not end in a brief decision are research, not analysis.
How do I do competitor analysis in Excel or Google Sheets?
Use one row per keyword, not one row per competitor. Set your column headers to competitor URL, target keyword, search intent, keyword difficulty, content format, word count, and defensibility verdict. The last column is what makes the sheet useful, so fill it for every row.
What makes a competitor analysis template actually useful for content strategy?
The defensibility classification column. Without it, you know who ranks but not whether you can displace them. Feature grids and pricing tables tell you about a product rival. The defensibility verdict tells you whether to spend a writer’s day on the keyword.
How to do competitor analysis for free?
Run a manual SERP audit, pull keyword gaps from Google Search Console, and drop the results into the template above. For low-KD keyword sets you need no paid tool at all. The judgment lives in the defensibility column, and that is free to apply once you know the four buckets.
