There’s something I’d like to address

There’s something I’d like to address.

Perhaps it’s an envelope.  Or an email.  Either way, addresses can be kind of thorny!

If you are keeping track of people (in a spreadsheet, a database, Mailchimp, you name it!), you are probably also keeping track of addresses.  And address management can have as many pitfalls as Philadelphia streets have potholes – from zip codes that start with zero, to people who have multiple — even seasonal! — addresses, to out of date or generally messy data.  But don’t worry, I’m here to help with some tips and best practices.  This blog post is brought to you by my advice column, Dear Spreadsheet Whisperer, where you can write in to get help with your pressing data questions.  I’ve gotten THREE requests about addresses lately so this must really be a hot button issue!

Separate but Equal

It might seeeeeem like a good idea to smoosh all of the address components together into one column, but I assure you, it’s not going to serve you well in the long run!  For example, what if you have everything except zip code?  It’s ordinarily pretty easy to find zip codes and key them in, but how would you even know without reading every single address?  What if you want to reach out to JUST people in Vermont?  How would you filter the list to display just VT people?  Answer:  with great difficulty!  Unless you have separate columns for each part of the address, that is.  It’s much easier to smoosh them together than un-smoosh (yes, that’s the technical term).

Here’s how I would recommend that you arrange address information:  separate columns for:

• First Name
• Last Name
• Street 1
• Street 2
• Street 3
• City
• State
• Zip
• Country (if applicable)

AND if you store more than one address per person (for example, billing and shipping, or work and home) then… you guessed it … one column each for each address type in your system.  Yeah, it’s a lot to keep track of!

Zip zap zop

Now that you have created a special column for your zip codes, let’s make sure that it is formatted properly.  Excel and GoogleSheets will automatically REMOVE the “leading zero” in zip codes like “08505” (my childhood, NJ zip) because numbers typically don’t start with zeros.  The secret is to tell Excel that the column is “text.”  When Excel is no longer trying to read the 08505 as a number (aka 8505) and instead as a word “08505,” you’ll be ready for smooth sailin.’  To change the column format (in Excel or GoogleSheets), follow these excellent instructions, by my friend and mentor Judi.

“state” of mind #PunIntended

When I give data advice to changemakers, I like to focus on the problems in the “here and now” instead of the hypothetical future.  So when it comes to organizing your address data, I would encourage you to be really practical and think about what you actually DO with your addresses.

• Do you send paper newsletters or fundraising appeals?  If yes, address accuracy is important.
• Do you dispatch volunteers for home visits?  If yes, address accuracy is SUPER IMPORTANT!   You don’t want to send someone to the wrong house!
• Do you use your addresses to “segment” your email list by state?  Accuracy is less important (except for the State column)… but volume is pretty important (so if you have people MISSING address info – they might not make it on to any of your lists).
• Do you plan trips and speaking tours and you want to reach out to people in a reasonable driving distance from your events?  If yes, you probably want to focus on zip codes, since cities or states can be either too big OR too small.  Or, you might want to divide your people into “regions” and use your own classification system to splice and dice them.

Really sit down and think about your goals!  I don’t want any changemakers to waste time collecting and maintaining data that you don’t really need.  On the other hand, I don’t want folks to miss out on an opportunity to reach their base.  Data management is an art AND a science.  Don’t put the cart before the horse.  One step at a time, and you’ll be well on your way to address serenity.

More resources:

• Do you ever want to search for people “near” a zip code or county?  If you’re giving a lecture in Berkeley, searching for California people is too broad, but searching for Berkeley people is too narrow, because certainly your friends would come in from SF or Oakland.  If you’re faced with this dilemma, you might want to search for zip codes or counties near other zip codes or counties – I use MapTechnica for this.  Once I have a list of Counties or Zips, I can filter my spreadsheet down to just those people.
• Do you have some people in the USA and other people abroad?  You might need to read up on storing international addresses – check out this informational blog post on just that topic!
• What about if two people in a household share an address?  Or have a vacation address?  Or if two people in the same household have two different addresses?  You can learn about all of this (and more) in Salesforce NPSP documentation
• Some folks store their contacts in an email platform, like in Mailchimp.  Here’s an excellent resource about things to consider when you’re cleaning up your list.  Just hold your nose during the references to “marketing” and you can pretend it was written for changemakers like us!

Good luck, dear changemakers, on addressing your address woes!  I completely believe in you, and don’t forget, I’m always here to help if you get stuck.  xo