The Scenario
You are an in-house SEO lead. The content calendar meeting is Tuesday morning. It is Monday and you need to know which pages drive the most estimated organic traffic to three competitor sites before you can make a coherent case for which content gaps to prioritize.
The competitor domain is in cell A1 of the Analysis tab. You need the top 30 pages — URL, traffic estimate, and ranking keyword count — in the workbook by end of day.
The bad version of Monday:
- You open Ahrefs Site Explorer for competitor 1, go to Top Pages, copy the top 30 rows
- You paste them into Excel, clean up the column formatting
- You do the same for competitors 2 and 3
- You realize your paste for competitor 2 overwrote competitor 1's data
- You walk into Tuesday's meeting with one competitor's data and two apologies.
The fast version is one prompt per competitor.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Excel workbook that reads the domain in the workbook and pulls the top pages report from Ahrefs directly, without you touching Site Explorer.
Open the SheetXAI sidebar and type:
Pull the top 30 pages by estimated organic traffic for the domain in cell A1 of the Analysis tab 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 30 rows, and your data is ready. Run the same prompt with three different competitor domains in A1 — each to its own tab — and you have all three reports before the meeting.
What You Get
A ranked organic pages table with 30 rows per competitor:
- Column A — page URL
- Column B — estimated monthly organic traffic
- Column C — number of ranking keywords for that page
- Sorted — highest traffic pages at the top
Ranking keyword count is the signal. A page with 8,000 estimated traffic but 2 ranking keywords is a single-term target. A page with 1,500 traffic but 90 keywords is a content cluster you are probably not going to beat with one article.
Want the full competitive picture? Ask SheetXAI to also pull the organic competitors list 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 you want to exclude the competitor's blog subdomain
Your competitor's blog is on a subdomain and you want the main site traffic only.
Pull the top 30 pages by estimated organic traffic for the root domain in cell A1 of the Analysis tab from Ahrefs, excluding any URLs that start with blog. or contain /blog/ in the path. List URL, traffic, and keyword count in columns A through C starting at row 2.
When you need a cumulative traffic share view
The content director wants to see which pages account for the top 50% of the competitor's estimated 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 rows where the cumulative share is 50% or less, and "Tail" for the rest.
When all three competitor domains are in a column instead of a single cell
You have three competitors in cells A1, A2, A3 and want separate tabs for each.
For each domain in cells A1, A2, and A3 of the Analysis tab, pull the top 30 pages by estimated organic traffic from Ahrefs. Create a separate tab named after each domain. In each tab, list URL in column A, traffic 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 competitor pages are targeting keywords your site does not rank for.
Pull the top 30 pages for the domain in A1 from Ahrefs and write URL, traffic, and keyword count to columns A through C. Retrieve the organic keywords driving traffic to those pages. Cross-reference against the keywords in the "My Keywords" tab. Flag any competitor keyword not found in my tab in column D with the label "Gap."
The pattern: the top pages pull is one prompt. The gap analysis extends it without opening a second tool.
Try It
Get the 7-day free trial of SheetXAI and open any Excel workbook with a 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 in Excel or the Ahrefs in Excel overview.