You are currently reviewing an older revision of this page.

First Time Buyers and Subsequent Buying

From (+) First Time Buyers from Previous Year Returning in 2023 - Discussions - analytic Coffee! - Tessitura Network

 (Spoleto Festival USA)
I'm wondering if anyone has anything that gives the accurate number first time buyers from a specified year that returned this year? 

(Pittsburgh Cultural Trust Consortium)

Hi Michael,

I have a couple of formulas that might help you with this concept. I created them to look overall at the return rate of first time buyers and what they were coming to next. I wonder if you could adjust things in these formulas to look more at specific years.

The summary widget is filtered to select different production seasons and then I have the following formula to calculate how many have since returned.

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

The first part of the formula recognizes if they are a first-time buyer [HW: see below #1], and then the second part adds in whether they have come to anything else.

Taking this formula, I then add onto it to create a proportion of how many are returning:

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

So, the denominator here just gets a total count of first-time buyers, as in the first part.

I have another widget that then that looks at what they are coming to next designed as a bar chart. This widget is filtered by the first performance name. The formula I use here probably could have also been used in a variation in the above calculations.

COUNT ( [Constituent ID] , 
         IF ( MIN ( DDIFF ( [Days in Date] , [Days in First Performance Date] ) ) > 0
            , [Max Constituent ID]
            , NULL )
      )

For this formula, I started with the formula for first time buyers, but then looked at anything that they have come to since that first performance date [hence > 0]. I have categories for season and production season, but if you just want to look more broadly at who came back in a certain season, you could probably just use season.

You won't be able to use a jump-to with these formulas, but you could always add a constituent ID to a pivot table to download and import as a manual list to verify.