Merge out of control on-line email database with in house marketing list
A professional services organization needed to consolidate, standardize, and cleanse their email contact database. Over the years this data had been managed by multiple employees with multiple views on what, where, and how data should be entered and retained. The database was managed on-line utilizing the contact management functionality provided by an email marketing services firm.
As part of the project the CMO wanted the cleaned email contact information matched and integrated back into her marketing database, already managed by Butler Data Solutions. The goal was to simplify data management by having one integrated database that was easier to update, maintain, and utilize.
Records with either a viable email address or a deliverable physical address were to be retained. All email addresses, and their source file designation, were initially retained after merging with the marketing database until the appropriate employees could determine the one best email address.
We consolidated/moved existing data in the on-line database into an optimized file structure resolving numerous problems such as:
- Data spread across sixty-two columns
- Data placement within columns depended on who entered it
- Five columns were designated as and used for email addresses
- Many records had multiple unique email addresses, the max being four
- Data such as First Name could be in one of up to five columns
- Merged data, such as First/Last Name, could be in the First or Last Name column
- Physical address data was spread everywhere within the database
- Many columns had the same field name (a design flaw by the email service provider)
- CASS (address element) processing was performed and records with non-deliverable physical addresses had that data removed
- All records containing a non-deliverable or unsubscribed email address without a valid physical address were removed.
- The on-line data was then merged with the master marketing database
- If present, multiple email addresses were retained
- Preference was given to the physical addresses in the marketing database
- Duplicate records were eliminated with the data merged into one record
- Records containing multiple email addresses were returned for determination of the one best address.
After completion of the project we managed a single database containing all of the organizations marketing data. Procedures were established so that all changes, additions, and corrections would be made through this single file.
The final numbers:
- Total records to be merged 15,858
- The original on-line email marketing file contained 9,308 records
- The original marketing database contained 6,550 records
- Final number of records in the new merged marketing file 10,656
- Records with an email address in the merged marketing file 7,772
- Records returned for review containing more than one email address 439