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

Author: Brett Herman ( @brett_hermann ) , Project Manager, Data Visualization In-2CRev-28px-R.png

Company: Cineplex

 

Cineplex Inc. (“Cineplex”) is one of Canada’s leading entertainment companies and operates one of the most modern and fully digitized motion picture theatre circuits in the world. A top-tier Canadian brand, Cineplex operates numerous businesses including theatrical exhibition, food service, amusement gaming, alternative programming (Cineplex Events), Cineplex Media, Cineplex Digital Media, and the online sale of home entertainment content through CineplexStore.com and on apps embedded in various electronic devices. Cineplex is also a joint venture partner in SCENE – Canada’s largest entertainment loyalty program. 

 

Awards Category: Most Time Saved

 

Describe the problem you needed to solve 

Incremental/Uplift Modelling is a popular method of evaluating the success of business initiatives at Cineplex. Its effectiveness at measuring the change in consumer behavior over time creates a high demand to produce this kind of analysis for various departments in the organization. Due to the large amount of requests we receive, the ‘Incremental Lift Model’ was developed to take in user-defined inputs, and output the results within a short period of time.

 

Describe the working solution

Our solution works through a four step process. The first step is for the client to populate the ‘study input form’ in order to define their study parameters and the type of study they want to run.

 

Visual 1: Study Input Form

Alteryx Analytics Excellence Awards 2016 2H - bhermann - Visual 1.jpg

 

The second step is to update/materialize our loyalty data that’s inputted into the model (yxdb format). We do this so that the model doesn’t put stress on our SQL Server databases, and to increase the model’s performance.

 

Visual 2: Update/Materialize Alteryx Input Data

Alteryx Analytics Excellence Awards 2016 2H - bhermann - Visual 2.jpg

 

The third step is the core of the incremental lift modelling. A macro processes one study at a time by pointing to the user defined inputs made in the first step.

 

Visual 3: Study Numbers are selected and passed through the incremental lift macro, and saves the output to SQL.

Alteryx Analytics Excellence Awards 2016 2H - bhermann - Visual 3.jpg

 

The data will then be passed through one of several macros depending on the study type, and filtered down based on the inputs defined by the user in the study input form. All data sources are joined together and lift calculations are made, which are then outputted into a master SQL Table ready to be visualized.

 

Visual 4: Incremental Lift Modelling based on study type selected.

Alteryx Analytics Excellence Awards 2016 2H - bhermann - Visual 4.jpg

 

The results are visualized using a Tableau Dashboard in order to share and communicate the results of the study back to the business clients.

 

Visual 5: Tableau Dashboard to explain to the business how the incremental lift model makes its calculations.

Alteryx Analytics Excellence Awards 2016 2H - bhermann - Visual 5.jpg

 

Alteryx Analytics Excellence Awards 2016 2H -bhermann - Visual 6.jpg

 

 

Describe the benefits you have achieved

The overarching goal of this project was twofold; to minimize the amount of work required to process business requests while maximizing the output generated, and to develop a means of delivering the results in a consistent manner. Both of these goals contribute greatly to our ROI by virtually eliminating all time spent executing requests that come in, and by minimizing time spent meeting with business users to explain how the incremental lift model works and how to interpret the results.

 

Suzanne.pngAuthor: Suzanne McCartin (@SMCCARTI) , Sr. Ops Reporting Analyst

Company: Nike, Inc.

 

Awards Category: Name Your Own - Get Back In Time

 

Describe the problem you needed to solve 

My two my personal favorite Nike values are 'Simplify and Go' and 'Evolve Immediately'!    In Nike Apparel Global Product Creation Operations.  Our immediate need was to replace a critical and core data set on a tight timeline.  Making sure our product creation centers didn't lose buy tracking visibility.     Buy readiness is the measure and metric for garment commercialization.  Do we have everything we need to purchase?   This was just the beginning...

 

Describe the working solution

The buy ready metric process was implemented using a combination of tools and the first step was to replace the one data source, adding Alteryx to the tool mix.  The build process was then reconstructed and migrated to Altetyx using blending and in-database tools.  Going from about a 5 hour process to 1 hour.

 

The next follow up solution was to upgrade the report generation processes.  The first solution was one process for each output, and each one had its own data collection process.  Each of these solutions was moved to one workflow using the same data collection process.   Allowing me to enforce Nike's single version of the truth mantra!  This solution  has all kinds of data cleaning , mapping, and shaping.

 

