Customer Relationship Management & Analysis Program - Jeremy Reese

Customer Relationship Management & Analysis Program file
Project Write-up


Background
Before returning to MBA school, I worked for a credit card Bank in a variety of different roles. One of my roles was to manage the outbound telesales (a.k.a. telemarketing) campaigns where existing Bank customers were contacted and given the opportunity to enroll in another product. Each month, the Bank would generate a file of eligible customers and I would manage the calling of that file, trying to get the largest number of sales, for lowest possible cost.

Business Challenge
When the telesales campaigns first started, they were extremely successful. The results continued to improve for a time, but there came a point where the campaign performance began to decline. After weeks of analysis, I discovered that all of the eligible customers had already been called about the offer and some were being called for a second or third time. Furthermore, I discovered that for each additional time a customer is called, they become less likely to accept the offer. While this could be extremely valuable information for the Bank, they did not have a customer relationship management system to store, analyze and use past marketing information to the improve performance of future campaigns.

Solution
In response to this challenge, I have created a Customer Relationship Management / Analysis program (CRM program for short) in VBA that will store past marketing information, calculate and analyze past marketing results in various ways, and output the information so the Bank can use it to guide future marketing campaigns. Below is a brief description on what the program does and how it works.
1. Stores campaign data – The CRM program can store the data for up to 13 marketing campaigns.
2. Analysis options – The user can select which campaign they would like to analyze, how many previous campaigns to include in the analysis (i.e. past 6, 9 or 12 campaigns), what type of analysis to conduct, how to output the results and they can choose if they want an email or text message notification once the analysis and output is complete.
3. Analysis – The CRM program evaluates each customer in the selected campaign and determines if, and how many times they were previously marketed to and the result of each attempt. Four different counts are available to summarize the findings and each count can be used in a different way to target customers for future campaigns. The counts are focused on the quality of the customer’s phone number on file as well as the “reach-ability” of the customer.
4. Output options – The CRM program can either append the selected counts on the selected campaign tab to use them in customer selection or it create an additional tab with all relevant data and counts which can be used to further analyze the historical results.
5. Notification – Finally, the user can request an email or text message notification once the CRM program has finished running.

Expected Results
By automating this analysis for the Bank, it will not only save an estimated 20 man hours per month (this is about how long it took me to complete the analysis), but it will also complete the analysis in a reasonable amount of time which will allow the Bank to use the information to better target customers for future campaigns. With just small improvements in the quality of the marketing lists (i.e. eliminating the confirmed bad phone numbers from past campaigns), the Bank should enjoy thousands of dollars of savings each month.