The curious case of Kimberly Ellinger

We have a phantom member of our family.

casper-friendly-ghost-3

(not like this, although does anyone else find it creepy than this dead child is following people around in children’s entertainment?)

When we moved into our first house, one of the people who sold us the house was Kimberly something-or-other.  We immediately started getting mail for Kimberly Ellinger – her first name, our last.  Our best guess is that a mailer assumed she got married, or divorced, and changed her last name to the one that was now on the house.

That was 2003.  For us, it was five residences in three states ago.  During that time, no one named Kimberly Ellinger ever sent in a donation, mailed back a comment card, or bought something through the mail.  Because she doesn’t exist.  Never has.

But she apparently loves Land’s End catalogs.  She clearly needs car insurance pretty badly.  And she got her AARP card last week.

AARP is one of the biggest acquisition mailers in the United States with about 170 million pieces going out recruiting new member each and every year.  Part of me felt a little bit good that even an organization like this can fall prey to something that costs non-profits each and every year: bad data.

You may think that the worst thing that bad data can do is run up your costs.  Au contraire, mon petit fromage.*

In 2014, a man received a letter from OfficeMax that was addressed to his name, with the second line of the address reading “Daughter Killed In Car Crash.”

Yes, a year earlier, his daughter had in fact been killed in a car crash.  As someone who has lost a daughter, I can tell you that this type of solicitation does not make me want to buy office products.  It makes me want to find the person responsible and punch them until I run out of fist or they run out of face.

But there probably was no person responsible.  A screw-up that large requires a faulty system to let that letter go through.  And every snowflake in that avalanche pleads not guilty.

While not as drastic, bad data is costing you dearly.  Here are some systems you can set up to make sure that you aren’t sending the next such letter (and, more mundanely, save on the costs of bad data, which include both costs of mailings and the opportunity costs of missed revenues):

  • Enter every bad address – mail and email — in your database as a bounce as soon as you can.  For the mail, this will save on postage; for email, it will help your emails not be seen as spam (and, even better, it will help your emails actually not be spam).
  • Track back these bad addresses to their origin.  If you get a bunch of your acquisition packages back, find out what list(s) the pieces came from.  If it’s one or two, that’s life.  If you are getting a bunch of return to senders back, you can ask for a refund from the list and/or not use that list again.
  • Use change of address tools.  For each mailing, you want to run NCOA to make sure mailings get where they are supposed to go (and to remove non-standard address two lines like the above horrific story).  For emailings, an eCOA can help you find new addresses from those that you’ve lost touch with.
  • Search your database for common swear and hate words.  It may shock you but not everyone on the Internet is a mature, upstanding member of society.  I have seen things in online databases in particular (which are usually user entered) that would make longshoreman blush.  Most of these are attempts to see more adult versions of “Dear Mr. Poopyfacehead” in print that I would describe as adolescent except for my desire not to besmirch adolescents.  However, some may put real people’s addresses on with these abhorrent names and woe be unto you if you mail them as such.
  • Similarly, look for ZIP codes of 11111, 12345, 0, and the like, as well as popular phone numbers like 123-456-7890 or 111-111-1111 and emails like xxx@xxx.com (that’s how ingrained it is to put .com at the end of email addresses).  Delete them.
  • Once you’ve taken out the bad data, delete accounts that don’t have the bare minimum necessary for inclusion, which should likely be a physical address, a phone number, or an email address.  I once saw a mailing addressed only to “Roy.”  Nothing else.  My guess is Roy wasn’t going to get it.

Finally, at a certain point, give up.  If Kimberly Ellinger hasn’t answered your first 50 mailings, number 51 probably isn’t going to sway her.  Also, when you do, do the industry a favor and mark them as “do not rent.”  Since you are going to be going after the people who provide you with bad names, it’s best not to become one yourself.
* This may surprise you, but I don’t actually know French.

The curious case of Kimberly Ellinger

