The Medicare Marketing department at an American health insurance company did not have a data solution in place for pulling and outputting non-mandated marketing campaign data. In particular, email campaigns to existing Medicare members. Negotiations were going on since 2015 to implement Adobe Campaign Engine. I suggested that in the meantime for a reasonable annual licensing fee we could use Alteryx Designer to handle the data lifting until we adopt the Adobe solution. Fortunately, for me, I was able to find another employee on the analytics side who had spearheaded an Alteryx pilot project which got me the loophole needed to get unapproved software installed on my machine through the pilot project.
Describe the business challenge or problem you needed to solve
The initial problem was a lack of a CRM tool and the lack of a CRM database. Compounding the problem was we had several data source systems and each operated in a silo of IT departments for each system. Getting access to all the data needed was a challenge, but then bringing it together to create campaign data was even more challenging given our resources were MS Access, Excel and a restrictive limited use SQL server table.
Surprisingly in 2015 there was no CRM database or data warehouse for Medicare in place for general member communications and there still isn't. Medicare marketing efforts were primarily focused on acquisition efforts and no retention efforts were in place. The project was to start promoting electronic communications as a cost savings initiative and also as a way to begin to dialogue with members outside of the CMS (Centers for Medicare & Medicaid Services) mandated materials provided during enrollment and annual notices of change. The goal was to notify members via mail and phone about the electronic opt-in for general communications and also electronic delivery of mandated materials. Thus, start collecting member email addresses and opt-in preferences to be used in future communications.
Our department was tasked with collecting data from these communications to give to our vendors. So they could deliver these messages and report out on the trend of email capture and preferences. Once email was captured along with preferences the goal was to begin various outbound communications designed to enhance and build the relationship with the membership to the company brand.
Describe your working solution
I'm using Alteryx Designer to pull in three pipe delimited files on the network, clean and output them as three Alteryx files. They are then used as the source data for all campaign processing and reporting, and they substantially speed the subsequent workflows. All this is done in one workflow and if I had the scheduler I would schedule them to run weekly as the files are updated on Mondays.
For the actual campaign there are several workflows. All built off one standard template which reads from the three main files, other Ad-Hoc Excel and text files, based on the campaign channels and data requirements are brought into Alteryx as needed. I also utilize Text Input tools to create look up tables, to tokenize personal data elements that are used for segmenting and targeting but need to be protected. I join to the data adding elements or masking data for security purposes using these text inputs which can easily be edited year over year as our member details change year over year.
I use In-DB tools to connect to our enterprise data warehouse in Teradata for data on campaigns where we are targeting members for preventative care reminders or prescription refill reminders. In addition, I ended up creating daily operations file checks for our mandated material campaigns, so we could monitor and audit data outputting to Tableau dashboards.
I run a daily workflow that then sends an email to me with attached outputs to review the daily audit reports I created using Alteryx ensuring timely uncovering of any data issues for our daily mandated campaigns.
Describe the benefits you have achieved
Without Alteryx we would be very limited in the scope of campaigns we are able to execute. Without Alteryx we managed only 4 campaigns in 2015, bringing Alteryx on board in late 2016 we got 11 campaigns out, and in 2017, 21 campaigns out, and for 2018 we have 31 campaigns deployed as of 12/14.
Each year through these campaigns we have experienced nearly 50% open rates showing a long awaited interest from our members in hearing from us outside of mandated material, and we've experienced actual cost savings from these campaigns of between $25-65K per year which more than covers the licensing costs for the one seat I'm using to generate these savings.
There are also numerous benefits that we are still working on quantifying such as improved retention rates for membership which decreases costs and improved member satisfaction. So far measurable impact to the bottom line has shown our digitally engaged members have a far higher retention rate than members we have not been communicating with throughout the year. As a bonus, measuring our results and reporting them out has been easier using Alteryx in combination with Tableau. As a member of my Alteryx User Group wisely said, it's not what can you do with Alteryx it's what could you be doing with Alteryx.
Here is a sample of a campaign level workflow to generate initial counts for a campaign drawing from the cleaned source data for our email database and enterprise database for additional targeting elements.