STBs Attended 3+ Productions in last 5 Years

Hi Michelle,

Set a filter on Season Current Fiscal Year Offset using a Value type filter of between -5 and 0 (or Season Fiscal Year and select the past 5).

Set another filter on either Price Type Category, selecting only the Single Ticket categories, or a filter on Package Season set to include only "(none)".

In a Pivot Table, add Constituent details on Rows (ID, Display Name...). In the Values panel, add a Unique Count of Production (or Production Season). Then hover over that Value and click the funnel icon to filter the results, selecting a Value type filter of >= 3.

Cheers,
Chris

Admittedly I have not spent much time in analytics because I have been focused on building our data integrity in the application, but aren't ticket sales in Analytics based on data from T_TICKET_HISTORY?

If T_TICKET_HISTORY is the source of ticket sales then the sales reflected in analytics will not be correct if an organization does things like credit admission tickets to the cost of a memberships by processing the tickets as returns given that returned tickets get removed from T_TICKET_HISTORY whenever TP_UPDATE_TICKET_HISTORY is run for the season.

The other issue with sales in analytics is that it uses the Order date as the sales date which is not necessarily the date the ticket was actually sold. Under normal operations our business model has scenarios where we have running order or orders that get created days sometimes months prior to when the sale is actually recorded.

For example if we started a running order in on January 1st for a trade customer for the month of January all sales for that customer in that order would have a sale date of 1/1 even if the tickets were actually sold multiple days throughout the month.

This also means in the sales number being reported in Analytics for a given day can change depending on if someone adds or removes tickets to an order with an historical sales date.

Lastly Order date is not a static value in that people can go in and change the date at any point in time resulting in completely different sales numbers for the affected dates when they do. While the field can be locked down, it still represents an opportunity for inconsistent sales data.

Hi Ronald,

Analytics optionally can include converted ticket history from a legacy system, but doesn't by default. Analytics always includes live orders.

In addition to order date, you can reference the CREATE DATE on the sub lineitems in an order. This is the date a ticket was added to an order.

I hope that helps,
Chris

Good to know.

We don't use create date from T_SUB_LINEITEM because that is not necessarily reflective of the sale date given that for a wide variety of reasons a ticket line can be added to an order long before it is registered as a sale and in certain ticket states it can be removed from an order without an offsetting transaction resulting in inconsistent reporting for a given date range.

We use the following logic to determine the sale date as this will always reflect the same numbers for a given date range and time.

Select sl.Sli_No, Printed_Date  as SaleDate, Order_no
, sl.Perf_no, price_type , due_amt as Price, sl.seat_no , sl.sli_status
, zone_no ,t.printed_loc , 1 as Qty, t.printed_by  from TX_SLI_TICKET as T
inner join T_SUB_LINEITEM as SL on t.sli_no = sl.sli_no where reprint_no = 0

Thank you Ronald,

That sheds a lot of light on your practices. It may not help you, but for clarity on the thread, removed SLI are removed from Analytics as well.

Is the printed date in you case also the date of attendance or the event (performance) date?

Hi Ronald, 

This sounds like an issue that may be specific to your business practices. I find most organizations who allow order dates to be changed, do so intentionally so they can consider a sale to have occurred on a particular date, even if for whatever reason it were not processed on that date. Considering from a slightly different angle, a good business case for adding additional items to an existing order, rather than a new one, is generally because one wants to consider those items having been "ordered" on the date of the existing order. In cases where it would be considered "ordered" on the date requested, it would be in a new order. Another reason to add to existing orders is to exempt someone from dynamic pricing to honor a price offered as of the time of the original order, rather than when the change were requested. Although in that case, the create date would suffice if the goal were to track when people were making the actual requests (whether they're add-ons or exchanges or not).

In all of my examples, the question being asked is "when do we consider the constituent to have placed the order?" This can and usually will have a different answer than "when do we recognize the revenue?" I mention that because you mentioned "registering as a sale" and it made me wonder if perhaps you and Chris are discussing two different things. 

Hi Ronald, 

This sounds like an issue that may be specific to your business practices. I find most organizations who allow order dates to be changed, do so intentionally so they can consider a sale to have occurred on a particular date, even if for whatever reason it were not processed on that date. Considering from a slightly different angle, a good business case for adding additional items to an existing order, rather than a new one, is generally because one wants to consider those items having been "ordered" on the date of the existing order. In cases where it would be considered "ordered" on the date requested, it would be in a new order. Another reason to add to existing orders is to exempt someone from dynamic pricing to honor a price offered as of the time of the original order, rather than when the change were requested. Although in that case, the create date would suffice if the goal were to track when people were making the actual requests (whether they're add-ons or exchanges or not).

In all of my examples, the question being asked is "when do we consider the constituent to have placed the order?" This can and usually will have a different answer than "when do we recognize the revenue?" I mention that because you mentioned "registering as a sale" and it made me wonder if perhaps you and Chris are discussing two different things. 

To keep this conversion relatively focused I'm not going to go into deferred revenue as that adds a completely different level of complexity not natively addressed in the application, particularly when it comes to recognizing revenue at the ticket level. In short we had to build our own.

My general point is that regardless of how an organization decides to classify something as a sale there should be some consistency to the definition across the organization, a general understanding of the underlying date, how operational processes may influence it and how consistent the historical data will remain over time.

For example if finance uses one metric to determine a sale and group sales is using a different metric both of them could provide two completely different set of sales numbers to the same audience.

For the same reasons historical consistency is especially important when trying to reconcile data. If someone is trying to reconcile numbers from January when the report said 1000 tickets were sold but now it says only 950 tickets were sold then it makes the task of reconciliation that much harder.

What we have discovered is that for our purposes many of the canned reports and dashboards do not meet our definitions of a sale and/or don't maintain the level of historical consistency the business is accustomed to.