Keyword Data You Can Trust: A Practical Pipeline from Raw Export to Build List
How to take a 40,000-row keyword file, strip the noise, normalise it across sources, and produce a defensible prioritised build order - without treating any export as ground truth.

What 'Good' Keyword Data Actually Looks Like (and Why No Tool Has the True Number)
A 40,000-row keyword export feels authoritative. It is not. Every source - Google Keyword Planner, Search Console, Ahrefs, Semrush, DataForSEO - is a model, a sample, or a bucket. Keyword Planner returns volume ranges (e.g. 1K-10K) unless you have active spend. Search Console only shows queries your site already ranks for, with privacy thresholds hiding the low-volume tail. Third-party tools estimate volume with their own models; ±30-50% variance on the same query is normal. Good keyword data is therefore not a single truth but a versioned, labelled, reconciled file you can defend in a meeting. This guide walks the pipeline from raw export to a 90-day build slate.
Prerequisites: The Four Things You Need Before You Touch a Keyword File
- A defined business outcome - qualified enquiries for lead-gen, add-to-cart for ecommerce. Volume without outcome is noise.
- First-party query data - Google Search Console (API) is the only first-party query source; export 16 months if available.
- A seed list - product categories, service pages, top competitor domains. A South African homeware retailer with 12 categories gets 12 category seeds plus material, room, and price modifiers.
- A naming convention - a
source_topic_YYYYMMDD.csvpattern prevents version chaos.
Search Console's Search Analytics API returns up to 25,000 rows per request and retains 16 months of data with a 2-3 day lag. Plan for pagination and store the pull date.
Where Keyword Data Comes From - and What Each Source Silently Omits
| Source | What it gives you | What it hides |
|---|---|---|
| Google Keyword Planner | Bucketed volume ranges, related ideas | Exact volume unless you have active spend; no intent |
| Search Console (API) | First-party queries your site ranks for | Queries you don't rank for; low-volume tail due to privacy thresholds |
| Third-party tools (Ahrefs, Semrush, DataForSEO) | Estimated volume, difficulty, SERP features | Model assumptions and refresh cycles; ±30-50% variance |
Step 1: Date-Stamp, Label and Version Every Export You Pull
Before cleaning, create a manifest row per file: source tool, export date, locale (en-ZA, en-GB, en-US), filters applied, row count. This is your audit trail - without it you cannot explain why last month's build list differs from this month's.
Step 2: Deduplicate by Cluster, Not by Row
Raw exports contain near-duplicates: "outdoor sofa", "outdoor sofas", "outdoor couch", "patio sofa". Treat these as one cluster. Use stemming and lemmatisation to group plurals, then a similarity threshold (Jaccard or embedding-based) to catch synonyms. A 40,000-row file typically collapses to roughly 1,200 clusters.
Summing volumes across close variants double-counts demand. Apply an exact-query filter in Search Console before summing - or dedupe first.
Step 3: Normalise Text and Separate Brand from Non-Brand
Lowercase, strip diacritics, handle stop-words consistently, and decide on exact vs phrase vs broad matching. Tag any query containing your brand or a competitor brand as brand, and exclude brand from opportunity scoring - brand demand is real but not addressable with new landing pages.
Step 4: Tag Intent Before You Judge Volume
Assign one of four intents: informational, commercial (comparison, reviews), transactional (buy, price, near me), navigational. A 200-volume "outdoor sofa clearance" beats a 2,000-volume "what is a patio" for a retailer. Intent tags come from keyword patterns plus a manual sample audit of 200 rows.
Step 5: Reconcile Volume Across Sources Instead of Averaging Blindly
Don't average Keyword Planner buckets with third-party estimates. Record each source's value and a confidence weight (first-party Search Console impressions = high; Planner bucket midpoint = medium; third-party estimate = low). Use the weighted value in scoring and flag any cluster where sources disagree by more than 50% for manual review.

