The fusionSpan Blog

Dear Betty: How do we clean up dirty data?

Author Image
By Dear Betty |January 31, 2014
How ToMembership

Dear Betty:

Our small-staff Association Management Company (AMC) recently acquired a new client. Which is great, but we have a problem. Their data is a mess! Keeping day-to-day operations running for our new association client is nearly impossible given the dire state of their membership and financial data. Should we try to fix the data as we go, or call a time-out to straighten out the mess? Any tips on how to handle this?


Gentle Reader:

One, congratulations on your new client! Two, you have to fix their data. Your ability to manage your new client successfully is entirely dependent on the quality of the data. If things are a mess, you have no way of knowing with any degree of certainty:

  • How many members you have
  • Who they are
  • Where they are and how to contact them
  • Who has lapsed
  • Who your membership prospects are
  • Who’s paid what to the association
  • What they’re expecting in return
  • Whether or not they received it
  • Whether you’re running in the black or in the red

So how do you fix your new client’s messed up data?

You’ve already taken the critical first step to ensuring data quality: you recognize that you have a serious problem. Next, you need to assign ownership of that problem appropriately. Given your small size, that likely means assigning someone to be the project lead/enforcer, and giving that person the authority she needs to make this happen.

The first thing your data chief must do is figure out the extent and severity of the problem, and set priorities for fixing it (i.e., financial data first, current member data next, etc.). Then she will need to document your data standards, creating the rules about how data will be collected, entered, and maintained. You may already have formal written data standards that you use with your other clients. If not, this provides an excellent opportunity to create them.

Once you know the extent of the problem, set your priorities for straightening it out, and write data standards to hold people accountable to, it’s time to actually fix the data. The old engineering adage applies here: “cheap, fast, good – pick two.” In your case, “good” is non-negotiable, so you have to decide if cheap is more important, in which case the process will be slow, or if fast is more important, in which case the process will be expensive.


You may not reach the same conclusion for all the data areas. For instance, you may decide that you need to fix the finances fast, so you opt to pay for outside CPA help. You may want to tackle current member data next and decide to hire temps or a telemarketing firm to make calls to try to track people down and get their current information. Or you may decide that your staff can handle current member data as an iterative process through normal business operations over the next 18 months. You may decide to just dump lapsed member records that are, say, more than three years old as being not worth trying to fix.

Close the loop

The final step, and perhaps the most important in closing the loop on data quality, is that you need to set up auditing procedures to make sure that your rules are being followed, that new data comes in pristine, and that you’re staying on schedule with the choices you make about cleaning the old data. When your audit finds problems (because it will), fix them immediately and update your procedures or provide additional training as appropriate.

What about you, Gentle Readers? What advice can you share about creating and maintaining data quality in your associations?

Dear Betty
Dear Betty: How do we clean up dirty data?

“Dear Betty” is the association advice columnist alter-ego of Elizabeth Weaver Engel, M.A., CAE, CEO and Chief Strategist of Spark Consulting LLC. Elizabeth has over sixteen years of experience helping associations grow, in membership, marketing, communications, public presence, and especially revenue, which is what Spark is all about. She speaks and writes frequently on a variety of topics in association management. When she's not helping associations grow, Elizabeth loves to dance, listen to live music, cook, garden, and blog about the Philadelphia Eagles.

More posts