cancel
Showing results for 
Search instead for 
Did you mean: 

At Alteryx, we’re constantly impressed with the amazing things that you do with our software. The Alteryx Analytics Excellence Awards recognize and celebrate your best Alteryx success stories.

We are now accepting submissions for the 2017 Alteryx Analytics Excellence Awards! Here’s how it works:

  • Download the form and submit your stories by April 7th to qualify.
  • Between April 17th and 28th we will encourage everyone to vote for their favorite stories right here on the Alteryx Community.
  • Winners will be announced at Inspire 2017 in Las Vegas, June 7th.

For full rules and details, click here.


Suggest an idea

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.

 

 

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: Jack Morgan (@jack_morgan), Project Management & Business Intelligence

 

Awards Category: Most Time Saved

 

After adding up the time savings for our largest projects we came up with an annual savings of 7,736 hours - yea, per year! In that time, you could run 1,700 marathons, fill 309,000 gas tanks or watch 3,868 movies!! Whaaaaaaaaaaaaat! In said time savings, we have not done any of the previously listed events. Instead, we've leveraged this time to take advantage of our otherwise unrealized potential for more diverse projects and support of departments in need of more efficiency. Other users that were previously responsible for running these processes now work on optimizing other items that are long overdue and adding value in other places by acting as project managers for other requests.

 

Describe the problem you needed to solve 

The old saying goes, Time is of the essence, and there are no exceptions here! More holistically, we brought Alteryx into our group to better navigate disparate data and build one-time workflows to create processes that are sustainable and provide a heightened level of accuracy. In a constraint driven environment my team is continuously looking for how to do things better. Whether that is faster, more accurately or with less needed oversight is up to our team. The bottom line is that Alteryx provides speed, accuracy, and agility that we never thought would be possible. Cost and the most expensive resource of all, human, has been a massive driver for us through our Alteryx journey and I'd expect that these drivers will continue as time passes us by.

 

Describe the working solution

Our processes vary from workflow to workflow, however overall we use a lot of SQL, Oracle, Teradata and SharePoint. In some workflows we blend 2 sources; in others we blend all of them. It depends on the need of the business that we are working with on any given day. Once the blending is done we do a variety of things with it, sometimes is goes to apps for self-service consumption and other times we push it into a data warehouse. However one thing that is consistent in our process is final data visualization in Tableau! Today, upwards of 95% of our workflows end up in Tableau allowing us to empower our users with self-service and analytics reporting. When using databases like SQL and Oracle we see MASSIVE gains in the use of In-Database tools. The ability for our Alteryx users to leverage such a strong no code solution creates an advantage for us in the customer service and analytics space because they already understand the data but now they have a means to get to it.

 

Audit Automation:

 

Billing:

 

 

File Generator:

 

Market Generator:

 

 

Parse:

 

Describe the benefits you have achieved

The 7,736 hours mentioned above is cumulative of 7 different processes that we rely on, on a regular basis.

 

  1. One prior process took about 9 days/month to run - we've dropped that to 30s/month!
  2. Another process required 4 days/quarter that our team was able to cut to 3 min/quarter.
  3. The third and largest workflow would have taken at estimate 5200 hours to complete and our team took 10.4 hours to do the same work!
  4. The next project was a massive one, we needed to create a tool to parse XML data into a standardized excel format. This process once took 40 hrs/month (non-standard pdf to excel) that we can run in less than 5s/month!
  5. Less impressive but still a great deal of time was when our systems and qa team contracted us to rebuild their daily reporting for Production Support Metrics. This process took them about 10 hours/month that we got to less than 15 sec/day.
  6. One of our internal QA teams asked us to assist them in speeding up their pre-work time for their weekly audit process. We automated their process that took them upwards of 65 hours/month to a process that now takes us 10 sec/week!
  7. The last of the 7 processes that have been mentioned in that our above write-up would be a process for survey data that took a team 2 hours/week to process. That same process takes our team about 20 sec/week to process.

 

We hope you've found our write-up compelling and win-worthy!

 

