Number of refunded orders/tickets by production season?

there is a widget that does this in the Cancellation Impact Tracking Dashboard - https://www.tessituranetwork.com/en/Files/Shared-Dashboards/Cancellation-Impact-Tracking-Dashboard

Thanks Mary, will take a look!

It seems this might not be possible on Analytics as the finance cube can only look at financial amounts rather than number of tickets/orders. 

Basically I am being tasked with coming up with a dashboard that details workload related to refunding customers for cancelled/postponed performances. So how many tickets and orders have we refunded to date and how many more do we have to do in the future.

Hi James,

I've had the same problem and come up with a few creative solutions so far - certainly can't promise they're the best out there! We are tracking number of tickets returned in Analytics, and I'm doing so by referencing the "original" number of tickets - aka the number of tickets in the cancelled performances before we started returns. Obviously to use this method you will need access to that information, but hopefully you have some old sales reports/records of where sales were at in terms of number of tickets at that time. 

I've attached a .dash file with some examples of the types of widgets we're using - so far we're only tracking by tickets OVERALL (not by production season) in the top widget, the "Progress so far (by # tix)". In addition to looking at the total # of tickets returned, this also calculates a percentage to full returns, and an average per day and estimated days to process the remaining tickets based on that average - the downside to this widget is those last two items need to have the "# of days" so far entered manually so right now I am doing that daily as part of my morning workflow (you'll see I have an indicator widget pulling from the finance cube that's doing the calculation for me so I just have to update to match that number - it totals the unique dates on which there were returns starting from the first day of returns). 

I also have a widget just below that that's tracking the same information for ticket revenue - which does NOT require any manual updating as it can pull everything directly from the finance cube. 

Thirdly I included the widget where we are tracking percent returns by production season, we're just tracking that by revenue, not number of tickets, but the same concept could be applied to a seats and tickets cube widget that calculates based on the original number of tickets for each production season rather than the original amount of revenue. These widgets do get into some pretty gigantic formulas for the values (including massive case statements for the by production season one) so if you haven't gone deep into those formulas before I recommend checking out the documentation or I'd be happy to connect with you and walk through how I built them - there will be a lot you'll have to update in those formulas that will be unique to your data in addition to the dashboard filters. 

I hope this is helpful, and good luck! (I had a bit of trouble figuring out how to add an attachment to this thread so hoping the file below works!)

Sarah Covie
scovie@porttix.com

PortTIX, Portland Maine

COVID-19ReturnsbyTICKETSexample.dash

This is great, Sarah.

For those wanting to try out Sarah's dashboard, but don't have that historical number to code in as "original" amounts, the Ticket Sales by Period report will get you something to work with. For the appropriate Event filters, you might set Perf Dates from 13-Mar-2020 through 31-Dec-2020, and the Sale Dates from 01-Jan-1900 through 13-Mar-2020. Group by Production Season and Price Type Category, probably include Comps, and you'll get a snapshot of where you were in tickets and revenue as of 13-Mar-2020.

Sarah... I can't thank you enough! Huge thanks from across the pond!

I am a bit of a data nerd so can't wait to get stuck into what you have shared.

Finding the time to do this with a 2 year old and 5 year old at home as well during lock-down is another matter!

I'm sure i'll have questions... thanks so much for now!

Thanks Chris... i've been wracking my brains in terms of how i can get figures out of tessitura in terms where sales were for a particular show on a particular date in the past. This would then allow me to track refund numbers as Sarah has done. 

I'm assuming that the Ticket Sales by Period report will allow me to do this?

Hi James,

Briefly responding from mobile, these instructions should help: https://www.tessituranetwork.com/Help_System_v151/Content/Tessitura%20Analytics/Sharing%20and%20Printing/Importing%20a%20Dashboard.htm 

Brilliant! Thanks Chris, I have it all plugged into our data now.

Chris, is there a way to find out the original number of orders (from a date in the past) on a specific date as you have shown above with tickets?

Hi James,

Yes. I recommend, for quickest access to that number, adding a widget to Sarah' dashboard for the Original Count of Orders. The widget should be using the Finance cube. Disable the Dashboard filters except for Season Fiscal Year and Production Season. Then add Widget filters for

• Performance Date, using the Calendar type filter, select from 13-Mar to Latest Date
• Order Date, using Calendar type filter, select from Earliest Date to 13-Mar

And then a Value for the Unique Count of "Order ID". 

Glad it was helpful! And it seems like you've got what you need to get going from Chris :) 

Best of luck finding some quiet time to dive into it!

- Sarah

Thanks Chris,

Another question, when i open up a brand new dashboard, how do i get it to pull from multiple data cubes? 

Also, is it possible to get two separate widgets to reference each other as part of a formula on a third?

So with the below two widgets, can i create a 3rd which minuses the two unique order ID figures? This would give me how many orders have been refunding since we closed. But the issue is that these two widgets pull from different cubes. 

I've been working hard this week on a sql procedure that in a nutshell tells us where our returned tickets came from and where they went.  It is not 100% accurate, but good enough to glean information.  OA stands for On Account.  The On Account column in the middle accounts for any money put on account from these tickets, before being applied elsewhere: refund, contribution, next year sub.   Phoenix is the name of our contribution fund to make STC rise again.  Cash direct is money directly refunded without using On account, as opposed to Cash OA.  Donated tickets are tickets returned in an order in benevolent mode.   FY21 Sub OA is money from the returned tickets that went to On account, then went to pay for a subscription for next year.  Balance OA is the amount still sitting in on account, attributable only to the returned tickets.  This is a pivot table so you can slice and dice as you like.   Happy to share the procedure if there are any other nerds out there who want to see the code.

Cheers,

BWG

I couldn't figure out how to do it in Analytics, so I ended up creating a custom report that counts up the number of tickets where there is a transaction type of "ticket refund". The end result shows ticket counts and dollar amounts for tickets that were returned as well as tickets "donated", and then calculates percentage of all tickets, including seats that are still ticketed for the selected performances. The "Refund" Column is for returns to original payment, and Gift Certificate and On Account are returns to those payment methods. Happy to share the report if anyone is interested.

Sara