Overview of Use Case
With Alteryx, the Analytics team at a North American based global airline was able to modernize its audit program, bringing to life an automated solution that saved them over ~$1M annually, and reduced the audit process from 5-7 days to 15 minutes. The airline was able to streamline a myriad of different manual processes involving SQL code, Access databases, Excel spreadsheets, other 3rd party software into a four Alteryx workflows that can be run by anyone on Alteryx Server.
Describe the business challenge or problem you needed to solve
The airline needed to modernize its audit solution and bring home all the different components from SQL to Excel/Access to dependencies on 3rd party systems into one compact, customizable, and cost-efficient solution. The audit rules engine also needed revalidation, along with automation of manual processes that were prone to data-entry errors. To complicate matters, the complex logic previously created was poorly documented and could only be understood by technical experts, meaning any small changes would require significant time and potentially breaking the process. By moving this program onto Alteryx, the airline could automate the entire process with several workflows, while documenting the entire process, and incorporating friendly user interfaces allowing for a customized audit by any novice end-user.
Describe your working solution
Four workflows were built to prepare and cleanse the data, select custom audit features, perform the audit, and create the PDF files. Each workflow represented a part of the business process that was either in SQL code, performed manually on Excel/Access, or required other external intervention. The drag-and-drop Alteryx tools made it easy to replicate functions that would take 5-10 minutes to code and test. The solution used tools from the Preparation, Join, Parse, Transform, In-DB, and reporting suites. Custom macros were built to automate loop processes. Advanced tools included Run Command, Parallel Block Until Done and Dynamic Rename. Interface tools were also used to create an analytics app that would be deployed to the Alteryx Gallery.
- Module 1. Prepare and Cleanse Data
This module import data from multiple databases and flat files and performed data cleansing functions to prepare the datasets to be audited. Basic tools from the Preparation, In-DB, and Parse module were used to build the module. A custom macro was also built to prevent duplicate writing of datasets in case the audit was run twice. Interface tools were used to create a UI that would allow the user to select and import the data sets.
- Module 2. Select Custom Audit Features
This module contained the audit logic used by the airline. The brain engine. A flexible UI was created to allow the end-user to customize their audit according to their needs. Custom macros were also used here.
- Module 3. Perform the Audit
The meat and potatoes. This module applied the business logic on the data sets prepared from Modules 1 and 2 to identify and flag fraudulent transactions for review.
- Module 4. Create the PDFs
Once the audit has been completed, Module 4 would create a batch of PDF reports using the Reporting suite to be sent out to external parties for further processing.
Describe the benefits you have achieved
Using Alteryx, the airline saw incremental increases in cost recoveries, over 2.5x as much as the previous legacy solution. Non-financial benefits include improving the speed of the automation, streaming the entire audit process under four workflows including documentation, and inclusion of user interface controls that allow the end-user to customize their audit. The previous solution required input from a 3rd party which added days to the process.
