Past Analytics Excellence Awards

Suggest an idea

Amway logo.jpgAuthor: Adam Rant (@Rant) - Business Systems Analyst

Team Members: Tom Madden, Jordan Howell, Brian Conrad, Megan Lilley & Sankar Mishra

Company: Amway

Business Partner: Marquee Crew (@MarqueeCrew)

 

Awards Category: From Zero to Hero

 

Global Procurement at Amway began its journey with Alteryx by purchasing 2-licenses in October of 2015. We generated instant value from this tool, and knew there was so much untapped potential. A few short months after our initial purchase, we started hosting internal Alteryx enablement sessions to spread the word throughout our organization. It wasn’t long before we were up to 10 licenses. As our scope continued to expand, IT got involved and pursued a mini-trial that included Server for the remainder of 2016. In 2017 we purchased the full year pilot to drive even greater user adoption. Today, we have over 30 users and growing!


As our user base and experience with Alteryx grows, we have evolved from diverse data blending to language translation and normalization. We are moving from old legacy tools like Access and Excel, into the modern world of Analytics with Tableau and Alteryx. We are structuring data that we once thought to be impossible, and even built out applications to search online E-bay and now Amazon. We are pushing into the world of predictive analytics. Models are being developed and geospatial tool-sets are being examined. Most of these were pipe dreams before we were introduced to Alteryx. Now we are making these things come to life and blazing a trail for Analytics at Amway.

 

Describe the problem you needed to solve

We found Alteryx through Tableau. It started with simple Data blending and Tableau data automation, but grew from there.

  • Automating Manual Scorecards/metrics
  • Translation Macro using Google Translate
  • E-Bay Web Scrap
  • Amazon Web Scrap
  • Commodity Predictive Modeling
  • Spatial

 

Describe the working solution

We are using a wide range of data sources including excel, access, SQL Server (In DB tools), Oracle (In DB tools), SharePoint, Google Sheets, E-bay, and Amazon. Most of our data sets are published directly to Tableau Server. We have Server up and running to automate most of our Tableau Dashboards. Alteryx Gallery for deploying Apps is our next project to tackle. 

 

Describe the benefits you have achieved

Alteryx is the engine that is driving our team to new levels. We are automating all of our scorecards from a data perspective. We are able to provide daily insights on the health of our supply chain verses monthly reporting. Here are a few of the major projects we accomplished inside of Alteryx.

 

  1. Automated over 20 data processes, eliminating over 350 hours of data prep, and saving over $80,000 annually.
    • These savings are simply based on time savings. Factor into it the ability to run these workflows daily and deliver insights to our users and this is very conservative.
    • These workflows are now reusable processing engines that we can continue to enhance and build off of.
  2. Using Alteryx we are able to automate the translation of data. We operate in over 100 countries. Using Alteryx we developed a workflow that can go through our data and translate it automatically using Google translate. We plan on deploying this on our Server as an App for others to leverage.
  3. Jordan Howell eliminated a custom Access Database that cost us $3 Million dollars to build. This process eliminates 40 hours a month in manual data preparation due to the database, and will save us $24,000 a year. In 3 weeks he was able to recreate the database in Alteryx.
  4. E-bay & Amazon web scrapping to effectively audit Amway products that are being sold on these sites. Before Alteryx we manually accomplished this, and we would only get 1-10% of the total products on the sites (We had trouble answering questions at a Macro level about how many products). After Alteryx we can do this in seconds and have 100% of the products. Allowing users to focus on delivering insights from the data and not having to pull it!

 

I1.png

 

 I2.png

 

 I3.png

 

 I4.png

 

I5.png

 

 I6.png

 

 

amway2.png

Ford logo.pngAuthor: Luojiao Shen (@Beta) – Business Analyst

Team Members: Dan Totten, Celia Ortiz, David Harris, Matt Shanku, Mario Beasley, Rong Jing

Company: Ford Motor Company

 

 

Awards Category: From Zero to Hero

 

Our Ford IT Analytics Architecture Team started to work on an Enterprise Technology Refresh Program in Feb 2016. The objective was to simplify the technology footprint and the tech renewal process across the Enterprise in support of our IT Strategic Initiatives.


