The tool exports more than three thousand rows and the team stares at the spreadsheet. Sales terms, job titles, competitor brands, and a pile of languages nobody on the team reads, all mixed together. Someone suggests sorting by volume and cutting half. Someone else groups by first letter, which scatters one idea across a dozen letters.
Keyword research is core SEO work, and the overall framework sits in the SEO starter guide. The first half is already covered on this site. Where seed terms come from, which expansion channels to use, how to classify intent, and what the seven-column table looks like are all in the keyword research and search intent guide. This article picks up the second half, turning three thousand rows into two hundred usable entries, with cleaning rules, merge rules, template fields, handoff, and a review cadence.
Build the Collection Table First
Messy cleaning usually starts with messy collection. The collection table does one job, capture the source as it is, with no judgment.
Five columns are enough, raw term, source, market and language, collection date, and notes. One raw term per row. Do not add volume, difficulty, or priority at this stage, and do not merge. Record the source clearly, whether it came from Keyword Planner, Search Console queries, search suggestions, a competitor page, or the ads search terms report. Source matters when judging reliability later.
If the same term arrives from three sources, keep three rows and let the source column distinguish them. Decide which row to keep during cleaning. Deduplicating at collection throws away signals such as "this term already has clicks in Search Console."
Tool exports usually include headers, blank rows, URL columns, and duplicates. Convert everything into the five columns first, then start cleaning. Keep market and language in their own column instead of guessing from characters. In cross-border projects, English lists often contain Spanish or Chinese terms, and a quick human scan beats a rule.
Seed sources and expansion channels are not repeated here. The parent guide has the full list. The collection table only captures sources, and the dumber it is, the better.
Cleaning Starts with Types of Dirty Terms
Cleaning is more than removing duplicates. Dirty terms have different causes, and each needs a different response. The table below works as a pass-by-pass checklist.
| Type | Example | Action |
|---|---|---|
| Export noise | Blank rows, repeated headers, URL fragments, HTML entities | Delete |
| Duplicate rows | Same term from three sources | Merge to one row, keep the source list |
| Spelling and plurals | keyword research tool / tools / tool | Normalize the spelling, then judge by intent and page |
| Word order variants | keyword research for beginners / beginner keyword research | Same as above, do not merge on string similarity |
| Mixed languages | Spanish or Chinese terms in an English list | Move to the right market list, do not delete outright |
| Same form, different meaning | apple, string, prime | Move brand-ambiguous terms out of the non-brand list |
| Pure navigational | login, official site, download | Remove from the content list, build a separate ads list |
| Irrelevant high volume | free movies, jobs, torrent | Delete |
| Too narrow or no page | Specific years, streets, discontinued products | Downgrade to the long-tail or FAQ pool |
Normalize only mechanical differences. Lowercase everything, convert full-width characters, trim extra spaces and punctuation, and keep brand names in their official form. Do not stem words down to a root at this stage. English plurals and gerunds often map to different intents, and over-stemming drags commercial terms into informational groups.
Be careful with mixed languages. A Spanish term in an English export may be an artifact of the tool's location settings, or it may be a market you actually want. Check the market column before moving it, and do not delete a term just because nobody on the team reads the language.
Handle brand-ambiguous terms separately. Words like apple, string, and prime carry both brand and ordinary meanings. Remove them from the non-brand list and keep a brand list for paid media and PR. Leaving them in the content list produces articles nobody needs.
Pure navigational terms can leave the content list without losing value. Someone searching for a brand plus login is already a customer. That term belongs in brand protection and support pages, not in a content calendar.
Irrelevant high-volume terms are the most tempting. The volume is large, but the term has nothing to do with the business and will not produce leads. A practical test is whether the person searching would buy what you sell. If not, delete it. Volume and market opportunity are different questions, and high search volume does not mean market entry lays out the full test.
Terms to Keep but Downgrade
Some terms should not be deleted but do not deserve a page yet. Terms with a clear long-tail intent, modest volume, and no ready page go into a long-tail pool and are marked as downgraded. Once the main page exists, decide whether they belong in an FAQ, a section, or a standalone article.
Downgrade does not mean discard. The term must remain searchable in the library, with a status of long-tail pool and a note about which page it hangs under. Review in three months and promote it if impressions appear.
Merge on Intent and Destination Page, Nothing Else
This is the central rule of the article. Do not merge by string similarity.
String similarity puts keyword research and keyword research jobs in the same bucket, when one wants to learn a method and the other wants a job. It also merges seo keyword tool with seo keyword strategy, when one needs a comparison page and the other needs a guide.
Assign two keys to every term, intent and destination page.
Four intent labels are enough, informational, commercial research, transactional, and navigational. The method for judging intent and reading SERPs belongs to [the intent and SERP guide] (backfill after publication). Here, intent is only a grouping label.
The destination page is a candidate URL. Write "to be decided" when unsure. A term has one primary destination and may have secondary placements, such as a section within an article.
Terms that share both keys form a group. Four cases appear after grouping.
| Case | Action |
|---|---|
| Same intent, same page | Merge into one group, choose one primary term, keep the rest as secondary |
| Same intent, different pages | Decide the canonical page first, so two pages do not chase one intent |
| Same page, different intents | Split into sections, or build a separate page |
| Different intent, different page | Keep separate, no merge |
An example.
| Keyword | Intent | Destination | Action |
|---|---|---|---|
| keyword research process | Informational | /keyword-research-guide | Primary |
| how to do keyword research | Informational | /keyword-research-guide | Secondary |
| keyword research tools | Commercial research | /keyword-tools | Primary |
| best keyword research tool | Commercial research | /keyword-tools | Secondary |
| keyword research template | Informational | /keyword-template | Primary, to be built |
| keyword research jobs | Navigational | None | Delete |
The first two rows differ in wording but share intent and page, so they form one group. Rows three and four form another group with a tools comparison page; they cannot merge into the guide. Row five needs its own template page and is marked to be built. Row six is irrelevant and goes.
Choose one primary term per group. Three inputs matter, how closely the term matches the page topic, whether it expresses the page's core intent, and whether the page already earns impressions for it. Volume is only a reference. A broad term with high volume should not demote a precise term.
One primary term per page. The primary goes into the title, H1, and opening paragraph. Secondary terms go into H2s, FAQs, image alt text, and internal link anchors. More than one primary term blurs the page and the title.
Do not force terms with different intents onto one page to make the grouping look tidy. A user looking for a tool comparison who lands on a methods article will leave immediately. The goal is fewer duplicate pages, not fewer terms.
Template and Handoff
A cleaned keyword library should be a table someone else can pick up, not a personal bookmark folder. Twelve fields, in three blocks, the term, the destination and status, and the evaluation and source.
| Field | Definition | Required |
|---|---|---|
| keyword | Normalized term | Yes |
| market_language | Market and language, such as en-US or de-DE | Yes |
| intent | Informational / commercial research / transactional / navigational | Yes |
| cluster | Group name after merging | Yes |
| target_url | Destination URL, or "to be built" | Yes |
| role | Primary / secondary / long-tail pool | Yes |
| status | To build / built / observing / retired | Yes |
| volume | Search volume, with source and date | No |
| difficulty | Difficulty, with tool name | No |
| source | Source, multiple values allowed | Yes |
| owner | Owner | Yes |
| last_reviewed | Last review date | Yes |
Put validation in the table, not in people's memory. The keyword and market_language pair is unique. Intent only takes four values. When role is primary, only one primary term may exist per target_url. When status is "to build," target_url must say "to be built" rather than stay empty. When status is "built," target_url must be a working path.
Write the status transitions down so the table never shows a built page that does not exist or a retired term still in the schedule.
To build means the term is confirmed and the page is not written. If a quarter passes with no movement, re-evaluate instead of letting it sit forever.
Built means the page is published and reachable. Replace target_url with the real path and fix the role.
Observing means the page has been live for four to eight weeks and data is accumulating. Do not change the primary term during observation, or the read is contaminated.
Retired means the page was merged, the intent was wrong, or the business no longer cares. Keep the row and the reason so future teams know why it was dropped.
When handing off to writers, do not send a spreadsheet alone. Add a brief with the primary term, secondary terms, intent, destination page, three to five FAQ questions from the long-tail pool, and the pages to link internally. The writer needs a task, not a data dump.
When handing off to paid media, open a separate tab for brand terms, competitor terms, navigational terms, and negatives. SEO and SEM libraries use different fields, and mixing them causes misapplication.
For product and content planning, two columns are enough, cluster and status, to show which topics are covered and which are open. The bridge from keyword library to content planning is covered in the AI-era content strategy piece.
Review Cadence
A keyword library is not a one-time project. Close it in a drawer and it expires within a quarter.
Do a light weekly pass, twenty to thirty minutes. Check new queries in Search Console, new terms in the ads search terms report, and internal site search. Add new terms to the collection table and resist merging them on the spot. Flag obviously irrelevant terms for the monthly clean.
Do a monthly clean and status update. Merge new variants, process the flags from the week, check terms that have been waiting more than a quarter, and update pages in the observation stage. Also check whether a new page has taken over a primary term.
Do a quarterly structural review. Retire pages with no impressions, merge pages with overlapping intent, and check whether a term's intent has shifted, for example from informational to transactional. Record the reason for every retirement so the next round is faster.
Review two things, impressions and clicks. Metric definitions and ROI framing belong to the data and tools guide. The table here is only for decisions.
| Signal | Check first | Possible action |
|---|---|---|
| Built eight weeks, near-zero impressions | Indexation, title, internal links | Fix indexing and title; merge or retire if still zero after four weeks |
| Impressions rising, CTR well below similar pages | Whether title and snippet match intent | Rewrite title and description, keep the primary term |
| Ranking falls for two consecutive periods | Competitor pages, freshness, internal links | Refresh the content or merge into a stronger page |
| A new term grows fast and no page covers it | Intent and destination fit | Add a section or build a page |
| Several primary terms compete for one page | Whether the library allows only one primary | Split the page or set clear priorities |
Do not copy industry benchmark percentages. Site size, category, and market vary too much. Compare against similar pages on your own site instead of memorizing a number. A young site often sees nothing in eight weeks, while a mature site should investigate after four.
Common Misconceptions
Merging by string similarity. Similar spelling does not mean the same intent, and one bad merge blurs two pages.
Sorting only by volume. Broad high-volume terms are hard to satisfy, and precise long-tail terms are where new sites win.
Deduplicating during collection. Losing the source makes it impossible to tell which term already has Search Console data.
A library without destination pages. Terms with no page owner cannot be executed by writers or paid media.
Handing off a spreadsheet only. Without a brief, the writer guesses at priority and structure.
Building once and never reviewing. The library needs weekly intake, monthly cleaning, and quarterly review.
Related Reading
- Keyword research and search intent, the upstream guide to seeds, expansion channels, and intent.
- Page titles, structure, and internal links, how the primary term lands in the title, H1, and links.
- SEO data and tools, definitions and reporting for the review metrics.
- SEO engineering, page templates and structured data implementation.