Weekly competitor SEO benchmark with Ahrefs and Google Sheets

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.