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:

{
  "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.