Ahrefs SEO Signals for Segmentation

Note for the Planning Advisor (dashboard-planning-advise) and anyone wiring SEO into segment_spine. Read alongside Keyword KB: Structure, Sources & Maintenance.

1. Why there are two keyword corpora — and don’t conflate them

We now hold keyword/SEO data on two different keyed axes. Both matter; they answer different questions.

	KEYWORD_INTELLIGENCE (+ CATEGORY_BENCHMARKS / CATEGORY_TOP_KEYWORDS)	Prospect Ahrefs layer (dim_seo, dim_seo_keywords)
Keyed by	subscriber_id / directory listing id	crawl_root (the prospect’s domain)
What it tells you	Demand — what the market searches in a category, and what FCR clients + the GP directory already rank for	Supply per prospect — what this specific prospect domain ranks for, how strong it is, and where it’s stuck
Volume basis	impressions, cumulative (never call it monthly)	Ahrefs volume = true national monthly search volume (the only source we trust for “searches/month”)
Coverage	28.7M rows, 8 sources. Its own ahrefs_organic/ahrefs_tracked source is client-only and thin (267 subs)	dim_seo ≈ 82K live prospect domains; dim_seo_keywords ≈ 3,485 thin-but-ranking domains
Intent	keyword_intent (commercial/informational/navigational/brand)	is_commercial / is_transactional / is_local per keyword

The new prospect Ahrefs layer is not the same as KI’s ahrefs_organic source. KI’s Ahrefs rows are for existing clients; the prospect layer is keyed by domain and is ~300× larger on the prospect side. Keep them separate; join them by category, not by id.

2. The prospect Ahrefs tables

• fcr_operations.dim_seo — one row per live prospect domain: domain_rating (DR), org_keywords, org_keywords_1_3, org_traffic, org_cost, paid_keywords, refdomains, backlinks. This is the “how strong / how thin” signal. Thin = domain_rating < 10 AND org_traffic < 50.
• fcr_operations.dim_seo_keywords — the deep pull on the thin-but-ranking cut (DR<10, traffic<50, org_keywords>=5): per keyword best_position, volume, cpc, intent flags. These are the “stuck on page 2–3, one push from page 1” keywords — concrete proposal fuel.
• fcr_operations.v_seo_pitch_targets — relevance-gated, commercial-weighted pitch ranking built on the above. A keyword only counts if it shares a real (stemmed, ≥4-char) token with the business’s category/name, so we never pitch a baby shop on “zara”. Columns: bizname, crawl_root, category, dr, traffic, n_rel_striking, rel_comm_vol, head_kw/head_vol/head_pos.
• Queues dim_seo_queue / dim_seo_kw_queue — the self-draining work lists for the two Ahrefs handlers (dashboard-prospect-seo-enrich, dashboard-prospect-seo-keywords). Rebuild both after each deep-crawl pass to sweep newly-reachable / newly-ranking domains.

3. The bridge to KEYWORD_INTELLIGENCE — normalized_category

The link between “this prospect” and “the market” is category:

1. Resolve the prospect’s category → KEYWORD_INTELLIGENCE.normalized_category (same canonicalisation the benchmarks use; map prospect primary_category via CATEGORY_MAPPING → GP → normalized).
2. Pull the category’s commercial demand from CATEGORY_BENCHMARKS / CATEGORY_TOP_KEYWORDS filtered to keyword_intent = 'commercial' — the keyword universe and impression weight that matter for selling.
3. Pull what the prospect already touches from dim_seo_keywords.
4. SEO opportunity = category commercial top-keywords − what the prospect ranks for (or is in striking distance of). Size the prize in Ahrefs monthly volume (dim_seo_keywords.volume), benchmark the prospect against category peers in KI impressions — never mix the two unit systems.

Unit rule: “X searches/month” must come from Ahrefs volume. KI impressions are cumulative — use them for relative category demand and peer comparison, not for a monthly number. (See keyword-kb-structure.md §5.)

4. How the segmentation bot uses these signals now

build_segment already exposes fit: seo and web_state: thin_site. The Ahrefs layer is what makes them true rather than crawl-depth proxies:

• web_state: thin_site / fit: seo should resolve to live site + low DR + ~0 organic traffic (dim_seo). A live-but-strong site is a weak SEO pitch; a live-but-thin one is “you’re invisible in search.”
• Best SEO pitch cohort = v_seo_pitch_targets ordered by rel_comm_vol — relevant, commercial, page-2/3 keywords with real volume.
• Discipline (carry into every answer):
  • Never pitch SEO/e-Store to a no_website business — that’s SitePro first (a site must exist before it can rank).
  • Only surface a keyword as an opportunity if it’s relevant to the business (the v_seo_pitch_targets gate); raw volume ranking pulls in brand/irrelevant terms.
  • Ahrefs = monthly volume; KI = cumulative. GP directory counts ≠ FCR client counts.

5. Future signals (after the deep crawl + next pulls)

• Rebuild queues → extend coverage. Deep crawl adds reachable domains; rebuild dim_seo_queue/dim_seo_kw_queue and re-run the two handlers (well within the monthly Ahrefs unit budget).
• A real fit_seo score on segment_spine. Combine three inputs into one dimension the bot can filter/sort on: thin (dim_seo: DR + org_traffic) × striking-distance gap (dim_seo_keywords vs CATEGORY_TOP_KEYWORDS commercial) × category demand (CATEGORY_BENCHMARKS). High score = “live, thin, low-DR, high local commercial demand, already ranking p2–3” = the hottest SEO prospects.
• Expose a joined view for the spine (e.g. v_prospect_seo_opportunity) keyed by crawl_root/place_id so segment-build can read it without per-row Ahrefs/KI scans (rollup once — see bigquery-and-sync.md §7).
• Optional, gated: organic-competitors for the ranking cohort only (it’s noisy/geography-blind for non-ranking thin sites; for local SMEs prefer GBP competitors via enrich-competitors).
• Tighten the relevance gate with an is_branded flag / entity match (today’s token gate still lets brand mismatches like “Office” through).

6. Gotchas

• crawl_root has www vs non-www duplicates — dedupe with REGEXP_REPLACE(crawl_root, r'^www\.', '') when ranking/joining.
• dim_seo org_traffic = 0 is genuinely “no organic traffic” (the pitch), not missing data.
• Cost discipline: build the joined opportunity view as a clustered rollup, never scan dim_seo_keywords per-row inside segment-build.