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
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!
my two cents - and perhaps this is the same issue Tom is referring to - we found a bug also related to seat releases. here's the network update to the TASK ticket - would appear it was resolved in 12.5 although i haven't made a point of checking:
"This issue has been resolved for version 12.5
Release Note: Exchanging a zero value seat while using the Title or Date Menus to navigate performances on the seat map screen would cause additional Reserved and Released events to be written to the seat history. This has been corrected."
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.
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
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!
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.
I've uploaded my attempt at point-in-time reporting for package sales, specifically, to my profile on TessituraNetwork.com. It's called LRP_PKG_SALES_PIT.
Feel free to message me with any questions, feedback, etc. I'm not claiming that this code is perfect (or well written :)), but it gives my organizations results that they're comfortable with.
I went and found my duplicate-row-finding code and uploaded a Bitbucket snippet -- I wrote some useful comments (I think).
https://bitbucket.org/snippets/TN_WebShare/9ooe7
(Find information at tessituracoders.bitbucket.org if you do not yet have access to Bitbucket.)
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
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:
my two cents - and perhaps this is the same issue Tom is referring to - we found a bug also related to seat releases. here's the network update to the TASK ticket - would appear it was resolved in 12.5 although i haven't made a point of checking: "This issue has been resolved for version 12.5 Release Note: Exchanging a zero value seat while using the Title or Date Menus to navigate performances on the seat map screen would cause additional Reserved and Released events to be written to the seat history. This has been corrected." 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!
Hello Everyone,
I’d like to introduce myself, I’m John Jakovich Tessitura’s VP of Business Intelligence. You probably heard that The Network is embarking on a Business Intelligence Initiative. I’m leading this effort with the community to understand overall needs and gaps, then design and develop functionality to address those needs.
This thread is very timely because we're beginning the development of standard reporting views which should make this sort of thing easier. The use cases in this thread are really helpful. I would also appreciate the opportunity to take a look at the views you’ve developed, as they will give us valuable context.
If you don’t mind sharing, please send them to me at jjakovich@tessituranetwork.com and I’ll follow-up with each of you. I can also give you a sneak-peak at what we're working on, I'd appreciate your feedback.
Thank you!John
P.S. I’m posting from 30,000 feet over Kansas!
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!
From: Richard Jackson <bounce-richardjackson6492@tessituranetwork.com> Sent: 9/13/2016 7:09:28 PM
In one of my test databases using your script, I’ve discovered.
119 “double_entries”
AND
21 “Asymmetric_doubles”
This seems like a fairly small set to me.
Early in the year when I was looking at this I had between 6,000 to 30,000 potential records that were problematic. (Some of which were eventually as normal behavior. I’m sure that my final numbers were much larger than this result.
One of the Interesting things about this result set is that these errors go back to when I upgraded to Tessitura V12.1.x. The errors that I was looking at went back, many years.
I wrote some useful comments (I think).
Unknown said: We have tried to do this. We ran into problems. There were / are some bugs in how Tessitura populates T_ORDER_SEAT_HIST. [...] When we were trying to do this on our own it turned out to be a sort of hard nut.
Just chiming in to add to the chorus of those that have attempted this sort of thing, e.g. in our case we have a custom daily sales report that we archive to PDF every day because, as described in this thread, it's not really possible to reliably re-generate this data on a date in the past. Folks here would love a version of this report that could do that, and like others here I have spent significant time puzzling over T_ORDER_SEAT_HIST trying to make it work.
Back in 2008, when Ron Wilson was working with us on our conversion, I asked him about reporting from this table, and he said something like, "In theory you could use that for reporting, but no-one does." And he was right!
My code allows me to compare against our current sales reporting for reconciliation. For the entire season last year (I wanted to use a closed season since I knew seats wouldn’t be moving real time), I’m only 122 seats off totaling $472 – that’s off by .115% in the count of seats and .009% in revenue. Now I know I have some differences in how I account for donated seats, but I’m so close that I keep banging my head against this wall… it would be reassuring to know I should stop banging my head because of a confirmed bug in 12.5 (which we’re on) vs “the bug is gone and you may have exchanged some seats incorrectly”… but again – with no solid tie between seats and seat history, I’m having difficulty.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Chris Jensen Sent: Thursday, September 15, 2016 2:42 PM To: Richard Jackson <richardj@alleytheatre.org> Subject: RE: [Tessitura Technical Forum] Tickets and Transactions
Tom Brown: We have tried to do this. We ran into problems. There were / are some bugs in how Tessitura populates T_ORDER_SEAT_HIST. [...] When we were trying to do this on our own it turned out to be a sort of hard nut.
Tom Brown:
[...] When we were trying to do this on our own it turned out to be a sort of hard nut.
From: Tom Brown <bounce-tombrown3568@tessituranetwork.com> Sent: 9/12/2016 11:02:30 PM