crm-refine

   __________  __  ___   ____       _____
  / ____/ __ \/  |/  /  / __ \___  / __(_)___  ___
 / /   / /_/ / /|_/ /  / /_/ / _ \/ /_/ / __ \/ _ \
/ /___/ _, _/ /  / /  / _, _/  __/ __/ / / / /  __/
\____/_/ |_/_/  /_/  /_/ |_|\___/_/ /_/_/ /_/\___/

Streaming CRM dedupe with reviewable recipes — a small, dependency-free Python CLI. Cluster duplicate records, pick a master, and emit reviewable merge instructions from a versioned recipe. Scales to ~1M+ rows on one machine. Connects to the major CRMs to export and merge.

Clean-room / MIT. Independent implementation — no OpenRefine code; see CREDITS.md.

Quickstart (30 seconds, no CRM, no setup)

python3 -m crm_refine apply sample.csv --recipe recipes/contacts-dedupe.json \
    --merges merges.csv --clean cleaned.csv
# sample.csv (20 messy rows) → cleaned.csv (15 rows: dupes collapsed, masters survivorship-merged)
# merges.csv shows exactly what was merged and why. Nothing else is touched.

On any CSV with different headers, skip the recipe and map columns inline:

python3 -m crm_refine apply your.csv \
    --id-col "Record ID" --company-col "Company Name" \
    --domain-cols "Email,Website" --date-cols "Last Activity" \
    --merges merges.csv --clean cleaned.csv

Why this exists

Two data-cleaning ideas (both public techniques, popularised by tools like OpenRefine) are all you need for CRM dedupe: key-collision clustering via a name fingerprint, and a reproducible recipe you review and replay. crm-refine implements those from scratch and runs headless in a pipeline — no JVM, no UI, no interactive server. Classic algorithms (Soundex, Levenshtein) are used directly. Full provenance in CREDITS.md.

Install

./install.sh            # bootstrap: installs via pipx/pip + runs first-run setup
./install.sh duckdb     # + the optional DuckDB engine
#   or manually
pipx install crm-refine
pip install "crm-refine[duckdb]"
pip install -e .        # from source; installs the `crm-refine` console command

Zero required dependencies — the core engine is standard library. duckdb is optional. Also runnable without installing: python3 -m crm_refine --help.

First-run setup & CRM connectors

crm-refine demo                 # 30-second dedupe on bundled sample data (no setup, no CRM)
crm-refine init                 # banner + arrow-key/number multi-select CRM auth
crm-refine providers            # list CRMs + which are configured
crm-refine auth hubspot         # (re)configure one provider
crm-refine doctor               # verify configured CRMs (live token + 1-record export)
crm-refine --version

The init picker uses arrow keys + space to toggle on a real terminal, and falls back to a numbered menu on non-TTY / CI / piped input. Supported CRMs and how each authenticates (credentials stored in ~/.config/crm-refine/credentials.json, chmod 600; env vars override so CI/EC2 never needs the file):

Provider	Auth	Export	Merge
HubSpot	private-app token	yes	native merge API
Pipedrive	API token	yes	native merge API
Microsoft Dynamics 365	Azure AD client-credentials	yes	native Merge action
Salesforce	OAuth client-credentials	yes (SOQL)	export only — merge via SOAP/UI
Zoho CRM	OAuth refresh-token	yes	export only — merge in-product

End-to-end:

crm-refine export --provider hubspot --object companies --out export.csv
crm-refine apply  export.csv --recipe recipes/contacts-dedupe.json --merges merges.csv --clean cleaned.csv
crm-refine merge  --provider hubspot --object companies --merges merges.csv        # dry-run
crm-refine merge  --provider hubspot --object companies --merges merges.csv --commit

Merges are irreversible in most CRMs — merge is dry-run by default, applies only decision=auto rows, and writes the rest to a review queue.

Engines

Same recipe, same output; pick how candidate pairs are found:

--engine	How	When
python (default)	streaming features + sorted-neighbourhood window + early-exit Levenshtein	single box up to a few million rows — fastest here
duckdb	SQL blocking + comparison in DuckDB (splink-style); pip install "crm-refine[duckdb]"	data already in a warehouse/DuckDB, out-of-core, or to swap in splink probabilistic scoring

