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?
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 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?
Correct Gabrielle,
Would it be a good "idea" to add Household or Group Constituent ID into Analytics, which would be the same for the Household and Primary Affiliates?
Thanks for the speedy response. Sounds like that would be really helpful for what I'm usually trying to do!
Edit: the Idea is here for anyone who wants to upvote!
https://community.tessituranetwork.com/ideas/i/ideas/add-household-and-or-group-constituent-id-to-analytics
Running this code
CASE WHEN MIN([Campaign Fiscal Year] ) = 2018 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]) , [Fiscal Year1 = 2017] ) )WHEN MIN([Campaign Fiscal Year] ) = 2019 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]) , [Fiscal Year2 = 2018] ) )WHEN MIN([Campaign Fiscal Year] ) = 2020 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]), [Fiscal Year3 = 2019] ) )WHEN MIN([Campaign Fiscal Year] ) = 2021 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]) , [Fiscal Year4 =2020] ) )WHEN MIN([Campaign Fiscal Year] ) = 2022 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]), [Fiscal Year5 =2021] ) )WHEN MIN([Campaign Fiscal Year] ) = 2023 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]) , [Fiscal Year6 =2022] ) )ELSE 0 END
When I try to drill into the group renewed from last year it opens a window with a filter showing Fiscal Year1 = 2017. This filter shows for every year in the table. What have I done wrong? I would paste a screen shot but this window won't let me paste an image.