SERP Overlap Keyword Clustering Template

Keyword Research Toolkit, template 3 of 6

SERP Overlap Keyword Clustering Template

Decide whether two keywords belong on one page or two by measuring how much their Google results overlap.

Whether two queries should be targeted by one page or separate pages, based on how many ranking URLs their SERPs share.

  • Excel workbook (.xlsx), opens in Google Sheets
  • PDF field guide, 9 to 11 pages
  • Blank and worked CSV templates
  • DataForSEO import script

Free, no email required. Version 3.0, updated November 2026.

SERP Overlap Keyword Clustering Template.xlsx
K13fx=IF(H13="","",IF(H13<>"Ready","n.a.",IF(J13>=F13,"Review one page","Review separate pages")))

Start HereDashboardWorkspaceDelivery QAField Guide

The formula above is the real one from cell K13. Scroll it sideways to read the whole rule.

Connected to the DataForSEO API

This template is designed around one DataForSEO endpoint. Pull the data, convert it with one command, and the workbook does the rest.

Setup steps

DataForSEO SERP API: Google OrganicThe top organic results for a queryserp/google/organic/live/advanced

Save the JSON responseThe full API response, kept as your source of truth.response.json

Run the import scriptOffline. No API calls, no credentials.dataforseo_import.py overlap

Paste at A13Dashboard, checks and action list update instantly.SERP Overlap Keyword Clustering Template.xlsx

Column map for this template:Filled by the APIAudit trail (automatic)You add

Query AQuery BUnique URLs AUnique URLs BShared URLsReview cutoffSource reference
7 of 7 input columns come straight from DataForSEO

What the SERP overlap keyword clustering template decides

Language models and keyword tools are good at grouping words that look alike. Google doesn't rank pages by how alike the words look; it ranks them by what searchers want. The fastest reliable signal of shared intent is the SERP itself: if the same URLs rank for both queries, Google is treating them as the same need.

This keyword clustering template measures that overlap for each pair of queries. Enter how many URLs you compared for each query and how many appear in both, and it calculates the Jaccard overlap (shared URLs ÷ all unique URLs). Pairs above your cutoff are flagged Review one page; pairs below it, Review separate pages.

Who it's for

  • SEOs clustering a large keyword list into page-level targets
  • Writers asking whether a variant needs its own article
  • Teams diagnosing cannibalization
  • Anyone checking an AI-generated keyword cluster against real SERPs

Where it fits in your workflow

The SERP overlap keyword clustering template answers one question. These three templates answer the questions around it:

  • Keyword Mapping Template: which single URL should own each keyword, and where your map assigns the same query to two different pages.
  • Keyword Cannibalization Checker Template: which query and URL pairs are true cannibalization worth consolidating, which are distinct intents to leave alone, and which page should win.
  • Keyword Prioritization Template: which keywords deserve your next hours of work, ranked by the monthly profit each could add per hour invested, with every assumption visible and editable.

What's inside the download

One ZIP with the workbook, a 7-column input template (blank and filled), the PDF field guide and an offline script that converts saved DataForSEO responses into the input CSV. The workbook has five tabs.

Workspace. The working table. 7 input columns, 4 calculated columns, room for 250 rows. Dropdowns, Yes/No fields and range checks catch bad inputs as you paste.
Start Here. What it decides, a four-step setup and the colour key.
Dashboard. KPI cards, charts, a ranked Top 10 and an automatic action list.
Delivery QA. Pass, fail or exclude each validation step with evidence, owner and date.
Field Guide. Every field explained, plus each formula in plain English.

How the keyword clustering template calculation works

Union URLs = URLs for A + URLs for B − shared URLs. Jaccard overlap = shared ÷ union. With 10 results each, 3 shared URLs gives 3 ÷ 17 = 0.18, while 7 shared gives 7 ÷ 13 = 0.54. The cutoff is editable per row so you can be stricter for money pages and looser for blog content.

The key formula, exactly as it appears in the workbook

=IF(H13="","",IF(H13<>"Ready","n.a.",IF(J13>=F13,"Review one page","Review separate pages")))
Result column Plain-English formula What it tells you
Union URLs Unique URLs A+Unique URLs B-Shared URLs Unique URLs across both sets.
Jaccard overlap Shared URLs ÷ Union URLs Shared URLs divided by union URLs.
Grouping review IF(Jaccard overlap ≥ Review cutoff,"Review one page","Review separate pages") Suggestion for human intent review.

The results you will see

Review one pageReview separate pages

Green meets the checks, red needs fixing first, amber needs a human decision and blue marks an opportunity. Rows with missing or out-of-range inputs are held back in the Input check column until they're fixed, so a half-filled row never sneaks into a total.

How to use the SERP overlap keyword clustering template

  1. Pick query pairs to testStart with the pairs you're unsure about: close variants, singular vs plural, 'best X' vs 'X'.
  2. Pull the top resultsCollect the top 10 organic URLs for each query from the same location, language and device on the same day.
  3. Count shared URLsEnter the number of URLs compared for each query and how many appear in both lists. Keep the review cutoff at 0.3 unless your niche calls for stricter grouping.
  4. Group and mapPairs marked Review one page go into the same cluster in your keyword map. Pairs marked Review separate pages get their own target URL.