We use Alteryx to blend dozens of data sources in order to prepare the data for analysis and visualizations. Our team was trained within the company and able to use Alteryx in a week. We use Alteryx to visualize the current state of technology and application portfolios in order to gain insights. Additionally, we perform data analysis for resource prioritization and planning of Tech Refresh activities.

 

Describe the problem you needed to solve

Initial Business problem: In order to visualize the Technology Footprint, it was necessary to pull data from multiple data sources across our large, complex IT organization. Technology is moving faster than ever before. Ford IT needs to stay ahead of this rapid pace in order to satisfy its customers with premium information based services and exceptional mobility products. Additional use case: Technologies of servers, applications, appliances, database components, hosting landing zones, etc. need to align with rapid delivery of capabilities to the business

 

Describe the working solution

Our inputs came in every imaginable format. Dozens of repeatable workflows and macros were created to blend, ingest and process data to drive sound decision making. InDB tools were utilized for Hadoop Big Data transformation and output to visualizations in Tableau and QlikView.

 

Describe the benefits you have achieved

Our data blending processes are now automated using Alteryx macros to generate multiple reports and visualizations. Alteryx enabled self-service and object-based trouble shooting, data preparation and blending.

 

The user friendly interface helps us to rapidly modify or create new workflows.

  • Time saving – processing time is reduced by 80%
  • Reduction of errors – is experienced due to the process now being automated
  • Business customer satisfaction has increased due to improved efficiency, quality and rapid delivery of actionable metrics

 

ford1.png

 

 

ford2.png

 

 

ford3.png

 

 

ford4.png

 

 

 ford5.png

 

 

 ford6.png

 

 

 ford7.png

84.51_logo.pngAuthor: Michael Carrico (@mcarrico) - Senior Analyst

Team Members: Justin Conley, William Storey, Jeff Bevan

Company: 84.51°

 

Awards Category: Best Value Driven with Alteryx

Following our POC with Alteryx, analysts began to socialize the automation benefits and workflows that not only saved an estimated 6,300 hours across 15 projects (realized and projected hours), but that also align with our company mission of making people’s lives easier.  Further detail pertaining to the value driven by Alteryx can be seen in the Benefit section below.

 

Awards Category: From Zero to Hero

Using the workflow outlined in the Business Challenge section below as well as other exploratory work in our POC, we were able to see the potential of Alteryx to solve challenges within our analytical organization. As a result of these POC projects, we have launched a series of initiatives outlined in the Solution section below to empower employees across 84.51° to realize the benefits offered by Alteryx.

 

Describe the problem you needed to solve

The original use case for Alteryx within our organization was for geospatial analysis by a small team within our larger analytics organization. After great success in the geospatial space, this team setup a proof of concept for a select number of members of our insights and engineering organizations to investigate the value of the software outside of its geospatial capabilities. One of the projects in our pipeline required automating a PowerPoint deliverable that pulled from a large number of sources located in multiple technologies (i.e. CSV, Excel, SQL Server, Oracle). The team decided to evaluate Alteryx to tackle this automation task, and the result is an ever-evolving analytic app that leverages a wide array of Alteryx tools as well as custom development called within the workflow. Through this combination, we were able to not only glue together many disparate data sources, but to create a solution that achieved end-to-end automation of a PowerPoint deck in ten minutes, which would previously have taken days for our client leads to complete. In the process of making this Frankenstein workflow, the team gained exposure to analytic apps, batch and iterative macros, and most importantly, the Run Command tool, which enabled us to call a custom framework developed in-house to populate templated PowerPoint decks with category-specific data without any manual intervention. In the end, it is estimated that this single workflow saves approximately 450 hours annually of repetitive, mindless work; more importantly, though, it exposed our wider analytical organization to all of the power and possibilities that Alteryx offered. Many of the lessons learned in this first workflow would go on to guide future development opportunities and automation that have resulted since.

 

84.51-1.png

 

Describe the working solution

