You exported 20,000 keywords from a competitor gap analysis, and about half of them turn out to be the same keyword with the words shuffled. Someone on Reddit described exactly this a few months back, right down to building a spreadsheet macro that alphabetized every remaining term just so the duplicates would stand up and be counted. The comments then spent the whole thread arguing about whether one version "covers" the others. Nobody won.
That's the real bottleneck, by the way. It's not knowing that Semrush and Ahrefs can show you what your rivals rank for. Everyone knows that. It's the hour after the export, when a good question has turned into a wall of near-duplicates and three different definitions of search volume.
Say you run a small invoicing app, and three competitors seem to outrank you everywhere that matters. You want their keywords. Not all of them, just the ones worth a month of evenings. Here's a working version of automated competitor keyword research: the tedious middle gets handled or at least tamed, and you keep every decision that needs a brain.
Step 1: Pick the rivals you actually fight in search
Your business rivals and your search rivals are rarely the same list. In search, your competitor is whoever occupies the results you want: other software companies, sure, but also comparison sites, an industry blog that published one great guide in 2022 and still lives off it, and a Reddit thread Google can't stop ranking.
The low-tech way to find them: take the five searches you most want to win, type them into Google, and write down who keeps showing up. Do that before opening any tool. Then cross-check with the competitors report your SEO tool already has. Ahrefs calls it Organic Competitors; Semrush has its own version of the same idea. When the two lists disagree, believe the one you built by hand.
You'll almost certainly surface at least one site you never thought of as competition. Those are usually the interesting ones, because they're winning your searches without ever being on your radar.
Step 2: Pull keywords from two tools, not one
One tool gives you a list.
Two give you a reality check.
Semrush's Keyword Gap compares up to five domains side by side and shows where your rivals are present and you're missing. Ahrefs' Content Gap does the same job from the other direction: keywords other sites rank for that yours doesn't. Run both if you can, or run one and spot-check a handful of its answers in the other.
They will disagree, and not politely. Ahrefs' own guide to keyword difficulty admits that scores for the same keyword "will vary quite substantially" between tools, and that the score doesn't account for your site's authority. So a difficulty of 30 means nothing on its own. Thirty for whom?
Volumes drift too, for duller reasons. Ahrefs estimates from a recent 12-month window, Semrush averages the last 12 months, and Google hands advertisers bucketed ranges. Three definitions, one keyword, three numbers. Keep them side by side in your spreadsheet and resist averaging them, because the average of two different definitions is a third number nobody actually measures.
Step 3: Clean the list before it cleans you
Least glamorous step, biggest win. Four cuts, roughly in this order.
Word-order twins. "Invoicing software for freelancers" and "freelancer invoicing software" are one page, not two. Sort the list alphabetically and the twins end up next to each other, which does most of the work for you.
Branded terms. If the keyword contains a competitor's brand name, that click was never yours. Delete without guilt.
Mismatches. Gap reports show you the exact page that ranks for each term. When that page clearly isn't what the searcher wants, your competitor is ranking by accident. That's not a gap, that's noise in a gap costume.
Stuff you can't serve. You'll know it when you see it: the legal-question keyword, the enterprise term a two-person product can't honestly touch. Cut now, grieve later.
The point of cutting hard isn't tidiness. A shortlist you'll actually work through beats a masterpiece you abandon by Thursday.
Step 4: Cross everything against what Google already tells you about your site
Search Console is free, it's Google's own record of how your site performs, and it has two quirks worth knowing before you lean on it. The data runs two to three days behind, per Google's own documentation. And Google withholds some of the searches people actually typed, usually the rare ones, which Ahrefs pegs at close to half of all clicks. Slightly stale, slightly censored. Still the most honest data you own.
The cross-check itself is one question per keyword: do I already show up for this? If you're getting impressions for a term, it was never a gap. If you rank on page two and get no clicks, that's not a content gap either. That's a title-and-snippet problem, which is the cheaper kind and somehow the more annoying kind.
Step 5: Group keywords that share the same results
Here's the step most people skip, right before they publish a new post that competes with their own older one. Keywords that return roughly the same set of results are one topic, not ten.
Keyword Insights explains its approach without hiding the machinery: look at the top 7 results for each keyword, and if two keywords share 40% or more of the same results, they go in one cluster. The percentage is adjustable, and other tools do a version of the same thing. Semrush's Keyword Strategy Builder groups by similar results too.
The payoff is structural. You stop writing one page per keyword and start writing one page per group, aimed at the main term, with the variants riding along for free. What you're not doing is spinning up a page for every keyword left on the list. Google's guidance on people-first content is blunt about mass-producing pages to chase rankings, and gap reports are exactly how that habit starts.
Step 6: Check what the searcher wants, and whether it's your topic
Every tool slaps an intent label on keywords, informational or commercial or whatever, and the labels are mostly right. When a keyword genuinely matters to you and the label looks vague, open the results page yourself and look at it. The live results are the only intent check that can't be out of date.
Then comes the question no tool can answer: is this your topic? Ahrefs has a scoring idea worth stealing called business potential, running 0 to 3. Three means your product is essentially the answer to the search. Zero means there's no natural way to mention what you sell. An invoicing app should not chase "how to start a freelance career" no matter how many searches it has.
And the volume trap, because it catches everyone exactly once: Ahrefs' own strategy guide walks through a keyword with roughly a million US searches a month that sends only about 200 clicks, because an AI overview and a Reddit thread sit on top of the results. Sort a list by volume and work downward, and you can grind for months on keywords that were never going to send you a visitor.
Step 7: Check you're not competing with yourself
Before writing anything new, look at whether two of your pages are already fighting over the same term. Search Console's performance report, filtered to a single search term, shows every page of yours picking up impressions for it. Two pages, one keyword, split signals. Cannibalization is the most self-inflicted problem in SEO, and the fix is usually choosing a winner and pointing the losing page somewhere useful, not writing anything new at all.
Step 8: Automate the boring parts, keep the judgment
Look at what the tedious middle actually consisted of: sorting, deduplicating, grouping, and pulling the same report from three places every month. That's the automatable half. The other half is deciding that your invoicing app should own "late invoice letter template" this quarter and ignore "accounts receivable" entirely. No tool knows your margins, your roadmap, or what you're pushing this month. That part stays yours.
One warning before you hand work to AI: it's genuinely good at sorting and grouping a list you give it, and it should never be allowed near a number it can't look up. SpyFu documented ChatGPT estimating 5,000 to 10,000 monthly searches for a keyword whose real volume was around 700. So let the machine sort, group, and summarize. Never let it estimate volume or difficulty. Those come from a tool with actual data, every time.
This is the gap tools like Inteldo's SEO Analyst are built for: read-only connections to Search Console, PageSpeed Insights and Ahrefs, answers with citations instead of manually assembled reports, and the ability to pass a question to another specialist agent when it drifts into traffic or content territory. Useful, not mandatory. Everything above works with a spreadsheet, a couple of tool subscriptions, and a free Sunday.
What I'd do this week
Skip the grand setup and run the loop once, end to end.
Write down the five searches you most want to win and Google them. Whoever keeps showing up is your competitor list. Run one gap report across those sites, then cut until it hurts: twins, branded terms, mismatches. Cross what's left against Search Console, because anything you already get impressions for is not a gap. Then group what survives, pick one group, and write the single best page on that topic you're capable of. Stop there.
One pass of this beats a month of exporting, because the painful part was never the research. It was the shuffling.
Top comments (0)