SERP Overlap Keyword Clustering Template (Optimization Theory, v3.0, November 2026)
==============================================================================

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

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

Files
  SERP Overlap Keyword Clustering Template.xlsx ............ the workbook (Start Here, Dashboard, Workspace, Delivery QA, Field Guide)
  SERP Overlap Keyword Clustering Template - Field Guide.pdf  the illustrated guide
  Input-Template-blank.csv ..... column headers in paste order
  Input-Template-example.csv ... the worked example (Northbeam Outdoor, fictional data)
  HOW-TO-USE-WITH-DATAFORSEO.txt step-by-step: request, save, convert, complete, paste
  Automation/ .................. import script, example request, field mapping (python3 Automation/dataforseo_import.py --help)

Built to run on DataForSEO
  Source: DataForSEO SERP API: Google Organic
  Fills automatically: Query A, Query B, Unique URLs A, Unique URLs B, Shared URLs, Review cutoff
  Full steps: HOW-TO-USE-WITH-DATAFORSEO.txt (also in the Automation folder), with example-request.json

Quick start
  1. Pick query pairs to test: Start with the pairs you're unsure about: close variants, singular vs plural, 'best X' vs 'X'.
  2. Pull the top results: Collect the top 10 organic URLs for each query from the same location, language and device on the same day.
  3. Count shared URLs: Enter 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 map: Pairs marked Review one page go into the same cluster in your keyword map. Pairs marked Review separate pages get their own target URL.

Works in Microsoft Excel, Google Sheets (upload, then open with Sheets) and LibreOffice Calc.
Paste values only at Workspace cell A13. Lime cells are inputs; blue cells are formulas.

Full tutorial: https://optimizationtheory.com/download-view/keyword-clustering-template/
Part of the Keyword Research Toolkit: https://optimizationtheory.com/download-view/keyword-research-toolkit-6-seo-planning-workbooks/
All 40 templates: https://optimizationtheory.com/download-view/free-seo-geo-toolkit-40-spreadsheets-and-field-guides/

Free to use in your own and client work. Please don't resell the templates themselves.
Built by Jourdan Rombough, optimizationtheory.com
