(0:00 - 0:08)
Okay, let's dive into this. Think about the last time you moved house. Wasn't sorting out your mailing address just, you know, a bit of a drag.

(0:08 - 0:13)
It often is, yeah. Well, for folks in Van Buren County, Michigan, it used to be a real tangle. Right.

(0:14 - 0:23)
Yeah, I read the report. It was quite something. We're talking phone calls, emails, even, get this, faxes, just to change where your mail goes.

(0:23 - 0:31)
That's right. And what's really interesting when you look back is just how many different ways those requests were coming in. So many channels.

(0:31 - 0:40)
Exactly. You had residents calling the Digital Information Department, the DID, directly. I had emails flying in from local treasurers.

(0:40 - 0:52)
Sometimes with good info, sometimes maybe not so much. Precisely. Plus, spreadsheets, you know, return mail coming back, and yes, those actual paper letters and faxes, it was very fragmented.

(0:53 - 1:00)
And it wasn't just the methods, was it? There are, what, 29 local units in the county, towns, cities? That's right. 29. And the coordination wasn't always there.

(1:00 - 1:12)
Well, not all those local units were getting regular address updates exported from the county's main system. Ah, so you could tell the county, but your local township might still have the old address, or vice versa. Exactly.

(1:12 - 1:20)
You can just imagine the potential for mix-ups and confusion. Yeah. That sounds like a recipe for frustration and inefficiency, I bet.

(1:21 - 1:27)
Oh, definitely. And that leads to a key point about how things were working, or maybe not working so well. The manual work.

(1:27 - 1:41)
Right. With all these different inputs, the DID staff were spending a good chunk of their time just typing, manually entering data. How much time are we talking? The report mentioned it could take up to three minutes per record, just to key it in and consolidate it.

(1:41 - 1:51)
Three minutes doesn't sound like a lot, but... But multiply that by hundreds, maybe thousands of changes a year. It really adds up. That's time they could be using for, well, more impactful work.

(1:51 - 1:58)
Plus the risk of errors, right? Typing things in by hand. Absolutely. Typos, maybe losing a request in the shuffle.

(1:58 - 2:01)
It happens. So, okay. The old way.

(2:02 - 2:10)
Clearly causing some headaches. And this is where it gets interesting. The Digital Information Department, the DID, they decided, okay, we need a better way.

(2:10 - 2:16)
They did. They launched this mailing address change automation project. And it sounds like it's really made a difference.

(2:16 - 2:25)
It has. And what's clever about their solution, I think, is they didn't need to build some huge, complex new software from scratch. No.

(2:26 - 2:33)
What did they use? They used a smart mix of tools that are pretty widely available. Things like JotForm for online forms. Okay.

(2:34 - 2:38)
Familiar with that. Google Sheets for organizing the data and tracking everything. Right.

(2:38 - 2:45)
Spreadsheets. And Google Apps Script to sort of automate the connections and the tasks behind the scenes. Okay.

(2:45 - 2:49)
Apps Script. That's like coding for Google tools. Basically, yeah.

(2:50 - 2:56)
Little bits of code to make things happen automatically. So a centralized digital workflow using these tools. So walk me through it.

(2:56 - 3:09)
How does this new system actually work for, say, a resident or someone in a local office? Okay. So now, instead of phone calls and faxes, you've got online forms. They set up two main JotForms.

(3:09 - 3:16)
Two? Why two? Well, one is designed for the public. Easy to use, clear instructions, maybe some help text built in. Makes sense.

(3:17 - 3:27)
And the other? The other is a more condensed version. It's meant for internal use, like by staff in the local city or township offices who handle these changes often. It's quicker for them.

(3:28 - 3:31)
Gotcha. And these aren't just basic forms, right? You mentioned they're smart. Yeah.

(3:31 - 3:43)
They use things like conditional logic. So the questions you see might change based on your previous answers. Oh, like if you say you're the owner, it asks different questions than if you're just updating the taxpayer address.

