Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Past Analytics Excellence Awards

Suggest an idea

andy_moncla_avatar.pngAuthor: Andy Moncla ( @AndyMoncla ), Chief Operating Officer & Alteryx ACE In-2CRev-28px-R.png

Company: B.I. Spatial

 

Awards Category:  Best Use of Spatial

With Spatial in our company name we use Spatial analytics every day.  We use Spatial analytics to better understand consumer behavior, especially relative to the retail stores, restaurants and banks they use.  We are avid proponents and users of customer segmentation.  We rely on Experian's Mosaic within ConsumerView.  In the last 2 years we have invested heavily in understanding the appropriate use of Mobile Device Location data.  We help our clients use the mobile data for better understanding their customers as well as their competitors' customers and trade areas.

 

Describe the problem you needed to solve 

Among retail, restaurant and financial services location analysts, one of the hottest topics is using mobile device location data as a surrogate for customer intercept studies. The beauty of this data, when used properly, is that it provides incredible insight. We can define home and work trade areas, differentiate between a shopping center’s trade areas versus its anchors, understand shopping preferences, identify positive co-tenancies, and, perform customer segmentation studies. 

 

The problem, or opportunity, we wanted to solve was to: 

1. Develop a process that would allow us to clean/analyze each mobile device’s spatial data in order to determine its most probable home location 

2. Build a new, programmatic trade area methodology that would best represent the mall/shopping center visitors’ distribution 

3. Easily deliver the trade areas and their demographic attributes 

 

And, it had to scale. You see, our company entered into a partnership with UberMedia and the Directory of Major Malls to develop residence-based trade areas for every mall and shopping center in the United States and Canada – about 8,000 locations. We needed to get from 100 billion rows of raw data to 8,000 trade areas. 

 

Describe the working solution

Before I get into the details I’d like to thank Alteryx for bringing Paul DePodesta back as a Keynote Speaker this year at Inspire. Paul spoke at a previous Inspire and his advice to keep a journal was critical to the success of this project. I actually kept track of CPU and Memory usage as I was doing my best to be the most efficient. Thanks for the advice Paul. 

 

journal.png

 

Using only Alteryx Spatial, we were able to accomplish our goal. Without giving away the secret sauce, here’s what we did. We divided the task into three parts which I will describe below. 

 

1.  Data Hygiene and Analysis (8 workflows for each state and province) – The goal of this portion was to identify the most likely home location for each unique device. It is important to note that the raw data is fraught with bad data, including common device identifiers, false location data and location points that could not be a home location. To clean the data, nearly all of the 100 billion rows of data were touched dozens of times. Here are some of the details.

a. Common Device Identifiers

i. The Summarize tool was used to determine those device ID’s, which were then used within a Filter tool 

ii. Devices with improper lengths were also removed using the Filter tool 

b. False Location Data – every now and again there is a lat/long that has an inexplicably high number of devices (think tens or hundreds of thousands). These points were eliminated using algorithms utilizing the Create Points, Summarization and Formula tools, coupled with spatial filtering.

c. Couldn’t be a Home Location – For a point to be considered as a likely home location, it had to be within a populated Census Block and not within other spatial features. We downloaded the Census Blocks from the Census and, utilizing the TomTom data included within Alteryx Spatial, built a series of spatial filter files for each US state and Canadian province. To build the spatial filters (one macro with 60+ tools), we used the following spatial tools:

i. Create Points 

ii. Trade Area 

iii. Buffer 

iv. Spatial Match 

v. Distance 

vi. Spatial Process Cut 

vii. Summarize - SpatialObj Combine 

 

Once the filters were built all of the data was passed through the filters, yielding only those points that could possibly be a home location. 

 

Typically, there are over one thousand observations per device, so even after the filtering there was work left to be done. We built a series of workflows that took advantage of the Calgary tools so that we could analyze each device, individually. Since every device record was timestamped, our workflows were able to identify clusters of activity over time and calculate the most likely home location. Tools critical to this process included: 

  • Sort 
  • Tile 
  • Multi-row Formula 
  • Calgary Join and Input 
  • Formula 
  • Create Points 
  • Trade Area 
  • Distance 

The Hygiene portion of this process reduced 100 billion rows of raw data to about 45 million likely home locations. 

 

2.   Trade Area Delineation (4 workflows/macros for each mall and shopping center, run iteratively until capture rate was achieved) – We didn’t want to manually delineate thousands of trade areas. We did want a consistent, programmatic methodology that could be run within Alteryx. In short, we wanted the trade area method to produce polygons that depicted concentrations of visitors without including areas that didn’t contribute. We also didn’t want to predefine the extent of the trade areas; i.e. 20 minutes. We wanted the data to drive the result. This is what we did.

a. Devised a Nearest Neighbor Methodology and embedded it within a Trade Area Macro – Creates a trade area based on each visitor’s proximity to other visitors. Tools used in this Macro include:

i. Calgary 

ii. Calgary Join 

iii. Distance 

iv. Sort 

v. Running Total 

vi. Filter 

vii. Find Nearest 

viii. Tile 

ix. Summarize – SpatialObj Combine 

x. Poly-Split 

xi. Buffer 

xii. Smooth 

xiii. Spatial Match 

 

b. Nest the Trade Area Macro within an Iterative Macro – By placing the Trade Area Macro within the Iterative Macro Alteryx allow the Trade Area Macro to run multiple scenarios until the trade area capture rate is achieved 

c. Nest the Iterative Macro within a Batch Macro – Nesting the Iterative Macro within the Batch Macro allows us to run an entire state at once 

 

The resultant trade areas do a great job of depicting where the visitors live. Although rings and drive times are great tools, especially when considering new sites, trade areas based on behavior are superior. For the shopping center below, a ring would have included areas with low visitor concentrations, but high populations. 

 

trade area with ring.png

 

3.  Trade Area Attributed Collection and Preparation (15 workflows) – Not everyone in business has mapping software but many are using Tableau. We decided that we could broaden our audience if we’d simply make our trade areas available within Tableau. 

 

Using Alteryx, we were able to easily export our trade areas for Tableau. 

Tableau - trade area.png

 

Build Zip Code maps. 

 

Tableau - zip code contribution.png

 

For our clients that use Experian’s Mosaic or PopStats demographics, Alteryx allows us to attach the trade area attributes. 

Tableau - mosaic bubbles.png

Tableau - PopStats.png

 

Describe the benefits you have achieved

The benefits we have achieved are incredible. 

 

The impact to our business is that both our client list and industry coverage have more than doubled without having to add headcount. By year end, we expect our clients’ combined annual sales to top $250 billion. Our own revenues are on pace to triple. 

 

Our clients are abandoning older customer intercept methods and depending on us. 

 

Operationally, we have repeatable processes that are lightning fast. We can now produce a store or shopping center’s trade area in minutes. Our new trade methodology has been very well received and requested. 

 

Personally, Alteryx has allowed me to harness my nearly 30 years of spatial experience and create repeatable processes and to continually learn and get better. It’s fun to be peaking almost 30 years into my career. 

 

Since we have gone to market with the retail trade area product we have heard “beautiful”, “brilliant” and “makes perfect sense.” Everyone loves a pat on the back, but, what we really like hearing is “So, what’s Alteryx?” and “Can we get pricing?” 

ksnow.pngAuthor: Keith Snow (@ksnow), President/Data Scientist 

Company: B2E Direct Marketing Twitter_logo_blue.png  In-2CRev-28px-R.png fb-art.jpg

 

Awards Category: Best 'Alteryx For Good' Story

On December 1st, 2015, which was "Giving Tuesday", a global day dedicated to giving back, B2E Direct Marketing announced a newly created grant program for 2016 called 'Big Data for Non-Profits'.  B2E Direct Marketing is a business offering Big Data, Visual Business Intelligence and Database Marketing solutions.

 

Non-profit organizations are a crucial part of our society, providing help to the needy, education for a lifetime, social interactions and funds for good causes.

 

Describe the problem you needed to solve 

While serving on three non-profit boards, Keith Snow, President of B2E, became aware that data is among the most important, under-used and least maintained asset of a non-profit. 

 

B2E_Volunteer.png"The 'Big Data for Non-Profits' Grant program was born out of a vision that we had at B2E to give back to our community. We wanted to offer non-profits the same visual business intelligence and database marketing services that we offer our other clients." says Snow.

 

The grant program includes the following services free of charge to the winning organization in the month for which they are selected:

  • Data Hygiene (clean up donor file)
  • Data Append (age, income, gender, marital status, lifestyle segmentation, and more)
  • Detailed donor analysis and overview reports

 

Each month in 2016, B2E will choose one non-profit from those that apply through www.nonprofit360marketing.com. Award recipient applications are reviewed by a panel selected by B2E and awards are given based upon how the services will be used and to further the organization's goals. The grant program began accepting applications from eligible 501(c)(3) non-profits at the end of December and has already completed work on three organizations so far this year.

 

"We are excited about using Alteryx to help non-profits expand their mission and to better serve our communities." says Snow.

 

Describe the working solution

B2E has an initial consultation meeting with each non-profit where the goals and takeaways of the 'Big Data for Non-Profits' program is discussed.

 

