Scoring SEO Opportunities: How AI Prioritizes What to Work on Next

This walkthrough gives you a reproducible, automated pipeline that turns crawler exports + Google Search Console + competitor keyword lists into prioritized content work items using the Opportunities + Competitors feature in Semantic.io. The output is a ranked backlog of actions — refresh, create, consolidate, or canonicalize — with a numeric priority score, required effort estimate, and expected traffic lift. You’ll get the exact inputs to ingest, the joins and transforms to run, scoring logic to implement, and operational guardrails (cadence, permissions, and governance) that let engineering and content teams turn recommendations into deployed pages.

Why combine crawler data, Google Search Console, and competitor keywords? (what each source contributes and the typical gaps they reveal) Three data sources, three roles. Combine them and you reduce false positives and prioritize work that actually moves business metrics.

• Crawl (content inventory & on‑page signals)
  • What it gives: a canonicalized content inventory, meta/title/H1 text, word count, structured data, internal link counts, status codes, indexation signals, and on-page intent proxies (headers, schema, headings distribution). Crawler exports form the authoritative “what we have” dataset. Use a modern crawler (DeepCrawl, Screaming Frog in batch mode, or an enterprise crawler) and export canonical URL, status, meta title, meta description, H1, H2s, word count, content word vector / topic tags (if your crawler / NLP layer provides them), and internal linking counts.
  • Why valuable: the crawl tells you whether a topic is missing, thin, cannibalized, or mis-targeted before you even look at query performance.
• Google Search Console (GSC — real query performance)
  • What it gives: actual queries that drive impressions and clicks, impressions, clicks, CTR, average position (by date range and page), and search appearance features. This is real user demand — not modeled. Pull GSC with the API so you can join at scale. Google Search Console API for performance data
  • Why valuable: GSC shows where you already have presence (impressions or low CTR) and where there’s opportunity to move the needle (high impressions with low CTR or position 6–20 impressions).
• Competitor keywords (external keyword datasets)
  • What it gives: the keywords your competitors rank for, their estimated positions, and keyword difficulty or CPC proxies from platforms like Ahrefs, Semrush, or your own SERP scrape. This is a directional “what they own” dataset. Use at least two independent competitor sources to reduce tool-specific blindspots (Ahrefs Content Gap, Semrush Keyword Gap). Ahrefs Content Gap analysis explained
  • Why valuable: competitor keywords expose queries driving traffic to others that you haven’t targeted or optimized for. They also reveal top-ranking content formats and subtopics you’re missing.

• Crawl-first gaps (coverage & structure)
  • Missing pages for known product/feature topics; thin pages (< 300 words) where GSC shows impressions; meta/title mismatches; canonicalization or pagination leaks.
• GSC-first gaps (demand vs. ownership)
  • Queries with impressions but low CTR or low position — ideal for refresh or UX/CTR optimization. High-impression queries with average position 6–20 are classic “low-hanging” wins.
• Competitor-first gaps (owned queries you don’t have)
  • Competitor-owned keywords with demonstrable traffic that you neither rank for nor target — candidates for new pages, content clusters, or feature pages.

This section lists the exact columns and fields you need to ingest, plus hygiene and access requirements.

• Crawl export (CSV/Parquet)
  • canonical_url (string) — required (use crawler’s canonical detection rules).
  • content_id (internal UUID) — optional but recommended.
  • status_code, indexability (index/noindex), title, meta_description, h1, word_count, language, last_modified, internal_inlinks_count, external_inlinks_count (if available), primary_topic_tags (if you run topic extraction), schema_presence (boolean), page_type tag (blog/product/landing).
• Google Search Console export (via API)
  • query (normalized text) — raw query text and normalized (lowercase, punctuation removed) version.
  • page (canonical URL) — match to crawl canonical.
  • date (day) — to allow rolling windows.
  • impressions, clicks, ctr, position, search_appearance (e.g., featured_snippet). Pull at least 90 days; produce rolling 28/90/365-day aggregates. Google Search Console data export guide
• Competitor keyword exports (Ahrefs / Semrush / your SERP scraper)
  • keyword (normalized), competitor_domain, position, estimated_volume (tool metric), keyword_difficulty or CPC, SERP_features_present, top_pages (top 3 URLS), top_format_type (blog/product/comparison). If using multiple tools, keep a source column.

