Daily SEO Snapshot to Google Sheets (Semrush + 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.

Additional information

What does this prompt do?

Every weekday at 7am, pulls a fresh SEO snapshot for each domain you track in Semrush.
Captures Authority Score, total backlinks, referring domains, follow link percentage, and top organic keyword counts.
Writes one row per domain to a Google Sheet so you have a clean time series to chart in Sheets or pull into Looker Studio.
Runs the same shape every day, so the tab grows into a clean historical dataset without any manual copy and paste.

What do I need to use this?

A Semrush account on a plan that includes API access (Business plan or the API add-on).
A Google account with edit access to the spreadsheet you want to log into.
The list of domains you want to track, plus the country database you care about (for example US or UK).
A spreadsheet tab with header row already set up (date, domain, authority_score, total_backlinks, referring_domains, follow_pct, organic_keywords_top10, organic_keywords_top3).

How can I customize it?

Change the schedule. Run it earlier, later, only on Mondays, or hourly during a launch week.
Add or remove domains from the tracked list as your portfolio changes.
Add columns you care about, like paid keyword counts or specific country databases, or rename existing columns to match an internal template.
Point it at a different sheet or tab, for example one tab per client or one master tab for everything.

Frequently asked questions

Will this work on a Semrush Pro or Guru plan?

No. Semrush only exposes the API on the Business plan or with the API add-on. Pro and Guru cannot run this workflow.

How many domains can I track?

As many as you want, but every domain consumes Semrush API units each morning. Start with five to ten domains and watch your monthly unit balance before scaling up.

Can I chart this in Looker Studio or another BI tool?

Yes. Connect Looker Studio, Power BI, or any tool that reads Google Sheets straight to the tab. Because every row carries a date, time series charts work out of the box.

What happens to my existing header row?

The workflow appends new rows below your last row of data, so your headers stay in place. Set them up once and the workflow keeps writing under them.

Can I run this more often than daily?

Yes. Switch the schedule to hourly, twice a day, or even weekly. Just remember Semrush data refreshes on a slower cadence, so polling every hour will mostly write the same numbers.

What if Semrush has no data for one of my domains?

The workflow writes the row with zeros or blanks for the missing metrics and keeps going. You will not get a failed run because of one quiet domain.