We identify current data sources that the non-profit has available, and request up to 48 months of donor contact and giving information.  Minimal information is requested from the non-profit as we know great value can be added using Alteryx Designer.

  • Name                                                                         
  • Address, City, State, Zip
  • Phone
  • Date of Donation
  • Amount of Donation
  • Campaign
  • B2E_Alteryx.pngDonation type: i.e. cash, check, soft credit, etc. 


B2E has created Alteryx workflows to perform donor file hygiene. Since we have licensed the data package, we take advantage of the CASS, Zip4 Coder and Experian Geodemographic append and TomTom capabilities.

 

All donor data is address standardized to meet postal standards and duplicates within their database are identified.  Once the data is updated to meet our standards, we process the files against the National Change of Address and the National Deceased database. 

 

The next step is taking the donor's contact information and appending demographics at the individual and household level (age, income, gender, marital, age of home, Mosaic segmentation, etc.) using the Alteryx Experian add-on product.  Alteryx Designer is invaluable for this process as we manipulate the donor data to be more useful for the non-profit.

 

B2E_DonationTableau.pngAlteryx' ability to export Tableau Extract files are key for this program to be successful. We have created key Tableau dashboards that highlight the following:

a. Consumer demographics

b. Mosaic marketing segmentation

c. Campaign or donation source

d. Donation seasonality / giving analysis

e. Pareto (80/20 Rule): to identify and profile the 20% of the donors who contribute 80% of the revenue

f. Geography (city, zip, county, metro area)

 

Once the data is in the Tableau Extract, business intelligence analysis is performed with visualization that is easy to understand and immediately actionable by the non-profit.  Tableau packaged workbooks are created for each non-profit so they have access to interactive analytics to help them make quick and immediate business decisions for their organization.

 

Describe the benefits you have achieved

B2E provides a niche service that many non-profits do not have the knowledge, tools or budget to complete on their own.

 

The benefits to each non-profit includes the following:

  1. The donor data from each non-profit can now be processed in days instead of weeks using Alteryx. This allows B2E the maximum ability to help more organizations. In the past, we only worked with one non-profit per year. Our 2016 goal is to work with twelve.
  2. A clean donor contact file with updated addresses, deceased individuals flag and duplicated merged is returned to the organization. Many non-profits send out direct mail, they immediately see their deliverability rates increase by more than 15% and return mail rates decrease. The cost for printing and postage is optimized as well.
  3. The best way to get your current donors to give more is to truly understand what they look like. Understand the donor's life stage, giving history, demographics, lifestyle characteristics, media preferences and digital behavior is key for success. Targeting a donor in a way that resonates with them has lead to an increase in giving. 
  4. All non-profits want access to new donors. A profile identifies what the best donor characteristics look like. Since B2E can also acquire direct mail and email lists, we help the non-profit find "look-alike" individuals who have never donated to their organization.
  5. B2E's goal is to help each non-profit to maximize the current donations coming into their organization so they can keep their expenses and overhead lower as well as offer them a free service they would not have otherwise acquired.

 

The impact to each non-profit is huge, but the impact to B2E is just as great as we are allowed to use a great tool to be a leader in Iowa as a company that truly gives back to our community all year long. As of April, 2016, we have provided services for:

  • Big Brothers Big Sisters of Iowa
  • Children's Cancer Connection
  • Youth Emergency Services and Shelter of Iowa
  • Governors District Alliance
  • Easter Seals Iowa

Author: Kristin Scholer (@kscholer), Insight Manager In-2C-14px.png
Company: Ansira
Awards Category: Most Time Saved

 

Ansira, an industry-leading marketing agency in integrated real-time customer engagement, activates big data through advanced analytics, advertising technology, programmatic media and personalized customer experiences. Ansira leverages superior marketing intelligence to build deeper, more effective relationships with consumers and the retail channel partners that engage them on the local level. Marketing intelligence is infused across all disciplines and executed through digital, direct, social, mobile, media and creative execution, marketing automation, co-op and trade promotion. 

 

Describe the problem you needed to solve

As a data-driven advertising agency, Ansira is constantly profiling customer behavior for a variety of clients in industries such as quick service restaurants, automotive brands and large retailers. Ansira’s Analytics team heavily utilizes media and consumer research that comes from the MRI Survey of the American Consumer to create Customer Behavior Reports. This large survey provides a vast database of demographics, psychographics, media opinions and shopper behavior that give insights into the actions and behaviors of the U.S. consumer. These insights help Ansira better understand consumers for new business perspectives as well as develop strategies for existing clients.


The challenge the Analytics team faced was that these rich insights were not easy to format, interpret or analyze. The data is accessed through an online portal and exported into an Excel format that does not make the data easy to manipulate. Depending on the project requirements, it could take an analyst 4-8 hours to format the data, identify survey responses that are statistically significant, build out a report to display all the information and write up a full summary. This is not cost effective and it became clear that a better way to transform this data was needed if Ansira wanted to utilize it on a regular basis.

 

Describe the working solution

After using Alteryx to format unfriendly Excel output for many projects, it was clear to the Analytics team that Alteryx could also be a solution for speeding up the Customer Behavior Report process. In about two days, one team member was able to create an Alteryx workflow that did all of the Excel formatting in just three seconds (this was generally taking over an hour to do manually).

 

Then Alteryx was taken one step further as formula tools were integrated to identify which behaviors were statistically significant for an analysis (this was taking 1-2 hours to work through manually). Next, the process was simplified one more time by incorporating the reporting tools to create a full report of all the data needed in the form of a PDF. The report even included color coding to easily identify statistically significant behaviors. Not only did this create a beautiful report in seconds but made key behaviors easy to identify, thus taking the analysis and summary process from 2-3 hours down to 15-30 minutes.

 

Describe the benefits you have achieved

The process that was created in Alteryx has allowed the Ansira Analytics team to offer Customer Behavior Reports to New Business and Strategy departments that can be turned around in a day instead of a week. If a full analysis is not needed, the Analytics team can turn around just the PDF data report in as little as 15 minutes (see picture below). This allows Ansira to gain additional direction on who is the consumer they are targeting, which can be instrumental in creating a new campaign.

 

31.jpg


To make this process even easier, the Analytics team has created a request form (see picture below) that anyone at Ansira can use to identify the behaviors they are interested in seeing for their client. Once the request form is received by the Analytics team, they can do a quick data pull from the MRI online portal, update the Alteryx workflow and have a full report created in under an hour.

32.jpg

 

Ansira recently had a consumer packaged goods client where the Strategy team needed to learn more about the difference in behavior patterns between Millennials and Non-Millennials who purchased 16 specific products. The Analytics team was able to pull data from MRI on these 16 products, run it through the Customer Behavior Report workflow and create 16 individual reports for each product comparing Millennials and Non-Millennials purchase behaviors in less than 4 hours. Without Alteryx, this would have taken a single analyst almost a full week to complete and likely would have never even been a possibility due to budget and capacity constraints.

 

Creating these Consumer Behavior Reports have become a regular occurrence with two to three requests coming into the Analytics department each week. With the help of Alteryx, these reports have become a large asset to Ansira as they provide very impactful information without a lot of effort.

Author: Alex Huang, Asst. Mgr, Quality Planning & Analysis

Company: Hyundai Motor America

 

Awards Category: Most Time Saved

 

There have been just a few times where some tool or platform has truly "changed" my life.  The two that come immediately to mind are Alteryx & Tableau.  Before I had either, the majority of my time was spent wrangling data, creating reports, and doing what I could using SAS, SQL, & Excel.  I had streamlined as much as I could and still felt bogged down by the rudimentary data tasks that plague many of us. 

 

With the power of Alteryx alone, I've regained 1,253 hours per year.  Alteryx WITH Tableau has saved me an additional 511 hours to a total of 1,764 hours saved per year!  Does that mean I can quit?  Maybe…but I’m not done yet!

 

For those that care for the details, here's a table of time savings I had cataloged during the start of my Alteryx journey.  I’ve had to blank out the activity names for security reasons but the time savings are real.

 

Picture1.png

 

I experienced a 71% savings in time with Alteryx alone!

 

With this new found "free time," I was able to prototype ideas stuck on my To-Do list and create new insight for my business unit.  Now my "what if's" go from idea straight to Alteryx (and to Tableau faster) and I couldn't be happier.  Insights are delivered faster than ever and with more frequent (daily) updates thanks to Alteryx Desktop Automation.

 

Describe the problem you needed to solve

Hyundai Motor America sells thousands of cars per day so the faster we can identify a quality issue and fix it, the more satisfied our customers will be.  Addressing quality concerns earlier and faster helps us avoid additional costs but most importantly brand loyalty, perceived quality, and vehicle dependability, etc.  Some examples of actions:

 

  1. Increased the speed at which we validate and investigate problems from survey data resulting in faster campaign launches and remedy development.
  2. Able to digest and understand syndicated data from J.D. Powers within hours instead of weeks allowing us to further validate the effectiveness of our prior quality improvement initiatives and also identify issues we missed.
  3. Being able to blend all the data sources we need (call center, survey data, repair data, etc.) in Alteryx allowed us to more rapidly prototype our customer risk models vs. traditional methods via SAS which took much longer.
  4. Alteryx automation with Tableau allowed us to deploy insight rich interactive dashboards that enabled management to respond to questions in real-time during our monthly quality report involving many major stakeholders throughout Hyundai.  This lead to more productive meetings with more meaningful follow-up action items.

 

