Past Analytics Excellence Awards

Suggest an idea

Author: Jennifer Jensen, Sr. Analyst In-2CRev-28px-R.pngand team members Inna Meerovich, RJ Summers

Company: mcgarrybowen 

 

mcgarrybowen is a creative advertising agency that is in the transformation business. From the beginning, mcgarrybowen was built differently, on the simple premise that clients deserve better. So we built a company committed to delivering just that. A company that believes, with every fiber of its being, that it exists to serve clients, build brands, and grow businesses.

 

Awards Category: Best Business ROI

 

Describe the problem you needed to solve 

Mcgarrybowen creates hundreds of pieces of social creative per year for Fortune 500 CPG and Healthcare brands, on platforms including Facebook and Twitter. The social media landscape is constantly evolving especially with the introduction of video, a governing mobile-first mindset, and interactive ad units like carousels, but yet the capabilities for measuring performance on the platforms have not followed as closely.

 

Our clients constantly want to know, what creative is the most effective, drives the highest engagement rates, and the most efficient delivery? What time of day, day of week is best for posting content? What copy and creative works best? On other brands you manage, what learnings have you had?

 

But, therein lies the challenge. Answers to these questions aren’t readily available in the platforms, which export Post-Level data in raw spreadsheets with many tabs of information. Both Facebook and Twitter can only export 90 days of data at a time. So, to look at client performance over longer periods of time and compared to their respective categories, and derive performance insights that drive cyclical improvements in creative – we turned to Alteryx.  

 

Describe the working solution

Our Marketing Science team first created Alteryx workflows that blended multiple quarters and spreadsheet tabs of social data for each individual client. The goal was to take many files over several years that each contained many tabs of information, and organize it onto one single spreadsheet so that it was easily visualized and manipulated within Excel and Tableau for client-level understanding. In Alteryx, it is easy to filter out all of the unnecessary data in order to focus on the KPIs that will help drive the success of the campaigns.  We used “Post ID,” or each post’s unique identifying number, as a unifier for all of the data coming in from all tabs, so all data associated with a single Facebook post was organized onto a single row.  After all of the inputs, the data was then able to be exported onto a single tab within Excel.

 

After each client’s data was cleansed and placed into a single Excel file, another workflow was made that combined every client’s individual data export into a master file that contained all data for all brands.  From this, we can easily track performance over time, create client and vertical-specific benchmarks, and report on data efficiently and effectively.

 

Single Client Workflow

mcgarrybowen1.png

 

Multi-Client Workflow

mcgarrybowen2.png

 

Describe the benefits you have achieved

Without Alteryx, it would take countless hours to manually work with the social data in 90 day increments and manipulate the data within Excel to mimic what the Alteryx workflow export does in seconds. With all of the saved time, we are able to spend more time on the analysis of these social campaigns.  Since we are able to put more time into thoughtful analysis, client satisfaction with deeper learnings has grown exponentially.  Not only do we report out on past performance, but we can look toward the future and more real-time information to better analyze and optimize.

Author: Jim Kunce, SVP & Chief Actuary

Company: MedPro Group

 

Awards Category: Best Use of Server

 

Describe the problem you needed to solve 

MedPro Group, Berkshire Hathaway's dedicated healthcare liability solution, is the nation's highest-rated healthcare liability carrier - according to A.M. Best (A++ as of 5/27/2015). We have been providing professional liability insurance to physicians, dentists and other healthcare providers since 1899. Today, we have insurance operations in all 50 states, the District of Columbia and are growing internationally. With such great size of operations and diversity of insurance products, it is a challenge to connect systems, processes and employees with one another.

 

Regardless of an insurance carrier's size and scale, its long-term success depends on:

 

  • Continued new business growth
  • Consistent pricing and risk-evaluation
  • Unified internal operations

Our challenge was to lay the analytical foundation necessary for an ever-growing insurance company to execute on these three objectives. We identified the following three action items and linked them to the drivers of long-term success.

 

  • Fuel new business growth by:  Centralizing processes & remove system silos, link manual processes together.
  • Drive consistent pricing and risk-evaluation: Remove data supply bottle-necks & empower business analysts to self-serve.
  • Unify internal operations: Accelerate modernization & facilitate enterprise-wide legacy system integration.

 

