NPSP – Calculating Household Donor StatusPosted: March 6, 2011
Something that is important to HELPSudan and many of the nonprofits I work with is the ability to segment donors by their giving history. I wrote a formula that will categorize Households based on the donation rollups in the new Households package in the NPSP. I thought I’d share for anyone that would find it useful.
IF( npo02__OppAmountThisYear__c > 0, "Current Donor",
IF( npo02__OppAmountLastYear__c > 0, "LYBUNT",
IF( npo02__OppAmount2YearsAgo__c > 0, "SYBUNT (2 years ago)",
IF( npo02__TotalOppAmount__c > 0, "SYBUNT (Lapsed)", "Non Donor"))))
It can easily be modified to show different or additional statuses based on criteria relevant to your organization, or added to Accounts or Contacts as well. Once you have the formula set up, create a summary report and summarize by your new status formula field. Then, you can build a pie or funnel chart to show you how your donors break down.
One other thing… if you’ve ever wanted to know how much someone has given this year relative to this time last year, the formula is simple though not necessarily intuitive.
npo02__OppAmountLastNDays__c - npo02__OppAmountLastYear__c
You could then run reports on donors whose status is “Current Donor” from the first formula, and has a negative value from the second formula (i.e. – they’ve given less money at this time this year than at this time last year). Now, you’ve identified people that are recently engaged in giving and are likely able to give again right now! Salesforce.com formulas, reports, and dashboards are a great way to turn our existing data into actionable items.
The formulas in this post should work via copy/paste with the latest version of the Householding package for the Non Profit Starter Pack.
I hope that a couple organizations find these useful!