Keyword KB: Normalisation Pipeline (SQL)

Overview

There are two stages each month: (0) the load — pull the new month’s rows from the raw Google feeds into KEYWORD_INTELLIGENCE; then the cleaning — these SQL scripts add the derived columns + rebuild the benchmarks. Without the load, the cleaning is a no-op (it only processes rows already in the table).

Step 0 — The data load (raw → KI)

data/keyword-intelligence/load_from_raw.py rebuilds KI rows from the raw monthly feeds (reverse-engineered + validated 2026-06-14; the original was a lost BQ-console query). Run via bq-admin / console (heavy). Per source:

Source	Raw table	Key transform
gsc_organic + directory gsc_*	GOOGLE_ACCOUNT_DATA.GSC_REPORTS_YYYYMM01	keyword = dimension1='query' value (all dimension2 breakdowns summed); subscriber via GoogleAccount_Subscr_MAP_GSearchConsole_native; source split by siteUrl
gmb_search	GOOGLE_ACCOUNT_DATA.GMB_KEYWORDS_YYYYMM01	searchKeyword; impressions = COALESCE(insightsValue.value, threshold); subscriber via mv_subscriber_service_map (GBP location_id)
google_ads	GOOGLE_ADS_TRANSFER_EU.ads_SearchQueryStats_*	search_term_view_search_term; subscriber via GoogleAccount_Subscr_MAP_GAds_native; month = segments_date month

Gotchas the loader handles:

• Shard naming is off by one — GSC_REPORTS_YYYYMM01 / GMB_KEYWORDS_YYYYMM01 are named by extraction date and hold the prior month’s data (KI month = the data month). Ads uses segments_date directly.
• GSC impressions are ~4× inflated (the loader sums across query×page×device×country, matching KI history). Ranking is unaffected (uniform); absolute “X searches” headlines must use property-level totals (one canonical sc-domain property), e.g. owned directories ≈ 710M; SME organic ≈ 193M (not the 908M keyword-sum).
• gsc_getlocal is parked — its history is category-attributed from the /browse-category/ page URL, so a query-only load is wrong; needs the GetLocal Keyword Leverage work. ahrefs_* is a separate non-monthly feed.

Cleaning columns

These SQL scripts add the derived columns and rebuild the benchmark tables. Run monthly after Step 0.

Column	Type	Description
normalized_category	STRING	The subscriber’s primary_category (2026-06-13 principle; supersedes the CATEGORY_NORMALISATION lookup)
keyword_intent	STRING	commercial, informational, navigational, or brand
is_fcr_client	BOOL	TRUE if subscriber_id is an active FCR client
source_type	STRING	paid, owned_organic, owned_gbp, directory, third_party
keyword_location	STRING	Irish city/county extracted from the keyword, or NULL

Execution Order

Run in BQ console in this order, monthly after the keyword-level data load (the GSC/GA/GMB/Ahrefs ingest that inserts the new months’ rows — upstream, not in this repo). Easiest: run 00_run_all.sql as one script (already uses subscriber-primary categorisation + step 06). Standalone files, in order:

1. ALTER TABLE — add columns (one-time only, first run)
2. 01b_subscriber_primary_category.sql — normalized_category = subscriber primary_category
   (SUPERSEDES 01_category_normalisation.sql — see Categorisation note below)
3. 02_keyword_intent.sql — classify keyword_intent on new rows
4. 06_location_backfill.sql — fill city/county (blanks) from subscriber location — BEFORE 02b
5. 02b_brand_and_location.sql — brand detection + keyword_location + is_category_brand
6. 03_fcr_client_and_source_type.sql — set is_fcr_client and source_type on new rows
7. 04_monthly_view.sql — create/replace the KEYWORD_INTELLIGENCE_MONTHLY view
8. 05_rebuild_benchmarks.sql — rebuild CATEGORY_BENCHMARKS and CATEGORY_TOP_KEYWORDS

Categorisation note (2026-06-13 principle, wired 2026-06-14)

normalized_category = the subscriber’s primary_category (listing-grain; exact for 96.4% single-category subs), NOT the scraped per-source category. The legacy 01_category_normalisation.sql lookup-backfill is superseded; CATEGORY_NORMALISATION is kept only as a raw-category reference. Follow-up to close the 3.6% multi-category gap: add listing_id to KI (GP-JSON backfill) and join at listing grain. See the 01b_*.sql header.

One-Time Setup (First Run)

Before running the scripts for the first time, add the columns:

