Daily SEO snapshot for tracked domains to Google Sheets

Build me a deterministic code workflow that logs a daily SEO health snapshot for a set of tracked domains into Google Sheets, so my team has a clean historical time series we can chart directly in Sheets or pull into Looker Studio.

Trigger: a cron schedule that fires every weekday (Monday through Friday) at 7am in my local timezone. No webhook, no polling, just a clock trigger.

Inputs the workflow should accept: a list of tracked domains (for example mycompany.com, competitor-a.com, competitor-b.com), the Semrush regional database to query (default to us), and the Google Sheets spreadsheet id and tab name (for example Sheet1) to append into.

For each domain in the tracked list, the workflow should call Semrush three times. First, call Semrush Backlinks Overview for the domain to get Authority Score, total backlinks, referring domains, and the follow vs nofollow split. Second, call Semrush Get Domain Organic Search Keywords for the domain in the configured database with a small display_limit (around 100 rows is plenty) so we can count how many of those keywords rank in the top 10 and the top 3 by position. Third, call Semrush Get Historical Backlink Data for the same domain and take the latest month data point as a sanity check on the live overview numbers. Keep display_limit small everywhere so we do not burn Semrush API units on data we are not writing.

Be defensive: if Semrush returns the empty-result sentinel (ERROR 50 :: NOTHING FOUND) for any of these calls on a given domain, treat it as zero or blank for that metric and keep going. One quiet domain should not fail the whole run.

After collecting metrics for all domains, call Google Sheets Append Values once (or once per domain, whichever is cleaner) to write rows to the configured tab. Each row is exactly: date (today in YYYY-MM-DD), domain, authority_score, total_backlinks, referring_domains, follow_pct (the follow / (follow + nofollow) ratio as a percentage), organic_keywords_top10, organic_keywords_top3. Use USER_ENTERED so numbers and dates parse correctly. Assume the user has already created the header row, so the workflow only appends data rows.

Important: this is a code workflow, not an agent. The pipeline is fully deterministic. Same domains, same metrics, same column order every day. No LLM reasoning step, no judgement, no summary. Just fetch, compute, append, exit.

Output: nothing user-facing in the run itself. The Google Sheets tab is the artifact. Over time it grows into a charteable time series of Authority Score, backlink volume, and keyword rankings per tracked domain.