Cluster Health Automation: Build a Lightweight Monitoring Sheet

Cluster Ops

Cluster Health Automation: Build a Lightweight Monitoring Sheet

Run cluster health like a process. Import Search Console data, join with your cluster map, compute a health score, and trigger refresh tasks. No scripts required.

Data: Search Console performance • Planner: Keyword Insights for SERP clusters

Export your cluster map Want me to build the sheet

Why automate cluster health

Clusters perform as a system. When one page decays or overlaps with a sibling, your hub, answers, and BOFU routes all feel it. A lightweight monitoring sheet gives you the signal to fix issues fast and measure the lift after a refresh.

Consistency because you check the same metrics every week
Speed because red flags are computed for you
Attribution because fixes are logged and linked to results

All data in this guide comes from the Search Console performance report and your own cluster export.

Sheet blueprint

Create a spreadsheet with the following tabs. Keep names exactly as written to make formulas easy.

Tab	Purpose	Key columns
Config	Thresholds and weights	RankTop10Target, CTRDropWarn, OverlapWarn, OrphanWarn, Weights
Pages	Canonical URLs and primary anchors	URL, Slug, PrimaryAnchor, ClusterID
Clusters	Export from Keyword Insights	ClusterID, HeadTerm, PageType, Intent
GSC_Pages	Search Console page export	URL, Clicks, Impressions, CTR, Position, DateRange
GSC_Queries	Search Console query export	Query, URL, Clicks, Impressions, CTR, Position
Join	Pages matched to clusters	URL, ClusterID, HeadTerm, PageType
Health	Computed metrics and score	ClusterID, KPIs, Score, Status, Priority
Fix_Log	What you shipped and when	Date, ClusterID, FixType, Notes, Owner

Get the data

Search Console exports

Open Performance
Choose last 28 days and 28 days before that
Export the Pages report and the Queries report for each period
Paste into GSC_Pages and GSC_Queries, add a DateRange label like Now or Prev

Cluster map

Export your cluster sheet from Keyword Insights
Paste ClusterID, HeadTerm, PageType, Intent into Clusters
Ensure each URL in Pages has a ClusterID that matches

If you have many clusters, you can still run this with weekly manual exports. For very large sites, consider a data warehouse later. Start simple first.

Build the sheet step by step

1) Normalize URLs and slugs

Add columns in Pages:

Slug from URL path
Folder for quick grouping, for example =REGEXEXTRACT(A2,"^https?://[^/]+/([^/]+)/")

2) Join pages to clusters

In Join use:

=ARRAYFORMULA(VLOOKUP(Pages!D2:D, Clusters!A:D, {1,2,3,4}, FALSE))

This pulls ClusterID, HeadTerm, and PageType for each URL.

3) Compute period metrics

In GSC_Pages filter by DateRange with Now and Prev. In Health, aggregate per cluster:

=QUERY({Join!A:D, GSC_Pages!B:F},"select Col2,sum(Col6),sum(Col7),avg(Col8) where Col5='Now' group by Col2",1)

Repeat for Prev, then join on ClusterID.

4) Rank distribution

Create bins from Position in GSC_Queries by cluster:

=COUNTIFS(GSC_Queries!F:F,"<=10",GSC_Queries!B:B,URL_RANGE)

Divide by total queries for the cluster to get share in top 10.

5) Overlap detector

For each query, count distinct URLs getting impressions:

=QUERY(GSC_Queries!A:C,"select A,count(distinct B) where C>0 group by A",1)

Join back to clusters, flag queries with more than one URL in the same cluster.

6) CTR and impression deltas

CTRΔ = (CTR_Now - CTR_Prev) / MAX(0.0001, CTR_Prev)

ImpΔ = (Impr_Now - Impr_Prev) / MAX(1, Impr_Prev)

Keep the pipeline simple. Update data, recalc metrics, log fixes, and watch the score.

Health score and alerts

Combine a few signals into one score between 0 and 100. Keep weights in Config so you can tune later.

Suggested inputs

RankTop10Share percent of queries in top 10
CTRΔ change vs previous period
ImpΔ impression change vs previous period
OverlapCount queries with 2+ URLs in the same cluster
OrphanCount optional if you track internal links

Example formula

In Health:

=ROUND(100 * ( 0.45*RankTop10Share + 0.25*MAX(-0.2,MIN(0.2,CTRΔ)) + 0.20*MAX(-0.3,MIN(0.3,ImpΔ)) + 0.10*(1 - MIN(1,OverlapCount/5)) ),0)

Cap deltas to avoid spikes and penalize high overlap. Adjust weights in Config.

Status	When to trigger	Suggested action
Green	Score ≥ 75 and OverlapCount = 0	Monitor only
Yellow	Score 55 to 74 or CTRΔ ≤ -10%	Refresh entities and examples
Red	Score < 55 or OverlapCount ≥ 5	Consolidate pages and repair anchors

Dashboards and views

Cluster leaderboard

Sort Health by Score descending. Add a sparkline:

=SPARKLINE(FILTER(GSC_Pages!C:C, GSC_Pages!A:A=URL), {"charttype","line"})

Overlap view

Pivot GSC_Queries with Query rows and distinct URL count as values. Filter to count ≥ 2.

Fix log

Track the change you shipped, the winner URL, and the redirect map. Use data validation for consistent FixType values.

Weekly runbook and SLAs

Update data paste new Search Console exports into GSC_Pages and GSC_Queries
Review alerts sort Health by Status then Score
Pick work for red items choose merge or rewrite, log in Fix_Log
Ship consolidate content, 301 redirect, update internal links, resubmit the destination page
Monitor check 14, 28, and 84 day comparisons for lift

Signal	Target SLA
OverlapCount	Zero within two weeks of detection
RankTop10Share	Grow five percent per quarter for strategic clusters
CTRΔ	Within ten percent of three month baseline

Cluster and export head terms Want me to build and run the board

FAQ

Can I automate the data pull

You can continue with manual exports each week. If you later centralize data, keep the same tab structure so formulas do not change.

Do I need to track every signal

No. Start with rank share, CTR trend, and overlap. Add impressions and orphans when you are ready.

What if my clusters mix formats

Use the overlap view to test SERPs. If results differ by format or audience, split the cluster and adjust anchors and routing.

How soon should I see improvement

For merges and internal link repairs, many teams see positive signals within two to six weeks. Keep monitoring with 28 and 84 day comparisons.