SEO Meta Automation

End-to-end automation that, for each row in a Google Sheet, runs a localized Google search, scrapes the top affiliate / casino-review competitors, and uses AI to generate optimized meta (H1, Meta Title, Meta Description) under strict rules — then writes a formatted Google Doc report and links it back to the sheet.

Google Sheet row (Keyword, GEO, Language)
│
▼
Build search query: keyword + casino <review-word in Language>
│ (avis/erfahrungen/reseña/recensione/análise/review)
▼
Localized Google SERP (nodriver default, Playwright fallback)
│ ──► TOP-10 organic results
▼
Pick 3 affiliate/review sites (pre_check → scrape → content filter → LLM)
│
▼
Scrape each: H1 / Meta Title / Meta Description / position / site structure
│ (Playwright; optional Capsolver hook for Turnstile/reCAPTCHA)
▼
Generate new meta in `Language` starting with the ORIGINAL keyword
│ (LLM + deterministic rule enforcement — TZ §3 rules 1-5)
▼
Google Doc "{Keyword}-{GEO}" ──► shared "anyone with link = Commenter"
│
▼
Write Doc link to `Result`, set `Status` (done / partial / error)

Pipeline details

SERP (serp.py + serp_nodriver.py). DIY Google scraping. Localization uses the country Google domain + gl + hl (the country's primary language)

• a uule canonical-location parameter. EU cookie-consent is auto-accepted.

The default backend is nodriver — a CDP-based undetected browser. This is required because the modern Google SERP (2024+) serves a JS-shell to the first GET and renders organic results client-side; Playwright contexts (even with playwright-stealth + real Chrome) are detected by that shell's anti-bot probe and redirected via ?sei=... → /sorry/index, where the IP gets rate-limited (429). nodriver doesn't leak navigator.webdriver, runs Chrome in offscreen headful mode (--window-position=-2000,0, invisible to the user) and gets through. The legacy Playwright backend (SERP_BACKEND=playwright) is kept as an escape hatch and still has the capsolver hook for /sorry/ reCAPTCHA v2.

nodriver also intercepts CDP Page.setInterceptFileChooserDialog(true) on every tab before the first navigation, so a misbehaving page (Chrome's profile-onboarding, a website's hidden <input type=file>) can't pop a native OS file-picker dialog over the operator's desktop while the script is running offscreen.

SERP query disambiguation (pipeline._disambig_suffix). The keyword in the sheet is a brand/game name (aviator, 1win, betway, …). Searching Google for the bare brand surfaces (a) the operator's OWN mirror sites and (b) non-gambling content for ambiguous names — aviator on google.fr returns the 2004 Scorsese biopic; 1win on google.co.in returns ten 1win-owned domains. Classifier correctly rejects both, and the row would end as error: 0 affiliates.

To make Google return affiliate review pages, we append casino <review-word> to the search query, where the <review-word> is the row-language equivalent of "review":

Language	Suffix
en, fallback	casino review
fr	casino avis
de	casino erfahrungen
es	casino reseña
it	casino recensione
pt	casino análise

The original keyword is what reaches generator.generate(...), so TZ §3.1 (H1/Meta Title start with the literal sheet keyword) is preserved. The disambiguation lives in pipeline.py, not in serp.build_search_url, so the URL builder stays a pure transformation.

Languages outside the table (hi, ja, ru, pl, …) fall back to casino review — for Hindi specifically this is measured to give more affiliate sites than समीक्षा would, because Indian online-gambling SEO is dominated by English-language content; for ja/ru/pl it's a safe default (rather than risk a worse localized term).

Classification (classifier.py). Four stages — chosen so we never pay for an LLM call on a candidate we can decide cheaply, and so an LLM hallucination can never sneak an operator past us:

1. pre_check — URL/domain/title blacklist. Hard-excludes wikis, news, forums, social, app stores, regulators. No I/O, no LLM.
2. _is_casino_relevant off-topic gate — after scraping the candidate, require ≥2 distinct casino-vocabulary tokens (casino, bonus, slot, roulette, multilingual). A movie page that accidentally mentions "bonus DVD" doesn't trip this.
3. is_affiliate operator fast-path — URL path contains /login//cashier//deposit//register + 2+ operator-tokens in text → reject as operator without paying for an LLM call (TZ §2.2: we want affiliate REVIEW sites, not operators themselves).
4. LLM 4-category verdict (affiliate / operator / news / other) for every non-trivial case, via the configured provider (AI_PROVIDER=claude|gemini).
5. Structural purity gate on every LLM "affiliate" verdict. The page must show STRUCTURAL evidence of being a review/listicle: either the URL/title contains a listicle marker (best, top, review, comparatif, migliori, …), OR the page has ≥3 outbound affiliate links (review sites link OUT to operators), OR a STRONG review token (review, avis, comparatif, recensione, vergleich, análise) appears in the H1/Title. Without ANY of these structural signs, an LLM "affiliate" verdict is overridden to reject — prevents ephpp.ca, livescore.com, transfermarkt.com from being mistakenly picked up.

Any LLM error → site is excluded, so an operator is never picked by accident.

Scraping (scraper.py). Extracts H1, <title>, meta description, plus the "maximum" structure: heading tree (H1–H3), nav/menu items, page sections, sitemap.xml URLs (via robots.txt) and schema.org types (JSON-LD + microdata). Per-site and sitemap timeouts prevent any one site from stalling the run; HTTP errors and Cloudflare/JS-challenge pages are treated as "blocked".

Generation (generator.py + validators.py). The LLM (Gemini or Claude, selected by AI_PROVIDER) writes the creative text; deterministic Python guarantees the hard rules from TZ §3:

• §3.1 Keyword first — H1 and Meta Title are forced to start with the exact keyword (H1 keeps the sheet casing; Title is Title-Cased).
• §3.2 No emojis / no stop-words — stripped in code (Discover, Thrilling, Enjoy, Excitement, Dive into, Experience), and the LLM is also told to avoid clichés.
• §3.3 Anti-template — competitor meta is fed into the prompt so the output differentiates; collect_problems then verifies the result mentions at least one bonus / payout-speed token (multilingual vocab) and triggers another revise pass when it doesn't.
• §3.4 Length — a feedback loop re-prompts the LLM ("rewrite longer" if Title < 40, "rewrite more concisely" if Title > 60 or Description ≥ 160). After MAX_GEN_ITERATIONS a deterministic fallback pads / trims using a per-language pool of benefit phrases (BENEFIT_PHRASES[language]) for the curated en/fr/de/es/it/pt set, falling back to keyword-based neutral padding for any other language. The keyword at the front is never cut.
• §3.5 Capitalization — Title-Case every word, unconditionally, in every language. Rule §3.5 covers Title and Description only, so H1 casing is left as written by the LLM.

After the revise loop + fallback finish, generator._audit_compliance runs a final §3 audit; any remaining violation is logged as a WARNING so it surfaces in run.log instead of slipping into the Doc silently.

Output (docs_builder.py). Creates the Doc, formats it via the Docs API batchUpdate (Heading 1 title, "Competitor Reports" with a linked entry + structure per site, "Optimized SEO Content", and a "Failures" block if fewer than 3 affiliates were found), and shares it as anyone with the link = Commenter through the Drive API.

TZ compliance

TZ section	Requirement	Implementation	Status
§0	Create Google Sheet, configure access for automation	Sheet 1wMKlN84… with OAuth2 (Sheets/Docs/Drive scopes)	✅
§1	Sheet columns: Keyword, GEO, Language, Result	config.COLUMNS = [Keyword, GEO, Language, Result, Status]	⚠️ ext (see below)
§1	Language is the output-meta language	RowInput.language → generator.generate(..., language, ...)	✅
§1	Multi-word keyword example (casino en ligne)	force_keyword_prefix handles arbitrary keyword length (see tests/test_validators.py:73)	✅
§2 / Search	Localized Google search per Keyword + GEO	serp.build_search_url (gl + hl + uule + country domain); nodriver SERP backend bypasses anti-bot	⚠️ disambig (see below)
§2 / Selection	First 3 affiliate sites strictly from TOP-10	pipeline._try_pick_affiliates walks TOP-N, stops at 3 collected, breaks if no more results	✅
§2 / Scraping	H1, Meta Title, Meta Description, position, site structure	scraper.scrape → ScrapedPage(h1, meta_title, meta_description, structure=SiteStructure(headings, nav, sections, sitemap_urls, schema_types))	✅
§2 / Fault-tolerance	Continue to next TOP-10 slot on block; if <3 — stop and report reasons	process_row collects failures list; never reaches outside TOP-10; doc renders "Failures" block	✅
§3.1	KEYWORD FIRST in H1 and Meta Title	validators.force_keyword_prefix runs in postprocess AND in fallback; generator._audit_compliance re-checks at the end	✅
§3.2	No emojis, no stop-words (Discover, Thrilling, Enjoy, Excitement, Dive into, Experience)	strip_emoji + remove_stop_words in postprocess; LLM prompt also forbids them; audit re-checks	✅
§3.3	Anti-template — bonuses / payout-speed emphasis	collect_problems flags missing anti-template vocab → triggers revise pass; multi-lingual vocab in config.ANTI_TEMPLATE_VOCAB	✅
§3.4	Title 40-60 chars, Description < 160 chars	Revise loop re-prompts the LLM; deterministic fallback pads/trims with BENEFIT_PHRASES (en/fr/de/es/it/pt) or _neutral_pad_title for other languages; audit re-checks	✅
§3.5	All Title words capitalized; sentence-case Description	apply_capitalization is unconditional Title-Case (to_title_case) + sentence-case (to_sentence_case); audit re-checks every word	✅
§4 / Filename	Doc named {Keyword}-{GEO}	docs.documents().create(body={"title": f"{keyword}-{geo}"}) (docs_builder.py:136)	✅
§4 / Heading	Analysis for [Keyword] - [GEO] (Heading 1)	b.para(f"Analysis for {keyword} - {geo}", style="HEADING_1") (docs_builder.py:89)	✅
§4 / Block	"Competitor Reports" — link, position, meta, structure for each of 3 sites	Heading 2 "Competitor Reports"; per-competitor: H3 with link to URL, URL line, H1, MT, MD, structure bullets (headings/nav/sections/sitemap/schema)	✅
§4 / Block	"Optimized SEO Content" — final H1, MT, MD	Heading 2 "Optimized SEO Content" + 3 plain paragraphs	✅
§5.1	Anyone-with-link = Commenter access	drive.permissions().create(body={"type": "anyone", "role": "commenter"}) (docs_builder.py:117-121)	✅
§5.2	Doc URL written into Result column	sheets.write_doc_url_early publishes URL early (while status=pending); final write_result overwrites with same URL + final status	✅
§6.1	Public GitHub repo (Python)	Code is structured for git init + push; secrets gitignored	✅
§6.2	Sheet with Editor access for reviewer	Operator step — share 1wMKlN84… with reviewer's email	✅ (operator step)
§6.3	Video demo across 2-3 keywords + GEOs (e.g. aviator+FR, 1win+IN, novibet+IE)	Sheet is pre-loaded with exactly these 3 rows + 2 extras (betway+DE, parimatch+IN)	✅ (operator step)
§6.4	README — architecture + parsing + generation + run instructions	This document	✅

Documented deviations from TZ

Two areas where the implementation does not match the literal TZ wording — both are intentional and authorised:

1. 5-column sheet (Status added). TZ §1 specifies 4 columns (Keyword, GEO, Language, Result). We add a 5th column Status so re-runs are idempotent: rows with Status ∈ {done, partial, error: …} are skipped, only pending (or blank) rows are processed. Without Status the script would re-process every row every run, hammering Google's rate-limit and re-generating Docs that already exist. This is a strict extension of TZ — the four mandated columns are still present and authoritative.

2. Search query disambiguation. TZ §2.1 says "search Google by Keyword". For a brand keyword like aviator, bare Google returns movie content (Scorsese 2004 biopic); for 1win it returns the operator's own 9 mirror domains. Classifier correctly rejects both per TZ §2.2, so the row ends with error: 0 affiliates in TOP-10 (TZ §2.4 compliant). To make the search actually surface affiliate review pages, the pipeline appends casino <review-word> to the Google query, with <review-word> localized per row language (avis / erfahrungen / reseña / recensione / análise / review). The original keyword is what reaches H1/Meta generation, so TZ §3.1 is fully preserved. This was a deliberate design choice after empirical measurement (see "SERP query disambiguation" above for the data and the per-language table).

Setup

1. Prerequisites

• Python 3.11+ (developed and tested on 3.13).
• A Google account (a normal Gmail is fine — Workspace is not required).
• A Google Gemini API key (free tier works), or an Anthropic API key for Claude.
• Google Chrome installed — the default nodriver SERP backend drives the real Chrome binary. On macOS/Windows the standard install location is found automatically; on Linux see R2.1 below for Xvfb.

2. Install

python -m venv .venv
# Windows: .venv\Scripts\activate# macOS/Linux: source .venv/bin/activate
pip install -r requirements.txt
# Playwright Chromium — only needed for competitor scraping (default) and the# legacy SERP backend (SERP_BACKEND=playwright). nodriver uses the real Chrome# from step 1.
playwright install chromium

3. Google Cloud (OAuth client)

1. Create / pick a project at https://console.cloud.google.com.
2. APIs & Services → Library: enable Google Sheets API, Google Docs API, Google Drive API.
3. APIs & Services → Credentials → Create credentials → OAuth client ID → Application type: Desktop app. Download the JSON and save it as credentials.json in the project root.
4. OAuth consent screen: add the Drive/Docs/Sheets scopes and publish the app to "Production" (see Known limitations — in "Testing" mode the refresh token expires after 7 days).

4. AI provider (Gemini or Claude)

Default is Gemini — create a key at https://aistudio.google.com/app/apikey. To use Claude instead, set AI_PROVIDER=claude and ANTHROPIC_API_KEY=... in .env. The model is configurable for either provider via GEMINI_MODEL / CLAUDE_MODEL.

5. Google Sheet

Create a sheet with this header row (columns A–E):

Keyword	GEO	Language	Result	Status
aviator	FR	fr		pending
1win	IN	en		pending
novibet	IE	en		pending

• GEO is an ISO country code; Language is the output language for the meta.
• Only rows with Status = pending (or blank) are processed (idempotent re-runs). The script writes the Doc link to Result and updates Status.
• Copy the spreadsheet ID from its URL.
• For submission, share the sheet with Editor access.

6. Environment

cp .env.example .env # Windows: copy .env.example .env

Required:SHEET_ID, SHEET_TAB, and one of GEMINI_API_KEY or ANTHROPIC_API_KEY (matching your AI_PROVIDER choice).

Optional knobs, grouped by what they affect:

Area	Knobs
AI provider	AI_PROVIDER (gemini default | claude), GEMINI_MODEL, CLAUDE_MODEL
SERP backend	SERP_BACKEND (nodriver default | playwright), NODRIVER_OFFSCREEN, NODRIVER_RENDER_WAIT_S, NODRIVER_USER_DATA_DIR, NODRIVER_SUBMIT_TIMEOUT_S
SERP rate-limit handling	NODRIVER_DNS_ROTATION, NODRIVER_DNS_REFRESH_S, NODRIVER_MAX_IP_ATTEMPTS, NODRIVER_RETRY_DELAY_S
Pacing	INTER_ROW_DELAY_MIN_S, INTER_ROW_DELAY_MAX_S (default 45-90 s, sustainable Google)
Competitor scrape (Playwright)	HEADLESS (the --headful CLI flag overrides)
Proxy (Playwright path only)	PROXY_SERVER, PROXY_USERNAME, PROXY_PASSWORD
Capsolver — fully optional	CAPSOLVER_API_KEY (leave empty / unset to disable everywhere), CAPSOLVER_TIMEOUT_S, CAPSOLVER_POLL_INTERVAL_S

TZ §3.5 capitalization is unconditional — Title is Title-Cased word-by-word in every language. An earlier CAPITALIZATION_MODE=natural opt-in escape hatch was removed because it produced sentence-case Titles on non-English rows, which violated §3.5.

See .env.example for the full list with comments.

Run

python main.py # process all pending rows
python main.py --row 2 # only sheet row 2
python main.py --limit 1 # at most 1 row
python main.py --headful # show the Playwright window used for competitor scraping# (the nodriver SERP browser is governed by NODRIVER_OFFSCREEN,# not this flag)

The first run opens a browser for one-time OAuth consent; the token is saved to token.json and refreshed automatically afterwards. Progress is logged to the console and to run.log (detailed per-slot failure reasons live there).

Tests

Pure logic (rule enforcement, classification heuristics, parsers, Docs index math, SERP disambiguation, file-chooser CDP wiring) is covered by unit tests — no network or browser required:

python -m pytest tests -q

At time of writing: 102 tests, ~0.5 s — a reviewer can sanity-check the whole pipeline before running the live main.py.

Known limitations

• R1 — OAuth "Testing" mode. Refresh tokens expire after 7 days and Drive is a sensitive scope (an "unverified app" warning appears). Mitigation: publish the OAuth app to Production (for personal use you self-approve and click through the warning once); the token then persists.
• R2 — Google anti-bot. Modern Google SERP fingerprints the browser before serving organic results — Playwright contexts get redirected to /sorry/ and rate-limited. The nodriver backend (default) avoids this; if you pin SERP_BACKEND=playwright, the Capsolver hook is the second line of defence for /sorry/ reCAPTCHA. A truly blocked SERP is recorded as a row error, never a crash — reliability depends on IP reputation and how much Google has been hammered from that IP.
• R2.1 — Linux servers and nodriver. nodriver runs Chrome in offscreen headful mode (the window-position trick makes it invisible to the user). On a headless Linux server, this needs a virtual display: install Xvfb (sudo apt-get install xvfb) and run xvfb-run python main.py. macOS and Windows work out of the box.
• R3 — GEO accuracy. From a single IP the localization (gl/hl/uule) is approximate. The proxy stub is the upgrade path to true in-country results: set PROXY_SERVER (and optionally PROXY_USERNAME / PROXY_PASSWORD) in .env — it is injected straight into the Playwright context, no code changes.
• R4 — Position is an estimate.serp.py numbers competitors after per-domain deduplication and reads from any a h3 on the SERP, so the written position can be off by 1-2 vs. the "true" Google ranking, and occasionally a non-classical organic block (e.g. People-Also-Ask) may enter the candidate pool. The "within TOP-10 by count" guarantee is preserved; the integer itself is best-effort for a DIY scrape.
• R5 — Brand-dominated keywords. A few operators (e.g. 1win) own enough mirror domains and rank them so aggressively that even <brand> casino review returns ≥8/10 operator-owned pages on a specific GEO. The classifier correctly identifies them as operator (not affiliate), so the row may end as error: 0 affiliates in TOP-10 — this is TZ §2.4 compliant, not a software bug. Mitigation only matters if downstream you want forced output for these keywords: switch the suffix table to <brand> casino review affiliates (one-line edit in pipeline._DISAMBIG_REVIEW_BY_LANG) — measured but not adopted in this drop because the cost-benefit on rare brand-dominant rows didn't justify changing the default for everyone.

Capsolver (CAPTCHA bypass) — optional

TL;DR — Capsolver is fully optional. The system runs end-to-end without it: a CAPTCHA-blocked page is simply recorded as a row-level failure and the pipeline moves to the next slot in the TOP-10 (per TZ §2.4). Leave CAPSOLVER_API_KEY unset or empty and no Capsolver code path executes — the module is import-safe and all call sites are gated on config.CAPSOLVER_ENABLED.

DIY SERP scraping and competitor scraping both occasionally hit a CAPTCHA: Google serves a reCAPTCHA v2 on /sorry/... when traffic looks unusual, and many competitor sites front their content with Cloudflare Turnstile. If you do configure Capsolver, the hook solves both transparently.

Set in .env:

CAPSOLVER_API_KEY=cap-... # from https://dashboard.capsolver.com
# Optional tunables:
# CAPSOLVER_TIMEOUT_S=180
# CAPSOLVER_POLL_INTERVAL_S=2

Where it kicks in:

• Google SERP — both backends. When the page lands on /sorry/... or the body contains the "unusual traffic" / "not a robot" markers, the sitekey is read from the embedded .g-recaptcha[data-sitekey], sent to Capsolver (ReCaptchaV2TaskProxyLess), and the returned g-recaptcha-response token is injected into the form. The form is submitted and the page is re-checked. On the nodriver path this is serp_nodriver._solve_via_capsolver; on the legacy Playwright path it's serp._solve_serp_captcha.
• Competitor scrape (scraper.py). When _looks_blocked fires on a competitor page, the scraper looks for either a Cloudflare Turnstile widget (.cf-turnstile[data-sitekey]) or a reCAPTCHA v2 widget, asks Capsolver (AntiTurnstileTaskProxyLess or ReCaptchaV2TaskProxyLess), injects the token into the matching response input (cf-turnstile-response or g-recaptcha-response), submits the form and re-renders.

Failure modes — all logged and downgraded, never crash a row:

• No widget on the blocked page → recorded as anti-bot / JS challenge page.
• Capsolver API error or poll timeout → recorded with the underlying reason (capsolver failed: …); the row moves on to the next SERP slot.
• Token submitted but the site still blocks → anti-bot persists after capsolver attempt.

The client uses only urllib from the stdlib, so no new dependency is added.

Design note: why OAuth2 (not a Service Account)

A Service Account has no Drive storage quota and cannot create Docs in a regular (non-Workspace) Drive — files.create fails with storageQuotaExceeded. Since this runs on a consumer Gmail account, OAuth2 (acting as the user, who owns the created files) is used for all Google operations.

Project layout

main.py CLI entrypoint, Playwright context + nodriver shutdown
pipeline.py per-row flow + within-TOP-10 failover + status
config.py tunables, GEO maps, classification signals, proxy stub
models.py dataclasses passed between stages
google_auth.py OAuth2 installed-app flow → Sheets/Docs/Drive clients
sheets.py read pending rows / write Result + Status
serp_nodriver.py nodriver-based Google SERP fetcher (DEFAULT backend)
serp.py legacy Playwright SERP path + shared helpers
(build_search_url, parse_organic) used by both backends
scraper.py page meta + "maximum" structure (incl. sitemap/schema)
classifier.py affiliate-vs-operator heuristics + LLM escalation
generator.py LLM generation + char-limit + anti-template revise loop
validators.py pure rule enforcement (prefix, limits, emoji, stop-words,
caps, multilingual BENEFIT phrases + anti-template vocab)
llm.py provider-agnostic LLM client (Gemini or Claude; lazy SDK, retry)
capsolver.py Capsolver HTTP client (reCAPTCHA v2 + Cloudflare Turnstile)
docs_builder.py Docs API formatting (UTF-16-aware indices) + Drive sharing
tests/ unit tests (no network)