Author: Omid Madadi, Developer

Company: Southwest Airlines Co.

 

Awards Category: Best Business ROI

 

Describe the problem you needed to solve 

Fuel consumption expense is a major challenge for the airline industry. According to the International Air Transport Association, fuel represented 27% of the total operating costs for major airlines in 2015. For this reason, most airlines attempt to improve their operational efficiency in order to stay competitive and increase revenue. One way to improve operational efficiency is to increase the accuracy of fuel consumption forecasting.

 

Currently, Southwest Airlines offers services in 97 destinations with an average of 3,500 flights a day. Not having enough fuel at an airport is extremely costly and may result in disrupting flights. Conversely, ordering more fuel than what an airport needs results in high inventory and storage costs. As such, the objective of this project was to develop proper forecasting models and methods for each of these 97 airports in order to increase the accuracy and speed of fuel consumption by using historical monthly consumption data.

 

Describe the working solution

Data utilized in this project were from historical Southwest Airlines monthly fuel consumption reports. Datasets were gathered from each of the 97 airports as well as various Southwest departments, such as finance and network planning. Forecasting was performed on four different categories: scheduled flights consumption, non-scheduled flights consumption, alternate fuel, and tankering fuel. Ultimately, the total consumption for each airport was obtained by aggregating these four categories. Since data were monthly, time series forecasting and statistical models - such as autoregressive integrated moving average (ARIMA), time series linear and non-linear regression, and exponential smoothing - were used to predict future consumptions based on previously observed consumptions. To select the best forecasting model, an algorithm was developed to compare various statistical model accuracies. This selects a statistical model that is best fit for each category and each airport. Ultimately, this model will be used every month by the Southwest Airlines Fuel Department.

 

 

In addition to developing a consumption forecast that increases fuel efficiency, a web application was also developed. This web application enables the Fuel Department to browse input data files, upload them, and then run the application in an easy, efficient, and effortless manner. Data visualization tools were also added to provide the Fuel Department with better insights of trends and seasonality. Development of the statistical models has been finalized and will be pushed to production for use by the Southwest Airlines Fuel Department soon.

 

 

 

Describe the benefits you have achieved

Initially, the forecasting process for all 97 Southwest Airlines airports used to be conducted through approximately 150 Excel spreadsheets. However, this was an extremely difficult, time-consuming, and disorganized process. Normally, consumption forecasts would take up to three days and would have to be performed manually. Furthermore, accuracy was unsatisfactory since Excel's capabilities are inadequate in terms of statistical and mathematical modeling.

 

For these reasons, a decision was made to use CRAN R and Alteryx for data processing and development of the forecasting models. Alteryx offers many benefits since it allows executing R language script by using R-Tool. Moreover, Alteryx makes data preparations, manipulations, processing, and analysis fast and efficient for large datasets. Multiple data sources and various data types have been used in the design workflow. Nonetheless, Alteryx made it convenient to select and filter input data, as well as join data from multiple tables and file types. In addition, the Fuel Department needed a web application that would allow multiple users to run the consumption forecast without the help of any developers, and Alteryx was a simple solution to the Fuel Department's needs since it developed an interface and published the design workflow to a web application (through the Southwest Airlines' gallery).

 

In general, the benefits of the consumption forecast include (but are not limited to) the following:

 

  • The forecasting accuracy improved approximately 70% for non-schedule flights and 12% for scheduled flight, which results in considerable fuel cost saving for the Southwest Airlines.
  • The current execution time reduced dramatically from 3 days to 10 minutes. Developers are working to reduce this time even more.
  • The consumption forecast provides a 12-month forecasting horizon for the Fuel Department. Due to the complexity of the process, this could not be conducted previously using Excel spreadsheets.
  • The Fuel Department is able to identify seasonality and estimate trends at each airport. This provides invaluable insights for decision-makers on the fuel consumption at each airport.
  • The consumption forecast identifies and flags outliers and problematic airports and enables decision-makers to be prepared against unexpected conditions.

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.

 

 

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.

 

 

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!

 

 

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.

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: Shelley Browning, Data Analyst

