Looking to report on giving during the 2017 end-of-year season? We've got you covered! With this easy step-by-step recipe, you can report on year-end giving from those who gave for the first time last end-of-year seasons vs. previous donors who gave during that same period - complete with a helpful array of averages, totals, minimums and maximums per cohort.

Objective

Create a custom Report in the Salesforce Nonprofit Success Pack that shows gifts from new donors vs. previous donors during the 2017 giving season

Estimated Time

30 - 40 minutes

Requirements

• Salesforce.org Nonprofit Success Pack
• Use of the Donations (aka Opportunities) objects to track donations
• Rudimentary familiarity with creating custom fields and Reports in Salesforce

Desired Outcomes

• Chart gifts from new donors vs. previous donors during last year's giving season which will be defined as starting with #GivingTuesday and concluding December 31st
• Calculate the average gift amount, largest gift amount, smallest gift amount, and total gifts for gifts during last year's giving season and aggregate by new donors and previous donors

The End Result

The chart attached in the first comment on this thread is from a custom Report on Opportunities and shows the breakdown of gifts by new donors vs. previous donors during the 2017 giving season, where "new donor" is defined as someone who gave for the first time ever during that holiday giving season. Totals, averages, minimums and maximums are shown for all donations during last year's end-of-year giving season at the top of the chart with the Report having these metrics for new vs. previous donors.

Step One: Create custom field on the Opportunity object

Since we're choosing to create our Report based on the Opportunity object, we need to determine which Opportunity records represent first donations from those new donors. Fortunately, the date of a Contact's first gift already exists in the Salesforce Nonprofit Success Pack in the First Gift Date field. We'll use that date in a new formula field on the Opportunity to determine if a given Opportunity record is the first. We'll also add in some logic so the field is only populated if the Opportunity is a donation. The formula below is specific to an NPSP instance using the NPSP Default record type and Soapbox Engage's Donations app for donation records. You can modify those record types to meet your needs.

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 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 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 chart below to create the fields

Opportunity Object Custom Field

Field Label: Is First Gift Ever

API Name: Is_First_Gift_Ever__c

Data Type: Formula (Text)

Formula: IF ( CloseDate = npsp__Primary_Contact__r.npo02__FirstCloseDate__c, IF ( OR (RecordType.Name = "Soapbox Donation", RecordType.Name = "Soapbox Recurring Donation", RecordType.Name = "NPSP Default"), "true", "false"), "false")

Step Two: Create a custom Report

With our Is First Gift Ever custom Opportunity field created, we can create our Report:

• Go to the Reports tab
• Click New Report
• For Select Report Type, choose Opportunities
• Click Create
• For Show, confirm "All Opportunities" is selected
• For Date Field > Range, select "Custom"
• For From, enter 11/28/2017 which was #GivingTuesday in 2017. This date can be updated for future year's reporting.
• For To, enter 12/31/2017
• Click Filter > Add
• In the new Filter form that appears:
  • Next, choose "Won" from the field drop down
  • Select "equals" for the operator
  • Enter "true" in the text field
  • Click OK
• Click Save and enter in the following values in the popup window and click Save:
  • Report Name: New v Previous Donors Last Giving Season
  • Report Unique Name: New_v_Previous_Donors_Last_Giving_Season
  • Report Description: Donations from New v Previous Donors During Last Giving Season
  • For Report Folder, select the folder of your choice
  • Click Save

Step Three: Change your Report to a Summary Report and Create a Bucket

• Next to the Preview header, click Tabular Format and select Summary
• In the Fields left column, double-click Add Bucket Field In the popup for Source Column, select Is First Gift Ever
• For Bucket Name Field, enter "New vs. Previous" without the quotes
• In the search field to the right, enter "true" without the quotes and click Search
• Tick the checkbox for "True" in the results view
• Click the down arrow next to Move To
• For the Create New Bucket field, enter "Donations from New Donors" without the quotes
• Click Enter
• In the search field to the right, enter "false" without the quotes and click Search
• Tick the checkbox for "True" in the results view
• Click the down arrow next to Move To
• For the Create New Bucket field, enter "Donations from Previous Donors" without the quotes
• Click Enter
• Drag and drop your new New vs. Previous bucket field where it says "Drop a field here to create a grouping."
• Hover over the Amount column heading and click the down arrow
• Click Summarize this field...
• Tick off all of the summary options
• Click Apply
• Save your report

Step Four: Modify the Report's chart

To create the chart noted in the attachment in Lightning, edit the default chart as follows:

• Display As: Donut Chart
• Chart Title: New v Previous Donors 2017 Giving Season
• Label Groups: Values
• Cumulative: check
• Add: Group
• Save your Report

Clarification

This Report is structured such that, if a new donor during last year-end giving season donated multiple times during that giving season, their first gift will be included in the "Donations from New Donors" cohort and their subsequent gifts in the "Donations from Previous Donors" cohort.