In our earlier post, we showed love to Lifetime Value as a tool for guiding fundraising success for your organization. We then shared a step-by-step recipe for extending the Salesforce Nonprofit Success Pack to calculate historical giving data for Contacts up to five years back as well as calculating a Contact's consecutive years of giving. In this how to recipe, we'll walk you through the final steps in calculating Gross Lifetime Value for your organization.
For a full overview of all steps in this series as well as a presentation of the strategy behind them, watch our 45-minute recorded webinar, Boost Donations with Data-Driven Fundraising: Lifetime Value and the Salesforce Nonprofit Success Pack.
Objective
Calculate Gross Lifetime Value for Contacts in the Salesforce Nonprofit Success Pack
Estimated Time
50 - 70 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
- Custom Opportunity fields and User Defined Rollups that add Total Gifts Three Years Ago, Total Gifts Four Years Ago, Total Gifts Five Years Ago, and Consecutive Years Giving fields on the Contact. Learn how >>
Desired Outcomes
- Calculate the Average Annual Giving for each Contact
- Create a Report that calculates Average Donor Lifespan, Average Annual Gift Amount, and Gross Lifetime Value for Contacts
- Display Average Donor Lifespan, Average Annual Gift Amount, and Gross Lifetime Value for your organization in a Dashboard
- Facilitate the easy comparison of Average Donor Lifespan, Average Annual Gift Amount, and Gross Lifetime Value across subsets of Contacts of your choosing to inform fundraising strategy
The End Result
To help guide our process, this is the custom field on the Contact, the Report, and the Dashboard we'll have when all is said and done, along with a bonus option for the extra-enterprising among you:
- Average Annual Giving: A formula field calculating the average amount given per year by a Contact for each of the last five years in which they donated
- Gross Lifetime Value Report: A summary report of Contacts & Accounts calculating Average Donor Lifespan, Average Annual Gift Amount, and Gross Lifetime Value for your organization
- Fundraising Key Performance Indicators Dashboard: A Dashboard with metric components displaying Average Donor Lifespan, Average Annual Gift Amount, and Gross Lifetime Value for your organization
- BONUS: Separate Average Donor Lifespans, Average Annual Gift Amounts, and Gross Lifetime Values for one-time, active recurring, lapsed recurring, and reactivated recurring donors on your Report and Dashboard
Step One: Create a custom field on the Contact object
Assuming you have used our earlier how to recipe to create custom fields and User Defined Rollups for Total Gifts Three Years Ago, Total Gifts Four Years Ago, Total Gifts Five Years Ago, and Consecutive Years Giving fields on the Contact, you have just one formula field to create to calculate Average Annual Giving. After creating it, you may wish to edit your Contact Page Layout to move it to the bottom of the Donations Totals section.
To create this field 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 chart below to create the fields
To create this field 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 chart below to create the fields
Contact Object Custom Field
| Elements | Field Details |
|---|---|
| Field Label: API Name: Data Type: Formula: |
Average Annual Giving Average_Annual_Giving Formula (Currency) IF (OR (npo02__OppAmountThisYear__c > 0, npo02__OppAmountLastYear__c > 0, npo02__OppAmount2YearsAgo__c > 0, Total_Gifts_Three_Years_Ago__c > 0, Total_Gifts_Four_Years_Ago__c > 0, Total_Gifts_Five_Years_Ago__c > 0), |
Step Two: Create a Report to calculate Average Donor Lifespan, Average Annual Gift Amount, and Gross Lifetime Value
To create this Report:
- Go to the Reports tab in Salesforce and click "New Report..."
- Under Select Report Type, click Accounts & Contacts
- Click Contacts & Accounts from the expanded list
- Click Create
- For Show, select All Accounts
- For Date Field > Range, select "All Time"
- For each of the following, click Add next to Filters and create each filter:
- Total Gifts Five Years Ago greater than "0"
- Total Gifts Four Years Ago greater than "0"
- Total Gifts Three Years Ago greater than "0"
- Total Gifts Two Years Ago greater than "0"
- Total Gifts Last Year greater than "0"
- Total Gifts This Year greater than "0"
- Click the down arrow to the right of Add next to Filters and select Filter Logic
- For Filter Logic, replace all ANDs with ORs (e.g., 1 OR 2 OR 3 OR 4 OR 5 OR 6) and click OK
- In the Preview pane, click Remove All Columns
- Using the Fields column on the left, drag and drop each of the following fields into the Preview pane:
- First Name
- Last Name
- Account Name
- Consecutive Years Giving
- Average Annual Giving
- Mouse over Consecutive Years Giving and select Summarize This Field... in the drop down
- Check Average in the popup
- Click Apply
- Mouse over Average Annual Giving and select Summarize This Field... in the drop down
- Check Average in the popup
- Click Apply
- In the Fields column on the left, drag and drop Add Formula into the Preview pane
- In the Custom Summary Formula popup, enter the following values:
- Column Name: Gross Lifetime Value
- Format: Currency
- Decimal Places: 2
- Where will this formula be displayed: All summary levels
- Formula:
Contact.Consecutive_Years_Giving__c:AVG * Contact.Average_Annual_Giving__c:AVG
- Click OK
- In the Preview pane, choose Summary Format in the drop down to the right of the Preview section title
- From the Fields column on the left, select a field by which you wish to summarize the report data and drag and drop it into the section of the Preview pane with the text "Drop a field here to create a grouping." Whichever field you choose will summarize your key performance indicators for each data group. If you followed our earlier recipes to create the Recurring Donor Status field, you can select it to calculate Average Donor Lifespan, Average Annual Gift Amount, and Gross Lifetime Value for one-time, active recurring, lapsed recurring, and reactivated recurring donors. If you are unsure which field you wish to summarize on at this time, select Account Name for the time being. You can update this later on, as you wish.
- Click Save
- In the Save Report popup, enter the following:
- Report Name: Gross Lifetime Value
- Report Unique Name: Gross_Lifetime_Value
- Report Description: Summary report calculating Average Donor Lifespan, Average Annual Gift Amount, and Gross Lifetime Value over the last five years
- Report Folder: Select a location of your choosing
- Click Save and Run Report
- Review the Report to locate the values for Average Donor Lifespan, Average Annual Gift Amount, and Gross Lifetime Value
Step Three: Create a Dashboard to display Average Donor Lifespan, Average Annual Gift Amount, and Gross Lifetime Value
With the Report created, you can now create a Dashboard to display the Average Donor Lifespan, Average Annual Gift Amount, and Gross Lifetime Value. If you chose to add a summary group that is of interest, you can also add these metrics to the Dashboard. To do so:
- Go to the Reports tab in Salesforce and click "New Dashboard..."
- From the Components column on the left, drag and drop a Metric component to the top of each of the three Dashboard columns
- Click the Data Sources tab in the left column
- Click Reports to expand it
- Find your Gross Lifetime Value report and drag and drop it onto the Metric component in the left column
- Edit the header of the Metric component to read "Average Donor Lifespan"
- Edit the empty text field in the middle of the Metric component to read "Average Donor Lifespan"
- Edit the footer of the Metric component to read "Includes all gifts within current calendar year and five previous calendar years"
- Click the wrench icon in the upper right of the Metric component
- In the Component Editor, click the Component Data tab
- For Value, select Average Consecutive Years. NOTE: Your Average Consecutive Years value will display differently for a Dashboard viewed in Classic vs. Lightning. In Lightning, it will show decimal places. In Classic, it will show only whole numbers. To view the decimals in Classic, you will need to click through to the underlying Report.
- Click OK
- From the Data Sources tab in the left column, find your Gross Lifetime Value report and drag and drop it onto the Metric component in the middle column
- Edit the header of the Metric component to read "Average Annual Gift Amount"
- Edit the empty text field in the middle of the Metric component to read "Average Annual Gift Amount"
- Edit the footer of the Metric component to read "Includes all gifts within current calendar year and five previous calendar years"
- Click the wrench icon in the upper right of the Metric component
- In the Component Editor, click the Component Data tab
- For Value, select Average Average Annual Giving. NOTE: Your Average Annual Giving value will display differently for a Dashboard viewed in Classic vs. Lightning with the former displaying the $ sign and the latter not.
- Click OK
- From the Data Sources tab in the left column, find your Gross Lifetime Value report and drag and drop it onto the Metric component in the right column
- Edit the header of the Metric component to read "Gross Lifetime Value"
- Edit the empty text field in the middle of the Metric component to read "Gross Lifetime Value"
- Edit the footer of the Metric component to read "Includes all gifts within current calendar year and five previous calendar years"
- Click the wrench icon in the upper right of the Metric component
- In the Component Editor, click the Component Data tab
- For Value, select Gross Lifetime Value. NOTE: Your Gross Lifetime Value will display differently for a Dashboard viewed in Classic vs. Lightning with the former displaying the $ sign and the latter not.
- Click OK
- Click Save
If you wish to display totals for Average Donor Lifespan, Average Annual Gift Amount, and Gross Lifetime Value by grouping in your Report, you can repeat the process above using the Table component rather than the Metric component. Each Table component can be placed under its corresponding Metric component, as shown in the Dashboard screenshot above.
Next Steps
Watch our 45-minute recorded webinar for key strategy discussions that guide using Lifetime Value and learn how you can calculate net Lifetime Value to account for donor acquisition and maintenance costs: 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
- BLOG: Boost Donations with Data-Driven Fundraising: Calculating and Using Lifetime Value
Donations
Events
Petitions
Forms
Shop
Directories