Describe the working solution

"Fuel new business growth by centralizing processes & remove system silos, link manual processes together."

 

This solution has three parts to it.

  • First, we programmed our pricing algorithm using Alteryx to "learn" the insurability of a prospective customer.
  • Second, we overlaid this system on our CRM data to create sales recommendations nationwide.
  • Third, we deployed this recommender system with our Alteryx private gallery to provide real-time access to our sales teams.

MPG Private Gallery Snapshot.jpeg

 

Today, from anywhere in the country, our sales personnel can request a report for a customer they are prospecting and receive a consistent, reliable recommendation in a matter of seconds with little manual intervention.

 

"Drive consistent pricing and risk-evaluation:  Remove data supply bottle-necks & empower business analysts to self-serve."

 

In an insurance company, actuaries and underwriters are responsible for pricing insurance policies and evaluating insurance risks of applicants. These complex decisions rely on many data inputs - some of which are internally available, but in other cases come from external sources (e.g. government websites, third party resources).

 

Today, we have been able to significantly reduce the data supply bottle-necks by configuring the Alteryx server to be the bridge between the data sources and our actuaries and underwriters. Each person along the pricing and risk evaluation process now gets “analysis-ready” data consistently and timely from the private gallery, a virtual buffet of self-serve apps for all data needs.

 

"Unify internal operations: accelerate modernization -- facilitate enterprise-wide legacy system integration."

 

In 2015, MedPro Group decided to scale up investments in modernizing legacy systems to a new web-based system. The challenge was to move data from our legacy systems into the new web-based system and vice versa. Additionally, the software solution needed to have a short learning curve and be flexible and transparent enough that key business leaders managing this modernization would be able to perform the data migration tasks.

 

Alteryx was a great fit in this case. Not only were business leaders able to program processes in Alteryx in a relatively short timeframe, we scaled up with ease and accelerated modernization by deploying on the private server for analysts to use in a self-serve, reliable environment.

 

Describe the benefits you have achieved

"We have connected systems, processes and employees to one another and made the benefits of that interconnectivity available to every employee."

 

We have been using the private gallery and server since July, 2015. What started as a proof of concept and experiment is now a fully functional production-grade experience. The list of systems that have been connected, processes that have been automated and employees who are finding value out of our private gallery and server is growing rapidly.

 

Here's a view into some of the measurable benefits we have achieved in just nine months -

  • 94: The number of apps published to the private gallery to date.
  • 6951: The number of times an app has run on the gallery. That's 26 runs a day over 9 months!
  • 15: The percentage of employees who are served with this consistent, reliable self-serve platform.

 

And our goal? Move that needle to 100% with Alteryx in the months to come!

Author: Michael Barone, Data Scientist
Company: Paychex Inc

Awards Category: Best Use of Predictive

 

Describe the problem you needed to solve

Each month, we run two-dozen predictive models on our client base (600,000 clients). These models include various up-sell, cross-sell, retention, and credit risk models. For each model, we generally group clients into various buckets that identify how likely they are to buy a product/leave us/default on payment, etc. Getting these results into the hands of the end-users who will then make decisions is an arduous task, as there are many different end-users, and each end-user can have specific criteria they are focused on (clients in a certain zone, clients with a certain number of employees, clients in a certain industry, etc.).


Describe the working solution

I have a prototype app deployed via Alteryx Server that allows the end-user to “self-service” their modeling and client criteria needs. This is not in Production as of yet, but potentially provides great accessibility to the end-user without the need of a “go-between” (my department) to filter and distribute massive client lists.

 

Step 1: ETL

  • I have an app that runs every month after our main company data sources have been refreshed:

51.png

This results in several YXDBs that are used in the models. Not all YXDBs are used in all models. This creates a central repository for all YXDBs, from which each specific model can pull in what is needed.

  • We also make use of Calgary databases as well, for our really large data sets (billions of records).

52.png

Once all the YXDBs and CYDBs are created, we then run our models. Here is just one of our 24 models:

53.png

  • Our Data Scientists like to write raw R-code, so the R tool used before the final Output Tool at the bottom contains their code:

54.png

