Metadata-Version: 2.4
Name: crm-refine
Version: 0.1.0
Summary: The reusable bits of OpenRefine for CRM data quality — streaming dedupe with reviewable recipes.
Author: Nik Dumroese
License: MIT
Project-URL: Homepage, https://github.com/nikdumroese/crm-refine
Keywords: crm,deduplication,data-quality,record-linkage,openrefine,hubspot
Classifier: Programming Language :: Python :: 3
Classifier: License :: OSI Approved :: MIT License
Classifier: Topic :: Database
Requires-Python: >=3.9
Description-Content-Type: text/markdown
License-File: LICENSE
Provides-Extra: duckdb
Requires-Dist: duckdb>=0.9; extra == "duckdb"
Provides-Extra: dev
Requires-Dist: duckdb>=0.9; extra == "dev"
Dynamic: license-file

# 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)
```bash
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:
```bash
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
```bash
./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
```bash
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:
```bash
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
```bash
# 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:
   ```bash
   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:
   ```bash
   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:
   ```json
   {
     "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:
```bash
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:
```bash
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:
```bash
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.
