Meet Ryan. Ryan is one of the most skilled community organizers I know and I’m lucky to count them among my friends! They’re compassionate, curious, and generous. They’re great at matching people with roles, but not afraid to roll up their sleeves and do the hard stuff, or the tedious stuff. Ryan has taught me soooo much and even coaxed me out of my early retirement from organizing. And, like many organizers, Ryan faces the great database/google sheets reconciliation quandary. *cue spooky music*
While planning for end of year fundraising, Ryan (pictured on the right) will make exports from a fundraising database and distribute them among a number of volunteers (who don’t all have database access) who will then ask for donations. But then, problems will inevitably emerge! What should they do when donations start to come in? As far as I can tell, Ryan has 4 options and none of them are great. Update the Google Sheet, update the database, do both manually, or update the database and then refresh/replace the sheets. There’s no point in updating the google sheet if it’s not actively in use… but how will Ryan know exactly WHEN to make the updates? These types of data reconciliations can be a nightmare … way worse than navigating a 5 acre cornmaze under a full moon (which is what we did while we had this conversation!)
This episode of Dear Spreadsheet Whisperer is brought to you by the sustainability themed cornmaze at the wonderfall Snipes Farm and the sweet, meandering conversations that took place in its rumored paths.
If you’re reading this, Ryan’s story might feel familiar. Does it feel familiar in your tummy? In your shoulders? In your hips? Somewhere else where you feel stress? It’s ok! I’m here for you and we can tackle this problem together. First, I have some ideas for making easy to use, shareable data views that will help you and your volunteers be more effective. Then, I have some ideas for how you can set up spreadsheets so that if your volunteers DO make changes that need to be synced back into the system, it’s easy to spot them!
Best Practices
As soon as I export data, I consider it “out of date.” Not necessarily inaccurate because I KNOW that no one else has messed with the data immediately, but once it’s out of the system, all bets are off. This is part of the “Single Source of Truth” mentality. If your group is running on the assumption that the database may be inaccurate because your Google Sheet is the most up to date single source of truth, then I gently encourage you to make a plan to get your database into tip top shape! And I can probably help you (in many cases, for free!).
That being said, we alllllll need to make exports for a variety of reasons, so here’s a list of some best practices that I use. I think they could really help!
Include a “Read me” tab in your Google Spreadsheet with instructions for your volunteer AND instructions for YOU to help you retrace your steps if you have to refresh the data
Include “As of” date (when you pulled data from the Database) (reminder that the two MAY be out of date if the “as of” is a few weeks old)
Write out the filter criteria in your Google Sheet in human-readable words 🙂 (example below)
- Save the Report or Filter in your database (so that you can easily access it again)
- Copy the URL or name of the Report/Filter somewhere in your Google Sheet
- Include a friendly explanation, like the example below!
EXAMPLE:
This is a list of everyone who donated in 2017 but hasn’t yet donated in 2018, who live in Pennsylvania and have Sam as their Primary Point Person, as of 10/23/2018. Originally downloaded from [Name of Report or even better… report URL from database!]
You can’t step into the same river twice
There’s an old saying that you can’t step into the same river twice, because you’ve changed and the river has moved. This is a great metaphor to apply to spreadsheets! My advice for Ryan and friends who see themself in Ryan’s story is to consider Google Spreadsheets (copied from your database) as a “snapshot” (this has a different connotation than a “report”). In today’s insta-selfie-snap-facetime culture, I think it can be a really wise reference point.
EXAMPLE:
This was our data in one instant. It’s a helpful guide but it may have changed since [LAST UPDATED DATE]. Please get back in touch with me if it’s been a few weeks and you need an updated version. I’ll be updating [OFFICIAL DATABASE] as gifts come in, but not necessarily this spreadsheet because updating info in 2 places is a recipe for confusion. Let me know if you have a major donor visit coming up so that I can get you the most recent information!
Nobody likes a syncing ship
Just kidding! We actually DO want our data to sync. But what’s the best way to do that?
Let’s say you have a volunteer who meets with Major Donor who happens to be named Bernie Sanders (no relation) and he says that he has a new phone number. Or he makes a pledge and you need to identify that he will need a follow up. How does a volunteer get that back to you so that you can update the Single Source of Truth?
I would recommend making a spreadsheet that has some color coding. So on the left side of the sheet, you would have columns for Name, Address, Phone Number, Contact Preferences, Giving History (all of these would come directly from the database). Let’s color that section blue. Then, on the right side, you have info that is subject to change – we’ll make it green. For example, date of major donor visit. Or, pledge gift amount. If your volunteer saves over the original phone number, how will you know that it ever changed?
In between the blue and the green sections of the page, make a column for “Has Core Info Changed?” and make it a Yes/No column. That way, if your volunteer makes any changes in the blue section, you will know to update it! Everything in the green side will need to be migrated over to the database, either manually (if you are a small org) or with an upload (if you are a bigger org). And if you’re a really big org, you probably don’t need this system at all because your fundraisers will be in the database anyway! But you can generally expect that you don’t have to change any of the blue info unless that box is checked off.
Ryan, THANK YOU for the amazing work you’re doing and for asking this very relevant question about how to sync and reconcile and manage multiple versions of the same data. If other readers have questions related to End of Year fundraising, get in touch with me ASAP! I’d love to blog about more spreadsheet solutions to help you reach your fundraising goals 🙂
This is such a good explanation!