I needed to solve a time problem first!  I was spending too much time doing things like data prep and reporting that just wasn’t quite enough for me.  I didn't have enough time to do what I really wanted to do, solve problems!

 

Being an avid fan/user of Tableau, data preparation started becoming my biggest challenge as my dashboard library grew.  I would end up writing monster SQL statements and scripts to get the data ready but I still struggled with automation for creating Tableau Data Extracts (TDE's). I explored using Python to create them but it just wasn't quite the "desired" experience.  Enter Alteryx, life changed.

 

Picture2.png

 

Describe the working solution

My work typically involves blending data from our transactional data warehouse, call center data, survey data, and blending third-party data from companies like J.D. Powers.  Since we have an Oracle database in-house, I'm able to leverage the In-DB tools in Alteryx which is just amazing!  In-DB tools are similar to a "visual query builder" but with the Alteryx look, feel, and added capability of Dynamic Input and Macro Inputs.  Since data only moves out of the DB when you want it to, queries are lightning fast which enable accelerated prototyping ability!

 

Describe the benefits you have achieved

I've quite literally freed up 93% of my time (given 1960 work hours per year with 15 days of vacation @ 8 hours per day) and started a new "data team" within my business unit with Alteryx & Tableau at its core.  The ultimate goal will be to replicate my time savings for everyone and “free the data” through self-service apps.  At this point, I’ve deployed 5,774 Alteryx nodes using 61 unique tools in 76 workflows of which 24% or so are scheduled and running automatically.  Phew!  Props to the built-in “Batch Macro Module Example” for allowing me to calculate this easily!

 

Picture3.png

 

We are able to identify customer pain points through an automated Alteryx workflow and algorithm that gauges how likely an issue will persist across all owners of the same model/trim package.  We’ve seen how blending Experian ConsumerView data bolsters this model but we’re still in the cost justification phase for that.  Upon detection of said pain point, we are able to trigger alerts and treatments across the wider population to mitigate the impact of this pain point.  Issues that can’t be readily fixed per se are relayed back to R&D for further investigation.  Ultimately customers may never see an issue because we’ve addressed it or they are simply delighted by how fast we’ve responded even when no immediate remedy is available.

 

The true bottom line is that the speed and accuracy at which we execute is critical in our business.  Customers want to be heard and they want to know how we are going to help resolve their problems now, not months later.  They want to love their Hyundai’s and the more they feel like we are helping them achieve that, the more loyal they will be to our brand.

 

Although we can’t fix everything, Alteryx helps us get to where we need to be faster which; in my opinion, is an enabler for success.

Authors: Irina Mihai (@irina_mihai) , Web Analyst 

                  Johannes Wagner, Senior Business Analyst

Company: Adidas International Trading B.V.

 

Awards Category: Name Your Own - Creating the New

 

Describe the problem you needed to solve 

The ecommerce business division was facing the challenge of keeping track and steering the performance of over 9000 articles.

 

Senior management had an overview of top level numbers but the actual people who could take action and steer the business on operational level had limited information.

 

Merchandizers tracked the sales of only most important product franchises which generated roughly 60% of the business, but they did not have an overview of article size availability and warehouse stock which was vital in order to know whether getting more online traffic for the article would lead to more sales or actually disappointed customers who didn't find their size. Besides stock information, merchandizers also needed BI data and web analytics data in order to have a holistic understanding of article and franchise performance, a situation which caused delays in acting upon information and steering the business proactively.

 

Even so, the full product range and especially the low-key franchises (40% of the business) were reported on an ad-hoc basis. No actions were taken on the less important franchises which led to unrealized opportunities, as unsold products are heavily discounted at the end of the season.

 

Given this complex business environment and time needed to get hold of data which even becomes obsolete before reaching the relevant stakeholders in a digestible format, we needed to give transparency on all product franchises and provide all the relevant information needed to take actions and drive the business on both aggregated and granular level, in real time, in one place, available to everyone, in an automated way.

 

To sum up, the drivers that led to a new way of working within analytics were:

 

  • Tracking ongoing performance on all articles improves our margin so that we can drive sales during the season and avoid heavy discounting at the end of the season. Offering too many discounts also has a negative long-term impact on the brand and educates consumers to buy on discount, so we wanted to make sure we maximize opportunities within season.
  • Besides immediate financial returns, we are also thinking of the consumer experience and the fact that not finding their desired sizes online disappoints customers. Being able to drive demand planning proactively and ensure enough supply is available is a way to keep customers happy and returning to our site.

 

Describe the working solution

Alteryx has allowed us to tap into multiple sources of data in a fast, scalable way not possible before, which allows us to be truly agile and data driven as an organization.

 

On a high level, the data sources used in the workflow are:

  • BI data incl. sales data and standard margin per article per day
  • Waiting List data from the CRM system indicating the number of times an out of stock product was placed on the waiting list
  • Article master data from the range management application
  • Demand planning master data with the estimated bought quantity per size which defines the relative importance of each size of an article
  • Web analytics data for product views and conversion rate  
  • Stock quantity data from the online platform with the daily stock snapshot on size level
  • Product range files manually maintained for retail intro date,  marketing campaign information, and original sales forecast quantity per month

 

  1. There are 3 work streams used in the main workflow:
    1.1 Calculation of daily sales forecasts per article number based on the product range files and master data file.

Several operations are done to clean up the data but the most important part is transforming the monthly forecast to a daily level also taking into account the retail intro date. For example if an article has a retail intro date in the middle of the month, we only generate a forecast for the days after that date and not before, to maintain accuracy.

 

Picture1.png

 

1.2 Data cleanse operations done on web analytics and BI data and subsequent join on article and day level

 

For each data type we have created a historical Alteryx database that gets unioned with new cleansed data, which then gets written into the historical database.

 

Picture2.png

 

1.3 Join of the daily sales forecast with the web analytics data, BI data and wishlist data on article and day level

Picture3.png

 

Here we also calculate the actual retail intro date for each article based on the first day when the product gets online traffic, thus allowing us visibility on products that were launched late.

 

  1. In a second workflow we calculate the stock availability per article size and size and buy availability per article. This is based on the master data file indicating the buy percentage per size and article and stock snapshot indicating the size availability per article. The output is a Tableau data extract.

Picture4.png

 

The outputs of the two workflows are then visualized in a Tableau dashboard that has a flow-like structure allowing users to see performance of the product franchises on high level and also drill down into details on article level:

 

Picture1.png

 

Picture2.png

 

Picture3.png

 

Picture4.png

 

 

Describe the benefits you have achieved

First of all, without Alteryx the Trading Dashboard would not have been possible due to the sheer amount of data sitting in different systems and manual work involved in retrieving and combining it at the same level of granularity.

Alteryx has allowed us the possibility to blend a variety of data sources in a scalable way and achieve the following business benefits:

 

  • In terms of time savings, prior to using Alteryx, two full time employees would have been needed to compile an in-season daily snapshot of the most important product franchises (60% of the business) with all the relevant metrics. By the time this report reached stakeholders, the information would have been obsolete and irrelevant to quickly react to consumer behavior in real time. Now with the help of Alteryx it takes 10 minutes per day for the analytics team to provide a holistic dashboard to both senior management and employees who can take quick decisions and steer the business based on real-time data.
  • Increased revenue and margin optimization: Our merchandisers and category managers now have a daily complete overview of how each and every single article is performing. Due to the exploratory and intuitive nature of the dashboard (from top level to detailed article level and coloring based on forecast achievement) they can easily identify which product franchises and individual products are falling behind sales forecast and what specific levers to pull in order to increase sales. Example actions are driving more traffic, improving on-site merchandising, restocking particular sizes, decreasing the price.
  • Customer satisfaction: as sizes are restocked faster than before due to the new proactive way of working of the demand planning department, consumers are also happier that they can purchase their desired sizes. This leads to more customers returning to our site because they know here they can find sizes that are not available in retail stores.

 

We have recently introduced the Trading Dashboard and there is already a mindset shift happening where different departments work more closely together to identify opportunities and act based on the data. We believe Alteryx has enabled us to reach our ambitious growth targets, improve customer satisfaction and operate as a data driven organization.

 

Quantum Spatial Logo Light Blue Q Gray Type on White.jpgAuthor: Pamela Rooney (@prooney) - Business Analyst

Team Members: HeatherAnn Bromell, Amar Shabbir, Dusty Evely, Julio Ramirez

Company: Quantum Spatial, Inc.

Business Partner: Slalom Consulting, Matt Ewalt

 

Awards Category: Best Value Driven with Alteryx

 

Our ERP is great for a lot of things, but it’s not great for tracking historical data. The Enterprise Systems team was charged with creating a way to track project Estimates-at-Complete (EAC) on a week to week basis so that Finance could understand variances in EAC and know where to look if a project EAC varies greatly from its baseline EAC.

 

By automating a weekly pull of project data, not only were we able to greatly impact Finance’s ability to track EACs, but we were also able to create a suite of reports for PMs using Tableau so that they can more easily get in front of large variances in EAC and be proactive in reining in project costs. Being able to accurately track the progress of EAC over the life of projects both helps keep EACs in check, and provides a trail of breadcrumbs to follow in case a project does “go off the rails.”

 

Awards Category: From Zero to Hero

 

A year ago, QSI had no formal BI system in place and the Enterprise Systems team had never heard of Alteryx. Slalom Consulting was engaged for BI advice and they demonstrated how Alteryx could provide value both as a tool for everyday workflows that can be automated to stakeholders and also as a builder of BI resources itself.

 

We were able to start delivering value to the Finance team with smaller workflows almost immediately, but our EAC Snapshot database (described below) has been the “game-changer” that everyone hoped it would be when we had thought it up over a year ago.

 

Additionally, since our ERP and CRM don’t “talk to” each other, we’ve started to bridge gaps in the project life cycle data collection and created the ability to smoothly report on projects from opportunity stage to project closeout.

 

Describe the problem you needed to solve

For years, QSI has been hampered by the inability of our ERP to hold historical data as a “snapshot” of where projects were in their execution at a particular point in time. Project EACs (Estimates at Complete or EAC = Job to Date or JTD + Estimate to Complete or ETC) were visible, but only as of “right now.” Senior leadership wanted the Enterprise Systems team to figure out a way to show EAC variance over time – that is, did the project perform better or worse as time progressed?

 

We were contemplating ways to do this when were introduced to Alteryx by our BI consultant from Slalom Consulting. We immediately saw potential for this to be the answer to our historical data problems. Using Alteryx, we could create a workflow that would take a “snapshot” of all project data each week and use that to compare project data week to week.

 

Since setting up this workflow, stakeholders in Production as well as Finance and Data Acquisition have found immense value in the data being captured each week. In addition to this, pieces of the EAC Snapshot workflow have been used as the basis of a number of reports with completely separate final outputs.

 

Describe the working solutionThis is the entire SQL table creation workflow.This is the entire SQL table creation workflow.

For the EAC Snapshot (and most other workflows), QSI pulls data from the ERP SQL tables and transforms those data in a number of ways.  Figuring out the SQL tables was half the battle with this workflow.  Inputs include:

 

Baseline (all joined in the workflow down to the task and resource level)

  • Consultants (all subcontractors being used on a project)
  • Expenses (all expenses from those working on the project)
  • Labor (all labor for a project)
  • Units (all acquisition assets used on a project – aircraft and sensors)

Job to Date (all joined in the workflow down to the task and resource level)

  • Consultants
  • Expenses
  • Labor
  • Units

Estimate to Complete (all joined in the workflow down to the task and resource level)

  • Consultants
  • Expenses
  • Labor
  • Units

Along the way, qualitative data such as project manager, account manager, and subvertical are added in addition to data from several custom data keys that are stored in Google Sheets. Date filters are used to show the data “as of the end of the previous week” so that we can easily define fiscal periods and make sure all data are appropriately accounted for.

 

qsi 2.png

 The workflow is run and the output table is written to our SQL server in early hours of Thursday mornings for two reasons: on Thursdays, we can be sure that all timesheets and units have been submitted for the previous week, and so as not to hit the database too hard during working hours.

 

Following the scheduled run of the workflow, a second workflow is run to retrieve the data from the EAC Snapshot database and convert it into a .tde so that Tableau can easily digest it. The EAC is then compared to the project baseline for variance in gross margin. There is also a trend version which shows changes week to week. Several other calculations give insight to how a project has progressed over time.

 

Describe the benefits you have achieved

For the EAC Snapshot (and most other workflows), QSI pulls data from the Deltek Vision SQL tables and transforms those data in a number of ways. Figuring out the SQL tables was half the battle with this workflow.

 

If the Enterprise Systems team were attempting to collect these data each week without Alteryx, it would take many, many hours per week. Maybe 40? Thank goodness we never had to try it. PMs save time because they no longer have to try to cobble together several reports to get some of the information they need. Also, they no longer have to try to root around in hard-to-read ERP reports to get to their bottom line.

 

Considering that none of us is a DBA, constructing this database without Alteryx would be scary at best and impossible at worst. The success of this endeavor has Enterprise Systems considering ways that Alteryx could help with client deliverables in our production environment.

CRC_logo.pngAuthor: Renilton Soares de Oliveira - Executive Director, Conjecto

Team Members: Ricardo Mendes, Roberto Teófilo, Valter Cazassa

Company: Central de Recuperação de Crédito - CRC

Business Partner: Conjecto

 

Awards Category: From Zero to Hero

First of all, as far as we know, CRC was the first Alteryx customer in Brazil. Therefore, we were starting using Alteryx from Zero not just from a single company perspective, but also we were starting a journey that was inspiring for several other companies in Latin America. In fact, CRC’s success story was shared in the first two Alteryx User Group meetings in Brazil.

 

After starting using Alteryx in early 2014, in less than one month we have eliminated all the risky, time-consuming manual activities of one of the most important data blending processes which usually took four days of hard work of a CRC's line of business (LOB) analyst. Now, using Alteryx workflows, the whole process runs in less than 15 minutes, which has brought significant productivity improvements and more accurate results not only for this particular process, but also for several others that depend upon it. The extraordinary results obtained in the first Alteryx use case at CRC had sparked new initiatives to explore other opportunities with data blending and advanced data analytics. Alteryx is now an essential part of CRC business operations.

 

Awards Category: Best Value Driven with Alteryx

In 2015, just two years after started using Alteryx, CRC has experienced almost 30% revenue growth and a reduced 15% in its headcount. That’s a remarkable accomplishment for our business and we have no doubt that Alteryx solutions and Conjecto analytics services have played a very important role to make that happen. We love the independence and flexibility that Alteryx brought to our LOB analysts. Our users are much more productive and CRC is now a more data-driven company that it used to be. That was a big boost to our business.

 

Describe the problem you needed to solve

When CRC first started using Alteryx in the first quarter of 2014, the company was beginning a rapid expansion of its business activities that eventually led it to bring more strategic accounts to its customer base and, as a natural consequence, more data sources and complexity to its operations. In this challenging scenario, it would be virtually impossible to reach its goals without innovative, effective solutions for data analysis and decision support in different levels of the organization.

 

Alteryx solutions have been extremely helpful for us to solve different, relevant business problems. A major challenge for CRC prior to using Alteryx solutions was the need to quickly and effectively improve the productivity of daily critical and complex business processes based on data blending from different, heterogeneous sources with huge amount of information that could not be handled timely and appropriately with traditional software systems. Moreover, CRC operations performance was looking for effective ways to use machine learning algorithms to improve business performance without paying expensive software infrastructure and hiring expensive data science experts.

 

Describe the working solution

Alteryx solutions have been extremely helpful for us to solve different, relevant business problems. A major challenge for CRC that was solved with Alteryx software was the productivity of daily critical and complex business processes based on data blending from different, heterogeneous sources with huge amount of information that could not be handled timely and appropriately with traditional software systems.

 

CRC has also implemented more advanced analytics based on Alteryx predictive and spatial tools. One of the main business problems that we have successfully solved with those tools is identifying which are the individuals or companies that are most likely will respond positively to an action that will eventually lead them to paying their debts. That’s pretty much what we need to do on a daily basis to get better results to our customers and to our organization.

 

Regarding data blending functionality, CRC has taken advantage of most of data prep tools, including data preparation, parse, join, transform, developer, and inDatabase. To implement advanced analytics applications and macros, our team has used predictive (e.g., boosted, logistic regression, and decision tree models) and grouping tools (for unsupervised learning).

 

Describe the benefits you have achieved

In fact, all sponsors for this initiative recognize the extraordinary results with what have been accomplished thus far regarding business growth and customer satisfaction. Business and decision making processes have been greatly improved which had a profound impact corporate results. In 2015, just two years after started using Alteryx, CRC has experienced almost 30% revenue growth and a reduced 15% in its headcount. That’s a remarkable accomplishment and we have no doubt that Alteryx solutions and Conjecto analytics services have played a very important role to make that happen. Nowadays, CRC is well-known in Brazil as one of the most analytics-driven company in financial services. 

 

CRC-1.png

Author: Aaron Harter (@aaronharter), Media Ops Manager

Company: Quigley-Simpson

 

Awards Category: Best Use of Alteryx Server

 

We leverage our Alteryx Server to design and implement custom apps that allow for any team member at the Agency to benefit from the power of Alteryx, without the programming knowledge necessary to construct a solution on their own.  Analytic apps allow for all employees at Q-S to leverage the capabilities of Alteryx in a fun and easy to use interface.

 

1- QS Gallery Collections.jpg

 

Describe the problem you needed to solve 

Any company can own, buy or hold data. Finding creative applications to use data to drive informed decision making and find opportunities in a market is what separates the wheat from the chaff, regardless of industry.

 

Quigley-Simpson is an advertising agency in the highly fragmented media industry and the unique problems include managing rapidly changing marketplaces with dozens of disparate data sets and supporting many teams with varying reporting needs. The Media Operations team has been tasked to implement custom solutions to improve efficiency and make sense out of the big data coming in the agency.

 

Media measurement is highly reliant on quality data sourcing, blending and modeling, and we have been able to use Alteryx as a centralized environment for handling and processing all of this data across many formats. We have worked closely with key stakeholders in each department to automate away all of their "pain points" relating to data and reporting and interacting with our media buying system.

 

Describe the working solution

Some of our apps join our media buy, audience delivery history with our client's first party data and the related third party audience measurement data from Nielsen. Other third party data sources we leverage include Digital and Social Media metrics, GfK MRI demographic and psychographic market research, TIVO TRA set-top box data combined with shopper loyalty data, MediaTools authorizations and strategic planning on the brand level, AdTricity digital feedback on pre-, mid-, and post- roll online video campaigns, and comScore digital metrics for website activity.

 

2 - QS App Design.JPG

 

Expediting the processing, summarizing, cross-tabbing and formatting of these data sets has added an element of standardization to our reporting which did not exist previously while improving the speed and accuracy. An app we built for the one of our teams produces over 50 reports, ready for distribution, in less than 3 min, replacing a process that used to take a full day to accomplish.

 

3 - QS Top 20 Data Blending Workflow.JPG

 

Additionally, we are using spatial tools to analyze delivery and performance of pilot Programmatic Television test, which aggregates local market TV inventory to represent a national footprint. Several of our workflows blend and prep data for visualization on our in-house "Data Intelligence Platform" which is powered by Tableau. This is then used by our media planners and buyers to optimize campaigns to meet goals and exceed client expectations.

 

The flexibility to build out apps or dashboards, depending on the needs statement of the end user, has been phenomenal and very well received at the Agency.

 

4 - QS Automaded Reporting Model.JPG

 

Describe the benefits you have achieved

Now that we are an Alteryx organization, we are replacing all of our outdated processes and procedures with gracefully simple workflows that are propelling the Agency to the forefront of technology and automation. Our report generating apps have improved the accuracy, reliability, and transparency of our reporting. The log processing apps have saved thousands of hours of manual data entry. Now that our workforce has been liberated from these time consuming, monotonous tasks, we are wholly focused on growing our clients' business while better understanding marketplace conditions.

 

Streamlining the workflow processes has allowed for drastically reduced on-boarding times while maintaining data integrity and improving accuracy. It has been a primary goal to give all employees the tools to increase their knowledge base and grow their careers by improving the access to data they use for daily decision making, a goal we are achieving thanks in large part to our Alteryx Server.

 

2016 Alteryx Server app totals (as of 4/22/16):

  • Teams using apps = 7
  • Number of apps = 44
  • 2016 app run count = 1,794
  • 2016 time savings = 4,227 hours

Author: Thomas Ayme, Manager, Business Analytics

Company: Adidas International Trading B.V

 

Awards Category: Name Your Own - Best Planning and Operational Use

 

Describe the problem you needed to solve 

As a new and successful business adidas Western Europe eCommerce keeps on growing faster and faster; new services are being launched every week, an increasing number of marketing campaigns are being driven simultaneously, etc. This leads to more and more products having to be shipped out every day to our end consumers.

 

This strong growth leads to an exponential increase of the complexities when it comes to forecasting our units and orders volumes, but also to bigger costs in case of forecasting mistakes or inaccuracies.

 

As these outbound volumes keep on increasing, we were being faced with the need to develop a new, more accurate, more detailed and more flexible operational forecasting tool.

 

Such a forecasting tool would have to cater to the complexities of having to forecast for 17 different markets rather than a single pan European entity. Indeed, warehouse operations and customer service depend on a country level forecast to plan carriers and linguistic staff. This is a very unique situation where on top of having a rapidly growing business we have to take into account local marketing events and markets specificities.

 

Finally, given the importance of ensuring consumer satisfaction through timely delivery of their orders, we also decided to provide a daily forecast for all 17 markets rather than the usual weekly format. Such a level of details improves the warehouse's shipping speed but also increase once again the difficulty of our task.

 

Describe the working solution

 

Our first challenge was to find reliable sources of information. Both business analytics (financial and historical sales data) and web analytics (traffic information) data were already available to us through SAP HANA and Adobe Analytics. However, none of our databases were capturing in a single place all information related to marketing campaigns, project launches, events, adhoc issues, etc.

 

That is why we started by building a centralized knowledge database, which contains all past and planned events that can impact our sales and outbound volumes.

 

This tool is based on an Alteryx workflow, which cleans and blends together all the different calendars used by the other eCommerce teams. In the past, bringing those files together was a struggle since some of them are based on Excel while others are on Google Sheets, moreover, all are using a different format.

 

Workflow Knowledge database.jpg

 

We made the best of this opportunity of now having a centralized event database by also developing a self-service visualization tool in Tableau, which displays all those past and future events. Such a dashboard is now used to:

 

  1. Give some background to our stakeholders about what is driving the volumes seen in the forecast.
  2. Have an overview of the business during our review the sales targets of the coming weeks, etc...

 

In a second time we created a workflow, which thanks to this new centralized event database, defines for each past and upcoming days as well as for each markets a set of "genes". These genes flag potential adhoc issues, commercial activations, level of discount, newsletter send outs, etc.

 

This gene system can then be used to define the histoical data to be used to forecast upcoming periods, by matching future and past days that share the same or at least similar genes. This is seen as the first pillar of our forecasting model.

 

The second pillar of our forecasting tool is a file containing our European weekly targets. These targets are constantly being reviewed based on new events shown in the centralized event database and current business trends. 

An Alteryx workflow derives from this target file our sales expectation for each upcoming day, market, category (full price, clearance) and article type (inline or customized). In order to do so, we use historical data defined by our genes in addition to a set of algorithms and calculate the sales impact ratio of each market and category. These ratios are then used to allocate a target to each one of the combination.

 

worlkflow forecasting.png

 

Finally, both pillars are brought together and we derive in a final Alteryx workflow, how many orders and units will have to be placed in each markets and from which article type.

 

However, since certain periods of time have genes combinations that cannot be matched, our working solution also gives us the flexibility to manually override the results. These forecast volumes are then shared with the team, warehouse, customer service call centers, etc. through a Tableau dashboard.

 

Knowledge database.png

 

Describe the benefits you have achieved

Thanks to the work that went into developing this new forecasting model in Alteryx, the adidas WE eCommerce business ended up getting:

 

  • A more accurate forecasting model, which allows for a better planning of our operations.
  • Reduced operational costs.
  • A more detailed forecast as we can now forecast on a daily level, when past methods required much more work and limited us to a weekly forecast.
  • A flexible forecasting model that can easily be modified to include new services and sales channels.
  • A forecast dashboard that lets us easily communicate our forecast to an ever growing number of stakeholders.
  • A centralized event “calendar” that can be used by the entire department for much more than simply understanding the forecast (e.g. it is used to brief in Customer Service teams on upcoming events).
  • A massive amount of free time that can be used to drive other analyses, as it is not required from us anymore to manually join together different marketing calendars and other sources of information, create manual overviews of the upcoming weeks, manually split our weekly sales target, etc.

DIG-logo.pngAuthor: Allen Long (@datawizard) - Chief Data Strategist

Team Members: Jack Pitts, Itzela Vasquez de Mundis

Company: Data Intelligence Group

 

Awards Category: Best Value Driven with Alteryx

 

Leveraged the breadth of the Alteryx platform to create a full end-to-end campaign management system for the world’s largest appliance manufacturer. The resulting solution drives a very aggressive $40M revenue growth goal combining industry-first marketing concepts developed at DIG with the industry-leading ETL/BI/Analytics toolsets found in Alteryx. This solution is unique in its application and scale of the Alteryx toolset to manage a complete marketing campaign management system from ETL to Analytics to Sales fulfillment.

 

Describe the problem you needed to solve

40M dollars in potential revenue was not being realized.

 

The largest appliance manufacturer in the world (OEM) fell short of their revenue targets for aftermarket warranty sales and suspected that the inefficient processes used to take their customer data from product sale to solicitation of sales for aftermarket warranties was the probable culprit.

 

The problem stemmed from complexity and a lack of visibility into the data.

 

Previously, the Third Party Administrator (TPA) in charge of marketing the OEM’s warranties relied on multiple vendors who each operated in a silo to handle their piece of the marketing campaigns.

 

The process looked something like this:

 

DIG1.png

 

The results didn't meet expectations, and the lack of transparency meant each vendor operated in a black box, making observation, performance analysis, and campaign improvement impossible for the OEM.

 

This disjointed effort and the amount of unrealized revenue prompted a search for a new solution.

 

Some of the challenges presented to DIG included:

 

  • Managing marketing campaign data for 18 million customer product leads (potential of 250 million+ consumer touchpoints)
  • Receiving 500K new or updated consumer leads each week
  • The need to increase speed to process - Multi-variate campaign changes had taken too long to implement
  • Pairing the right message with the right customer - Customization of individual mail pieces at scale
  • A desire to take intelligent, strategic action based on the data

 

Describe the working solution

Using Alteryx, disparate data sources are aggregated, unified and processed to allow greater visibility and auditing of results to drive revenue growth through optimized marketing messages.

 

Specifically, the solution is driven by the following processes:

 

Data Hygiene by allowing more efficient automation in file transfers with messaging and daily reporting for leads and their statuses.

 

Customer Lead Management improved by creating variable product bundling.  This added the ability to assemble multiple products into a single offer.  Also, we initiated a process using Alteryx to rescue bad leads which resulted in a larger, more accurate universe of marketable prospects.

 

Campaign Management analytical overview of customer data elements combined with specific behavior triggers to craft an individual message for each contact event.  In addition to dynamic messaging from over 20 thousand variants, Alteryx allows dynamic event assignment which allow customized event timing.

 

Creative Library is integrated with Alteryx which allows dynamic personalized messaging because we can now monitor numerous data fields (and changes within those fields) to immediately drive relevant messaging changes.  We added a table-driven process in Alteryx to add creative parts that are both virtual and physical inventory.  This allows complete customization to define their inclusion and placement within the message.

 

Implemented Automated Test Management to allow a new process to fully manage the planning, data selection, implementation, forecasting and results of the creative tests.

 

Price Elasticity incorporated into the Alteryx process, we can continually modify and measure price elasticity within a multivariate pricing model as part of the ongoing marketing strategy.

 

Visibility due to new reports available because all applications are executed inside of Alteryx rather than in multiple, isolated vendor systems.

 

Loyalty Management Processes are planned to lay the foundation for lifetime customer value analytics that will allow the client to see beyond the immediate campaign and provide a holistic view of the entire customer household value over a longer term.

 

DIG takes advantage of the breadth of Alteryx tools, using 15 of the available Alteryx tool groups including 2371 tools across 23 workflows in the solution.DIG2.pngDIG leverages the power of Alteryx to enhance the revenue streams through an improved campaign management system. The new streamlined process provides 100% visibility, 30% faster throughput, and customized, data-driven marketing materials.

 

DIG3.png

 

Describe the benefits you have achieved

Alteryx has powered process improvements and efficiencies to drive progress in realizing the challenge goal of generating $40Million in revenue due to incremental lifts in various process applications and rates.

 

The previous solution was slow, cumbersome and static.

 

The Alteryx solution is responsive, fast and optimized.

 

The business value of Alteryx is realized in four distinct areas:

 

Speed of Processing allows more dynamic, efficient and greater volume of marketing touch points.

  • Data acquisition to prospect messaging is 30% faster using Alteryx over the previous solution. This opens the ability for time-sensitive marketing messaging. Formerly, without Alteryx, the entire universe was sent a static message.  With Alteryx, the speed of processing allows DIG to process and adjust information to send a dynamic message to a test audience first, and then an optimized message can be deployed based on the results.
  • Specifically, over 9.125 million product leads were processed in January and February 2017 alone.

Increased Visibility

  • The Alteryx solution has increased visibility into the customer lead journey, meaning that the historic data for every lead, and each change made to that same lead over time, can be tracked and measured.
  • The client has 100% visibility due to weekly waterfall reports with summary and detail on 30+ disposition codes. DIG interprets the data which enables the client to make data driven marketing decisions.
  • These performance analytics mean the TPA and OEM can be more efficient with their marketing dollars and time.

Revenue

  • The attach rate of each customer contact is increased by 20% using Alteryx integrated with the Creative Library. Dynamic messaging coupled with dynamic timing result in a highly customized message that is presented to the prospect to increase the likelihood of conversion.  This revenue optimization is made possible because of the customization powered by Alteryx.
  • In addition to the increased attach rate, the number of contacts that are valid prospects are increased because Alteryx efficiencies allow us to recover approximately 100K previous lost leads monthly due to its ability to complete records. These previously unmarketable records become valid prospects and increase the base numbers of contacts to message.

Advanced future analytic opportunities by housing the solution inside of one system

  • Alteryx enables the development of a Lifetime Customer Value analytics.
  • Ability to develop a customer rewards program to deepen brand loyalty.

DIG has built and sustained a business model for over 10 years powered by Alteryx.  This application showcases the business value of Alteryx and how the system can maximize efficiencies and generate new income for best in class results.  The revenue benefit is such that the world’s largest appliance manufacturer trusts DIG, a collective of data strategists in Franklin, TN, to harness the powerful tools in Alteryx, to realize a 40-million-dollar potential gain.

Author: Michael Peterman, CEO In-2CRev-28px-R.png

Company: VeraData

 

Awards Category: Best 'Alteryx for Good' Story

 

We provide deep analytics services for hundreds of clients.  Of particular interest is the NCCS (National Childrens Cancer Society).  This prestigious and highly respected organization has been doing more for the families of children with cancer since 1987 - yep, for almost 30 years.  We are honored to be serving them as a client.

 

Describe the problem you needed to solve 

NCCS, like every other large charity in America, sends out direct mail fundraising solicitations to support these families.  Like any other business has to spend money to acquire new customers, non-profit organizations spend money to acquire donors.  They were faced with a year over year trend of increasing donor acquisition costs and increasing costs to reactivate lapsed donors.   This was coupled with a concern was that there was a shrinking universe of potential donors who were willing to support their efforts.

 

Describe the working solution

Enter VeraData. Our initial engagement with NCCS was to build a donor acquisition model to reduce their costs to acquire donors, which subsequently reduces the cycle time to break-even on the investment in new donors. Concurrently, we developed a lapsed reactivation model that used tons of external, outside information to select from their audience of former donors the individuals most likely to donate again, therefore increasing the universe of marketable names while maintaining the cost to reactivate. Lastly, our third component was to uncover an expanded universe of individuals who had the propensity to support the NCCS. This meant identifying new data sets and determining which individuals would be profitable to pursue.

 

There were several methodologies deployed to achieve these goals. Our analytic team settled on a series of support vector machine models solving for response rate, donation amount, package and channel preferences, etc. All of the information in our arsenal was called upon to contribute to the final suite of algorithms used to identify the best audience. Using Alteryx, R, Tableau and our internal machine learning infrastructure, we were able to combine decades worth of client side data with decades worth of external data and output a blended master analytic database that accounted for full promotional and transactional history with all corresponding composite data on the individuals. This symphony achieved all of the goals, and then some.

 

Macro.png

 

workflow (2).png

Describe the benefits you have achieved

The client experienced a 24% reduction in their cost to acquire a donor, they were able to reactivate a much larger than anticipated volume of lapsed donors (some were inactive for over 15 years) and they discovered an entirely new set of list sources that are delivering a cost to acquire in line with their budget requirements. Mission accomplished.

 

Since that point, we have broadened the scope of our engagement and are solving for other things such as digital fundraising, mid-level and major donors. Wouldn't have been possible to do with the same speed and precision had we not been using Alteryx.

Author: Alberto Guisande (@Aguisande), Services Director

 

Awards Category: Most Unexpected Insight - Proving teachers wrong - Apples & Oranges can be compared! (thanks to Alteryx)

  

Describe the problem you needed to solve 

Our customer is a Public Transportation company, in charge of buses going around the city of Panama. They transport more than 500K passengers a day (1/6 of the total population of the country). Almost 400 routes, with 1,400 buses going around the city all days, working 24/7, reporting position every a few seconds. The company is supporting its operation with a variety of tools, but at the time to put all data together, they realized there was no "point of contact" in the data. They have to compare apples & oranges! Really? Why does the saying exist? Because you can't! So we started trying to do the impossible!

 

BTW, the business questions are pretty simple (once you got the data!): What route was every bus in, when every transaction occurred? What is the demand of every route? and for every stop?

 

Describe the working solution

Working with Alteryx, we were able to analyze data coming from three different sources, where the only common information was some LATITUDE & LONGITUDE (taken with different equipment, so the accuracy was, at least, questionable) at some random points in time. The data was received in several files:

 

  • Routes: Contains the ID & the name of every route. Stop Points: Containing every bus stop, its LAT & LONG, and the stop name
  • Pattern Detail: Containing every route, its stops and the sequence of those stops in a route
  • Some remarks: A lot of stops are used by different routes, and there are some stops, where the bus pass through, that are not part of the specific route the bus is at

 

So far, the easy part! We managed very easily to get all this info together. Now the tricky part: There mainly two operational datasets: AVL (Every position of every bus, every n seconds, where n is an arbitrary number between 0 and what the piece of hardware wanted to use). BTW, a huge amount of data every day.

 

Transactions: transactions registered in time, in a bus. As you may infer, there are no data in common that allow us to match records beside an arbitrary range of latitude and longitude in some random time ranges. Because of how everything is reported, the bus may be passing in front a stop that is part of another route, or stopping far from the designated stop.

 

Describe the benefits you have achieved

With this solution, the company can start analyzing activity per route, demand per bus, route, stop, etc. Without Alteryx, this customer information still be looking like apples and oranges! We were able to make it sense and allow them to use it to get insights.

 

Colorful note(and some ego elevator) : 5 other vendors took the challenge. No other one could reach a glimpse of solution (of course, "no Alteryx, no gain").

 

Processpng.png

Process2.png

Process3.png

Author: Andrew Kim, Analyst (@andrewdatakim)

 

Awards Category: Name Your Own - Scaling Your Career with Alteryx

 

Describe the problem you needed to solve 

Deciding on a tool to invest your time in is a problem everyone faces in their career. Learning to blend the tools given to us in college versus what the professional world is actually using are starkly different. I have quickly discovered to have a career that has both the opportunity to start from a company from scratch and the flexibility to work in a Fortune 100 environment requires the knowledge of assets that can scale without a significant investment of time or money.  My background is in Marketing and Finance with most of my work experience in small to midsize companies where every person is required to be/do more for the company to survive.

 

Describe the working solution

I set out to find these tools 3 years ago with the understanding that information drives a business, which lead me to Gartner report. I went through trials of a dozen different options and even had contracted assistance from a developer of one of the software options. Alteryx quickly became my option of choice which greatly contributed to my  previous company's growth from $250k in annual revenue online to $12 million in 2 years. The ability to access multiple data source types, leverage Amazon MWS data and use historical competitive landscape information allowed us to create the perfect dashboards in Tableau to analyze current inventory and buying opportunities that were previously inconceivable.  I was able to save 10,000 labor hours a day in discovering new products. Prior to Alteryx being purchased the average buyer's assistant could run 200 Amazon listings per 8 hour day. After Alteryx we were retrieving over 250,000 listings per run multiple times a day (The math: 250,000/25 listings per hour=10,000 hours per run). The primary customer in this scenario were the buyers for the company. By taking all of the data processed through Alteryx and providing them with Tableau dashboards to conveniently view current and historical product information compared to the previous Excel models we were able to maximize inventory turnover and margins.

 

Describe the benefits you have achieved

Alteryx knowledge allowed me to advance to my current company and position in a Fortune 50 company where I am a Data Analyst/Programmer. I now work heavily with survey data and again Alteryx has proven an indispensable asset even with the change in scale. Its versatility has allowed all of my skills to transfer from operational data to qualitative without skipping a beat. I find Alteryx is an asset that has only increased my passion for data and I am eager to see how I can continue to scale my career with it.

K-LOVE_logo.pngAuthor: Bill  Lyons  - Principal Data Scientist

Team Members: Trudy Fuher, Alana Welz, Arlyn Baggot

Company: Educational Media Foundation

 

Awards Category: Best ‘Alteryx For Good’ Story 

The initial project has the potential to save this non-profit organization up to $2.2 million per year in streaming costs when recommendations are fully implemented. Other use cases improve internal efficiencies, communication, and productivity.

 

Awards Category: Best Use of Alteryx Server for Analytics Deployment

Alteryx Server automatically processes daily file downloads, weekly file downloads with decompression, decryption and bulk insertion, and monthly zip code DMA assignments. Other use cases support self-service imports, exports and reporting.

 

Awards Category: Best Use of Alteryx for Spatial Analytics

Alteryx spatial tools combined with Alteryx data is driving optimization of regional streams associated with DMAs.

 

Awards Category: Best Value Driven with Alteryx

Optimizing regional streams has resulted in at least $500,000 in savings since July 2016, with recommendations implemented so far. When all recommendations are fully implemented, savings could be $2.2 million per year or more.

 

Awards Category: From Zero to Hero

Even though we purchased our first Designer license in June 2015, as of early March 2016, we had not created a single workflow with Alteryx. We were considering not renewing our license. At that time, we got a new rep, Nick Glassner, who arranged for a couple of WebEx sessions with Alteryx Solutions Engineer Ali Sayeed to get us started on a real project. Within a few weeks, I recognized many more potential applications for Alteryx, and was off and running. I changed from a skeptic to an enthusiastic user. Analysis for this project began in mid-April and was completed in mid-May. We acquired Alteryx Server in June, and had the first phase of the implementation of this project running on a daily schedule by August. Other phases came online in November and in January 2017.

 

At that point, I was still the only person using Alteryx heavily in analysis and production. So, I began some internal workshops showing how to solve real-world problems with Alteryx. We now have 3 more internal users becoming productive with Alteryx, and are looking to hire another. Some of these users are also taking advantage of the “Enablement Series” offered by our new rep, Tim Cunningham.

 

Describe the problem you needed to solve

Initial business problem: Recent regulatory changes caused our national internet radio streaming costs to more than double, from less than $1 million to over $2 million annually. The goal was to find ways to optimize our streams to move usage from the national stream to our underutilized regional streams, and thus reduce our costs.

 

Other use cases, including their business challenges, solutions, and benefits, follow the solutions and benefits of this initial business problem.

 

Describe the working solution

Alteryx played a major role in analysis of the streaming data. Some of the regional streams were underutilized, while others exceeded their cost effective limits, so the first phase was to analyze the accuracy of IP address geolocation software to see what would be causing this. The website systems and the log analytic systems used different IP geolocation software (the websites used IP2Location, and the analytic systems used Maxmind) so we needed to know if one was better than the other, or if neither was adequate. However, these system are isolated from each other by firewalls, making direct comparisons impossible. Alteryx Designer allowed me to connect to three different SQL Server database systems and compare their data with a .csv file from another vendor being evaluated (NetAcuity).

 

This analysis made extensive use of Alteryx spatial matching and Alteryx spatial data, visualizing results with Tableau. It revealed some disturbing facts, including that the geolocation was very inconsistent between the systems. As an example, we found that less than half of the listeners to the New York City stream were even in the NYC DMA (Figure 1).

 

Figure 1Figure 1

 

Additionally, we learned that only a little more than half of the listeners in the NYC DMA were listening to the NYC stream. (Figure 2)

Figure 2Figure 2

 

 The analysis also compared actual registered listener locations to the location reported by the various services. This showed that IP2Location was clearly inferior. (Figure 3)

 

Figure 3Figure 3

But Maxmind returned a significantly higher number of unknown locations, both within the US, and even identifying the country. (Figure 4)

 

Figure 4Figure 4

 

The analysis concluded with 16 recommended changes to systems, software, programming and contracts.


One of those recommendations was to unify both the websites and the analytics on the same and most consistently accurate IP address geolocation provider: NetAcuity. Alteryx supports the updates to the NetAcuity database by downloading the data from NetAcuity, decompressing, decrypting, and bulk inserting it into SQL Server. It does this on a weekly schedule in Alteryx Server, each time moving roughly 40 million rows of data in about an hour.

 

Primary workflow:KLOVE-5.png

 

Supporting macros:KLOVE-6.png

 

 

An Alteryx Server scheduled app then builds Calgary databases of the IP geolocation data.KLOVE-7.png

 

 

Next, another Alteryx Server scheduled app applies that geocoding to the streaming log data.KLOVE-8.png

 

 KLOVE-9.png

 

 

Alteryx spatial data also supports Server scheduled monthly updates to keep zip to DMA to stream assignments up to date.

 

Describe the benefits you have achieved

4 of the 16 recommendations have been implemented to date, saving over $500,000 since last July, and an estimated $700,000 for 2017. More steps are in development, with a goal of saving $2 million per year.

 

Never before did we have a reliable and up-to-date zip code to DMA assignment process. We previously bought zip code to DMA data from Nielsen, but it was incomplete and quickly out-of-date.

 

Other Significant Alteryx Use-Cases

 

1. Transmitter location identification

  • Business Challenge: Property tax filings must be made with the appropriate jurisdiction for the location of the property. With normal property, the street address easily identifies that jurisdiction. However, radio transmitter sites are frequently in very remote locations where there is no street address, and frequently on tops of mountains, within a few feet of jurisdictional boundaries. Historically, property tax accountants manually used transmitter location geographic coordinates to search maps to identify state and county with which to file property tax forms. This very laborious process took a team of 3 or 4 people up to 8 weeks each year, and was fraught with error.
  • Solution: Alteryx Server scheduled app performs spatial match between transmitter geographic coordinates and Alteryx spatial data, precisely and accurately identifying and coding transmitter location state and county. Run time: about 15 seconds per day, automatically. This simple workflow took only a couple of hours to build and deploy.KLOVE-10.png

     

  • Benefit: Savings of up to 8 man-months of manual labor per year. Reduction in errors (this process identified more than 200 instances where the location was either undocumented or in the wrong jurisdiction; 2 were even in the wrong state).

 

2. Log file FTP download

  • Business Challenge: The system downloading new log files from content delivery network (CDN) daily was very fragile, requiring manual checks and restarts every few days.
  • Solution: Alteryx workflow app, scheduled to run daily, downloads list of available files, compares list to list of previously downloaded files, downloads new files, updates list of files downloaded.K-LOVE-11.png

     

     KLOVE-12.png

     

 

 

  • Benefit: Alteryx job has run without error for 8 months. Saves time (about an hour per week) monitoring and maintaining each week, but it is mostly a huge reduction in the "hassle factor." Time to develop was less than a couple months’ worth of manual corrections.

 

3. User import of Excel into SQL Server

  • Business Challenge: Data files from mobile app vendors come each month in Excel files and need to be imported to SQL Server. This import required a DBA to manually import, and was consequently a year behind.
  • Solution: Gallery app allows users to upload files themselves, automatically removes duplicate data, reports duplicates ignored, structure errors, and data imported.KLOVE-13.png

     

     

 

  • Benefit: Self-service of data import relieves workload of DBAs and allows users to have immediate reporting of data in Tableau. This process also revealed that the supplier had duplicate records that overlapped between months. This had created erroneous data of which we had not previously been aware.

 

4. Tealium reporting

  • Business Challenge: Connecting Tableau directly to Redshift was slow.
  • Solution: In-Db tools query Redshift database, filter, aggregate, and download to Tableau Server Data Source Extract. App is scheduled in Alteryx Server. 

     

    KLOVE-14.png
  • Benefit: Faster Tableau reports

 

5. Studio automation logs

 

  • Business Challenge: Log files have been inconsistent and incomplete, with gaps and overlaps, making downstream reports unreliable.
  • Solution: Download tool connects directly to REST API of studio automation software, parses the JSON, and inserts into SQL Server data warehouse. Scheduled in Alteryx Server daily.KLOVE-15.png

     

     

  • Benefit: Reliable data for reporting.

 

6. Record of donor communication

 

  • Business Challenge: Producers call donors to record their stories, logging that call in Google Sheets. Donors call back, talking to communicators in the Listener Services department who have no visibility to the Google Sheets, and there was no record in the donor system. Awkward conversations ensued.
  • Solution: Alteryx Server app scheduled to run every 5 minutes connects to Google Sheet, downloads the call records and insert records into the SQL Server donor system of record.KLOVE-16.png

     

  • Benefit: Listener Services communicators can now intelligently communicate with donors.

 

Author: Qin Lee, Business Analyst

Company: MSXI

 

Awards Category: Most Unexpected Insight

 

Huge data, large file and multiple applications have been created and saved and shared in a small size of Alteryx file. And now, I can test the script/coding and find the errors. This is the good way to develop the proof of concept for our company.

 

Describe the problem you needed to solve 

We need to go through many applications to get the data and save into one location to share and view.

 

Describe the working solution

We are blending the data sources form SQL, Access Excel and Hadoop, Yes, we are leveraging many parties' data. We are developing the workflows and functions for a concept now. Yes, we are exporting to a visualization tool.

 

image002.jpg

 

image004.jpg

 

Describe the benefits you have achieved

Collected the data from many locations and saved into a small size of the Alteryx database file and created the workflow and function and developed a search engine and design the proof of concept for approval and launch. Saved time and resolved the problem and increased customer satisfaction. I would like to send my sincere thanks to Mr. Mark Frisch (@MarqueeCrew), who helped us for many days to finish this project.

DSC_0035.JPGAuthor: Erik Miller (@erik_miller), Sr Systems Engineer - Cyber Security Analytics

 

Awards Category: Most Time Saved

 

Describe the problem you needed to solve

My team's story starts from the ground level of analytics: no tools, no resources, no defined data sources. But our Information Security team had an idea: to be able to report out on all of Western Union's Agent Locations (think Kroger grocery stores, mom & pop shops, etc) and the risk they posed by not having certain security measures implemented - look at every PC/terminal they have to determine their individual risks (2.4 million when we started), their fraud history, their transaction limits, etc, etc. and risk-rate every one of those 500,000+ Locations. We completed a proof of concept and realized it was completely unsustainable, requiring over 100+ hours every month to be able to produce, what outwardly looked like, a simple report. We took that process and built it out in Alteryx. And with just a little over 2.5 hours with the tool, we took a process which dominated my time and turned it into a 5 ½ minute layout of time. What's more, we've turned this POC project and turned it into a full-fledged program and department, focused on risk analytics surrounding employee & contractor resource usage (malicious or uneducated insiders), customer web analytics (looking for hackers), and further Agent analytics.

 

Beyond our humble beginnings, there's the constant threat of data breaches, fraud, and malicious insiders in the Information Security world - it's the reality of the work we do. Having the ability to build out an strategic analytics program has been a huge step in the right direction in our industry and company & not an area which many other companies have been able to focus on, which also sets us ahead of the curve.

 

Describe the working solution

We are using Alteryx to assess several data sources - HR data sets for active/terminated employees & contractors, clickstream data from our digital assets and websites, security data from our Netezza system, fraud data, log files from our various security platforms, user behavior data from our UBA (User Behavior Analytics) system, Identity and Access Management attributes/entitlements, system infection logs, installed applications, etc., etc. As I've said in other talks, we don't have a data lake, we have an ocean.

 

We are currently exporting our data to Tableau tde files, Hadoop, and MySQL databases. In addition, we have started looking/experimenting with our Alteryx Server implementation (which I support for our company).

 

Describe the benefits you have achieved

Overall time savings is nearing 150 hours a month, so a massive savings and an ability for our team to stay incredibly lean - no additional FTEs needed to keep taking on more and more data and challenges. We've also been able to give visibility to the security implementations for all of our 500,000+ worldwide locations - something which we didn't have visibility to prior to now, and which helps us drive the business to implement security features where needed - based on logic, numbers, and fraud data, not feelings.

 

We also are able to provide insights into our user base - how are our employees using our assets, what are they doing that's lowering our security posture, how are they getting infected. We're providing insights which can help our company become more secure.

 erik_miller_workflow.png

How much time has your organization saved by using Alteryx workflows?

What has this time savings allowed you to do?

With just our first workflow, we saved over 100 hours per month - so over a full FTE of time has been taken off of my plate. Alter
yx has allowed us to now only save time each month, but keep our team incredibly lean (we only have three people, and that's all we need to churn through massive amounts of security & fraud data each month).

 

So what has this time saving allowed us to do? Many, many things.

 

First, I was promoted to Sr. Systems Engineer - Cyber Security Analytics. With that change in title, also came the opportunity to build out a strategic-focused Information Security Analytics team, focused on looking at all security data throughout the company and identifying areas where we can improve our security program and posture.

 

Second, It's allowed me time to work with other departments to build out their analytics programs and help them learn to use the Alteryx tools in their respective areas.

 

Third, it's allowed my team to work on new, expanding projects with great ease.

FifthJusicial_logo.jpgAuthor: John Matyasovsky Jr (@JohnMaty) - Database Manager and Systems Analyst

Team Members: Mike Svidron, Cynthia Zwergel

Company: Fifth Judicial District of PA

Business Partner: eMoksha LLC – Vivek Mehta

 

Awards Category: From Zero to Hero

 

We are government. To put precisely, we are local government. Budgets are always tight and we are always looking for something we can use that will be effective, flexible and an excellent ROI. We are a data hub to all other departments and organizations within government. We share data with everyone. This data is critical in providing services to the people of Allegheny County.

 

We work closely with Vivek Mehta from eMoksha LLC, who first introduced us to Tableau. It was an eye-opening mess. The tool exposed our urgent and clear need to standardize and clean data. To complement Tableau, Vivek then presented us with Alteryx. The effect was immediate. We downloaded the free-trial and BEFORE THE FREE TRIAL EXPIRED, we were able to create and deploy an XML parsing process that would have traditionally taken months to complete. Not only was it completed in record time, but it was completed by someone who is not a traditional data guru. Normally we would need to allocate developer resources to this project, but due to Alteryx’s friendly interface, I was able to complete the project.

 

It was estimated that the free-trial alone paid for the license investment. That was two years ago. We have not looked back since!

 

Describe the problem you needed to solve

We are responsible for sharing data with multiple departments and agencies within the county. These data need to be near real time and incredibly accurate. Judges, police officers, Children, Youth and Family workers, and probation offers all use these data in the field and on the bench. We are dealing with real lives and freedoms. Being government however, we are constrained by certain rules and requirements. In certain divisions, we are required to use mandated systems to which we have limited back-end data access. Some of the most critical data we need comes from a mandated system and is available in xml format. We needed to consume, parse, and load these data elements into multiple different systems. Alteryx allows us to do just that.

 

Describe the working solution

We created several alteryx workflows and scheduled them to run continuously. Currently we have 14 workflows performing a scheduled ETL on a myriad of data. Additionally we have 117 workflows that are used ad-hoc. (Keep in mind we have been using Alteryx for only 20 months. That’s over 2 developments a week, all by only 2 people!) These data are consumed by the target systems, mostly in SQL server, and made available to the end users through our own proprietary systems and through Tableau Server. The response has been incredible. We have presented our progress at National and State events, specifically the National Association of Court Managers (NACM), and the Pennsylvania Association of Court Management (PACM). No other court in the state is doing what we are doing!

 

Describe the benefits you have achieved

As stated above, we are now able to allocate resources differently within our department.  Duties that used to be reserved only for developers can now be handled by differently specialized employees, thus freeing up critical resources.  Alteryx alone through reallocation of resources has created 1x FTE.  Data sharing development times have been halved.  We are now creating interactive dashboards that are near-real time and are a boon to the organization.  Data sharing has increased tremendously between organizations.   I have included samples of what we do on a daily, weekly, and monthly basis within our organization.  As you can see, we are invested in Alteryx!

 

 

Figure 1 - Case Creation Process for Adult ProbationFigure 1 - Case Creation Process for Adult Probation

 

 Figure 2 - Partial - Case Creation from xml for Adult ProbationFigure 2 - Partial - Case Creation from xml for Adult Probation

 

 Figure 3 - Warrant NotificationsFigure 3 - Warrant Notifications

 

Figure 4 - Case Lifecycle for Statistical ReportingFigure 4 - Case Lifecycle for Statistical Reporting

 

Figure 5 - Reporting for PA Department of Probation and ParoleFigure 5 - Reporting for PA Department of Probation and Parole

 

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.