Hello all -
So I have four custom views that I use for all ticketing reporting here - one for all "main" season tickets, one for "main" season single tickets only, one for education products and one for what we call here "alternative" programming. All the program function basically the same way: they take all seats sold (from t_sub_lineitem) and reports sales on them and with each SLI representing a seat, it's both a revenue and seating program that we use across multiple other reports here in the company.
However, the report is never 100% accurate for past dates due to exchanges and such, so it's never been fully reliable for, say, looking at sales at this point last year v this year.
So I'm attempting to build a transactional seating report - but I'm running into obstacles and I'm not sure where the obstacles are. I have a new program that pulls transactionally (actually via seat history, not transactions), but I cannot get it to reconcile against any common field: for example
I can't reconcile against customer because seats can be transferred; I can't reconcile against order because the seats move from order to order; I can't reconcile against performance number because folks go in and out of performances at different prices... the list goes on and on.
Has anyone ever writing a transaction-ish seating report that is both accurate over history and in the present? I'm happy get into more detail with anyone interested - any assistance is appreciated!
RJ
It was pretty complicated, and frankly I don't trust myself to remember it properly without spending a couple of hours immersing myself in the old TASK tickets. But the gist of it was that certain actions would cause duplicate rows to be written to T_ORDER_SEAT_HIST, which caused reports (which usually sum up on event types) to be skewed. A correct history fix script would be capable of identifying duplicate rows (while also allowing for minor variations in audit row timings), removing the correct one of the duplicates (and doing this properly when a price type change occurred in the transaction), and ideally do testing against something like a T_SUB_LINEITEM report to verify that cumulative sums after the fix match up with existing reliable reporting. I'm pretty sure I got as far as identifying duplicate rows with audit timing variations with my scripts. From: Richard Jackson <bounce-richardjackson6492@tessituranetwork.com> Sent: 9/13/2016 7:09:28 PM What exactly is the bug in t_order_seat_hist? maybe I can account for it in my code here to see what I come up with? From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nick Reilingh Sent: Tuesday, September 13, 2016 9:16 AM To: Richard Jackson <richardj@alleytheatre.org> Subject: RE: [Tessitura Technical Forum] Tickets and Transactions We also reported some bugs in the way T_ORDER_SEAT_HIST was being recorded (showing up on the Ticket Sales by Period report), and while I believe these issues have been resolved in the client application, I haven't yet developed a reliable method of correcting issues in the way the table was written (though I have done some considerable R&D to isolate those issues). Wondering if anyone else has already written a script to fix those problems, or if you'd be interested in the work I've done so far. From: Tom Brown <bounce-tombrown3568@tessituranetwork.com> Sent: 9/13/2016 1:49:59 PM Katie, I would love to see any code you are willing to share. We have reported at lease one of the bugs in T_ORDER_SEAT_HISTORY to the network via TASK related to seat releases. If folks find other problems place share them with the network so that they can be fixed. I looked at T-Stats data and for my purposes. T-Stats did not work, because historical statuses would change. I was looking at questions like how many seats are available to general public on the day we went on subscription sales, and to the general public. I looked at the data warehouse under t-stats and it did not have a way to consistantly answer that question either. On Tue, Sep 13, 2016 at 9:23 AM, Katie Lachance-Duffy <bounce-katielachanceduffy1902@tessituranetwork.com> wrote: I also have attempted this, and the result is a “good enough” report for our consortium. I’m happy to share it, but like Tom said, it relies on data from T_ORDER_SEAT_HIST, which seems to have some recording inconsistencies and complexities. To add to that, it seems like everyone has their own way of selling and exchanging packages, setting up packages, etc., so it’s a pretty large beast, which is not to say that it’s impossible! I haven’t tried this, but as an alternate solution, would using T-Stats data work for you? Or maybe warehousing some of its warehoused data? J ~Katie From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Tom Brown Sent: Monday, September 12, 2016 7:02 PM To: Catherine Lachance-Duffy Subject: RE: [Tessitura Technical Forum] Tickets and Transactions We have tried to do this. We ran into problems. There were / are some bugs in how Tessitura populates T_ORDER_SEAT_HIST. We would really love to have a view that provides this information. I would like to work with you to see if we can crack this nut. When we were trying to do this on our own it turned out to be a sort of hard nut. --Tom … 718.724.8135 tbrown@BAM.org From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Richard Jackson Sent: Monday, September 12, 2016 5:08 PM To: Thomas Brown Subject: [Tessitura Technical Forum] Tickets and Transactions Hello all - So I have four custom views that I use for all ticketing reporting here - one for all "main" season tickets, one for "main" season single tickets only, one for education products and one for what we call here "alternative" programming. All the program function basically the same way: they take all seats sold (from t_sub_lineitem) and reports sales on them and with each SLI representing a seat, it's both a revenue and seating program that we use across multiple other reports here in the company. However, the report is never 100% accurate for past dates due to exchanges and such, so it's never been fully reliable for, say, looking at sales at this point last year v this year. So I'm attempting to build a transactional seating report - but I'm running into obstacles and I'm not sure where the obstacles are. I have a new program that pulls transactionally (actually via seat history, not transactions), but I cannot get it to reconcile against any common field: for example I can't reconcile against customer because seats can be transferred; I can't reconcile against order because the seats move from order to order; I can't reconcile against performance number because folks go in and out of performances at different prices... the list goes on and on. Has anyone ever writing a transaction-ish seating report that is both accurate over history and in the present? I'm happy get into more detail with anyone interested - any assistance is appreciated! RJ This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you! This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you! This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you! This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you! This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
It was pretty complicated, and frankly I don't trust myself to remember it properly without spending a couple of hours immersing myself in the old TASK tickets. But the gist of it was that certain actions would cause duplicate rows to be written to T_ORDER_SEAT_HIST, which caused reports (which usually sum up on event types) to be skewed.
A correct history fix script would be capable of identifying duplicate rows (while also allowing for minor variations in audit row timings), removing the correct one of the duplicates (and doing this properly when a price type change occurred in the transaction), and ideally do testing against something like a T_SUB_LINEITEM report to verify that cumulative sums after the fix match up with existing reliable reporting.
I'm pretty sure I got as far as identifying duplicate rows with audit timing variations with my scripts.
From: Richard Jackson <bounce-richardjackson6492@tessituranetwork.com> Sent: 9/13/2016 7:09:28 PM
What exactly is the bug in t_order_seat_hist? maybe I can account for it in my code here to see what I come up with?
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nick Reilingh Sent: Tuesday, September 13, 2016 9:16 AM To: Richard Jackson <richardj@alleytheatre.org> Subject: RE: [Tessitura Technical Forum] Tickets and Transactions
We also reported some bugs in the way T_ORDER_SEAT_HIST was being recorded (showing up on the Ticket Sales by Period report), and while I believe these issues have been resolved in the client application, I haven't yet developed a reliable method of correcting issues in the way the table was written (though I have done some considerable R&D to isolate those issues). Wondering if anyone else has already written a script to fix those problems, or if you'd be interested in the work I've done so far.
From: Tom Brown <bounce-tombrown3568@tessituranetwork.com> Sent: 9/13/2016 1:49:59 PM
Katie,
I would love to see any code you are willing to share.
We have reported at lease one of the bugs in T_ORDER_SEAT_HISTORY to the network via TASK related to seat releases. If folks find other problems place share them with the network so that they can be fixed.
I looked at T-Stats data and for my purposes. T-Stats did not work, because historical statuses would change. I was looking at questions like how many seats are available to general public on the day we went on subscription sales, and to the general public. I looked at the data warehouse under t-stats and it did not have a way to consistantly answer that question either.
On Tue, Sep 13, 2016 at 9:23 AM, Katie Lachance-Duffy <bounce-katielachanceduffy1902@tessituranetwork.com> wrote:
I also have attempted this, and the result is a “good enough” report for our consortium. I’m happy to share it, but like Tom said, it relies on data from T_ORDER_SEAT_HIST, which seems to have some recording inconsistencies and complexities. To add to that, it seems like everyone has their own way of selling and exchanging packages, setting up packages, etc., so it’s a pretty large beast, which is not to say that it’s impossible! I haven’t tried this, but as an alternate solution, would using T-Stats data work for you? Or maybe warehousing some of its warehoused data? J ~Katie From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Tom Brown Sent: Monday, September 12, 2016 7:02 PM To: Catherine Lachance-Duffy Subject: RE: [Tessitura Technical Forum] Tickets and Transactions We have tried to do this. We ran into problems. There were / are some bugs in how Tessitura populates T_ORDER_SEAT_HIST. We would really love to have a view that provides this information. I would like to work with you to see if we can crack this nut. When we were trying to do this on our own it turned out to be a sort of hard nut. --Tom … 718.724.8135 tbrown@BAM.org From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Richard Jackson Sent: Monday, September 12, 2016 5:08 PM To: Thomas Brown Subject: [Tessitura Technical Forum] Tickets and Transactions Hello all - So I have four custom views that I use for all ticketing reporting here - one for all "main" season tickets, one for "main" season single tickets only, one for education products and one for what we call here "alternative" programming. All the program function basically the same way: they take all seats sold (from t_sub_lineitem) and reports sales on them and with each SLI representing a seat, it's both a revenue and seating program that we use across multiple other reports here in the company. However, the report is never 100% accurate for past dates due to exchanges and such, so it's never been fully reliable for, say, looking at sales at this point last year v this year. So I'm attempting to build a transactional seating report - but I'm running into obstacles and I'm not sure where the obstacles are. I have a new program that pulls transactionally (actually via seat history, not transactions), but I cannot get it to reconcile against any common field: for example I can't reconcile against customer because seats can be transferred; I can't reconcile against order because the seats move from order to order; I can't reconcile against performance number because folks go in and out of performances at different prices... the list goes on and on. Has anyone ever writing a transaction-ish seating report that is both accurate over history and in the present? I'm happy get into more detail with anyone interested - any assistance is appreciated! RJ This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you! This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you! This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
I also have attempted this, and the result is a “good enough” report for our consortium. I’m happy to share it, but like Tom said, it relies on data from T_ORDER_SEAT_HIST, which seems to have some recording inconsistencies and complexities. To add to that, it seems like everyone has their own way of selling and exchanging packages, setting up packages, etc., so it’s a pretty large beast, which is not to say that it’s impossible!
I haven’t tried this, but as an alternate solution, would using T-Stats data work for you? Or maybe warehousing some of its warehoused data? J
~Katie
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Tom Brown Sent: Monday, September 12, 2016 7:02 PM To: Catherine Lachance-Duffy Subject: RE: [Tessitura Technical Forum] Tickets and Transactions
We have tried to do this. We ran into problems. There were / are some bugs in how Tessitura populates T_ORDER_SEAT_HIST.
We would really love to have a view that provides this information. I would like to work with you to see if we can crack this nut. When we were trying to do this on our own it turned out to be a sort of hard nut.
--Tom
…
718.724.8135
tbrown@BAM.org
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Richard Jackson Sent: Monday, September 12, 2016 5:08 PM To: Thomas Brown Subject: [Tessitura Technical Forum] Tickets and Transactions
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!