Deduplicating a messy CRM with geocoding and stable keys

Every CRM over two years old has the same problem. Marketing imports a list from a trade show. Sales manually enters leads from a conference. A SaaS integration pushes records from a web form. Operations runs a bulk upload from the old system. Four months later, an account manager is on the phone with a prospect and discovers there are six records in the CRM, all for the same office building, each with a slightly different address string. Two are marked "qualified", one is "closed-lost", one belongs to a rep who left, and two are genuinely new contacts who should be separate people.

String deduplication does not solve this. Normalising "St." to "Street" and lowercasing everything catches the easy cases, but it collapses on anything with a suite number, a directional prefix, a missing zip code, or an international format. Fuzzy string matching is worse — it introduces false merges and requires a threshold you have no principled way to choose. It also gives you nothing useful when the same physical location appears under a trading name in one record and a registered company name in another.

The pattern that actually works is: geocode every address, build a stable key from the normalised output, and merge on the key. This post walks through exactly that — from a messy CRM export to a clean, keyed dataset — using CSV2GEO's batch geocoding, the REST API, and the Web batch tool for quick runs. The merge logic stays in your hands; the key derivation and the normalisation are handled by the geocoder.

Why string matching fails and geocoding does not

The failure mode of string matching is easy to demonstrate. Take a real address:

"123 Main Street NW, Suite 400, Atlanta, GA 30303"
"123 Main St NW Ste 400 Atlanta Georgia 30303"
"123 main st nw #400, atlanta, ga, 30303"
"Main Street, Atlanta, 30303"

These four strings have an edit distance that ranges from modest to large. A fuzzy matcher with a tight threshold will miss the third and fourth. A loose threshold will collapse things that should not be collapsed. You end up tuning the threshold against your specific data, and when you import the next trade-show list in a different format, you start again.

Now geocode all four. Assuming the geocoder handles them correctly — and a geocoder backed by 461 million addresses with strong normalisation handles this class of input well — you get back latitude and longitude within a few metres of each other, the same normalised address string, and the same or equivalent place identifier. Build your dedup key from the rounded coordinate: round(lat, 4), round(lng, 4) gives you a cell of roughly 11 metres on a side. In a dense urban area that might contain two buildings; at 5 decimal places you are down to 1 metre. Pick the precision that fits your data density.

The insight that makes this work is that a geocoder's job is exactly normalisation — it was designed to collapse every surface form of an address to a canonical location. That is a harder problem than string matching, and it has already been solved by the geocoder. You are just extracting the key it produces.

What a stable geocode key looks like

A geocode key for deduplication purposes has three components.

Normalised coordinate, rounded. The lat/lng returned by the geocoder, rounded to 4-5 decimal places depending on how finely you want to distinguish neighbouring buildings. Four decimal places is the right default for B2B CRMs where addresses are offices and buildings. Five decimal places is better for B2C where two units in the same building are distinct accounts.

Confidence band. The geocoder returns a confidence score with every result. A score above 0.8 means the geocoder matched at rooftop or parcel level — the key is stable and you can merge automatically. A score between 0.5 and 0.8 means the match was at street or postal code level — the key is usable but you should queue those records for human review before merging. Below 0.5 the address is ambiguous enough that merging on the key will produce errors; those records need manual address correction first.

Component hash, optional. For large enterprise accounts with multiple buildings on one campus, the rounded coordinate alone will collapse them into one record. Adding a hash of the normalised street number and street name to the key splits them correctly. The geocoder's normalised output gives you clean components to hash even when the input was dirty.

A minimal key in Python:

import hashlib

def dedup_key(lat, lng, house_number, street, confidence):
    if confidence < 0.5:
        return None  # do not key low-confidence records
    lat_r = round(lat, 4)
    lng_r = round(lng, 4)
    addr_part = f"{house_number}|{street.upper().strip()}"
    addr_hash = hashlib.md5(addr_part.encode()).hexdigest()[:8]
    return f"{lat_r},{lng_r}|{addr_hash}"

Two records with the same key are the same physical location at the same street address, regardless of how the original address string was typed. You can now GROUP BY dedup_key in SQL, or pick the "winner" record by recency or data completeness, and merge.

Option A: the web batch tool for a quick first pass

If you are running a one-off CRM clean-up for a dataset under a few hundred thousand rows, the web batch tool is the fastest path. No code required for the geocoding step itself.

Go to csv2geo.com, sign in, and upload your CRM export as a CSV or Excel file. Map the address columns — the tool accepts a single combined address field or separate street, city, state, zip columns. One credit is consumed per address row. Click geocode, wait for the job to complete, and download the enriched CSV.

The download will contain your original columns plus the geocoder's output: normalised address, latitude, longitude, confidence score, and match type. You now have everything you need to build the dedup key in a spreadsheet formula or a short Python script, without having written any HTTP code.

