Quick overview
This workflow runs every 6 hours (or manually) to audit websites listed in Google Sheets using Apify Actors, scores technical SEO, AI search readiness (/llms.txt), DMARC/security, and Google Trends coverage, then uses OpenAI to generate remediation briefs and creates Gmail draft emails while syncing results back to Google Sheets.
How it works
- 7-Day TTL Queue & Concurrency Lock: Runs every 6 hours (or manually), reads your Google Sheet, prioritizes unaudited websites ahead of bounded retries (Retry_Count < 3) and expired weekly audits (reAuditAfterDays: 7), and locks the active batch as Status = Auditing in Google Sheets so overlapping executions never collide.
- Actor #1 — Broken Links, XML Sitemap, AI Bot Rules & /llms.txt Crawl: Calls the Apify Broken Link Checker, Sitemap & AI SEO (llms.txt) Auditor (xenikii/broken-link-sitemap-llms-txt-auditor) with a 290s timeout and retries to verify internal and external links (using GET Range fallback to avoid Cloudflare/WAF false positives), parse XML/gzipped sitemaps, audit 11 AI search crawlers (GPTBot, OAI-SearchBot, ClaudeBot, PerplexityBot, Google-Extended) in robots.txt, and generate a spec-compliant /llms.txt file.
- Automatic Seed Topic Extraction: If Target_Keyword is left blank in your Google Sheet, the workflow automatically derives a clean seed topic keyword from the audited website's root page <title> or <h1> heading before launching trend analysis.
- Actors #2 & #3 — Parallel CMS/DMARC Stack & Google Trends Breakout Demand: For reachable websites, fans out into two parallel branches joined by a Merge node: (a) Company Domain, Tech Stack & MX/DNS Enricher (xenikii/company-domain-tech-stack-enricher) to detect CMS/framework (Next.js/Vercel, WordPress, Webflow, Shopify, Cloudflare), SPF/DMARC policy, and technical role contacts (webmaster@, dev@, tech@, seo@, hello@), and (b) Google Trends API, Breakout Keywords & Comparison Scraper (xenikii/google-trends-scraper) in explore mode to fetch rising/breakout search queries (+5000%), momentum %, and Knowledge Graph topics.
- 4-Pillar Health Scoring & Sitemap vs. Trends Content-Gap Engine: Deterministically scores each website from 0–100 across Technical SEO (0–40), AI Search Readiness (0–30), Security & DMARC (0–15), and Search Trend Coverage (0–15), comparing rising/breakout Google Trends queries against all URL slugs in sitemap.xml to surface Uncovered Breakout Queries.
- CMS-Aware OpenAI Fix Plan & /llms.txt Appendix: Routes websites scoring below fixScoreThreshold (80), having broken links, missing /llms.txt, or missing breakout search topics to OpenAI (gpt-4o-mini) to draft CMS-specific redirect and robots.txt rules (next.config.js, .htaccess, Webflow 301s, Cloudflare _redirects) plus 2 content-gap slug briefs, then deterministically appends the generated /llms.txt file in code.
- Guarded Gmail Draft & 29-Column Google Sheets Sync: Creates a Gmail Draft (Alert_Status = Drafted) addressed to the best available contact email once senderName is configured, and writes all 29 audit, scoring, tech stack, trend gap, and report columns back to Google Sheets (or logs bounded retry / Unreachable states).
Setup
- Prepare your Google Sheet: Create a spreadsheet (e.g., SEO & AI Search Audit Pipeline, tab Websites), paste this exact header row into Row 1 (A1:AC1 via Data -> Split text to columns): Website,Target_Keyword,Geo,Owner_Email,Domain,Final_URL,Status,Retry_Count,Health_Tier,Overall_Health_Score,Tech_SEO_Score,AI_Search_Score,Security_DMARC_Score,Trend_Coverage_Score,Score_Breakdown,Pages_Audited,Links_Checked,Broken_Links_Count,Bot_Protected_Links,Broken_Links_Top5,Sitemap_Found,Sitemap_URLs_Count,Llms_Txt_Published,Blocked_AI_Bots,OnPage_Issues,CMS_Platform,Tech_Stack,Hosting_And_DNS,Email_And_DMARC,Search_Trend_Direction,Uncovered_Breakout_Queries,Semantic_Knowledge_Topics,Primary_Contact_Email,Alert_Status,Generated_Llms_Txt,AI_Report_Subject,AI_Report_Body,Last_Audited_At — then add target domains in the Website column.
- Configure Audit & SEO Strategy Config: Open the Audit & SEO Strategy Config node and replace YOUR_NAME_OR_TEAM in senderName (required to unlock OpenAI & Gmail draft creation). Optionally set projectOwnerEmail to route all audit reports to your own inbox, and adjust batchSize (2), reAuditAfterDays (7), or fixScoreThreshold (80).
- Connect Credentials: Attach Google Sheets OAuth2 on the 4 Google Sheets nodes, HTTP Header Auth (Name: Authorization, Value: Bearer YOUR_APIFY_API_TOKEN) on the 3 Apify HTTP Request nodes, OpenAI API on the Draft CMS Fix Plan & Content Brief (OpenAI) node, and Gmail OAuth2 on the Create Audit & Fix Report Draft in Gmail node.
Requirements
- Google Sheets OAuth2, Gmail OAuth2, OpenAI API key (gpt-4o-mini), and an Apify API token configured as an HTTP Header Auth credential (Authorization: Bearer YOUR_APIFY_API_TOKEN).
Customization
- Adjust batchSize (default 2), reAuditAfterDays (default 7), maxRetries (default 3), fixScoreThreshold (default 80), maxPagesPerDomain (default 30), defaultGeo, and projectOwnerEmail in the centralized Audit & SEO Strategy Config node.
Additional info
Uses three Apify Actors in parallel: Broken Link Checker, Sitemap & AI SEO (llms.txt) Auditor (https://apify.com/xenikii/broken-link-sitemap-llms-txt-auditor), Company Domain, Tech Stack & MX/DNS Enricher (https://apify.com/xenikii/company-domain-tech-stack-enricher), and Google Trends API, Breakout Keywords & Comparison Scraper (https://apify.com/xenikii/google-trends-scraper).