Daily SEO snapshot for tracked domains to Google Sheets
Log Authority Score, backlinks, and organic keyword counts for your tracked domains into Google Sheets every weekday morning.
Semrush
Google SheetsMarketingOperationsDaily DigestsResearch & MonitoringData SyncBuild 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?
How many domains can I track?
Can I chart this in Looker Studio or another BI tool?
What happens to my existing header row?
Can I run this more often than daily?
What if Semrush has no data for one of my domains?
Related templates
Every night we run your saved Databricks query and refresh a Google Sheet with the results, so your team reads the latest numbers without a login.
Every morning, automatically record yesterday's match results and each league's latest table into a spreadsheet you can chart or pivot.
Every morning we check your list of IP addresses for abusive activity, update your spreadsheet, and alert your team only about the risky ones.
Automatically add every new Azure DevOps work item to a Google Sheets tracker so your team can filter, sort, and report without opening Azure DevOps.
Check key email accounts against known data breaches every weekday, log every exposure to a spreadsheet, and get a Slack alert when a new breach shows up.
Every weekday morning, your team gets one Slack briefing on who's flying today, their live flight status, and any delays or cancellations to act on.
Stop pulling SEO numbers by hand every Monday morning.
Connect Semrush and Google Sheets once, and Geni logs every tracked domain at 7am on the dot.