Hands-on: link audit spreadsheets, formulas and Python

0:00 Hands-on: the link audit sheet

Here's a situation you'll meet sooner or later. A new client says, we think our old agency bought links. Can you check? You open their backlink tool and see a toxic score of sixty-something percent and twelve hundred linking domains. Where do you even start? In this lecture you'll build a proper link audit spreadsheet, with formulas that do the heavy lifting, and see the same workflow in Python for bigger sites.

0:31 Why build your own

Why not just trust the toxic score? Because it's one vendor's model, not Google's view. If you disavow or remove links based on it alone, you can throw away good links and miss the ones that actually matter. A professional audit is different. It's a documented, repeatable classification that a human has reviewed. That's what a reconsideration request needs, and it's what a client board meeting needs when someone asks why you did what you did.

1:04 Your source records

Think of it like an accountant's audit. The accountant doesn't trust the company's summary. They pull the source records, reconcile them, flag anomalies and then investigate. Our source records are three things. The Search Console links export, which is Google's own sample, free but not complete. One third party index, like Ahrefs, Semrush, Moz or Majestic, which is bigger and shows anchors. And your own records: invoices, vendor reports, outreach logs. Those records are often the best evidence of links that were bought.

1:40 Step 1: normalise to domains

Step one is normalising. Links come as full URLs, but you make decisions per domain. So in column B you use a formula that extracts the host name and strips www. The lesson text has it ready to paste. It uses REGEXEXTRACT to pull everything between the protocol and the first slash, then LOWER and REGEXREPLACE to tidy it. One caution. Blogging platforms like blogspot or medium host many separate publishers on subdomains, so review those individually.

2:13 Step 2: one row per domain

Step two, one row per domain. In a new tab, use UNIQUE to list every domain, COUNTIF to count links per domain, and VLOOKUP to pull in the tool's metric and estimated traffic. Then add a column that checks whether Google's own sample includes that domain. If you like one formula that does it all, a QUERY with group by and count will give you domains ranked by number of links. Now twelve hundred rows of URLs have become a manageable list of publishers.

2:50 Step 3: flags prompt review

Step three, automatic flags. These don't decide anything. They tell you where to look first. A sitewide flag when a domain sends fifty or more links, which usually means a footer or template. A metric without audience flag when the domain score is high but estimated traffic is tiny. A money anchor flag when anchors contain exact match commercial phrases. And a topic flag for words like casino, loans or guest post in the domain. Then a flag count, so you can sort.

3:26 Anchor analysis

Anchor text deserves its own tab. Give every anchor a type: brand, URL, generic like click here, topical, or commercial for exact money phrases like accountant Manchester. Then a single formula gives you the share of commercial anchors across the whole profile. Natural profiles are dominated by brand names, URLs and generic phrases. When commercial anchors make up a big slice, that's one of the clearest footprints of past link buying. It's also a great before and after chart for the client.

4:02 Step 4: human classification

Step four is the human part. Add a classification column with three options: natural, questionable and unnatural. Add an evidence column, and columns for removal requested, removal status and disavow. The rule of thumb is simple. Unnatural needs evidence, like an invoice, a vendor list, or identical guest posts with money anchors on sites that sell posts. Questionable means look again. And most scraper junk you never built is simply natural noise that Google generally ignores.

4:35 Example 1 (simple)

Worked example one, simple. A local bakery's site has sixty linking domains. The flags light up only three: two sitewide links from a web designer's footer credits and one directory. Manual review says all three are natural. The designer credit is a bit sloppy, so the baker asks for it to be nofollowed, but there's no manual action and no need to disavow. Total time, forty minutes. That's a real audit result too. Sometimes the answer is: you're fine.

5:09 Example 2 (illustrative)

Worked example two, illustrative numbers. Northgate Accountants in Manchester inherits twelve hundred and forty linking domains. The flags surface two hundred and twelve. Manual review classifies one hundred and eighty-one as unnatural: identical guest posts with anchors like accountant Manchester, all on sites with paid post menus. Nineteen are questionable. The rest are natural. Commercial anchors drop from thirty-one percent to under five once those domains are excluded. Removal requests go out, the rest are disavowed at domain level, and the sheet is linked in the reconsideration request.

5:47 Pause and think

Let's pause and test your judgement. Here's a row from an audit sheet. The domain sends three links, all in the body of articles, with anchors like this guide and the company's brand name. The site covers small business finance, and its traffic trend is stable. There are no flags at all. Natural, questionable or unnatural? Natural. Now change one thing. Your client's old invoices show they paid this site two hundred pounds per article. Suddenly it's unnatural, even though no formula flagged it. That's why the evidence column matters more than any formula.

6:28 Watch me do it: build the audit sheet

Watch me do it. I'll build the audit sheet from scratch for a small site. First, in Search Console I go to Links, external links, export, latest links. I paste it into a tab called Raw. Next, in column B I paste the domain formula from the lesson and fill it down. Every long URL becomes a clean domain without www. Then I create a Domains tab. In A2 I type UNIQUE of FILTER over the Raw domains, and three hundred and twelve domains appear. In B2, COUNTIF gives links per domain. I paste my backlink tool's export into a Tool tab and use VLOOKUP to bring in the domain metric and traffic. Now the flags. Sitewide if fifty or more links. Metric without audience if the score is forty plus and traffic under a hundred. Money anchor from the Anchors tab. Then a flag count. I sort by flag count. Twenty one domains have flags. I review each one in a browser and fill classification and evidence. Seventeen are natural, three questionable, one unnatural with a paid post menu. Finally I sample thirty unflagged rows to make sure the flags aren't missing a pattern. Total time: about ninety minutes for a few hundred domains.

7:57 The Python version

For bigger sites, spreadsheets get slow, so the lesson includes a Python version using pandas. It reads the Search Console export and your tool export, normalises every URL to a root domain, classifies anchors with two regular expressions, one for your brand and one for money phrases, then groups by domain with link counts and flags. It writes a link audit CSV sorted with the riskiest domains first. A human still fills in the classification column, and that file then feeds the disavow script from the previous lesson.

8:35 Common mistakes

Common mistakes. Treating flags as verdicts. Auditing URLs instead of domains, which multiplies the work. Mixing two tools' numbers in one column. And losing the evidence column. Recap: collect Google's sample plus one tool, normalise to domains, flag patterns with formulas, classify with evidence, and only then remove or disavow. Try this now. Export your latest links from Search Console, paste them into a sheet, and add the domain formula. You'll have your first one row per publisher view in ten minutes.