• Canonical URL normalization: lower-case, remove tracking params, map crawler canonical to GSC page field using exact-match; for near-duplicates use a hashed content fingerprint to align.
• Content ID: assign a stable internal ID for each canonicalized page (use site CMS page ID or slug-based UUID). Join all datasets by content ID (preferred) or canonical_url (fallback).
• Normalized keyword text: remove punctuation, fold unicode, replace smart quotes, whitespace collapse. Keep raw query for provenance but run joins on normalized_keyword.
• Topic/tag taxonomy: map both crawler topic tags and competitor top_format_type into your canonical taxonomy (Awareness / Consideration / Decision) using an intent classifier to ensure consistent intent segmentation. See our approach to funnel-stage keyword segmentation. Ahrefs keyword research tips and strategies

• GSC: site-owner or delegated property-level access for API pulls. Exports must include query-level data; if you can only access URL-only performance, request query access for full value. Google Search Console query data access
• Crawler frequency: weekly for rapidly-changing SaaS sites, biweekly for most B2B; increase cadence around product launches.
• Competitor refresh: monthly to capture SERP volatility and competitor content pushes. If competitors run big product launches or you see ranking shifts, run ad-hoc scrapes.
• Storage & compute: store raw exports in a data lake (Parquet or BigQuery) and run deterministic ETL jobs to produce the canonical datasets for Opportunities + Competitors.

Below is a practical implementation that takes raw inputs through transformation, scoring, and output into the Opportunities + Competitors workflow.

• Ingest raw crawler CSV → canonicalize urls → assign content_id.
• Ingest GSC query exports (90 days) via API; aggregate to 28-day and 90-day windows per (normalized_query, content_id). Keep raw daily rows for trend analysis. Google Search Console API for daily data
• Ingest competitor keyword exports (monthly) and normalize keywords.

• Run query normalization + lemmatization. Produce query intent label (Awareness/Consideration/Decision) using your funnel-intent classifier. See Funnel-Stage Keyword Segmentation for automating intent classification at scale. Ahrefs funnel-stage keyword segmentation
• Run SERP-scrape for top 10 for each target keyword to capture top formats (listicle, comparison, docs) and presence of SERP features (People Also Ask, AI Overview triggers).

• Left-join GSC (query → page) to crawler content_id via canonical_url.
• Right-join competitor keywords to the normalized query universe to identify “competitor-owned-only” queries where:
  • competitor ranks in top 10, AND
  • your site has 0 impressions or average position > 50 or no content mapped to the query.
• Compute presence flags:
  • HA (Have Already): site ranks in top 10.
  • NEAR (Near-Miss): impressions > X (default 500/month) and avg position between 6–20.
  • DISCOVERED_BY_COMPETITOR: competitor ranks top 5 and site has zero or negligible presence.

Create a transparent score; I use this formula operationally:

• DemandScore = normalized(impressions_90d) × SERPFeatureMultiplier
  • normalize impressions to 0–1 (log scale). Multiply by 1.5 if query triggers AI Overview or PAA (higher leverage on citations). Use GSC impression counts. Pew Research AI Overview click impact
• GapScore = competitor_presence_weight + your_presence_penalty + position_weight
  • competitor_presence_weight = 1 if competitor ranks top 5 (0 otherwise)
  • your_presence_penalty = 0.1 if you already rank top 3 (reduces score), 1 if you have zero impressions
  • position_weight = linear function where avg position 6–20 adds 0.8, position 21–50 adds 0.4
• EffortScore = estimated_hours_to_fix / 10 (so typical values between 0.5 and 5) — lower is better

• Surface for manual review: PriorityScore > 0.35 and DemandScore > 0.2
• Auto-promote to content ops backlog (no manual review): PriorityScore > 0.75 and EffortScore < 2

(Example) PriorityScore = 0.24 × 1.5 × (1.0 + 1.0 + 0.8) × (1 / 0.8) ≈ 0.9 — actionable and high-priority.

• Group recommendations into action types: Refresh, New page, Consolidate, Canonicalize (technical). Attach:
  • Title (suggested target query and primary intent).
  • Rationale (short): e.g., “90d impressions 3,600; average position 12; competitor A ranks #2 with a long-form comparison; our page is thin.”
  • Estimated effort (hours), expected clicks uplift (see uplift model below), required stakeholders (content, UX, eng).
• Push the structured recommendations into Opportunities + Competitors via API or CSV import. Tag each item with priority, project_id, and target quarter. Use programmatic labels for content ops (e.g., “Q3: Refresh / High Priority”).

• High-impression, low-CTR refresh candidates:
  • Filter: impressions_90d > 2,000 AND avg_position <= 10 AND ctr < expected_ctr_by_position (use industry CTR curve) — these are CTR UX/metadata wins. GSC shows the CTR and impressions. Google Search Console performance report