Both are quality-identical on key-collision, method, ngram, thresholds and the NN toggle (verified). They can differ slightly only on the fuzzy nearest-neighbour pass at strict thresholds: the Python engine windows it (bounded work), DuckDB does a full within-block join (more exhaustive). Measured at 306k rows on one laptop: python ~40s / ~490MB, duckdb ~105s / ~670MB. The Python engine wins on raw speed on a single machine; reach for duckdb when the data lives in a DB, RAM is the constraint, or you want to replace the transparent comparison with splink's probabilistic (Fellegi-Sunter/EM) model behind the same seam (crm_refine/engines_duckdb.py).

Commands

# 1. triage — nulls, distinct, duplicate signal
python3 -m crm_refine profile export.csv --cols company,email,phone

# 2. dedupe — run a reviewed recipe → clusters + merge instructions (streaming, memory-bounded)
crm-refine apply export.csv --recipe recipes/contacts-dedupe.json \
    --out result.json --merges merges.csv --engine python --verbose

# 3. eval — score a recipe against a ground-truth label column
python3 -m crm_refine eval eval/messy_crm.csv --recipe recipes/contacts-dedupe.json \
    --truth-col true_cluster --min-f1 0.9

The recipe (versioned, reviewable, sign-off-able)

recipes/contacts-dedupe.json declares the id/company/domain columns, the clustering method and thresholds, master-record selection (date columns), the auto-vs-review gate, and an optional transform block. This is the OpenRefine "operation history" idea: review it in Git, sign it off, replay it deterministically.

Create a recipe (3 ways)

1. Generate one — auto-detects columns from your CSV header:

   crm-refine recipe data.csv --out recipe.json          # add --with-transform for a cleanup block
   

2. Save from a run — tune with flags, then persist exactly what you used:

   crm-refine apply data.csv --company-col Company --id-col Id --domain-cols Email,Website \
       --method ngram --edit-threshold 0.9 --save-recipe recipe.json
   

3. Hand-write — minimal recipe:

   {
     "name": "contacts-dedupe",
     "cluster": {
       "id_col": "record_id", "company_col": "company",
       "domain_cols": ["email", "website"], "date_cols": ["last_activity_date"],
       "method": "fingerprint", "edit_threshold": 0.86, "cross_domain_threshold": 0.93,
       "nearest_neighbor": true, "auto_confidence": 0.95, "auto_max_size": 3
     }
   }
   

   Only id_col + company_col are required; everything else has sane defaults.

Standardize fields (batch consistency, OpenRefine-style cell edits)

Clean a CSV without deduping — casing, phone formats, malformed emails:

crm-refine transform data.csv --out clean.csv --report changes.json \
    --op "phone:phone:country_col=country" \
    --op "first_name:namecase" --op "last_name:namecase" \
    --op "company:company" --op "email:email"

op	does
trim lower upper titlecase	whitespace + casing
namecase	person names: Mc/Mac/O'/hyphens/particles (van, de, von…)
company	company casing; keeps true acronyms (LLC, GmbH) upper
email	lowercases + adds a <col>_valid column flagging malformed addresses
phone	standardises against the row's country when available, else leaves it untouched

Phone behaviour: a +-prefixed number is normalised to +<digits>; otherwise the row's country (ISO-2 or name) picks the dialing code (020 7946 0000 + GB → +442079460000); with no resolvable country the number is returned unchanged (safe fallback). The report counts phones_standardised vs phones_left_no_country. Use country_col=<yourcol> to point at your country field. Put the same ops in a recipe transform: […] block to version them.

One pass (standardise → dedupe → clean): if your recipe has a transform block, run everything in a single command — ideal for a scheduled/EC2 job:

crm-refine apply data.csv --recipe recipe.json --transform-first --merges merges.csv --clean cleaned.csv

Transforms run first, so clustering and the cleaned.csv operate on standardised values.

For LLM agents / automation

crm-refine is built to be driven by an agent, not just a human:

• Structured output: apply --json emits {stats, duplicates:[{master_id, duplicate_ids, decision, confidence, fields:[{column,result,source}]}]} — the full merge preview as JSON, non-interactive (no prompt, no colours, no banner).
• Self-describing: crm-refine schema emits the recipe schema, the transform-op list, engines, and command summaries as JSON — so an agent can construct valid recipes/commands without guessing.
• Programmatic cell edits: crm-refine patch data.csv --id-col record_id --edits edits.json (or JSON on stdin) applies {"<id>": {"col": "val"}} — the machine equivalent of the human edit (nano) command. Emits a JSON result.
• JSON everywhere it matters: profile, transform, and eval already print JSON; --report writes a JSON change report.
• Pipe-friendly: - means stdin/stdout for the CSV arg and --out/--clean/--merges; global --json and -q/--quiet on the data commands. See AGENTS.md for the full agent reference.
• Non-interactive by default off a TTY: prompts/menus/colour only appear on a real terminal; piped/CI runs are clean and deterministic. Exit codes are meaningful (eval --min-f1 returns non-zero on failure).

