Build a nonprofit donor dashboard with Google Sheets and Kindful

By Leigh Doyle

"When you consider the people that start a nonprofit, they're not the bookkeepers or accountants. They're people with big visions and big hearts. Usually spreadsheets are too much for them. So I try to make it as painless as possible," says Clay Sandoz, Owner, Wellstead Solutions.

In this episode of Gearing Up, Clay talks about how he wanted a way to offer his clients the value of a full-time business manager without having to work every minute of every day. He'd watched his clients' eyes glaze over when he shared static rows of numbers, so he decided to make a change.

The challenge:

Donor management platforms provide robust information on fundraising and individual donors, but Clay wanted a way to quickly surface organization-wide financial trends and insights — and he wanted an approach that scaled. 🚀🚀🚀 

The solution:

Clay developed a user-friendly, interactive dashboard in Google Sheets using data from Kindful. It sorts data by different criteria and presents it by month or over the life of the organization. This makes it easier for nonprofit leaders to engage with their financial information and see where they need to take action. For Clay, these dashboards help him deliver a full-time business manager experience to multiple clients. 

Watch the full episode for a deep dive where you’ll see: 

  • 🛠 The tech stack Clay uses for his nonprofit clients 

  • 🖥 Clay’s custom financial dashboard with pivot tables and nested formulas 

  • 🔦 The different ways to interact with the dashboard to uncover actionable insights 

  • 💡 Me learn something new — specifically how to import .csv files to replace data at a specific cell

To learn more about Clay and his firm visit and be sure to check out the mix of services he provides to nonprofits.

If you’re ready to share your knowledge on an upcoming episode of Gearing Up, get in touch, submit your challenge + solution using the form.

We want to hear from everyone: The solo practitioners, the firms with 30+ staff, the bookkeepers, the CPAs, those who just got started, those who have sold their firms, and everyone in between! 😊

Remember, Gearing Up is published every two weeks, subscribe to get the next episode.

Gearing Up is dedicated to amplifying the creative and smart ways accountants and bookkeepers overcome the day-to-day challenges of running a successful firm.

How do successful firms solve day-to-day challenges?

Find out in Gearing Up — our bi-weekly video series. Blake Oliver talks to practicing accountants and bookkeeper what tools and strategies they use to thrive.


Gearing Up episode 2 transcript:

Blake Oliver: Hello, Blake Oliver here. And this is Gearing Up, a series by Relay. Relay is business banking designed for accountants and their clients. Just like me, they're obsessed with making firms more effective and efficient. So in each episode, I talked to real accountants and bookkeepers about one challenge they faced in their firm and how to solve it.

Today we have Clay Sandoz of Wellstead Solutions on the show. Thanks for joining me, Clay.

Clay Sandoz: Hey, Blake, it's good to be here.

Oliver: So tell me a bit about you and your firm.

Sandoz: Sure, so my name's Clay and I operate Wellstead Solutions and to put it simply, we are simplifying financial operations for small businesses and nonprofits, focusing primarily on nonprofits. It all really began as an employee of a nonprofit who was overseeing financial operations, as with one organization as my full-time job and realizing just some of the needs that were not unique to one organization but I was seeing across the entire industry and I worked really hard.

I got to joke that I would almost hate to total up the number of hours I put into time saving techniques because if you think about how much time you spend trying to save time, it can be a little overwhelming.

Oliver: Yeah, you spend eight hours to automate a 15 minute task.

Sandoz: Oh, man, it's such a relief, yeah exactly. But through doing that, I Developed a lot of good systems and practices that I was able to then branch off and scale and allow me to serve a number of nonprofits, basically offering what in their experience feels like a full-time business manager, but I'm able to scale an offer in multiple places because from their standpoint it's less than the cost of like a part-time employee. So it's a really good fit for me, for the clients that I serve, and allows the directors and the leadership of the nonprofits to really just focus their energy on their mission, their vision, focus on what they are there to do and not worry about all the messy backend stuff of processing checks and online donations and dealing with donors and that kinda stuff.

Oliver: Well, I'm sure you're automating as much as possible, going cloud as much as possible. So let's talk about the technology that you're using in your practice. Can you give me an outline of your tech stack?

