Case and spacing

• Use lowercase for comparisons
• Collapse multiple spaces into one
• Trim leading and trailing spaces

Unicode and punctuation

• Normalize to NFC or NFKD for accents
• Replace fancy quotes and dashes with simple ASCII
• Remove emoji and symbols outside text

See MDN on String.prototype.normalize.

Separators

• Convert “&”, “/”, “+” to space when they join words
• Keep “c++” or “c#” style tokens as exceptions list
• Map “vs”, “versus” to a shared token like “vs”

Safe pairs

• dashboards → dashboard
• analyses → analysis
• policies → policy

Avoid collapsing

• pricing vs price (different intent)
• analytics vs analysis (different meaning)
• report vs reporting (noun vs activity)

Signature idea

• Lowercase
• Strip punctuation and disposable modifiers
• Tokenize, drop stop words, sort tokens
• Join with a single space

Example: “crm for sales teams” and “sales crm team” share signature “crm sales team”.

Fuzzy pass

• Use a distance metric on signatures
• Flag pairs with distance ≤ 2 for manual review
• Whitelist product names to avoid wrong merges

What to keep separate

• “pricing” vs “cost calculator”
• “setup” vs “migration”
• “template” vs “example” when docs differ

Remove years and fluff

// years and recency words
/(19|20)\d{2}\b|latest|newest|updated|update/gi

// quality fluff
/\b(best|top|ultimate|complete|definitive)\b/gi

Normalize separators and dashes

// fancy quotes and dashes to simple
/[“”‘’–—]/g  → replacement: '-'

// joiners to space
/[&+/]/g      → replacement: ' '

Collapse whitespace

/\s{2,}/g → replacement: ' '

Light plural handling

// endings like -s, -es, -ies
/\b([a-z]{3,})s\b/gi     → "$1"
 /\b([a-z]{3,})es\b/gi   → "$1"
 /\b([a-z]{3,})ies\b/gi  → "$1y"

Use only on a copy of the text and keep an exceptions list.

Normalize and strip noise

=LOWER(TRIM(REGEXREPLACE(
  REGEXREPLACE(A2,"(19|20)\d{2}|\b(latest|newest|updated|best|top)\b",""),
  "[^a-z0-9\s\+&/]", "")))

Keeps alphanumerics, spaces, plus, ampersand, slash for the next step.

Unify separators

=REGEXREPLACE(B2,"[&/+]"," ")

Token sort signature

=TEXTJOIN(" ",TRUE,SORT(UNIQUE(
  FILTER(SPLIT(REGEXREPLACE(C2,"\s{2,}"," "), " "),
  LEN(SPLIT(REGEXREPLACE(C2,"\s{2,}"," "), " "))>0,
  NOT(REGEXMATCH(SPLIT(REGEXREPLACE(C2,"\s{2,}"," "), " "), 
  "(^| )(for|to|in|of|the|a|an)$"))
))))

Drops common stop words from the signature only. Display text stays untouched.

JavaScript signature function

const STOP = new Set(["for","to","in","of","the","a","an"]);
const normalize = s => s.toLowerCase()
  .normalize("NFKD")
  .replace(/[“”‘’–—]/g,"-")
  .replace(/[^\w\s\+&/]/g,"")
  .replace(/[&/+]/g," ")
  .replace(/\s{2,}/g," ")
  .trim();

function signature(q){
  const toks = normalize(q).split(" ").filter(t => t && !STOP.has(t));
  return [...new Set(toks)].sort().join(" ");
}

Python near-duplicate flag

from difflib import SequenceMatcher

def near_dup(a, b, threshold=0.92):
    r = SequenceMatcher(None, a, b).ratio()
    return r >= threshold

# use on signatures, not raw strings
# near_dup("crm sales team", "sales crm team") → True

Coverage

• No blank or whitespace only rows
• No duplicate signatures kept
• All brand and year noise handled

Safety

• Pricing and template terms not removed
• Product names whitelisted from stemming
• Geo terms preserved if relevant to scope

Reproducibility

• Each step lives in its own column
• Dictionary files for modifiers and exceptions
• Change log with date and owner

Should I remove brand names

Keep your own brand and major competitor names. They influence intent and will matter later. You can ignore them in the signature if the goal is to dedupe formatting only.

Do I keep misspellings

Keep high volume misspellings as separate rows if the SERP looks different. Otherwise correct them and merge into the canonical spelling.

What about long queries with many modifiers

Create a clean signature and store the full display text. You can later decide if the extra modifiers justify a separate page or belong as on-page copy.

Can I automate everything in Sheets

Most cleaning can be done with formulas. For near duplicates at scale, export signatures to a small script and review flagged pairs manually before deletion.