Member Activity Report

Hi SIlvia,

Lists and Output don't have benefit usage date elements so we'll have to focus on Analytics Interactions for this. We need a widget that includes all current members, but then only reports on those without benefit use in the last 90 days.

I used the CONSTITUENT MEMBERSHIP ORGANIZATION dimension Standing field set to Current as a dashboard filter. Optionally add Membership Organization as a filter. Then with Membership Organization, Constituent ID, Constituent Display Name, and Membership Level on Rows of a Pivot widget, we can create a formula to report on the days since each member's last benefit use.

Unfortunately, the function for calculating the number of days between two dates doesn't support filtered value formula techniques to limit the dates in question to benefit usage interaction dates. Instead the formula will have to iterate through each constituent's benefits (including non-benefit interactions) and then only evaluate results for benefit interactions. The non-benefit interactions will have a Benefit ID = -999999.

This formula (for each constituent row) looks at each of their benefits, and returns the minimum number of days since the Interaction Date of any benefit use.

MIN( [Benefit ID] ,
  IF ( 
    MAX( [Benefit ID] ) = -999999
    , 999
    , MIN( DDIFF( NOW( [Days in Date] ) , [Days in Date] ) )
  )
)

My local data doesn't have any current members with benefit use more than 90 days ago, so they all show up as have no benefit use (999). I also added a dashboard filter to only include memberships with a Membership Start Date more than 90 days ago. Otherwise, this formula would return a 999 for a membership with no benefit use, but that started yesterday.

CurrentMemberswithoutBenefitUsage.dash

Hi SIlvia,

Lists and Output don't have benefit usage date elements so we'll have to focus on Analytics Interactions for this. We need a widget that includes all current members, but then only reports on those without benefit use in the last 90 days.

I used the CONSTITUENT MEMBERSHIP ORGANIZATION dimension Standing field set to Current as a dashboard filter. Optionally add Membership Organization as a filter. Then with Membership Organization, Constituent ID, Constituent Display Name, and Membership Level on Rows of a Pivot widget, we can create a formula to report on the days since each member's last benefit use.

Unfortunately, the function for calculating the number of days between two dates doesn't support filtered value formula techniques to limit the dates in question to benefit usage interaction dates. Instead the formula will have to iterate through each constituent's benefits (including non-benefit interactions) and then only evaluate results for benefit interactions. The non-benefit interactions will have a Benefit ID = -999999.

This formula (for each constituent row) looks at each of their benefits, and returns the minimum number of days since the Interaction Date of any benefit use.

MIN( [Benefit ID] ,
  IF ( 
    MAX( [Benefit ID] ) = -999999
    , 999
    , MIN( DDIFF( NOW( [Days in Date] ) , [Days in Date] ) )
  )
)

My local data doesn't have any current members with benefit use more than 90 days ago, so they all show up as have no benefit use (999). I also added a dashboard filter to only include memberships with a Membership Start Date more than 90 days ago. Otherwise, this formula would return a 999 for a membership with no benefit use, but that started yesterday.

CurrentMemberswithoutBenefitUsage.dash