How Lewis County Proved Its Grant Office Brings Home Over $5M a Year
We consolidated 30-plus grant-tracking tables into a single Airtable base and built a public Power BI dashboard that shows residents and legislators exactly how much state and federal funding the county secures, and where every dollar goes.
Lewis County’s Planning and Community Development team pulls in state and federal grant money that funds projects across the county: more than $5 million every year from a single team. On paper the department reads like an operating expense. In reality, every dollar it spends multiplies into outside funding for the community. Quantify built the system that finally made that impact visible.
The challenge
The department’s value was hiding in plain sight. Its grant work was buried in more than 30 disconnected tracking tables spread across multiple years and staff members, with no clear way to show legislators or the public the return on the office’s effort.
The team already had strong tables and knew exactly what they wanted to track. But the tracking wasn’t unified across years or team members, it wasn’t visible to the public, and the tables weren’t relational. That made storytelling nearly impossible. The county wasn’t short on data or results. It was short on a way to tell the story.
What we built
We worked closely with the planning team to understand the subject matter and the working tables they already used, then designed a data model, built prototypes, and reviewed them with the subject-matter experts at every step.
We consolidated more than 30 tables down to 7 in a single Airtable base. Applications, grant programs, awards, and the community projects they fund are connected through linked records, so the relationships the old tables could only imply are now built into the system. The base is the team’s single place for data entry and day-to-day grant tracking.
Reporting runs on the county’s existing Microsoft stack. An Azure Data Factory pipeline moves grant data from Airtable into Lewis County’s Azure data warehouse, which feeds a Power BI dashboard for county leadership along with a public-facing view that shows residents how projects get funded by winning state and federal dollars rather than local taxes.
- A public Power BI dashboard mapping funding across 13 municipalities and 5 county departments
- An Airtable grant-tracking base with effort, success-probability, and ROI tracking
- An Azure Data Factory ETL pipeline loading Airtable data into the county’s Azure data warehouse, ready for further analytics
- In-person training and Standard Operating Procedure documentation so the team owns the system
Technical stack: Airtable, Azure Data Factory, Azure data warehouse, and Power BI.
The outcome
Three years of applications, once scattered across 30-plus tables, are now unified in one base with clear award status. The story the planning department always wanted to tell is finally visible to everyone.
A value multiplier, made obvious. The public dashboard shows total funding secured, mapped by location and ranked by project, making it clear that the office returns far more than it costs.
One source of truth, two audiences. Staff work in Airtable, leadership gets the detail it needs to plan, and residents get a plain-language view of how projects get funded.
Data the team can act on. The consolidated system now supports personnel development, project planning, and budgeting, not just reporting.
"They were thoughtful in their process and mapped out every detail, communicating consistently to keep the project progressing. It helped us organize our anticipated and existing grants and use the data for personnel development, project planning, and budgeting. Efficient and rewarding!"
"The efficiencies and data-driven metrics they provide enhance transparency and efficiency, ultimately improving the service delivery of local government."
Interested in results like these? Let’s talk.