Standardise Customer Names Without Writing Formulas
The Problem: Customer Name Format Inconsistency
Customer names should be straightforward. A person has a first name and a last name. In practice, customer databases are a mess of name formats that breaks your workflows.
Your CRM might store a name as Smith, John. A CSV import uses John Smith. Legacy data stores it as JOHN R. SMITH. International customers have names like García, María del Carmen. Some records include titles: Dr. John Smith or Smith, Mr. John. Others include suffixes: Smith, John Jr. or John Smith III.
When you try to personalise emails, merge mail campaigns or integrate with mailing list services, these inconsistencies create problems. Email templates cannot distinguish first names from full names. Mailing list services reject records with unexpected format. Reporting breaks because names are not consistently formatted.
The business impact is subtle but real: campaign personalisation fails, integrations require manual mapping and data quality erodes as more records enter the system in new formats.
Why Name Formulas Fail
The obvious solution is to write a formula. Unfortunately, name formatting is deceptively complex.
Approach 1: Split and Reorder tries to split the name string on spaces or commas, then reorder the parts. This works for simple Last, First to First Last conversions but breaks when names have middle names, multiple components or titles.
Approach 2: Conditional Logic attempts to detect whether the name is in first-last or last-first format (is there a comma?), then apply appropriate transformations. But this logic becomes complex when you need to handle titles, suffixes and middle names across different formats.
Approach 3: Regular Expressions uses patterns to extract first, middle and last names, then reassemble them. This approach works for predictable formats but fails when names deviate (no middle name, multiple middle names, compound last names).
All three approaches require you to anticipate format variations upfront. When a new variation appears (a compound first name, a title you have not seen before), the formula breaks or produces incorrect results.
Introducing Learn from Examples
The Learn from Examples feature takes a different approach. Instead of writing rules, you show the system what you want.
You provide a few examples of customer names in various formats and what the standardised version should be. The pattern synthesis engine analyses these examples, identifies the underlying transformation logic and applies it to your entire customer database. Complex name reformatting logic emerges from simple examples.
Case Study Walkthrough
Step 1: Identify Format Variations
The marketing agency had over 62,000 customer records from multiple acquisition sources. They identified the most common name format variations: first-last, last-first, names with titles, names with suffixes and names with middle initials or full middle names.
Step 2: Create Example Pairs
In a dedicated sheet, the agency created two columns. Column A contained the messy customer names exactly as they appeared in the source data. Column B contained the desired standardised format: first name plus last name, title case, with no titles or suffixes.
| Dirty (Original Format) | Clean (Standardised) |
|---|---|
Smith, John |
John Smith |
John Smith |
John Smith |
Dr. John R. Smith |
John Smith |
Smith, Mr. John Jr. |
John Smith |
SMITH, JOHN |
John Smith |
García, María del Carmen |
María García |
Step 3: Test the Pattern
The agency selected both columns and clicked Test. The pattern synthesis engine detected: Detect last-first format (comma-separated) and convert to first-last. Remove titles (Dr., Mr., Ms., etc.). Remove suffixes (Jr., Sr., III, etc.). Remove middle initials. Convert to title case. Trim extra spaces.
A preview showed how the system would transform a sample of customer names. The results matched the intended output.
Step 4: Configure and Schedule
The agency selected their full customer name column as input. They wanted to ensure the standardised names were consistent before running their mailing campaign, so they set the schedule to process all 62,000 records in one batch and set output to a new column called "Standardized_Name".
Step 5: Verify and Deploy
The first run completed in approximately 5 minutes. All 62,000 customer names were standardised to the consistent format. The agency spot-checked 75 random rows and verified accuracy. Names in various formats, with titles, suffixes and multiple components were all handled correctly. They then used the standardised names in their email campaign, achieving significantly better personalisation and integration with their mailing list service.
How Pattern Synthesis Works
The Learn from Examples feature applies sophisticated analysis to identify name transformation logic:
- Analyse the Differences: The system compares each messy name to its clean counterpart and identifies what changed. Was the name reordered? Were characters removed? Was case changed?
- Extract Common Patterns: It looks across all your example pairs to find patterns common to all transformations. If 90 percent of your examples remove titles and suffixes, that becomes part of the learned rule.
- Build a Rule Pipeline: The system constructs a pipeline of transformations (detect format, reorder sections, remove unwanted components, change case) that, when applied in sequence, standardise all variations to your desired format.
- Validate Against Examples: Before applying the rule to your full customer list, the system verifies that the discovered rule correctly transforms all your example pairs.
- Apply to Full Dataset: Once validated, the rule is applied to every name in your input range.
The Business Impact
For the direct marketing agency, name standardisation delivered measurable improvements to campaign effectiveness and operational efficiency:
| Metric | Before Learn from Examples | After Learn from Examples | Improvement |
|---|---|---|---|
| Customer name format consistency | 38% | 100% | 62 percentage points |
| Email personalisation success rate | 76% | 98% | 22 percentage points |
| Manual name corrections per campaign | 8,100 | 340 | 96% reduction |
| Mailing list integration errors | 4.2% failure rate | 0.3% failure rate | 93% fewer errors |
| Campaign launch time | 3 days (including cleanup) | 4 hours (data standardised automatically) | 87% faster launch |
The largest operational benefit was eliminating the manual name correction phase that previously delayed every campaign launch. Campaigns could now launch within hours rather than days. Email personalisation improved dramatically because first names were now consistently extracted and formatted. Integration with third-party mailing services became reliable.
Getting Started with Your Own Data
Assess Your Name Formats
Before you start, gather 5 to 8 customer names that represent the different formats in your database. Note the variations: first-last vs. last-first, titles, suffixes, middle names. This diversity ensures your examples capture all the transformations needed.
Create Example Pairs
In a new sheet, create two columns. Column A contains the messy names exactly as they appear. Column B contains the standardised version. Decide on your target format upfront: first-last? Title case? Any specific capitalisations? Your examples should all follow the same target format.
Test and Validate
Use the Test function to preview the detected rule. If it matches your intention, proceed. If not, add another example pair that clarifies the expected transformation and test again.
Schedule and Monitor
Select your full customer name column and configure the frequency. For one-time cleanup before a campaign, choose Process to end. For ongoing standardisation of new customer records, choose Process in a loop. Monitor the first execution to verify that all names are standardised correctly.
For detailed instructions, see the Learn from Examples documentation.
Frequently Asked Questions
What if I need to preserve first names and last names in separate columns?
If your target format is to have first and last names in separate columns, show that in your examples. For instance, show Smith, John transforming to John in one example pair and Smith, John transforming to Smith in another. You would then run the Learn from Examples operation twice: once for first names, once for last names.
Can it handle names from different cultures and naming conventions?
Yes. Provide examples that show how you want names from different cultures standardised. If you have compound first names or family names in different orders, show examples of each. The system learns the pattern from your examples.
What if some names have middle names and others do not?
If you want to remove middle names, show that in your examples: John Robert Smith becomes John Smith. If you want to preserve middle names, show that: John Robert Smith stays John Robert Smith. The system learns from your examples.
Can it handle titles and suffixes?
Yes. If you want to remove titles and suffixes, show that in your examples. If you want to preserve certain titles or suffixes, you can show that as well. The system learns which components to remove and which to keep based on your examples.