Total number of first-time buyers for each perf?

I've used the given formula to identify the number of first time buyers for each production in a given year (or whatever filtered timeframe). I'm interested to know how many of those went on to buy other things? For example, if we had 10,000 new buyers for Wicked, how many also bought other things. So column 1 has the name of the production, column 2 has the # of new buyers, can column 3 show how many purchased other things?

I've used the given formula to identify the number of first time buyers for each production in a given year (or whatever filtered timeframe). I'm interested to know how many of those went on to buy other things? For example, if we had 10,000 new buyers for Wicked, how many also bought other things. So column 1 has the name of the production, column 2 has the # of new buyers, can column 3 show how many purchased other things?

Hi Hilary,

I recommend adding to the IF() statement a condition like

AND ( [# unique Production Season ID] , ALL( [Production Season] ) ) > 1

So, assuming [Production Season] on Rows, if a constituent in that production has a first performance date in that production, and has a count of unique productions from all production seasons (not just the one for this row) is greater than 1, then they attended future (since this row is their first) productions.

SUM ( [Constituent ID] , 
  IF (
    MIN ( DDIFF ( [Days in Performance Date] , [Days in First Performance Date] ) ) = 0
    AND ( [# unique Production Season ID] , ALL( [Production Season] ) ) > 1
  , 1
  , NULL
  )
)

Thanks!!!! If I then want to see what Genres/Seasons those patrons purchased (Broadway, Dance, etc), would there be a way to do that?

That's interesting, Hilary. I'm going to recommend something, but there may be factors / filters at play in your dashboard or widget that require tweaks to what follows.

We're looking to flag constituents who don't just meet that second criteria of having more than one unique production, but where at least one of those "more than one" is (e.g.) Broadway (for which I'm going to use Season Type). 

So, for a given Production Season row in the pivot widget, for each first-time constituent in that row with future productions (from the formula above) what is their total count of Broadway productions across all productions and all season fiscal years? The weird bit is determining whether that count includes the current row of the pivot widget or not. If the current row is not a Broadway production, then if the constituents' count of Broadway productions is greater than 0 then they went on to attend Broadway. If the current row is a Broadway production, then the total count needs to be greater than 1 to indicate that they went on to an additional Broadway production after their first time attendance.

To determine whether the current row is a Broadway production, I have the count of Broadway Production Season ID for the row. If the row is a Broadway production, it returns 1. If the row isn't a Broadway production, then the result is NULL or N/A rather than 0. Nulls mess with IF statements because they make everything FALSE. So instead of checking whether 1 is greater than NULL, which is false, I have an IF and a check for whether the value ISNULL, that then replaces the NULL with 0.

AND ( [# of unique Production Season ID] , ALL( [Production Season] ) , ALL ( [Season Fiscal Year] ) , [Season Type=Broadway] ) 
  > IF ( ISNULL ( ( [# of unique Production Season ID] ,[Season Type=Broadway] ) ) , SUM(0) , 1 ) 

To update our formula, to get a count of first-time attendees for each production season who went on to at least one future Broadway production, we can replace that check for "any future production" with this new check for a future Broadway production:

SUM ([Constituent ID],
 IF (
   MIN ( DDIFF ( [Days in Date],[Days in First Performance Date] ) ) = 0 
   AND ( [# of unique Production Season ID] , ALL( [Production Season] ) , ALL ( [Season Fiscal Year] ) , [Season Type=Broadway] ) 
     > IF ( ISNULL ( ( [# of unique Production Season ID] ,[Season Type=Broadway] ) ) , SUM(0) , 1 ) 
 , 1 , NULL 
 )
) 

Once in place, rename it something like "First-timers Returning Broadway" and then from the options menu of the value, click Duplicate. Edit the formula and change the two Season Type filters to the second Season Type for Dance.

(aside) I had Season Fiscal Year on my dashboard and realized it was limiting the scope of "future productions" that could qualify as return purchases for first-timers, so I added that ALL ( [Season Fiscal Year] ) into the future production comparison.

Auditing this a little...

The widget at the left shows that of the 4 first-timers who came in via Astronomy Lectures 2022, 2 went on to Broadway, 2 to Ballet, and all 4 to subsequent Admissions events. The widget at the right is filtered to Astronomy Lectures 2022 first-timers, and shows all their events by event date, and it validates the numbers in the first widget.