What competitor keyword analysis actually decides
Most teams run this exercise once, export a few thousand rows, and never open the file again. The bottleneck is rarely effort. A raw gap export answers "what does the competition rank for?" when the real question is what you should publish next, and why that instead of something else. Teams often ask how to do competitor keyword analysis and get handed a tool login instead of a method.
Two limits shape everything below. Every volume figure is an estimate: Google's Keyword Planner defines average monthly searches as the average number of times people searched for a keyword and its close variants, averaged over a 12-month period by default, and Google notes that the statistics are rounded, so totals across locations never quite add up (Google Ads Help). Difficulty is just as approximate. Ahrefs' Keyword Difficulty pulls the top 10 ranking pages and counts how many websites link to them, and Ahrefs states plainly that every tool calculates the metric differently, that the same keyword's score varies substantially between tools, and that the score describes the average website rather than yours (Ahrefs).
Treat both numbers as filters instead of facts, and a competitor analysis for SEO stops being a report and becomes a decision tool.
Step 1: Collect competitor keywords without hoarding
Collect from four surfaces, and timestamp every one:
- Your own query data. Export the queries dimension from Google Search Console. It shows the queries that earned impressions and clicks for your property, with clicks, impressions, CTR, and average position (Search Console Help). This is your baseline: what you already earn, and what you almost earn.
- Competitor ranking exports. Pull the keywords where two or more SERP competitors rank in the top 10 and you do not. Split each row into missing (no page at all) or weak (a page ranks poorly) before you do anything else.
- SERP discovery surfaces. Autocomplete, related searches, and People also ask show the phrasing real searchers use. Pull them for your seed terms, not for every competitor.
- Paid-search suggestion exports. Any keyword tool that proposes variants from a seed term can widen the top of the funnel. Keep suggestion rows separate from ranking rows; suggestions are hypotheses, not validated demand.
Say you publish tutorials for a 3D printing slicer such as Orca Slicer, and your SERP neighbours are pages about Bambu Studio, PrusaSlicer, and Cura. One export will hand you branded queries like "bambu studio profile settings", comparison queries like "orca slicer vs bambu studio", and category queries like "3d printer profile settings". Do not clean them yet. Log the source, the date pulled, the market, and whether the volume is a bucket or an exact metric.
Completion check: every row traces back to a named source with a date attached. If a row cannot, delete it now rather than later.
Step 2: Clean the list like a data set
Cleaning is where most of the judgment lives, so make it visible. Work through the raw file in this order:
- Normalise and dedupe. Lowercase the strings, strip punctuation, collapse plurals and spelling variants, and keep one canonical row per distinct query. Dedupe at the cluster level rather than the string level: "orca slicer profile settings", "orca slicer presets", and "slicer profile settings" are one topic with three phrasings.
- Suspect identical volumes. Google groups a keyword together with its close variants into one reported number, so two rows showing the same bucket are often the same underlying query rather than two opportunities.
- Strip noise with a written rule. Remove other brands' navigational terms, job-seeker queries, and modifiers that describe someone else's problem rather than your product. Write the rule down, because "I dropped it because it felt wrong" does not survive a handover.
- Separate seeds from long tail. A seed carries real demand and a broad topic; a long-tail phrase is a specific way of asking the same thing. Group every long tail under one seed before scoring, or you will publish five thin pages that compete with each other.
- Record the decision. Add two columns to the sheet: kept (yes or no) and why. That single column is the difference between an analysis and an export.
Step 3: Prioritise with a score you can defend
Score the survivor rows on five inputs, then convert the score into a decision:
Input | Scale | The question it answers |
|---|---|---|
Product fit | 1-5 | Does the query describe something we actually offer or teach? |
Intent value | 1-5 | How close is this searcher to a decision we can serve? |
Coverage gap | 0-3 | 3 = no page, 1-2 = a weak page, 0 = already covered well |
Volume band | 1-5 | The log band from your market data, never the raw count |
Difficulty band | 1-5 | The difficulty score for this keyword, read against your own baseline |
Priority = (Fit x 3) + (Intent x 2) + Gap + Volume - Difficulty
Because difficulty scores are absolute rather than relative to your site, calibrate the band first. Look up the scores of keywords you already rank for in the top 20 and treat that range as your reachable band; anything far above it is a later play. Then read the actual SERP. Ahrefs' own guidance is that a difficulty score can hide exploitable cracks, such as results that have not been updated in years, so a mid-score keyword with stale top results can be easier than its number suggests.
Two rules keep the score honest. Score the cluster rather than each phrasing, because the cluster is what you will publish. Then rank the result and cut it to the 20-40 rows your team can genuinely produce this quarter. A scored list of 900 rows is a filing cabinet; a scored list of 25 rows is a plan.
The judgment calls that decide the shortlist
The model handles the easy rows. These four cases are where teams stall, and where a written rule saves an hour of debate.
A competitor's branded query. A term like "bambu studio profile settings" can show tempting volume, but you cannot win a query whose answer is someone else's product. Keep branded competitor terms out of the content plan unless you are a legitimate alternative for that exact job. Even then, the right target is a comparison or alternative page, not a blog post.
A high-volume query that does not fit the product. A general "what is keyword research" query brings readers you cannot help and cannot convert. Score it low on product fit even when its volume band is the highest in the file. Volume you cannot serve is a cost, not an opportunity.
Two clusters that overlap. When two clusters describe the same job, merge them. Pick one primary query, keep the other as a secondary on the same URL, and write the merge reason in the sheet. Splitting them across two URLs is how cannibalisation starts.
A weak row rather than a missing one. If you already rank around position 12-20 with a thin page, upgrading that URL usually beats publishing a new one. Reserve new URLs for topics with no page at all.
A reusable template you can copy
Build one sheet with these columns, in this order: keyword, cluster, source, date pulled, market, intent, volume band, difficulty score, product fit (1-5), intent value (1-5), coverage gap (0-3), priority score, action (create, upgrade, optimise, or skip), owner page, review date, and decision note.
Fill it in three passes so each pass has one job: paste the raw rows, clean them down to one row per cluster, then score only the survivors. The decision note is not optional. It is the audit trail that lets a teammate challenge a call without rerunning the whole analysis. Together, the scoring table and this sheet give you a competitor keywords example you can copy row for row.
The cadence matters as much as the template. Check rankings for the top 20 rows monthly, rerun the gap export after you publish something significant, and redo the full analysis once a quarter. If your category moves fast, shorten that to every six weeks.
Completion standard: the analysis is finished when every row in the final sheet has an action, an owner page or an explicit "new URL", and a review date. A row missing any of the three is still a wish rather than a plan.
Where this usually breaks
Three failure modes are worth naming. The first is scoring before cleaning, which inflates scores and produces a busy shortlist that nobody acts on. The second is comparing difficulty scores across tools as if they measured the same thing; they do not, so pick one tool and use it for the whole analysis. The third is treating a single export as the answer. Competitor data is a sample of a moving target, which is exactly why the template above ends with a review date instead of a conclusion.
