Daily backlink ledger in Google Sheets (Ahrefs Firehose)

Build me a code workflow that maintains a permanent, append-only backlink ledger for my domain in a Google Sheet. No AI, no summarization, no judgement. Just a faithful row-per-backlink record I can pivot in Sheets.

Trigger: cron, daily at 06:00 in my local timezone.

Inputs the workflow should accept as parameters: (1) my domain, e.g. mybrand.com, (2) the Google Sheets spreadsheet ID, (3) the sheet/tab name to append to, default 'Backlinks'.

Step 1. Use Ahrefs Firehose List Rules (after resolving the tap token via List Taps) to find an existing rule tagged for this domain. If a rule already exists, reuse it. If not, create one with a Lucene query that captures both anchor and body mentions of my domain, for example: added_anchor:mybrand.com OR added:\"mybrand.com\". The rule should be scoped to the configured tap.

Step 2. Call Ahrefs Firehose Stream against that rule with a recency filter of recent:24h to fetch every web page change in the last 24 hours that matched the rule. Read the SSE stream until it drains for the 24h window, collect all matching events, then close the connection. Treat the stream as a finite batch for this run, not a long-lived listener.

Step 3. For each matched event, deterministically map fields to one row. Columns, in order: timestamp of the change (publish_time, ISO 8601), source page URL (url), source domain (domain), page title (title), anchor text (added_anchor, joined with a comma if multiple), page type (page_type), page category (page_category), language (language). If a field is missing, write an empty string. Do not transform or rewrite values, write them verbatim.

Step 4. Use Google Sheets Append Values to append all rows in a single call to the configured spreadsheet and tab, range '<tab>!A:H', valueInputOption=RAW, insertDataOption=INSERT_ROWS. The first time the workflow runs against an empty tab, also write a header row with the column names above.

Behavior rules: append-only, never overwrite. If zero events match, finish cleanly with no rows appended and no errors. If the tap has no rule yet, create the rule once and continue with an empty result for this run (the next run will pick up matches). Surface clear errors if Firehose or Sheets credentials are missing or invalid.

Output of the workflow run: a count of rows appended and the spreadsheet URL, for the run history.

Additional information

What does this prompt do?

Runs every morning at 6:00 and looks for any new backlink that pointed to your domain in the last 24 hours.
Adds one row per backlink to a Google Sheet, with the date, source page, source domain, page title, anchor text, page type, page category, and language.
Keeps your ledger append-only, so old entries are never overwritten and your full backlink history stays in one place.
Skips summarization and scoring on purpose, you get the raw record so SEO and marketing teams can sort, pivot, and analyze it however they want.

What do I need to use this?

A Google account with permission to edit the Google Sheet you want to use as your ledger.
An Ahrefs Firehose account so we can watch new backlinks as they appear across the web.
The domain you want to track, for example yourbrand.com.

How can I customize it?

Change the run time, for example to hourly or to 8:00 in your timezone instead of 6:00.
Track more than one domain by adding extra brand names or domains to the watch list.
Adjust which columns get written, for example add the destination URL on your site or remove the language column.

Frequently asked questions

Will this overwrite my existing backlink history?

No. Each run only appends new rows to the bottom of the sheet, so anything you already have is preserved.

How fresh is the data?

Each morning's run captures new backlinks discovered in the previous 24 hours, so your ledger stays close to real time.

Can I track multiple domains or brand names?

Yes. You can extend the watch list to include extra domains, subdomains, or brand mentions, and each match will get its own row.

What if a backlink shows up twice?

The workflow writes whatever the source reports, so if you want strict de-duplication you can add a quick filter or pivot in Sheets, or extend the workflow to skip rows that already exist.

Do I need any technical setup beyond connecting accounts?

No. Once you connect Ahrefs Firehose and Google Sheets and tell us which spreadsheet to use, the workflow runs on its own every morning.