Welcome step four: Setting up your systems

So you have your plan for your welcome series.  It is somewhere between 1 and n number of communications, depending on the person.  It crosses media where possible.  It thanks, learns, teaches, and asks.  And it honors the gift the person has given, while letting them know they can still be a bigger part of the change they seek to make in the world.

And it is worth nothing unless it is written down.

eyrha

You are working with a process that likely has:

  • At least three media (email, mail, phone) and perhaps more (mobile/texting, addressable ads, video, events)
  • Multiple vendors/systems involved (including caging, database, mail house, telemarketers, online communication systems)
  • Multiple points of differentiation, including medium, message, and high-touch v low-touch
  • Multiple people at your organization (you, donor relations staff, executives)
  • Intricate timelines.  For example, if you have three communications that you want in the order of thank you, learning about you/you learning about us, and ask, you really, really don’t want them to happen in the reverse order.  Also, every time you suppress someone from anything, things get complex.

This is not something that can be informal. In order for these systems to work together, you need to write out how.

Which does not mean you should write it in stone.  The basic principles should be (thank as quickly as possible, customize communications to the person receiving them, include both gratitude for what the person has done and opportunity to do more).  But how you accomplish them should be fluid with your testing regime.

I would say that the easiest way to create your process is to start with the simplest case and work your way up.  In this case, we have the rare example where ontogeny recapitulates phylogeny*.  So here’s how to build your program and your welcome/acknowledgment flowchart.

  1. Start with the most basic thank you by medium.  For an online gift, this is an email thank you; for an offline one, it is a mailed thank you.  Figure out how to get it out as soon as possible. 
  2. Customize those communications to the reason for giving.  A simple way to do this is to have a paragraph in the communication that changes based on the appeal to which the person gave their initial gift (since that’s usually the only information you have about the person at this point). 
  3. Create a special high-touch way to reach out to higher-potential-value donors.  This could be a policy of thanking all sustaining donors by phone or thanking all $100+ new donors and $1000+ existing donors with a handwritten card — whatever you are capable of doing.  This should be added to, not in place of, other thank yous. 
  4. Explore ways to break down your acknowledgement silos and thank people in different ways.  Put those that work into your process. 
  5. Add in a customized ask.  Yes, we’ve gotten this far before adding in an ask.  My thought is that a well-thanked donor is more likely to give to a regular-communication-stream ask than a poorly thanked one is to give to a specialized communication.  Also, you’ll note that this comes before creating a gap in communications (there likely already is one  that you can take advantage of) or learning about/educating your donor (I would rather have a less-educated donor who makes a second gift than a more-educated one who hasn’t).  This ask will be a bit more generic than we would like at this point, but you crawl before you walk. 
  6. Create your communication(s) to learn about your constituents.  These will usually be, but don’t necessarily have to be, separate communications from your acknowledgement and/or ask. 
  7. Create your if/then tree for customization from these learning communications.  That is, you should have something that says “if they are interested in advocacy, send them X paragraph in the ask; if they are interested in conservation, send them Y; if we don’t know, send them Z.” 
  8. Create the systems by which these changes will be implemented both for the ask as part of the welcome series and for all future communications. 
  9. Add communications from other media to the mix. 
  10. Create your timing for all of these communications, expressed in number of dates from the receipt of the gift.  I would encourage you to do a range, rather than an exact date for these communications — you may want to avoid having people telemarketed to on Christmas or on Sundays, for example.