(3:43 - 3:47)
Exactly. And they also use field validation. Meaning it checks the format.

(3:47 - 3:55)
Like, is the zip code actually five digits? Precisely. It checks things like that before you can even hit submit. Helps catch errors right at the source.

(3:55 - 3:59)
That alone must save a lot of back and forth. Huge amount. Get the right info the first time.

(4:00 - 4:10)
Okay, so the form is submitted. Then what? Where does the data go? Straight into Google Sheets. They have it set up so public submissions go into one sheet, internal ones into another.

(4:10 - 4:14)
And they can track the status. See if it's been approved. Yep.

(4:14 - 4:21)
There's a column for approval status, tracking notes, all that. Keeps everything organized in one place. Okay, now bring in the app script.

(4:21 - 4:25)
The automation part. Right. This is where the real time-saving kicks in.

(4:25 - 4:38)
The app script does a few key things automatically. Like what? First, it scans a specific Gmail inbox. JotForm sends an email when a request needs approval, right? The script looks for those emails.

(4:39 - 4:51)
When it finds an approval email, it automatically goes into the Google Sheet and updates the status of that specific request to approved. Wow. Okay, so no one has to manually check emails and update the spreadsheet.

(4:51 - 4:53)
Nope. Script handles it. That's clever.

(4:53 - 5:04)
What else? It gets even better. Once requests are approved, the script takes that data, merges it if necessary, and formats it perfectly into CSV files. CSV comma separated values.

(5:05 - 5:12)
Like spreadsheet files. Exactly. But these are formatted specifically to be compatible with the county's main assessment software, BSNA.

(5:12 - 5:17)
Ah. So it creates files ready for direct import? Precisely. Two separate files.

(5:17 - 5:32)
Actually, one for owner changes, one for taxpayer changes, formatted just how BSNA needs them. So before, someone had to manually take all the approved changes, figure out the right format, type it all in, or copy-paste? Correct. Which took time, and again, introduced potential for error.

(5:33 - 5:48)
Now these perfectly formatted CSVs are automatically generated. And sent out how? The script emails the CSV files, weekly, to the designated staff members. So they just get an email, grab the file, and import it into BSNA? Pretty much.

(5:48 - 5:55)
The import itself takes just a couple of minutes now, instead of potentially hours of manual formatting and entry. OK. The process sounds way smoother.

(5:55 - 6:01)
Let's talk numbers. What's the actual impact? This work is really impressive. They estimate saving around 86 staff hours per year.

(6:02 - 6:08)
86 hours! That's what? More than two full work weeks? Yeah. Just on this one process. Think about it.

(6:08 - 6:20)
How does that break down? Where are the savings coming from? Well, remember, the different ways requests came in. Handling a phone address change. That's now saving about 2.5 minutes per call, because it's done via the form.

(6:20 - 6:27)
Dealing with changes from spreadsheets, emails, faxes, mail. That's saving about 1.5 minutes per request now. Right.

(6:27 - 6:35)
And just the task of manually consolidating all that data each week. That time is basically eliminated. It's done by the script.

(6:35 - 6:38)
So, hand-keying data. Gone. Manual formatting.

(6:38 - 6:40)
Gone. Manual consolidation. Gone.

(6:40 - 6:49)
Largely, yes. Huge reduction in those repetitive manual tasks. And it's not just time, is it? What about data quality? That's another major benefit.

(6:50 - 6:54)
The form validation helps get accurate data up front. Right. The zip code check and thing.

(6:54 - 7:02)
And the automated notifications. Because the system can automatically ping all 29 local units when updates happen. Everyone gets the same info at the same time.

(7:03 - 7:09)
Exactly. Or at least, much more consistently. It really cuts down on those mismatches between county and local records.

(7:09 - 7:14)
Better accuracy. Better communication. And here's something important for, you know, anyone listening from another local government.

(7:15 - 7:18)
What's that? This whole setup. Jot form. Sheets.

