BB SERP Intelligence System: Keyword-Based SERP Fetching → Page Crawling → FAQ Extraction → Google Sheets

Problem:

An SEO team needed to systematically research top-ranking pages for target keywords across different countries, extract FAQ content from competitor pages, and store structured SERP intelligence, manually searching, crawling competitor URLs, and extracting FAQs was too slow to support ongoing keyword research at scale.

Solution:

Built a form-triggered n8n four-section SERP intelligence pipeline, the Get SERPs by Keyword and Country section pulls mobile and desktop SERP results for the target keyword across specified countries and tags each result by device type; the Prepare SERPs for Crawling and Analysis section merges results, deduplicates URLs, splits them for processing, and removes duplicate entries before queuing for crawl; the Crawl Pages with Crawl4ai section loops over each SERP URL, fetches page content via the Crawl4ai API, polls for completion status, runs bot detection checks, retrieves clean content as Markdown, and passes it through an OpenAI model for content analysis before inserting SERP analysis records to Google Sheets; the GET FAQs section extracts all FAQ questions from the crawled content, removes zip code duplicates, and inserts structured FAQ records into a dedicated Google Sheets FAQ tab.

Result:

Delivered a fully automated SERP research system that takes a keyword and country, fetches live rankings across devices, crawls all top-ranking pages with AI content analysis, and delivers structured SERP data and competitor FAQs directly into Google Sheets, replacing days of manual research with a single form trigger.