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)
SERP (serp.py + serp_nodriver.py). DIY Google scraping. Localization
uses the country Google domain + gl + hl (the country's primary language)
- a
uulecanonical-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:
pre_check— URL/domain/title blacklist. Hard-excludes wikis, news, forums, social, app stores, regulators. No I/O, no LLM._is_casino_relevantoff-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.is_affiliateoperator 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).- LLM 4-category verdict (
affiliate / operator / news / other) for every non-trivial case, via the configured provider (AI_PROVIDER=claude|gemini). - 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 — preventsephpp.ca,livescore.com,transfermarkt.comfrom 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 —
H1andMeta Titleare 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_problemsthen 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_ITERATIONSa 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 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] |
|
| §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 |
|
| §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 | ✅ |
Two areas where the implementation does not match the literal TZ wording — both are intentional and authorised:
-
5-column sheet (Status added). TZ §1 specifies 4 columns (
Keyword,GEO,Language,Result). We add a 5th columnStatusso re-runs are idempotent: rows withStatus ∈ {done, partial, error: …}are skipped, onlypending(or blank) rows are processed. WithoutStatusthe 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. -
Search query disambiguation. TZ §2.1 says "search Google by
Keyword". For a brand keyword likeaviator, bare Google returns movie content (Scorsese 2004 biopic); for1winit returns the operator's own 9 mirror domains. Classifier correctly rejects both per TZ §2.2, so the row ends witherror: 0 affiliates in TOP-10(TZ §2.4 compliant). To make the search actually surface affiliate review pages, the pipeline appendscasino <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).
- 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.
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- Create / pick a project at https://console.cloud.google.com.
- APIs & Services → Library: enable Google Sheets API, Google Docs API, Google Drive API.
- APIs & Services → Credentials → Create credentials → OAuth client ID →
Application type: Desktop app. Download the JSON and save it as
credentials.jsonin the project root. - 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).
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.
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 |
GEOis an ISO country code;Languageis the output language for the meta.- Only rows with
Status=pending(or blank) are processed (idempotent re-runs). The script writes the Doc link toResultand updatesStatus. - Copy the spreadsheet ID from its URL.
- For submission, share the sheet with Editor access.
cp .env.example .env # Windows: copy .env.example .envRequired: 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=naturalopt-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.
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).
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 -qAt time of writing: 102 tests, ~0.5 s — a reviewer can sanity-check
the whole pipeline before running the live main.py.
- 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 pinSERP_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 runxvfb-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: setPROXY_SERVER(and optionallyPROXY_USERNAME/PROXY_PASSWORD) in.env— it is injected straight into the Playwright context, no code changes. - R4 — Position is an estimate.
serp.pynumbers competitors after per-domain deduplication and reads from anya h3on the SERP, so the writtenpositioncan 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 reviewreturns ≥8/10 operator-owned pages on a specific GEO. The classifier correctly identifies them asoperator(not affiliate), so the row may end aserror: 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 inpipeline._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.
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_KEYunset or empty and no Capsolver code path executes — the module isimport-safe and all call sites are gated onconfig.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 returnedg-recaptcha-responsetoken is injected into the form. The form is submitted and the page is re-checked. On the nodriver path this isserp_nodriver._solve_via_capsolver; on the legacy Playwright path it'sserp._solve_serp_captcha. - Competitor scrape (
scraper.py). When_looks_blockedfires on a competitor page, the scraper looks for either a Cloudflare Turnstile widget (.cf-turnstile[data-sitekey]) or a reCAPTCHA v2 widget, asks Capsolver (AntiTurnstileTaskProxyLessorReCaptchaV2TaskProxyLess), injects the token into the matching response input (cf-turnstile-responseorg-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.
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.
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)