Then, test the everlovin’ crud out of the system.  You are looking to break your system and then make it stronger at the broken places.  Some common things to test:

  • Do you have a plan, and only one plan, for every giving amount?  I’ve seen plans that say that donors over $100 get this communication and donors under $100 get this other communication.  They forget that a computer is going to be looking at this and ignoring people who give exactly $100. 
  • Do you have a plan for defaults?  Remember in most cases, you are not going to have additional information from the donor when you make your welcome series ask.  You want to make sure there isn’t a big blank space where paragraph three should be. 
  • What happens to your system if someone miskeys a code? 
  • What does your flowchart look like if someone does everything?  That is, you have paragraphs for people who are interested in various particular diseases, want to do advocacy, or have a personal connection to the mission.  What if they are all three, and they are a high-dollar donor?  The goal here would be to make sure you have prioritization and that you are not inundated with communications.  Remember that one of the priorities with your welcome series is to help the donor understand what to expect from you.  This should not be “I will expect to be annoyed.” 
  • How do your dates line up?  If you are integrating multiple messages and channels, you want to make sure that a person doesn’t get a phone call, mail piece, and email all exactly 21 days after their gift. 
  • How are you going to be able to handle the load?  That is, if you are going to be sending a getting to know you email seven days after the gift, will you be able to handle that on January 7th given your December 31st volume?  What?  You don’t have huge December 31st volume?  Let’s do a week on year-end fundraising at some point.

And you want to be vigilant to potential leaks even when you have this written down.  I have the privilege of working with a great donor relations person who keeps me apprised of the tone, tenor, and quantity of calls we get.  From this, she was able to discern that we were getting people calling (from a pattern of three people — like I said, she’s good) that already existing donors were getting member cards, something we include in our getting-to-know-you section of our welcome series.

What had happened was that our caging vendor had had instructions to send the new donor welcome letter to people who came in from acquisition mailings.  Since acquisition mailings often have lapsed donors in them that you are looking to reacquire, there were people who had donated for over 20 years who were being treated as if they were brand new to the organization.

Sovigilance

Image credit.

I will say that I have entirely failed to set up a welcome series for the weekly newsletter companion to this blog.  If you were to sign up for the newsletter here, what would you like to see?  Email me at nick@directtodonor.com; I’d love to use this as a test case.


* “Ontogeny recapitulates phylogeny” is the (mistaken) idea that we go through all of the stages of our biological evolution in development of our embryos.  The catchiest treatment of this I’ve seen is in Stephen Jay Gould’s I Have Landed, where he talks about how the drawings that supposedly prove this theory (like the allegation that we have gill slits at a point in the womb) persist in science textbooks.  It’s also the theme of his first book, which is a technical book and thus one I haven’t read.  In any case, I doubt his first book has any essays on Gilbert and Sullivan, which I Have Landed does, so that’s another point for the latter work.

Welcome step four: Setting up your systems

Getting to the Truth of one database

the-one-ring

One Database to rule them all.
One Database to find them.
One Database to bring them all
And in the darkness bind them.*

A beloved former boss of mine once asked the best question I’ve even heard and may ever hear about databases: “Which database is going to be the Truth?”

Others may call this the database of record, but the Truth is far more evocative.  It encompasses “which database will have all of our people?”, “which database will have all of our donations regardless of source?”, and “which database will be the arbiter and tie-breaker for all constituent record issues?”

This is a necessary pre-condition of donor knowledge.  You will not have true knowledge of a constituent of all of your data isn’t all in one place.  And working on donor information without the backend systems to back it up could be a waste of time and effort.

If you are like most nonprofits, you are either laughing or crying at the discussion of one database.  You likely have a few different donor databases by donation type.  Then you have records of people you serve, your email list, your event attendees, and so on.

And, sadly, some of them are necessary.  Some databases do things that other databases will not do.  You may not be able to run your direct mail program out of your online database or vice versa.

So here are some steps you can take to get all of your information in one Truth even if there are multiple databases behind it:

