The Scenario
You are a head of SEO and your board presentation is two weeks away. The ask is a full competitive backlink benchmark: your site against five competitors, covering total referring domains, domain authority score, dofollow versus nofollow ratio, and the top 10 anchor texts per domain.
You have all six domains in an Excel workbook. You have never had to pull this at scale before.
The bad version of this project:
- You open Semrush and pull the backlink overview for Domain 1
- You type the numbers into the workbook by hand to get the right column format
- You pull the anchor text report for Domain 1, which is paginated
- You spend twenty minutes scrolling and copying 10 anchors manually
- You repeat for five more domains
- By Domain 4 you have a typo in the authority score
- The board deck goes out with a wrong number and you find out during the Q&A.
The fast version is one prompt and a clean workbook.
The Easy Way: One Prompt in SheetXAI
SheetXAI reads the domain list and pulls backlink overview data and anchor text distributions from Semrush for each domain, writing everything into structured tabs in the workbook.
Open the SheetXAI sidebar and type:
For each domain in column A of the Domains tab, fetch the Semrush backlinks overview and write total backlinks, referring domains, authority score, and dofollow percentage into columns B through E. Then for each domain, pull the top 20 anchor texts and their frequency from Semrush and write them as rows in a new Anchors tab with a Domain column so I can compare link-building strategies across competitors.
SheetXAI populates the overview metrics and builds the Anchors tab with 20 rows per domain, all labeled. The board summary writes itself.
What You Get
An enriched Domains tab with:
- Column B — Total Backlinks — raw link count
- Column C — Referring Domains — unique linking root domains
- Column D — Authority Score — Semrush 0–100 domain authority
- Column E — Dofollow % — percentage of links passing equity
Plus a separate Anchors tab with columns for Domain, AnchorText, and Frequency.
The Anchors tab is where the real competitive intelligence lives. If a competitor's top anchor is your brand name with "vs," they are running a comparison SEO strategy. You can see the strategy in the data.
What If the Data Is Not Quite Ready
Backlink audits often need more nuance than a straight pull.
When you want to separate dofollow and nofollow anchor distributions
Your link-building team only cares about dofollow anchors. Mixing in nofollow anchor texts skews the picture.
For each domain in column A of the Domains tab, pull the top 20 anchor texts from Semrush but filter to dofollow links only. Write them into the Anchors tab with a Domain column and a LinkType column set to "Dofollow."
When some domains have fewer than 20 distinct anchors
A newer domain in your comparison set has fewer than 20 distinct anchors. You do not want blanks skewing the averages.
Pull anchor texts for each domain in column A. For any domain with fewer than 20 distinct anchors, write all available anchors and note the actual count in an AnchorCount column in the main Domains tab.
When you want to flag anchor texts that suggest toxic link patterns
Spammy anchors — random strings, non-English exact-match phrases — are worth separating.
After pulling anchor texts for all domains into the Anchors tab, scan for any anchor that appears to be a random string, contains non-Latin characters, or is a pure commercial exact-match phrase with no brand context. Write "Review" in a Flag column for those rows.
When the full benchmark needs data, scoring, and a summary narrative in one pass
The deck needs numbers and prose.
For each domain in column A of the Domains tab, fetch the Semrush backlinks overview and write total backlinks, referring domains, authority score, and dofollow percentage into columns B through E. Pull top 20 anchors per domain into the Anchors tab. Then in a new cell at the top of the Domains tab, write a 3-sentence summary comparing the six domains: which has the strongest link profile, which is growing fastest by referring domain count, and which shows the riskiest anchor distribution.
The pattern: the data pull, the analysis, and the narrative for the deck all come from one prompt.
Try It
Get the 7-day free trial of SheetXAI and put your domain and competitor domains in column A of any Excel workbook, then ask it to build the backlink benchmark. The Semrush integration is included in every SheetXAI plan. For a related workflow, see how to export referring domains sorted by authority score in Excel or the Semrush in Excel overview.