Company: Intermountain Healthcare

 

Awards Category: Most Time Saved

  

Describe the problem you needed to solve 

Intermountain Healthcare is a not-for-profit health system based in Salt Lake City, Utah, with 22 hospitals, a broad range of clinics and services, about 1,400 employed primary care and secondary care physicians at more than 185 clinics in the Intermountain Medical Group, and health insurance plans from SelectHealth. The entire system has over 30,000 employees. This project was proposed and completed by members of the Enterprise HR Employee Analytics team who provide analytic services to the various entities within the organization.

 

The initial goal was to create a data product utilizing data visualization software. The Workforce Optimization Dashboard and Scorecard is to be used throughout the organization by employees with direct reports. The dashboard provides a view of over 100 human resource metrics on activities related to attracting, engaging, and retaining employees at all levels of the organization. Some of the features in the dashboard include: drilldown to various levels of the organization, key performance indicators (KPI) to show change, options for various time periods, benchmark comparison with third party data, and links to additional resources such as detail reports. Prior to completion of this project, the data was available to limited users in at least 14 different reports and dashboards making it difficult and time consuming to get a complete view of workforce metrics.

 

During initial design and prototyping it was discovered that in order to meet the design requirements and maintain performance within the final visualization it would be necessary for all the data to be in a single data set. The data for human resources is stored in 17 different tables in an Oracle data warehouse. The benchmark data is provided by a third party. At the time of development the visualization software did not support UNION or UNION ALL in the custom SQL function. During development the iterative process of writing SQL, creating an extract file, and creating and modifying calculations in the visualization was very laborious. Much iteration was necessary to determine the correct format of data for the visualization.

 

Other challenges occurred, such as when it was discovered that the visualization software does not support dynamic field formatting. The data values are reported in formats of percent, currency, decimal and numeric all within the same data column. While the dashboard was in final review it was determined that a summary of the KPI indicators would be another useful visualization on the dashboard. The KPI indicators, red and green arrows, were using table calculations. It is not possible to create additional calculations based on the results of table calculations in the visualization software. The business users also requested another cross tabular view of the same data showing multiple time periods.

 

Describe the working solution

Alteryx was instrumental in the designing and development of the visualization for the workforce dashboard. Without Alteryx the time to complete this project would have easily doubled. By using Alteryx, a single analyst was able to iterate through design and development of both the data set and the dashboard.

 

 

The final dashboard includes both tabular and graphic visualizations all displayed from the same data set. The Alteryx workflow uses 19 individual Input Data tools to retrieve data from the 17 tables in Oracle and unions this data into the single data set. Excel spreadsheets are the source for joining the third party benchmark data to the existing data. The extract is output from Alteryx directly to a Tableau Server. By utilizing a single set of data, filtering and rendering in visualization are very performant on 11 million rows of data. (Development included testing data sets of over 100 million rows with acceptable but slower performance. The project was scaled back until such a time as Alteryx Server is available for use.)

 

 

 

 

 

Describe the benefits you have achieved

The initial reason for using Alteryx was the ability to perform a UNION ALL on the 19 input queries. By selecting the option to cache queries, output directly to tde files, and work iteratively to determine the best format for the data in order to meet design requirements and provide for the best performance for filtering and rendering in the visualization, months of development time was saved. The 19 data inputs contain over 7000 lines of SQL code combined. Storing this code in Alteryx provides for improved reproducibility and documentation. During the later stages of the project it was fairly straight forward to use the various tools in Alteryx to transform the data to support the additional request for a cross tab view and also to recreate the table calculations to mimic the calculations the visualization. Without Alteryx it would have taken a significant amount of time to recreate these calculations in SQL and re-write the initial input queries.

 