The individual model scores are stored in CYDB format, to make the app run fast (since the end-user will be querying against millions and millions of records). Client information is also stored in this format, for this same reason.

 

Step 2: App

  • Since the end-user will be making selections from a tree, we have to create the codes for the various trees and their branches. I want them to be able to pick through two trees – one for the model(s) they want, and one for the client attributes they want. For this app, they must choose a model, or no results will be returned. They DO NOT have to choose client attributes. If no attribute is chosen, then the entire client base will be returned. This presents a challenge in key-building, since normally an app that utilizes trees only returns values for keys that are selected. The solution is to attach keys to each client record for each attribute. My module to build the keys in such a way as I described is here (and there will be 12 different attributes from which the user can choose):

545.png

  • Here is what the client database looks like once the keys are created and appended:

56.png

  • The model keys do not have to be as complex a build as client keys, because the user is notified that if they don’t make a model selection, then no data will be returned:

57.png

  • Once the key tables are properly made, we design the app. For the model selection, there is only one key (since there is only one variable, namely, the model). This is on the far right hand side. This makes use of the very powerful and fast Calgary join (joining the key from the pick-list to the key in the model table). For the client table, since there are 12 attributes/keys, we need 12 Calgary joins. Again, this is why we put the database into Calgary format. At the very end, we simply join the clients returned to the model selected:

58.png

 

Step 3: Gallery

  • Using our private server behind our own firewall, we set up a Gallery and Studio for our apps:

59.png

  • The app can now be run, and the results can be downloaded by the end-user to CSV (I even put a link to an “at-a-glance” guide to all our models):

591.png

  • The user can select the model(s) they want, and the scores they want:

592.png

And then they can select the various client criteria:

593.png

Once done running (takes anywhere between 10 – 30 seconds), they can download their results to CSV:

594.png

 

Describe the benefits you have achieved

Not having to send out two dozen lists to the end-users, and the end users not having to wait for me to send them (can get them on their own).  More efficient and streamlined giving them a self-service tool.

Author: Slaven Sljivar, Vice President, Analytics

Company: SmartDrive Systems, Inc.

 

Awards Category: Most Time Saved

 

Describe the problem you needed to solve

SmartDrive’s Analytics Team, which is approaching its 9th year in its existence in our 12-year-old company, is focused on three areas: 1) customer-facing analytics, 2) analytics supporting the internal teams, and 3) analytics as it is embedded within our product.  To support these activities, we rely a well-developed data warehousing and business intelligence stack that includes Tableau, R, SQL Server (for relational dimensional data warehouse) and SQL Server Analysis Services cubes. 

 

Alteryx, which we first started using only 5 months ago (March 2016), fills in a gap in our ability to quickly integrate data.  Prior to Alteryx, we relied on a combination of R scrips, SQL stored procedures and SQL Server Integration Services (SSIS) jobs to develop data integration solutions.  While this approach worked for us over the years, it had several drawbacks:

  1. It was a more “code-heavy” approach than we liked. While our Analytics team is comprised of competent coders and scripters, we seek to minimize the amount of code we generate (and maintain!)
  2. It was relatively slow and labor-intensive. A project that involved data integration took much longer to complete than a project that could be completed with “curated” data that already existed in our data warehouse and/or cubes.
  3. It was not very maintainable. Once a failure occurred or an enhancement was needed, dealing with code made it more difficult to get into “flow of things” compared to dealing with visual workflows.

 

One specific example is a repetitive analysis that we call “Fuel Savings Analysis” (FSA).  The goal of this analysis is to evaluate how much fuel our customers (commercial vehicle fleets) saved from drivers operating their vehicles differently after SmartDrive’s video event recorders were installed in the vehicles.  Because video event recorders activate in response to unsafe and abrupt maneuvers, drivers tend to avoid executing such maneuvers.  These maneuvers also often lead to fuel waste.  For example, harsh braking wastes more kinetic energy than gradually coasting down and using the kinetic energy (and not fuel) to overcome the rolling friction and aerodynamic drag. 

 

We had already developed a tool that automated the FSA analysis, utilizing stored procedures, R code, custom data cubes and Tableau.  However, the tool required several manual steps and needed to be run for one customer at a time.  As the result, SmartDrive’s Account Management team had to make a request of the Analytics team whenever the analysis needed to be run, and the Analytics team needed to expend 2 to 3 hours of effort for each request.

 

