Analytics help - Number of tickets purchased by first time ticket buyers

Hi Nicola,

I am wondering if you managed to get this working?

I am trying to do something similar. Instead of "how many tickets first time ticket buyers purchased" I want to do some Analysis on the price types in those orders.

For a couple of price types, I want to know out of all the orders where customers have bought them, how many were first timers.

Any help would be greatly appreciated!

Thanks, Donald

Hi Donald,

With a Production Season or Performance as a dashboard or widget filter, and with Price Types on rows of a Pivot Table widget, and a unique count of Constituent ID as the total number of constituents purchasing those price types, add a value such as Nicola's...

SUM ( [Constituent ID] , 
   IF (
      MIN ( DDIFF ( [Days in Performance Date] , [Days in First Performance Date] ) ) = 0
      , 1 , 0
   )
)

This will give you the number of constituents purchasing those price types who are also first-time buyers within each of those price types. 

Thanks! This seems a good fit for adding a column to our daily pivot that I hadn't even thought about trying to include. 

Hi Chris,

This is great - thank you. I have been able to build a pivot table that show me what I need to know (i.e. out of those who booked a price type, for X number of customers it was their first visit with us). 

Next step for me on this is to do some Analytics on what these X number of customers did after, i.e. did they start booking full price tickets. 

I am having some trouble isolating these customer so I can put into another dashboard or list. When I try amending my pivot table to 'jump to' it is bring back the wider list, not the X figure. I think this is because the 'jump to' dashboard is not being filtered by the formula I am using. 

Any ideas on how I can get the IDs of these constituents into a list, or any resources on how to filter Jump To dashboards this way?

Thank you for your help. 

Donald

Hi Donald,

You may be able to filter a widget or dashboard on Constituent ID and apply a formula that will limit the filter to only constituents for whom their first performance was with one or more specific price types. We add a filter and select the [Constituent ID] field. Then switch to the Ranking type filter settings. Set it to the TOP 10000 (or more if necessary to capture the expected number of constituents). Then click the [fx] button to open the formula editor. This gets a little weedy. I had to try a few different formulas to find one that it didn't have trouble processing in the context of a Ranking filter, so for future readers that's why this isn't more simplified...

SUM( [Performance ID] ,
   ( SUM( [Price Type ID] ,
      IF( MAX( DDIFF( [Days in Performance Date] , [Days in First Performance Date]) ) = 0, 1, NULL )
   ) , [Price Type = {entry price type}])
)

For each looking only at Entry Price Types, for each Price Type ID within each Performance ID for each Constituent ID, if that was their first performance, then return a "1" to the filter for that Constituent ID, otherwise return a NULL to the filter for that Constituent ID.

Also add alongside the [Constituent ID] filter, a filter for [Seat Sold Flag] = Y.

Then assuming this is starting in a Pivot widget, add on Rows, [Constituent ID], [Days in First Performance Date], [Days in Performance Date], [Production Season], [Price Type] and a Value for [Total Ticket Count], and that should allow you to validate the filter. All the performances for each constituent in the filter should be listed, and the first performance for each constituent (they'll be ordered by performance date) should have the desired entry price type(s). I used "Complimentary" as my "entry price type", and for each constituent, we can see the subsequent event dates and price types. 

CompEntryConstituentsPerformances.dash (edit the Constituent ID filter to use the desired Entry Price Type(s).

Hi Donald,

You may be able to filter a widget or dashboard on Constituent ID and apply a formula that will limit the filter to only constituents for whom their first performance was with one or more specific price types. We add a filter and select the [Constituent ID] field. Then switch to the Ranking type filter settings. Set it to the TOP 10000 (or more if necessary to capture the expected number of constituents). Then click the [fx] button to open the formula editor. This gets a little weedy. I had to try a few different formulas to find one that it didn't have trouble processing in the context of a Ranking filter, so for future readers that's why this isn't more simplified...

SUM( [Performance ID] ,
   ( SUM( [Price Type ID] ,
      IF( MAX( DDIFF( [Days in Performance Date] , [Days in First Performance Date]) ) = 0, 1, NULL )
   ) , [Price Type = {entry price type}])
)

For each looking only at Entry Price Types, for each Price Type ID within each Performance ID for each Constituent ID, if that was their first performance, then return a "1" to the filter for that Constituent ID, otherwise return a NULL to the filter for that Constituent ID.

Also add alongside the [Constituent ID] filter, a filter for [Seat Sold Flag] = Y.

Then assuming this is starting in a Pivot widget, add on Rows, [Constituent ID], [Days in First Performance Date], [Days in Performance Date], [Production Season], [Price Type] and a Value for [Total Ticket Count], and that should allow you to validate the filter. All the performances for each constituent in the filter should be listed, and the first performance for each constituent (they'll be ordered by performance date) should have the desired entry price type(s). I used "Complimentary" as my "entry price type", and for each constituent, we can see the subsequent event dates and price types. 

CompEntryConstituentsPerformances.dash (edit the Constituent ID filter to use the desired Entry Price Type(s).