• Near-miss content expansion:
  • Filter: impressions_90d > 1,000 AND avg_position between 6 and 20 AND word_count < 1,200 — candidate for expansion and clustering.
• Competitor-owned new content:
  • Filter: competitor_top5 = TRUE AND our_impressions_90d = 0 AND estimated_volume > 250 — candidate for new page with matching format.
• Technical canonicalization:
  • Filter: multiple canonicalized versions mapping to same content_id OR status_code != 200 with indexed flag — candidate for canonical fixes.

To prove ROI quickly, estimate incremental clicks conservatively:

Estimated Incremental Clicks = impressions_90d × (target_CTR - current_CTR) × ExpectedPositionGainFactor

• Use industry CTR baselines by position (e.g., position 1 CTR ≈ 27–31%; position 2 ≈ 15%; position 3 ≈ 10%) — use your historical CTR curve if available. Ahrefs Google Search statistics
• ExpectedPositionGainFactor: if you’re at position 12 and implement a best-practice refresh and build 3–5 internal links, assume position gains of 4–7 places for good-fit queries (conservative: 4 places). Use scenario testing (pessimistic/realistic/optimistic).

Estimated Incremental Conversions = Estimated Incremental Clicks × ConversionRate
Estimated Incremental Revenue = Estimated Incremental Conversions × AverageDealValue

Use this to create a “days-to-payback” estimate by dividing implementation cost (hours × blended hourly rate) by incremental revenue per month.

• Weekly: run crawler (or pick up incremental changes) and produce updated content inventory. Keep a delta feed for new pages and recently edited pages to avoid duplicate work.
• Daily/weekly: pull GSC performance API for rolling windows (aggregate once per day). Use 28/90 day windows for seasonality smoothing. Google Search Console API data
• Monthly: refresh competitor keyword exports and SERP scrapes. Re-run OpportunityScore for the full universe.
• Sprint handoff: Opportunities + Competitors items auto-labeled “Quick Win” (Effort < 8 hours) go into the next content sprint; larger items get scoped by PO and scheduled.
• Measurement: tag each content action with a UTM and track GSC + analytics over 90 days post-publish. Use identical measurement windows to estimate realized uplift vs. expected.

• AI Overviews & citation strategy: if a query triggers AI Overviews, priority changes. Being cited yields visibility but often not clicks — citations function more like brand impressions than immediate traffic. Prioritize becoming the cited source when:
  • Your site already ranks in top 10 for similar queries, OR
  • The query converts well from discovery (high LTV), AND
  • You can provide unique, citable data (benchmarks, original charts, price tables). See Pew Research data: AI Overviews reduce clicks to traditional results (8% click rate with a summary vs 15% without), and only ~1% click to links inside the summary — treat citation as different KPI. Pew Research AI summary impact on clicks
• Multi-format strategy: competitor SERP format matters. If top results are product comparison tables or interactive calculators, writing a long-form blog post rarely wins — you need the same format (table, calculator, or sample code). Capture top_format_type during SERP scrape and include it in the action item.
• Backlink + topical authority multiplier: include referring-domain metric when available. Ahrefs’ analysis shows a strong correlation between referring domains and organic traffic — include an AuthorityMultiplier in GapScore when competitors have clear backlink advantages. Ahrefs keyword research tips

• Ingest connectors: use the GSC API connector to pull query-level data automatically; upload crawler CSV or feed Parquet into Semantic.io’s ingestion pipeline; map competitor exports into the competitor keyword import. Google Search Console data export
• Transform layer: implement the normalization and joins in Semantic.io with rulesets (canonical matching, query normalization, intent classification).
• Scoring and prioritization: implement the Demand × Gap × Effort formula inside Opportunities; store per-item scoring breakdown so recommendations are explainable. See Scoring SEO Opportunities: How AI Prioritizes What to Work on Next for model design patterns. Ahrefs blog on keyword research
• Outputs & handoffs: push ranked items into your task tracker (Jira/Asana) using the integration; include the brief, expected uplift, and necessary assets (SERP screenshots, competitor top pages, schema requirements).

Use a small pilot: pick 50 opportunities that meet PriorityScore > 0.6 and Effort < 8 hours. Measure:

• Baseline: average monthly organic clicks to the set for prior 90 days (GSC).
• After action: measure delta clicks for 90 days post-publish and convert to revenue using your conversion and AOV. Compare to implementation costs (hours × blended rate). A simple rule: prioritize items with < 90 days payback (common for mid-TAM B2B content). This lets you show direct ROI from the automated pipeline.