Purge unnecessary databases.  And I mean purge them. Ideally it should be as if your unnecessary database displeased Stalin: it just disappears from history, incorporated into other people’s stories.  To do that:

  • Ask whether another database can do what this database does.  If so, bring the data over and train the relevant parties.  The good news is that often the rogue database in question is just an Excel spreadsheet that can be directly imported into your database of choice.
  • Ask whether another database can do what this database does with modifications.  Rarely is something perfect initially.  You will likely have to create reports for people that they are used to running, but if you are bringing them into a good database, that’s a matter of business rules and set-up, rather than technical fixes.
  • If not, ask if the person can do without what the database can’t do.  You’d be surprised how many things are done because they have been done rather than for any rational reason.

Assuming that you have some databases that can’t be replicated in one big happy database, decide what database is going to be the Truth.  This should have the capacity to store all of your fields, run reports, and do basic data entry.  If you are keeping your direct marketing database, it doesn’t need to be able to run a direct marketing program.  But it does need to have the capacity to do the basic functions.

You may say that you don’t have a database that can fulfill this function.  In that case, I would recommend what I call a Traffic Cop database.  This is a database that you can inexpensively put in the center of multiple databases and get data to and from the other databases.  It’s job is to make sure every database knows what every other database is doing and existing to pull out duplicates and host change management.

Now, sync the databases to the Truth database.  Sometimes you may be fortunate and be using a database that has existing linkages.  For example, if you have decided that SalesForce is going to be your Truth, there are some pre-existing syncs you can get from their apps.  If not:

  • Start by syncing manually.  That is, export a report from one database and import it into the other.  Then, reverse (if you keeping a database, syncing it has to go both ways).  This will allow you to figure out what fields go where and more importantly how to translate from one database to the other (e.g., some databases want the date to be formatted 01/18/2016 and woe be unto you if you forget the zero before the one; others may not having a leading zero or have month and date as separate fields or the like).
  • After you have your process down, you can automate.  This can happen one of two ways: through the database’s APIs or through an automated report from one database that uploads to a location followed by an automated import from the other database.  Both are viable solutions — you would generally prefer the API solution, but you do what you have to do.
  • Make sure you have an effective deduplication process.  It almost goes without saying (and if it doesn’t, check out our PSA for data hygiene here), but data can get messy quickly if you don’t have these in place.

Here are some of those common objections and the easiest replies:

  • Cost: “how can we afford to take on a database project?”  Answer: how can we afford not to?  The lost donations from people calling you up asking for a refund and you have to look through five different databases to see where they donate.  The extra time to try to reconcile your donor database and financial systems.  The data that you won’t be able to get or use for your direct marketing and the lost revenues from that.
  • No direct marketing constituents: “I don’t want X (usually the people we serve) to get hit up for donations.”  Answer: We won’t be able to guarantee they won’t get a solicitation unless we know who they are.  We rent acquisition lists all the time and these people could be on there.
  • We’ve already invested in this other database: Answer: point them to this Wikipedia page.  It’s easier than trying to explain sunk costs on your own.
  • Provincialism: “We have database X and it works fine for us.” Answer: actually there are three answers for this one.  First, start elsewhere.  Usually, someone will have a database that isn’t working for them and better you start with them, who will then start singing the praises of both you and the Truth, than with the people who like where they are currently.  Second, usually, there is an “I wish we could do X” list somewhere that will make it worth this person’s time to switch.  Third, go to the highers-up with your business case.  By this time, you hopefully have some happy converters and some results from your direct marketing program (e.g., “we can put the year someone started with us on their member card now!”) to share.

Hopefully, this helps you get to your own version of the Truth.  Now that you have it, let’s talk about what to put in there.  That’s our charter for the rest of the week.

* Since we started with Game of Thrones yesterday, we have to do Lord of the Rings today…

Getting to the Truth of one database

Acquire your own online donors

I know, it’s an odd title.  But every year, 30% of your online constituents go away not because they aren’t interested in you anymore, but because they changed their online contact information.  Seventeen percent change after six months.  That means that the half-life of your list is less than two years.

halffull.jpgIt doesn’t matter if it’s half full or half empty, just that it’s half of what it was.