Our customers are now able to view their Workforce Optimization metrics in a single location. They can now visualize a scenario in which their premium pay has been increasing the last few pay periods and see that this may be attributed to higher turnover rates with longer times to fill for open positions, all within a single visualization. With just a few clicks our leaders can compare their workforce optimization metrics with other hospitals in our organization or against national benchmarks.  Reporting this combination of metrics had not been attempted prior to this time and would not have been possible at this cost without the use of Alteryx.

 

Costs saving are estimated at $25,000 to-date with additional savings expected in future development and enhancements.

Author: Scott Elliott (@scott_elliott) , Senior Consultant

Company: Webranz Ltd

 

Awards Category: Best Use of Alteryx Server

 

We are using the server to store Alteryx Apps that get called by the "service bus" and perform calculations and write the results into a warehouse where growers can log into a web portal and check the results of the sample.

 

Describe the problem you needed to solve 

Agfirst BOP is a agricultural testing laboratory business  that perform scientific measurement on Kiwifruit samples it receives from 2500 growers around New Zealand. In peak season it tests up to 1000 samples of 90 fruit per day. The sample test results trigger picking of the crop, cool storage, shipping and sales to foreign markets. From the test laboratory the grower receives notification of the sample testing being completed. They log into a portal to check the results. Agfirst BOP were looking for a new technology to transform the results from the service bus up to the web portal which gave them agility around modifying or adding tests.

 

Describe the working solution

We take sample measurement results from capture  devices. These get shipped to a landing warehouse. There is a trigger which calls the Alteryx Application residing on the Alteryx server for each sample and test type.  The Alteryx App then performs a series of calculations and publishes the results into the results warehouse. The grower is now able to login to the web portal and check their sample. Each App contains multiple batch macros which allow processing sample by sample. Some of the tests have a requirement for the use of advanced analytics. These tests call R as part of the App.  The use of macros is great as it provide amazing flexibilty and agility to plug in or plug out new tests or calculations. Having it on Alteryx Server allows it to be enterprise class by giving it the ability to be scaled and flexible at the same time. As well as being fully supported by the infrastructure team as it is managed within the data centre rather than on a local desktop.

 

App:

 

Agfirst APP.jpg

 

Batch Macro:

 

Agfirst Batch Macro.jpg

 

Describe the benefits you have acheived

The benefits realised include greater agility around adding/removing sample tests via the use of Macros. We are able to performed advanced analytics by calling R and it futures proofs the business by enabling them to choose any number of vendors and not be limited by the technology because of the ability of Alteryx to blend multiple sources. It gives them amazing flexibility around future technology choices and it is all supported and backed up by the infrastructure team because it sits within the datacentre and they have great comfort in knowing it's not something sitting under someones desk.

Author: Michael Peterman, CEO 

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.

 

 

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: 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.

 

 

 

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.

Author: Andy Kriebel (@VizWizBI), Head Coach

Company: The Information Lab

 

Awards Category: Best 'Alteryx for Good' Story

 

The Connect2Help 211 team outlined their requirements, including review the database structure and what they were looking for as outputs. Note that this was also the week that we introduced Data School to Alteryx. We knew that the team could use Alteryx to prepare, cleanse and analyse the data. Ultimately, the team wanted to create a workflow in Alteryx that Connect2Help 211 could use in the future.

 

Ann Hartman, Director of Connect2Help 211 summarized the impact best: "We were absolutely blown away by your presentation today. This is proof that a small group of dedicated people working together can change an entire community. With the Alteryx workflow and Tableau workbooks you created, we can show the community what is needed where, and how people can help in their communities."

 

The entire details of the project can be best found here - http://www.thedataschool.co.uk/andy-kriebel/connect2help211/

 

Describe the problem you needed to solve 

In July 2015, Connect2Help 211, an Indianapolis-based non-profit service that facilitates connections between people who need human services and those who provide them, reached out to the Tableau Zen Masters as part of a broader effort that the Zens participate in for the Tableau Foundation. Their goals and needs were simple: Create an ETL process that extracts Refer data, transforms it, and loads it into a MYSQL database that can be connected to Tableau.

 

Describe the working solution

 

