Hi everyone! This feels like something that already exists that I just can't seem to find - we want to see the number of donors who are repeat donors, specifically looking year to year.
IE. number of 2023 donors who gave in 2022; number of 2023 donors who gave in 2022 AND 2021; number of 2023 donors who gave in 2022 AND 2021 AND 2020; and so on and so forth
I'm a little familiar with the bucketing technique, but it doesn't seem to want to cooperate in the Contributions cube. Way down the line it would be cool to find out who those people are, but for the time being we're just looking at numbers.
Thoughts?
Without diving into the full bucketing formula (my brain is a bit fried right now), it would be something like: SUM ( [Constituent ID] ,
IF ( [Contribution Amount] , [Contribution Date = 2023] ) > 0 AND ( [Contribution Amount] , [Contribution Date = 2022] ) > 0
, 1 , 0 )
)This would say "Count up all constituents whose giving in 2023 AND 2022 was more than 0". From there, you could duplicate/tweak the formulas to reference whichever year(s) you want (as you noted above). Once you've got the building blocks (formulas you know work), then you have the fun (and sometimes laborious) task of building out comprehensive formulas. Design > test > replicate > multiply!
There might be simpler ways to do this, but that's my first pass. Does that make sense?
This is such great timing! I'm trying to do something similar to figure out donor retention rate (not just members).
Ahhhh thank you so much Nathanael! That makes sense to me, but it seems Analytics isn't happy with it, I've been playing around with it but keep getting a function syntax error of varying kinds?
I copied the formula you posted and updated it with our data and got this:
I began to swap in and out some other measures (like contribution count instead of amount) and continued getting similar errors. It appears to be throwing the error at the IF statement. I'm going to continue tweaking to see what may work, but any suggestions on where to look?
This seems to work for me:
SUM ( [Constituent ID] , IF ( ( [Total Amount] , [Calendar Year = 2023] ) > 0 AND ( [Total Amount] , [Calendar Year = 2022] ) > 0 , 1 , NULL ) )
Essentially "If a Constituent ID has giving in 2023 and 2022, mark them as a 1- mark everyone else as a 0. Now, add up all the 1s". 'Calendar Year' is 'Contribution Date / Calendar Year' and 'Total Amount' is 'Contribution Amount'.
You can then set a filter on the widget/dashboard to look at certain constituencies or funds. You also could add in rows for Constituent ID or Constituent Display Name, and check the records that way.
Let me know if that works!
Oh wait, that worked! I had a typo
Thank you so much!!
This is great, thank you! I'm working on something similar and finding myself stuck on trying to do a calendar year offset (still learning analytics language). Is there a way to get that formula to do a calendar year offset so a pivot table like this will work?
Hi Keri,
Something like this might server up what you're looking for:
CASE WHEN MIN( [Calendar Year] ) = 2018 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]) , [Calendar Year = 2017] ) )WHEN MIN( [Calendar Year] ) = 2019 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]) , [Calendar Year = 2018] ) )WHEN MIN( [Calendar Year] ) = 2020 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]), [Calendar Year = 2019] ) )WHEN MIN( [Calendar Year] ) = 2021 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]) , [Calendar Year = 2020] ) )WHEN MIN( [Calendar Year] ) = 2022 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]), [Calendar Year = 2021] ) )WHEN MIN( [Calendar Year] ) = 2023 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]) , [Calendar Year = 2022] ) )ELSE 0 END
RetentionRate_2D00_AllCampaignsDRAFT.dash
This is very timey for me as well as we put together reporting specifically for donors (not just members). Counts for acquisition and retention are at the top of the list, and so far I'm missing something with the formulae. I've tried the formulae in this post, and there have other posts with suggestions but none of mine will go. I think I'm picking the "wrong" date or amount, or even constituent ID. I've tried COUNT and SUM, Fiscal offset, Days In Date, etc. I feel like I'm super close, but just missing the mark.
Jenny
Hi Jenny,
Go ahead an post more detail for everyone to look at. What does the widget look like when editing it? What filters are in play? What does the formula you have look like? Maybe even export and attach the dashboard.
This is great, thanks Chris. Am I correct in assuming this won't catch instances where one or more of the transactions (donations here, or if I adapt this for tickets later) lives on the household? Analytics only looks at individuals' constituent IDs, right?