Past Analytics Excellence Awards

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

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

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

Visual of Tableau Analytic with associated comments

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

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.  

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.  

Author: Pamela Rooney (@prooney) - Business Analyst

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

Company: Quantum Spatial, Inc.

Business Partner: Slalom Consulting, Matt Ewalt

Awards Category: Best Value Driven with Alteryx

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

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

Awards Category: From Zero to Hero

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

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

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

Describe the problem you needed to solve

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

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

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

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

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.

 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.

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

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

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

Describe the benefits you have achieved

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

The previous solution was slow, cumbersome and static.

The Alteryx solution is responsive, fast and optimized.

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

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

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

Increased Visibility

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

Revenue

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

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

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

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

Author: Bill  Lyons  - Principal Data Scientist

Team Members: Trudy Fuher, Alana Welz, Arlyn Baggot

Company: Educational Media Foundation

Awards Category: Best ‘Alteryx For Good’ Story 

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

Awards Category: Best Use of Alteryx Server for Analytics Deployment

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

Awards Category: Best Use of Alteryx for Spatial Analytics

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

Awards Category: Best Value Driven with Alteryx

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

Awards Category: From Zero to Hero

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

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

Describe the problem you needed to solve

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

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

Describe the working solution

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

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

Figure 1

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

Figure 2

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

Figure 3

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

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

Supporting macros:

An Alteryx Server scheduled app then builds Calgary databases of the IP geolocation data.

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

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.

   

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

   

   

   

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

   

   

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

   

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

   

   

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

   

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

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.