The Scenario
You are a content ops manager at a mid-size company. Leadership has asked for a content pruning and consolidation project: find the pages that rank but underperform, the pages that used to rank and lost visibility, and the pages that have never broken through. You need every page currently ranking in Google's top 100 for your domain, with estimated organic traffic, number of ranking keywords, and the top keyword per URL.
The domain is in cell A1. The project kickoff is Monday. Today is Thursday.
The bad version of this project:
- You open Semrush and pull the Organic Pages report for your domain
- The report has 847 pages and exports in a format with 12 columns you do not need
- You download the CSV, delete the columns you do not want, reorder the ones you keep
- You realize "top keyword" is not a column in the export — it is a separate click per row
- You manually note the top keyword for your top 50 pages
- You go into Monday's kickoff with data for 50 pages instead of 847 and a spreadsheet that looks like a work in progress.
The fast version is one prompt.
The Easy Way: One Prompt in SheetXAI
SheetXAI reads the domain from cell A1 and pulls the full organic pages report from Semrush, writing only the columns you need into the sheet.
Open the SheetXAI sidebar and type:
Fetch the Semrush organic pages report for the domain in cell A1 and write each ranking URL, its estimated traffic, keyword count, and top keyword into columns A through D. Include all pages currently ranking in the top 100.
SheetXAI pulls the full pages report and writes exactly the four columns you asked for, one row per ranking URL, all formatted consistently.
What You Get
A clean sheet with:
- Column A — URL — the ranking page's full URL
- Column B — EstimatedTraffic — monthly organic traffic estimate from Semrush
- Column C — KeywordCount — number of keywords this URL ranks for
- Column D — TopKeyword — the single keyword driving the most traffic to this URL
Sort by EstimatedTraffic descending. Your top 20 pages drive the bulk of your organic traffic. The pages at the bottom with KeywordCount of 1 and EstimatedTraffic under 10 are your pruning candidates.
Sort by KeywordCount descending for a different view: pages that rank for many keywords but have low traffic may have cannibalization issues. Pages with high traffic from a single keyword are fragile — one ranking drop matters.
What If the Data Is Not Quite Ready
Content audits need segmentation before they are actionable.
When you want to separate blog posts from product and category pages
Your site has mixed content types and you want to analyze them separately. Blog posts live at /blog/, product pages at /products/, category pages at /category/.
Fetch the Semrush organic pages report for the domain in A1. Write URL, estimated traffic, keyword count, and top keyword into columns A through D. Then in column E, categorize each URL: "Blog" if the URL contains /blog/, "Product" if it contains /products/, "Category" if it contains /category/, and "Other" for everything else. Sort by ContentType then EstimatedTraffic descending.
When you want to flag pages that have very few keywords but meaningful traffic
A page ranking for only 1–2 keywords but generating significant traffic is a consolidation risk. If that ranking slips, all the traffic goes.
After pulling the organic pages report, add a Risk column: write "Single-Keyword Risk" for any URL with keyword count of 1–2 and estimated traffic above 200. Write "Thin" for any URL with keyword count above 5 but estimated traffic below 20. Leave the rest blank.
When you want to pull pages for multiple domains to compare content depth
You want to compare your organic pages inventory against one competitor to see who has broader content coverage.
Fetch the Semrush organic pages report for each domain in column A. For each domain, write URL, estimated traffic, keyword count, and top keyword into new rows on a Pages sheet with a Domain column. After building the sheet, count the total ranking pages per domain and write the summary at the top of the Pages sheet.
When the full content audit needs pages data, risk flags, and a pruning recommendation in one pass
The content team needs a ready-to-act audit, not raw data.
Fetch the Semrush organic pages report for the domain in A1 and write URL, estimated traffic, keyword count, and top keyword into columns A through D. Categorize each URL by section (/blog/, /products/, /category/, Other) in column E. Flag "Prune" in column F for any URL with estimated traffic below 5 and keyword count of 1. Flag "Consolidate" for URLs with estimated traffic below 20 but keyword count above 10 (likely cannibalization). Sort by Flag then EstimatedTraffic descending.
The pattern: the data pull, the risk flagging, and the action recommendation all come from one prompt. The content team starts Monday already knowing which pages to act on.
Try It
Get the 7-day free trial of SheetXAI and put any domain in cell A1, then ask it to pull the organic pages report from Semrush. The Semrush integration is included in every SheetXAI plan. For a related workflow, see how to map the geographic distribution of referring domains or the Semrush in Google Sheets overview.