Analyzing Direct Marketing Campaign Results on a Budget
Analyzing the results of a direct marketing campaign presents 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 marketing campaign and stay on a budget?
I faced this problem recently with one of my clients. My client, a marketing director, recently ran a direct marketing campaign using a mailing list (formatted according to USPS standards). After the mailing, he 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. Further adding to the need, 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.
I was working with two data sets: one with the mailing house which included a cleansed list of names and addresses that were targeted in the mailing campaign and the other with all of the existing customer contact information in my client’s source system. My client’s data was not cleansed or standardized, and it 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 weren’t overwhelming, but they were too large for Excel.
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. Because the answer had to be turned around in a few hours, and 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–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 than the USPS standardized format.
For automated matching in a production environment, the recommended approach for matching direct marketing 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 marketing campaign. Based on the results of my analysis, the client made a fact-based decision about the effectiveness of his marketing campaign and determined where he should he invest.
Bernard Wehbe is responsible for operational strategy, account management and the management of day-to-day company activities at StatSlice Systems. He has over twelve years of consulting experience focused exclusively on data warehousing and business intelligence. His experience includes data warehousing architecture, OLAP, data modeling, ETL, reporting and dashboarding, business analytics, team leadership, and project management. His business systems expertise includes but is not limited to Consulting Services, Financial Services, Retail, Transportation, Manufacturing, Telecom, and Online Subscription. Bernard received a Bachelor of Science as well as a Master of Science in Industrial Engineering & Management from Oklahoma State University.
You can subscribe to our RSS feed.