• Replies 3 replies
  • Subscribers 962 subscribers
  • Views 465 views
  • Users 0 members are here
Related

Retention rate formula

Sofia Bolin
$organization

Hi,

I'm looking for a formula of constituents with a booking in the ongoing season (21/22) who already have visited us some time during the past 4 seasons. This since we count constituents who hasn't visited us the last 4 seasons as churned. So what I'm looking for is a formula for the retention rate. Is there anyone who has a formula for this?

Thanks,

Sofia

  • $organization

    Hi Sofia,

    I recommend some Venn math where set A is constituents in the last 4 seasons and set B is constituents in the ongoing season. Taking the count of constituents in A and adding the count of constituents in B will double count constituents who are in both A and B. Subtracting from that unique count of constituents in the union of A and B removes from the total any constituents only in A, and any that are only in B, and removes one set of the double counted constituents that are in both A and B. That then leaves only a single counting of constituents that are in both sets A and B. 

    A + B - (A or B)

    ( [# unique Constituent ID] , [Season Fiscal Current Year Offset between -4 and -1] )
    + ( [# unique Constituent ID] , [Season Fiscal Current Year Offset = 0] )
    - ( [# unique Constituent ID] , [Season Fiscal Current Year Offset between -4 and 0] )

    Turning that into a retention rate then, do you want retention as a percentage of all constituents in the last 4 seasons who could potentially hold tickets to the ongoing seasons? If so, divide the result by the unique count of constituents in the union of A and B.

    ( A + B - (A or B) ) / (A or B)

    ( ( [# unique Constituent ID] , [Season Fiscal Current Year Offset between -4 and -1] )
    + ( [# unique Constituent ID] , [Season Fiscal Current Year Offset = 0] )
    - ( [# unique Constituent ID] , [Season Fiscal Current Year Offset between -4 and 0] ) )
    /     ( [# unique Constituent ID] , [Season Fiscal Current Year Offset between -4 and 0] )

    If the rate is instead a percentage of all constituents just in the ongoing season, then divide by the formula for the B set instead, where the Offset = 0.

  • $organization in reply to Chris Wallingford

    Hi Chris,

    Glad I found this forum post! Now, how can I adapt this formula to package sales for each year? I tried out the current formula and it gave me one number. I wonder if it would be possible to get a new rate for each season?

    Thanks,
    Linda

  • $organization in reply to Linda Elvir

    Hi Linda,

    The most straightforward way would be to create a distinct value formula for each season. "This Season" would be the formula above. "Last Season" would be the same formula adjusted back on FY for each filter...

    ( ( [# unique Constituent ID] , [Season Fiscal Current Year Offset between -5 and -2] )
    + ( [# unique Constituent ID] , [Season Fiscal Current Year Offset = -1] )
    - ( [# unique Constituent ID] , [Season Fiscal Current Year Offset between -5 and -1] ) )
    /     ( [# unique Constituent ID] , [Season Fiscal Current Year Offset between -5 and -1] )

    Etc.

    Best,
    Chris