New subscribers widget

Hi Anh,

If you're just looking from year to year, and depending on how your organization does rollovers, you can easily create a filtered value using the "solicitor display name." For example:

([# of unique package seat key], [solicitor display name])

To get new subscribers, you would filter to exclude "subscription rollover" and to get renewals you would filter to only include "subscription rollover."

I hope that helps,
Christine

Forgive me as I don't have Tessitura open in front of me at the moment (I know!), but can you do something with package season, and counting the number of unique seasons?

Hi Anh,

The formula I shared would also included lapsed - essentially anyone who is not a renewal in the current season.

Kathleen's formula would get you more towards first time buying a subscription ever. Try changing your "Count" to a "Sum"? Looking at my notes for multi-pass aggregations it seems to usually use a sum.

~ Christine

Hi Anh,

Looks like a variation on this formula Michael shared. For the following, set up a widget or dashboard filter on Season Fiscal Current Year Offset = 0, limiting the constituents to current season buyers. Add another filter on something that limits both the current season buyers and our interrogation of their historical purchase, to subscriptions... for example Is Package Flag = Y. Alternatively you can try Price Type Category = Subscription.

SUM ( [Constituent ID] ,
 IF (
 (
   ISNULL( ( [# unique Package Seat Key] , [Season Fiscal Current Year Offset < 0] ))
   OR ( [# unique Package Seat Key] , [Season Fiscal Current Year Offset < 0]) = 0
 )
 , 1
 , NULL
 )
)

For each constituent, explore their package seat count in any season fiscal year prior to the current one, and if there isn't any history prior to this season, then include them in the total count. It would probably work with just the INSULL and without the OR = 0, but this covers the cases.

Hello Anh Le,
I know it has been a while since your initial post, but I'm curious if you ever got Chris W.' formula to work for this question? New constituents that are in a package for the first time?

This is what my formula looks like:

Hi Michael,

The formula in that image is incomplete from the one I posted above, so might try including the second half of the IF condition... and confirm that there a filter to limit the results to only packages.

Hi Chris Wallingford, I went ahead and added the rest of that formula but didn't receive any counts.  
I thought best to share the.dash and see if something looks off on your end in that formula.

Here is that formula without showing the applied filters in the season current fiscal year offset:

SUM ([Constituent ID],
IF (
ISNULL (([# of unique Package Seat Key],[Season Fiscal Current Year Offset]))

OR
([# of unique Package Seat Key], [Season Fiscal Current Year Offset])=0
,1
, NULL
)
)

NewSubscribers.dash

Thanks
Mike

I see now you're grouped by Season. I'd swap that our with Season Type, or add ALL([Season]) to both parts of the IF, but if there are multiple season rows the latter will count a past purchase to any Season Type as a prior purchase making them not new to the Season of the given row.

That said, the formula is still giving me trouble. I tried Returning (in screenshot below) and it gave me results...

SUM([Constituent ID],
 IF(
 ([# of unique Season],[Season Fiscal Current Year Offset]) > 0
 ,1,0
 )
 )

But when I reverse the ,1,0 to ,0,1 I get all zeros. I'll dig into later, but in the meantime, the Venn approach may be better in this case anyway.

([# of unique Constituent ID],[Season Fiscal Current Year Offset <= 0])
- ([# of unique Constituent ID],[Season Fiscal Current Year Offset < 0)

For New subscribers, this the unique count of all subscribers in this and any prior year minus the count of all subscribers in any prior year.