In the current digital era, businesses are always looking for new methods to leverage data to boost production and efficiency. Local governments can do the same. The data engineering team at the Analytics, Performance, and Innovation (API) office has been working with the Bureau of Administrative Adjudication (BAA) to automate some of their mailing procedures.
What is the BAA?
BAA as part of its position as an administrative case adjudicator is involved in hearing and ruling on administrative matters, for example, fines for failing to comply with the code violation, etc. This means holding fair and impartial hearings, examining evidence, hearing the viewpoints of all parties, and making binding conclusions or orders. Once the orders are made, BAA uses physical mail to contact property owners and alert them of any civil fine or notices related to their non-compliant property code ticket disputes. The issue emerges, though, when a sizable portion of these letters are returned as a result of incorrect address labeling (due to data-system limitations), creating a backlog of awaiting mail that needs to be dispatched.
Prior to this project, the BAA used a manual, step-by-step procedure to confirm the owner(s)' names and mailing address(es). Initially, the BAA checks the Notice of Violation (NOV) ticket, which only includes the names of single property owner. The BAA strives to mail to each owner's mailing address (in cases with multiple owners) to provide proper service to all owners. As a result, BAA manually searches the participants' names and addresses into the city database (AS400) and cross-references them with the ImageMate (Onondaga County Real Property Search). Then, links them to the right infraction property, exposing addresses kept in water billing systems. Additionally, in case the mail is returned, BAA searches West Law, to locate each owner's most current mailing address. Through this scrupulous procedure, the BAA made certain that the most recent postal addresses for property owners were gathered to facilitate efficient mail delivery.
Automating processes
In this blog post, we will delve into technical details on how automating the procedure brings about positive changes and improves the overall efficiency of the BAA’s mailing system. The API team used script consolidation, data cleansing and transformation, data migration to Azure data lake, and extensive process mapping to automate the work.
Step 1: Mapping Data Systems
Our journey began with a thorough mapping of the data systems used in the BAA's mailing process. This first phase made it evident where the data were located and created the groundwork for later developments. Different data systems, including IPS, RPS, and AS400, separately saved the owner names and addresses. The team was able to verify and combine the data into a single source of truth by getting insight into the data systems, which substantially streamlined their procedures.
Step 2: Access Data
To retrieve the required information, the team used SQL Scripts and Python’s pandas library. These tools allowed the team to query data from on-premise databases and Azure Datalake efficiently. By automating the data retrieval process, the BAA could achieve faster, more reliable results.
Step 3: Data Cleaning and Transformation
Raw data seldom comes in a format ready for immediate use. Recognizing this, we decided to clean and transform the retrieved data. For example – by ensuring that the data aligned with the format of the BAA's affidavit of service document, the team reduced inconsistencies, improved accuracy, and prepared the data for further processing. (An affidavit of service is a legal document kept as a record in BAA, confirming that legal papers have been sent to a person or entity involved in a case, ensuring that they are aware of the legal proceedings and have been given the necessary information.)
Step 4: Address Queries
To improve the quality of addressing labels, the BAA searches current LLC and Inc owner address information from the state’s LLC’s website. This program eliminated faulty data, and manual data entry, and enabled the team to provide up-to-date owner names and addresses. By automating this process, the BAA significantly improved accuracy and reduced the potential for errors.
Step 5: Data Migration
We then migrated the resulting data in Azure data lake and stored the curated data in the required format (CSV in our case) to the BAA team. This transformation will enable BAA staff to perform mail merge and print owner names and addresses directly onto mailing labels. The elimination of manual handwriting not only will save time but also ensure consistency and improve the overall presentation of mailed documents.
Step 6: Script Consolidation for Automation
The final step involved consolidating all the developed functions into a single script for automation. By automating the process, the API team established a streamlined workflow that could be run on a scheduled basis. This automated script facilitated the querying and emailing of current BAA referrals to the appropriate party, ensuring a timely and accurate distribution of information.
Results
The API's data engineering project exemplifies the transformative power of automation in resolving manual tasks and addressing backlog issues. By mapping data systems, employing SQL Scripts and pandas, cleaning and transforming data, migrating data to data lakes, and consolidating functions into an automated script, BAA achieved remarkable improvements in their mailing processes.
This implemented automation resulted in an average reduction of approximately 30% in the time required to handle each ticket. Previously, it took approximately 23 minutes per ticket, but now it takes roughly 16 minutes per ticket. This time-saving improvement is equivalent to an estimated annual cost savings of $30,000, considering the volume of open cases we receive on a monthly basis.
It not only saves time but also eliminates backlog issues by ensuring timely processing and distribution of letters. This project highlights how we can find routine processes and leverage data engineering to streamline their operations, improve efficiency, and eliminate bottlenecks.