See the workflow and further details in the blog post - http://www.thedataschool.co.uk/andy-kriebel/connect2help211/

 

Describe the benefits you have achieved

While the workflow looks amazingly complex, it absolutely accomplished the goal of creating a reusable ETL workflow. Ben Moss kicked off the project presentations by taking the Connect2Help 211 team through what the team had to do and how Connect2Help 211 could use this workflow going forward.

 

From there, the team went through the eight different visualisation that they created in Tableau. Keep in mind, Connect2Help 211 wasn't expecting any visualisations as part of the output, so to say they were excited with what the team created in just a week is a massive understatement.

 

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").

 

Author: Rana Dalbah, Director - Workforce Intelligence & Processes

Company: BAE Systems

 

Awards Category: Most Unexpected Insight - Best Use Case for Alteryx in Human Resources

 

Working in Human Resources, people do not expect us to be technology savvy, let alone become technology leaders and host a "Technology Day" to show HR and other functions the type of technology that we are leveraging and how it has allowed us, as a team, to become more efficient and scalable.

 

Within the Workforce Intelligence team, a team responsible for HR metrics and analytics, we have been able to leverage Alteryx in a way that has allowed us to become more scalable and not "live in the data", spending the majority of our time formatting, cleansing, and re-indexing. For example, Alteryx replaced both Microsoft T-SQL and coding in R for our HR Dashboard, which allowed us to decrease the pre-processing time of our HR Dashboard from 8-16 hours per month to less than 10 minutes per month, which does not account for the elimination of human intervention and error.

 

With the time savings due to Alteryx, it has allowed us to create custom metrics in the dashboard at a faster rate to meet customer demands. In addition, it has also given us the opportunity to pursue other aspects of Alteryx forecast modeling, statistical analysis, predictive analytics, etc. The fact that we are able to turn an HR Dashboard around from one week to two days has been a game changer.

 

The HR dashboard is considered to have relevant data that is constantly being used for our Quarterly Business Reviews and has attracted the attention of our CEO and the Senior Leadership. Another use that we have found for Alteryx is to create a workflow for our Affirmative Action data processing. Our Affirmative Action process has lacked consistency over the years and has changed hands countless times, with no one person owning it for more than a year. After seeing the capabilities for our HR Dashboard, we decided to leverage Alteryx to create a workflow for our Affirmative Action processing that took 40 hours of work down to 7 minutes with an additional hour that allows for source data recognition 

recognition and correction.  We not only have been able to cut down a two or three month process to a few minutes, but we also now have a documented workflow that lists all the rules and exceptions for our process and would only need to be tweaked slightly as requirements change.

 

For our first foray into predictive analytics, we ran a flight risk model on a certain critical population.  Before Alteryx, the team used SPSS and R for the statistical analysis and created a Microsoft Access database to combine and process at least 30 data files.  The team was able to run the process, with predictive measures, in about 6 months.  After the purchase of Alteryx, the workflow was later created and refined in Alteryx, and we were able to run a small flight risks analysis on another subset of our population that took about a month with better visualizations than what R had to offer.  By reducing the data wrangling time, we are able to create models in a more timely fashion and the results are still relevant.

 

The biggest benefit of these time-savings is that it has freed up our analytics personnel to focus less on “data chores” and more on developing deeper analytics and making analytics more relevant to our executive leadership and our organization as a whole.  We’ve already become more proactive and more strategic now that we aren’t focusing our time on the data prep.  The combination of Alteryx with Tableau is transformative for our HR, Compensation, EEO-1, and Affirmative Action analytics.  Now that we are no longer spending countless hours prepping data, we’re assisting other areas, including Benefits, Ethics, Safety and Health, Facilities, and even our Production Engineering teams with ad-hoc analytics processing.

 

Describe the problem you needed to solve 