Describe the benefits you have achieved

The first round benefit was getting the upgrade done and we did so with improved accuracy and data visibility.    The real benefit was to allow the process to get us back to the future and we are lined up to better collaborate with IT and move to Tableau and other new platforms!

PATH logo.png

Author: Jeff Bernson, Senior Director, Results, Management, Measurement and Learning 

Company: PATH 

Team Members: Mike Hainsworth, Allan Walker, Doug Morris, Anya A’Hearn, Philip Riggs, Chris DeMartini, Jonathan Drummey, Jeff Bernson, Daniel Bridges, Joe Mako, Ken Black

 

Awards Category: Best 'Alteryx for Good' Story

 

PATH is the leader in global health innovation. We are driven by an unshakeable commitment to health equity and a bold belief in the power of innovation to improve health, save lives, and transform the future, especially for women and children. Working with partners across the public and private sectors, our trusted experts bring together the right tools and people at the right time and place to accelerate innovative solutions to the health challenges facing vulnerable women, children, and communities worldwide. By tenaciously supporting solutions all the way from early concept to large-scale use, PATH’s work translates smart ideas into tangible change for millions of people.

 

Describe the problem you needed to solve 

In Africa, malaria is the leading cause of death in young children. For the past decade, the Zambian National Ministry of Health and the National Malaria Elimination Centre (NMEC) with support from PATH have used data to fight malaria—saving thousands of lives. In 2015, PATH teamed up with Alteryx and six other technology partners to launch Visualize No Malaria—a campaign focused on integrating new tools and systems for data use to support Zambia’s Ministry of Health in their effort to eliminate malaria by 2021.

 

The PATH-supported malaria laboratory in Lusaka, Zambia, located at the NMEC is implementing a range of molecular tools in the quest to achieve elimination. Every month, thousands of blood samples from around the country are processed to answer key questions around issues such as diagnostic efficacy, drug resistance, and transmission intensity.

Prior to our partnership with Alteryx, the lab was ill equipped to deal with these data. At the time, our laboratory information system was costly, cumbersome and necessitated building a custom database for each study. We needed a flexible, agile tool that could grow with our needs, namely, a system that would provide a simple mechanism for reporting data, aggregating it, and producing summary outputs.

 

Describe the working solution

Alteryx software has transformed how we access data. As we experimented with Alteryx, it quickly became apparent that we could almost instantly curate, process, and archive large amounts of data to inform our field activities and to better understand what’s really happening with malaria transmission patterns and dynamics in near real-time.

 

Our partnership will empower frontline health workers with the critical tools to prevent, track, and treat malaria cases to help eliminate this deadly disease.  We are improving data accuracy and making real-time critical data-informed decisions about how and where to tackle outbreaks. We are also building the skills of district and facility health teams to combat the disease at community level. If successful, this model could serve to inform global efforts to end malaria for good. 

 

PATH and its data science partners are using Alteryx in a number of ways to help lead the charge toward elimination including:

 

1. Automating workflows that help us process lab results and notify health workers of positive malaria cases faster in targeted study sites. Prior to the introduction of Alteryx, we essentially were unable to provide feedback on results in any meaningful way. Now instead of waiting months, we can process results on a daily basis with automated outputs sent to the end-user for dissemination and to inform decisions.

 

PATH Workflow 1.png

 

2. Responding to survey data during field data collection. Survey data is usually evaluated at the end of the process, which is fine if everything has gone well, but potentially catastrophic if errors were introduced. Through Alteryx, we have been able to access survey data as it is collected through a cloud API and then validate and summarize data to ensure that we are on track. The speed of this process meant that we were able to not only identify and resolve issues during the survey, but could rapidly and iteratively interrogate the data to ensure high data quality.

 

PATH Workflow 2.png


3.
 Developing a workflow to enable automated prediction and forecasting of malaria cases in the Southern Province of Zambia that can be batched on a weekly schedule.

 

PATH Workflow 3.png

 

 

4. Leveraging Alteryx for geospatial data processing and analysis to create modeled boundaries such as facility catchment areas, entity relationship, and resource optimization diagrams, such as health worker to Facility network diagrams, and input layers for other geographic processing and rendering tools, such as Mapbox.

 

PATH Mapbox.png

 