ALTER TABLE `listingmanager-1529856313699.fcr_operations.KEYWORD_INTELLIGENCE`
  ADD COLUMN IF NOT EXISTS normalized_category STRING,
  ADD COLUMN IF NOT EXISTS keyword_intent STRING,
  ADD COLUMN IF NOT EXISTS is_fcr_client BOOL,
  ADD COLUMN IF NOT EXISTS source_type STRING,
  ADD COLUMN IF NOT EXISTS keyword_location STRING;

Then run all scripts. The first run will backfill all existing rows. Subsequent monthly runs only process new/unclassified rows (WHERE column IS NULL).

What Each Script Does

01 — Category Normalisation

Creates CATEGORY_NORMALISATION lookup table. Strips eircodes, business names, normalises case, merges synonyms (Funeral Directors → Funeral Director etc.). Updates normalized_category on KEYWORD_INTELLIGENCE.

02 — Keyword Intent (Initial Pass)

Classifies keywords by intent using rules:

• navigational: rip.ie, goldenpages, google maps, yelp, tripadvisor
• brand: business name match (full name LIKE) or >85% subscriber click concentration
• commercial: “near me”, cost/price patterns, locality + service, paid clicks, GBP discovery
• informational: how-to/what-is patterns, death notices, default fallback

02b — Brand Refinement + Location Extraction

Two-phase refinement run AFTER step 02:

Phase 1 — Improved brand detection: Uses subscriber click concentration (>70%, <=3 subs) with commercial pattern exclusion. Catches keywords like “frank buttimer solicitor cork” and “valentine ladders” that step 02’s business name matching misses (because the keyword contains only part of the business name). Reclassifies ~4M rows from commercial/informational to brand.

Phase 2 — keyword_location extraction: Matches keywords against 730+ Irish cities and counties (from active_clients). Longest match wins — “electrician naas kildare” → “Kildare”. Tags ~6M rows. NULL means no location in the keyword (e.g. “solicitor near me”).

03 — FCR Client + Source Type

Flags FCR clients (JOIN against active_clients snapshot) and maps sources to tiers:

• paid = google_ads
• owned_organic = gsc_organic
• owned_gbp = gmb_search
• directory = gsc_goldenpages
• third_party = everything else

04 — Monthly View

Creates a pre-aggregated monthly view for fast category-level queries.

05 — Rebuild Benchmarks

Rebuilds CATEGORY_BENCHMARKS and CATEGORY_TOP_KEYWORDS from normalised data, now including:

• total_fcr_subscribers — real FCR client count (not inflated GP directory counts)
• commercial_keywords / commercial_impressions — commercial intent only
• informational_keywords / informational_impressions
• brand_keywords / navigational_keywords
• keyword_intent per keyword in CATEGORY_TOP_KEYWORDS
• appearing_in_fcr_subs — FCR clients ranking for each keyword

Known Issues

Category Mapping Mismatch

Three naming systems exist:

• GP: “Dental Surgeons”, “Funeral Directors”, “Solicitors”
• Google GBP: “Dental Clinic”, “Funeral Director”, “Legal Services”
• KI normalized: “Dentist”, “Funeral Director”, “Law Firm”

CATEGORY_MAPPING bridges GP↔Google but normalized_category is a third set of names. The worker handlers now match against BOTH gp_category_name AND google_category_name from CATEGORY_MAPPING, plus the raw category column, to ensure results are found regardless of which naming system applies. Long-term fix: align step 01 normalisation to use CATEGORY_MAPPING as the reference.

Keyword Planner CPC

Google returns zero bid estimates for keywords with too few advertisers in a location. The worker handler returns estimated_cpc_eur: null in these cases — the advisor should omit CPC rather than show EUR0.00.

Maintaining the Synonym List

The synonym mapping in 01_category_normalisation.sql covers the most common duplicates. To add new synonyms:

1. Query for the raw category values you want to merge
2. Add a new WHEN clause to the synonym CASE block
3. Re-run step 01 — the MERGE will update existing mappings

Maintaining the Brand Detection

The brand detection in 02b_brand_and_location.sql uses subscriber click concentration. If you find false positives (generic keywords misclassified as brand), increase the concentration threshold (currently 0.70) or add patterns to the commercial exclusion regex. If you find false negatives (brand keywords still showing as commercial), lower the threshold or increase the subscriber count limit (currently <=3).

Maintaining the Location List

Location names come from active_clients cities and counties. As new clients are added in new towns, the location list grows automatically. Excluded short/ambiguous names (new, old, cross, bridge, hill, park, etc.) are hardcoded in 02b_brand_and_location.sql — add to this list if new false positives appear.