The Scenario
You are an in-house SEO lead. The content calendar meeting is Tuesday morning. It is Monday afternoon and you need to know which pages drive the most estimated organic traffic to your three main competitors before you can make a coherent argument for which content gaps to prioritize.
The competitor domain is in cell A1. You need the top 30 pages by estimated organic traffic — URL, traffic estimate, and ranking keyword count — in a table by end of day.
The bad version of Monday afternoon:
- You open Ahrefs Site Explorer for competitor 1
- You go to Top Pages, sort by traffic, manually copy the top 30 rows
- You paste into the sheet, clean up the formatting
- You do the same for competitor 2 and competitor 3
- You realize your paste for competitor 2 overwrote competitor 1's data and you have to redo it
- You walk into Tuesday's meeting with one competitor's data done and apologies for the other two.
The fast version is one prompt per competitor.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your spreadsheet that reads the domain in the sheet and pulls the top pages report from Ahrefs directly, so you do not have to touch Site Explorer.
Open the SheetXAI sidebar and type:
Pull the top 30 pages by estimated organic traffic for the domain in cell A1 from Ahrefs. List the URL in column A, traffic estimate in column B, and ranking keyword count in column C, starting at row 2.
SheetXAI calls Ahrefs, writes the top 30 pages into the sheet, and you have the data. Run the same prompt three times with three different competitor domains in A1 and you have all three reports, each on its own tab.
What You Get
A ranked organic pages table with 30 rows per competitor:
- Column A — the page URL
- Column B — estimated monthly organic traffic
- Column C — number of ranking keywords for that page
- Sorted — highest traffic pages at the top
The ranking keyword count is the signal. A page with 10,000 estimated traffic but only 3 ranking keywords is ranking for one big term — it is a single-keyword target. A page with 2,000 traffic but 80 ranking keywords is a content cluster — you are probably not going to out-rank it on a single article.
Want the full competitive picture? Ask SheetXAI to also pull the organic competitors list — sites that compete for similar keyword sets — and write them to a second tab.
What If the Data Is Not Quite Ready
Competitor analysis always has edge cases. SheetXAI handles them in the same prompt.
When the domain in A1 has multiple subdomains to exclude
Your competitor's blog is on a subdomain and you want the main site only, not the blog traffic.
Pull the top 30 pages by estimated organic traffic for the root domain in cell A1 from Ahrefs, excluding any URLs that start with blog. or /blog/. List URL, traffic estimate, and keyword count in columns A through C starting at row 2.
When you need a quick "share of voice" view
The content director wants to see which pages account for the top 50% of the competitor's estimated organic traffic.
After writing the top 30 pages to columns A through C, calculate the cumulative share of total traffic in column D. Add a column E that says "Top 50%" for all rows where the cumulative share is 50% or less, and "Tail" for the rest.
When the competitor list is in a separate column instead of a single cell
You have three competitors in cells A1, A2, A3 and want separate sheets for each.
For each domain in cells A1, A2, and A3, pull the top 30 pages by estimated organic traffic from Ahrefs. Create a separate sheet for each domain named after the domain. In each sheet, list URL in column A, traffic estimate in column B, and keyword count in column C.
When the meeting also needs a content gap callout
The content lead wants to know which URLs from the competitor's top pages are targeting keywords your site does not rank for at all.
Pull the top 30 pages for the domain in A1 from Ahrefs and write URL, traffic, and keyword count to columns A through C. Then retrieve the organic keywords driving traffic to each of those pages. Cross-reference against the keywords in the "My Keywords" tab. Flag any competitor keyword not found in my sheet in column D with the label "Gap."
The pattern: the top pages pull is one prompt. The gap analysis extends it without a new session.
Try It
Get the 7-day free trial of SheetXAI and open a sheet with any competitor domain in cell A1, then ask it to pull the top organic pages from Ahrefs. The Ahrefs integration is included in every SheetXAI plan. For related workflows, see how to build a keyword gap analysis table or the Ahrefs in Google Sheets overview.