Back to Blog
Insights10 min read

How to Build a Multi-Country Keyword Database That Keeps Its Evidence

Build a usable candidate register across countries: import new sources, preserve exact queries and evidence, resolve duplicates, and record decisions before mapping pages.

Start with one record for each exact query in each country-language market. Attach observations as separate records, then record every accept, hold, reject, or replacement decision with its reason. That structure lets an in-house SEO answer a practical question after the next tool export: is this a new opportunity, a second observation of an existing candidate, or a term the team already reviewed and set aside?

This is a candidate register, not the final keyword-to-URL map. It is an editorial workflow design, not a format required by Google. The example below is explicitly fictional and contains no search-volume figures, observed SERPs, or recommended local wording. Use the separate multilingual keyword mapping guide only after a candidate has enough evidence to enter page planning.

1. Define the record before importing a spreadsheet

The candidate key is the combination of country, language, and exact query string. Give that combination a stable candidate_id. A concept_id is only a way to relate candidates that serve a shared business idea; it is not proof that their wording or intent is equivalent. Store country_code and language_code separately. The same English string entered for US and UK research needs two candidate IDs, because each market can have different evidence and a different decision.

  • Same tuple, another source: keep the existing candidate ID and add a new evidence row. Do not delete the second source just because the text matches.
  • Different exact string: create a new candidate ID, even if it is a spelling correction. Mark the old record superseded and link the new ID in the decision log; do not silently replace query_exact.
  • Similar words or scripts: flag for review rather than normalizing them into one term. A deduplication key can help find candidates; it must not erase the original string.
  • Different market: keep separate records even when query_exact is identical. Never reuse a metric or SERP conclusion merely because the spelling matches.

This contract also resolves a common spreadsheet error: a row number is not a durable ID. Sorting, inserting, or removing rows changes its meaning. Assign candidate_id once and use it as the link from observations and decisions. If a candidate was entered under the wrong country, supersede it and create a corrected record instead of rewriting its history.

2. Put source observations in their own file

Each evidence row has an evidence_id and a candidate_id, plus evidence_type, source_ref, checked_at, source_country, source_language, method_or_scope, observed_value, limitations, and reviewer. These are different from the candidate's current_status. A source-language translation, a keyword-tool estimate, a country-set SERP inspection, a Search Console report, and a language review answer different questions. Keep them as separate rows so a later reviewer can see what was actually checked.

  • Tool export: retain the original file or report ID. Record the provider, country scope, metric name, value and check date. If the provider gave no value, leave observed_value blank and state that in limitations; blank is not zero.
  • SERP observation: retain the query, date, device and actual location-setting method. Note page types and uncertainty. An incognito window by itself does not establish the country of the results.
  • Language review: record who checked the exact phrase and whether it is suitable for customer-facing wording. A generated or translated candidate remains a hypothesis until reviewed against market evidence.

If you import Search Console data, save the query, page, country, date range and report reference in method_or_scope or the source export. Google's Performance report documentation distinguishes these dimensions, notes anonymized and truncated queries, and says most page data is attributed to canonical URLs. An absent query row is therefore not evidence that demand is zero. The register indexes a source; it does not replace the original report.

3. Import one source batch without losing earlier work

Keep the raw export unchanged and give the batch a reference before copying values into the register. For each source row, identify the exact query and its country and language. If either market field is unknown, hold the row outside the accepted queue until the scope can be established. The following order keeps a second import from overwriting the first one:

  • Look up the tuple: compare country_code, language_code and query_exact with existing candidates. Use a normalized search key only to surface possible duplicates for human review.
  • Create or reuse an ID: create a candidate with current_status new if the exact tuple is new; otherwise reuse its candidate_id. Do not change an accepted or rejected status merely because a tool exported the term again.
  • Append evidence: create one evidence row for the new source observation, pointing to the raw batch, settings and date. A second source gets a second evidence_id, even if its value matches the first.
  • Queue a decision: move new to checking, or reopen a held or rejected candidate only with a dated reason and reviewer. Status changes belong in the decisions CSV, not in an undocumented cell edit.