This path is appropriate when:

• The dataset is a bounded one-off clean-up, not an ongoing sync
• You want to inspect the geocoded output before committing to a merge
• The team doing the merge is in marketing ops and is more comfortable with CSV manipulation than REST calls

For ongoing pipelines — new leads coming in daily from multiple sources — the REST API path is the right architecture.

Option B: the REST API for ongoing pipelines

The REST endpoint for batch geocoding accepts a single address per call and returns the same normalised fields. For a pipeline that runs continuously, you geocode each new record at ingest time, compute the dedup key, look it up in your key store, and either merge the incoming record with the existing account or create a new record.

Step 1 — geocode a single address

curl -G "https://csv2geo.com/api/v1/geocode" \
  --data-urlencode "q=123 Main St NW Ste 400 Atlanta GA 30303" \
  --data-urlencode "api_key=$CSV2GEO_API_KEY"

Response:

{
  "results": [
    {
      "formatted": "123 Main Street NW, Suite 400, Atlanta, GA 30303, US",
      "lat": 33.7490,
      "lng": -84.3880,
      "confidence": 0.94,
      "match_type": "rooftop",
      "components": {
        "house_number": "123",
        "road": "Main Street NW",
        "unit": "Suite 400",
        "city": "Atlanta",
        "state": "Georgia",
        "postcode": "30303",
        "country_code": "us"
      }
    }
  ]
}

The formatted field is the canonical address string. The lat/lng and confidence are what you need for the dedup key. The components object is what you use to build the optional address hash.

Step 2 — build the key in Python

import os
import hashlib
import requests

API = "https://csv2geo.com/api/v1/geocode"
KEY = os.environ["CSV2GEO_API_KEY"]

def geocode_and_key(address_string):
    r = requests.get(
        API,
        params={"q": address_string, "api_key": KEY},
        timeout=15,
    )
    r.raise_for_status()
    data = r.json()
    if not data.get("results"):
        return None, None, 0.0

    result = data["results"][0]
    lat = result["lat"]
    lng = result["lng"]
    confidence = result.get("confidence", 0.0)
    components = result.get("components", {})

    house_number = components.get("house_number", "")
    road = components.get("road", "")

    if confidence < 0.5:
        return None, result.get("formatted"), confidence

    lat_r = round(lat, 4)
    lng_r = round(lng, 4)
    addr_part = f"{house_number}|{road.upper().strip()}"
    addr_hash = hashlib.md5(addr_part.encode()).hexdigest()[:8]
    key = f"{lat_r},{lng_r}|{addr_hash}"
    return key, result.get("formatted"), confidence

The function returns a (key, formatted_address, confidence) triple. Callers branch on confidence < 0.5 (skip/queue), 0.5 <= confidence < 0.8 (key exists but flag for review), and confidence >= 0.8 (automatic merge candidate).

Step 3 — the same call in Node

const API = 'https://csv2geo.com/api/v1/geocode';
const KEY = process.env.CSV2GEO_API_KEY;

async function geocodeAndKey(addressString) {
  const url = `${API}?q=${encodeURIComponent(addressString)}&api_key=${KEY}`;
  const r = await fetch(url);
  if (!r.ok) throw new Error(`http ${r.status}`);
  const data = await r.json();
  if (!data.results?.length) return { key: null, formatted: null, confidence: 0 };

  const result = data.results[0];
  const { lat, lng, confidence = 0, components = {}, formatted } = result;

  if (confidence < 0.5) return { key: null, formatted, confidence };

  const latR = lat.toFixed(4);
  const lngR = lng.toFixed(4);
  const houseNum = components.house_number ?? '';
  const road = (components.road ?? '').toUpperCase().trim();
  const addrPart = `${houseNum}|${road}`;

  // Node 21+ has Web Crypto built-in; for older versions use the `crypto` module
  const encoder = new TextEncoder();
  const hashBuffer = await crypto.subtle.digest('SHA-256', encoder.encode(addrPart));
  const hashHex = Array.from(new Uint8Array(hashBuffer))
    .map(b => b.toString(16).padStart(2, '0'))
    .join('').slice(0, 8);

  return { key: `${latR},${lngR}|${hashHex}`, formatted, confidence };
}

Both implementations produce the same key structure from the same input data. You can run them in parallel across services written in different languages and the keys will match.

Step 4 — process a CRM export in bulk

For a batch run over an existing export, wrap the geocode calls in a concurrency-limited loop. The free tier gives you 3,000 calls per day; paid tiers scale from there. A dataset of 50,000 CRM records at modest concurrency finishes well within a day on the free tier split over two days, or in a single run on a paid plan.

import csv
import time
from concurrent.futures import ThreadPoolExecutor, as_completed

