Content Consolidation Analyser

Features

• Clusters queries that share SERP URLs (--min-urls threshold, default 4)
• Connected component and clique detection, both written to the output
• Consolidation scoring 0-100 with recommendation bands
• Connectivity metrics (actual vs possible query connections)
• Overlapping clusters allowed, flagged via is_in_multiple_clusters
• Configurable query and URL column names for any SERP or GSC export

How to use

1. 1 Export SERP or GSC data as CSV with one row per query and ranking URL
2. 2 Run the script with --input (wildcards supported for multiple files)
3. 3 Set --query-col and --url-col unless your columns match the SERP API defaults
4. 4 Adjust --min-urls to control clustering sensitivity (default 4 shared URLs)
5. 5 Review query clusters with consolidation scores and recommendations
6. 6 Check the CSV output; unclustered queries appear under NO_CLUSTER

Frequently asked questions

What input format does it expect?

A CSV (or several, via a wildcard like data/*.csv) with one row per query and ranking URL pair, i.e. a query column and a URL column. The default column names are search.q and result.organic_results.link, which match a flattened SERP API export. For Search Console or rank tracker data you must pass --query-col and --url-col, otherwise the read fails because those exact columns are not found.

Does it cluster pages or keywords?

Keywords. Two queries are joined when their result sets share at least --min-urls URLs (default 4), and each output row is a query, not a page. The cluster_id is simply the shortest query in the cluster. A cluster of queries whose SERPs keep surfacing the same URLs is the signal that one page could target all of them.

Why does the same query appear in more than one row?

Two clustering strategies both run and both write to the output: connected components (loose chains of overlap) and cliques (every query overlaps every other). The same group of queries can therefore appear once as cluster_type connected_component and again as clique, and overlapping cliques are allowed. Filter on cluster_type if you want a single view, and use is_in_multiple_clusters to spot queries claimed by several clusters.

How is the 0-100 consolidation score built?

Up to 40 points from average shared URLs (4 points per shared URL, capped), up to 30 points from connectivity (the fraction of query pairs in the cluster that actually overlap), up to 20 points from cluster size (5 points per query beyond two), minus up to 10 points if queries sit in multiple clusters. Recommendations map to fixed bands: 80+ strong candidate, 60+ good, 40+ possible, 20+ weak, below 20 keep separate.

Is there a practical size limit?

Similarity is computed for every pair of unique queries, so runtime grows with the square of the query count. It is fine for a few thousand queries but will crawl on very large exports; pre-filter to one topic or folder if you have tens of thousands. Also note the shared_urls column in the CSV only lists the first five shared URLs, the shared_url_count column has the true number.