In April 2016, one month after we started using Alteryx, our Marketing team asked for the analysis to be done that assessed the fuel savings for all SmartDrive customers.  They were interested in including that statistics in an upcoming momentum press release.  Of course, this was not achievable with the existing tool, so we thought we would try to implement the workflow in Alteryx.  We were ultimately successful in being able to support this request, leading to the following paragraph being included in the April 12th, 2016 press release:

 

Saved customers an average of $4,903 per vehicle per year—with annual per vehicle savings of $1,878 in collision exoneration, $1,784 in collision cost reduction, and $1,240 in fuel expense


Describe the working solution

Our Alteryx workflow solution issues several queries against the data warehouse, with the primary (and the largest) query representing fuel consumption and distance driven for each customer vehicle and for each week that the vehicle was driven. This is combined with a dataset that tracks when each customer site was installed with SmartDrive, so that baseline and treatment period data can be separated. An R script that employs a decision tree (rpart) is used to group vehicles and is embedded within the workflow. The key calculation for the expected fuel consumption in the treatment period (e.g. scenario that removes the effect of SmartDrive) is calculated in Alteryx, and the resulting dataset is published on Tableau Server. We authored a Tableau workbook that implements additional calculations (e.g. % fuel savings, $ savings, etc.) and allows our Account Management team to create visuals that can be shared directly with the customer. The Alteryx workflow is scheduled to run weekly every Tuesday. In less than 30 minutes, the workflow processes the entire customer dataset, with the bulk of the time being spent waiting for the data warehouse to generate the vehicle-week extract. The entire workflow is shown in the image below.

 

41.png

 

Describe the benefits you have achieved

In this particular example, Alteryx allowed us to completely streamline a process that was already largely automated using other tools. While we could have invested more time to fully automate the existing tool, that would have involved so much effort that we have repeatedly decided to de-prioritize that work.

 

Now that we have a fully-streamlined process, our Account Management team is able to “pull up” the Fuel Savings Analysis visualization (“report”) on their own, with up-to-date results. Also, our Marketing team is able to report on the overall actual fuel savings realized by SmartDrive customers.

 

Beyond the Analytics team no longer needing to spend time and effort on running the Fuel Savings Analyses, this new capability allows our Account Management team to more consistently present the fuel savings results to our customers, particularly those that are still piloting SmartDrive. This leads to increased revenue from improved pilot conversion and also greater customer satisfaction stemming from the knowledge that their investment in SmartDrive service is generating positive financial returns.

Author: Cesar Robles, Sales Intelligence Manager 

Company: Bavaria S.A.

 

Awards Category: Best Business ROI

 

Describe the problem you needed to solve

In September 30th 2015, a gossip widespread through whatsapp reduces our Pony Malta sales to 40% of normal levels. The social networks’ gossip that impacts a brand destroys brand equity and creates distrust in our customers. Our company executes a 1st stage plan that helps to spread the gossip in the first weeks to more customers increasing the crisis. In Colombia no brand had suffered an attack like this before.

 

Describe the working solution

The Alteryx solution was develop to design and decision tree that define which customers has an relevant impact in sales volume in 5 groups that allows define differentiated protocols to recover our sales in a healthy way. These 5 groups were:

 

Citizens: Actual Customers without any impact related to social network crisis.
Refugees: Customers that reduce significantly (<50%) his rate of sales related to social network crisis.
Deportees: Customers that didn’t bought our brand related to social network crisis.
Pilgrims: Customers with doubts about our products related to social network crisis.
Aliens: New customers without any impact related to social network crisis.

 

Our gap in crisis was 180k PoS (Point of Sales) impacting 92 KHl (Kilo-hecto-liters)

 

This workflow runs monthly and uses multiple sources of information in SQL server related to Customer properties and historic sales levels. We report some results in Excel and Java applications to define our performance in recovery actions. Actually we are migrating to in database process to optimize the algorithm performance and use Tableau to manage our visualization process.

 

11.png

Figure 1. Decision Tree description

 

12.png

Figure 2. 1st Quarter Deportees results

 

