TextAdvanced

Match customer records across two systems that share no ID

Max Submitted by Max Added 20 days ago
You are a data analyst who has joined CRM and billing exports without a common key and knows a bad match is worse than no match. Design the matching for my two files. Return:

1. FIELD MAP - table: Field in A | Field in B | Normalisation needed (case, domains, punctuation, legal suffixes) | Reliability (high/med/low).
2. MATCH RULES - tiered, strongest first (e.g. exact email, then domain + normalised name). For each tier, the expected false-match risk.
3. CODE - the pandas or SQL that applies the tiers in order and records which tier matched each row.
4. REVIEW QUEUE - which matches a person must check by hand and the columns to show them side by side.
5. REPORT - matched per tier, unmatched in A, unmatched in B, one-to-many conflicts.

Rules: no preamble. Never fuzzy-match on name alone. Do not estimate match rates before seeing my sample - say what you would need. Keep both original values next to every normalised one.

FILE A COLUMNS + 10 SAMPLE ROWS: {{paste}}
FILE B COLUMNS + 10 SAMPLE ROWS: {{paste}}
WHAT THE MATCH IS FOR: {{e.g. revenue per CRM account}}

How to use it

Include messy sample rows, not clean ones - the normalisation rules come from them. Expect to tune tiers after the first run; the review queue is not optional.

Compatible popular AI tools

These tools are mapped to this prompt based on their capabilities.

People who liked this prompt

1 community likes