Dear Spreadsheet Whisperer,*
We went to college together and it’s amazing to see how our careers have led us in totally unexpected directions! Now I work at a nonprofit that supports the North American Syringe Exchange Network (NASEN), which operates a buyers club through which the majority of the country’s harm reduction programs order supplies. The problem is, all of our data are stored in Quickbooks and we rarely keep it up to date unless we are mailing inventory. The other problem is, some of the programs have requested that we don’t release their data, so we don’t enter their info in Quickbooks at all. We also have two other databases… one for actual bookkeeping (not in Quickbooks) and another in Access, that hasn’t been updated in the last 5 years or so. Our program is growing and we’re trying to do surveys and program evaluation, but it’s difficult to tell who’s completed what, or who we should be reaching out to! Is there a perfect system out there that can solve our problems? Or general advice?
Sincerely, Needle in a Haystack
*letter is paraphrased for length
Dear Needle in a Haystack,
First of all, THANK YOU for doing what you’re doing! I have been closely following the story of the first supervised drug injection site in Philadelphia, one of many strands of conversation in the harm reduction movement right now. I didn’t know there was such a thing as NASEN and I’m so glad that it exists. It’s important to me to be engaged in this topic because of the public health crisis in Philadelphia and because I’ve had family members and dear friends develop addiction with injectable opiates. I don’t want any of these folks to be forced to compromise their dignity and safety because of chemical dependency.
It sounds to me like you have a case of the Pre-Merge-Blues! Data stored in lots of places make it nearly impossible to trust your records, keep info up to date, and generally maintain day to day operations. Matching survey responses ideally shouldn’t take all day or all week, but it can if you have data discrepancies and data in multiple places. It’s likely possible to consolidate everything into one system, but first you’ll need to do some reflection about all of the things the system needs to be able to do.
of Bloom and Glory
I’m no Quickbooks expert, but I know that it can function as a mini CRM (constituent relationship manager) if your contact list is pretty simple. If you are trying to track complex attributes like “keep anonymous” or “did they fill out the survey in 2019?” in addition to “how many orders have they placed?” and “who is the right contact for x/y/z purpose” I would imagine that you have outgrown what you can realistically accomplish in QB.
The first thing I would want to do is export your contact data from QB into a spreadsheet and take an honest look at what’s in there. How many contacts? How many organizations do those contacts represent? How many of them have email addresses? Who’s missing? I would do some quick Excel (or Google Sheets) tricks to make this easier. I blogged about some of those tips here and here and here. Once you update the spreadsheet, there should be a way to upload it back into QB (if you’re sticking with QB, which you probably are for the near term). That being said, it sounds like you also have a gap where program contacts should be because you’ve mostly kept track of inventory contacts so far. Someone is going to have to research that list to get programmatic contacts, but before that happens, it would probably be helpful to have a system for where you are going to store and maintain those contacts, which will inevitably grow and change over time!
Next, it’s time to make some decisions. For the organizations that are no longer active, do you want to remove them from the system or are they attached to meaningful legacy data (like their order history?). I believe QB has a checkbox for “no longer active” and if they don’t, hopefully you can figure out how to make one … or contact QB support. An alternative is to give those records a new name. I’ve seen some organizations use naming conventions like [DEPRECATED] <name> or XYZ <name>. Then, you can filter them out pretty easily.
It sounds like you have different types of contacts that should receive different types of information (like outbound emails) and for which YOU hold onto different types of information (are they an ordering contact? a programmatic contact? can someone be both?). This could be accomplished with checkboxes or a drop down list as long as someone is consistent with maintaining whatever system you decide. I’m not sure if QB can accommodate this degree of contact segmentation, but likely you can find an answer in QB forums online.
When it comes to the survey matching quagmire, I would take a list of survey responses and a list of organizations in our system and export both of them to Excel. If the names are likely to have overlap, I would use a VLOOKUP function to match whatever I *could* and then sort alphabetically and match the remainders manually. Have you used VLOOKUPs before? I wrote a blog post about them here. The downside here is that for VLOOKUPs to work, you pretty much need to have an exact match. And that’s not always possible with survey results where people type in the name of their organization.
I’ve written a lot of paragraphs, but I haven’t answered your biggest question of all!
None of us really know what’s possible, full stop, much less what we can get on a nonprofit budget. For example, I think it’d be great if something could handle both our “selling stuff” needs and our “keeping track of program contact info” needs, if that’s a thing that’s possible (I feel like this is what CRM software is? But I don’t know).
My answer is… unequivocally, without a doubt, YES. There are systems that are built to handle this exact kind of information. It will take time and some $$ and an expert or two to figure it out, but it’s definitely possible. I’m most familiar with Salesforce databases (which offer 10 free licenses for qualifying nonprofits), but other databases like CiviCRM could also accommodate the kind of database I think you would need, too. (Civi has an added bonus of being Open Source, but the training materials aren’t as robust and there aren’t as many experts around to help out). I know plenty of good Salesforce consultants who could help you understand how much it would cost to do an implementation project. I would say the cost would probably be in the $5-$15,000 range to customize everything.
That being said, you are going to have to tend to your new, beloved, hypothetical database or you can quickly end up with the same problems you already have. No system is perfect, but I think you can build a system that’s substantially better than what you have now — or enhance your current system to be better at handling your complexity.
I believe that nonprofits should have access to excellent data tools and expertise, just like companies in the private sector do… but better, since we’re trying to make the world a better place! It’s SO hard to find reliable information about CRM systems and where to even begin to iron out data issues. If you want to hop on the phone, I’d be more than happy to do a deeper dive. I’m sooo happy to hear from you and I wish you and NASEN all the best!
Yours in spreadsheet agony and bliss,
Spreadsheet Whisperer