Daily brand reputation watcher for Google search and AI Overviews

Build a deterministic code workflow that monitors how Google ranks our branded queries every weekday morning. The trigger is a cron, Monday through Friday at 7am US Eastern. There is no input payload; the workflow reads everything it needs from a Google Sheet.

Step 1. Read the query list from Google Sheets using Get Values. The source sheet has one row per branded query and contains four kinds of terms: our company name, our executives' names, our product names, and our flagship feature names. The spreadsheet id and the range (for example Queries!A2:B) should be configured at the top of the workflow so a non-technical owner can edit the sheet without touching the workflow. Each row also carries a label column so we can group the alert output later.

Step 2. For every query in that list, call Real-Time Web Search Search with num=10, fetch_ai_overviews=true, and gl=us, hl=en. Capture, for each query: the ordered list of result domains for the top 10 organic results, a boolean for whether an AI Overview was returned, and the full AI Overview text body when present. Handle the response envelope of { status, request_id, data } and read results from data. If a single query errors, log it and keep going so one bad query does not block the rest of the run.

Step 3. Append one snapshot row per query to a separate ledger sheet using Google Sheets Append Values. The ledger columns are: run_date (ISO date in ET), query, label, top_10_domains (comma-separated, in rank order), ai_overview_present (true/false), and ai_overview_text (the full body, or empty). This sheet is the audit trail and the source of truth for tomorrow's diff.

Step 4. Before appending, read the ledger to pull yesterday's snapshot rows (the most recent run_date strictly before today) using Get Values. For each query in today's run, compare against yesterday's row for the same query and detect three change types: (a) a domain that is in today's top 10 but was not in yesterday's, (b) a domain that was in yesterday's top 10 but is not in today's, and (c) a material change to the AI Overview text. Treat a material AI Overview change as either present-to-absent, absent-to-present, or a normalized text similarity below roughly 0.85 (case-folded, whitespace-collapsed) so cosmetic edits do not trigger alerts.

Step 5. If at least one query produced a change, post exactly one Slack message via Send a Message to a configured channel. The message should be a single grouped digest, not one message per query. Format it with a short header ("Brand SERP changes for {today}"), then a section per changed query showing: the query and label, new domains added to page 1 with the rank they entered at, domains that dropped off, and a one-line note about the AI Overview change with a short before/after excerpt if applicable. If no query changed, do not post anything. The channel id should be a workflow config value.

Constraints. Keep the flow strictly deterministic: discrete nodes, no agent reasoning. All thresholds (similarity cutoff for AI Overview changes, top N, schedule) should be named constants at the top of the workflow so they are obvious to tune. This is a reputation and PR monitoring workflow for branded queries, deliberately separate from commercial SEO keyword rank trackers. The value is catching a new domain on page 1, a domain dropping off, or an AI Overview narrative shift for terms where our brand is the subject of the search.