Sandoz: Sure thing. So from a financial standpoint, I really like to use Kindful, it's a great donor management platform. I've experimented and used a number of others with different clients. But when I have new clients that come along, I really like to point them toward Kindful. It's really very user friendly, it's very donor friendly, it's a really good platform. So that's primarily where we process online donations. And then for physical checks that come in, we also log them there. So it's the main data center for all of our donations that come in. It's also what allows us to send out receipts at the end of the year, because again, all of our donation data is in one place. That doesn't mean that all of our organizational financial data is there, but just the donation side of things.

In some cases we use Jotform as well, not necessarily to process donations but they do allow for some of that. I have a couple of clients who within the guidelines of what's allowable for a nonprofit have some things that they sell, like selling donated items and things like that. So we've set up some online shops where they can sell. I really like Jotform because of the way it integrates so easily with Google Sheets and things like that. So as you can see here, my Kindful and Jotform is the input side of things. 

And then the things go in two directions, the money goes straight to the bank over to Relay. So all that kind of stuff is on auto deposit. And then the information, the data side of all the donations that come in, I send a Google Sheets first, almost everything can integrate directly into QuickBooks online but I find sometimes when you send it directly there, there's a little bit of a loss of intentionality and being able to send that data exactly where you want it to go. So I send everything first to Google Sheets where I'm able to then reorganize it, retool it in a way that's gonna be most clear to me as the bookkeeper, to my clients who are viewing it, for the CPA at the end of the year who's gonna be preparing the 990, which is the nonprofit tax return. So I like to clean up the data in Google Sheets and then have some ways to push that up into QuickBooks, which then of course syncs with the bank as well. So it all comes full circle.

Oliver: Can we take a peek at your spreadsheet?

Sandoz: Absolutely. So I approach it a couple of different ways for each client. But this is the baseline. So what we wanna end up with is a user-friendly dashboard here that a client can look at and say, "This is what month we had." and then go on to compare it to other months. I'll give you a quick look through first inside of Kindful just to show, it's not that Kindful is insufficient or falls short. This is the main activity feed inside of Kindful. And so you can see this is actual live or it's real data from one of my clients but you can see on Monday that little circle symbol there shows that it's a recurring donation. So that was on Monday the 20th, we had one, two, three, four, five recurring donations hit and they've got some large recurring donors that were giving. Then on Sunday, the day before, you can see that little check mark, that just means a check was manually logged into the system for $500.00, below that we had a PayPal donation.

So you can see a nice feed of activity, but it doesn't give you a lot to compare it to or to really help you interpret it. Kindful does a great job giving you information on specific donors. So looking at this donor, you can see their lifetime donation, you got these nice charts of when they were giving and things like that. But the thing I find about it is you have to know that you want information about that donor and you have to go looking for it. But what I'd like to provide my clients is a broader look at how their organization is doing financially so that maybe they could, some of these trends would stand out and then they could go dig into it and maybe go look at this donor's profile in Kindful to get a little bit more granular information but this is not usually, they're not gonna go combing through each donor's profile to see where are the irregularities and things like that. The thing is what comes from Kindful is a CSV file.

So it's not very helpful, that's not something I'm gonna hand over to a client to say, "Here's your donation data for the month or the year or whatever, this is gonna make." A lot of people go blurry in the eyes especially when you consider in the nonprofit world, typically the people that go out and start a nonprofit, especially the small nonprofits, like the ones I deal with, they're not the bookkeepers, they're not accountants, they're not the nerdy back office folks, they're people with big visions, big hearts, they wanna be with people, they wanna be out there with a big vision and this kind of stuff. Usually spreadsheets in general are a lot of times too much for the people with those big visions. So I try to make it as painless as possible. 

So this is where we deal with this raw data, we start here. So what I do is I've got this note to my clients here, “please don't modify anything to the right of this. This is all my territory.” A lot of the stuff I end up hiding but I make this note, just “please don't mess with it.”

Oliver: That's a good tactic, I like that.

Sandoz: Yeah and then I put it up here too. “Please don't edit or modify the sheet in any way.”

Oliver: I'm sure it still happens sometimes.

