Analyzing the results of a direct-mail campaign represents special challenges when your customers’ contact data in your company’s source system does not follow the United States Postal Service (USPS) standardized format. How do you deal with this problem when you are trying to answer questions about your direct-mail campaign while on a budget?
I faced this problem recently with one of my clients. My client, the marketing director, recently ran a direct-mail campaign using a mailing list (formatted according to USPS standards). After the mailing, the client wanted to understand the results of the marketing campaign. The challenge was that his company’s source contact data was not very well standardized and there was not enough time or budget to pass all that data through a cleansing process. In addition, the results had to be turned around very quickly in order for him to decide if he should continue running the campaign or completely shut it down.
The Problem
I was working with two data sets, one from the mailing house which included a cleansed list of addresses, and names that were targeted in the mailing campaign. The second was all of the existing customer contact information in my client’s source system. My client’s data was not cleansed or standardized and followed a very different format than the mailing house data. My assignment was to confirm the results of the mailing campaign in a very short timeframe–meaning I did not have the ability to formally cleanse the source system data using an address standardization system. The data sets that I was dealing with were too large for Excel but the data size was not overwhelming.
The Solution
The traditional approach to solving this problem is using a data integration tool (such as SSIS) to parse the data and pass it to an address standardization system (such as QAS) in order to standardize and format both data sets into a uniform format. From that point the two sets can be compared using the data integration tool to determine possible matches. Since the answer had to be turned around in a few hours and because the client was not sure if they were going to “productionize” this process yet, I used a different approach to solve this problem. Using SQL Server tools (SQL and the Import Data Wizard), I answered the client’s questions in a few hours and without building a sophisticated system. I used two primary methodologies to match the mailing data set with the client’s source system data.
The output of the exercise above is a pretty small data set which I manually examined in Excel and kept only the possible matches. The reason for using the two methodologies above is to make sure I did not miss any possible matches. If I had just relied on Method A, I would have missed customers who have recently had a last name change or those living with someone with a different last name. If I just relied on Method B, I would have missed customers who did not give correct addresses or typed it slightly differently then the USPS standardized format.
Conclusion
For automated matching in a production environment, the recommended approach for matching direct-mail campaign results to an internal customers list is to use data integration and address cleansing tools. However, that effort can be time consuming and costly. If your objective is to do a quick and low budget analysis, you can use this methodology to get some answers about the performance of your direct-mail campaign. Based on the results of my analysis the client made a fact-based decision about the effectiveness of his marketing campaign and determined if he should keep investing in it or not.
Best regards,