Automating Financial Reporting Using Alteryx

Log in to a health insurance carrier’s website. Select data fields and download. Repeat for the next set of data. Combine the downloaded data with policy information. Manipulate, format, and generate a financial report for the client. Repeat for hundreds of clients every month.

This is the process that is performed by financial analysts at an insurance broker. Thankfully, a new process has been introduced to replace this manual, time consuming, and, at times, error-prone process. Alteryx plays a major role in preparing, manipulating, and automating the new financial reporting process.

Alteryx

Alteryx bills itself as a “self-service data analytics platform” on its website. Gartner classifies Alteryx in the “Data Science and Machine Learning Platforms” magic quadrant. Data analytics starts with a tool called Alteryx Designer. 

Designer allows data analysts to quickly build out workflows to read data from spreadsheets and databases. Join, filtering, formula, input/output, and other tools exist to easily manipulate, prep, analyze, and organize the data any way you want. Writing output to a spreadsheet, database, or report is a breeze. You don’t need to be a developer to do this — writing code is not needed. Designer’s intuitive UI makes it easy to come up to speed and pump out meaningful workflows quickly.

Download Data

The process starts with insurance carrier downloads. The downloaded data includes membership, payment, and claims information. This requires a financial analyst to log in to a carrier website, select several applicable options on the webpage, download the data, and convert it to a spreadsheet. The process takes a lot of time and opens the door for errors like selecting an option incorrectly. 

Since carriers do not offer web services to pull down data, browser automation software was leveraged due to the complexity of the download. If APIs were provided by the insurance carriers, this could be automated in Alteryx as well. Once new data is downloaded from the carrier to a location on the network, the workflows go into action.

Stage the Data

The first workflow in the process “stages” the insurance carrier data. This staging workflow has minimal data manipulation: it just reads and persists data to a database in a consistent manner for all carriers and to add meta data. The goal is to put all carrier data into one database table for consumption by the next workflow.

The staging workflow reads spreadsheets from the carrier data downloads. There are many carriers, and each carrier download has distinctly different data schemas. One carrier might name a field differently than another carrier, but the data represents the same information. For example, one carrier might use the term “policy,” but others might use the term “group” or “account” or a combination of the two. Thus, a staging workflow exists for each carrier to handle the uniqueness of data. Ultimately, all carrier data ends up in one database table in a consistent format.

Warehouse the Data

Once the carrier data is persisted in the staging table, a “warehousing” workflow processes the data. This is where the rubber meets the road. This workflow merges policy, carrier, account data from Salesforce with data from the carriers. Calculations are performed for enrollments, claims, payments, fees, rebates, contributions, and stop loss with quality checks along the way. All this data is persisted in a database warehouse that is ultimately consumed by a reporting tool for the financial analysts.

The staging and warehouse workflows work well, but it is a manual process to execute them. The staging workflow requires that the carrier data is in a specific location. The warehouse workflow requires a successful staging workflow. Both can be run within a developer’s local environment using the Alteryx designer. But this requires an Alteryx user to click a button. If Salesforce data is changed or new carrier data is downloaded, we want the warehouse updated automatically.

Automation

The Alteryx Gallery is an enterprise application that allows teams to share and execute workflows, and it provides version control and scheduling. The goal here is to kick off the staging and warehouse workflows in sequence, on a schedule, and without human interaction. 

A third type of workflow is needed to execute the staging and warehouse workflows in sequence. The “runner” workflows live in the gallery and leverage the gallery’s scheduling and version control features. The runner workflows chain the workflows together, track errors and send notifications.

The runner workflows leverage “Crew” Macro runner and log parsing tools. Runner tools execute other workflows and can conditionally run a workflow based on the result of an upstream workflow result. The log parsing tools allow workflow output to be easily parsed and included in logs and email notifications to quickly troubleshoot issues if they occur.

The runner workflows are scheduled in the gallery. The gallery provides a user-friendly UI to set up the schedule and view the results of each runner workflow. 

Another benefit of the gallery is version control. The gallery version control is not as robust as a tool like Git. However, it is more than adequate since the runner workflows do not contain a lot of business logic so changes will be less frequent.

Reporting

The final piece to the financial reporting process is the reporting tool, Qlik. Once the warehouse workflow completes, all the data necessary to generate a report is in the database. The reporting tool accesses this data to create reports. The tool has canned report templates that a financial analyst can generate at the click of a button or can create custom reports from Qlik’s user-friendly UI.

Conclusion

Alteryx provides the features necessary to import, prepare, manipulate, and automate a wide variety of repetitive tasks. This saves time and reduces the chance for error. Now, for one insurance broker and hundreds of clients, reports can be generated more quickly and accurately. 

To learn more about Alteryx, and how Zirous can help with your data engineering and custom software development needs, contact us today.