Ticket Sales by Period vs. Database

Okay, this is going to be longish-so please forgive (as well as for the time taken during investigation of this issue).  Tessitura experts, please feel free to weigh in on any wrong assumptions I may have made, but I think I have figured this out.

Restating problem:  Database pull for tickets issued not matching "Ticket Sales by Period" (database pull results in 2 more tickets than report).

Notes on the "Ticket Sales by Period" report that I have learned in investigating this, obvious and not obvious alike (results from a brand new performance in our Test database so as to prevent unknown variables):

• Seats that have been purchased - included in ticket count and revenue
• Seats that have been returned - subtracted from ticket count and revenue
• Seats that have been exchanged-  only final change value indicated (e.g. 6 exchanged out, 4 exchanged in results in -2 total for the performance); this is true for both ticket count and revenue
• Seats that have been donated for resale, but NOT resold:  They are still included in total ticket count (count comes from line in T_ORDER_SEAT_HIST from original order which is not removed).  Their value is still included in total revenue (value comes from amount column in line in T_ORDER SEAT_HIST from original order which is not removed).
• Seats that have been donated for resale AND resold:  They are counted twice in total ticket count, first from original order, second from resold order (ad infinitum for multiple donate-for-resale/sales).  Their value is included twice in total revenue, same as above (ad infinitum as above as well).

Thus the total sold on the "Ticket Sales by Period" report will always be the total number of tickets issued as of that moment (plus or minus comps depending on the setting of the report), plus the value of every ticket that has been donated for resale based on its original selling value, regardless of that particular seats' current status.  Consider the above when you are getting values that disagree.

Now, on to my actual issue here.

The above can easily explain why "Ticket Sales by Period" would give a larger number than pulling all issued tickets from TX_PERF_SEAT because you might forget about donated tickets.  What it does not do, however is explain how you can, remembering donated tickets, get a higher number from TX_PERF_SEAT than the "Ticket Sales by Period" report.  We had a performance that was giving me exactly that result, and here is what I found.

I delved into the procedure which runs the "Ticket Sales by Period" report itself to figure out what was going on.  This report, since it tracks every change to the tickets, and not just a final result (which is what you get from TX_PERF_SEAT), tracks totals by adding and subtracting 1s for each line of change that goes on.  These are, explicitly, +1s for "reserved" and "new paid" and -1s for "returned", "released" and "payment unpaid".  "Payment adjusted" has representation in the procedure, but NOT in this area where the adding 1s and -1s takes place, and it is there where I found my issue.  We changed the price type on the tickets of one of our subscribers (total of 2 tickets) due to a customer service issue from the regular subscription price to a Sub Comp.  The +1s mentioned above are specific:  "reserved" is for comps only while "new paid" is for paid tickets only.  As such, the seats in this instance were never released, therefore were not classified in the T_ORDER_SEAT_HIST table as "reserved".  However, since they were comp tickets, they also never passed the rigours of the "new paid" part of the report so a +1 was not added back in for them there (I figured this out by going seat by seat through the performance through their corresponding lines in the table, which took some time as this venue is a 1600 seat venue and the performance was attended at 75% capacity.  Not complaining, it gave me some good practice, but hopefully none of you will have to do this since I have found this out now).  If I understand it correctly, there needs to be some form of +1 for something like a "new payment adjusted" in the comp section of this report, but I will let others worry about that as I have not verified how that would impact other table lines, as well, as I am still only reasonably sure that I have found the issue.  I may have made some mistakes and might still be wrong, but I am at least somewhat confident.

If you are reading this and cannot understand my mess of writing above, please feel free to contact me, and I can try to explain it more clearly and/or send you the queries I used to locate the information that allowed me to find the results I obtained.  My e-mail is jmoskal@TheCenterPresents.org and my phone line is (317) 819 - 3506 (Indiana, USA, Eastern Standard Time).

John A. Moskal II

There are some standard reports we abandoned early due to counting issues like this, e.g. counting seats twice sounds bad to me.

I was warned early in my life as a Tessitura report writer to avoid T_ORDER_SEAT_HIST for all but certain very specific uses, and would avoid it for ticket sales. Unless I am looking for capacity or some such I usually don't look at TX_PERF_SEAT, either. Everything needed can usually be found in T_SUB_LINEITEM, e.g. often simply counting sli_status 3 and 12 (sometimes 2, 3 and 12) for the perf(s) I'm interested in, broken up by price type or whatever the specific case requires.

Well, and there you nailed it.  We were getting some very specific questions about capacity in reference to sales which is what drove me to search here in the first place.  Also, our organization has to this point used the "Ticket Sales by Period" report for quite a number of varied purposes in terms of numbers, so I wanted to verify whether or not we could fully trust those numbers for our needs.

Pretty much though, I agree with you in that I will be avoiding T_ORDER_SEAT_HIST if at all possible.  For my capacity purposes, I will continue to use TX_PERF_SEAT as it does seem the best for that, but generally I do not use either of these for sales numbers.  I was merely curious about why there were differences, and now I know why.

Anyway, thanks all.

Well, and there you nailed it.  We were getting some very specific questions about capacity in reference to sales which is what drove me to search here in the first place.  Also, our organization has to this point used the "Ticket Sales by Period" report for quite a number of varied purposes in terms of numbers, so I wanted to verify whether or not we could fully trust those numbers for our needs.

Pretty much though, I agree with you in that I will be avoiding T_ORDER_SEAT_HIST if at all possible.  For my capacity purposes, I will continue to use TX_PERF_SEAT as it does seem the best for that, but generally I do not use either of these for sales numbers.  I was merely curious about why there were differences, and now I know why.

Anyway, thanks all.