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 an Excel workbook, 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 Competitor A
- You export a CSV and paste it into a tab
- You repeat for Competitor B, Competitor C, and your own domain
- You write a VLOOKUP across four tabs to find keyword overlap
- The formula breaks on keywords that contain commas
- You debug the formula on Thursday morning
- You walk into the board meeting with an incomplete pivot table and a long explanation.
The fast version is one prompt.
The Easy Way: One Prompt in SheetXAI
SheetXAI reads the domain list from the workbook and pulls each domain's top organic keywords from Semrush, then runs the gap analysis and writes the results into a clean output tab.
Open the SheetXAI sidebar and type:
For each domain in column A of the Domains tab, fetch the top 200 organic search keywords from Semrush US database and write keyword, position, search volume, and URL into new rows on a tab named by the domain. Then compare the organic keywords across all domain tabs and write into a new Gaps tab every keyword where at least one competitor ranks in positions 1–10 but our site (domain in cell A1 of the Domains tab) ranks below 20 or is absent.
SheetXAI creates one tab per domain, fills each with 200 organic keywords, runs the gap comparison, and writes the output into the Gaps tab.
What You Get
Four tabs of raw organic keyword data, 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 SearchVolume descending. The biggest opportunities your competitors own and you do not are at the top.
What If the Data Is Not Quite Ready
Gap analyses get complicated when data is inconsistent. SheetXAI handles the edge cases inline.
When Semrush returns position ranges instead of specific numbers
One competitor's export uses "1–3" as a position range because the keyword fluctuates daily.
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 tab.
When you want gaps grouped by topic cluster
The marketing team thinks in clusters. They want the Gaps tab organized by topic, not just sorted by volume.
Pull the top 200 organic keywords per domain as above. After building the Gaps tab, group gap keywords by topic: categorize each into "SEO Tools," "Keyword Research," "Backlink Analysis," "Content Marketing," or "Other" based on the keyword text. Write the cluster name in a Cluster column. Sort by Cluster then by SearchVolume descending.
When you want to exclude branded gap keywords
The gap analysis includes competitor brand terms that you will never realistically rank for. You want to exclude those.
Run the gap analysis as above. After building the Gaps tab, remove any row where the keyword contains the name of any competitor domain or known brand term. Move those rows to a BrandTerms tab for reference.
When you want gap keywords enriched with difficulty scores for prioritization
A gap keyword with 5,000 monthly searches but keyword difficulty 85 is not a quick win. You need difficulty before you can prioritize.
Pull top 200 organic keywords per domain and run the gap analysis as above. For each gap keyword in the Gaps tab, fetch the Semrush keyword difficulty score and write it into a Difficulty column. Then add a Priority column: "Quick Win" if difficulty is below 40 and search volume above 500, "Long-Term" if difficulty is 40–70, "Skip" if difficulty is above 70 or search volume is below 200. Sort by Priority then SearchVolume descending.
The pattern: the raw pull, the comparison, and the prioritization happen in sequence. 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 Excel workbook, 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 in Excel or the Semrush in Excel overview.