This innovative data integration project demonstrates advanced expertise in both traditional data processing and cutting-edge AI implementation. The system addresses a common but complex business challenge: accurately matching customer records between different databases while handling variations in business names, addresses, and other identifying information.

The solution employs a multi-layered matching approach, beginning with traditional fuzzy matching algorithms to identify potential matches based on normalized company names and addresses. What sets this system apart is its integration with the Llama 3.2 LLM to perform intelligent analysis of each potential match. The AI component evaluates matches using a comprehensive set of criteria, including name similarity patterns, address match levels, and business context, providing human-like reasoning for each decision.

def format_record_for_llm(customer_df, index):
record = customer_df.iloc[index]
output = f”””
CUSTOMER INFORMATION:
Name: {record.get(‘company’, ‘N/A’)}
Address (Business or Billing): {record.get(‘full_address’, ‘N/A’)}

POTENTIAL MATCHES:
“””
matches = record.get(‘matches’, [])
if not matches:
output += “No potential matches found for this customer.n”
else:
for i, match in enumerate(matches, 1):
output += f”””Match {i}:
Score: {match.get(‘score’, ‘N/A’)}%
Match Type: {match.get(‘match_type’, ‘N/A’)}
Name: {match.get(‘zoho_name’, ‘N/A’)}
Address: {match.get(‘zoho_address’, ‘N/A’)}
“””

output += “””
INSTRUCTIONS:
1. Select the SINGLE BEST match from the potential matches provided.
2. Analyze ONLY the selected best match.
3. If no match is suitable, choose SKIP.

ANALYSIS STEPS:
1. Categorize Names:
– Determine if the customer name and the selected potential match name are for an Individual or a Business.

2. Evaluate Name Similarity:
– Look for exact matches or close variations.
– Categorize the name similarity using the provided categories (a-f).
– Explain the reason for the category selection.

3. Compare Addresses:
– Determine the level of address match: Full, Partial, City Only, State Only, or No Match.
– Note that PO Box addresses are valid and businesses may have different billing and physical addresses.

4. Identify Mismatches:
– List any significant differences between the customer and the selected potential match information.

5. Assess Confidence:
– HIGH: Exact or near-exact name match AND full address match. Same name category (Individual or Business).
– MEDIUM: Strong name match AND at least partial address match (e.g., same city and state). Same name category.
– LOW: Partial name match, matching across categories, or weak/nonexistent address match.
– VERY LOW: Significant discrepancies in both name and address, or matching across categories with weak similarities.

6. Make Decision:
– Choose MATCH if there’s a convincing match, otherwise choose SKIP.
– Prioritize accuracy over finding a match.

7. Explain Reasoning:
– Provide a clear explanation for your decision, addressing name categories, similarity, address match, and any other relevant factors.

IMPORTANT:
– Focus on selecting and analyzing only the SINGLE BEST match.
– Matching an individual to a business name (or vice versa) should ALWAYS result in LOW or VERY LOW confidence.
– A high match score alone is NOT sufficient for HIGH or MEDIUM confidence.
– Be extremely cautious with common names.
– Any significant address mismatch should result in LOW or VERY LOW confidence.
– When in doubt, use a lower confidence level or choose SKIP.
– If no convincing match exists, choose SKIP.

RESPONSE FORMAT:
Customer Name Category: [Individual/Business]
Match Name Category: [Individual/Business]
Match Number: [1-10 if MATCH, N/A if SKIP]
Exact Name Match: [Yes/No]
Name Similarity Analysis:
Category: [a-f]
a) Same name with spouse
b) Spouse name only
c) Personal name matches business name
d) Misspelled name
e) Name variation
f) Other
Explanation: [Brief reason for selection]
Address Match Level: [Full/Partial/City Only/State Only/No Match]
Significant Mismatches: [List major differences]
Reasoning: [Explain decision, addressing all analysis steps and why this is the best match if applicable]
Confidence: [HIGH/MEDIUM/LOW/VERY LOW]
Decision: [MATCH/SKIP]

Remember:
– Always select and analyze only the SINGLE BEST match.
– Follow the analysis steps in order for the selected match only.
– Provide clear reasoning for your decision, including why you believe this is the best match (if applicable).
– Be conservative with confidence levels, especially when matching across categories or when addresses don’t align well.
– Choose SKIP if no convincing match exists.
“””
return output