This is going to be online acquisition week and I’ll go through a lot of tactics for bringing folks into your organization online.  But because of this, the best and the easiest way to acquire new online constituents is not to lose the old ones.  Conversely, it doesn’t make sense to start acquiring new online people if you aren’t ready to keep them.

So we’re going to go back to that old saw: data hygiene.  If you don’t believe in the necessity of data hygiene, then there’s a PSA you need to read.  We’ll look at the online specific ways to keep your data clean.

Scrubbing user-entered data.  Users will misspell their own names, then blame you when you address them by that wrong name.  Email addresses are no better, but those at least have a standard format that you can check.  Hopefully, you have an email validator on your forms, but you probably have data that predates your validation.  Some things to look for:

  • Does the email address end with a top-level domain (e.g., .com, .gov, .net, .org, .edu, .mil, or a country code)? Many an email has bounced back because it went to aol.con.
  • Does the email address have an @? If the email has a ! or # in it, chances are that, because these are right next to the @ symbol, @ is what was intended.
  • Does it bounce? That is, when you send the email, does it come back to you?  This is one reason that it’s important to send your email from a real email address – so you can change bounced emails or mark them for further work.  (Also, a real email address will help with charges that you are spamming people).

Put an email validator on your forms if you haven’t already.  Just to make that perfectly clear.

Run an ECOA service.  ECOA, or electronic change of address, is a service that functions a bit similarly to the national change of address (NCOA) registered with the USPS.  These services look at innumerable services across the Web to determine where a person might have gone.  You should ideally run both bad email addresses and email addresses that have not opened an email in a certain amount of time (say, six months).  If they haven’t opened an email in six months and they have a good email address…

Suppress chronic nonresponders from most emails.  If people aren’t opening your emails continually, they won’t miss you not sending emails (and, when you do send a very occasional email, it will be a bit more of a surprise).  And they won’t drag down your open rate or mark your emails as spam, making you more likely to survive email providers spam filters.

E-append your file.  Take your offline donor file and give it to an e-append service; they will return email addresses of people who would probably like to interact with you online, but haven’t yet given you their email address.  This also works like ECOA for bounced and non-opening addresses.

This isn’t going to stop your attrition from bad email addresses.  But it will help you hold on a little longer to the people who want to hear from you.

Acquire your own online donors

Appending data in Excel

One of the primary reasons to have a database is to have data in various tables.  A simple example is a database with a people table – a list of your donors – and a donations table.  This allows for there to be a one to many relationship between the donors and donations.  After all, a good donor will generally donate multiple times.  By using a key in the people table, each donation is marked with that same key – e.g., all donations from donor 12398 have 12398 in their donor field and are associated with that record in the people table.

Excel can’t do this.  As a flat file, all you can represent are one-to-one relationships.  If you had to have multiple gifts in Excel, you would have duplicate donor records for each instance of the gift.

But you can append data to a record in Excel.

A while back, I wanted to do some homemade modeling on a file to add sex in as a variable (male/female in this case, to simplify the model).  Unfortunately, I didn’t have a list of who was male or female in this particular file, so I used US census records to determine how many males and females there were with a given first name.  This led to the odd conclusion that someone who had a name that 60 females and 40 males have in the US is a 60% woman.  Thank goodness I was using this for modeling and not for assigning Mr. or Ms.

By the way, if you were wondering if there were any perfect half-female, half-male names, there were three: Ariel, Hong, and Kris.  Sydney, Robbie, Frankie, and Kerry were almost androgynous, just missing the cut.  The Excel sheet is here if you are curious.

To complete the model, I had to put the percentage of femaleness with each record.  I used a somewhat fiddly function called VLOOKUP.

VLOOKUP tells Excel to look through a bunch of rows to find a particular value in the left column of an array, find it, and return another column in that array.  To wit:

=VLOOKUP(A1, ‘[Male v Female first names.xlsx]Sheet1’!$A:$B,2,FALSE)