Typical agent loop:

crm-refine schema                                             # learn the recipe shape + ops
crm-refine profile data.csv                                   # JSON: columns + duplicate signal
crm-refine apply data.csv --recipe r.json --json              # JSON: stats + what would merge
crm-refine patch data.csv --edits fixes.json                  # apply targeted cell fixes
crm-refine apply data.csv --recipe r.json --clean out.csv --np  # write the deduped file

Outputs

• --merges merges.csv — per duplicate cluster: master_id, duplicate_ids, confidence, join_reason, decision (auto | review). Instructions only; nothing is mutated.
• --clean cleaned.csv — a deduplicated copy of the input: duplicates of auto clusters are dropped, the surviving master keeps its own values and fills any blanks from its duplicates (survivorship). Master is chosen by completeness → recency (date_cols) → id. Review-decision clusters are left intact.
• --out result.json — full clusters + stats.

Customize the clustering (like OpenRefine's cluster & edit)

Tune via recipe fields or inline flags (flags override the recipe):

Flag / recipe field	What it does	Default
--method / method	key function: fingerprint, ngram, phonetic (soundex)	fingerprint
--ngram-size / ngram_size	n for --method ngram	2
--edit-threshold / edit_threshold	same-domain name-similarity radius (higher = stricter)	0.86
--cross-threshold / cross_domain_threshold	name-only similarity when no shared domain	0.93
--window / window	sorted-neighbourhood window (bigger = catch more, slower)	25
--no-nn / nearest_neighbor:false	key-collision only (skip fuzzy matching)	nearest-neighbour on
auto_confidence, auto_max_size	auto-merge gate (else → review)	0.95 / 3

Measured effect on the bundled eval (precision stays 1.0 unless noted):

default ................. F1 0.99  recall 0.97
--no-nn ................. F1 0.84  recall 0.73   (key-collision only)
--edit-threshold 0.95 ... F1 0.94  recall 0.89   (stricter)
--method phonetic ....... F1 0.94  recall 0.98  precision 0.91  (over-merges — phonetic tradeoff)

Inspect any config against ground truth with crm-refine eval ... --min-f1 <x> before shipping a recipe change.

Merge instructions (the output)

Per duplicate cluster: master_id, duplicate_ids, size, confidence, join_reason, and a decision of auto (high confidence, small cluster) or review (queued for a human). Nothing is mutated — you get instructions; applying them is a separate, explicit step (see integrations/hubspot-cli.md).

How it scales (measured)

• 306k rows → ~40s, ~490MB peak RAM (single pass features + O(n·window) sorted-neighbourhood). Linear ⇒ ~1M rows ≈ 2–3 min, ~1.5GB. Fine for an EC2 batch job.
• Memory-bounded: full rows are never all held in RAM; only rows inside duplicate clusters are materialized (for master selection).

Quality (measured, seeded synthetic with hard negatives)

• 582 rows → F1 0.97, precision 1.00, recall 0.94, 96% clusters exact.
• 23k rows → F1 0.97, precision 0.99. Hard negatives (same free-email domain, distinct companies) are correctly kept apart. Regenerate/verify anytime: python3 eval/generate_messy_crm.py && python3 -m crm_refine eval ….

Methods (in crm_refine/core.py)

fingerprint (accent-fold, lowercase, de-punctuate, unique+sort tokens, strip legal suffixes) · n-gram fingerprint · Soundex phonetic · bounded/early-exit Levenshtein · free-email domains treated as null signal · union-find clustering · master by completeness → recency → id.

Layout

install.sh          bootstrap installer (banner + setup)
pyproject.toml      installable package (console script `crm-refine`, optional [duckdb] extra)
LICENSE / CREDITS.md  MIT + clean-room provenance (no OpenRefine code)
crm_refine/         core.py · engines_duckdb.py · auth.py · connectors.py · banner.py · cli · profile · evaluate
recipes/            contacts-dedupe.json (the reviewed recipe)
sample.csv          20 messy rows for a zero-setup demo (matches the default recipe columns)
eval/               generate_messy_crm.py (seeded messy data + ground truth) · messy_crm.csv
integrations/       hubspot-cli.md (export → dedupe → merge wiring)

License

MIT (LICENSE). Independent, clean-room implementation — contains no OpenRefine source; see CREDITS.md for prior-art and algorithm provenance.