community
cancel
Showing results for 
Search instead for 
Did you mean: 

Past Analytics Excellence Awards

Alteryx Gallery is experiencing a problem in which system emails are not being sent out. As a result, if you are attempting to sign up for a new account, you may be unable to verify your email address. We are working to solve this as soon as possible and will remove this notice once resolved.
Suggest an idea

Author: Dan Jeavons, GM Advanced Analytics Centre of Excellence

Team Members: 

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

Company: Shell

Awards Category: Icon of Analytics

 

Shell is a global group of energy and petrochemical companies.

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

PATH logo.png

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

Company: PATH 

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

 

Awards Category: Best 'Alteryx for Good' Story

 

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

 

Describe the problem you needed to solve 

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

 

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

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

 

Describe the working solution

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

 

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

 

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

 

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

 

PATH Workflow 1.png

 

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

 

PATH Workflow 2.png


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

 

PATH Workflow 3.png

 

 

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

 

PATH Mapbox.png

 

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

 

PATH Tableau Viz.png

 

 

Describe the benefits you have achieved

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

 

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

PATH Benefits 1.png

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

 

KLOVE-1.pngFigure 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)

KLOVE-2.pngFigure 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)

 

KLOVE-3.pngFigure 3

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

 

KLOVE-4.pngFigure 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.

 

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 (@TimChandler)

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.  

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!

 

 

5jd-1.pngFigure 1 - Case Creation Process for Adult Probation

 

 5jd-2.pngFigure 2 - Partial - Case Creation from xml for Adult Probation

 

 5jd-3.pngFigure 3 - Warrant Notifications

 

5jd-4.pngFigure 4 - Case Lifecycle for Statistical Reporting

 

5jd-5.pngFigure 5 - Reporting for PA Department of Probation and Parole

 

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

Team Members: Jack Pitts, Itzela Vasquez de Mundis

Company: Data Intelligence Group

 

Awards Category: Best Value Driven with Alteryx

 

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

 

Describe the problem you needed to solve

40M dollars in potential revenue was not being realized.

 

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

 

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

 

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

 

The process looked something like this:

 

DIG1.png

 

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

 

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

 

Some of the challenges presented to DIG included:

 

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

 

Describe the working solution

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

 

Specifically, the solution is driven by the following processes:

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

DIG3.png

 

Describe the benefits you have achieved

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

 

The previous solution was slow, cumbersome and static.

 

The Alteryx solution is responsive, fast and optimized.

 

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

 

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

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

Increased Visibility

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

Revenue

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

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

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

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

Honeywell_logo.pngAuthor: Joseph Majewski - Commercial Finance Director

Team Members: Niki Yang, Larry Scates, Dan Trimble, Richard Haas, Wendy Edsall

Company: Honeywell Aerospace

Business Partner: Sherri Benzelock - VP Business Analytics

 

Awards Category: Best Use of Alteryx Server for Analytics Deployment

 

Alteryx has automated previous manual processes driving over 2000 hrs annual productivity to the Finance community. Alteryx server consolidates quarterly outlook commentary data from over 40 sources every 30 mins daily and publishes to Tableau server as a *.tde data source. Alteryx also loads extracted Essbase files to Tableau server nightly which are joined together for enhanced Tableau analytics providing insight to our quarterly forecast. 

 

Awards Category: Best Value Driven with Alteryx

 

Alteryx helped drive over 2000 annual hours of productivity and enabled over 250 users to migrate to Tableau based self- service analytics. Consumers are now better prepared for weekly Revenue outlook meetings as they have access to the data almost real-time versus waiting for Finance to prepare reports for the weekly forecast meetings. The new analytics developed in Tableau are now being referenced live in weekly meetings with business Presidents. 

 

Describe the problem you needed to solve

Significant manual effort was being done to compile business comments and associate them with the financial data on a management review dashboard in PowerPoint. Meetings were ineffective because only limited static views could be generated. The data presented was information overload and difficult to digest. There was no dynamic selection views so questions typically needed to be captured then answered and communicated after the meeting. Historical versions were saved in separate data sources and were not readily available for the reviews.

 

honeywell1.jpgAlteryx flow to update the Comments which publishes the comments data source to both our Aero-Development and our Aero Certified-Production sites

Describe the working solution

Under the new Alteryx solution, the compilation of weekly commentary files has been automated. Every week over 40 comment files are saved to a shared drive in *.csv format. Since submissions occur from all global regions, files are submitted at different times each week and are now consolidated more frequently (every 30 mins) allowing users across the globe to access the most updated information. The process also manages current week and historical weeks’ comments. We are also using Alteryx to cleanse the data removing all the error values and to correct business team data mapping issues. Alteryx also loads extracted Essbase files to Tableau server nightly which are joined together for enhanced Tableau analytics providing insight to our quarterly forecast.

 