After understanding the power of the tools included in Alteryx as well as the ability to extend them using packaged macros and the Run Command tool, we have continued to find projects well-suited for Alteryx automation. Dreaded data refreshes and endless email exchanges have evolved into scheduled workflows and analytic apps, and our analysts have taken notice. Recognizing this increased interest, the members of our POC began to lay the groundwork for equipping our entire insights organization with the capacity to perform analysis and automation within Alteryx.

 

Our "A-Team" has since been formed to create Alteryx trainings, standards, a macro library, learning groups, reference guides, video tips, and template workflows to continue spreading the Alteryx love across not only our analytical organization, but our client line and IT groups as well. By supplementing the existing abilities of Alteryx with these company-specific resources, Alteryx has now become a standard starting point for many of our automation tasks, and the success stories that are being shared in company-wide meetings have generated enough buzz that our user base has more than doubled in the last 6 months. In addition to automation, our larger user base is continuing to push the software in other directions such as incorporating new data sources, modeling, and building on the geospatial success that started Alteryx in our organization.  

 

84.51-2.png

 

Describe the benefits you have achieved

Alteryx has enabled our analysts to think differently about the way that they approach projects. Before Alteryx, several different technologies may have been required to execute various phases of projects. For example, a typical analyst execution plan may have included SQL to pull data from the warehouse, the analyst's programming language of choice to transform/analyze/model that data, and then into Excel/Tableau/PowerPoint for telling the story. This fragmented technology stack caused for inefficiencies in data movement from start to finish, as well as an analytical plan that was not set up for repeatability. Alteryx has facilitated the consolidation of each of these steps into a single workflow that can quickly be flipped from a custom insights project to an analytic app that can either be made available for our client associates to order themselves without analyst intervention or setup on a scheduler to allow periodic refreshes of deliverables.

 

Members of our insights organization have created analytic apps and workflows that have either saved or are projected to save the organization an estimated 6,300 hours across 15 projects. To put that in perspective, that is over 8.5 months of savings or approximately 3 FTEs over just 15 projects. Many of these hours are recurring so we will continue to realize these savings as these workflows are sustained and enhanced. Furthermore, these automation tasks are not only saving time, but they are enabling members of our analytic and client organizations to work more efficiently now that tasks which previously took days are reduced to hours, and those that took hours are reduced to minutes. Through our training, standardization, and enablement initiatives, we are hoping to scale these benefits from 15 projects spread across 7 analysts to countless projects spread across 150 analysts.  

marketo-logo.pngTeam Members: Amy Jorde, Ambika Suresh, Praneesha Gaddam, Tim Chandler (@TZChandler)

Company: Marketo

Business Partner: Grazitti, Rahul Sachdeva (@rahuls)

 

Awards Category: Icon of Analytics

 

This story is about going from nothing to a corporate wide data pipeline and on-demand data delivery in less than 12 months. During the deployments this grew from 1 to 5 people that control the data of the company. The business impact is in almost all departments powering our Dashboards.

 

Marketo has been a customer of Alteryx since 2015, but it wasn’t until January 2016 that we truly started to use Alteryx throughout the company. In 2016, Alteryx became a mission critical solution that changes the way we work across the company. Organizations include Customer Success, Sales, Partner Programs, Finance, Marketing, Legal, Accounting, and Professional Services.

 

Describe the problem you needed to solve

Deliver company wide trusted data to enable better business decisions. Eliminate or reduce the many manual processes across the company that take too long to accomplish and are prone to errors. 

 

Describe the working solution

This solution is described in three parts.

1. Integration and Automation
2. Business Enablement
3. Scaling and Accelerating Business Intelligence Adoption

 

1. Integrations and Automation

In early 2016, Marketo launched Mavericks, our company data pipeline. A 100% cloud based solution, Mavericks pulls data from many sources, enriches the data using business rules and then published the data where it’s needed. Every week our production system has over 80 scheduled workflows and 30 gallery applications delivering the information that organizations rely on. Marketo uses off-the-shelf and custom API connections to integrate data the business needs.

 

