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,
Edit: "the number of constituents who have donated in 2023"
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. Brown (He/Him/His) (What's this?)Tessitura Quality Assurance and Data Support AdministratorPittsburgh Cultural Trust803 Liberty Ave, Pittsburgh, PA 15222412-930-8052 tcbrown@trustarts.org | TrustArts.org
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!
Thank you!