5. Pre-processing and merging of geospatial and epidemiological data for ease of use in front end rendering and analytic tools, such as Tableau.

 

PATH Tableau Viz.png

 

 

Describe the benefits you have achieved

The net result from this work has been a more efficient workflow process, more insightful analyses, and the development of a more holistic approach to understanding the complete malaria picture in Southern Province. Alteryx put the power in the hands of the user so we can build exactly what we need when we need it.

 

Since deployment of these tools, along with a number of strategies to further accelerate the gains against malaria, since 2014 there has been a 90 percent decrease in malaria cases in the Southern Province. As transmission rates decrease and malaria cases become increasingly localized, combining the existing interventions with capabilities of data analytics tools like Alteryx will play an essential role in combating the disease at the community level and bringing the number of cases to zero.

PATH Benefits 1.png

Author: Michael Barone, Data Scientist

Company: Paychex Inc.

 

Awards Category: Most Time Saved

 

We currently have more than two dozen predictive models, pulling data of all shapes and sizes from many different sources.  Total processing time for a round of scoring takes 4 hours.  Before Alteryx, we had a dozen models, and processing took around 96 hours.  That's a 2x increase in our model portfolio, but a 24x decrease in processing time.

 

Describe the problem you needed to solve 

Our Predictive Modeling group, which began in the early-to-mid 2000s, had grown from one person to four people by summer 2012.  I was one of those four.  Our Portfolio had grown from one model, to more than a dozen.  We were what you might call a self-starting group.  While we had the blessing of upper Management, we were small and independent, doing all research, development, and analysis ourselves.  We started with using the typical every day Enterprise solutions for software.  While those solutions worked well for a few years, by the time we were up to a dozen models, we had outgrown those solutions.  A typical round of "model scoring" which we did at the beginning of ever y month, took about two-and-a-half weeks, and ninety-five percent of that was system processing time which consisted of cleansing, blending, and transforming the data from varying sources.

 

Describe the working solution

We blend data from our internal databases - everything from Excel and Access, to Oracle, SQL Server, and Netezza.  Several models include data from 3rd party sources such as D&B, and the Experian CAPE file we get with out Alteryx data package.

 

Describe the benefits you have achieved

We recently have taken on projects that require us processing and analyzing billions of records of data.  Thanks to Alteryx and more specifically the Calgary format, most of our time is spent analyzing the data, not pulling, blending, and processing.  This leads to faster delivery time of results, and faster business insight.

Author: Wayne Franklin, Student Experience Evaluation Officer

Company: Charles Darwin University

 

Awards Category: Most Time Saved

 

The time saved mainly effects my workload; this in turn allows me to work on other projects for the department which helps the overall organisation. Being a smaller organisation our resources are limited so any time saved makes a significant difference to our overall output. Using Alteryx has quite often saved days of manual work and it significantly reduces the risk of errors.

 

Describe the problem you needed to solve 

The issue we faced was how best to amalgamate multiple data tables into three new unique excel files to be used by a 3rd party survey tool - Blue eXplorance. While this doesn't sound too difficult, it becomes very time consuming when there are thousands of rows of data in each data source. Being a smaller educational institutional I am at present the only person that works with all the setting up, running and reporting of all the surveys within the university; spending a day or two stuck on setting up one survey can have a detrimental effect on other projects.

 

Describe the working solution

The old way of doing things: Download each of the data sources which included the full student and unit information for a given semester. This was followed by a series of pivot tables, copy pasting, creating new fields, making things more meaningful (i.e. change 'M' to Male - not much but supervisors like it better that way). Once all that was done I would eventually end up with clean unit, student and relationship files that are set up to be used for 3rd party survey software. This doesn't sound like much but is quite time consuming. I got pretty good at excel formulas which helped cut the time down a little, but still took a day messing around in excel to get the final product. The new way to do things: click run on the Alteryx app I made, wait a minute, done! The app I created allows me to select the files to upload and where to save the output files at the end.

 

Describe the benefits you have achieved

What started as a solid day or two work is now reduced to a minute wait time as the Alteryx app is running. This frees me up to continue work on other projects I am working on and be a more productive member of our team.

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

Company: Bristlecone Holdings

 

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

 

Describe the problem you needed to solve 

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

 

Describe the working solution

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

 

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

 

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

 

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

 

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

 

Describe the benefits you have achieved

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

 

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

Author: Dan Jeavons, GM Advanced Analytics Centre of Excellence

