IT challenges and solutions for a nonprofit organization providing homeowners with energy efficiency programs to strengthen Chicago neighborhoods.
Nonprofit IT Challenge
The Chicago-based nonprofit organization works with qualifying homeowners to offer free services for more energy savings. The organization’s team works closely with a construction firm and applicants to provide a seamless experience in helping them save on energy costs and provide much-needed help in the scorching hot Chicago summers and bitterly cold winters.
The team uses many Google Sheets spreadsheets to keep track of applications, as well as an outdated Access database to access applications from the past decades. The spreadsheets are sorted by month, which creates 12 new spreadsheets per year, each with several tabs for different statuses of applicants in the pipeline. Since some applicants can stay in the pipeline for longer, it is sometimes necessary to search through the spreadsheets from months or even years ago to work on the correct application and avoid duplicates.
Speaking of duplicates, there is no simple way to verify if the applicant is a duplicate. Since the application process is based on physical addresses, the owner might have moved or simply forgotten if they had applied before, making the deduplicating process time-consuming and never-ending.
The team needs to work on managing the growing number of applications (already in the tens of thousands!) with increasing legwork and snowballing tedious, repetitive tasks.
The biggest issues they struggle with are as follows:
No workflow automation
All applications are manually added, updated, and edited. When an applicant submits a website form, a team member adds information over tens of columns into a spreadsheet for tracking.
No data consistency
All data is added manually, so there is no consistency in notes, key dates (added as text in different formats), and numbers.
No (visual) reporting
Separate spreadsheets make it hard to visualize data for the board of members, so all yearly data had to be painstakingly calculated and combined. Issues with data consistency mean it is impossible to combine data into meaningful reports to help the organization improve.
Difficult collaboration
Spreadsheets can get messy -- important notes can be missed, or applicants may fall through the cracks. Every user has a different working style, which can be difficult to merge into one application.
Repetitive, manual tasks
The team is manually looking up data from the databases of the City of Chicago for each application - for example, the ward number, community area, and building image to establish eligibility. The time spent adds up quickly, removing resources from more complex tasks.
Manual reachouts
Most emails were also manually sent, so every application approval, denial, or status email had to be compiled and sent. With hundreds of applications per month, the workload was just overwhelming.
The Solution
We started by analyzing the existing setup and making notes of issues they are currently experiencing. We discovered areas of improvement for automation, collaboration, and reporting.
The key to automating workflows was Airtable, which the client had heard about and was interested in implementing. Airtable is a low-code platform for building collaborative apps — you can think of it as a simplified spreadsheet or database with built-in automation capabilities.
We started the process by going through the existing Access database and picking out the data that was needed to keep the new Airtable base clean and simplified.
Once the data was sorted, it was time to clean it and make it uniform. This included cleaning notes into separate categories, such as dates, contractor notes, homeowner notes, and more. This allows for quick filtering and sorting based on dates and allows the leadership team to create useful reports.
When all data was clean, it was time to configure necessary automations, such as:
- Application approved/denied emails
- Date reminder and assessment scheduling emails for applicants
- Internal automations for automatic approval/denial
- Pulling data automatically from the different City of Chicago databases
- Feedback email and collection
- Flagging addresses that are already found in the system for easy deduping
Data cleanup
The original Access Database had tens of thousands of lines of data that needed to be updated or removed. Spreadsheets containing data were formatted differently and couldn't be used in reporting or filtering. Information was scattered across folders, spreadsheets, tabs, and database tables.
We cleaned up the data to make it uniform, determined what is needed and what was just extra weight, and combined all necessary files into one central database for easy access and filterability.
Report setup
We set up real-time visual reports that change in real-time to provide a simple, efficient way to keep track of trends in the short- and long term.
Team members are able to create their own easily editable reports to gather data on and make it quickly understandable for board members.
Easy collaboration
Team members use the built-in tagging functionality in Airtable, allowing them to leave notes and get each others' attention on a specific application quickly and easily. All notes stay on the application, so it's easy to track the history of an applicant from one central location.
Any change made on an application is also stored in history, which ensures everyone's on the same page and quick collaboration is effortless.
Automated tasks
Lots of information had to be manually searched and added into spreadsheets before, so we set up an automation that pulls information from the City of Chicago databases instantly and helps save valuable time.
With the new system, it takes seconds for information to populate from different online databases, requiring only oversight from a team member before approving an application for greater control.
Automatic emails
Instead of manually emailing applicants in relation to their application status and next steps, we implemented a set of automations that run only if certain criteria is met. Applicants receive automated emails throughout the workflow, for example:
- After submitting their information
- When any action is taken on their application
- When they are approved or denied
- When any additional info needs to be submitted
These automations help applicants stay in the loop about the status of their applications, while allowing team members to focus on making the process more streamlined and reaching more Chicagoans.
The Results
Better reach
The time left over from focusing on tedious tasks and workflow automation allows team members to spend time more efficiently, reaching more people in need.
Data-driven decisions
Real-time visual reports make it easier to make decisions based on accurate data and provide an overview of work without having to put in hours of work.
Streamlined feedback
Setting up automated feedback emails and questionnaires helps the organization quickly respond to critiques and fix issues as soon as they arise.
Accessibility through the Cloud
Cloud-based access lets team members access vital data from anywhere while keeping the data secure and private.
