Configuring Customizable Rollups in NPSP: Part Three

Welcome to Part Three

In parts one and two of this three part series, we learned all about Customizable Rollups in the NPSP, including how they were developed, when and for what they are a good solution, how to enable them, and finally how to create your own custom rollup.  Now that we’ve been through the nuts and bolts, let’s look at some interesting use cases for this new feature, and how a custom rollup could be configured to solve each.

Before we get started, the folks at Salesforce.org have created a fantastic guide in the NPSP Documentation called “Customizable Rollups Recipes,” and it’s a great place to start.  This guide will walk you through configuring NPSP for some of the most common use cases that aren’t addressed in the standard off-the-shelf configuration.  In this guide, we’ll try to add to this great set of solutions, and maybe even push the envelope a bit.

#1: The Board “Give-Get”

The Use Case:

Many nonprofits ask their board members to raise a certain amount of money each year for the nonprofit.  These funds can be either direct donations they make, or donations they solicited from their friends on the nonprofit’s behalf.  In many nonprofits, this obligation is referred to as the Board “Give-Get,” in a reference to the fact that they can either “give” or “get” donations.  Tracking Give-Get can be challenging, however; you have to look not just at the funds the Board member has given and therefore is the primary donor for, but also at any gifts they solicited.

The Solution:

• Begin by creating an Opportunity Contact Role (OCR) to track Board member’s relationship to opportunities.  This is typically called “Solicitor” or “Board Solicitor.”
• Create a currency field on the Contact to hold the total board solicited gifts; in this case we’ll call it “Current Board Solicited Total”.
• Assign this OCR to the Board member on any gifts they secure towards their Give-Get obligation.  
• Create a Custom Filter with a filter rule on the Contact Soft Credit for Role Names that equal “Board Solicitor.”
• Create an Opportunity>Contact(Soft Credit) rollup to the Contact record summing the current year’s amount using the Board Give-Get Filter.

• Create a formula field adding this field to the standard rollup totalling the Contact’s hard credit giving (usually “Total Gifts This Year,” but can vary based on your configuration, so definitely check).
• Use your new field to track your Board Give-Get and to drive fun automations like congratulations emails!

#2: Who Loves a Party?

The Use Case:

Many nonprofits have a signature event or set of events that they spend a lot of blood, sweat, and tears on every year.  Fundraising events can be a tremendous amount of work, so marketing them to your donors who are most likely to attend (and spend money at) the event is key to maximizing ROI.  Making matters more challenging, maybe you can only send out so many invitations, and you want to be sure you maximize your RSVP’s. But donors are different! Some never miss an event, some never attend even though they give regularly to your annual fund, and many fall somewhere in-between.  How can you gauge a donors relative interest in your special events compared to your most general annual campaign?

The Solution:

• This solution assumes you use Campaigns to track your special event giving, and that you have an option in the “Type” field on that campaign for “event.”  Start by creating a field on the account to track total event giving.
• Create a filter group on the Opportunity object where the Primary Campaign Type equals “Event.”
• Create an Opportunity>Account(Hard Credit) rollup that sums the amount of all giving over all time with your new Filter for event-type campaigns.

Follow this same process with ANOTHER custom rollup totalling all giving to Annual Fund campaigns.

• Create a percent formula field that calculates the ratio between these two values; divide the event total giving by the annual fund total giving.  To interpret this number, read it as what percent of a donor’s annual giving their event giving amounts too. For example, if I’ve given $1,000 to events and $3,000 to the annual fund over the years, then my event spending is only 33% of my annual giving.  
• Side note: this also works great when event spending is calculated as a portion of total giving; play with options to see what works best for your organization.
• Now you can use this number as a proxy for a donor’s historical tendency to spend big at your events.  This can help you get the invite list down to (or up to) the right number of prospects.

#3: On a Roll with Loyalty Members

Use Case:

For this use case, we will explore rolling up values other than currency.  Many arts and culture-based nonprofits have large, complex membership programs.  Donors can purchase a membership that gives them access to exhibits or events, with the hope of annual renewals.  One way to encourage consistent renewals and the steady stream of revenue they represent is through Loyalty Membership programs that recognize constituents who have consistently held a membership over a certain number of years.  However, quickly and easily tracking these specific donor streaks in the midst of all the giving to annual funds and special events used to mean custom code or excessive automation.

