You can’t clean your way out of a bad form

A nonprofit hired us this summer to clean up their registration data, and about a day into the job it was obvious we’d been hired to fix the wrong thing.

They’re a first-responder support organization in the Midwest — small staff, big program, the kind of place where the same three people run the event, answer the phones, and reconcile the spreadsheet afterward. They run retreats. People register, attend, and come back in later years. Somewhere along the way the master list of who had ever attended anything had become untrustworthy, and they wanted it made trustworthy again.

So we looked at it. And the duplicates were real, but they weren’t a database problem. They were an intake problem wearing a database costume.

Here is what the data actually looked like. The same human being appeared several times, once per year they attended, and each appearance was entered slightly differently. A married name one year and a maiden name the next. A nickname on the form and a legal name on the check. A middle initial that showed up in some years and not others. A phone number that changed when they changed carriers. Nothing in the record tied those appearances to each other, because nothing had ever been designed to.

There were free-text fields where a dropdown belonged, so the same retreat was recorded under four different spellings depending on who typed it. There were paper forms, transcribed by whichever volunteer was available, and volunteers are wonderful and inconsistent in exactly the ways you would expect. There were fields that meant one thing in an early year and something else later, because the program changed and the form didn’t.

None of that is a criticism of the organization. It’s what happens to every small nonprofit that survives long enough to have history. Nobody sat down in year one and designed a data model, because in year one there were eleven people and a sign-up sheet and a data model would have been an absurd thing to spend a Saturday on. The sign-up sheet worked. Then it kept working, at increasing cost, for years.

The arithmetic of cleaning versus fixing

We could have cleaned it. That’s a real service, we sell it, and we would have done a good job.

But run the numbers on what cleaning actually buys. You clean the historical data, you hand back a beautiful file, and then the organization runs registration again the same way it always has — and by the following spring the file is dirty again, in precisely the same ways, because the thing generating the mess is still running. Cleanup, in that arrangement, isn’t a project. It’s a subscription. It’s a recurring line item that exists to compensate for a one-time fix nobody made.

The one-time fix is the form.

If registration captures a stable identifier, the duplicates stop being duplicates and start being what they actually are: the same person, returning, which is the single most valuable fact a small nonprofit can know about anyone. If the retreat is a dropdown instead of a text box, there is exactly one spelling forever. If the required fields are required at the moment of entry, nobody is chasing them down in August.

None of this is clever. That’s rather the point. It’s boring, it’s cheap relative to the alternative, and it’s the difference between data you have to fix and data that arrives correct.

And then the form needed somewhere to live

So we rebuilt the registration form. Which meant the form had to go on the website. Which is where the project quietly turned into a different project.

The site couldn’t hold it. Not in a dramatic way — the site was fine, in the sense that it existed and had their information on it. But it had been built as a brochure, at a time when the job of a website was to sit there and be findable, and we were now asking it to be an operational system. Asking a brochure to run your intake is like asking a poster to take a message.

That’s the part I keep thinking about, and it’s the reason I’m writing this instead of just filing it under a job we did.

For a small nonprofit, the website is not marketing. The website is the data collection layer. It is where registrations happen, where donations happen, where applications and volunteer sign-ups and foster inquiries happen. Every number that will ever end up in a board report enters the organization through it. When the site and the data are treated as two separate concerns — two vendors, two budgets, two conversations — the mismatch between them isn’t an accident. It’s structural. The person who built the form never had to report on what the form collected, and the person reporting on it never got to touch the form.

We ended up doing both, because we do both, and I’d love to tell you that was a strategy. It wasn’t. It was a cleanup job that kept pulling the thread until the thread turned out to be the whole sweater.

What to actually do about it

If you run a small organization and any of this sounded familiar, you don’t need to hire anyone to find out how bad it is. Pull up your own registration or donation form and ask three questions.

First: if the same person comes back next year, what field tells you it’s the same person? If the honest answer is “their name, probably,” you have the duplicate problem, whether or not it’s visible yet.

Second: how many fields on this form are free text where the answer is really one of a known set? Every one of those is a spelling variant waiting to happen, and every spelling variant is an hour someone spends in a spreadsheet later.

Third: what does someone on your staff do with this information after it’s submitted? If the answer involves copying it into another system by hand, that step is where your data quality goes to die, and it is almost always the cheapest thing on this list to fix.

You can fix a form in an afternoon. You’ll be cleaning up after a bad one for years.

This piece describes real client work. The organization is not named and identifying details have been generalized, per our Field Notes practice of never naming a client without written permission.