SERP Overlap Keyword Clustering Template
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.
=IF(H13="","",IF(H13<>"Ready","n.a.",IF(J13>=F13,"Review one page","Review separate pages")))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.
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
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.
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
- Pick query pairs to testStart with the pairs you're unsure about: close variants, singular vs plural, 'best X' vs 'X'.
- Pull the top resultsCollect the top 10 organic URLs for each query from the same location, language and device on the same day.
- 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.
- 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.
- 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.
- Save the full JSON responseSave the complete response, not just the task ID, next to the template folder.
- 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
- Add what the API can't knowNothing: every input column fills from the API.
- 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.
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
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.
Use it with these templates
Each template answers one decision. These are the ones that feed into, or follow from, the SERP overlap keyword clustering template.
02Keyword Mapping TemplateLabs: Ranked Keywords
Assign every keyword to exactly one URL and catch conflicting assignments before they become cannibalization.
22Keyword Cannibalization Checker TemplateLabs: Ranked Keywords
Find queries splitting clicks across two URLs, check whether intent really overlaps and decide whether to consolidate.
01Keyword Prioritization TemplateLabs: Keyword Overview
Rank every keyword by the profit it could add per hour of work, not by search volume alone.
04Keyword Gap Analysis TemplateLabs: Ranked Keywords
Find the queries your competitors rank for that you don't, and size each gap in potential clicks.
19SEO Content Brief TemplateLabs: Keyword Overview
A content brief built for Google and AI answers: real questions, original evidence, sources and a three-step approval workflow.
25AI Prompt Research TemplateLabs: Keyword Overview
Turn real customer tasks and keywords into a weighted prompt panel for ChatGPT, Perplexity, Gemini and AI Overviews.
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.
Further reading on Optimization Theory
Built by Jourdan Rombough, technical SEO and GEO consultant with 15+ years in search. About Jourdan or get an SEO plan.