Integrations include:

  • Marketo
  • Salesforce.com
  • Tableau
  • Intacct
  • Coupa
  • Workday
  • Oanda
  • Jobvite
  • Hire Bridge
  • Hire Select
  • Xactly
  • Box
  • Jira
  • Slack
  • Clarizen
  • AWS (S3 and Redshift)
  • High Radius
  • Host Analytics
  • Allocadia
  • Silicon Valley Bank
  • Smartsheets

 

Company Data PipelineMarketo_company data pipeline.png

 

Business Continuity
Workflows are mission critical to our business so we have developed a production process that enables us to identify issues before the business. Workflows are monitored by Tableau performance monitoring dashboards. These dashboards show trend of workflow behavior and highlight issues. We also have integrated Slack to both alert us to issues and allow the business to see when data is refreshed and more.

 

Bidirectional Data Integration
Marketo originally used Alteryx to publish information to our BI environment Tableau, however starting in early 2016 we went further with bidirectional system updates. With each implementation we moved away from manual to automated processes.

 

Here are some same use-cases we have completed in the last year.

  • We have a scalable methodology for obtaining Salesforce.com data, storing in AWS Redshift, publishing to Tableau, and using for other workflows without overwhelming our Salesforce.com instance with API requests.
  • Currency exchange rates from OANDA are fully integrated into Salesforce.com using methodology which matches the exchange rates calculated by our ERP (Intacct).
  • We prevent accounts payable check fraud with a workflow which automatically triggers a positive pay file run in our Intacct with an Intacct partner tool from Wipfli and then sends the resulting file with authorized check numbers, payees, and amount paid directly to the bank. The entire process is monitored by our AP team in Slack.
  • Intacct invoices and invoicing schedules get processed and displayed in a Tableau dashboard for deferred revenue forecasting, a dashboard which ends up more accurate than the forecasting report found directly inside Intacct!
  • Many more!


2. Business Enablement

Using Alteryx Gallery, we provide on-demand solutions to our business partners freeing them from time consuming manual processes.

 

With the use of Marketo Alteryx Gallery, users can update data previously only able to be updated via tedious manual updates or via API only. Example workflows which have been added into our company’s gallery allow users perform the following types of tasks:

  • Attach expense receipt PDFs for billable expenses to the related customer invoices. No more edit, attach, save on automatically created expense invoices - just click a button in the gallery and done!
  • Automatically write off small balances owed by customers on invoice transactions which meet certain fixed criteria.
  • Reactivate employee records in our ERP and expense tool for final expense report processing. A related scheduled workflow then deactivates the employee after the expense report has been reimbursed to that employee. No more Jira tickets to reactivate employees and we maintain clean data!
  • A collection of gallery applications allows insights into monthly professional services revenue being posted to ensure that data is accurate and will post correctly into our general ledger.
  • Excel Replacement! Upload CSV files and export with blended and processed data by combining uploaded file with other system data and returning complete file or analysis. One of our gallery apps used in this manner saves over 40 hours per month of processing time!
  • Process integrations on demand if user needs to have up to date data in another system and cannot wait until the scheduled timing. Data on your schedule.

 

3. Scaling and Accelerating Business Intelligence Adoption

Using Alteryx to prepare information for our BI environment has benefited us in both scaling and accelerating our adoption. Here’s how:

 

Scaling
Traditionally much of the BI data manipulation happens within the BI presentation layer. This includes blending data sources, creating calculated fields and complex level of detail solutions. We take a different approach. We use Alteryx to do the data prep including detailed data modeling. This approach minimizes the load on our BI Server allowing us to have more concurrent users than is typically expected. The costs avoided for 2017 and beyond look substantial.

 

Accelerating Adoption
Driving adoption with the business happens faster when the data is in the right model and has common definitions across the company. 80% of the work is data prep and this is all done in Alteryx. Instead of training the Business to become Tableau experts, we choose to focus on driving adoption. This means that we do not spend time on calculated fields, blending data and LOD with the business. We focus on shifting from manual Excel solutions to more insightful visual dashboards. Several people in the business were able to publish dashboards that changed the way people worked within days. Reconciliation of data has dramatically been reduced. For example Marketo use a single Opportunity data source for all dashboards.

 