(7:18 - 7:22)
Apps. Script. It sounds like a reusable pattern, right? Absolutely.

(7:22 - 7:29)
It's a repeatable framework. They built this for address changes, but you could adapt the same model for, I don't know. Name changes.

(7:30 - 7:35)
Permit tracking. Anything involving forms and approvals. Totally.

(7:35 - 7:54)
It provides a template for automating other workflows, without needing massive investment in specialized software. Now, it probably wasn't all smooth sailing, was it? Any challenges? Lessons learned? Oh, sure. The report mentions a few interesting ones, like Jot form itself couldn't directly update the Google Sheet based on an approval action within Jot form.

(7:54 - 8:03)
Ah, so that's why they used the email workaround with apps script scanning Gmail. Exactly. It shows you needed a bit of creativity and flexibility sometimes.

(8:04 - 8:11)
You find workarounds when direct integration isn't there. Good point. What us? They also learned how important it is to plan your output formats really carefully from the beginning.

(8:11 - 8:25)
Because they needed separate CSVs for owners and taxpayers for BSNA. Right. So they had to use some formulas and different views within Google Sheets to filter and categorize the records correctly before the script generated the final files.

(8:25 - 8:32)
Planning that output is key. Makes sense. Any other hurdles? Well, even with automation, you still need some human oversight.

(8:32 - 8:46)
They found that doing a quick manual review during the final BSNA import step was still necessary. Why? To catch duplicates? Yeah, mainly to catch potential duplicates or anything really unusual that the automation might miss. A final sanity check.

(8:46 - 8:54)
Okay. And they actually improved the system based on feedback too, right? They did. Initially, the form only allowed one parcel number per submission.

(8:54 - 9:07)
But people often own multiple properties or manage them. Exactly. So based on user feedback, they enhanced the form to allow someone to submit changes for, I think, up to 10 parcel numbers in one go.

(9:07 - 9:10)
Much more efficient for users. That's great. Listening to the users.

(9:11 - 9:32)
So looking ahead, what's next for this system? They have a few ideas, like making the notifications even smarter. How so? Right now, if a change involves properties in, say, three different townships, maybe all 29 units get a general notification. They're thinking about parsing the parcel numbers to send more targeted alerts only to the specific jurisdictions affected.

(9:33 - 9:35)
Oh, that'd be slick. Less noise for everyone else. Yeah.

(9:36 - 9:47)
And maybe, down the road, exploring potential for more direct integration with BSNA, if that becomes feasible. Bypassing the CSV step entirely? Potentially, yeah. Yeah.

(9:47 - 9:53)
And, of course, using this automation template for other county processes, like we talked about. Building on the success. And ensuring it lasts.

(9:53 - 10:13)
You know, good documentation, backups, making sure the knowledge isn't just with one person. Data resilience. So if we boil it all down, what's the main takeaway here? I think the Van Buren County project is a fantastic example of how applying technology, thoughtfully, even fairly simple tech.

(10:14 - 10:24)
Like forms and spreadsheets? Right. How that can streamline a common, sometimes annoying process. It leads to real savings in time, boosts accuracy, and just provides better service.

(10:24 - 10:28)
Yeah. It wasn't just tinkering. It was about freeing up valuable staff time for more complex work.

(10:29 - 10:38)
Exactly. And creating a system that's more reliable and efficient for everyone, residents and the government staff. So the final thought, maybe for you listening, especially if you work in local government.

(10:39 - 11:02)
Where are the address change type processes in your world? What seems simple, but eats up a surprising amount of manual effort? That's the key question, isn't it? Where could a similar approach, using accessible tools, make a real difference in efficiency and service in your community? Definitely something to think about. And if you want the nitty-gritty details. Yeah, the full report from the Van Buren County Digital Information Department is available.

(11:03 - 11:07)
It goes much deeper into their methodology and results. Could spark some ideas. Absolutely.

(11:08 - 11:12)
A great example of how a focused automation project can really pay off.