Display recurring donation data on Salesforce Contacts using User Defined Rollups in the NPSP

To effectively nurture sustaining donors, you want more than smart strategy. You need to get the most from a quality constituent relationship management system (CRM) to aid in the effort. In the first of a series of posts, we offer specific step-by-step recipes for extending the Salesforce Nonprofit Success Pack (NPSP) to be a sustaining donor cultivation powerhouse. Today, we dive into how you can use the NPSP and its User Defined Rollups to present recurring donation information directly on the Contact record.

For a full overview of all steps in this series as well as a presentation of the strategy behind them, check out our recorded webinar Convert One-Time Donors to Sustaining Supporters: Strategy, Tactics, and the Salesforce Nonprofit Success Pack.

Objective

Create custom fields and User-Defined Rollups in the Salesforce Nonprofit Success Pack (NPSP) to display recurring donation data on the Contact record

Estimated Time

30 - 45 minutes

Requirements

Salesforce Nonprofit Success Pack
Use of the Donations (aka Opportunities) and Recurring Donations objects to track donations
Rudimentary familiarity with creating custom fields and editing page layouts in Salesforce
[Optional] Fields capturing credit card expiration month and date for recurring donations on the Donation record

Desired Outcomes

Prioritize sustaining donor engagement by displaying recurring donation data on the Contact
Aggregate pertinent current recurring giving and lifetime recurring giving data in one place on the Contact so users aren't required to view multiple Recurring Donation and Donation records
Facilitate the building of Levels, Engagement Plans and workflow automations specific to recurring donors to streamline engagement efforts and maximize donations
Distinguish between current, lapsed, and reacquired monthly donors, allowing for more targeted donor cultivation

The End Result

Salesforce Nonprofit Success Pack User-Defined Rollups

To help guide our process, these are the fields we want to have on the Contact at the conclusion of our process:

Recurring Donation Amount: Dollar value of any currently active open recurring donation, if one exists
First Ever Recurring Date: Date of the first installment of a Contact's initial recurring donation
Last Ever Recurring Date: Date of the most recent installment of any recurring donation
First Active Recurring Date: Date of the first installment of a Contact's currently active open recurring donation, if one exists
Last Active Recurring Date: Date of the most recent installment of a Contact's currently active open recurring donation, if one exists
[Optional] Credit Card Expiration Date: Last date of the expiration month of the credit card used for an active open recurring donation
[Optional] Days Until Expired: Number of days before the credit card expires

Step One: Create custom fields on the Opportunity object

User Defined Rollups in the NPSP allow you to calculate average, total, maximum, or minimum values from date, currency, or number fields on Opportunity records, and save these values to fields on an associated Contact, Account, or Household. These User Defined Rollups mimic the behavior of standard rollups in the NPSP that calculate values such as First Gift Date, Last Gift Date, and Total Gifts. They include only Closed Won Opportunities and exclude any Opportunity Record Types or Opportunity Types which are excluded in NPSP Settings > Donor Statistics > Opportunity Rollup Record Types. User Defined Rollups do not, however, allow for any filtering of Opportunity records so all Opportunities associated with a given Contact, Account, or Household that have populated values on the field in question are included, regardless of Record Type.

We're only interested in Opportunities associated with Recurring Donations. Even more specifically, we're going to concern ourselves primarily with recurring donations that have an indefinite end date, or, specifically, an Open Ended Status of "Open" in their Recurring Donation record. To allow us to include only Opportunities related to Recurring Donations in our rollups, we'll create three formula fields noted in the table below. "Recurring Date: Active" will be populated with the Close Date of the Opportunity, if that Opportunity is associated with a Recurring Donation with a Open Ended Status of "Open." "Recurring Date: Ever" will be populated with the Close Date of the Opportunity, if that Opportunity is associated with any Recurring Donation. "Recurring Donation Amount" will be populated with the Amount of the Recurring Donation associated with the Opportunity, if that Recurring Donation has an Open Ended Status of "Open." These formula fields will be blank if the criteria aren't met, effectively excluding them from the rollups we'll create.

As an optional field below, we've also included a formula field which can translate two separate number fields for credit card expiration month and year into a date value representing the final day of the expiration month. We'll use this date value on the Contact to provide an easy means of tracking whose credit card is set to expire soon. Our example uses Soapbox Engage's Opportunity fields in the formula but you can use whichever fields you might have to track expiration month and year.

To create these fields in Salesforce Classic:

Go to Setup
In the left column, click Customize
Click Opportunities
Click Fields
In the Opportunity Custom Fields & Relationships section, click New
For each step in the process, use the values in the Opportunity Object Custom Fields charts below to create the fields
Repeat until all fields are added

To create these fields in Salesforce Lightning:

Go to Setup
Enter "Object" in the Quick Find box in left column
Select Object Manager under the Objects and Fields menu
Scroll down the list and click Opportunities
Click Fields
In the Opportunity Custom Fields & Relationships section, click New
In the Fields & Relationship section, click New
For each step in the process, use the values in the Opportunity Object Custom Fields charts below to create the fields
Repeat until all fields are added

Opportunity Object Custom Fields