honeywell2.jpgAlteryx flow that brings in the new Essbase data (“SRO_Extract_Current.txt” and updates/appends/replaces the scenarios in the “SRO_Extract_History.txt” Data source file used by Tableau

Describe the benefits you have achieved

Utilizing Alteryx has resulted in initial savings of 2000+ hours per year by cleansing, compiling, and blending business commentary with financial data. Analysts are no longer manually compiling, copying, and pasting comments onto quarterly outlook presentations. Weekly review meetings became more effective as over 250 business leaders and analysts are better informed prior to as opposed to seeing the data in the meeting for the first time. This is allowing more efficient business meetings helping HON achieve its revenue growth targets.

The Alteryx dataset allows Honeywell Aerospace to slice and dice all information dynamically using Tableau which was not possible before. Questions are easily answered in the meetings because the data with variance, opportunity, and risk commentary is available prior to the meeting. Ad hoc questions are now researched live in meetings through our enhanced visualizations. Tableau’s dynamic reporting eliminated the need for Excel and PowerPoint reports thus reducing meeting preparation times as well. Business teams are now able to focus more on analytics than the data/presentation preparation. The solution we developed are making our business processes more contemporary, enabling business users quicker access in a format that is easier to consume on desktop and mobile devices ensuring they have the necessary information to make the right business decision.

 

honeywell3-A.png

 

honeywell3-B.pngVisual of Tableau Analytic with associated comments

Catalyst logo.pngAuthor: Jason Claunch - President

Company: Catalyst

Business Partner: Slalom Consulting - Sean Hayward & Marek Koenig

 

Awards Category: Best Use of Alteryx for Spatial Analytics

 

The developed solution used many of the Spatial Analytics components available within Alteryx:

  • Trade Area – have user select target area to analyze
  • Spatial Match – combine multiple geospatial objects,
  • Intersection – cut objects from each other to create subject area
  • Grid tool – sub-divide the trade blocks to determine complete coverage of trade ring
  • Distance – use drivetime calculation to score and rank retailers in the vicinity

Describe the problem you needed to solve

Retail site analysis is a key part of our business and was taking up too much time with repetitive tasks that could have been easily automated.

 

Describe the working solution

To support selection of best-fit operators, Catalyst partnered with Slalom Consulting to develop a tool to identify potential uses to target for outreach and recruitment. Previously, we would have to manually build demographic profiles using tools like qGIS, ESRI, and others, but found the process to be cumbersome and quite repetitive. Demographic data was acquired at the trade bloc level, which was too granular for identify target locations and would not mesh well with the retail data.

 

Alteryx and its spatial capabilities was used in a few ways:

 

1) Minimize our retail data selection from the entire US to a selected state using the Spatial Match tool.catalyst1b.png

 

2) Create a demographic profile for each retail location that consisted of data points such as median income, population, daytime employees, and others. The data was aggregated around a 3 mile radius of the specific retail location with an Alteryx Macro composed of a Trade Area, Grid Tool, Spatial Match, and Summarization tool.

 

catalyst2-A.png catalyst2-B.png catalyst2-C.png

 

3) Using a Map Input, the user selected an area to profile and candidate retailers were output for further review.

catalyst3.png

 

4) After selecting specific retailers to do an in-depth analysis on, Alteryx would score all possible locations by distance (Drivetime Analysis) and by score (proprietary weighting of various demographic attributes). The profiled results were then used to build a client presentation; the automated profiling tool saved us countless hours and allowed us to deliver more detailed analysis for our clients.

 

catalyst4.png

 

Describe the benefits you have achieved

Using Alteryx was a massive time saver, the tool that we built took a process that normally required at least 8 hours of manual work down to merely a few minutes. This has directly benefited our bottom line by allowing us to focus on more key tasks in our client outreach and recruitment. A return-on-investment was immediately realized after we were able to close a deal with a major client using our new process.

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 solutionqsi 1.pngThis 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.

QralGroupLogo.jpgAuthor: Ryan Bruskiewicz (@rbruskiewicz) - Management Consultant

Company: Qral Group

 

Awards Category: Best Use of Alteryx for Spatial Analytics

 

I am using spatial analytics in Alteryx, in combination with healthcare utilization data for drugs and procedures published by Centers for Medicare & Medicaid Services (CMS) and shape files on data.gov (ZCTA, US primary roads) to optimize geographic territory alignment for sales representatives in the life sciences industry.

 