Marketo’s Data Pipeline for Opportunity DataMarketo_data pipeline for opportunity data.png

 

Workflow description

Data is pulled from five Salesforce.com tables and blended together in grey boxes. Yellow boxes blend names, adjust times and create calculated fields. Green boxes convert currencies to USD. From the orange to the blue box data is blended with excel spreadsheets owned and updated by the business. The blue box adds a timestamp and the final yellow boxes publish to Tableau.

 

Summary

The combination of powering our company's data pipeline, Mavericks, and providing on demand services with Gallery has allowed us to take control of our business’s data. It’s an awesome tool where the only limits are our time and creativity.

 

Our business partners are incredibly happy with the work we’ve done on our Alteryx server over the last year. Look for more results from us in 2017!

 

Describe the benefits you have achieved

The impacts of Alteryx are wide. Here are some of the highlights.

  1. Time saved as a result of automated integration and data blending using Alteryx is over 250 hours each month, conservatively. This is based on feeback from two departments that state have detailed their timesaving. With each new solutions we save more and more business process time.
  2. Cost avoidance comes in the way of being able to scale Tableau by Alteryx doing the data processing.
  3. Data Quality. Data is trusted across the company allowing people to focus on the busuness rather than ratifying numbers.  

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

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

Company: Quantum Spatial, Inc.

Business Partner: Slalom Consulting, Matt Ewalt

 

Awards Category: Best Value Driven with Alteryx

 

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

 

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

 

Awards Category: From Zero to Hero

 

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

 

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

 

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

 

Describe the problem you needed to solve

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

 

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

 

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

 

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

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

 

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

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

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

  • Consultants
  • Expenses
  • Labor
  • Units

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

  • Consultants
  • Expenses
  • Labor
  • Units

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

 

qsi 2.png

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

 

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

 

Describe the benefits you have achieved

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

 

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

 

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

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

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

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

Business Partner: Conjecto

 

Awards Category: From Zero to Hero

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

 

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

 

Awards Category: Best Value Driven with Alteryx

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

 

Describe the problem you needed to solve

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

 

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

 

Describe the working solution

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

 

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

 

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

 

Describe the benefits you have achieved

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

 

CRC-1.png

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

Team Members: Trudy Fuher, Alana Welz, Arlyn Baggot

Company: Educational Media Foundation

 

Awards Category: Best ‘Alteryx For Good’ Story 

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

 

Awards Category: Best Use of Alteryx Server for Analytics Deployment

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

 

Awards Category: Best Use of Alteryx for Spatial Analytics

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

 

Awards Category: Best Value Driven with Alteryx

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

 

Awards Category: From Zero to Hero

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

 

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

 

Describe the problem you needed to solve

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

 

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

 

Describe the working solution

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

 

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

 

Figure 1Figure 1

 

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

Figure 2Figure 2

 

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

 

Figure 3Figure 3

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

 

Figure 4Figure 4

 

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


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

 

Primary workflow:KLOVE-5.png

 

Supporting macros:KLOVE-6.png

 

 

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

 

 

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

 

 KLOVE-9.png

 

 

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

 

Describe the benefits you have achieved

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

 

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

 

Other Significant Alteryx Use-Cases

 

1. Transmitter location identification

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

     

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

 

2. Log file FTP download

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

     

     KLOVE-12.png

     

 

 

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

 

3. User import of Excel into SQL Server

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

     

     

 

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

 

4. Tealium reporting

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

     

    KLOVE-14.png
  • Benefit: Faster Tableau reports

 

5. Studio automation logs

 

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

     

     

  • Benefit: Reliable data for reporting.

 

6. Record of donor communication

 

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

     

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

 

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

Team Members: Mike Svidron, Cynthia Zwergel

Company: Fifth Judicial District of PA

Business Partner: eMoksha LLC – Vivek Mehta

 

Awards Category: From Zero to Hero

 

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

 

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

 

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

 

Describe the problem you needed to solve

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

 

Describe the working solution

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

 

Describe the benefits you have achieved

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

 

 

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

 

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

 

 Figure 3 - Warrant NotificationsFigure 3 - Warrant Notifications

 

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

 

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