I received a call from a municipality that has a dilemma with how they could enforce an ordinance. The ordinance is clear; when a property is for sale, it must go through an inspection.
The owner must complete an application and pay a fee. An inspector comes to the property and checks for violations. A report is then sent back to the property owner with a list of the issues found. The owner can address the violations or discuss with a buyer. It is often a way to discuss price and lower it as a method to not do the work. If the owner decides to fix the violations, a final inspection is the next step in the process. A certificate of completion lets the owner and buyer know the property meets a standard. The law deals with items found during an inspection that are for the safety of the people who live there. From the client perspective, the process is simple and manages itself. It brings in money and pays for the service. It is a simple in-and-out process in government.
When legislation passes, administrative and professional staff are responsible implementation. The problem is how does the municipality make sure people are following the law as written? They rely on property owners and realtors to self-regulate. In fairness, reliance on people to be honest and that causes its own brand of problems.
When I heard more about this problem, I decided to research more in detail. I wanted to provide a resolution for the issue and the logic seemed simple. Here are some questions I raised during an interview with my client:
- How many properties are currently for sale in your jurisdiction?
- How many of the listed properties had a current application for inspection?
- What is the lost revenue?
- What is the best method to communicate with the property owner/listing agent?
The number of currently listed properties for sale in the township is 58. I sat with my client to create a report from their system to show what properties have inspections. The report needed a cleaning due to how the system processes information. Once the file was ready, a comparison showed that 8 properties were in compliance. The remaining 50 properties did not have any application or inspection on record.
At a $100 per inspection, the lost revenue is $5,000. It is not a large amount of money, but, when the annual number becomes $60,000, the problem needs a solution. Depending on salary scales, the number could pay for an employee and ask for money to cover costs. We came up with a friendly letter sent to property owners and realtors explaining the law. There was no threat or penalty in the letter. It came down to gentle words with a call for action. The people realized an error occurred and immediately applied for inspections.
A Technical Solution:
The first step in the process to gather the information required a great deal of manual effort. The real estate listings required copying and pasting into a spreadsheet. The client did not have a clear understanding of how to remove unnecessary records. We went through records on two different spreadsheets to review non-compliant properties. The number was small and did not take a large amount of time. I was able to confirm the results with the criteria provided by my client. The list showed what properties complied and those that did not comply. I then started to write the program to automate the process using the Python language.
The Power of Python:
Once the process and analysis confirmed my concerns, I began to write a script in Python. It has the ability to use packages that analyze, visualize, and compile data. Python is very powerful in web scraping and results can help solve complex problems. The trick is to understand the data and what story it tells. There are many times where seeing data and knowing what it means is a major issue. If the person understands what is being shown, then the problem is solvable in most cases.
The Python Scripts:
The first script written used two Python packages; Beautiful Soup and Selenium. The script visits the website where a listing of houses are for sale or apartments are for rent. Within a page, the process begins by collecting information for each property. The script then goes into each individual listing for more information. The scraping routine collects every piece of the detailed listing. For each property, the data collected gets placed into a file. The entire process takes about 20 minutes to finish. The amount of data is helpful in providing useful analysis not for this project but for others as well.
The next step in the process filtered out records that were not for inspections before a sale. There is a consistent value that prevented any type of data issues and the filter did not change. I wrote some more code to help with other potential changes in the data to be safe.
The next step in the process required careful thought and consideration. The listing service lacks consistent data validation rules, allowing for variations on addresses. To help with the data issues, a script using the Fuzzywuzzy package cleaned most of the records. The package allows for matching of addresses that are not exact. Differences such as North instead of N, created challenges and required extra thinking. matching criteria with refined logic. In the graph, it shows how many properties had a current inspection compared to those that did not.
DELIVERING THE MAIL:
The municipality does not have any formal permission to send out electronic communications. A mail merge prepared the letters sent out to the property owners and listing agents. The file created by the process imports into Microsoft Word and prints on letterhead. A staff member then prepares the letters and includes an inspection application. The letters and application then go into the mail for delivery. Additionally, a table stores all the letters to avoid duplicate notices. This step prevents people from being irate. A second phase is in the works to improve the process with hope to obtaining consent for emails.
In conclusion, the problem presented to me did not seem trivial. A meeting with my client and discussing the situation in detail was an advantage. The resolution produced the desired results. Now, the process is efficient and run on a daily basis to ensure compliance of the ordinance. As a consultant who understands both sides of the problem, I am able to listen and present solutions. We are tracking the success of the process. It will ensure application fees remain consistent. I can predict that property owners will send the applications along with payment. In theory, the process will pay for itself as the revenues will meet budget projections. With a more efficient process to scan local listing services, it creates less work for staff.
The list of ordinances requiring oversight is cumbersome in nature. By using technology to improve a process, the possibilities are endless. Researching a problem and using programming to solve it is significant. The revenue found as a result of improving the process is a major benefit. It requires a unique individual who understands many disciplines, including government and technology. It is easy to compare two lists in an Excel spreadsheet using staff members to perform the task. The process would last hours out of the day or week. With using Python, the amount of time saved allows staff to work on other important tasks.