def process_row(row):
    address = row.get("address") or (
        f"{row.get('street','')} {row.get('city','')} "
        f"{row.get('state','')} {row.get('zip','')}"
    ).strip()
    key, formatted, confidence = geocode_and_key(address)
    return {**row, "dedup_key": key, "normalised_address": formatted,
            "geocode_confidence": confidence}

with open("crm_export.csv") as fin, open("crm_keyed.csv", "w", newline="") as fout:
    reader = csv.DictReader(fin)
    extra_fields = ["dedup_key", "normalised_address", "geocode_confidence"]
    writer = csv.DictWriter(fout, fieldnames=reader.fieldnames + extra_fields)
    writer.writeheader()
    rows = list(reader)

    with ThreadPoolExecutor(max_workers=8) as pool:
        futures = {pool.submit(process_row, r): r for r in rows}
        for future in as_completed(futures):
            try:
                writer.writerow(future.result())
            except Exception as e:
                original = futures[future]
                original.update({"dedup_key": None,
                                  "normalised_address": None,
                                  "geocode_confidence": 0.0})
                writer.writerow(original)
            time.sleep(0.01)  # light throttle; tune to your plan's rate limit

The output CSV has one new column per record: dedup_key, normalised_address, and geocode_confidence. Everything else is unchanged. You have not touched your CRM yet — this is a staging file.

Step 5 — identify duplicates and decide on the merge strategy

Load the output into your analysis tool of choice — SQL, pandas, a spreadsheet. Group by dedup_key and count rows:

SELECT
    dedup_key,
    COUNT(*)            AS record_count,
    MIN(created_at)     AS oldest_record,
    MAX(updated_at)     AS most_recently_touched,
    AVG(geocode_confidence) AS avg_confidence
FROM crm_keyed
WHERE dedup_key IS NOT NULL
GROUP BY dedup_key
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC;

The result set is your duplicate report. A dedup_key appearing 12 times means twelve CRM records that all resolve to the same physical address. You now have to decide the merge policy:

• Winner by recency: keep the most recently updated record, link contacts from the others to it.
• Winner by completeness: score each record by how many non-null fields it has; keep the richest.
• Winner by lead stage: keep the furthest-progressed record in the sales funnel.

The geocoder does not make this decision — it produces the key. The merge policy is a business rule that belongs to your ops team. What the geocoder gives you is the ability to make that decision confidently, because the grouping is based on real-world location identity rather than a fuzzy string heuristic.

Handling low-confidence records — the queue that saves you

Every address set has a tail of genuinely ambiguous inputs. A rural route number. A PO Box that looks like a street address. A city name with no street. An address for a country the geocoder covers but the record has a typo in the postcode that shifts the match. These are the records where confidence < 0.5 or match_type comes back as city or country rather than rooftop or street.

Do not merge on these. Do not discard them either.

Queue them for a human review step. The right workflow is:

1. Filter the output CSV for geocode_confidence < 0.5 or dedup_key IS NULL.
2. Send those rows to a review queue — a shared spreadsheet, a Jira board, a Retool app.
3. A human corrects or confirms the address.
4. Re-run just those records through the geocoder with the corrected input.
5. Assign the resulting key and merge.

In a typical CRM export from a mature B2B company, this low-confidence tail is 3–8% of records. It is also the 3–8% that is most likely to contain either genuinely new accounts you have never seen or genuinely garbage entries from years of un-governed data entry. Handling it carefully is worth the investment.

The Geocoding Confidence Scores Explained post covers the full confidence rubric — what each threshold means, how to use match type alongside the score, and how to communicate confidence to non-technical stakeholders.

Keeping keys stable across re-runs

A dedup key is only useful if it is stable — the same address produces the same key every time you geocode it, so you can JOIN your historical merge log against new imports without re-processing everything.

Two things to watch.

Coordinate rounding. If you geocode the same address on two different days and the geocoder returns 33.74901 one day and 33.74899 the next, your 4-decimal-place rounding will collapse both to 33.7490. This is by design — the rounding absorbs minor float variance in the geocoding engine. If you use raw unrounded coordinates as keys, you will get instability. Round.

Address hash components. Use the geocoder's normalised output components for the hash, not the raw input. "Main Street NW" and "Main St NW" will both resolve to the same normalised road field in the geocoder's response. If you hash the raw input you get two different hashes for the same physical address. Hash the components.road from the response.

The Idempotent Geocoding — Safe to Retry post covers the broader pattern of making geocoding calls safe to re-run — important for the batch jobs that process your CRM export, where a mid-run failure should not produce inconsistent keys in the output.

Cost and scale

The maths for a CRM clean-up project is straightforward.

