Match customer records across two systems that share no ID
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