# Competitor backlink gap analysis in one spreadsheet

> Two COUNTIF formulas, a shell alternative with sort, uniq and comm, and the normalisation bugs that make domains fail to match.

URL: https://claudegpt.si/news/competitor-backlink-gap-analysis-spreadsheet  
Published: 2026-10-26T05:14:24.986Z  
Author: ClaudeGPT Si Newsroom  
Section: Guides  
Topics: Backlinks, Spreadsheets, SEO  
Publisher: ClaudeGPT Si

## Key takeaways

- The gap is domains in at least two competitor lists and not in yours, which COUNTIF or uniq -c and comm can find in seconds.
- Strip protocol and www, lowercase, keep the host only and deduplicate each list before counting.
- Fill every column from the same source; seogeoaeo.ai's Backlink Finder returns 100 linking sites per domain for 70 credits.

A competitor backlink gap analysis is a set operation dressed up as an SEO task. You have one list of linking domains per competitor and one for yourself. You want the domains that appear in at least two competitor lists and not in yours. This walkthrough does it in a spreadsheet, then shows the same thing in a few shell commands, and lists the bugs that quietly break it.

## Inputs

You need linking domains, one row per site, for two to four competitors and for your own domain. All lists must come from the same data source. Each backlink index crawls differently, so a domain missing from your list might just be missing from that tool's crawl, and you would chase a link you already have.

seogeoaeo.ai's [Backlink Finder](https://seogeoaeo.ai/tools/backlink-finder) is a quick way to fill the columns. Give it a domain and it returns the 100 strongest linking sites with a link rank from 0 to 1,000, a spam score out of 100 and the first-seen date for each. It costs 70 credits a run, so four lists cost 280 credits. For your own column you can also export the Links report from Google Search Console, but then you are mixing sources, so treat its matches with care.

## The spreadsheet

Sheet1 layout:

| Column | Contents |
| --- | --- |
| A | Competitor 1 linking domains |
| B | Competitor 2 linking domains |
| C | Competitor 3 linking domains |
| D | Your linking domains |

On Sheet2, put the unique domains from A to C in column A (paste them stacked, then remove duplicates). Then:

```
B2: =COUNTIF(Sheet1!A:C, A2)
C2: =COUNTIF(Sheet1!D:D, A2)
```

B is how many competitors the domain links to. C is 1 if it already links to you. Filter for B at least 2 and C equal to 0. Sort what is left by link rank, highest first.

## The same thing in a shell

If you prefer text files, with one domain per line in `c1.txt`, `c2.txt`, `c3.txt` and `me.txt`:

```bash
norm() { sed -E 's#^https?://##; s#^www\.##; s#/.*$##' "$1" | tr 'A-Z' 'a-z' | sort -u; }
for f in c1 c2 c3 me; do norm $f.txt > $f.n; done
cat c1.n c2.n c3.n | sort | uniq -c | awk '$1>=2 {print $2}' | sort > shared.txt
comm -23 shared.txt me.n > gap.txt
```

`gap.txt` holds domains linking to two or more rivals and not to you.

## Bugs that break the match

- **www and protocol.** `www.example.com` and `example.com` do not match. Strip both before counting.
- **Case.** Lowercase everything.
- **Trailing slashes and paths.** Keep the host only.
- **Duplicates inside one list.** If a competitor list repeats a domain, COUNTIF counts it twice and a single-rival link looks shared. Deduplicate each column first.
- **Subdomains.** `blog.example.com` and `example.com` are different strings. Decide whether to collapse them and do it the same way for every list.
- **Spam.** Drop domains with a spam score of 40 or more, which Backlink Finder marks as likely spam, then eyeball the borderline ones. The score is an estimate.

## Triage the output

The gap file is a list of leads, not a to-do list. Open the page carrying each link and sort by type:

- Edited directories, "best tools" roundups and resource pages: worth an email.
- Pages that mention your product without linking: the easiest ask there is.
- Guest articles: only if you have an uncovered subject.
- News, partner pages, sponsor pages: skip. They follow events or deals, not emails.

Leave out directories that take any submission. Google's spam policies list low-quality directory and bookmark links as link spam, along with paid links that pass ranking credit. Paid placements are fine only with rel="sponsored" or rel="nofollow".

## Limits worth knowing

Backlink Finder covers whole domains, returns the strongest 100 linking sites rather than all of them, and does not give contact details. For a large rival with thousands of linking sites, a suite such as Semrush, whose Backlink Gap compares up to five sites in one report, will show a longer tail. For most small sites, the top 100 per rival is where the realistic leads are, and [per-run pricing](https://seogeoaeo.ai/pricing) starts at $29 a month for 2,000 credits.

## Frequently asked questions

### Why does my gap list include sites that already link to me?

Usually a normalisation mismatch such as www, case or a subdomain, or a different data source for your own column.

### How many rows should I expect?

In a crowded market the filtered list can still run to hundreds. Sort by link rank and start at the top.

## Sources

- [Backlink Finder: see who links to any site](https://seogeoaeo.ai/tools/backlink-finder), seogeoaeo.ai
- [seogeoaeo.ai pricing](https://seogeoaeo.ai/pricing), seogeoaeo.ai
- [Links report](https://support.google.com/webmasters/answer/9049606), Google Search Console Help
- [Spam policies for Google web search](https://developers.google.com/search/docs/essentials/spam-policies), Google Search Central
- [How Backlink Gap can help you](https://www.semrush.com/kb/773-backlink-gap), Semrush
- [Competitor backlinks: how to find the links you're missing](https://seogeoaeo.ai/blog/competitor-backlinks), seogeoaeo.ai