In our previous post, we sang the praises of Lifetime Value as a powerful metric that can guide fundraising success for your organization. As we noted, to calculate Lifetime Value, you'll need to know your average donor lifespan, ideally for up to five years back. If you happen to be using the Salesforce Nonprofit Success Pack, you're well on your way to getting that number. In this how to recipe, we'll walk you through a few steps that surface this on the Contact record for easy calculation.
For a full overview of all steps in this series as well as a presentation of the strategy behind them, watch our power-packed 45-minute recorded webinar, Boost Donations with Data-Driven Fundraising: Lifetime Value and the Salesforce Nonprofit Success Pack.
Objective
Create custom fields and User Defined Rollups in the Salesforce Nonprofit Success Pack (NPSP) to calculate Consecutive Years Giving for a Contact and enable calculation of average donor lifespan
Estimated Time
20 - 30 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
Desired Outcomes
- Expand annual giving total data in the Nonprofit Success Pack to include total gifts for three, four, and five years ago on the Contact record
- Calculate Consecutive Years Giving for up to six years (current year plus five previous) and display in a field on the Contact record
- Facilitate the calculation of your nonprofit's average donor lifespan across all Contacts
The End Result
To help guide our process, these are the fields we want to have on the Contact at the conclusion of our process:
- Total Gifts Three Years Ago: Dollar value of all donations from this Contact given during the year three years ago
- Total Gifts Four Years Ago: Dollar value of all donations from this Contact given during the year four years ago
- Total Gifts Five Years Ago: Dollar value of all donations from this Contact given during the year five years ago
- Consecutive Years Giving: Number of years in a row the Contact has donated for up to six years (current year plus five previous)
Step One: Create custom fields on the Opportunity object
In the Nonprofit Success Pack, you already have fields for Total Gifts Last Year, Total Gifts This Year, and Total Gifts Two Years Ago on the Contact. We'll just need to capture the same data for three, four and five years ago to round out our period of interest. We'll do this by creating source fields for User Defined Rollups we'll create which are specific to each of those years. As noted in an earlier recipe, User Defined Rollups in the NPSP are incredibly flexible but need a little help to rollup only the records you want. To provide that help, we'll create three formula fields on the Opportunity object that populate with the Amount of the Opportunity if it has a Close Date within each of the years we want.
To create this field 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 field
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 field
Opportunity Object Custom Fields
| Elements | Field Details |
|---|---|
| Field Label: API Name: Data Type: Formula: |
Gift Amount if Three Years Ago Gift_Amount_if_Three_Years_Ago Formula (Currency) IF ( YEAR (CloseDate) = ( YEAR (TODAY()) - 3 ), Amount, NULL) |
| Field Label: API Name: Data Type: Formula: |
Gift Amount if Four Years Ago Gift_Amount_if_Four_Years_Ago Formula (Currency) IF ( YEAR (CloseDate) = ( YEAR (TODAY()) - 4 ), Amount, NULL) |
| Field Label: API Name: Data Type: Formula: |
Gift Amount if Five Years Ago Gift_Amount_if_Five_Years_Ago Formula (Currency) IF ( YEAR (CloseDate) = ( YEAR (TODAY()) - 5 ), Amount, NULL) |
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. We'll also create a formula field which will calculate the number of unbroken years of giving. We'll create this formula to start counting this year for anyone who has already given in the current calendar year - or last year, if someone gave last year but has not yet given in the current year. For the details of the formula itself, we happily give Beth Breisnes of Soapbox Engage partner Bigger Boat Consulting all the credit!
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 add these fields. They should fit nicely in the Donations Total section under Total Gifts Two Years Ago.
Contact Object Custom Fields
| Elements | Field Details |
|---|---|
| Field Label: API Name: Data Type: |
Total Gifts Three Years Ago Total_Gifts_Three_Years_Ago Currency (16, 2) |
| Field Label: API Name: Data Type: |
Total Gifts Four Years Ago Total_Gifts_Four_Years_Ago Currency (16, 2) |
| Field Label: API Name: Data Type: |
Total Gifts Five Years Ago Total_Gifts_Five_Years_Ago Currency (16, 2) |
| Field Label: API Name: Data Type: Formula: |
Consecutive Years Giving Consecutive_Years_Giving Formula (Number) /* Donated in current year */ |
Step Three: Create User Defined Rollups
To populate the new total gifts fields on the Contact with the sum of Opportunities for the Contact, we'll create our User Defined Rollup 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 |
|---|---|---|---|
| Gift Amount if Three Years Ago | SUM | Contact | Total Gifts Three Years Ago |
| Gift Amount if Four Years Ago | SUM | Contact | Total Gifts Four Years Ago |
| Gift Amount if Five Years Ago | SUM | Contact | Total Gifts Five Years Ago |
Considerations
If you wish, you can create additional fields and User Defined Rollups to surface historical giving data on the Contact record for more than five years. Go back as far as you wish! For our purposes of calculating average donor lifespan, five years should be sufficient.
Next Steps
In an upcoming blog post, we'll offer details on how you can create a Report that calculates average donor lifespan. We also share key strategy discussions in our recorded webinar Boost Donations with Data-Driven Fundraising: Lifetime Value and the Salesforce Nonprofit Success Pack.
Related content:
- WEBINAR RECORDING: Boost Donations with Data-Driven Fundraising: Lifetime Value and the Salesforce Nonprofit Success Pack
- SALESFORCE HOW TO BLOG: Calculate Consecutive Years Giving using User Defined Rollups in Salesforce Nonprofit Success Pack
- SALESFORCE HOW TO BLOG: Calculate Gross Lifetime Value for Contacts in Salesforce Nonprofit Success Pack
- BLOG: Boost Donations with Data-Driven Fundraising: Calculating and Using Lifetime Value
Donations
Events
Petitions
Forms
Shop
Directories