Hey Everyone! Working on a dashboard to track new, lapse, and long lapse donors. I tried the following formula to no avail (I get an error message). Any Idea?
COUNT([Constituent ID],[Campaign Fiscal Year]) -
count([Constituent ID], IF(MIN([Contribution Count],[Campaign Fiscal Year1])=1, MAX([Constituent ID]), NULL))Where the first Campaign Fiscal Year is for anything 2023 and forward. Campaign Fiscal Year on the second line is anything previous to 2022. I thought this would show The number of constituents who have donated in 20223 (or to future campaigns) minus the constituents who have at least one donation prior to 2023. Thanks in advance for any advice,
Hi Michael,
The syntax error is a missing ")" at the end of the second statement.
You've sort of the right idea here, but we need a slightly different approach for the correct number. Let's look at the unique count of distinct constituents with ANY contribution minus the unique count of constituents with a PAST contribution.
COUNT([Constituent ID]) - (COUNT([Constituent ID]),[Campaign Fiscal Year < 2023])
With the original post logic of this year's donors minus prior year's donors, a donor from a past year who hasn't contributed this year, is substracted from another donor who contributed for the first time this year. I contributed last year but not this year. You contributed this year but not last year. We net zero in the output.
Using some Venn math, if we know we have 10000 unique donors ever, and 9000 unique donors prior to this year, then we have 1000 unique donors who are new this year. Some previous posts with related content:
(+) Retention rate formula - Reporting & Analytics - Forums - Tessitura Network
(+) Booking churn Subscribers in Analytics? - Reporting & Analytics - Forums - Tessitura Network
Hi Chris, Thank you for simplifying this for me! I was definitely overthinking this. I also overlooked the fact I was doing "count" and not just subtracting the const. IDs from a list. Again, thank you!
T.C. is correct. I updated the formula in my last post to reflect the correction.
Hello formula experts! I am trying to follow along but am receiving a sytax error...can someone take a look at my formula below and provide any insight to correct it? Thanks in advance!
Try putting parentheses around [Total Campaign Fiscal Year] --> ([Total Campaign Fiscal Year])
Hi Sue, in the formula in Chris' comment, I believe he clicked and filtered the campaign fiscal year, as opposed to typing out the <2023.
In your filter, it looks like you may need to click the "[Total Campaign Fiscal Year]" and change the type(sum) to all items. Then click it again and select filter from the menu. In the values tab in the filter pop up, add a new condition and set it to < 2023.
Filtering did it...thank you for the step-by-step instructions...it was very helpful! I filtered the widget by the fund "Individual Contributions" in order to isolate non-membership contributions.
This is now showing me a list of all constituents who, in 2023, made a first gift to this fund, however it includes constituents who made a previous gift in other funds. I'm trying to identify brand new donors to our org where their only gift was to a non-membership (our Individual contribution) fund. Any thoughts on how to achieve this?
Many thanks in advance!
Hi Sue,
I might recommend a different approach wherein we trust that the [Contribution ID] is a reliable indicator of the order in which contributions are made (technically the Contribution Date could be back dated such that this isn't 100% reliable, but should be okay). With that, we could count a constituent only if their MIN Contribution ID across all of their contributions is the same as their MIN Contribution ID to the desired fund in 2023. If those two Contribution ID are the same, then they are first time donors whose first contribution is to the desired fund. If that sounds right to you, then it looks like this in formula...
SUM ( [Constituent ID] , IF ( MIN( [Contribution ID] ) = ( MIN( [Contribution ID] ) , [Fund = Individual Contributions] , [Campaign Fiscal Year = 2023] ) , 1 , NULL ))
For each constituent, IF their min/first contribution is also their min/first contribution to the individual contributions fund in 2023, then count them. Or if you already have widget or dashboard filters on Fund = Individual Contributions, then it would look like this:
SUM ( [Constituent ID] , IF ( ( MIN( [Contribution ID] ) , ALL( [Fund] ) ) = ( MIN( [Contribution ID] ) , [Campaign Fiscal Year = 2023] ) , 1 , NULL ))
For each constituent, IF their min/first contribution across all funds (overriding the widget or dashboard filter on Fund) is also their min/first individual contribution in 2023, then count them.
Cheers,Chris