Isn’t it great when spreadsheets just WORK, making your repetitive tasks faster and more accurate? Here are two examples of spreadsheet tools I’ve built with a combination of Salesforce exports and spreadsheet prowess. Using them makes me feel proud and blissful!
This post was written half on Friday 3/15 and half on Monday 3/18.
I’m home. Aaaaaah it feels so good to say that. Dinner is cooking itself (crock pot tagine and roast chicken doin’ their things). I was stranded in the Denver airport for 36 agonizing hours during the “bomb cyclone” weather event. There’s a first time for everything, but I’m not here to celebrate my first time attempting sleep in an automated massage chair (yes, indeed, I was quoted in the Denver Post coverage of the storm).
Earlier this week, I wrote about attending the PEAK Grantmaking annual conference, which was overall a GREAT experience. I attended many sessions about the need for administrative transparency and efficiency as a tactic toward dismantling the power differential between grantmakers and grantseekers. This definitely hit home close to home for me!
Is the budget budging?
I was humbled to share the custom budget tool that we use to support operations at The William Penn Foundation – a solution that took months to build (I blogged about it a bit over here). Hopefully, in a future post I can write about the solution steps with more detail. It was a great experience to craft the presentation – where we wanted to share not only the tool itself, but also HOW? and WHY? and SO WHAT? My colleague came up with a framework that puts the tool in the context of strategic planning which provided a perspective and application that I hadn’t even considered!
Let’s back up for a moment. What is this fabled tool I speak of? It’s a template that we export from our database system that dumps all of our payments into a spreadsheet and runs formulas on them to help us understand how much of our budget is committed versus available in a several different categories. We use it to figure out how much money we can give out in new grants. Similar logic can be applied on the nonprofit side if you are analyzing donations compared to different fundraising goals and how much you still need to raise. I’m really proud of the tool, especially since it is ‘technology agnostic’ – the fundamental concepts can be applied to any database system or dataset!
Want to learn more about the formulas we used behind the scenes? I wrote about the SUMIF function here and the VLOOKUP function here and here.
It was an honor to present in front of such a talented and well informed audience about a fundamental and yet innovative tool. It sounds like we’ll be doing follow up webinars and sharing the tool kit more widely, which makes me feel optimistic and excited!
Things are v-looking up
Did you know that I do a tiny bit of consulting? I take on a few smaller contracts per year to support social justice orgs that need a lil boost in the database department. One of my clients is an LGBT center (hiiiii if you’re reading!). This project was extra fun since they *already* have great practices for logging data and with a few tweaks on the back end, they could get a lot more impact out of their efforts. We whipped up some dashboards, automation, data hierarchy and documentation to boot!
One particularly interesting need was a way to compare everyone who attended an event “so far this year” versus people who had RSVPed to their annual ‘gala’ like event. I tried and I tried but I couldn’t think of a good way to do this inside of Salesforce. Bleh! (If you’re reading this and you know, leave a comment!)
Instead, I created a Google Sheets template where team members could export 2 Salesforce reports, copy the data into the tool, and the tool would immediately show repeat guests (guests whoa attended BOTH a prior event AND the gala). It involves a little bit of exporting and cutting and pasting, but the formulas are set up for success. It’s a good intermediate effort between hand-counting (boooo…. also, unsustainable) and creating new VLOOKUPs each time, which can be time consuming and intimidating and error prone.
Let’s take it outside
Both of these projects involve taking structured data from a database and dumping them into a spreadsheet template with pre-built formulas across several tabs. I’m not really sure what to call it… a macro? a tool? a workaround? an extension? And I’m not sure who else builds stuff like this… But whatever “it” is, it’s AWESOME! When I reach the end of the road with what I can accomplish in Salesforce, I usually rely on Excel/Google Sheets as my first line of defense. I want to make solutions that work over and over again, even when the underlying data change over time. It would be silly to waste time re-creating the same formulas every time you download a dataset. They’re YOUR data and they should be able to tell a story quickly and easily. The main thing you need to be able to do is narrow down your set of questions to a discrete requirement and build formulas to satisfy that one thing.
It’s SO fun and rewarding to apply spreadsheet fundamentals to new problems and work collaboratively to free up time and energy so that changemakers can do more of what they do best. That being said, I want ANYONE to be able to make these interventions! (Yes! Working myself out of a job!) I’ve been dreaming up some new resources and opportunities for learning, building confidence, and having fun with data… you’ll hear more soon. But for now, thanks for coming on this spreadsheet adventure. I’ll see you back here soon. Don’t touch that dial!
Out of curiousity, why were you unable to do this in Salesforce? It seems like as long the event tracking was done in Campaigns it’d be easy enough to track.