The Solution:

• Start by creating a custom number field on the contact record that tracks how many years the person has been a member.  In this example, we’ll call it “Membership Streak.”
• Create a filter with two rules this time, both on the Opportunity object: one to check that “Won” is TRUE, and the other to check that the Record Type ID equals “Membership.”
• Create an Opportunity>Contact(Hard Credit) rollup on the Contact object with the operation “Donor Streak” filtered to only won memberships.  
• NOTE: This configuration assumes that the Primary Contact on the Membership Opportunity is also the member.  You may need to use an Opportunity>Contact(Soft Credit) rollup depending on how you track the person who holds the membership as a result of the gift.  In some orgs, this is an OCR for “Member” to allow gifted memberships.

• Finally, create a boolean (checkbox) formula field that compares the value in the Membership Streak field to the number of years you designate so that the field returns TRUE when the streak is equal to or more than the designated year count.  
• If you want to get really fancy, you can use the Graphics Pack and write a formula that puts a GOLD STAR 🌟 on your Loyalty Member’s highlights panel!

#4: What Did I Just Ask For? The “Last Ask”

The Use Case:

Our last use case goes out to all the Major Gift officers out there.  Maybe you are part of a team in a large nonprofit that is making a lot of asks to a lot of different donors, and you need Salesforce to help you keep it all straight.  Donors can get fatigued if they are over solicited, and feel isolated from your nonprofit and it’s mission if you forget their most recent commitment. Maybe you’re also out in the field A LOT, and you mostly use Salesforce on your mobile device.  You want to quickly see on a donor’s record what was the last solicitation they responded to -- be it pledged or closed, hard or soft credit -- and when it was, so you will be able to go into every meeting ready to recognize their most recent gift and better judge if it’s time to make another ask.  

The Solution:

• This solution will use a set of rollups to find our answer.  First, create a sets of fields on the contact record for the Last Ask Amount, Last Ask Date, and Last Ask Stage.
• Create a filter group with two rules.  The first should be on the Opportunity object’s “Stage” field, and add the stage values your organization uses for Closed Won and Pledged (they may be exactly that, or more, like “Posted”). The second filter rule should be on the Contact Soft Credit, and include in a list those soft credit roles you want to catch.  For example, you may want to include “household donor” so you know if the person is a spouse of someone who recently made a major commitment.
• Now begin creating your rollups.  You will need two sets: one set of Opportunity>Contact(Hard Credit) rollups for the Last Ask Amount, Date, and Stage, and one set of Opportunity>Contact(Soft Credit) rollups for Last Ask Amount, Date, and Stage.  I won’t include pictures of them all here, but here’s the Opportunity>Contact(Hard Credit) rollup for the amount of the most recent gift.

• NOTE: you need TWO sets of rollups because you want to catch the most recent opportunity, whether the donor holds a hard or soft credit, and as we discussed in the last post, customizable rollups can only compute one or the other.
• Finally, you need a clever set of formula fields.  The first compares the two dates and finds the most recent one:

• The next two simply compare this found value, “Last Ask Date” to the Last Ask Date for the Hard Credit and Soft Credit rollups, and when it finds a match, returns the amount and stage respectively.

• One final note: if the donor has both a hard credit and a soft credit for the same day, this configuration will return the first one in the formula… but either way, you’re still recognizing their recent commitment!
• Finally, drop your formula fields for Last Ask Date, Amount, and Stage to a prominent place on the page layout, making it easy to find your mobile device.

In Conclusion: Vive la Customizable Rollups!

In this final installment of my three-part series on Customizable Rollups, I explored some interesting use cases for this awesome feature and showed how you can customize rollups to work with formula fields to extend the functionality of the Nonprofit Success Pack.  Of course we have only scratched the surface, there is so much more this feature can do. I hope you’re inspired to give Customizable Rollups a try in your org!

Are you using the new NPSP Customizable Rollups?  Tell me all about it in the Salesforce Trailblazer Community, or tweet directly at me on Twitter @hayleytuller.