The Scenario
You are a digital marketing analyst. Your CMO wants a competitive keyword gap report before the board meeting next Friday. You have three competitor domains in a Google Sheet, plus your own domain. The ask: every keyword where at least one competitor ranks in the top 10, but your site ranks outside the top 20 or does not rank at all.
This is the kind of audit that takes a full day manually. You have until Thursday.
The bad version of this week:
- You open Semrush and pull the top 200 organic keywords for Domain A
- You export a CSV, paste it into a tab labeled "Competitor A"
- You repeat for Domain B and Domain C, then your own domain
- You write a VLOOKUP formula to find keyword overlap across four tabs
- It returns thousands of results and the formula breaks when a keyword has a comma in it
- You spend Thursday afternoon finding the formula error
- You walk into the board meeting with an incomplete pivot table and a very long explanation.
The fast version is one prompt.
The Easy Way: One Prompt in SheetXAI
SheetXAI reads the domain list and pulls each domain's top organic keywords from Semrush, then does the gap analysis and writes only the gaps into a clean output tab.
Open the SheetXAI sidebar and type:
For each domain in column A, fetch the top 200 organic search keywords from Semrush US database and write keyword, position, search volume, and URL into new rows on a sheet named by the domain. Then compare the organic keywords across all domain tabs and write into a new Gaps sheet every keyword where at least one competitor ranks in positions 1–10 but our site ranks below 20 or is absent. Our domain is in cell A1.
SheetXAI creates one tab per domain, fills each with the top 200 organic keywords, runs the gap comparison, and writes the output directly into the Gaps tab.
What You Get
Four tabs of raw organic keyword data (one per domain), plus:
- Gaps tab — every opportunity keyword, with columns for Keyword, CompetitorDomain, CompetitorPosition, SearchVolume, and OurPosition (blank if absent)
The Gaps tab is board-ready. Sort by search volume descending. The biggest opportunities your competitors own and you do not are at the top.
You can filter it further — priority gaps only, or gaps within a specific topic cluster — without re-running anything. The data is all there.
What If the Data Is Not Quite Ready
Gap analyses get complicated when the data is inconsistent. SheetXAI handles the edge cases inline.
When Semrush returns keyword positions using different ranking formats
One competitor's export uses "1–3" as a position range instead of a specific number, because the keyword fluctuates.
After pulling organic keywords for all domains, normalize the position column: if a position value is a range, use the lower end of the range as the numeric value. Then run the gap analysis and write gaps into the Gaps sheet.
When you want gaps grouped by topic cluster
The marketing team thinks in clusters: "SEO tools," "content marketing," "backlink analysis." They want the Gaps tab organized by cluster, not just sorted by volume.
Pull the top 200 organic keywords per domain as above. After building the Gaps tab, group the gap keywords by topic: look at the keyword text and categorize each into one of these clusters: "SEO Tools," "Keyword Research," "Backlink Analysis," "Content Marketing," or "Other." Write the cluster name in a Cluster column. Sort the Gaps tab by Cluster then by SearchVolume descending.
When you only care about non-branded gap keywords
The gap analysis includes a lot of competitor brand terms. You want to exclude those.
Run the gap analysis as above. After building the Gaps sheet, remove any row where the keyword contains the name of any competitor domain or known brand term. Flag removed rows in a separate ExcludedBrandTerms sheet so I can review them separately.
When the full audit needs to include position history to show which gaps are growing
Static positions tell you where things stand today. Growing keywords are worth more.
For each domain in column A, pull the top 200 organic keywords from Semrush. Then for the gap keywords in the Gaps sheet, pull the 3-month position trend for our domain (column A1) from Semrush and write the trend direction — Improving, Declining, or New — into a Trend column. Sort by SearchVolume descending, then by Trend with Improving first.
The pattern: the raw pull, the comparison, and the prioritization happen in sequence inside one prompt. You do not assemble this by hand.
Try It
Get the 7-day free trial of SheetXAI and put your domain and competitor domains in column A of any Google Sheet, then ask it to build the gap report. The Semrush integration is included in every SheetXAI plan. For a related workflow, see how to pull full backlink profiles from Semrush or the Semrush in Google Sheets overview.