--- title: "Hands-on: link audit spreadsheets, formulas and Python" description: "Why build your own audit sheet \"Toxic link\" scores from tools are opinions from a vendor's model. A professional link audit is a documented, repeatable…" url: https://optimizeall.com/learn/off-page-seo-and-link-building/link-audit-spreadsheets-and-formulas updated: 2026-10-05 --- Off-Page SEO & Link Building · Spam policies, penalties and disavow · lesson 6 of 17 · 14 min # Hands-on: link audit spreadsheets, formulas and Python ## Why build your own audit sheet "Toxic link" scores from tools are opinions from a vendor's model. A professional link audit is a **documented, repeatable classification** of every linking domain that a human has reviewed — the kind of evidence a reconsideration request or a client board meeting needs. This lesson gives you a working template in Google Sheets and the same workflow in Python for larger sites. ## Step 1: Collect the exports | Source | Where | What you get | |---|---|---| | Google Search Console | **Links → External links → Export**: "More sample links" and "Latest links" (the latter includes crawl dates) | Google's own *sample* of links; free, not complete | | Third-party index | Ahrefs, Semrush, Moz or Majestic referring-domains and backlinks exports | Larger index, anchors, first seen/lost, estimated traffic | | Your records | Past vendor reports, invoices, outreach logs | Evidence of links you (or a vendor) built | Use **one** third-party tool consistently; indexes differ and mixing them mid-audit creates noise. ## Step 2: Normalise to root domains Put all linking URLs in one tab (`Raw`, column A). In column B extract the hostname and strip `www.`: ```text B2: =LOWER(REGEXREPLACE(REGEXEXTRACT(A2, "^(?:https?://)?([^/:?#]+)"), "^www\.", "")) ``` Hostnames like `blog.example.co.uk` stay as they are — review subdomains of large platforms (blogspot, medium, wordpress.com) individually, because each is a separate publisher. ## Step 3: One row per domain In a `Domains` tab: ```text A2: =UNIQUE(FILTER(Raw!B2:B, Raw!B2:B<>"")) B2: =COUNTIF(Raw!B:B, A2) // links from this domain C2: =IFERROR(VLOOKUP(A2, Tool!A:D, 2, FALSE), "") // domain metric from your tool tab D2: =IFERROR(VLOOKUP(A2, Tool!A:D, 3, FALSE), "") // est. organic traffic E2: =IF(COUNTIF(GSC!B:B, A2)>0, "yes", "no") // does Google's sample include it? ``` Or summarise in one formula with `QUERY`: ```text =QUERY(Raw!A:C, "select B, count(A) where B is not null group by B order by count(A) desc label count(A) 'links'", 1) ``` ## Step 4: Automatic flags (they prompt review, they don't decide) ```text F Sitewide? =IF(B2>=50, "sitewide/template?", "") G Metric-no-audience =IF(AND(N(C2)>=40, N(D2)<100), "metric without traffic", "") H Commercial anchor =IF(COUNTIFS(Anchors!A:A, A2, Anchors!C:C, "commercial")>0, "money anchor", "") I Risky TLD/topic =IF(REGEXMATCH(A2, "(casino|loan|bet|pills|seo-links|guestpost)"), "topic flag", "") J Flag count =COUNTA(F2:I2)-COUNTBLANK(F2:I2) ``` Classify anchors in an `Anchors` tab with a helper column: `brand` (contains your brand name), `url`, `generic` ("click here", "this guide"), `topical`, or `commercial` (exact-match money phrases). The share of commercial anchors is one of the clearest footprints of past link buying: ```text =COUNTIF(Anchors!C:C, "commercial") / COUNTA(Anchors!C2:C) ``` ## Step 5: Human classification Add columns `K: Classification` (data validation: `natural`, `questionable`, `unnatural`), `L: Evidence`, `M: Removal requested (date)`, `N: Removal status`, `O: Disavow (Y/N)`. Rules of thumb: - **Natural**: editorial, relevant or at least plausibly organic; includes most scraper junk you did not build (Google generally ignores it). - **Unnatural**: you have evidence it was bought, swapped or placed (invoices, vendor lists, identical guest posts with money anchors, networks). - **Questionable**: needs a second look — do not disavow on suspicion alone. Sort by `J` (flag count) descending and review the flagged rows first; then sample 10% of unflagged rows to check the flags aren't missing a pattern. ## Step 6: The same workflow in Python (large sites) ```python import re import pandas as pd GSC_FILE = "gsc_latest_links.csv" # first column = linking page URL TOOL_FILE = "tool_backlinks.csv" # adjust column names below to your export TOOL_URL_COL, TOOL_ANCHOR_COL = "Referring page URL", "Anchor" BRAND = re.compile(r"northgate", re.I) MONEY = re.compile(r"(accountant|tax return|bookkeeping)\s+(manchester|uk|near me)", re.I) def root(url: str) -> str: host = re.sub(r"^https?://", "", str(url)).split("/")[0].split(":")[0].lower() return host.removeprefix("www.") gsc = pd.read_csv(GSC_FILE) tool = pd.read_csv(TOOL_FILE) gsc["domain"] = gsc.iloc[:, 0].map(root) tool["domain"] = tool[TOOL_URL_COL].map(root) def anchor_type(a: str) -> str: a = str(a) if BRAND.search(a): return "brand" if MONEY.search(a): return "commercial" if a.startswith("http"): return "url" return "other" tool["anchor_type"] = tool[TOOL_ANCHOR_COL].map(anchor_type) by_domain = (tool.groupby("domain") .agg(links=("domain", "size"), commercial=("anchor_type", lambda s: (s == "commercial").sum())) .reset_index()) by_domain["in_gsc_sample"] = by_domain["domain"].isin(set(gsc["domain"])) by_domain["flag_sitewide"] = by_domain["links"] >= 50 by_domain["flag_money"] = by_domain["commercial"] > 0 by_domain["classification"] = "" # filled in by a human reviewer by_domain.sort_values(["flag_money", "links"], ascending=False).to_csv("link_audit.csv", index=False) print(by_domain.head(20)) ``` The output feeds straight into the disavow script in the previous lesson once humans have filled in `classification`. ## Worked example (illustrative) Northgate Accountants (Manchester) inherits 1,240 linking domains. Flags surface 212 domains; manual review classifies 181 as unnatural (identical "guest posts" with anchors like *accountant Manchester*, all on sites selling posts), 19 as questionable and the rest as natural. Commercial anchors fall from 31% of all anchors to under 5% once those domains are excluded from the calculation — a clear before/after chart for the client. Removal requests go out; the rest are disavowed at domain level; the sheet is linked in the reconsideration request. ## Common mistakes - Treating automatic flags as verdicts instead of prompts for review. - Auditing URLs instead of domains, which multiplies the workload by the number of sitewide links. - Mixing two third-party tools' numbers in one column. - Losing the evidence column — the "why" is what makes the audit credible. ## Video lecture: Hands-on: link audit spreadsheets, formulas and Python Lecture coming soon · 14 chapters · about 9 minutes. Read the full transcript below. 1. Hands-on: the link audit sheet 2. Why build your own 3. Your source records 4. Step 1: normalise to domains 5. Step 2: one row per domain 6. Step 3: flags prompt review 7. Anchor analysis 8. Step 4: human classification 9. Example 1 (simple) 10. Example 2 (illustrative) 11. Pause and think 12. Watch me do it: build the audit sheet 13. The Python version 14. Common mistakes ## Lecture transcript ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ## Key takeaways - A link audit is a documented, human classification of every linking domain, not a vendor toxicity score. - Combine Search Console exports with one consistent third-party index and normalise to root domains. - Use formulas to flag sitewide links, money anchors, metric-without-traffic and risky topics, then review manually. - Record evidence and removal status per domain; the sheet feeds disavow files and reconsideration requests. ## Try it Build the audit template for a real or demo site: import one GSC export and one tool export, add the normalisation and flag formulas, and classify the 20 most-flagged domains with evidence. - [Previous: Manual actions, disavow and recovery](https://optimizeall.com/learn/off-page-seo-and-link-building/manual-actions-disavow-and-recovery) - [Next: Linkable assets and data-driven content](https://optimizeall.com/learn/off-page-seo-and-link-building/linkable-assets-and-data-driven-content) - [All lessons of Off-Page SEO & Link Building](https://optimizeall.com/learn/off-page-seo-and-link-building)