The process leverages several spatial analytics tools in Alteryx, including Distance/Driving Distance, Find Nearest, Create Points, SpatialObjCombine in Summarize Tool, Spatial Match, and Location Optimization Macros. The Alignment Optimization workflow outputs data files for visual mapping, analysis and summary reporting in Tableau, and outputs files to a tool called TerritoryMapper for manual refinement of territory zip code boundaries.

 

Describe the problem you needed to solve

The Initial Business Problem

The business problem solved is optimization of sales force territory alignments. The objective is to create territories that are balanced in terms of workload and sales potential and consider geographic constraints and travel time. The approach developed in Alteryx efficiently optimizes geographic alignments in a matter of minutes without costly purchases of third party data sources:

  • Traditional approaches to this business problem in the life sciences typically require purchase of third party healthcare drug/procedures utilization data for specific therapeutics areas or markets that represent a significant investment. Leveraging data published by CMS, we were able build a completely flexible model that can optimize territory alignment design for any market basket (i.e. any combination of drugs or healthcare procedures) without purchasing additional data.
  • Traditional approaches also typically require weeks or months to complete (as opposed to minutes or hours with our approach in Alteryx). The approach eliminates time spent purchasing/acquiring data, loading data, preparing/summarizing data, loading data into an alignment tool, manually defining and refining territory boundaries, and summarizing alignment results – these steps are fully-automated with our Alteryx workflow.

Additional use cases solved
Leveraging the alignment optimization workflow as an initial platform, solutions to address other related business problems have been developed and integrated into the workflow:

  • Market sizing & value concentration curve
  • Physician segmentation by patient volume & specialty
  • Sales force sizing to determine optimal # of sales representatives for a given targeting strategy
  • Territory-level call plans to physicians/accounts

Together, this set of solutions provides a suite of tools to automate and optimize field sales force deployment.

 

Drivers and applications
These solutions are used on projects to support Business Development and/or Commercial Operations teams within life sciences companies. The alignment optimization workflow has also been generalized to enable Sales Operations teams in any industry to design sales territories by taking an individual company’s customer target list and demand/sales history as an input.

 

Internally, Qral Group has leveraged this tool to create territory alignments for many combinations of sales force size and therapeutic area. With this broad set of alignment scenarios, we can quantify how much of territory alignment is effectively “objective” due to population distribution vs. variable for specific therapeutic areas due to regional differences in disease incidence and prevalence. We found that there is typically an 80-85% overlap of territory alignment, regardless of therapeutic area!

 

Optimal Territory Alignment by Therapeutic Area and Sales Force SizeQral1.png

 

 

Describe the working solution

The working solution integrates ~12 GB of data representing ~12 billion healthcare claims from the following data sources:

  • Flat files (.csv)
    • Healthcare provider universe
    • Medicare provider utilization & payment data for inpatient procedures, outpatient procedures, Part D prescribers, and Part B services
    • Demographic data for population by age, gender by ZIP Code
  • Shape files (.shp)
    • S. primary roads geodatabase
    • Cartographic boundary shape data for ZIP Code Tabulation Areas (ZCTA)

The alignment optimization tool consists of two repeatable workflows.

  • The first workflow integrates the data sources described above into a database that can be consumed by the alignment optimization algorithm, and is only run when raw data sources need to be updated (typically twice per year)
  • The second workflow connects to an excel-based user input form and leverages several custom-built Alteryx macros to execute the geographic territory alignment optimization. This workflow is run on-demand to create the territory alignment output.

The excel user input form allows a user to specify:

  • Drugs, services, and provider types to be considered markers for sales representative workload
  • Planned workload/call frequency by provider segment
  • Target workload range (min/max) for each territory for workload balancing optimization
  • Number of sales representatives, number of first-line sales managers, and number of second-line sales managers

The optimization workflow also includes macros to accomplish certain complex operations, batch processes, iterative processes, and optimizations, such as:

  • Clustering algorithm macro using native R-based clustering tool to identify territory workload centers
  • Adjustment of territory alignment to consider geographic constraints, such as US primary roads, driving time, and state boundaries
  • Batch macro to split heavy geographies into equal territories
  • Location optimization macro to optimally rebalance workload across neighboring territories by reassigning ZIP codes
  • Iterative macro to define locations for 1st line and 2nd line managers, and determine sales force hierarchy (assignment of reps to districts and regions)

