Many of our performances have shared consignments with our venue partners, and our process for daily sales reporting happens outside of Tessitura (yes, an excel document that compiles data from Tess and Ticketmaster), but I'd like to change this. Order imports on a daily or even weekly basis is unfeasible for this project. I really just want a way to store and easily update basic figures on these third-party sales in Tessitura so it can automatically generate a complete picture of sales on a daily basis. I just need total tickets sold and income.I'm wondering if anyone else does this, or has a bright idea for how to store this simple data on a performance. -My first thought is out of the box, using the content tab and creating content elements for counts and revenue. -Create a custom screen in infomaker?And, try and create a simple and clever way for someone to take an excel document from Ticketmaster and importing(?) to update sales data. This is tricky considering that we often get sales data in different formats depending on the partner, and it can change from year to year. This may have to be some kind of utility that relies on manual entry, but of course I'm trying to avoid this.
This is something we're working on as well. Our current idea on how to handle this is to create a constituent record with the partner's name so it's easy to identify its purpose. Then we would use the order import utility to import those ticket sales transactions into that constituent for reporting purposes. So we'll be using a manual process for now. If there's any way to automate such a process, I'm eager to hear about it.
I have little to offer in terms of order import automation...but I've tried. There are a number of things for us that make such automation challenging, but the closest I ever came was building out excel macros that converted ticketmaster formats. Unfortunately, dozens of shows in several venues caused extensive manual work on these files that made regular imports untenable for us. I've searched for solutions and would also welcome suggestions!As for constituent ticket history, this is an interesting solution. While order imports might be more "accurate" (although, maybe not) our immediate needs are to store simple sales totals, which could include a manual update to t_ticket_history, maybe using the TM event code with some matchup table, so that it can be isolated for our sales reporting needs without getting sticky with list pulls or other things.
Chaz, have you considered using Excel PowerQuery (Get & Transform Data - on the Data tab)? You can build steps that it will remember (using M Code). You can also insert new steps as you come across new issues.The structure I'd suggest is build the PowerQuery part to look open a file (txt or csv) within a specific folder. That way you simply replace the file within that folder and then open your Excel document and click Refresh All (to do all the steps). You then copy that transformed data and paste it into a file to do the importing. You are just mimicking a standard ETL (Extract Transform Load) process.
Does that conceptually make sense?
If interested, here is a resource I've used. Power Query Articles • BI GorillaPower Query is an ETL (Extract Transform and Load) tool that allows you to import data, transform it and load it into a destination.What is Power Query: A Beginner's Guide [2024] (gorilla.bi)