• Joining on non-canonical URLs: canonical mismatches create duplication in recommendations. Solution: trust and standardize crawler canonical detection and map to GSC page field.
• Ignoring format fit: building a blog post when SERP favors a tool/comparison will fail. Solution: capture top_format_type during SERP scrape and include that in the brief. Semrush content gap analysis guide
• Over-automation without review: auto-publishing low-quality pages is worse than manual checks. Solution: gate auto-publish to narrow, high-confidence buckets (PriorityScore > 0.9 and Effort < 2).

1. Export: schedule a GSC API export (90 days) and a weekly crawl export (include canonical_url and word_count). Ensure you have GSC property access. Google Search Console export details
2. Import: upload both into Semantic.io and import one competitor keyword export (Ahrefs or Semrush). Ahrefs blog post on SEO tools
3. Configure: enable the Opportunity scoring template (Demand × Gap × Effort) and set your site-level Effort hourly cost.
4. Run a pilot: surface the top 50 recommendations, label 10 as “Quick Win,” and deliver them to content ops. Measure 90-day uplift.
5. Scale: iterate scoring weights, add backlink signals, and automate cadence.

If you want a hands-on example: I’ll hand you a reproducible SQL + scoring notebook that ingests one crawl CSV, one GSC export, and one Ahrefs content gap export and produces a ready-to-import Opportunities CSV. Reach out via Semantic.io to request the sample playbook and template.

• Google Search Console — About & Help (overview, API and data uses). Google Search Console overview and help
• Pew Research Center — "Google users are less likely to click on links when an AI summary appears in the results" (July 22, 2025). Important stats: searches with AI summary clicked traditional result 8% vs 15% without; only 1% click links inside the summary. Pew Research on AI summary click-through rates
• Ahrefs — Content gap analysis and related guides (how to do content gap analysis, correlation of referring domains to organic traffic). Ahrefs blog on content gap analysis
• Semrush — Content gap analysis guide and AI visibility research. Semrush guide to content gap analysis
• Internet Live Stats — Google search volume estimates (used as context for search scale). Internet Live Stats Google search volume
• Search Engine Journal — coverage of AI Overviews and publisher impact. Search Engine Journal on AI Overviews impact
• Ahrefs Help / Feature docs — how to use Content Gap tools. Ahrefs help on Content Gap tool usage
• BrightEdge — organic channel share research and industry context (organic search as dominant channel). BrightEdge research on organic search share

• How to Build a Complete Keyword Universe Using AI and Real Search Data — How To Build A Complete Keyword Universe Using AI And Real Search Data
• Funnel-Stage Keyword Segmentation: Automating Intent Classification at Scale — Funnel Stage Keyword Segmentation Automating Intent Classification At Scale
• Scoring SEO Opportunities: How AI Prioritizes What to Work on Next — Scoring SEO Opportunities How AI Prioritizes What To Work On Next
• How to Score Your Site's LLM Readiness: A Data-Driven Framework — How To Score Your Site S LLM Readiness A Data Driven Framework
• The Dual-Optimization Framework: Ranking in Google AND Getting Cited by AI — The Dual Optimization Framework Ranking In Google And Getting Cited By AI
• How to Identify Content Refresh Opportunities Using Performance Data — How To Identify Content Refresh Opportunities Using Performance Data
• How to Set Up a Fully Autonomous SEO System That Runs Without You — How To Set Up A Fully Autonomous SEO System That Runs Without You

If you implement this pipeline you get predictable, auditable recommendations instead of ad-hoc keyword lists. The combination of crawl + GSC + competitor keywords forces specificity: it tells you what you have, what users are asking, and what the market already values. Automate the joins, keep the scoring explainable, and instrument outcomes — that’s how content gap analysis becomes an operational, automated lever for growth.

If you want the CSV import template, the scoring notebook, or a live demo of how Opportunities + Competitors maps these signals into a prioritized backlog, tell me the scale of your site (pages) and the crawl cadence you prefer and I’ll provide the starter pack tailored to your environment.

• URL inventory management SEO
• build keyword universe AI
• keyword funnel segmentation automation
• content gap analysis automated
• automated SEO system
• automated SEO site crawl
• crawl data GSC correlation
• SSR failure detection SEO
• AI keyword prioritization
• hub and spoke content strategy AI
• topical authority scoring
• automated content brief generation