KEYWORD_INTELLIGENCE Improvements — Agent & Skill Update Guide

What Changed

Four new columns were added to KEYWORD_INTELLIGENCE and are now live across all 28.7M rows:

CATEGORY_BENCHMARKS and CATEGORY_TOP_KEYWORDS were rebuilt with new fields: total_fcr_subscribers, commercial_keywords, commercial_impressions, informational_keywords, informational_impressions, brand_keywords, navigational_keywords, appearing_in_fcr_subs, and keyword_intent per keyword.

The four worker handlers (keyword-intelligence.js, category-keywords.js, keyword-gap.js, prospect-intel.js) have already been updated to use the new columns.

Handler API Changes

/dashboard-keyword-intelligence

New fields in response:

• intentStats — array of { intent, keywords, totalImpressions, totalClicks } (one per intent type)
• sourceStats[].sourceType — e.g. "paid", "owned_organic"
• topKeywords[].intent — intent classification per keyword
• Benchmarks now include total_fcr_subscribers, commercial_keywords, commercial_impressions, etc.
• Keyword gaps now include keyword_intent and appearing_in_fcr_subs

/dashboard-category-keywords

New fields in response:

• topKeywords[].intent — intent per keyword
• topKeywords[].fcrSubscribers — FCR client count (vs total subscriber count)
• sourceBreakdown[].sourceType — channel tier
• sourceBreakdown[].fcrSubscribers — FCR clients per source
• subscriberKeywords[].intent — intent per subscriber keyword
• peerStats.fcrSubscribers — FCR client count in the category

/dashboard-prospect-intel

Changed: The Google Ads commercial terms query now uses keyword_intent = 'commercial' instead of the per-query subscriber concentration filter. Simpler, faster, and pre-computed.

/dashboard-keyword-gap

Changed: Category resolution uses normalized_category instead of raw category. More accurate matching.

Skill Updates Required

1. /prospect (SKILL.md)

Current behaviour: Takes top 10 keywords by impressions from categoryKeywords.topKeywords without filtering by intent.

Update needed: Filter for commercial intent keywords when selecting the 5 LRC keywords.

Changes to .claude/skills/prospect/SKILL.md:

In Step 2: Keywords & Locations, section c) Prospect Intel, update:

FROM:
  Take top 10 keywords by impressions from `categoryKeywords.topKeywords`.

TO:
  Take top 10 keywords by impressions from `categoryKeywords.topKeywords` where
  `intent` is `commercial`. If fewer than 5 commercial keywords are available,
  supplement with `informational` keywords that contain the category name
  (e.g. "how much does a plumber cost" is informational but still relevant).
  
  Never use `navigational` keywords (rip.ie, goldenpages, etc.) for LRC.

In Keyword selection strategy, update:

FROM:
  If business has a website:
  - 3 keywords they're already showing for (from site/GBP)
  - 2 high-value gap keywords (from category benchmarks)

TO:
  If business has a website:
  - 3 keywords they're already showing for (from site/GBP — prefer commercial intent)
  - 2 high-value gap keywords from CATEGORY_TOP_KEYWORDS where intent = 'commercial'
  
  If no website:
  - 3 top commercial category keywords (intent = 'commercial', highest impressions)
  - 2 specific service keywords (commercial, lower competition)
  
  Always exclude: navigational keywords, brand keywords (competitor names),
  informational-only keywords (death notices, how-to guides).

In Step 2b: Search Volume & Client Benchmarks, add:

When presenting keyword data, always note the intent breakdown:
- "Of the {X} keywords tracked for {category}, {Y} are commercial 
  ({Z}M impressions) and {W} are informational ({V}M impressions). 
  We're focusing on the commercial terms."

2. /keyword-gaps (SKILL.md)

Current behaviour: Shows all keywords regardless of intent.

Update needed: Group keywords by intent in the display.

Changes to .claude/skills/keyword-gaps/SKILL.md:

Update step 3 display:

FROM:
  - **Top keywords** (`topKeywords`): keyword, impressions, clicks, CTR, source

TO:
  - **Top keywords** (`topKeywords`): keyword, impressions, clicks, CTR, source, **intent**
    Group by intent: show commercial keywords first, then informational.
    Flag navigational/brand keywords separately — these are not actionable.
  
  - **Intent breakdown** (`intentStats`): show keyword count and impressions per intent.
    "Commercial: {X} keywords ({Y} impressions) | Informational: {X} ({Y}) | Brand: {X}"

Update step 4 display:

FROM:
  - **Gap keywords** (`keywords`): keywords missing, ranked by impressions

TO:
  - **Gap keywords** (`keywords`): keywords missing, ranked by impressions
    Highlight `keyword_intent` for each gap keyword.
    Lead with commercial gaps — these are the ones worth acting on.
    Note: "Gap keywords with `intent = 'informational'` (e.g. 'how much does X cost')
    may be content opportunities rather than ranking opportunities."

Update Notes section:

FROM:
  - Category matching uses fuzzy anchor-word matching

TO:
  - Category matching uses normalized_category (exact match after monthly normalisation)
  - Keywords now include intent classification: commercial, informational, navigational, brand
  - Peer stats now show FCR client count vs total subscriber count

