Hello!
We went live with Tessitura in March and are beginning our YE fundraising/membership reporting. I was wondering how others calculate their consecutive years of giving and consecutive years of membership. Do you take into account a little lapse? For example, if your fiscal year is calendar year and the donor gives in Dec19 and Jan21, is that still considered consecutive in 2020?
And, what are you using in Tessitura to obtain this info? Do you have any samples?
I really appreciate any and all help and tips/tricks.
Thanks so much!
PS. I also asked this question on the reporting/analytics forum, but I thought we probably compare things apples to apples being in a similar sector.
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!