• Replies 17 replies
  • Subscribers 966 subscribers
  • Views 1335 views
  • Users 0 members are here
Related

Booking churn Subscribers in Analytics?

Josefin Johansson
$organization

Hi, 

We are looking into the booking churn for our subscribers at Malmo Opera.

Parameters we want to look into on a seasonal level:

  • How many are returning subscribers season after season?

             In what packages? Other patterns?

  • How many are new subsribers per season?

             In what packages? Other patterns?

  • How many drop offs per season?

             In what packages? Other patterns for drop offs?

Anyone got any tips and tricks on how get started with this in Analytics, reports or in List Manager/ Extraction Manager?

We dont know if Analytics is the right place for this since there is no Data set for Subscribers?

 

Thanks a lot for your help!

Josefin

Parents
  • $organization

    - 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!

  • $organization in reply to Amy Culliford

    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

Reply
  • $organization in reply to Amy Culliford

    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

Children
No Data