Sandoz: Exactly, it has happened a few times. Thankfully with Google though, you can roll back edits pretty easily, I've had to do that a few times. So what I do is I just drop in the CSV, I formatted everything to be ready for the data even down to calculating. This is a formula that's gonna give us all of our fees based on whether there was an ACH donation, a check that came in, cash, credit card, that kind of stuff. So I'll just go to File, Import, and I'm gonna upload thatCSV there and replace data-

Oliver: See, I didn't even know you could do that. I just learnt something today.

Sandoz: And it's invaluable. I use it all the time because once you've built the framework for it, and you know the parameters of what you're bringing, it takes some testing. But once you can trust the parameters of what you're bringing in, you can just overwrite. And so what that means is next month I'm gonna have by design more donations on the list, but it's gonna also include the ones here, that's why this is red, I click here and I say, “replace starting at this cell” and it overwrites everything with new data, but also the old data. And so what we have is now this is all the donation data, we have each fee.

Oliver: And can you bring this in from the beginning of their...

Sandoz: Yeah, so I use some dummy data here just starting in June. But yeah, I mean, I have organizations that I do this for and it's years of data. I mean, it's a little probably overkill but it's worth it again, in the case of historical updates or modifications to the records. And with the CSV file, it's not like we're talking gigs of data, it's pretty manageable. 

So we're bringing in all the important information here, including this note that allows us to, it doesn't really matter that, okay, this is monthly on the first, monthly on the fifth. I don't do much with that date but what I'm able to use is this and use a formula that says, “it doesn't matter when but just note that this is a recurring donation”, because we're gonna note that to the client later so that this will stand out to them, that this is not just a one time donation but this is a standing recurring donation. So all this stuff and we won't go into all the details, but I mean, this gets pushed out into a pivot table which I didn't show you, but I mean, there was nothing here before, but now this gets automatically populated. We've got this pivot table is leading building queries, which is building other pivot tables. So there's like all these nested formulas that go deep back here which come back to our summary sheet where we started which was blank but now we've got a lot of data that we can deal with. 

So the donor lists, if this was at the end of the month of August, now they're looking at August of 2021. And here's all their donors who gave in that month, the date, their donation amount, the fee. And here's that note to say, “and this was a recurring donation”. So good old Jeff Bezos, he's donating $100.00 a month and he's agreed to Kindful offers, you can cover the fee. So he paid an extra three bucks. The actual fee was $3.28, but it more or less came closer to $100 donation rather than us paying the fee on it. So this is just a helpful way month-to-month that clients can see this where it's really helpful. 

Let's say we're in the month of November and we're looking now at all this information we wanna see, we wanna go back. So now we can click this drop down and we can keep flipping back through the months and see we can watch these totals. “How much are we paying in processing fees?” If this number is off the charts, we can say, maybe we would like to have some of our bigger donors give by check because we're paying out the nose, paying 3% on a $10,000.00 credit card donation or something like that. So just again, I offer tiered services to my clients. Some I serve as an operations director where I will help interpret the data and help make decisions and help guide in some of these directions. In a lot of cases though, I leave that up to them, but I provide all the information for them to make the best decisions that they can. And to me, this is a really critical part of it. 

What we didn't have is, this is just a running total of all the donors to this organization and their grand total. So we've got the Human Fund, there's their grand total, we've got all these different individual donors and just their running totals. This gives us a good bird's eye view of “who are our big donors” and “where is most of our money coming from” over the life of the organization. It's not a time-based report, it doesn't give us this year or this month or anything like that. It just gives us big picture stuff. And then each of these, we can sort by different criteria. So right now it's chronological, show me the month and show me just in order. But we could also say, “I wanna see it by donation amount” and “I wanna see my biggest donors of last month.”

Okay, so we got this Working Nine to Five LLC donated $10,000, no fee, so that tells us it's probably a check. So we can see in a descending order who are our big donors and we can do the same thing here with, we've got this already now sorted by our donation amount. We could sort by last name if we just wanna see all of our donors in that order. 

So it gives the client a lot of ways to interact with their big picture donation information as well as the monthly stuff. But then, in cases where we have recurring donors who are giving over time, it's one of the most common issues, people will get their credit card hacked or stolen. Somebody uses it fraudulently, card gets canceled and they've got a million updates to make. They have to update their Netflix and Amazon and all the services that they have on this card. A lot of times they forget their charitable donations and those get lagged, then their donations get canceled and it falls off. Well, we wanna know that and I will say, Kindful does an amazing job with that. One of the things they even do is they'll monitor your expiration date and the donor will get an email two months ahead of the expiration date and say, "Hey, it looks like a card is about to expire. If and when it does, here's how to update it." Really nice, makes my life a lot easier.

