# Duplicate Record Detection And Merging (`first_watch/duplicate-record-detection-and-merging`) Actor

Recurring CRM, customer, lead, supplier, procurement, and inventory exports contain exact and near-duplicate rows. Teams need to consolidate them without manually comparing every record or losing better values during a merge.

- **URL**: https://apify.com/first_watch/duplicate-record-detection-and-merging.md
- **Developed by:** [Jordan Nabbe](https://apify.com/first_watch) (community)
- **Categories:** Automation, Business, Developer tools
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $2.00 / 1,000 canonical records

This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events.

Learn more: https://docs.apify.com/actors/running/actors-in-store.md#pay-per-event

## What's an Apify Actor?

An Actor is a serverless cloud program that runs on the Apify platform. It has two run modes.
In Batch mode, an Actor accepts a well-defined JSON input, performs an action which can take anything from a few seconds to a few hours,
and optionally produces a well-defined JSON output, datasets with results, or files in key-value store.
In Standby mode, an Actor provides a web server which can be used as a website, API, or an MCP server.

Apify vocabulary and the platform model are defined once, in the agent quickstart at https://apify.com/agents.md.

## How to integrate an Actor?

If asked about integration, you help developers integrate Actors into their projects.
You adapt to their stack and deliver integrations that are safe, well-documented, and production-ready.

Do not guess an integration path. Every one of them is in the agent quickstart at https://apify.com/agents.md: the Apify MCP server, Agent Skills with the Apify CLI, the JavaScript and Python clients, the REST API, and the account-free path for an agent with no human to sign in. It also carries the rule on stating cost before the first paid run.

For examples already wired to this Actor's own input schema, see the [API](#api) section below.

Each client library has reference documentation the quickstart does not restate: [JavaScript/TypeScript](https://docs.apify.com/api/client/js/docs.md) (`npm install apify-client`) and [Python](https://docs.apify.com/api/client/python/docs.md) (`pip install apify-client`).

# README

## Duplicate Record Detection And Merging

Clean recurring CRM, customer, lead, supplier and inventory exports before your next import. Duplicate Record Detection And Merging is for operations, RevOps, ecommerce, procurement and data teams. It finds exact and near-duplicate rows, keeps the best value for each field and traces every canonical record back to its source rows. It reads inline records, CSV, JSON and real Excel XLSX workbooks. From one bounded batch it returns import-ready canonical rows, a review queue and a row-level audit trail.

### What it does

- Parses CSV with Papa Parse and reads the actual bytes of XLSX workbooks with ExcelJS. You do not need to extract text first.
- Normalizes column names. It compares identity fields using weighted exact, email, phone (digits only) and character-bigram fuzzy matching.
- Merges automatically only when it is safe to do so. Every pair inside a merged cluster must reach `matchThreshold`, and the `neverMergeFields` must not conflict. This means a chain of weak matches cannot merge. Borderline clusters stay separate and are marked `NEEDS_REVIEW`.
- Chooses canonical values with survivorship rules: `first_source` (default), `longest`, `most_complete`, `newest`, `oldest` or `preferred_source`.
- Explains every cluster with a measured similarity score and the fields that matched. Scores are similarities, not calibrated probabilities.

The default dataset contains only canonical records: one row per merged, unique or review-marked record. Each row carries these fields:

- `sourceReference`: the source row IDs the record came from
- the canonical and cluster IDs
- `reviewStatus`
- `duplicateConfidence`

Your own columns get the prefix `data_` and are always strings or null, so identifiers such as `00120` keep their leading zeroes. Values are never reinterpreted as numbers or dates, so amounts and ambiguous dates are passed through exactly as supplied. Failures, summary counts, source-row mappings and review pairs go to the OUTPUT record, never to the dataset.

### How to use it

Run the prefilled input, or paste this offline example. It makes no network request:

```json
{
  "sources": [{
    "id": "crm",
    "format": "records",
    "records": [
      { "Customer ID": "C-100", "Name": "Ada Lovelace", "Email": "ADA@example.com", "Company": "Engines Ltd" },
      { "Customer ID": "C-100", "Name": "Ada  Lovelace", "Email": "ada@example.com", "Company": "Analytical Engines Limited" },
      { "Customer ID": "C-102", "Name": "Grace Hopper", "Email": "grace@example.com", "Company": "Compiler Systems" }
    ]
  }],
  "identityFields": [
    { "field": "email", "matchType": "email", "weight": 3 },
    { "field": "name", "matchType": "fuzzy", "weight": 1 }
  ],
  "blockingFields": ["email"],
  "neverMergeFields": ["customer_id"],
  "survivorship": { "company": "longest" },
  "matchThreshold": 0.9,
  "reviewThreshold": 0.75,
  "includeAuditRecords": true,
  "includeMergedXlsx": false
}
```

`sources` and `identityFields` are required. Each source needs a unique `id`, a `format` and exactly one payload:

| Format | Payload |
| --- | --- |
| `records` | `records`: an array of flat objects |
| `csv`, `json` | `text`, `base64` or an approved `url` |
| `xlsx` | workbook bytes in `base64`, or an approved `url` |

For XLSX files, the optional `sheet` setting picks a worksheet by name or by one-based index. If you leave it out, the first non-empty worksheet is used, and its first row supplies the headers. Formula cells use their cached results. Row references such as `crm:2` give the one-based position of the parsed record.

Remote files are downloaded only over HTTPS and only from the exact hosts `apify.com` and `stackoverflow.com`. The Actor rejects other hosts, IP addresses, credentials, custom ports and redirects that lead outside this list. For files hosted anywhere else, send them inline or as base64.

### Output example

| canonicalRecordId | sourceReference | reviewStatus | duplicateConfidence | data_name | data_company |
| --- | --- | --- | --- | --- | --- |
| canonical_001 | crm:1,crm:2 | AUTO_MERGED | 1 | Ada Lovelace | Analytical Engines Limited |
| canonical_002 | crm:3 | UNIQUE | 0 | Grace Hopper | Compiler Systems |

The OUTPUT record contains:

- `rows`: exactly the rows delivered to the dataset
- `summary`, `canonicalRecords`, `clusters` and `failures`
- `sourceRows`, with normalized values
- `reviewPairs` and `artifacts`
- an `outcome` object with `status` (SUCCEEDED, PARTIAL, FAILED or LIMIT_REACHED), `itemsReceived`, `itemsSucceeded`, `itemsFailed`, `billableCount`, `limitReached` and `undelivered`

The key-value store also receives `MERGED_CSV` and `REVIEW_QUEUE_CSV`. It can optionally receive `MERGED_XLSX` and `MERGE_AUDIT` (JSON) too. If your spending limit stops delivery, the status is LIMIT_REACHED, and OUTPUT and the files contain only the records that were delivered. If nothing usable is produced, OUTPUT is still written with status FAILED and a reason (for example `Input rejected: Review threshold cannot exceed match threshold.`), and the run then fails with a non-zero exit code.

### Pricing

Pay per event. The main charge is per canonical record; everything else listed below is free.

| Event | Price (USD) | Charged when |
|---|---|---|
| Actor start (`apify-actor-start`) | $0.005 | The run starts. |
| Canonical record (`apify-default-dataset-item`) | $0.002 | A canonical record row is pushed to the default dataset. |

Not charged:

- OUTPUT record
- MERGED_CSV
- REVIEW_QUEUE_CSV
- MERGED_XLSX
- MERGE_AUDIT
- failure reports

Examples (default 512 MB memory, one start event):

- 100 canonical records: $0.005 start + 1 × $0.005 + 100 × $0.002 = **$0.21**
- 1,000 canonical records: $0.005 start + 1 × $0.005 + 1000 × $0.002 = **$2.01**

Spending limit: when you set a maximum cost per run, the Actor stops before it would exceed it, keeps every canonical record it already delivered and reports `limitReached` in the `OUTPUT` record. It never delivers results beyond the limit.

The Store's Pricing tab shows the prices in force. If this section and the Pricing tab ever differ, the Pricing tab applies.

### Use cases

- Consolidate overlapping CRM contact exports before a migration or enrichment import.
- Merge customer or lead lists and keep the most complete or longest field values.
- Find supplier duplicates caused by variations in spelling, email or phone formatting.
- Deduplicate product exports with exact SKU or identifier rules.
- Run the same saved rules on scheduled, recurring cleanup batches.

### Limits

Each run accepts:

- up to 20 sources and 1,000 parsed rows
- at most 100 distinct normalized columns
- 6 MB of serialized input and 4 MB of combined decoded sources
- eight nesting levels and 1,000 items per input array
- cells of up to 2,048 characters, and identity values of up to 256 characters

XLSX files are limited to 200 ZIP entries, 12 MB uncompressed and 20 worksheets. The Actor rejects ZIP64, encryption, XML doctype declarations and oversized sheet coordinates.

Matching allows at most 100,000 candidate pairs and 20 seconds. If the budget runs out, the affected rows are marked for review. At most 1,000 review-pair explanations are returned.

Downloads run one at a time and follow at most three validated redirects. HTTP 429 and 5xx responses are retried up to three times, and each source times out after 15 seconds.

The Actor has no browser, no enrichment, no semantic AI, no master database that persists between runs and no interactive review screen. Precision and large-batch performance have not been benchmarked.

### FAQ

#### Does a malformed row stop the whole batch?

No. Bad rows and unreadable sources are reported one by one in OUTPUT, and the valid data is still processed (status PARTIAL). The run ends with an error exit, after OUTPUT records the failure and its reason, in these cases:

- the configuration is invalid (for example the review threshold is above the merge threshold)
- the base64 is invalid
- no source can be parsed
- a configured identity field is missing from every row

#### Can I remove or hash sensitive columns?

Yes. `excludedFields` removes columns from the generated values. `hashFields` replaces values with unsalted SHA-256 hashes after matching. Hashing is pseudonymization, not anonymization, and the original INPUT record still holds the data you submitted. The Actor does not log raw records.

#### How do I control retention?

Results are stored in the default dataset and key-value store for the run. Use your account's storage retention settings, and delete runs when your retention period ends. Only process records you are authorized to use.

#### Are results reproducible?

Yes. The same source order, content and rules always give the same IDs, merge decisions and chosen values.

# Actor input Schema

## `sources` (type: `array`):

Required. Supply 1-20 objects with a unique id, format (records, csv, json or xlsx), and exactly one payload: records, text, base64 or url. XLSX accepts an optional sheet name or one-based index. Total decoded input is limited to 4 MB. Remote URLs are restricted to HTTPS on apify.com or stackoverflow.com; inline inputs make no document requests.

## `identityFields` (type: `array`):

Required. Each rule requires field and matchType (exact, email, phone or fuzzy), with optional weight greater than zero and at most 10. Field names are normalized. Missing values score zero. Every configured identity field must have a usable value somewhere in the batch.

## `blockingFields` (type: `array`):

Column names (1-100 characters each). Rows sharing any normalized blocking value become candidates. Exact identity-value matches are always candidates. When empty, fuzzy identity fields also use their first three normalized characters as a block.

## `matchThreshold` (type: `number`):

Every pair within an automatically merged cluster must meet this weighted similarity score. Scores are similarities, not calibrated probabilities.

## `reviewThreshold` (type: `number`):

Positive candidate scores at or above this value connect possible duplicates for review. Must not exceed the automatic merge threshold.

## `neverMergeFields` (type: `array`):

Column names whose different non-empty cleaned values prevent automatic merging. Candidate conflicts enter review even if their similarity is low. Comparisons preserve case and punctuation.

## `excludedFields` (type: `array`):

Column names removed from canonical and normalized audit values after matching. This does not remove original data from the Apify INPUT record.

## `hashFields` (type: `array`):

Column names whose non-empty output values are replaced with SHA-256 hashes of normalized comparison values. Matching happens first. Hashing is deterministic pseudonymization and does not remove original INPUT data.

## `survivorship` (type: `object`):

Map field names to first_source, longest, most_complete, newest or oldest, or to {strategy: preferred_source, sourceOrder: \[source IDs]}. Newest and oldest compare ISO dates in that column. Ties retain source order.

## `includeAuditRecords` (type: `boolean`):

Save MERGE_AUDIT in addition to the source mappings and explanations always present in OUTPUT.

## `includeMergedXlsx` (type: `boolean`):

Write a real XLSX workbook containing the canonical export rows, including source references and review status.

## Actor input object example

```json
{
  "sources": [
    {
      "id": "crm",
      "format": "records",
      "records": [
        {
          "Customer ID": "C-100",
          "Name": "Ada Lovelace",
          "Email": "ADA@example.com",
          "Company": "Engines Ltd"
        },
        {
          "Customer ID": "C-100",
          "Name": "Ada  Lovelace",
          "Email": "ada@example.com",
          "Company": "Analytical Engines Limited"
        },
        {
          "Customer ID": "C-102",
          "Name": "Grace Hopper",
          "Email": "grace@example.com",
          "Company": "Compiler Systems"
        }
      ]
    }
  ],
  "identityFields": [
    {
      "field": "email",
      "matchType": "email",
      "weight": 3
    },
    {
      "field": "name",
      "matchType": "fuzzy",
      "weight": 1
    }
  ],
  "blockingFields": [
    "email"
  ],
  "matchThreshold": 0.9,
  "reviewThreshold": 0.75,
  "neverMergeFields": [
    "customer_id"
  ],
  "excludedFields": [],
  "hashFields": [],
  "survivorship": {
    "company": "longest"
  },
  "includeAuditRecords": true,
  "includeMergedXlsx": true
}
```

# Actor output Schema

## `results` (type: `string`):

Every result row stored in the default dataset.

## `summary` (type: `string`):

Summary written to the OUTPUT record.

# API

You can run this Actor programmatically using our API. Below are code examples in JavaScript, Python, and CLI, as well as the OpenAPI specification and MCP server setup.

## JavaScript example

```javascript
import { ApifyClient } from 'apify-client';

// Initialize the ApifyClient with your Apify API token
// Replace the '<YOUR_API_TOKEN>' with your token
const client = new ApifyClient({
    token: '<YOUR_API_TOKEN>',
});

// Prepare Actor input
const input = {
    "sources": [
        {
            "id": "crm",
            "format": "records",
            "records": [
                {
                    "Customer ID": "C-100",
                    "Name": "Ada Lovelace",
                    "Email": "ADA@example.com",
                    "Company": "Engines Ltd"
                },
                {
                    "Customer ID": "C-100",
                    "Name": "Ada  Lovelace",
                    "Email": "ada@example.com",
                    "Company": "Analytical Engines Limited"
                },
                {
                    "Customer ID": "C-102",
                    "Name": "Grace Hopper",
                    "Email": "grace@example.com",
                    "Company": "Compiler Systems"
                }
            ]
        }
    ],
    "identityFields": [
        {
            "field": "email",
            "matchType": "email",
            "weight": 3
        },
        {
            "field": "name",
            "matchType": "fuzzy",
            "weight": 1
        }
    ],
    "blockingFields": [
        "email"
    ],
    "neverMergeFields": [
        "customer_id"
    ],
    "survivorship": {
        "company": "longest"
    }
};

// Run the Actor and wait for it to finish
const run = await client.actor("first_watch/duplicate-record-detection-and-merging").call(input);

// Fetch and print Actor results from the run's dataset (if any)
console.log('Results from dataset');
console.log(`💾 Check your data here: https://console.apify.com/storage/datasets/${run.defaultDatasetId}`);
const { items } = await client.dataset(run.defaultDatasetId).listItems();
items.forEach((item) => {
    console.dir(item);
});

// 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/js/docs

```

## Python example

```python
from apify_client import ApifyClient

# Initialize the ApifyClient with your Apify API token
# Replace '<YOUR_API_TOKEN>' with your token.
client = ApifyClient("<YOUR_API_TOKEN>")

# Prepare the Actor input
run_input = {
    "sources": [{
            "id": "crm",
            "format": "records",
            "records": [
                {
                    "Customer ID": "C-100",
                    "Name": "Ada Lovelace",
                    "Email": "ADA@example.com",
                    "Company": "Engines Ltd",
                },
                {
                    "Customer ID": "C-100",
                    "Name": "Ada  Lovelace",
                    "Email": "ada@example.com",
                    "Company": "Analytical Engines Limited",
                },
                {
                    "Customer ID": "C-102",
                    "Name": "Grace Hopper",
                    "Email": "grace@example.com",
                    "Company": "Compiler Systems",
                },
            ],
        }],
    "identityFields": [
        {
            "field": "email",
            "matchType": "email",
            "weight": 3,
        },
        {
            "field": "name",
            "matchType": "fuzzy",
            "weight": 1,
        },
    ],
    "blockingFields": ["email"],
    "neverMergeFields": ["customer_id"],
    "survivorship": { "company": "longest" },
}

# Run the Actor and wait for it to finish
run = client.actor("first_watch/duplicate-record-detection-and-merging").call(run_input=run_input)

# Fetch and print Actor results from the run's dataset (if there are any)
print(f"💾 Check your data here: https://console.apify.com/storage/datasets/{run.default_dataset_id}")
for item in client.dataset(run.default_dataset_id).iterate_items():
    print(item)

# 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/python/docs/quick-start

```

## CLI example

```bash
echo '{
  "sources": [
    {
      "id": "crm",
      "format": "records",
      "records": [
        {
          "Customer ID": "C-100",
          "Name": "Ada Lovelace",
          "Email": "ADA@example.com",
          "Company": "Engines Ltd"
        },
        {
          "Customer ID": "C-100",
          "Name": "Ada  Lovelace",
          "Email": "ada@example.com",
          "Company": "Analytical Engines Limited"
        },
        {
          "Customer ID": "C-102",
          "Name": "Grace Hopper",
          "Email": "grace@example.com",
          "Company": "Compiler Systems"
        }
      ]
    }
  ],
  "identityFields": [
    {
      "field": "email",
      "matchType": "email",
      "weight": 3
    },
    {
      "field": "name",
      "matchType": "fuzzy",
      "weight": 1
    }
  ],
  "blockingFields": [
    "email"
  ],
  "neverMergeFields": [
    "customer_id"
  ],
  "survivorship": {
    "company": "longest"
  }
}' |
apify call first_watch/duplicate-record-detection-and-merging --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,first_watch/duplicate-record-detection-and-merging"
        }
    }
}
```

The hosted server signs you in with OAuth on first connect, so no API token belongs in this config. Clients without OAuth support can send an `Authorization: Bearer <APIFY_API_TOKEN>` header instead, using a token from API & Integrations in Apify Console (https://console.apify.com/settings/integrations).

## OpenAPI specification

Download the OpenAPI definition: https://api.apify.com/v2/actors/mbgnpAfhLu6XoVdYo/builds/9b92pjJcAalHGgrow/openapi.json