A few years ago, HR metrics was a somewhat foreign concept for our Senior Leadership. We could barely get consensus on the definition of headcount and attrition.  But in order for HR to bring to the table what Finance and Business Development do: metrics, data, measurements, etc. we needed to find a way to start displaying relevant HR metrics that can steer Senior Leadership in the right direction when making decisions for the workforce.  So, even though we launched with an HR Dashboard in January of 2014, it was simple and met minimum requirements, but it was a start. We used Adobe, Apache code and SharePoint, along with data in excel files, to create simple metrics and visuals. In April 2015, we launched the HR Dashboard using Tableau with the help of a third party that used Microsoft SQL server to read the data and visualize it based on our requirements. However, this was not the best solution for us because we were not able to make dynamic changes to the dashboard in a fast timeframe. The dashboard was being released about two weeks after fiscal month end, which is an eternity in terms of relevance to our Senior Leadership.  

 

Once we had the talent in-house, we were able to leverage our technological expertise in Tableau and then, with the introduction of Alteryx, create our workflows that cut down a 2 week process into a few days - including data validation and dashboard distribution to the HR Business Partners and Senior Leadership.  But why stop there?  We viewed Alteryx as a way to help refine existing manual processes: marrying multiple excel files using vlookups, pivot tables, etc. that were not necessarily documented by the users and cut back on processing time. If we can build it once and spend minimal time maintaining the workflow, why not build it?  This way, all one has to do in the future is append or replace a file and hit the start button, and the output is created.  Easy peasy! That is when we decided we can leverage this tool for our compliance team and build out the Affirmative Action process, described above, along with the EE0-1 and Vets processing.

 

What took months and multiple resources now takes minutes and only one resource.

 

Describe the working solution

The majority of the data we are using comes from our HCM (Human Capital Management Database) in excel based files. In addition to the HCM files, we are also using files from our applicant tracking system (ATS), IMPACT Awards data, Benefit provider, 401K, Safety and Health data, and pension providers.

 

Anything that does not come out of our HCM system are coming from a third party vendor. These files are used specifically for our HR dashboard, Affirmative Action Plan workflow, Safety & Health Dashboard, and our benefits dashboard.

 

In addition to dashboards, we have been able to leverage the mentioned files along with survey data and macro-economic factors for our flight risk model. We have also leveraged Google map data to calculate the commute time from an employee's home zip code to their work location zip code. This was a more accurate measurement of time spent on the road to and from work when compared to distance.

 

The ultimate outputs vary: an HR dashboard that tracks metrics such as demographics, headcount, attrition, employee churn/movement, rewards and exit surveys is published as a Tableau workbook. The Flight Risk analysis that allows us to determine what factors most contribute to certain populations leaving the company; a compensation dashboard that provided executives a quick way to do merit and Incentive Compensation planning includes base pay, pay ratios, etc. is also published as a Tableau Workbook.

 

This workflow has as its input our employee roster file, which includes the employee’s work location and supervisor identifiers and work locations going up to their fourth level supervisor.  For the first step of processing, we used stacked-joins to establish employee’s supervisor hierarchies up to the 8th level supervisor.  We then needed to assign initial “starting location” for an employee based on the location type.  That meant “rolling up” the employee’s location until we hit an actual company, not client, site.  We did this because Affirmative Action reporting requires using actual company sites.  The roll-up was accomplished using nested filters, which is easier to see, understand, modify, and track than a large ELSEIF function (important for team sharing). 

 

Once the initial location rollup was completed, we then needed to rollup employees until every employee was at a site with at least 40 employees.  While simply rolling all employees up at once would be quick, it would also result in fewer locations and many employees being rolled up too far from their current site which would undermine the validity and effectiveness of our Affirmative Action plan.  Instead, we used a slow-rolling aggregate sort technique, where lone employees are rolled up into groups of two, then groups of two are rolled up into larger groups, and so on until sites are determined with a minimum of 40 employees (or whatever number is input).  The goal is to aggregate employees effectively, while minimizing the “distance” of the employee from their initial site.  This sorting was accomplished using custom-built macros with a group size control input that can be quickly changed by anyone using the workflow.

 