This assumes that A1 is the first name you want to look up.  This then goes over to the document called Male v Female first names and looks at the first two columns, A & B.  It searches for the name in column A.  If it finds it, it returns back the value in B next to that value.  So, if A1 is Hong, it finds Hong in the array, sees the value next to that is 50%, and puts it in the cell in question.  The FALSE at the end just means you want an exact match, rather than the closest one.

It is by no means as pretty or efficient as a SQL JOIN statement.  In fact, if you have a lot of these to do, as I did, copy and paste it for the full column, then get yourself some tea and a crossword puzzle while Excel labors like a hamster on a wheel.  But, on the plus side, you don’t have to learn SQL.  You still should, and we’ll cover SQL for direct marketers at some point, but you can’t learn everything immediately.

Appending data in Excel

A brief instructional video about the benefits of data hygiene

(Billy is walking to school.  He is in black and white, but mostly white, between is grey blond hair, grey pale skin, grey khaki pants, and white white button up shirt.  Only his shoes show black – certainly not his small town.)

Narrator: Woah.  Hold on there, Billy.

(Billy stops and looks for the voice).

Narrator: Where you going in such a hurry?

(Billy holds up the books he is carrying and taps on his watch with a finger)

Narrator: Ah, going to school.  And I suppose you haven’t even given a thought to DATA HYGIENE?

(Billy pauses, has a quizzical look, and proceeds to sniff his armpits.)

Narrator: No, not that type of hygiene.  DATA hygiene.  One of the things that makes America great (cut to stock footage of a flag waving and other good and wholesome things) is Americans doing positive, American things for other Americans, helping our neighbors, our pets, and our environment.  For example, elderly people! Puppies! Parks!  All of this is possible because of our positive, American civic associations of good people.

(Cut back to Billy nodding enthusiastically.  If Billy were tragically stuck inside a paper bag, he could not act his way out)

Narrator: But what if ALL OF THIS CAME TO AN END?  Were there no data hygiene, our civic associations won’t be able to help preserve our positive American way of life?  They wouldn’t be able to reach new volunteers, get voluntary donations, or help out in your neighborhood.

(The previous images of an elderly person, a puppy, and a park, all disappear in rapid succession)

Narrator: THIS WOULD LEAD TO COMMUNISM!

Billy: Holy shit!

Narrator: Wait, you can talk? I thought we had you muted.  Why didn’t you talk before?

(Billy shrugs.)

Narrator: There are two types of data: data that you control and user-entered data.  Let’s take a tour of data you control.

(Billy is whisked away to an average city street)

Narrator: Here is the hustle and bustle of a nonprofit organization’s traffic.  All sorts of data come through here on a daily basis, from caging vendors to development officers to syncs with other databases.  And we can control these with some basic community standards.  But you have to make sure these community standards are followed, from address 1 line standards…

(close-up on the street sign, which magically changes from Mockingbird Lane to Mockingbird Ln)

…to address line 2 standards…

(close-up on an apartment, labeled as 123 Main Street, Apartment 3-B, which changes to
123 Main St
Apt 3B)

…to pick lists that dictate what titles and suffixes can be used.

(A mailbox that says Reverend Timothy Lovejoy, III, changes to Rev Timothy Lovejoy III).

Narrator: It’s better that these things are cleaned up in the database…

(Billy opens a door and see workers in a dank, grey office working on various spreadsheets.  A supervisor is whipping them if they don’t clean lists fast enough.  He recoils in horror.)

So that hard-working Americans don’t have to fix them later.  Fortunately, you have professionals doing data entry here, so that should limit duplication and errors.  The same can’t be said for…

(Click. Billy is instantly transported to a seedy red light district.  Crude graffiti adorn the spare brick walls and trash lays, some in bags, some not along the street).

Narrator: USER GENERATED DATA!