Team Members: 

Roel Esselink, Aaron Levis, Dennis Vallinga, Chris Bridge, Phil Ochs, Feliciano Gonzalez, Bryce Bartmann, Ciaran Doherty, Heather Stevens, Evgenia Domnenkova, Amjad Chaudry, Zoe Moore, Pawel Dobrowolski, Melissa Patel, Deval Pandya; Hariharasudhan Ramani, Wayne Jones

Company: Shell

Awards Category: Icon of Analytics

 

Shell is a global group of energy and petrochemical companies.

 

Our operations are divided into: Upstream, Integrated Gas and New Energies, Downstream, and Projects & Technology.

 

In Upstream we focus on exploration for new liquids and natural gas reserves.

 

In Integrated Gas and New Energies, we focus on liquefying natural gas (LNG) and converting gas to liquids (GTL) The New Energies business has been established to explore and invest in new low-carbon opportunities.

 

In Downstream, we focus on turning crude oil into a range of refined products. In addition, we produce and sell petrochemicals for industrial use worldwide.

 

Shell purchased the Alteryx platform 2 years ago. The tool has been made available as part of their Shell’s Advanced Analytics Lab, (essentially their data science workbench). Alteryx demand has grown significantly – quickly becoming the most popular tool in the toolbox being used by 88% of lab users across multiple Shell business units. The variety of applications is staggering – here are just a few examples:

 

1. Shell Downstream Lubricants Supply Chain uses Alteryx to assist with the assurance of the integrated business value provided through the supply chain. They have used Alteryx to develop a Supply Chain Excellence award winning suite of tools which provide critical information on inventory, margin, forecast accuracy and blending options. These tools have taken previously manual processes built in Excel & R into fully automated end to end workflows making quality data available far faster than was previously possible.

 

2. Shell Downstream Trading Compliance team using Alteryx enabled capability for monitoring operations across multiple markets, achieving compliance with current regulations and creating transferability as markets and regulations develop.

 

3. Shell Exploration had a highly manual process for analysing information coming back from exploration drilling campaigns.  Using Alteryx, they have constructed a “New Well Portal” where various subject matter experts can come to understand future extraction opportunities.

 

4. Shell Contracting & Procurement uses predictive analysis in Alteryx as part of a solution to optimise the ordering, storage and utilisation of pieces of spare part inventory ranging from well heads to pipeline parts. The project paid for itself in 4 weeks and continues to deliver millions of dollars to the bottom line.

 

5. Shell Downstream Retail is just starting on their Alteryx journey, using Alteryx to digest multiple data info sources and “transform” manual monthly process into accurate, error-free and automated reporting around of the performance of their marketing campaigns

 

In addition, Shell has been working closely with Alteryx as a co-innovation partner, driving the development of products like Alteryx Connect, and developing a platform eco-system with other innovative partners like DVW, Maana and Databricks to accelerate the development of their advanced analytics platform as part of their digitalisation journey.

Author: Manju Devadas (@manju_devadas) - CEO

Team Members: Vijay Bondale, Salil Amonkar

Business Partner: Pluto7

Client: Cisco

 

Awards Category: Best Value Driven with Alteryx

 

  • Cisco Virtual Sales organization is responsible for nearly $5 Billion plus in revenue. One of the key challenges they faced was figuring out which bookings to associate with opportunities. There was a lack of mapping data, which would have helped achieve this visibility directly.
  • With Alteryx we did data science work and built fuzzy logic to match data based on geography, customer hierarchy and other factors.
  • The visibility provided was humanly difficult to achieve otherwise.

 

Describe the problem you needed to solve

Bookings to opportunity matching was missing hence the sales leadership had a tough time attributing the sales efforts to the results. 

 

Describe the working solution

SAP Hana, Excel. Department has Alteryx Server. Workflows with data science logic for bookings to sales pipeline matching for business insights. We exported the data to Tableau. The goal here is to drive higher sales effectiveness.

 

Describe the benefits you have achieved

a. Sales leadership can now drive higher productivity and results from their sales work force.
b. Potentially increase the revenue for Cisco Virtual Sales.

Author: Kunpeng Zhang - Sr Quality Control Analyst & Lam Truong

Company: Southwest Airlines Co.

 

Awards Category: Best Value Driven with Alteryx

 