Oliver: Super helpful.

Sandoz: But it still happens, yeah. So what we wanna do is then be able to see this information spread out over time so that's our donor monthly details. So now we have this same as this summary here. It's our master list of donors but it is broken out over time. So now we can see June, July, August and all the way through the year, who's donating how much. And we can now look for the trends that we saw in Kindful, we saw that chart but we can see it all at one glance. 

So in this case Michael Bluth donated $26.00 a month, which is really, $25.00 plus the fee. So okay, great, it looks like he's a standing donor and then, oh, it looks like something had happened between October and November. It looks like his card dropped off, it's been a month and a half. He probably should have already donated by this point in December, we should probably reach out to him and say, "Hey, it looks like your card got canceled. Can we help you reconnect it?" So again, this is the stuff with some clients that might be my job to go find that, pursue that person, track them down, get it done. In some cases, it's just my job to provide that information. So the client can have that so they can make contact and do what they need to do. 

So then in this case, we've got again, the donor, now we can see their history over time with their grand total over here. And we can do the same thing, sort it by all the same criteria depending on what the client really wants and needs out of the information. 

So that's about it, that's a tour of the basics of the dashboard. And then what's nice is from there I can actually use a lot of this data to then build custom journal entries that I can then push right out into QuickBooks. But again, all the information is here so that I can begin working with it just in one place.

Oliver: Well, that is really neat,thank you for showing us that.

Sandoz: Absolutely.

Oliver: Yeah, so can you quantify how much time you think this has saved you over the years compared to the old way you used to do it now with a single client?

Sandoz: I mean, it used to be, I'll say it this way, it used to be a full-time job for one organization. And now I do it for eight or nine different organizations and have space to accommodate quite a few more.

Oliver: That's amazing.

Sandoz: Yeah, I mean, it saved me, I mean, hours and hours and hours a week, spread out over years.

But again, it was a lot of upfront investment and I mean, it's fortunate that I enjoy it. So it was grueling to spend the time investing to figure this out and play with it and experiment with it. But it was a lot of upfront time.

Oliver: I think I saw one of your formulas had eight or nine nested parentheses in there. It was a pretty advanced function there. How long do you think it took you to make this? I mean, obviously you've iterated on it over time.

Sandoz: Sure, yeah, yeah, oof, man. Well from scratch I made a similar dashboard. It was very customized for a client. I wanna say it took me about 40 or 50 hours maybe, over a month or two.

Oliver: But well worth it when you compare that to now I can have not just one client but I think you said eight, nine and with room for more, right?

Sandoz: Eight, nine and room for more, yeah.

Oliver: Well Clay, thank you again for showing us this. I learned a lot and I'm sure our viewers did as well. Is there anything else you'd like to share with us today? For instance, where folks can get in touch with you.

Sandoz: Sure, you can find meat You can contact me there if you ever have any questions about nonprofit needs. And a quick overview if I can of some of the things I offer. Everything turnkey really for nonprofits. Again, we work as well with small businesses but our focus is really on nonprofits. I've got several clients who came to me just with an idea. They had nothing more than just, “here's what I have in mind and I have some backing financially.” So we walked them through that entire process working with the IRS, working with the state to get everything formed, setting up bank accounts and donation processing and mailing addresses and all that kind of stuff. Just turning it into a legal organization. Process development, setting up online donation systems,  processing paper checks, which can just be a real hassle, being the face of donor relations. 

I was on the fundraising side of the nonprofit world for a long time and my income relied solely upon it. So I know how critical it is to maintain good relationships with donors and so has my staff. So we bring that into our relationships as we operate on behalf of our clients. If anybody has any questions,I usually offer a consultation just to get started and can have a conversation for nothing and then see where things go from there.

Oliver: Sounds good. Well, I'm sure a bunch of folks will be reaching out about your spreadsheet. I think you could offer a lot of insight on that regard.

Sandoz: Yeah, great.

Oliver: So thank you, Clay. I am Blake Oliver and you have been watching Gearing Up. We'll see you again next time.