For those looking to make sense of your 2017 fundraising performance, we've got this handy how-to recipe that reports on giving by new donors vs. previous donors by month - complete with a purty little chart and all. Use it to analyze giving trends last year and plan for this year with a clear-eyed understand of the donors you gained and the donors you kept.

Objective

Create a custom Report in the Salesforce Nonprofit Success Pack with a chart that displays new donors acquired vs. previous donors cumulatively by month for the previous year

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 new donor acquisition vs. previous donors who gave last year cumulative by month
• Calculate the average gift amount, largest gift amount, smallest gift amount, and total gifts for the first gift last year of new donors vs. previous donors for the previous year and by each month

The End Result

The chart shared in the comment below is from a custom Report on Opportunities and shows the cumulative number of new donors vs. previous donors who gave last year by month. While this graph could have been created through a Report on the Contact object in the Salesforce Nonprofit Success Pack, we chose to use the Opportunity object to capture metrics at the top of the chart. These summarize the statistics for donors' first gift of last year, aggregated by new donors 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 "Close Date" > Range, select "Previous CY"
• Click Filters > Add drop down and select Field Filter
• In the new Filter form that appears:
  • Choose "Is First Gift of Year" from the field drop down
  • Select "equals" for the operator
  • Enter "true" in the text field
  • Click OK
  • 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 vs. Previous Donors by Month Last Yr
  • Report Unique Name: New_vs_Previous_Donors_by_Month_Last_Yr
  • Report Description: New donors vs. previous donors cumulative by month for previous year
  • For Report Folder, select the folder of your choice
  • Click Save

Step Three: Change your Report to a Summary Report and add a bucket

• Next to the Preview header, click Tabular Format and select Summary
• Drag and drop the Close Date field to where it says "Drop a field here to create a grouping."
• Hover over your new Close Date group and click the down arrow
• For Group Dates By, select Calendar Month in Year
• Click OK
• 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 "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 "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." under the Close Date 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 customize the Report's chart in Lightning Experience, edit the default chart as follows:

• Display As: Click Line Chart
• Chart Title: New vs. Previous Donors Last Year
• Y-Axis: Record Count
• Cumulative: check
• Add: Group
• Save your Report