The ZIP-Territory alignment and sales force hierarchy is output to Tableau to visualize the geographic alignment and report on summary statistics (e.g. number of customers, sales, workload) for each territory and span of control for managers.

 

Describe the benefits you have achieved

Alteryx has had a significant impact on our consulting project work through time savings and cost reduction.  For this geographic territory alignment use case, specifically:

  • Time savings: Reduced time needed to complete analysis from 1-2 months to 1 day (savings across each analysis step from data acquisition, loading, preparation, alignment optimization, and results visualization)
  • Cost savings: For clients who do not already own prescriber-level data, reduced cost by eliminating need for a costly one-time data purchase from third party vendors to complete territory alignment analysis.

At Qral Group, we have realized many benefits of leveraging Alteryx Designer for other use cases as well.  We regularly analyze large volumes of healthcare claims data.  We can use Alteryx to process hundreds of millions and billions of records without heavy investment in infrastructure.  This capability allows to better understand treatment pathways and patient journey, patient utilization, referral networks, physician and patient segmentation, payer cost impacts, and patient identification for rare diseases.

Author: Manju Devadas (@manju_devadas) - CEO

Team Members: Vijay Bondale, Salil Amonkar

Business Partner: Pluto7

Client: Cisco

 

Awards Category: Best Value Driven with Alteryx

 

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

 

Describe the problem you needed to solve

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

 

Describe the working solution

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

 

Describe the benefits you have achieved

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

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

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

Company: Southwest Airlines Co.

 

Awards Category: Best Value Driven with Alteryx

 

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

 

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

 

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

 

Describe the problem you needed to solve 

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

  

Describe the working solution

Alteryx performs three basic jobs for this project:

  • Data connector
  • Model builder
  • Task runner

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

 

Describe the benefits you have achieved

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

SW 1.pngSummary

 

 SW 2.pngWizard Work Load By Task Cards

 

SW 3.pngWork Load From SBX by Hours

 

SW 4.pngStaffing From QuickBase

 

SW 5.pngCompliance Score from Excel

 

SW 6.pngWrite Back To QuickBase

Author: Michael Barone, Data Scientist
Company: Paychex Inc

Awards Category: Best Use of Predictive

 

Describe the problem you needed to solve

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


Describe the working solution

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

 

Step 1: ETL

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

51.png

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

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

52.png

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

53.png

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

54.png

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

 

Step 2: App

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

545.png

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

56.png

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

57.png

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

58.png

 

Step 3: Gallery

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

59.png

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

591.png

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

592.png

And then they can select the various client criteria:

593.png

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

594.png

 

Describe the benefits you have achieved

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

Author: Slaven Sljivar, Vice President, Analytics

Company: SmartDrive Systems, Inc.

 

Awards Category: Most Time Saved

 

Describe the problem you needed to solve

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

 

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

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

 

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

 

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

 

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

 

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


Describe the working solution

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

 

41.png

 

Describe the benefits you have achieved

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

 

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

 

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

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

 

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

 

Describe the problem you needed to solve

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


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

 

Describe the working solution

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

 

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

 

Describe the benefits you have achieved

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

 

31.jpg


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

32.jpg

 

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

 

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

Author: Alexandra Wiegel, Tax Business Intelligence Analyst In-2C-14px.png
Company: Comcast Corp


Awards Category: Best Business ROI

 

A Corporate Tax Department is not typically associated with a Business Intelligence team sleekly manipulating and mining large data sources for insights.  Alteryx has allowed our Tax Business Intelligence team to provide incredibly useful insight to several branches of our larger Tax Department. Today, almost all of our data is in Excel or csv format and so data organization, manipulation and analysis have previously been accomplished within the confines of Excel, with the occasional Tableau for visualization. Alteryx has given us the ability to analyze, organize, and manipulate very large amounts of data from multiple sources.  Alteryx is exactly what we need to solve our colleague’s problems.


Describe the problem you needed to solve

Several weeks ago we were approached about using Alteryx to do a discovery project that would hopefully provide our colleagues further insight into the application of tax codes to customer bills. Currently, our Sales Tax Team uses two different methods to apply taxes to two of our main products respectively. The first method is to apply Tax Codes to customer bill records and then run those codes through software that generates and applies taxes to each record. The second method is more home-grown and appears to be leading to less consistent taxability on this side of our business.

 

Given that we sell services across the entire country, we wanted to explore standardization across all our markets. So, our Sales Tax team tasked us with creating a workflow that would compare the two different methods and develop a plan towards the goal of standardization and the effect it would have on every customer’s bills.

 

Describe the working solution