(A scantily clad woman of the night beckons to Billy.  He starts to walk over)

Narrator: Billy! What do you think you are doing?

(Billy stops and gives the camera a sheepish shrug).

Narrator: Here in user-generated data, anything goes.  In order to get people to fill out forms, you have to make it very easy.  Most of the time, that means leaving out items on the form…

(a metal mailbox is embedded in the wall.  It says simply “Jim”.)

… not validating standard fields or following capitalization rules…

(the neighbor’s mailbox is labeled “mt. cHris hartnet, mbs”

… having people put data wherever…

(the camera pans up to reveal a steel mill in the background labeled “Dr. Alice Humphries, DDS”)

… and just plain having jokers plant fake information.

(the building next to the mill is labeled “I. P. Freely.”  Billy looks frightened.)

Narrator: Yes, this lack of cleanliness is the price you pay for freedom.  American freedom!  But we all have to do our part, or else the civic organizations won’t be able to reach anyone in this neighborhood.  And you know what that means, don’t you Billy?

(Billy nods and mouths the word “communism”)

Narrator: That’s right.  Here we have to use more broad hygiene techniques.  Enforce your capitalization rules, not theirs.  Every one of these houses should be run for address standardization, so the addresses are correct.  They should also go through strict National Change of Address forms, so you know when someone has moved.  If someone’s email address doesn’t work, make sure to suppress it or you will be labelled a spammer.  And you know what happens to spammers.

(The camera shows a man in a stockade.  Residents are throwing cans of Spam at him.)

Narrator: Appending data like telephone numbers, addresses, and email address when you don’t have them is also usually worth the investment.  That way, you know if Jim back there is the same Jim who just wrote your nonprofit a big check.

(Click. Billy is back on his school grounds.)

Narrator: So, Billy, you know the importance of data hygiene, right?

(Billy nods enthusiastically and runs off camera)

Narrator: Data hygiene: it’s the right thing to do.  America.

A brief instructional video about the benefits of data hygiene

Deduplication 2: Deduplicate harder

The deduplication exercise from yesterday talked about how to deduplicate clean, easy-to-use data.  If you ever find data like that, please let me know.

In reality, there are always quirks in the data to work around.  Here’s how to solve a few of them.

The middle name conundrum.  There is no good way to deal with middle names in a database, because the way people use them is non-standard.  For some people, a middle name is never used and sometimes a throwback to a bygone ancestor (e.g. ,Ronald Bilius Weasley).  For some, it is an integral part of the first name (e.g. Mary Kate and Ashley Olson; that is just one long name, right?).  Others skip right over the first name and go right to the middle (e.g., F. Murray Abraham).  This is why a good database will have a salutations field you can fill in, so your letters go to Ron, Mary Kate, and Murray, and not Ronald, Mary, and F.

This, however, makes deduplication a bear.  You want to preserve the original first name, but need to manipulate the data:

  1. Insert two columns next to your first name row (more if you think people will have three or more names in the first name field)
  2. Copy (Ctrl + C) the first name column into the first empty column.
  3. Highlight the column and go to Data > Text to Columns
  4. Choose Delimited and hit Next
  5. Choose Space and hit Finish

This separates the names into separate columns.  Dedup as normal based on these fake name columns, then delete these columns and you have clean(er) data.

Forms of names. An automated system won’t be able to tell you that Robert and Bob are the same person, but you can figure out close matches of names like Robert and Rob.  The trick here is when you deduplicate for first name to put

=LEFT(A2,3)=LEFT(A3,3)

Instead of

=A2=A3

 This compares the three first letters of cell A2 to the first three letters of cell A3.  This solves your Rob/Robert’s and Nick/Nicholas’s of the world.  If you want to be a little less restrictive, you can do two instead of three and solve for your Nicole/Nikki’s and your Jo/Joanne’s.

This handy trick also works well for addresses.  Generally if the first 4-5 digits of the address are the same, they are close enough for government work (but still catches them if someone put Rd instead of Dr).

Adding as you go

Because Excel is a flat file and not an actual database, you will frequently have to sum gifts to get a total amount of giving.  Let’s go back to our initial email list, now with gift amounts and the middle names broken out.  Columns F, G, and H, are set up to compare A, C, and D.

thirdexcellist

Here’s how to proceed:

  1. Set up column I with the statement =AND(F2,G2,H2) (and copy it for all of the rows). This returns TRUE if all of the columns are TRUE and FALSE if any are.  This puts in one cell whether something is a duplicate or not.
  2. Set up the first row of column J with the statement =IF(I1,J1+E2,E2). This is the trickiest bit.  What this is saying is “Look at the row before this.  If it’s the same as this row, add this donation to all of the previous donations that match this record.  If not, just put in the one gift.”
  3. Copy this column and paste values like you did with the TRUE/FALSE earlier.

fourthexcellist

Now, when you deduplicate, you’ll be getting the total amount of gifts for a donor, summed up.

There are certainly not perfect.  But to do perfect, you need a database.

Deduplication 2: Deduplicate harder

Your trapezoid zero database – a spreadsheet

Why trapezoid zero? Because square one is too dignified.

If you are like most nonprofit direct marketers, you have one true donor database that filters in all of the data you need from various applications, gives you a view into what every constituent needs and wants, and allows you to segment and communicate with them instantly.

Then you wake up from your dream. You perform requisite amounts of personal grooming, cover your dirty shameful bits with clothing sufficiently uncomfortable to convey gravitas, go to work, and stare at the latest list you have pulled in Excel.

Excel is not a bad program per se and has gotten even better since Clippy was taken out for some cannoli in downtown Redmond and never heard from again.  The trick is that it is not intended to do what we do with it for address lists and the like.  For that, Microsoft created Access, which is actually a bad program per se.

I promise to talk about using Excel for its intended purpose, but first, here’s how to manage your lists in Excel, starting with basic deduplication.

edharris

Deduplication could effectively be called Sisyphean except some is pushing the blinkered rock down as you are trying to push up. You are never, never done deduplicating, especially given that the most common records for deduplication are your best supporters (given that they will be engaging the organization from all angles).

So let’s assume you have a very simple list.  You want to remove duplicates from an email list.  Specifically, you are looking for exact duplicates – you don’t mind emailing the same email twice as long as it is for different names and you don’t mind emailing the same person twice as long as it is at two separate addresses.  Here’s your list:

firstexcellist

As you can see, there are a few Blofelds in there to get rid of.  Your list will be longer and less menacing.

  1. Select all by hitting Ctrl + A
  2. Go to Data > Sort
  3. Sort by all three fields in any order
  4. Now, you are going to compare each row to the one below it. Go to the top row and the first empty column and put in =A2=A3.  This creates a TRUE/FALSE statement where anything that comes back TRUE is identical.
  5. Drag the lower right corner of the cell you just created down so it covers all rows and over to the right so it covers as many columns as you have. It will look something like this:
  6. Highlight the TRUE/FALSE columns, hit Ctrl + C to copy the columns, then right-click on the same columns to bring up the special menu. Select Paste Values.  What you are doing here is replacing your TRUE/FALSE statements with the results of those statements.  Because you are going to be sorting by these, you don’t want Excel recalculating.
  7. Select all by hitting Ctrl + A
  8. Go to Data > Sort
  9. Sort by the three TRUE/FALSE columns. The output will look something like this.

    secondexcellist
    As you can see, there are two records in rows 14 and 15 that were exact matches for other records in the database, since they have three columns of TRUE.  Those are your duplicates and you can delete them.  One might say you have a license to delete them.

That works fine for simple data.  But how often does that happen?  Tomorrow will be some advanced tips for deduplication in Excel.

Your trapezoid zero database – a spreadsheet