How to build a keyword map — the deliverable that survives an agency change

The map starts where the keyword list stops

Keyword research ends with a validated list of queries grouped by intent. That's a genuine deliverable and it is not a plan. Nothing in a list tells you which page is supposed to rank, whether that page exists, or what happens when a writer picks the same topic again in March.

The map is the artefact that answers all three, and the trick is a change of shape that sounds trivial and isn't. A keyword list has one row per keyword. A keyword map has one row per URL. Every keyword either lands in a row or it doesn't have a home yet, and rows with no home are the backlog.

That inversion is the whole design. It makes the URL the primary key, and once the URL is the primary key, the spreadsheet can enforce rules about it — starting with the only rule that actually matters.

The columns, and the one most maps leave out

Nine columns. Fewer than that and it stops answering questions; more and nobody maintains it.

Most published templates carry the first five. The sixth is the one almost everybody omits, and it's the column that makes the rest of the map decidable rather than debatable.

• url — absolute, and the primary key. One row per URL, always. Pages you haven't built yet get their intended URL here, in a different fill, so the shape of the site is visible before it exists. It's also where structure gets decided, so be deliberate: descriptive words rather than IDs, and topically similar pages grouped into directories, both of which Google's starter guide recommends.
• page_type — service, product, category, blog, comparison, glossary, location. Determines the template, the internal link pattern and who writes it.
• primary_keyword — exactly one. Never two. Never a comma.
• cluster — the secondary terms this URL is also meant to serve, comma-separated. Usually five to twenty; occasionally one.
• intent — informational, commercial, transactional, navigational. Copied from the research, not re-litigated here.
• current_ranking_url — the URL of yours that Search Console shows receiving impressions for the primary keyword today. This is the column most maps omit. It's the difference between planning in the dark and planning against what Google has already decided about your site.
• decision — new, update, merge or retire. Four values, and every row must carry one before anything gets written.
• status — planned, briefed, drafting, published, needs-refresh. This is what makes the map a work queue instead of a document.
• owner and date — a human name and the date the row last changed. Rows nobody owns are the rows still sitting in planned at the end of the contract.

One primary keyword per URL, and why it's enforced rather than encouraged

The rule is easy to state and almost never enforced: no keyword may appear as the primary_keyword of more than one URL, and no URL may have more than one primary keyword.

Enforced means a formula, not a good intention. A conditional-format rule that turns the primary_keyword column red on any duplicate takes thirty seconds to add and does the work of a quarterly audit. If your map lives in a database or a CMS field instead of a sheet, make it a unique constraint and let the write fail.

That's the entire cannibalisation mechanism. Two pages chasing the same intent don't announce themselves — they show up months later as rankings that flip between URLs while clicks drift down and nothing looks broken. In a map with an enforced unique key, the collision happens at the moment someone types it, in a spreadsheet cell, before a word is written.

It matters because the alternative isn't neutral. Where several of your URLs are close enough to read as duplicates, Google decides which one to show if you haven't, and its choice is made on its own evidence, not on which page you'd rather sell from.

Mapping a cluster without breaking the rule

The obvious objection: a page ranks for hundreds of queries, so one primary keyword per URL looks like an artificial constraint invented by spreadsheets.

It isn't, because the primary isn't a claim about what the page will rank for. It's a claim about what the page is for — the single query whose searcher, if they landed here, should feel the page was written for them. Everything else the page serves goes in cluster, and a healthy cluster column has fifteen entries in it.

So the question is only ever which terms belong in one URL's cluster and which need their own row. The test is SERP overlap, not wording: if the top results for two queries are largely the same pages, Google is treating them as one need and one URL should serve both. If the results diverge, they're two needs, two rows, two primaries — however similar the words look.

Two failure modes sit either side of this. Splitting produces a dozen thin near-duplicates that split their own signal, and Google's guidance on people-first content asks directly whether you're producing lots of content on many topics hoping some of it performs. Over-merging produces one enormous page trying to answer a buying question and a definition question at once, satisfying neither.

New URL or existing URL — the decision, applied the same way every time

This is the decision the map exists to force, and left informal it defaults to 'new page', because writing a new page is more fun than fixing an old one and easier to invoice.

The default should be the opposite. An existing URL carries internal links, age, and whatever authority it has accumulated. A new URL starts at zero and has to earn all three again. So: update unless there's a reason not to, and write the reason in the row.

Fill current_ranking_url first, from the Search Console performance report — filter to the primary keyword, switch to the Pages view, and record whichever URL of yours receives the impressions. Then read the decision off the table below.

• A new URL also needs an internal linking plan in the same row. A page with no links pointing at it from your existing site is a page you've published into a cupboard.
• retire is a real option. Pages that rank for nothing, serve no intent and duplicate a better page should be merged and redirected, not left to dilute the site.
• Record the reason, not just the verdict. *Updating: /seo-audit already ranks 14 for this.* Six months on, that sentence is why nobody re-argues the decision.

The map as an early-warning system

Once the map is enforced, it starts telling you things Search Console won't for another quarter.

The first is duplication before publication. A writer proposes a piece, someone checks the primary against the map, the cell turns red, and the conversation happens while it costs an email. Compare that with the alternative: the page ships, both URLs oscillate for four months, and the fix becomes a merge, a redirect and a rewrite of every internal link pointing at the loser.

The second is drift. Add a quarterly pass where you refresh current_ranking_url for every row from Search Console and compare it against url. Rows where the two disagree are pages Google has quietly reassigned — usually because a newer post is now a better match for the query the original page was built for. That mismatch is the earliest reliable signal of cannibalisation, and it appears in a spreadsheet column long before it appears in a traffic chart.

The third is honest capacity. A map with 240 rows in planned and four published a month is not a plan, it's a wish, and the map makes the arithmetic impossible to avoid in a status call.

Why this is the file to demand when you change agency

Most agency handovers transfer assets: logins, drafts, a backlink list, a folder of reports. All useful, none of it explains anything. Six months later the new agency is redoing keyword research you already paid for, because nothing on the old drive records why the previous team made the choices they made.

The keyword map is the exception. It carries the reasoning — which URL owns which query, what was merged into what, which pages were deliberately retired, and what's still unassigned. That's the expensive part of the work, and it's the part that's cheapest to lose.

So ask for it in the contract, in a portable format, updated monthly — alongside the other access and assets you should hold from day one. If an agency can't produce a keyword map on request, one of two things is true: the work isn't planned, or it's planned somewhere you'll never see. Neither is a good answer, and both are easier to discover in month two than in month fourteen.

For our part, the map is built inside the first ten working days of an engagement and shared as a live sheet the client owns, not as an export. It's the same document behind our keyword research work, and clients keep it whether they stay or leave — which is, admittedly, a slightly uncomfortable commercial position and exactly the point.