Our original source file was a customer level report where the records were each item (products, fees, taxes, etc.) on a customer’s bill for every customer in a given location. As it goes with data projects, our first task was to cleanse, organize, and append the data to make it uniform.

 

21.PNG

 

The next step was to add in the data from several data sources that we would ultimately need in order to show the different buckets of customers according to the monetary changes of their bills. Since these sources were all formatted differently and there was often no unique identifier we could use to join new data sources to our original report. Hence, we had to create a method to ensure we did not create duplicate records when using the join function. We ended up using this process multiple times (pictured below)

 

22.PNG

 

And so, the workflow followed. We added tax descriptions, new codes, and other information. We added calculated fields to determine the amount of tax that should be owed by each customer today, based on our current coding methods.

 

23.PNG

24.PNG

25.PNG

26.PNG

26.PNG

 

After we had layered in all the extra data that we would need to create our buckets, we distinguished between the two lines of business and add in the logic to determine which codes, at present, are taxable.

 

28.PNG

 

For the side of our business whose taxability is determine by software, you will notice that the logic is relatively simple. We added in our tax codes using the same joining method as we did above and then used a single join to a table that lists the taxable codes.

 

29.PNG

 

For the side of our business whose taxability is determine by using our home-grown method, you can see below that the logic is more complicated. Currently, the tax codes for this line of business are listed in such a way that requires us to parse a field and stack the resulting records in order to isolate individual codes. Once we have done this we can then apply the taxability portion. We then have to use this as a lookup for the actual record in order to determine if a record contains within the code column a tax code that has been marked as taxable. Or in other words, to apply our home-grown taxability logic is complicated, time consuming, and leaves much room for error.

 

210.PNG

 

Once we stacked all this data back together we joined it with the new tax code table. This will give us the new codes so that the software can be used for both lines of business. Once we know these new codes, we can simulate the process of the software and determine which of the new codes will be taxable.

 

211.PNG

 

Knowing whether or not codes are taxable helps us hypothesize about how problematic a geographic location may end up being for our team, but it does not tell us the dollar amount of taxes that will be changing. To know this we must output files that will be run through the real software.

 

Hence, once we have completed the above data manipulation, cleansing, and organization, we extract the data that we want to have run through the software and reformat the records to match the necessary format for the software recognition.

 

212.PNG

213.PNG

 

We created the above two macros to reformat the columns in order to simply this extensive workflow. Pictured below is the top macro. The difference between the two resides in the first select tool where we have specified different fields to be output.

 

214.PNG

 

After the reformatting, we output the files and send them to the software team.

 

215.PNG

216.PNG

 

When the data is returned to us, we will be able to determine the current amount of tax that is being charged to each customer as well the amount that will be charged once the codes are remapped. The difference between these two will then become our buckets of customers and our Vice President can begin to understand how the code changes will affect our customer’s bills.

 

Describe the benefits you have achieved

Although this project took several weeks to build in Alteryx, it was well worth the time invested as we will be able to utilize it for any other locations. We have gained incredible efficiency in acquiring insight on this standardization project using Alteryx. Another benefit we have seen in Alteryx is the flexibility to make minor changes to our workflow which has helped us easily customize for different locations. All of the various Alteryx tools have made it possible for the Tax Business Intelligence team to assist the Tax Department in accomplishing large data discovery projects such as this.

 

Further, we have begun creating an Alteryx app that can be run by anyone in our Tax Department. This frees up the Tax Business Intelligence team to work on other important projects that are high priority.

A common benefit theme amongst Alteryx users is that Alteryx workflows save companies large amounts of time in data manipulation and organization. Moreover, Alteryx has made it possible (where it is impossible in Excel) to handle large and complicated amounts of data and in a very user friendly environment. Alteryx will continue to be a very valuable tool which the Tax Business Intelligence team will use to help transform the Tax department into a more efficient, more powerful, and more unified organization in the coming years.

 

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

We could never have done this data discovery project without using Alteryx.  It was impossible to create any process within Excel given the quantity and complexity of the data.

 

In other projects, we are able to replicate Excel reconciliation processes that are run annually, quarterly, and monthly in Alteryx.  The Alteryx workflows have saved our Tax Department weeks of manual Excel pivot table work.  Time savings on individual projects can range from a few hours to several weeks.

 

What has this time savings allowed you to do?

The time savings has been invaluable.  The Tax Department staff are now able to free themselves of the repetitive tasks in Excel, obtain more accurate results and spend time doing analysis and understanding the results of the data.  The “smarter” time spent to do analyses will help transform the Tax Department with greater opportunities to further add value to the company.