A 100,000-record CRM export is 100,000 geocoding calls. At 3,000 free calls per day, you can run it in 34 days on the free tier — practical for a staged pilot but not for a deadline-driven clean-up. A single paid month starting at $54 for 100,000 calls covers the whole dataset in one run, with credits left over for testing and re-processing the low-confidence tail.

Ongoing deduplication — one geocode call per new lead at ingest — is a rounding error on any paid plan. If your CRM takes in 1,000 new leads per week, that is roughly 4,000 geocoding calls per month, well within the entry bracket.

The free tier (no credit card, 3,000 calls/day) is the right starting point for a proof of concept on a slice of your data. Run 3,000 records through the web batch tool today, build the dedup key in a spreadsheet, look at the duplicate report, and decide whether the pattern is worth the full investment. That pilot costs nothing but an hour of your time.

See live pricing at csv2geo.com/pricing/api.

What the pattern does not cover

Honest scope. The geocoding-key approach solves location-based duplication. It does not solve:

Company name disambiguation. "Acme Corp" and "Acme Corporation" might be two different legal entities or the same one. The dedup key groups them if they share a physical address, but two offices of the same company at different addresses will produce different keys. That is correct behaviour for address deduplication; it is a separate problem for entity resolution.

PO Boxes and virtual offices. A shared corporate-registration address or a PO Box will geocode to the same location as every other company that uses it. Your dedup logic needs a step that detects known virtual-office coordinates and excludes them from automatic merging.

International address formats. CSV2GEO covers 39 countries. For addresses outside that coverage, the geocoder may return low confidence or no result. The manual review queue handles these; do not attempt automatic deduplication on unresolved international records.

Contact-level deduplication. Two contacts at the same address are not duplicates unless they are literally the same person. The dedup key identifies duplicate accounts and locations; contact merging requires name and email matching on top of the location key.

FAQ

Does geocoding actually normalise addresses reliably enough to use as a key? For addresses in the 39 countries covered at rooftop or street level, yes — reliably enough for a confidence threshold of 0.8 or above. The geocoder was built specifically to resolve surface-form variation to a canonical location. That is a harder problem than string matching and it is already solved. For the ambiguous tail (3–8% of typical CRM exports) you review manually before merging.

What if two different buildings produce keys that round to the same cell? At 4 decimal places the cell is roughly 11 metres on a side. In dense urban environments two separate entrances of the same building can fall in the same cell. The address-component hash in the key construction (house number + normalised street name) distinguishes them: 400 Main Street and 402 Main Street will have different hashes even if their coordinates round identically. Use the full composite key, not just the coordinate part.

Can I use the dedup key in my CRM directly or does it need to stay in a staging layer? You can write it as a custom field in most CRMs. Storing it in the CRM lets you deduplicate incoming records in real time by querying existing keys before insert. Keeping it in a staging layer is safer during the initial clean-up phase because it lets you iterate on the merge policy without touching production records.

Is the key stable across API version upgrades? The coordinate component of the key is stable as long as the geocoder's underlying address database covers the address at the same precision. The normalised component fields may change format in major API versions. Freeze the key-generation code against a fixed API version during a clean-up project; update deliberately when you have time to re-key the full dataset.

Should I geocode addresses I already have coordinates for? If the coordinates came from a reliable geocoder and you have the normalised component fields, you can build the key directly without re-geocoding. If the coordinates were entered manually, user-reported, or came from an unreliable source, re-geocoding is the safe choice — it catches the 15% of user-reported coordinates that are wrong by more than a city block.

What is the right merge policy for a B2B CRM? The geocoder does not have an opinion; this is a business decision. The most common policy in practice: keep the record with the most complete data (fewest null fields), link contacts from duplicate records to the winner, preserve the oldest created_at timestamp to protect attribution, and log all merged IDs in an audit table for rollback. Do not hard-delete merged records immediately — keep them flagged as status = merged for 90 days before permanent deletion.

How do I handle the ongoing feed after the initial clean-up? Geocode each new record at ingest, compute the dedup key, and query your key store before insert. If the key exists, either merge the new record into the existing account or create a new contact linked to the existing account, depending on whether it is a duplicate lead or a new contact at a known account. This real-time check is one API call per new record — a negligible cost at any lead volume.

Related Articles

• Geocoding Confidence Scores Explained — understanding the score that tells you when to merge automatically and when to queue for review
• Caching Geocoding Results — 90% Cost Reduction — cache geocoded addresses so repeated imports of the same record set cost nothing
• Benchmarking Geocoding APIs — Honest Numbers — what to measure when evaluating geocoding quality for address normalisation
• Idempotent Geocoding — Safe to Retry — making batch geocoding jobs safe to re-run without producing duplicate or inconsistent keys
• Observability for Geocoding Pipelines — metrics and alerting for the pipeline that keeps your CRM clean over time

---

*I.A. / CSV2GEO Creator*