13.png

Figure 3. 1st Quarter Refugees results

 

14.png

Figure 4. 1st Quarter Citizens results

 

15.png

Figure 5. Numerical Distribution Initial and End State

 

16.png

Figure 6. Blending Workflow

 

17.png

Figure 7. Decision Tree workflow

 

18.png

Figure 8. Hierarchy and Priority workflow

 

Describe the benefits you have achieved

The project defines a new way to customer segmentation in our company. We use the same algorithm to define not only crisis contingence, also we used to brand expansion and price control process including geographical variables and external info of our providers (Nielsen, YanHass, Millward Brown).

 

The solution had not been implemented before Alteryx. An estimated time saving show us that initial state needs 2 or 3 weeks to develop compared with 4 or 5 days that we used in Alteryx (We just used it 1 month ago in the firs solution). Right now our response time is less than 2 days in similar solutions.

 

In Business terms, we achieve to recover 100k PoS (approximately 25% of all Colombia Market) and increase our sales in 75% of normal levels in the first 3 months. In August 2016, we recover our normal levels of sales with the trade marketing actions focused support by Alteryx workflow.

Author: Jeffrey Jones (@JeffreyJones), Chief Analytics Officer  In-2CRev-28px-R.png

Company: Bristlecone Holdings

 

Awards Category:  Name Your Own - Most Entertaining (but Super-Practical) Use of Alteryx

 

Describe the problem you needed to solve 

Our marketing department needed a working Sex Machine, but that sort of thing was strictly prohibited in our technology stack.

 

Describe the working solution

Analytics built a functional Sex Machine! Let me explain...

 

Because our business involves consumer lending, we absolutely cannot -- no way no how -- make any kind of decisioning based on sex or gender. Regulators don't want you discriminating based on that and so we don't even bother to ask about it in our online application nor do we store anything related to sex in our database. Sex is taboo when it comes to the Equal Opportunity Credit Act. But the problem was that the marketing department needed better insight into our customer demographics so that they could adjust their campaigns and the messaging on our website, videos, etc., based on actual data instead of gut instinct.

 

Well, it turns out the Census Bureau publishes awesome (and clean) data on baby names and their sex. So we made a quick little workflow to import and join 134 years of births in the U.S. resulting in over 1.8 million different name/sex/year combinations. We counted the occurrences, looked at the ratio of M to F births for each and made some (fairly good) generalizations about whether a name was more likely a "Male" name or "Female" name. Some were pretty obvious, like "John." Others were less obvious, like "Jo." And some were totally indeterminate, like "Jahni."

 

Then we joined this brand new data set to an export of our 200k customer applications and were able to determine the sex of around 90% our applicants fairly reliably, another 7% with less reliability, and only 3% as completely unknown. The best thing about it is that we were able to answer these questions completely outside our lending technology stack in a manner disconnected from our decisioning engine so as to maintain legal compliance. We also didn't have to waste any money or time on conducting additional customer surveys.

 

This was literally something that was conceived in the middle of the night and had been born into production before lunch on the following day. (bow-chicka-bow-bow) Doing this wouldn't have been just impossible before Alteryx, it would have been LAUGHABLY IMPOSSIBLE. Especially given the size of the third-party data we needed to leverage and the nature of our tech stack and the way regulation works in consumer lending.

 

Describe the benefits you have achieved

It sounds silly, but our organization did realize tangible benefit from doing this. Before, we had no idea about a critical demographic component for our customers. It's impossible to look a bank of nearly 200k names across four totally unrelated industry verticals and conclude with any kind of confidence sex-related trends. Now we can understand sex-related trends in the furniture, bridal, pet, and auto industries. We can link it to the products they're actually getting and tweak the messaging on our website accordingly. And what's more, we're able to do all this in real-time going forward without wasting any of our DBAs' time or distracting our legal department. This probably saved us a hundred man-hours or more given all the parties that would have needed to get involved to answer this simple demographic question.

 

We should probably tidy up this workflow and the .yxdb because it might be useful for other companies who want to get a full demographic breakdown but don't have any pre-existing information on customer sex. If anybody wants to know the total number of people born with every name for the last 134 years and needs the M:F occurrence ratio for each, holler at me.