We went live in January, and I'm still getting my feet wet w/r/t List Manager. I'm trying to set up our Pre-Show Reminder lists to work with WordFly and automatically send x number of days before the performance, depending on type. All well and good, until I start setting up the reminders for our School Day program, which is more of a group sales kind of situation. Due to the way some of the schools pay, we may not get paid until a month after the performance.
If I just use the following criteria, I only get paid orders:
Ideally, I'd like to set up two lists, one for paid and one for unpaid, so I can change the wording on the unpaid email and remind them they still owe us money. It's also way more important to remind the schools that haven't paid that they have a performance coming up, as the number of no-shows has skyrocketed since 2020.
It would be good to use on our Mainstage shows as well, since sometimes we reserve tickets for members and I'd love to send them an automatic message a week or two before the show reminding them of an outstanding balance.
However, I suspect this is not currently possible as things are set up. Has anyone managed to do this?
Jesse Dillman Good point - I guess I would be looking for both - right now it only returns Owner, which is would be the Elementary School and in most cases does not have an email attached. The Initiator returns the Teacher, which does have an email.
I can't use the add affiliates/associates option, since that returns any and all teachers at Elementary School instead of just the one associated with that order.
There are also a handful of non-school associated orders, which would return a real person as the Owner instead of an Organization.
John Trimble Yes, so my Paid list works great (using vs_ticket_history) and the email in Wordfly generates the list every day at 8am, so it's very unlikely I will miss someone who did pay (since most of these orders are coming in as checks through the mail and being manually input between 9am-5pm). I was mostly trying to take the line I thought was pulling in Initiators from the Paid list criteria and adding it in to the Unpaid manual criteria Jesse had posted a few weeks ago - but my SQL skills are basically non-existent and I'm too new to Tessitura to know if t_ticket_history can even look at that - or if it does but it's called something else - etc.
You might be able to use a BI view to get started and work back from descriptions there to the integer details in your org in vs_ticket_history for better efficiency. Be sure to test and review. The example I'm sharing works with our schedule of two live perfs in the next six days (sent before the performance weekend), but you could change that to tomorrow or today. Sorry there's a couple of complications above what you probably need: we have a recorded livestream in the same production season that has a later perf_date to allow for extended viewing (perf_date is the last date a viewer could view), so you might want to roll back from production season to performance in that clause. I haven't had cause to pull unpaid recipients, so just go with the history views. When I want unpaid recipients for some (outlier for us) reason, I use order search or query from back end. You'd have to get into sub_lineitems for unpaids and recipients, and it would get even more complicated due to orders without recipients (meaning you can't just switch out recipient_no as customer_no in list manager) so for unpaid I'm sticking with recommending Order Search. When I first started with manually coding list manager, I relied heavily on https://www.tessitura.com/files/docs/systemadmin/tessitura-table-structures-v15_1 or for 16 https://www.tessitura.com/files/docs/systemadmin/tessitura-table-structures-v16
select distinct customer_no from bi.VT_TICKET_HISTORY vth where vth.perf_name not like 'virtlib%' --exclude upcoming on demand collection (based on naming convention) --and vth.season_no = 602 --594 --change this every season to pick up the new season --removed to future-proof via season description and vth.season_desc like 'dbdt%' and vth.role_no in (1,3,4,5,6,7) ----all the roles -- --1=owner -- -- 2=initiator -- -- 3=owner/initiator -- -- 4=recipient -- -- 5=owner/recipient -- -- 6=initiator/recipient -- -- 7=owner/initiator/recipient and vth.prod_season_no in ( --this part gets everything in the production season, even though the virtual perf date is possibly more than x days later select distinct vth.prod_season_no from bi.VT_TICKET_HISTORY vth group by --vth.season_no, vth.prod_season_no, vth.perf_dt, vth.perf_no, vth.perf_name having min(vth.perf_dt) between CONVERT (DATETIME, CONVERT (VARCHAR, GetDate(), 112)) -- TODAY and DateAdd(dd, 6, CONVERT (VARCHAR, GetDate(), 112) + ' 23:59:59.997') --today + 6 days. Change the six if you're running for more than one production in the next six days )
I actually use a dashboard in analytics to track ticketing receivables and initiator constituent IS available there - I use performance date so that Finance can know monthly what is unpaid to add to receivables revenue. We have a ton of School Groups that come so we have a whole process setup to manage this.
Now that you mention it* I had a look, and bi.VT_TICKET_HISTORY includes status (though vs_ticket_history doesn't). So with a little extra coding, a variation of what I shared might get Allison Frost her list.
https://www.tessitura.com/files/docs/systemadmin/bi-views.
*The data in Analytics often gets there via a view, and the ones with bi.VT_DW (Data Warehouse) in the descriptions are dedicated to that purpose (while BI.VT_ <no DW> aimed more at reporting, but still sometimes used in Analytics). So here's the ticket status codes for reference if you're looking at a table or view that has them.