Daily backlink ledger in Google Sheets
Log every new backlink to your domain into a Google Sheet each morning so you have a permanent, queryable record you can pivot and filter at will.
Integrations
Ahrefs Firehose
Google Sheets
Type
Categories
- Marketing
- Operations
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.
What it does
- 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 you’ll need
- 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 to 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.
Use cases
- Research & Monitoring
- Data Sync
- Daily Digests
