Do you know how loyal your donors are?
One key metric of donor loyalty is to look at how many consecutive years they have donated. Some folks call this concept a donor’s “streak”. I first heard about this metric and the desire to include it as a field on a donor’s Contact record in Salesforce at the NPSP Community Sprint in 2015. At that time, we believed that it would require code to calculate, and the idea got backburnered. (This same concept also applies to questions like, “How many years running has a contact attended at least one event?” or “How many years running has a contact had at least one engagement?” It essentially applies to any child object that has a date field. What “streaks” are meaningful to your organization?)
Recently, I heard about a clever use of Declarative Lookup Rollup Summaries from the always ingenious Katie McFadden. She used DLRS’ “Concatenate Distinct” rollup function to display the unique years a donor has donated into a text field on the Contact. So if a donor gave twice in 2012, once in 2014, three times in 2015, and once in 2017, the field “Years Donated” would read “2012, 2014, 2015, 2017”.
Then the lightbulb went off. I realized I could write a formula field — albeit a complex one! — that uses Katie’s “Years Donated” field to calculate how many consecutive years the donor has given leading up to this year, or leading up to last year if they haven’t given yet this year but did give last year.
So I present to you, Donor Streak with NO CODE!*
- Create a text field on Contacts called “Years Donated”. Whether you put this on page layouts is up to you; it’s not strictly necessary for calculating the Donor Streak number, but your users may find it helpful it its own right.
- Create a number field on Opportunities called “Year”. This is just a helper field for DLRS; you don’t need to put it on any page layouts.
- Set up a DLRS like this:
(Don’t have DLRS yet? What are you waiting for? Grab the latest install link here and hop over to the Hub to see some amazing use cases!) - Create a formula field (data type number) on Contacts called “Donor Streak”. This one definitely goes on your relevant page layout(s). Here’s the formula:
https://gist.github.com/bethbrains/bbcc4c45f9b731e27a7bdd70e2f7c3e3
Don’t be intimidated! Yes, it’s a complex one, but it’s just a series of nested IF statements that check each year going back from this year (top block) or last year (bottom block) and adding 1 for each year they’ve donated until it hits a year they haven’t donated, at which point it stops counting.
As it appears above, the streak counting maxes out at 13.** Going any further hits the formula compile size limit.
Ta-da! Donor Streak without breaking a code-y sweat. Now go cultivate those loyal donors!
*To be fair, DLRS is actually code, but it’s code that’s generated automatically by Andy’s app, so you don’t have to touch it. The configuration you do is all clicks.
**I have a few ideas about this. You could re-do this slightly as a text formula instead of a number formula, and get it to return “13+” instead of just “13”. You could leave this field as is but make a second formula field, this time data type text, that returns the Donor Streak number but returns “13+” for “13”. This would be potentially helpful for making it clear to your fundraisers that “13” may actually mean more than 13, but I do like having the Donor Streak number be an actual number for reporting purposes.