• Replies 21 replies
  • Subscribers 704 subscribers
  • Views 629 views
  • Users 0 members are here
Options
Related

Finding Donor Retention/Repeat Donors in Analytics

Ashleigh Arrington
$organization

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?

Parents
  • $organization

    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?

  • $organization in reply to Nathanael Pearson

    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?

  • $organization in reply to Ashleigh Arrington

    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!

  • $organization in reply to Nathanael Pearson

    Oh wait, that worked! I had a typo Sweat smile

    Thank you so much!!

  • $organization in reply to Nathanael Pearson

     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?

  • $organization in reply to Keri Noerrlinger

    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

  • $organization in reply to Chris Wallingford

    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?

Reply Children