A fast-paced healthcare company engaged Focal Point to assist with automating their various manual processes. The Company’s manual processes presented multiple challenges that resulted in security risks, privacy issues, loss of revenue, increased payroll costs, and unreliable reporting. Using a variety of tools, programming languages, and hosting solutions, Focal Point designed and implemented automated and semi-automated processes (where subjectivity was involved) and helped the Company overcome five significant data quality challenges.
Project Summary
Challenge 1: Decentralized Marketing Function
Since the Marketing department of the Company was decentralized, various groups were creating similar yet different Google forms that fed directly into Google Sheets, (i.e., free text fields). This segregated information required daily manual manipulation to consolidate the data and ensure it was consistent.
Solution
To solve this issue, the Focal Point team created a comprehensive questionnaire webform using PHP and a SQL Database in the cloud (AWS) to store and process the information. Centralizing this form significantly reduced the number of disparate forms across the Company, increasing the Company’s data security and providing consistent data without additional staffing.
Challenge 2: Collecting Data from Massive Network of Data Sources and Endpoints
Over the course of the project, the Focal Point team discovered that a significant portion of the business was collecting and analyzing unstructured data from various sources (e.g., FTP sites).
Solution
Focal Point implemented APIs and other data feeds to automatically obtain PDFs from seven Dropbox accounts, two Box.com accounts, and roughly 100 FTP drives. After obtaining the file structures and storing a local copy, the files were then converted from PDFs to flattened data using advanced scraping techniques. The flattened data was then stored in the Company’s database, which made it easy to mine, analyze, and transport.
Challenge 3: Redundant Data Entry
After further exploration, Focal Point discovered that employees were forced to manually key information from their Google Sheets into a third-party system. This manual process was very labor-intensive and had a significant risk of errors.
Solution
Focal Point leveraged an existing API to automate the data feed. This cleansed the Company’s data and ensured only valid records went through downstream processes. As a result, roughly 10 to 20 employees were able to move their focus and efforts to more important, beneficial tasks.
Challenge 4: Manual Application of Business Rules
During the project, Focal Point discovered that business rules were being applied manually to allocate inventory, which was time consuming and prone to error, despite being a consistent process.
Solution
Focal Point conducted interviews and built a rules engine that applied business rules by customer and then maximized profit based on individual customer prices. The Focal Point team was able to reduce the timeframe of this process from several hours to just five minutes.
Challenge 5: Unobtainable Data Insights
Historical data was so disparate and inconsistent that it was often impossible or unreasonable to assign resources to perform data analysis.
Solution
To address this issue, Focal Point designed and implemented a central data repository to easily query data on-demand and in real-time. In addition, the Company can now query historical data as far as back the oldest records available in the database, which allows the Company to analyze trends and investigate data-related issues within minutes.