Which keywords do both sites rank for?

The data

Overlap and gaps in SQL

-- keywords both rank for
SELECT a.keyword, a.pos AS site_a, b.pos AS site_b
FROM a JOIN b ON a.keyword = b.keyword;

-- keywords only site B ranks for (site A's gaps)
SELECT b.keyword FROM b LEFT JOIN a ON a.keyword = b.keyword WHERE a.keyword IS NULL;

Site A's gap: “consolidate excel”. Site B's gaps: “combine csv files” and “vlookup two sheets”.

In a spreadsheet

Site B position =XLOOKUP(LOWER(TRIM([@Query])), LOWER(TRIM(B[Query])), B[Position], "")
Both            =FILTER(A[Query], ISNUMBER(XMATCH(LOWER(A[Query]), LOWER(B[Query]))))

Uses

• Two of your own sites (or a blog and a docs subdomain): overlapping queries may be competing with each other — consolidate or differentiate the pages.
• You vs a competitor: overlap shows where you compete directly; their unique keywords are content ideas.
• Weight by impressions or search volume, not just counts — one high-volume gap matters more than ten tiny ones.

Ask it in Analistable: “Which queries do both sites rank for in the top 20, and where is site B ahead?”

Prioritising the gaps

Add impressions or search volume to each gap keyword and sort descending. A gap at 5,000 searches a month where the other site ranks in the top 3 is worth a dedicated page; a gap at 50 searches is a section in an existing one. For overlap keywords, sort by the position difference to see where you're closest to overtaking.

Who is ahead on shared keywords

SELECT a.keyword, a.pos - b.pos AS gap,
       CASE WHEN a.pos < b.pos THEN 'A' ELSE 'B' END AS ahead
FROM a JOIN b ON a.keyword = b.keyword
ORDER BY gap;

Lower position numbers are better, so a positive gap means site B is ahead. Site B leads on both shared queries; “merge excel files” is the closest race at 1.3 places.

Clean the queries before joining

• Lower-case and trim both lists; Search Console usually reports queries in lower case, but rank-tracker exports may not.
• Decide whether near-variants count as the same keyword (“merge excel file” vs “merge excel files”). An exact join treats them as different; a mapping table can group them.
• Use the same country and device filter on both sides, or positions aren't comparable.
• Average position in Search Console is an average over impressions, not a single rank — treat small differences (under 1) as a tie.

Two of your own sites

When both sites are yours, export queries with the page dimension from each Search Console property. For overlapping queries, list the ranking page on each site: if both are about the same thing, merge them or make one clearly different. Combining Search Console with analytics data helps decide which page to keep — see finding pages losing clicks.

Troubleshooting

Frequently asked questions

How do I compare keywords between two sites?

Export queries with positions from each, join on the query text (lower-cased and trimmed), and compare positions.

How do I find keyword gaps?

List queries the other site ranks for that have no match in your site's export — a left anti join.

Can I get a competitor's Search Console data?

No; use a rank-tracking or SEO tool's export for the competitor and your Search Console export for your site.

Which site is ahead on a shared keyword?

The one with the lower average position. Subtract one position from the other and sort by the gap.

Do plurals count as the same keyword?

Not with an exact join. Group variants with a mapping table if you want them treated as one.

Related guides

• Which suppliers deliver late, and by how much?
• Which leads never became customers?
• Why don't these two reports add up to the same total?
• Which customers are new this month?
• Which orders were refunded but still shipped?