Hello all,
Building a new to file dashboard and want to track ticket sales from events those patrons have attended after their first event. In other words I want to sum ticket sales after the first sale. I would probably put this in a simple pivot. Has anyone done this before or can maybe point me in the right direction?
Hi Jonathan,
Let's say you have a dashboard filtered to a production season, and for that run of events, the KPI is the total ticket sales after that production season attributable to constituents for whom an event in that production season was their "new to file" event. For each constituent that held tickets to the production season, if their [First Performance Date] is the same as their [Performance Date], then calculate their [Total Ticket Paid Amount] for ALL productions to which they held tickets, minus the Ticket Paid Amount attributable to the production season of the dashboard. If that sounds aligned with your goal then an enhancement to this point should get you there...
Number of tickets purchased by first time ticket buyers in table - Reporting & Analytics - Forums - Tessitura Network
SUM ( [Constituent ID] , IF ( MIN ( DDIFF ( [Days in Performance Date] , [Days in First Performance Date] ) ) = 0 , ( [Total Ticket Paid Amount] , ALL ( [Production Season] ) ) - [Total Ticket Paid Amount] , NULL ))
This assumes there are no filters other than Production Season in play. If there are others, e.g. Season, then you would need additional ALL() exceptions tied to that [Total Ticket Paid Amount] to ensure it allows for the inclusion of all desired future events for these new-to-file constituents.
If you need this broken out by constituent, then with the constituent details in a pivot table, and one row per constituent, it would be the formula from above, but only the inner IF() portion.
Thanks Chris!
I'm assuming [First Performance Date] is the actual first performance the patron has ever attended, correct? If so, then things are a bit more complicated for us. We define "New to file" as patrons who have not attended in the last 3 seasons. So maybe the approach would be to identify the first performance in a season and subtract that [Total Ticket Paid Amount]. I could then use a list filter that contains all of the "New to Files".
Yes, Jonathan, good thought. I agree that seems reasonable.