Has anyone figured out a way to see what shows guests exchange out of and then change into each season? It can be aggregate but I need to be able to see the leaving from X show going into Y show and then totals for the year.
- Chris
I'm also very interested in something that would show this!
From what I understand, there is nothing that "links" the exchanged shows other than the timing of the returned ticket and newly purchased one. But hopefully someone else might have a solution!
That is what I feel is the major issue is the nature of exchanges and how they are processed. This is a huge tool needed for Broadway booking houses so we know which shows people like and don't like as we go into booking for each season. I can see many other organizations needing a tool like this for programming as well. I could really see orchestras using this as they plan their seasons.
I completely agree! It would be a huge help to be able to see this kind of information, especially during the planing process. Potentially in a future version?
Yes I think it really depends on the best practices of your organization. If you're doing those show exchanges within the same order then you could use the finance cube to examine credits and debits for an array of orders either on the production season, GL or Payment Method level.
Unfortunately we don't use an exchange MOS so I don't have that as an easy access point to figure that out.
The other caveat....we are just looking at number of seats, not revenue. We just want to see what shows people leave and go to as part of their substitution.
Not sure if this helps as a non-analytics starting point, but here is some SQL I was provided with (from Martin Keen I think).
We use to see what people exchange their gift vouchers into.
select
i.description prod_season_desc,
p.perf_code,
pt.description price_type,
convert(varchar,osh.event_date,103) return_dt,
count(distinct osh.seat_no) num_seats,
sum(osh.amount) val_seats
from T_ORDER_SEAT_HIST osh
join T_PERF p
on p.perf_no=osh.perf_no
join T_INVENTORY i
on i.inv_no = p.prod_season_no --although you could join on p.perf_no if you want the performance rather than the production season name
join TR_PRICE_TYPE pt
on pt.id=osh.price_type
where osh.event_code = 3
--and p.prod_season_no = XXX --use this if it's a whole production season
--and p.perf_code = '<sample perf_code>' --use this if it's just one performance
group by i.description, p.perf_code, pt.description, convert(varchar,osh.event_date,103)
Nancy Sheleheda Sr. Director of Application Development and Support ServicesPittsburgh Cultural Trust803 Liberty Avenue, Pittsburgh, Pa 15222412-456-1387 Sheleheda@trustarts.org | TrustArts.org
Hey Ronan. This is some great code. Now I just need to figure out how to change the date structure to appear in US format (month, day, year vs day, month, year). Thanks for this bit of code!
We use a sub swap price type but not a specific exchange price type. We aren't currently using any sort of swap or exchange MOS so I can't go off that. We're working with Tessitura Consulting as well to help us figure out how to get this data.
Hopefully, this is useful: https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/