This project aimed to figure out our station level workload and data sampling plan. Our team, Quality Control, conducts monthly audits at a fixed number per month, which varies by station type (heavy station, intermediate station, and line station). Each station handles different types and volumes of work. Assigning the right amount of audit work that aligns with work load capacity provided a better way to collect the work samples we needed to evaluate each stations performance.

 

To make the audit quantity decision, the analysis involves combining work load, work schedule, staffing, and performance score data. The data required to complete this project exists in totally different environments, which makes it difficult to combine and mix. This is where Alteryx jumped in and helped tremendously to solve the problem. Previously, our analysis was arbitrarily set to review 4, 8, and 12 year histories. However, as time went by, the workload began to vary greatly for each station every month. The workflow we created enabled us to increase data collection without interfering with each stations work capacity.

 

Prior to Alteryx, this process had been handled by Excel, Access, Oracle PL developer and Python, which altogether made it difficult to implement and manage. After adopting Alteryx, we are able to focus on the algorithm and data analysis, as opposed to manually building connections between disparate data sources.

 

Describe the problem you needed to solve 

Scattered data sources, slow calculation speed, multiple tools and platforms.

  

Describe the working solution

Alteryx performs three basic jobs for this project:

  • Data connector
  • Model builder
  • Task runner

Alteryx consumes data from a cloud platform, an enterprise data warehouse, and department reports, processes and mixes them via model, then exports the results back to the cloud platform (From: Text files, Quickbase, Teradata, SqlServer, and Excel, TO: QuickBase)   

 

Describe the benefits you have achieved

I can quickly test an idea or a model using Alteryx, which saves me around 70% of the time I would have spent coding calculations on massive amounts of data. It also enables and tolerates different data sources from other departments, which are typically out of my control.  This allows me to focus on the analyses rather than spending time connecting to the data.

SummarySummary

 

 Wizard Work Load By Task CardsWizard Work Load By Task Cards

 

Work Load From SBX by HoursWork Load From SBX by Hours

 

Staffing From QuickBaseStaffing From QuickBase

 

Compliance Score from ExcelCompliance Score from Excel

 

Write Back To QuickBaseWrite Back To QuickBase

Author: Mandy Luo, Chief Actuary and Head of Data Analytics

Company: ReMark International

 

Awards Category: Best Use of Predictive