Elements	Field Details
Field Label: API Name: Data Type: Formula:	Recurring Date: Active Recurring_Date_Active__c Formula (Date) `IF ( ISPICKVAL ( npe03__Recurring_Donation__r.npe03__Open_Ended_Status__c, "Open" ), CloseDate , NULL)`
Field Label: API Name: Data Type: Formula:	Recurring Date: Ever Recurring_Date_Ever__c Formula (Date) `IF ( NOT ( ISBLANK(npe03__Recurring_Donation__c) ) , CloseDate , NULL)`
Field Label: API Name: Data Type: Formula:	Recurring Donation Amount Recurring_Amount__c Formula (Currency) `IF ( ISPICKVAL ( npe03__Recurring_Donation__r.npe03__Open_Ended_Status__c, "Open" ), npe03__Recurring_Donation__r.npe03__Amount__c , NULL)`

Optional Field for Credit Card Expiration

Elements Field Details

Field Label:
API Name:
Data Type:
Formula: Credit Card Expiration Date
Credit_Card_Expiration_Date__c
Formula (Date)
IF ( AND ( ISPICKVAL ( npe03__Recurring_Donation__r.npe03__Open_Ended_Status__c, "Open" ), sbx_CreditCardExpirationYear__c > 0, sbx_CreditCardExpirationMonth__c > 0 ), DATE(sbx_CreditCardExpirationYear__c, (sbx_CreditCardExpirationMonth__c) + 1, 1) - 1, null)
NOTE: Replace the sbx_... fields noted above with your own Credit Card Expiration Month and Credit Card Expiration Year fields, if not using Soapbox Engage

Elements	Field Details
Field Label: API Name: Data Type: Formula:	Credit Card Expiration Date Credit_Card_Expiration_Date__c Formula (Date) `IF ( AND ( ISPICKVAL ( npe03__Recurring_Donation__r.npe03__Open_Ended_Status__c, "Open" ), sbx_CreditCardExpirationYear__c > 0, sbx_CreditCardExpirationMonth__c > 0 ), DATE(sbx_CreditCardExpirationYear__c, (sbx_CreditCardExpirationMonth__c) + 1, 1) - 1, null)` NOTE: Replace the sbx_... fields noted above with your own Credit Card Expiration Month and Credit Card Expiration Year fields, if not using Soapbox Engage

Step Two: Create custom fields on the Contact object

With our Opportunity source fields created, let's create the target fields on the Contact object where our rollup data will go. Most are simple fields. One optional field is a formula field which takes our expiration date value and calculates how many days from today remain before expiration.

To create these fields in Salesforce Classic:

Go to Setup
In the left column, click Customize
Click Contacts
Click Fields
In the Contact Custom Fields & Relationships section, click New
For each step in the process, use the values in the Contact Object Custom Fields charts below to create the fields
Repeat until all fields are added

To create these fields in Salesforce Lightning:

Go to Setup
Enter "Object" in the Quick Find box in left column
Select Object Manager under the Objects and Fields menu
Scroll down the list and click Contacts
Click Fields
In the Contact Custom Fields & Relationships section, click New
In the Fields & Relationship section, click New
For each step in the process, use the values in the Contact Object Custom Fields charts below to create the fields
Repeat until all fields are added

As the finishing touch for the Contact object, edit your Contact Page Layout to create a new section called Recurring Donation Information and add the new fields there.

Contact Object Custom Fields

Elements	Field Details
Field Label: API Name: Data Type:	First Active Recurring Date First_Active_Recurring_Date__c Date
Field Label: API Name: Data Type:	First Ever Recurring Date First_Ever_Recurring_Date__c Date
Field Label: API Name: Data Type:	Last Active Recurring Date Last_Active_Recurring_Date__c Date
Field Label: API Name: Data Type:	Last Ever Recurring Date Last_Ever_Recurring_Date__c Date
Field Label: API Name: Data Type:	Recurring Donation Amount Recurring_Donation_Amount__c Currency (16, 2)

Optional Fields for Credit Card Expiration

Elements	Field Details
Field Label: API Name: Data Type:	Credit Card Expiration Date Credit_Card_Expiration_Date__c Date
Field Label: API Name: Data Type: Formula:	Days Until Expired Days_Until_Expired__c Formula (Number) `Credit_Card_Expiration_Date__c - TODAY()`

Step Three: Create User Defined Rollups

To bring it all together, we'll create our User Defined Rollups in the NPSP. To do so:

In the Nonprofit Success Pack app, click the NPSP Settings tab
Click Donations
Click User Defined Rollups
Click New User Defined Rollup
Use the table below, enter the appropriate values
Click Save
Repeat until all rollups are added

User Defined Rollups

Opportunity Field	Rollup Operation	Target Object	Target Field
Recurring Date: Active	MIN	Contact	First Active Recurring Date
Recurring Date: Ever	MIN	Contact	First Ever Recurring Date
Recurring Date: Ever	MAX	Contact	Last Ever Recurring Date
Credit Card Expiration Date	MAX	Contact	Credit Card Expiration Date
Recurring Donation Amount	MAX	Contact	Recurring Donation Amount
Recurring Date: Active	MAX	Contact	Last Active Recurring Date

Possible Additions or Modifications

If you wish, you can use this same process to display this data on the Account or Household record. You can also create numerous other User Defined Rollups to calculate values such as Total Recurring Gifts or Total Number of Recurring Gifts.

Next Steps

We shared key strategy discussions in our recorded webinar Convert One-Time Donors to Sustaining Supporters: Strategy, Tactics, and the Salesforce Nonprofit Success Pack on Thursday, March 16th at 3 pm ET. Watch for full details!

Comments? Suggestions? Feedback of any kind for the good of the order? Feel free to share them on this Salesforce Power of Us thread (login required)!

Soapbox Engage Blog

Online engagement news and strategies for changemakers