3. /category-keywords (SKILL.md)

Current behaviour: Shows source breakdown with raw subscriber counts. No intent information.

Update needed: Add intent breakdown, show FCR client counts.

Changes to .claude/skills/category-keywords/SKILL.md:

Update step 6 Category Overview display:

FROM:
  - Total unique keywords, total impressions, subscriber count
  - Source breakdown: GMB, Google Ads, GSC organic, GP directory

TO:
  - Total unique keywords, total impressions, **FCR client count** (from fcrSubscribers)
  - Source breakdown with sourceType grouping and FCR counts:
    "Paid (Google Ads): {X} FCR clients, {Y} keywords | 
     Organic (GSC): {X} FCR clients, {Y} keywords | 
     GBP: {X} FCR clients, {Y} keywords |
     Directory (GP): {X} total listings (not FCR clients)"
  - **Intent breakdown**: 
    "Commercial: {X} keywords ({Y}M impressions) | 
     Informational: {Z} keywords ({W}M impressions)"

Update Top 20 Keywords table:

FROM:
  | Keyword | Impressions | Clicks | CTR | Avg CPC | Subscribers |

TO:
  | Keyword | Intent | Impressions | Clicks | CTR | Avg CPC | FCR Clients |
  
  Sort commercial keywords first. Mark informational keywords with (i) flag.
  Note: "Subscriber count now shows FCR clients only. Directory listings excluded."

Ro.am AI Advisor (Haiku) Updates

The Ro.am AI Advisor uses a bq_query tool that can run arbitrary SQL against BigQuery. It doesn't use the worker handlers directly, so it needs updated system prompt instructions to leverage the new columns.

System Prompt Updates

Add to the Ro.am system message (in the n8n AI Advisor workflow):

## KEYWORD_INTELLIGENCE — New Columns (March 2026)

The KEYWORD_INTELLIGENCE table now has 4 new columns you should use:

1. **normalized_category** — Use this instead of the raw `category` column for all queries.
   Example: WHERE normalized_category = 'Funeral Director' (not LIKE '%funeral%')

2. **keyword_intent** — Filter by intent to avoid noise:
   - 'commercial' — keywords with purchase intent (near me, cost, locality + service)
   - 'informational' — educational queries (how to, what is, death notices)
   - 'navigational' — platform-specific (rip.ie, goldenpages)
   - 'brand' — business name searches
   
   For sales/prospect conversations, always filter: WHERE keyword_intent = 'commercial'
   For content strategy, include informational keywords too.

3. **is_fcr_client** — TRUE = active FCR subscriber, FALSE = directory listing or non-client.
   Use this for accurate peer counts: COUNT(DISTINCT CASE WHEN is_fcr_client THEN subscriber_id END)
   
   IMPORTANT: The old subscriber counts included GP directory listings (e.g. 418 "subscribers" 
   for funeral directors). The real FCR client count is ~95. Always use is_fcr_client for peer stats.

4. **source_type** — Groups sources: 'paid', 'owned_organic', 'owned_gbp', 'directory', 'third_party'
   Use for channel-level reporting instead of listing individual sources.

### Updated Query Patterns

Category keyword query (OLD):
  WHERE LOWER(category) LIKE LOWER('%electrician%') AND LENGTH(category) < 60

Category keyword query (NEW):
  WHERE normalized_category = 'Electrician'

Commercial keywords only (NEW):
  WHERE normalized_category = 'Electrician' AND keyword_intent = 'commercial'

FCR peer count (NEW):
  COUNT(DISTINCT CASE WHEN is_fcr_client THEN subscriber_id END) as fcr_clients

### CATEGORY_BENCHMARKS — New Fields

The CATEGORY_BENCHMARKS table now includes:
- total_fcr_subscribers — FCR clients in this category
- commercial_keywords / commercial_impressions — commercial intent only
- informational_keywords / informational_impressions — informational intent only
- *_fcr_subscribers — FCR client counts per source (gmb_fcr_subscribers, ads_fcr_subscribers, etc.)

### CATEGORY_TOP_KEYWORDS — New Fields

- keyword_intent — per keyword
- appearing_in_fcr_subs — FCR clients ranking for this keyword

Tool Usage Updates

When the Ro.am advisor selects keywords for LRC or DF submission via submit_insites_lrc / submit_insites_df, update the keyword selection logic:

BEFORE: Select top keywords by impressions from KEYWORD_INTELLIGENCE
AFTER:  Select top keywords by impressions WHERE keyword_intent = 'commercial'
        Exclude navigational and brand keywords from LRC submissions.

Worker Deployment

The handler code changes (keyword-intelligence.js, category-keywords.js, keyword-gap.js, prospect-intel.js) need to be deployed to the Cloudflare Worker before the new API response fields are available.

cd worker && npx wrangler deploy

Backwards compatibility: The new fields (intent, fcrSubscribers, sourceType, intentStats) are additive — existing consumers that don't read them won't break. The only breaking change is that prospect-intel.js now uses normalized_category in its SQL, but the new column is already populated, so this works immediately.

Summary of Actions

FCR Dashboard documentation · generated from docs/ · keep counts verified, not guessed.