Before saving the batch, check that all three ID columns are unique, every evidence and decision candidate_id exists, every evidence_ids reference resolves, and the latest decision matches the candidate's current_status. Use YYYY-MM-DD for observation dates and a time with timezone offset for decisions, so two same-day changes can be ordered. These are data-integrity checks, not search-engine ranking rules. The field guide gives the precise column meanings and a small linked example.

4. Follow a candidate through a deliberately unverified example

Imagine an internal intake note proposes the exact string project management software for both US English and UK English. Use one concept ID, but create candidate K-US-01 and K-GB-01. Neither row says that people in either country search this way. The note is evidence of a proposal, not evidence of search demand or suitable local wording.

  • First intake: attach evidence E-US-01 to K-US-01 with evidence_type source_note and source_ref EXAMPLE_ONLY_INTAKE. Record its limitation: no country metric, SERP check, or language review. K-GB-01 has no country evidence yet.
  • Repeated US source: a second fictional intake note proposes the same US tuple. Append E-US-02 to K-US-01. Do not create K-US-02 just because another source mentions it.
  • Decision: a hypothetical editor records D-US-01, new → held, citing E-US-01 and E-US-02, because target-country validation is missing. K-GB-01 is also held with its own reason; US evidence is not copied into its row.
  • Wording correction later: if the exact US string changes, create a new candidate ID, such as K-US-02. Mark K-US-01 superseded and put K-US-02 in related_candidate_id. Preserve the earlier evidence and decision trail.

Every record and reviewer in this example is fictional; the guide labels them EXAMPLE_ONLY. It supplies no market result. Its useful output is a defensible hold with a specific next check, not a manufactured priority score. In your real register, replace all example references with actual source records and accountable reviewers.

5. Decide when a candidate can move forward

Do not use a fixed volume cutoff as the acceptance rule. Decide whether the exact phrase and page task are supportable in the target market. A volume estimate may help prioritization, but it is an estimate with a provider, scope and date. The decision log records previous_status, new_status, decided_at, reason, evidence_ids, reviewer, and related_candidate_id when one record replaces another.

  • Accept for mapping: the target market is defined, the wording has a named language review, the local task and result-page evidence are documented, the business can serve that task, and a reviewer records the decision. Acceptance is not permission to publish a page.
  • Hold: location, wording, source, page task, or reviewer is missing or contradictory. Name the next check and its owner instead of filling the gap with a guessed metric.
  • Reject: evidence shows the term is off-topic, the offer cannot serve it, or the intended task conflicts with the project scope. Keep the source and rejection reason so future imports do not resurrect it blindly.
  • Supersede: the exact query or market key was wrong. Link the corrected candidate; do not rewrite the old row. A later reversal is another decision event, not an edit to history.

6. Refresh evidence and check the handoff

When a provider refreshes a metric, a new country is added, the offer changes, or a reviewer challenges a phrase, append a new observation and decide whether the status still holds. Keep the old value with its date. Compare figures only when their providers, market scopes and methods are sufficiently aligned; a change in source settings is not automatically a change in demand. Reopen a held or rejected candidate by moving it to checking with a new reason and reviewer, rather than using reopened as a permanent status.

  • Can another reviewer locate the raw source for each observation and reproduce its country, language and date scope?
  • Can the team see why a duplicate import reused an ID, why a changed string received a new one, and which old record it superseded?
  • Does every current_status agree with the latest decision, with no orphan evidence ID or decision ID?
  • Does every accepted candidate have a named reviewer and a page task, while held records name the missing check?

Send accepted candidates, their evidence IDs and country-language scope to the keyword-to-URL mapping process. That later step can group candidates by task and choose an existing or proposed owner page; one candidate row does not require one URL. If genuinely equivalent localized pages are published, follow Google's localized-version guidance for their technical relationship. Keep the register as the record of why the content decision was made.