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.
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.