Customer Loyalty. New vs Existing Bookers in Analytics

Hi Dara,

If you're just after the counts, a formula could help with that. For NEW BOOKERS, I'll assume in any given Year we need a count of Constituents who booked in that year and no prior year. For EXISTING BOOKERS I'm working under the assumption that they booked this year and in ANY prior year included in Analytics.

Filter the dashboard on Season Fiscal Current Year Offset (assumes 15.1.7 or later), and include the current and prior few years.

In a new Pivot widget, add Season Fiscal Year on Rows.

Create a new Value formula that will look something like this in the end:

SUM ( [Constituent ID] , IF (
   [Max Season Fiscal Current Year Offset] = 0 AND ISNULL( ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset] ) )
  , 1 , NULL )
  )

For each Constituent ID, within the current Row (Season Fiscal Year), if we're in the fiscal year row that is the current fiscal year (Max Season Fiscal Current Year Offset = 0) and the constituent has no performances in any season prior to the current season ([Season Fiscal Current Year Offset] filtered to "< 0", plus that ALL( [Season Fiscal Year] ) to look outside of the given Row, and ISNULL() to return "TRUE" when the results don't exist.), then return a 1 to the SUM function. This results in a count of constituents who are New Bookers in the Current Season Fiscal Year (whatever year that is).

The trick now is that we need that Value to return different counts depending on which Row of our Pivot table we're in. That's how we might end up with something like:

SUM ( [Constituent ID] , IF (
    [Max Season Fiscal Current Year Offset] = -3 AND ISNULL( ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset < -3] ) ) OR
    [Max Season Fiscal Current Year Offset] = -2 AND ISNULL( ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset < -2]) ) OR
    [Max Season Fiscal Current Year Offset] = -1 AND ISNULL( ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset < -1] ) ) OR
    [Max Season Fiscal Current Year Offset] = 0 AND ISNULL( ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset < 0] ) )
, 1 , NULL )
  )

Each line within the IF() statement starts by asking whether the current row's season is the current, prior, or 1 of the 2 season before that. Then inspects whether the constituent has any performances in season fiscal years prior to the season fiscal year of that row. (I am showing the Season Fiscal Current Year Offset filtering in the field descriptions above for convenience. The fields in the formula editor won't present them in this way.)

For Existing Bookers, the approach is the same, but instead of ISNULL(), we'll inspect whether that count of performances in prior seasons is > 0.

SUM ( [Constituent ID] , IF (
   [Max Season Fiscal Current Year Offset] = -3 AND ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset] ) > 0 OR
   [Max Season Fiscal Current Year Offset] = -2 AND ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset1] ) > 0 OR
   [Max Season Fiscal Current Year Offset] = -1 AND ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset2] ) > 0 OR
   [Max Season Fiscal Current Year Offset] = 0 AND ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset3] ) > 0
, 1 , NULL )
  )

A really nice characteristic of this formula approach is that it will respect your other filters. If on the fly or for copies of this dashboard, you want to exclude certain Season Types or exclude Comps, you can do that and the numbers will adjust accordingly. The less nice thing is that given the complexity here, it might take a long time to render this widget. It took about 8 seconds on my laptop for 4 seasons and about 1,800 constituents in that Pivot widget. The Bar chart takes about 3 seconds.

BookersNew&ExistingbySeasonFiscalYear.dash

After getting this put together and uploaded here, I realized that if you're not limited to Season Fiscal Year, but are okay with Calendar Year, you could use the CONSTITUENT First Performance Date field to inspect for each Performance Date Calendar Year whether the constituents who purchased in that year had a First Performance Date in that year or some prior year. So for the New Bookers, something like this where [Years in Date] is from the [PERFORMANCE DATE].[Date] field:

SUM ( [Constituent ID] ,
       CASE WHEN MAX( YDIFF( [Years in First Performance Date] , [Years in Date] ) ) = 0
       THEN 1 ELSE NULL END
      )

For Existing Bookers then, instead of = 0, use < 0.

Dashboard filters include Performance Date in This and the 3 Prior Years. And the widgets will again respect other filters like Price Type Category or Season Type. And this one loads a lot faster.

BookersNew&ExistingbyCalendarYear.dash

Hi Dara,