The end result was the roster of employees with the original data, with new fields identifying their roll-up location, and what level of roll-up from their initial location was needed.  A small offshoot of “error” population (usually due to missing or incorrect data) is put into a separate file for later iterative correction.

 

Previously, this process was done through trial and error via Access, and Excel.  That process, was not only much slower and more painstaking, but it also tended to result in larger “distances” of employees from initial sites then was necessary.  As a result, our new process is quicker, less error-prone, and arguably more defensible than its predecessor.

 

image001.png

 

One of the Macros used in AAP:

 

image002.png

 

Describe the benefits you have achieved

Alteryx has enabled our relatively small analytics shop (3 people) to build a powerful, flexible and scalable analytics infrastructure without working through our IT support.  We are independent and thus can reply to the user's custom requests in a timely fashion.  We are seen as agile and responsive - creating forecasting workflows in a few days to preview to our CEO and CHRO instead of creating Power Point slides to preview for them a concept.  This way, we can show them what we expect it to look like and how it will work and any feedback they give us, we can work at the same time to meet their requirements.  The possibilities of Alteryx, in our eyes, are endless and for a minimal investment, we are constantly "wowing" our customers with the service and products we are providing them.  In the end, we have been successful in showing that HR can leverage the latest technologies to become more responsive to business needs without the need for IT or developer involvement.

Author: Katie Snyder, Marketing Analyst

Company: SIGMA Marketing Insights

 

Awards Category: Most Time Saved

 

We've taken a wholly manual process that took 2 hours per campaign and required a database developer, to a process that takes five minutes per campaign, and can be done by an account coordinator. This frees our database developers to work on other projects, and drastically reduces time from data receipt to report generation.

 

Describe the problem you needed to solve 

We process activity files for hundreds of email campaigns for one client alone. The files come in from a number of different external vendors, are never in the same format with the same field names, and never include consistent activity types (bounces or opt-outs might be missing from one campaign, but present in another). We needed an easy, user-friendly way for these files to be loaded in a consistent manner. We also needed to add some campaign ID fields that the end user wouldn't necessarily know - they would only know the campaign name.

 

Describe the working solution

Using interface tools, we created an analytic app that allowed maximum flexibility in this file processing. Using a database query and interface tools, Alteryx displays a list of campaign names that the end user selects. The accompanying campaign ID fields are passed downstream. For each activity type (sent, delivered, bounce, etc), the end user selects a file, and then a drop down will display the names of all fields in the file, allowing the user to designate which field is email, which is ID, etc. Because we don't receive each type of activity every time, detours are placed to allow the analytic app user to check a box indicating a file is not present, and the workflow runs without requiring that data source.

 

 

 

All in all, up to six separate Excel or CSV files are combined together with information already existing in a database, and a production table is created to store the information. The app also generates a QC report that includes counts, campaign information, and row samples that is sent to the account manager. This increases accountability and oversight, and ensures all members of the team are kept informed of campaign processing.

 

Process Opt Out File - With Detour:

 

 

Join All Files, Suppress Duplicates, Insert to Tables:

 

 

Generate QC Report:

 

 

Workflow Overview:

 

 

QC Report Example:

 

 

Describe the benefits you have achieved

In making this process quicker and easier to access, we save almost two hours of database developer time per campaign, which accounts for at least 100 hours over the course of the year. The app can be used by account support staff who don't have coding knowledge or even account staff of different accounts without any client specific knowledge, also saving resources. Furthermore, the app can be easily adapted for other clients, increasing time savings across our organization. Our developers are able to spend time doing far more complex work rather than routine coding, and because the process is automated, saves any potential rework time that would occur from coding mistakes. And the client is thrilled because it takes us less time to generate campaign reporting.

Author: 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!

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.

 

 

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.

 

 

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.

 

 

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.

Author: Andrew Kim, Analyst (@andrewkim80916)

 

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.

andy_moncla_avatar.pngAuthor: Andy Moncla ( @andy_moncla ), 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?” 

Author: Jeffrey Jones (@JeffreyJones), Chief Analytics Officer  

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: 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