Alteryx Success Stories
Learn how Alteryx customers transform their organizations using data and analytics.STORIES WANTED
Showcase your achievements in the Maveryx Community by submitting a Success Story now!
SUBMISSION INSTRUCTIONSAutomated Reconciliation Tool for End User Billing
2019 Excellence Awards Entry: Automated Reconciliation Tool for End User Billing
Overview of Use Case:
We are a business process outsource company. We provide a variety of products and services including an End User Billing platform for insurance companies. The Billing platform interfaces with many internal and external data sources which distill primarily into two areas, coverage history and Cash Distribution. The Billing system serves many client organizations, each with their own custom business rules in addition to state specific and federal rules as well as variances due to implementation/on-boarding processes. The complexity of data sources (exchanges, clients, end users, groups, etc.), business rules on multiple levels, plus prevalence of manual interventions at implementation as well as for ongoing operations often leads to discrepancies. Add to this the often-rapid change in data sources and rules/regulations from state and federal regulators further increase risk in new development/enhancements in software.
Describe the business challenge or problem you needed to solve:
In simple terms, the Billing system generates billing from coverage history, i.e. the record of what coverage you have and the rates and other attributes. Cash Distribution is the current and historical record of what was billed, adjusted, written off and collected. Issues arise in the regular updating of coverage history due to late arriving rate changes and other things that affect the elements that drive billing. Further adjustments after the initial bill can be done in error or done incorrectly.
Billing operations is regularly challenged to react to discrepancies that are found between coverage history which is considered the source of truth for what should be billed and Cash distribution data which reflects what has happened to date. Retro rate changes and adjustments mis-coded or incorrectly applied through manual operations are some of the events that drive discrepancies. There was a strong belief that we were only seeing the tip of the iceberg of issues. Additionally, once issues are identified then time-consuming research is required to determine the corrective action.
Reporting was requested of IT to assist in identifying known, definable, issues but the cost was too high and the timeline was too distant to be acceptable to the business. The BI team had already proven our ability to rapidly build and deploy solutions with Alteryx so Billing Ops came to asking for a basic solution to address those issues highest on their list of concerns.
Describe your working solution:
One person using Alteryx and collaborating with Billing SMEs for requirements and clarifications built the initial offering that became the "Billing Automated Reconciliation Tool" (BART). The initial offering was built in a couple months working part time with one resource, the IT estimate was 6 months involving multiple resources and was not possible to schedule for a year based on their existing workload pipeline.
The solution has been refined in iterative development sprints and now provides regular and on-demand reporting that identifies nearly a hundred issues comprised of specific conditions and rules for each, and provides recommendation for corrective actions. Some issues are packaged and fed into an interface for automated correction while others are used to expedite the manual process to verify and approve actions.
The solution extracts data primarily from IDAA appliance (massively parallel DB, Netezza from IBM) using SQL. This is initially in two data flows, one blends data from the cash/billing tables (what was billed, adjusted, and collected) and the other from coverage history tables (source of rates and effective dates, etc.) then cleanses and blends the data from both streams and then joins and reconciles the streams and produces reporting from a detail bill period level to high level summaries.
The solution was designed to be configurable by client and run instance. An .xlsx was designed to provide a means for Billing Ops to enter/modify what client they want to report on, what edits they wanted to exercise, time frames, and other considerations.
The volume of data and complexity of processing resulted in very long run times. The initial workflow was divided into three separate flows, the first two extract and blend the two input streams and are scheduled in our custom scheduler to run in parallel to reduce the wall clock time to run to the longest of the two flows. Once both are completed the third flow which joins the streams and does the final edits and reporting runs per the custom scheduler. This technique of breaking up a flow into the components that can run independently saved ~60% of the total run time when it was all in one flow.
One of the things that made Alteryx such a perfect solution, aside from the rapid development it facilitates, is the ability to blend the two major styles of processing data, set at a time processing (SQL) and sequential processing. I grew up in a batch processing environment which was primarily sequential processing. Over time SQL against RDMS’s became the dominant method of processing. Now few people do sequential processing and so don’t even think in such terms. In this case we faced challenges that were not easily and in some cases simply not feasible to solve through SQL. Alteryx allows you to embed SQL, make it dynamic, and to take the result and further process it sequentially. You get the power of both styles in a highly efficient platform. Leveraging tools like the “Sort”, “Muti-Row Tool”, “Cross-Tab”, etc. you can process with a knowledge of before and after relative to the current row of data and you can spin your vertical data into horizontal perspectives easily and quickly.
I have used many tools like assembler, COBOL, Easytrieve, C, Tableau, and brief dalliances with some other tools and none have provided the power and capability in the ETL/Blend and modeling space that Alteryx has. One person did in months what would have been at least 10 times the man-hours using developers leveraging the traditional tools/languages. Additionally, the result would have been much more complex and costly to maintain and expand on.
Describe the benefits you have achieved:
Prior to "BART" the billing ops team was limited to only working the few issues that were spotted incidentally. This was purely reactive, manual and time consuming to research, verify and then define and take action.
Post "BART" we gained an understanding of the magnitude of issues, saw the whole iceberg, quickly had the cases with issues and their specific bill periods problems and remedies identified, some corrective issues were automated, and others accelerated and facilitated to allow large numbers of corrections. We went from reactive and anecdotal to proactive and comprehensive. We made completely manual processes largely automated enabling us to do a volume of work previously not possible.
Additionally I had designed a custom scheduler using a mix of Alteryx and windows command line for automating our body of work. This scheduler is leveraged to automate the execution of the various workflows that comprise the final system. This scheduler combined with the configurability designed into the flows allows Billing Ops to run the system on-demand with various configurations and for selected cases they may want to focus on in addition to the regularly scheduled production runs for an entire client’s portfolio using the standard configuration of edits and periods they defined.