If you're just after the counts, a formula could help with that. For NEW BOOKERS, I'll assume in any given Year we need a count of Constituents who booked in that year and no prior year. For EXISTING BOOKERS I'm working under the assumption that they booked this year and in ANY prior year included in Analytics.

Filter the dashboard on Season Fiscal Current Year Offset (assumes 15.1.7 or later), and include the current and prior few years.

In a new Pivot widget, add Season Fiscal Year on Rows.

Create a new Value formula that will look something like this in the end:

SUM ( [Constituent ID] , IF (
   [Max Season Fiscal Current Year Offset] = 0 AND ISNULL( ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset] ) )
  , 1 , NULL )
  )

For each Constituent ID, within the current Row (Season Fiscal Year), if we're in the fiscal year row that is the current fiscal year (Max Season Fiscal Current Year Offset = 0) and the constituent has no performances in any season prior to the current season ([Season Fiscal Current Year Offset] filtered to "< 0", plus that ALL( [Season Fiscal Year] ) to look outside of the given Row, and ISNULL() to return "TRUE" when the results don't exist.), then return a 1 to the SUM function. This results in a count of constituents who are New Bookers in the Current Season Fiscal Year (whatever year that is).

The trick now is that we need that Value to return different counts depending on which Row of our Pivot table we're in. That's how we might end up with something like:

SUM ( [Constituent ID] , IF (
    [Max Season Fiscal Current Year Offset] = -3 AND ISNULL( ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset < -3] ) ) OR
    [Max Season Fiscal Current Year Offset] = -2 AND ISNULL( ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset < -2]) ) OR
    [Max Season Fiscal Current Year Offset] = -1 AND ISNULL( ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset < -1] ) ) OR
    [Max Season Fiscal Current Year Offset] = 0 AND ISNULL( ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset < 0] ) )
, 1 , NULL )
  )

Each line within the IF() statement starts by asking whether the current row's season is the current, prior, or 1 of the 2 season before that. Then inspects whether the constituent has any performances in season fiscal years prior to the season fiscal year of that row. (I am showing the Season Fiscal Current Year Offset filtering in the field descriptions above for convenience. The fields in the formula editor won't present them in this way.)

For Existing Bookers, the approach is the same, but instead of ISNULL(), we'll inspect whether that count of performances in prior seasons is > 0.

SUM ( [Constituent ID] , IF (
   [Max Season Fiscal Current Year Offset] = -3 AND ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset] ) > 0 OR
   [Max Season Fiscal Current Year Offset] = -2 AND ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset1] ) > 0 OR
   [Max Season Fiscal Current Year Offset] = -1 AND ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset2] ) > 0 OR
   [Max Season Fiscal Current Year Offset] = 0 AND ( [# of unique Performance ID] , ALL( [Season Fiscal Year] ) , [Season Fiscal Current Year Offset3] ) > 0
, 1 , NULL )
  )

A really nice characteristic of this formula approach is that it will respect your other filters. If on the fly or for copies of this dashboard, you want to exclude certain Season Types or exclude Comps, you can do that and the numbers will adjust accordingly. The less nice thing is that given the complexity here, it might take a long time to render this widget. It took about 8 seconds on my laptop for 4 seasons and about 1,800 constituents in that Pivot widget. The Bar chart takes about 3 seconds.

BookersNew&ExistingbySeasonFiscalYear.dash

After getting this put together and uploaded here, I realized that if you're not limited to Season Fiscal Year, but are okay with Calendar Year, you could use the CONSTITUENT First Performance Date field to inspect for each Performance Date Calendar Year whether the constituents who purchased in that year had a First Performance Date in that year or some prior year. So for the New Bookers, something like this where [Years in Date] is from the [PERFORMANCE DATE].[Date] field:

SUM ( [Constituent ID] ,
       CASE WHEN MAX( YDIFF( [Years in First Performance Date] , [Years in Date] ) ) = 0
       THEN 1 ELSE NULL END
      )

For Existing Bookers then, instead of = 0, use < 0.

Dashboard filters include Performance Date in This and the 3 Prior Years. And the widgets will again respect other filters like Price Type Category or Season Type. And this one loads a lot faster.

BookersNew&ExistingbyCalendarYear.dash