The worked example in the Workspace tab. Clear the lime input cells, paste your CSV at A13 (values only) and every result recalculates.

Fill the SERP overlap keyword clustering template with DataForSEO

Every template in this library is built to run on DataForSEO data. This one uses DataForSEO SERP API: Google Organic: the top organic results for a query.

A helper command builds the input rows from saved API responses. The overlap command compares every pair of saved SERPs from the same location, language and device and fills all six columns for you.

  1. Request the dataSend this request with your DataForSEO API login (HTTP Basic auth).
    POST https://api.dataforseo.com/v3/serp/google/organic/live/advanced [ { "keyword": "your keyword", "location_code": 2840, "language_code": "en", "device": "desktop", "depth": 10 } ]

    Send one task per query with identical location, language and device, and save each response.

  2. Save the full JSON responseSave the complete response, not just the task ID, next to the template folder.
  3. Convert it with the included scriptIt runs offline, makes no API calls and never sees your credentials.
    python3 Automation/dataforseo_import.py overlap --input serp-*.json --output my-inputs.csv --depth 10 --cutoff 0.3
  4. Add what the API can't knowNothing: every input column fills from the API.
  5. Paste at A13Clear the demo rows, paste the CSV values at Workspace cell A13 and the dashboard updates.
Fills automatically from DataForSEO You add
Query A, Query B, Unique URLs A, Unique URLs B, Shared URLs, Review cutoff Nothing

Source reference fills automatically with the task ID, endpoint, location, language, device and snapshot time, so every row is traceable. Before a big pull, estimate the cost with the DataForSEO pricing calculator. The same steps, plus an example request file, are in the download's Automation folder.

Worked example: Northbeam Outdoor

Every template ships filled with data from Northbeam Outdoor, a fictional outdoor-gear retailer that appears across all 40 workbooks, so you can see what useful output looks like before you add your own. This workbook has 16 example rows.

16Records
0Rows with input issues
32.0%Mean pair overlap
8Pairs to consolidate

In the example, the boot sizing pair and the rain jacket pair overlap heavily and belong on one page each, while 'trekking poles' and 'how to use trekking poles' share almost nothing: a product page and a how-to guide.

Top 10 by jaccard overlap

# Query A Jaccard overlap
1 what to pack for a day hike 66.7%
2 rain jacket womens 66.7%
3 hiking boot sizing guide 53.8%
4 waterproof hiking boots 53.8%
5 day hiking backpack 42.9%

Input fields

Keep the columns in this order when you prepare a CSV. A missing observation stays blank; use 0 only for a measured zero or a verified No. Fields marked DataForSEO can be filled automatically by the included script.

CSV field Column Meaning and rule Source
query_a Query A First query in a matched market and device. Your records
query_b Query B Second query in the same market and device. Your records
urls_a Unique URLs A Distinct organic URLs in the selected depth for query A. Your records
urls_b Unique URLs B Distinct organic URLs at the same depth for query B. Your records
shared_urls Shared URLs Distinct intersection of the two organic URL sets. Your records
review_cutoff Review cutoff Analyst-selected Jaccard cutoff for grouping review. This is not a search-engine threshold. Decimal: 0.08 = 8%. Your records
source_ref Source reference Task ID, export filename and observation scope. Retain the untouched source response separately. Optional. DataForSEO: source_ref

Reading the result honestly

Overlap is supporting evidence for content consolidation. Inspect dominant page types before assigning one URL.

Limits. Pairwise chains can connect dissimilar endpoints. Do not treat connected pairs as an automatically validated cluster.

Keyword clustering template mistakes to avoid

Comparing SERPs from different days or locationsSERPs move. Pull both lists at the same time with the same settings or the overlap is noise.
Counting SERP features as URLsCompare organic URLs only. Videos, shopping and People Also Ask blocks inflate the union and lower the overlap.
Treating 0.3 as a lawThe cutoff is a starting point. Check a few borderline pairs by eye and adjust.

Tips from practice

  • Pull SERPs at scale with the DataForSEO organic endpoint and count overlaps in a script, then paste the counts here.
  • Re-test clusters for important pages twice a year; intent drifts.
  • Use the overlap score to settle debates with writers: it's evidence, not opinion.

Frequently asked questions

What is keyword clustering by SERP similarity?

It groups keywords based on how many of the same URLs rank for them in Google. High overlap means Google sees one intent, so one page should target both.

What overlap means two keywords belong together?

A common rule of thumb is three or more shared URLs in the top 10, roughly a Jaccard score of 0.18 to 0.3. This template defaults to 0.3 for a stricter grouping.

What is Jaccard similarity?

It's the size of the intersection of two sets divided by the size of their union. Here, shared ranking URLs divided by all unique ranking URLs across both queries.

Can I use this with Google Sheets?

Yes, the XLSX opens in Google Sheets and Excel with all formulas working.

Part of the Keyword Research Toolkit

Get all 6 Keyword Research templates in one ZIP, or the complete set of 40 SEO and GEO templates with the 401-page field handbook.

Download the Keyword Research Toolkit

See the complete SEO & GEO toolkit

Jourdan Rombough

Built by Jourdan Rombough, technical SEO and GEO consultant with 15+ years in search. About Jourdan or get an SEO plan.

More on digital marketing in our blog

See all posts