As a trained Statistician, I understand why "70% data, 30% model" is not an exaggeration. Therefore, before applying any regression models, I always make sure that input data are fully reviewed and understood. I use various data preparation tools to explore, filter, select, sample or join up data sources. I also utilize the data investigation tools to conduct or validate any statistical evaluation. Next, I would usually choose 3-5 predictive modeling candidates depending on the modeling objective and data size. I often include one machine learning methods in order to at least benchmark other models. After the modeling candidates finish running, I would select the best model based on both art (whether the coefficients look reasonable based on my understanding of the data and business) and science (statistical criteria's like the goodness of fit, P-value and cumulative lift etc.).  I am also often using the render function for model presentation and scoring/sorting  function for model validation and application.

 

Describe the problem you needed to solve 

ReMark is not only an early adopter in predictive modeling for life insurance, but also a true action taker on customer centricity by focusing on customer lifetime analytics (instead of focusing on 'buying' only). In this context, we need to 'join up' our predictive models on customer response, conversion and lapse in order to understand the most powerful predictors that drive customer activities across pre and post sales cycle. We believe the industry understand that it is insufficient to only focus on any single customer activity, but is still exploring how this can be improved through modeling and analytics, which is where we can add value.

 

Describe the working solution

Our working solution goes with the following steps:

  1. Match over one year post sales tracking data back to sales payment data and marketing data (all de-personalized)
  2. Build 3 predictive models: sale(whether the purchase is agreed or not), conversion (whether the first premium bill is paid or not), 1 year persistency (whether lapse happened at month 13 or not).
  3. Compare model results by key customer segments and profiles
  4. Expert to visualization tool (e.g. Tableau) to present results
  5. Model use test: scoring overlay and optimization strategy

 

Describe the benefits you have achieved

  • We could create customer segments' not just based on tendency to 'buy', but also tendency to 'pay' and 'stay'.
  • We could further demonstrate ReMark's analytics and modeling capabilities covering the whole customer lifetime value chain without missing out

Author: Sintyadi Thong ( @MizunashiSinayu ), Pre-Sales Consultant, Karen Kamira & Harry Yusuf

Company: Lightstream Analytics PTE Ltd.

 

LightStream Analytics focuses on performance management, big data, advanced analytics, and innovative visualization through cloud SaaS applications, mobile, and traditional on-site systems. LightStream Analytics is well-positioned to deliver the most advanced products and services by capitalizing on its significant regional presence in Singapore and Indonesia. The combined offices have over 60 employees with deep technical and senior business experience. The company leverages our existing technical support and R&D centers in Indonesia and China to develop solutions which disrupt customary methods of data analysis and give clients access to revolutionary tools for understanding their data.  LightStream Analytics has partnered with more than 100 multinational and local clients to integrate, structure, analyze, and visualize information to measure their business performance and drive enterprise value growth.

 

Awards Category: Most Time Saved

  

Describe the problem you needed to solve 

 

One of our clients tried to implement one of the most Business Intelligence solution to help them grow their business through another company (we can pretty much say our competitor). However, there is one thing which hinders their development of the BI. When usually most companies want to see the date of sales (on which date their agents perform sales), this company would like to see the other way around, they would like to see ‘on which dates their agents do not perform sales activity’. For them this is very important. The BI developers hit a dead-end on this thing, and therefore I came with Alteryx.

 

Describe the working solution

 The BI I previously mentioned is QlikView. Well Qlik can do it, and I can guarantee. But it involves heavy scripting and logic. With heavy scripting it will also mean it requires heavy resources to perform the run (will only be visible when running with low RAM). Alteryx on the other hand can do this easily using drag-and-drop and repeatable workflow, so I feed Alteryx with the actual sales data, perform several left joins, filter, and unique. Alteryx requires no scripting, and to be honest I am not even an IT guy, I know nothing about SQL and programming, but I can create this workflow easily. So we proposed to have Alteryx prepare and blend the data before feeding it to QlikView, therefore it will help to make the data visible before feeding it to QlikView and lessens the burden on QlikView. While the client has not yet confirmed whether they will get Alteryx or not, it is really satisfying and rewarding to solve this problem easily while others had hardships in getting this result.

 

Describe the benefits you have achieved

While I created the workflow in only an hour vs their 2 weeks development for this one case (and in which they failed after 2 weeks), this shows how much of a time savings the client would get if they developed QlikView alongside with Alteryx. Alteryx will help the customers to get results faster and perform an advanced ETL which might be hard to do in traditional SQL language.

Author: Andrew Simnick, Head of Strategy

Company: Art Institute of Chicago

 

Awards Category: Best 'Alteryx for Good' Story

 

The Art Institute of Chicago is one of the world's greatest art museums, staying true to our founding mission to collect, preserve, and interpret works of art of the highest quality from across the globe for the inspiration and education of our audiences. Today we face new competition for visitor attention, a continued responsibility to expand our audiences, and an increasingly-challenging economic environment.  Alteryx has allowed us to quickly overcome our data and resource constraints, develop a deeper understanding of our local audiences, and strike a balance between mission- and revenue-driven activities to continue to deliver on our mission for Chicago.

 

Describe the problem you needed to solve 

We, as do other museums, face the challenges of growing our audience while  maintaining a strong financial foundation. Our strategy to navigate this has been to increase visit frequency from our core visitor segments in the near term and use this increase to further expand outreach to new local audiences. However, our challenges to achieving this have been three-fold. First, visitor segmentation in the arts and culture space is a relatively recent concept, and general segmentation schema are not always applicable to Chicago at a granular level. Second, we have very useful data but in inconsistent formats, ranging from handwritten notes and Excel documents to normalized but disconnected databases. Third, we are resource-constrained as an institution and cannot dedicate large amounts of time or money towards dedicated analytics or external consulting.

 

Describe the working solution

First, we built a database describing the Chicago CBSA at the census block group level, providing the nuance necessary for a city where demographics change block-to-block and limit the utility of ZIP code analysis. Alteryx allowed us to get to this new additional level of detail and make our analysis relevant to Chicago. Using the Allocate Input and Calgary Join, we applied information from the US Census as well as Experian data sets. We utilized basic data such as population, income, and education, as well as proprietary Experian segments such as Mosaic groups and ISPSA (Index of Social Position in Small Areas) to describe these census blocks.

 

Second, we brought together our disparate visitor data into a blendable format. Some of our datasets are well defined, such as our membership CRM which resides in a relational database on MSSQL Server, whereas others are more ad hoc, such as our Family Pass users, which are transcribed from pen and paper into an Excel document. The Join tools in Alteryx provided a simple way to bring these data together without commanding significant time from our small analytics team.

 

Third, each of these datasets was CASS Encoded and geocoded using the US Geocoder tool, providing us a spatial object. We then utilized the Spatial Match tool to find the intersection of these objects with our universe of Chicagoland Block Groups. Each of these distinct streams were then normalized and combined to the block group aggregation level resulting in our final dataset. We also utilized a shared public custom macro which allowed us to convert these block groups into polygons for visualization in Tableau.

 

Finally, we utilized heatmaps and scatterplots to identify which proprietary Experian segments correlate with our different offerings. This informed our choice of variables for our final Decision Tree tool analysis, which identified prime target block groups associated with our different offerings. These bespoke segments created via machine learning were more applicable to our own audiences and required a fraction of the time and cost of other segmentation methods.

 

Describe the benefits you have achieved

This approach has given us a framework and the supporting intelligence from which to make institutional decisions surrounding visitor outreach and programming, allowing us to focus our resources on actions which we believe will have the greatest impact towards increased participation, attendance, and/or revenue. For example, we can now tailor membership messaging more effectively and quantify the effects on repeat visitation. We also can identify gaps in our geographic coverage of Chicagoland and test different outreach efforts to engage new audiences. Most importantly, we can unify our approach to audience development across departments using a common baseline and methodology. These combined efforts enabled by Alteryx will help us to build our audiences and fulfill our civic responsibilities well into the future.

Author: Francisco Aristiguieta, Audit Specialist

 

Awards Category: Name Your Own - Best Engagement From Management

 

Describe the problem you needed to solve 

With operations in all time-zones and more than 10,000 people, my company needed an effective way to ensure we don't have rouge employees exposing us to corruption.

 

Before our Alteryx tool, we had a very complete compliance program focused on prevention; but we did not had a viable method to verify the mandates were understood and followed across the globe.

 

Describe the working solution

Our plan was to every month inspect every payment the company had done for signs of potential problems. We would do this by searching each invoice line for keywords that could represent problems.

 

The plan was simple, although the implementation would have been an enormous problem if we had not had Alteryx.  Here are a few of the (multiple) humps Alteryx helped us address:

 

1. Payment information was broken in multiple tables. Even if we would be working with Oracle data, our IT department insisted that we worked with off-line copies of the tables instead of connecting directly. This made our data source a series of multiple monthly csv tables, where the tables had no meaning on their own.

 

>> Importing all files in a folder, and using "Unique", "Filter", "Select" and "Join"; allowed me to conquest this first challenge.

 

2. I used "find replace" to do the keyword searches; which was a great step forward. Sadly, in many cases our chosen "keywords" were part of innocent words, which caused a plague of false positives for follow-up. i.e. the word "magenta" would be caught when we searched for "agent". 

 

>> Using "Formula" to set-up some "If-Then-Else statements", and carefully using "and" to set-up my conditions, I was able to safe list some of these innocent words and get rid of a large portion of these false positives.

 

3. Because the outputs of each run is stored separately, my last big challenge was making sure I didn't report/investigate the same transactions month after month as we re-ran the tests.

 

>> Solving this was easy through a collection of file imports, "union", and "join" to compare the current results to the recent past (keeping only new hits) in my analysis.

 

Francisco_FCPATEsts.jpeg 

Describe the benefits you have achieved

Even if (after follow-up) the tests have not found any real problems, we are very happy to finally have peace of mind regarding how our employees are behaving across the world. This test was a great way to demonstrate the value of analytics to the more traditional pockets of our company, and its results have been greatly celebrated, giving me and my team some great exposure to the highest levels of my organization. Here are a few quotes from our clients:

 

  • "This is another SUCCESS for the Data Analytics initiative.  There is NO WAY we would have ever even known this was an issue without this capability "
  • "I believe that this proactive approach is clearly one of the most significant advances in early detection techniques that (the team) has implemented in quite a while"
  • "The mere fact that the word will get out that we have tools like this to potentially catch such payments should be a powerful deterrent"
  • "Our analytics practices have changed the way we (work) increasing our effectiveness and efficiency"
  • "I am looking forward  to work on another (analytics) initiative with (the team)"