Booking churn Subscribers in Analytics?

Hi Josefin,

This is such a big, interesting topic. The variations in what define new, returning, lapsed, and churned (or drop offs) varies widely, as does the scope of the patrons to be considered in those buckets (everyone, or only subscribers, or any buyer within a certain season type).

I'm going to see if I can scratch the surface a bit from the Analytics side to start, where we can pull together the overall numbers. This NewReturnLapseandChurn.dash dashboard is an exercise I've drafted in calculating these values in Analytics in such a way as to respect any dashboard filters you may apply in order to impact the scope of the buckets (e.g. only subscribers). The dashboard outlines some assumptions around the calculations for who is new verses lapsed, etc., which is logic you can alter once you've been through the exercises and translate this into your own business rules.

This dashboard is part of a greater whole, and hard codes the fiscal years into the value formulas, whereas typically I would use relative fiscal year values so that each year's change over would not require editing all the formulas in the dashboard.

I'm also attaching NewReturnLapseandChurnChart.dash shown below as an example of what you can do with these numbers. The dashboard has default filters for

• Is Package Flag, to limit the results to just package buyers, non-package buyers, or everyone.
• Season Type, to limit to only certain types of yearly seasons.

One could add any other applicable filter, and the new, return, lapsed, and churn values will respect it.

Best,
Chris

• Importing a Dashboard from File

Thanks a lot Chris!

This was very helpful.

I will import the dashboard and start expirement right away!

Best, 

Josefin

Hi again Chris,

Thanks again for this dashboard.

It is really helpful in this case!

Just so that I am clear on what I am looking at here. What is the difference between lapsed and churned in your dashboard?

Thanks a lot för your help,

Josefin

Hi Josefin,

Thank you for asking. In the context of THIS sample, the logic is defined as follows:

• New = constituents who attended in the current year, and not in the prior 2 years
• Returning = constituents who attended in current year, and (at any point) in the prior 2 years
• Lapsed = constituents who have not attended in the current year, and did attend in the season 1 year prior
• Churned = constituents who have not attended in the current or prior 1 year, and did attend in the season 2 years prior

Said another way, using FY 2020 as the current FY:

• New = attended in 2020 and not 2019 nor 2018
• Returning = attended in 2020 and in 2019 or 2018
• Lapsed = attended in 2019 and not in 2020
• Churned = attended in 2018 and not in 2019 nor 2020

I tried to document in the dashboard itself along with the Venn Math used to calculate the buckets so that it can be extended for other versions of these buckets based on different business rules. 

Hi Chris, 

Thanks for the answer!

All the best,

Josefin

Hi - I want to say thank you so much for this. I just got this set up in my Analytics and very grateful for your shared work!

Chris Wallingford - this has been a huge help for my organization - thank you for providing both the dashboard and chart.
I was wondering if you know how I can get the same new/returned/lapsed/churn information, but instead for the total number of subscription packages, instead of constituents? I've tried different formula options, but I can't seem to find one that works. Thanks in advance!

Hi Amy,
Yes this is possible in Analytics, but we have to deviate from the simpler Venn math to get at those figures. Instead, we'll need a formula that interrogates each constituent and then buckets them based on their subscription purchases. So for each constituent, determine weather they have any package seats in the past 2 years, and at least one this year, and if so, count their package seats from this year. Then sum all those qualifying constituents' package seats and bucket them as "New". That could look like this:

SUM ( [Constituent ID] ,
 IF ( ISNULL ( ( [Total Ticket Count] , [Package ID>0] , [Season Fiscal Year in 2018,2019] ) )
  AND ( [Total Ticket Count] , [Package ID>0] , [Season Fiscal Year=2020] ) > 0
  , ( [# of unique Package Seat Key] , [Package ID>0] )
  , NULL )
 )

For Returned, we could do something similar with:

IF ( ( [Total Ticket Count] , [Package ID>0] , [Season Fiscal Year=2019] ) > 0
 AND ( [Total Ticket Count] , [Package ID>0] , [Season Fiscal Year=2020] ) > 0

Or, we could shift to counting seasons in which they held package seats, filtered to the current and prior fiscal years:

IF ( ( [# of unique Season Fiscal Year] , [Package ID>0] , [Season Fiscal Year in 2019,2020] ) = 2

For Lapsed, it'll be ISNULL 2020 AND > 0 in 2019.
And for Churned, it'll be ISNULL 2019,2020 AND > 0 in 2018.
An alternative for Churned might be to see if their maximum season fiscal year is 2018:

IF ( ( [Max Season Fiscal Year] , [Package ID>0] , [Season Fiscal Year<=2020] ) = 2018

NewReturnLapseandChurnSubscriptionCounts.dash

Best,
Chris

So this is great!  One more question:  i am trying to find which subs from 2020 and 2021 didnt renew in 2022 but bought single tickets to performances in the 2022 season.  Which dashboard would be best for that?   I have imported all the dashboards and they all have great info but not sure I am seeing exactly what i need quite yet.  This might be just a simple adjustment of a formula.  Unfortunately, I am pretty darn new at this so thanks for all the help!  

Hi Robert,

I think we'd just add a couple of conditions to the Returned formula above...

IF ( ( [Total Ticket Count] , [Package ID>0] , [Season Fiscal Year=2020] ) > 0
 AND ( [Total Ticket Count] , [Package ID>0] , [Season Fiscal Year=2021] ) > 0
 AND ISNULL ( ( [Total Ticket Count] , [Package ID>0] , [Season Fiscal Year=2022] ) )
 AND ( [Total Ticket Count] , [Season Fiscal Year=2022] ) > 0
)

...if they have packages in 20 and in 21, and not in 22, and otherwise have tickets in 22... Does that sound right?

That sounds exactly right!!!!

I am getting a syntax error when I replace the formula in the returned 

Yes Robert, sorry, I used some shorthand in my reply... the full formula would be

SUM ( [Constituent ID] ,
  IF ( ( [Total Ticket Count] , [Package ID>0] , [Season Fiscal Year=2020] ) > 0
    AND ( [Total Ticket Count] , [Package ID>0] , [Season Fiscal Year=2021] ) > 0
    AND ISNULL ( ( [Total Ticket Count] , [Package ID>0] , [Season Fiscal Year=2022] ) )
    AND ( [Total Ticket Count] , [Season Fiscal Year=2022] ) > 0
  )
  , 1
  , NULL )
)

And unfortunately copy / paste from the text here into the formula editor will not work. A valid formula in the text editor is much more than the text that's visible, and pasting this text into the formula editor doesn't include that additional information. Instead it needs to be recreated in the formula editor. 

So Ive tried to recreate this a number of times.   I always get an error.  First, if i add that last ) after the NULL line, I get "Function Syntax Error:  End of formula expected.  ')' found instead.   If i delete that last ')' , I get a different error that I will attach below.   I am not sure if i need to type the [Season Fiscal Year =2020] or if editing the filter and selecting 2020 and so on works.   Same with the Package ID >0.   I just used the filters.  Thanks for your help!  I am sure this is something i am simply doing wrong.   

Hi Robert,

This ")" corresponds to the "IF (", so it should be after the results if true or false. You can just move it to the very end and the formula should allow you to save...