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
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] )
( [# 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] )
( ( [# 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] ) )/
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.
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
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] )
( ( [# 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] ) )/
Etc.
Best,Chris