• Replies 13 replies
  • Subscribers 701 subscribers
  • Views 469 views
  • Users 0 members are here
Options
Related

New Donor Indicator Widget

Tessitura User
$organization

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,

  • $organization in reply to Sue Llende

    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

  • $organization

    Thanks All. 


    How would Chris's formula for new donors work if you had a WEB DEFAULT campaign that had no set fiscal year. This default campaign gets a lot of untracked contributions and cart survey responses for sources. 

  • $organization in reply to Amy Welsh

    Hi Amy,

    You can use the CONTRIBUTION DATE > Fiscal Year field instead of the Campaign Fiscal Year.