Data cleaning is a “contact” sport

Dear Spreadsheet Whisperer,

I inherited a list of 3,500 contacts from the founder of my organization and we need to sort them into categories and upload them back into our database.  Right now, we have 11 “primary” tags and 25+ “additional” tags.  This project has been going on for so long that we have multiple spreadsheet versions that don’t even match anymore and no where to officially add new contacts to the system.  We are all losing patience and getting cross-eyed looking at all of these data points.  Can you help?

Signed, Contact Sport

Dear Contact Sport, 

Bear with me while I employ a sports metaphor (let it be known that I’m no athlete…).  Like contact sports, “Contact Segmentation” has rules to learn and skills to develop… and sometimes it can make you feel sore/grumpy/bruised.  You’re not alone and it’s not your fault that the data have fallen into disrepair.  Changemakers the world round struggle with the same questions that you are facing – and it’s nearly impossible to find resources that aren’t marketing for a database product.

I think there are some things we can do right away to make this project simpler and less painful for you, but at the end of the day, sorting through contacts is no fun, and someone who knows all of the people (often the busiest person!) is just going to have to sit down and do it (isn’t that what chocolate milkshakes are for?!)  It’s not glamorous, but if you do it right the first time and you set up good systems for the future, hopefully it’s a one-time fix.

Rules of the game

K e e p    i t   s i m p l e

Make meaningful categories, attached to action!

Only review each record once

Set an end date and expect unfinished business

Too many categories will make sorting AND using your data a nightmare – so my first recommendation is to KEEP IT SIMPLE.  Dozens upon dozens of tags and categories is enough to make me wake up with a cold sweat.  I am a big proponent of a “Contact Details” section where you write 1-2 sentences about how you met the person and their interests.  Then, your actual categories will be more based on how you will *use* the data (usually to create call lists or email lists, or to invite people to events)… and less based on random facts you know about your people.

Try to answer the question, “what will storing this information allow me to be able to do?”  Do you NEED separate categories for “DNC Women” and “Pantsuit Nation”?  I think that in most cases, when you email one, you’ll want to email both of them.  Do you need separate categories for “donor” and “prospective donor” when you can sort by “total donations to date”?  Plus, who will update someone from prospective to actual donor?  You probably don’t need a separate tag for each conference you attended.  In a list of 3,500 people, any category that doesn’t have at least 20 people should be scrutinized and possibly merged into a more general category.  Any category with 10 or less people should probably be tossed away.  Keeping your categories actionable will make it must easier to clean, maintain and use your contact lists – so that you can connect with the right people at the right time.

If it takes too long to categorize your contacts, by the time you are finished your first pass, the spreadsheet will already be out of date.  I think you should review each person once (and only once!) and then MOVE ON.  There are simply too many people to be a perfectionist.  Lastly, my recommendation is to set an end date (2 weeks?) and whatever is done, is done.  After all, cleaning data is an on-going project, anyway!  First, make it to the finish line.  Then, figure out your training plan for next year’s race.

Skills

I really think that contact segmentation is more a question of strategy than skills.  Sure, being a good dribbler will help you win your soccer game, but good plays are how you ultimately score a goal.  Making dropdown menus and using filters (tutorial links below) will help you quickly run through your data and ensure standardization – but from what I can tell, you’re already on the right track!  #PunIntended

1.  Data validation rules (how to create dropdown menus in Google Sheets)
2.  Format your spreadsheet as a “table” so that you can easily sort and filter
3.  Use filters to help you spot inconsistencies, like typos or spelling mistakes

Contact Sport, I have confidence in you!  Let me know how your data clean-up goes so that I can cheer for you from the sidelines!