SEO Monitoring
by kostja94 / marketing-skills
Build an SEO data analysis system, monitor indexing/traffic/keywords/backlinks, or set up benchmarks.
Analyzes server and CDN logs in BigQuery to show what Googlebot crawls, where crawl budget is wasted, and which priority URLs are under-crawled.
Listed ·Updated
AGENT ROLE: Autonomous crawl-analytics agent. You operate over a BigQuery logs table. Resolve the schema, run the parameterized query in scripts/googlebot_crawl_analysis.sql, and emit the JSON contract in references/output.schema.json. Never SELECT * on a raw logs table — always project and aggregate.
Classify verified Googlebot requests over a window and report: crawl volume by status class, top wasted-crawl paths (non-200, non-indexable, parameterized), orphaned crawls (crawled but absent from the supplied sitemap set), and high-priority URLs under-crawled relative to their importance.
project (REQUIRED): GCP project id for billing/jobs.logs_table (REQUIRED): fully-qualified project.dataset.table holding access logs.column_map (REQUIRED): map logical fields → actual columns, e.g. { "ts":"timestamp", "path":"cs_uri_stem", "status":"sc_status", "ua":"cs_user_agent", "ip":"c_ip", "bytes":"sc_bytes" }.window_days (OPTIONAL, default 30).sitemap_urls (OPTIONAL): array of indexable path strings for orphan detection.priority_paths (OPTIONAL): array of {path_prefix, weight} for under-crawl scoring.GOOGLE_APPLICATION_CREDENTIALS (service-account key).
error.code="AUTH_MISSING_CREDENTIALS".roles/bigquery.jobUser on {project} AND roles/bigquery.dataViewer on the dataset of {logs_table}.
403 accessDenied THEN STOP error.code="AUTH_NO_BQ_ACCESS" naming the missing role.jobs.query (or bq query --use_legacy_sql=false). ALWAYS pass maximumBytesBilled = 10 * 2^30 (10 GiB) as a cost guard.
error.code="COST_GUARD_TRIPPED" and report the dry-run byte estimate; instruct the user to narrow window_days or add a partition filter.ua LIKE '%Googlebot%'.unverified and set verification="ua_only" in output; DO NOT silently claim verified. IF reverse-DNS enrichment is available, keep only IPs whose PTR ends in .googlebot.com or .google.com and forward-confirms.dryRun=true; read totalBytesProcessed. Enforce the cost guard.GET table metadata (tables.get) to confirm every column_map value exists. IF a mapped column is missing THEN STOP error.code="COLUMN_MAP_INVALID" listing the missing keys.scripts/googlebot_crawl_analysis.sql with query parameters (@window_days, @sitemap, @priority). Parameterize — never string-concatenate user input into SQL.STEP 1 — Bind params, dry-run, cost-check, execute. STEP 2 — From the result rollups compute:
status_breakdown: request counts by class 2xx/3xx/4xx/5xx and the wasted ratio = (3xx+4xx+5xx)/total.wasted_paths: top 20 non-200 or parameterized paths by hits.orphaned: paths with Googlebot hits whose normalized form is NOT in sitemap_urls (only if provided).under_crawled: for each priority_paths prefix, crawl_rate = hits / days; flag IF crawl_rate < 1 for weight ≥ 0.7.
STEP 3 — Emit JSON per schema, sorted by impact.rateLimitExceeded → exponential backoff (2^attempt, max 5) then STOP error.code="RATE_LIMITED".quotaExceeded (concurrent queries) → wait 10s, retry up to 3.jobs.get every 2s up to 120s; IF still running THEN return partial status="timeout" with job_id for later retrieval.window_days yields 0 Googlebot rows THEN status="no_googlebot_traffic"; return empty arrays, do not error.sitemap_urls absent THEN omit orphaned and set orphan_analysis="skipped_no_sitemap".COLUMN_MAP_INVALID; these are mandatory.One JSON object validating references/output.schema.json. No prose.
scripts/googlebot_crawl_analysis.sql — parameterized BigQuery Standard SQL.references/output.schema.json — output contract.Not using the CLI? Copy the SKILL.md and paste it straight into ChatGPT, Claude, or any agent.