Weekly Competitor SEO Benchmark in Google Sheets (Ahrefs)

Build me a code workflow that snapshots a fixed competitor SEO benchmark into a Google Sheet every week, so my marketing team has a weekly time series of where we stand against named competitors.

Trigger: a cron schedule that fires every Monday at 7:00am in the user's timezone.

Configurable inputs at the top of the workflow:

1) competitor_domains: an array of root domains to benchmark, including our own domain. Example: ['ourcompany.com', 'competitor-a.com', 'competitor-b.com', 'competitor-c.com']. 2) spreadsheet_id: the Google Sheets spreadsheet ID to append rows to. 3) sheet_name: the tab name within that spreadsheet (default 'Competitor Benchmark').

For each domain in competitor_domains, call these Ahrefs operations against that domain (mode = 'domain'):

1) Get Domain Rating — retrieve domain_rating. 2) Get Metrics — retrieve org_traffic (organic traffic), org_keywords (organic keyword count), and org_traffic_value (organic traffic value in USD). 3) List Referring Domains with limit 1 — we only need the live referring domain count for the target, not the full list. Use the response metadata or a small select to derive the live referring domain count.

Important: the Ahrefs `select` parameter is mandatory on list endpoints and drives the per-row unit cost. Pick the minimum field set for each call so we keep API unit usage low. Do not request fields we are not going to write to the sheet.

After all three calls succeed for a given domain, append one row to the Google Sheet via Google Sheets Append Values with these columns in order: date (today's date in YYYY-MM-DD), domain, domain_rating, organic_traffic, organic_keywords, traffic_value_usd, referring_domains.

Process domains sequentially and continue on per-domain failures: if one domain's Ahrefs call fails, log the error and skip that domain rather than aborting the whole run, so the rest of the competitors still get logged. Respect Ahrefs rate limits (60 requests per minute) and add a small delay between domains if needed.

The sheet's header row (date, domain, domain_rating, organic_traffic, organic_keywords, traffic_value_usd, referring_domains) is created by the user beforehand. The workflow just appends underneath it, so existing history is preserved and the marketing team can pivot or chart the result inside Sheets.

No reasoning is required, this is a deterministic structured pipeline: same domains, same fields, same sheet every week. Output is a clean weekly time series.

Additional information

What does this prompt do?

Snapshots five core SEO numbers (Domain Rating, organic traffic, organic keywords, traffic value, and referring domains) for every competitor on your watchlist, plus your own domain.
Appends one row per competitor per week to a single Google Sheet, so you build a clean time series you can pivot or chart inside Sheets.
Runs automatically every Monday morning at 7am, no one has to remember to pull the numbers.
Pulls only the minimum fields needed from Ahrefs, so each run stays cheap in API credits.

What do I need to use this?

An Ahrefs account on a paid plan with API access. Workspace owners and admins can generate the key in Account settings.
A Google account with edit access to the spreadsheet you want to log into.
A Google Sheet with a header row for date, domain, domain_rating, organic_traffic, organic_keywords, traffic_value_usd, and referring_domains.
Your list of competitor domains (root domains like example.com), including your own.

How can I customize it?

Edit the competitor list to add new rivals or drop ones you no longer care about.
Change the schedule, for example run it daily, every other Monday, or on the first of each month.
Add extra columns like paid traffic, country breakdown, or backlink growth, and the workflow will pull them too.
Point it at a different Google Sheet or tab if you want separate logs per region or business unit.

Frequently asked questions

Do I need a paid Ahrefs plan?

Yes. Only paid Ahrefs subscriptions can create API keys, and the data this workflow pulls (Domain Rating, organic traffic, traffic value, referring domains) lives behind that API.

Can I track my own domain alongside the competitors?

Yes, that is the point. Add your own root domain to the list and the workflow logs you in the same sheet, so each row pivot shows you ranked against the field.

What if one of my competitors blocks crawlers?

It does not matter. Ahrefs maintains its own crawl index, so the numbers come from Ahrefs, not from hitting the competitor's site.

Will this overwrite my historical data?

No. The workflow only appends new rows under your existing data, so every Monday you get one more row per competitor and your history stays intact.

How many competitors can I track?

There is no hard cap from us. Each competitor adds a handful of API calls, so most teams run between five and twenty domains comfortably on a standard Ahrefs plan.

Can I change the day or time it runs?

Yes. Monday at 7am is just the default. You can pick any cron schedule, including multiple times per week if you want a denser time series.