Consecutive years of giving/membership

Hi Trini,

Assuming the Dec19 contribution is to a put toward a 2020 campaign, and the Jan21 toward a 2021 campaign, such that despite the gap in years of contribution dates, the campaign fiscal years are without gaps, then I can see a fairly easy way to get at this in Analytics. If not, I'd have to give it more of a think of how you might define consecutive as within 13 months, but maybe this will be close enough, or enough to get you started. 

Here's a dashboard filtered on FY 2021 and a Contribution Amount > 0

consecutive_2D00_years_2D00_of_2D00_giving_2D00_membership.dash

The rightmost widget started as a list of constituents that had given to FY 2021, but then expanded to show the last 5 FY, with a widget filter that limits the results to those with a Contribution Amount in FY 2021 > 0. So everyone has a value in the 2021 column, and then we can see the continuity (or not) back 4 years.

Then to turn those rows of continuity into a single calculation, a conditional CASE statement was added. If the constituent has 5 distinct FY when filtered to only 5 FY, then they have 5 years of concurrent giving. If they have 4 distinct FY when filtered to only the past 4 FY of giving, then they'll be counted as having 4 consecutive years (the 5s aren't double counted in the 4 bin with this kind of statement). This could be easily filtered to one specific Consecutive Years result, and click on the Consecutive Years grand total to Jump To the _Constituents dashboard for list saving from Analytics.

To pivot that around to counting constituents in each bucket of consecutive yearness, we need a series of formulas like this... taking each of those WHEN conditions and make a value out of it, that is grouped by Constituent ID.

And then just for good measure, there's a single, stacked column in that leftmost widget. A nice thing about this is that dashboard filters for Membership Organization or Campaign Category (included here, but disabled by default), work correctly to recalculate the concurrency on the fly.

Best,
Chris

Hi Trini,

Assuming the Dec19 contribution is to a put toward a 2020 campaign, and the Jan21 toward a 2021 campaign, such that despite the gap in years of contribution dates, the campaign fiscal years are without gaps, then I can see a fairly easy way to get at this in Analytics. If not, I'd have to give it more of a think of how you might define consecutive as within 13 months, but maybe this will be close enough, or enough to get you started. 

Here's a dashboard filtered on FY 2021 and a Contribution Amount > 0

consecutive_2D00_years_2D00_of_2D00_giving_2D00_membership.dash

The rightmost widget started as a list of constituents that had given to FY 2021, but then expanded to show the last 5 FY, with a widget filter that limits the results to those with a Contribution Amount in FY 2021 > 0. So everyone has a value in the 2021 column, and then we can see the continuity (or not) back 4 years.

Then to turn those rows of continuity into a single calculation, a conditional CASE statement was added. If the constituent has 5 distinct FY when filtered to only 5 FY, then they have 5 years of concurrent giving. If they have 4 distinct FY when filtered to only the past 4 FY of giving, then they'll be counted as having 4 consecutive years (the 5s aren't double counted in the 4 bin with this kind of statement). This could be easily filtered to one specific Consecutive Years result, and click on the Consecutive Years grand total to Jump To the _Constituents dashboard for list saving from Analytics.

To pivot that around to counting constituents in each bucket of consecutive yearness, we need a series of formulas like this... taking each of those WHEN conditions and make a value out of it, that is grouped by Constituent ID.

And then just for good measure, there's a single, stacked column in that leftmost widget. A nice thing about this is that dashboard filters for Membership Organization or Campaign Category (included here, but disabled by default), work correctly to recalculate the concurrency on the fly.

Best,
Chris

Wow Chris.  This is very helpful.  I'll dig in more and share with my team.  This just made my weekend.  Happy Friday!

I'm looking into a dashboard similar to this and it's been a great help to start with. I was trying to export the dashboard you attached but it did not work. If possible, would you be able to type out the Case statement? I'm trying to make sense of what Campaign Fiscal Year1, Campaign Fiscal Year2, etc, are coming from in the list.

Thank you.

Hi Amanda,

We unfortunately cannot copy/paste a formula from a community forum post into an Analytics formula editor and have it translate. When we copy a formula out of the editor into a forum post, in only provides the text of the formula and doesn't include all the other stuff it needs to execute in a given organization's Analytics (server names and such).

That said, the formula for the first widget with Constituent ID on rows, which is from the perspective of 2021 being the current fiscal year, is

SUM (
   CASE WHEN ( [# unique Campaign Fiscal Year] , [Campaign Fiscal Year is 2021, 2020, 2019, 2018, or 2017] ) = 5 THEN 5
   WHEN ( [# unique Campaign Fiscal Year] , [Campaign Fiscal Year is 2021 ,2020, 2019, or 2018] ) = 4 THEN 4
   WHEN ( [# unique Campaign Fiscal Year] , [Campaign Fiscal Year is 2021 ,2020, or 2019] ) = 3 THEN 3
   WHEN ( [# unique Campaign Fiscal Year] , [Campaign Fiscal Year is 2021, or 2020] ) = 2 THEN 2
   WHEN ( [# unique Campaign Fiscal Year] , [Campaign Fiscal Year is 2021] ) = 1 THEN 1
   ELSE NULL END
)

In plainspeak, for a constituent, if the count of distinct campaign fiscal years between 2017 and 2021 against which they have given gifts is 5, then they have given in all 5 consecutive years. When evaluating a CASE, the result from first true statement in the list is returned. So, if the first statement isn't true, but the second statement is true, then they gave in that past 4 consecutive years, but did not give in 2017.

Hope that helps!

(edited to correct formula per this comment)

Thank you so much for the explanation! I understand how the formula is written but my knowledge is still quite limited when it comes to building formulas. The error message I received below, I'm not sure what it means. Does including spaces effect the formula or am I missing a parenthesis somewhere?

Thanks again!

My bad... apologies for that Amanda. Please remove "CASE" from all but the first line... I'm going to edit my above post to show that as well and avoid confusion in the future.

SUM(
 CASE WHEN ( [# of unique Campaign Fiscal Year] , [Campaign Fiscal Year1] ) = 5 THEN 5
 WHEN ( [# of unique Campaign Fiscal Year] , [Campaign Fiscal Year2] ) = 4 THEN 4 
 WHEN ( [# of unique Campaign Fiscal Year] , [Campaign Fiscal Year3] ) = 3 THEN 3 
 WHEN ( [# of unique Campaign Fiscal Year] , [Campaign Fiscal Year4] ) = 2 THEN 2 
 WHEN ( [# of unique Campaign Fiscal Year] , [Campaign Fiscal Year5] ) = 1 THEN 1 
 ELSE NULL END
 )

Also, if helpful, the dashboard linked above can be downloaded and imported into your Analytics.

Thank you for the change, I was able to get it to work! I did tried to download and export the dashboard you link but I got an error. It would be really helpful to learn more from your dashboard, as well.

Hi Amanda, you need to change the cube to your own. When you import someone else's dashboard it tries to look at their cube. Once you change it to your own contributions cube it should work for you.

Excellent! It worked out. Thank you, Anne.