Reports for a Data Analysis

TM Desktop App Reports
#1

Hey all!

The county has requested some data from us to map ticket buyers and attendance behavior. I’m trying to figure out the best way to approach this.

They are looking for an Excel or CSV file showing:

  • Street Address
  • Venue of performance
  • Year of performance
  • Ticket Type (sub vs single tickets)
  • number of tickets purchased for that performance
  • Genre of performance

The report can be broken down by play. So a subscriber would have multiple entries on the sheet.

I can fill in Venue, Year, and Genre - but I’m trying to find a report that will show the patron’s address, promo code, and # of tickets by play.

“House Report - By Order (With Address)” seems close to something that might work, but it loses the Address field when I export it for some reason.

Any thoughts or ideas to how to gather this data?

Thanks!!

Donation Fundraising Data Reports - Giving Levels Bar Chart
#2

Hi Nick and welcome to the TM Community! A direct data export might be easiest here. You can export Ticket Data and get all these fields, then in Excel sum the quantity per performance per patron. (Ticket type could make this a little messier for folks with multiple ticket types to one event, but you could make that part of another column with “multiple types” as an option). If you haven’t directly exported data before, here’s the starter Artsman help page: https://help.theatremanager.com/theatre-manager-help/rExportBaseHeader

In Excel, I would start by using the advance filter option to copy unique values only for Patron # and Performance #. Using those two columns, now a unique combination, you can then rebuild the rest of the table:

  • Use VLOOKUP or INDEX(MATCH) to match on Patron # and pull over the primary address fields
  • Use VLOOKUP or INDEX(MATCH) to match on Performance # and pull over the venue, genre and year fields
  • Use SUMIFS to sum the number of tickets matching Patron # AND Performance #

Hope that helps jumpstart the brainstorming a little - sounds like a fun project!

3 Likes
#3

Hi Sophia!

Ahh you’re amazing! I’ll be giving this a try and will report back. Thank you!

#4

Just an update to say this worked great! Thank you!!

If anyone finds themselves in the same situation - An additional thing I did was create an extra column using ‘CONCATENATE’ to combine the Patron # and Performance #. I used this column to pull over the Promotions for the Ticket Type column using VLOOKUP, as you suggested for most of the table. This seemed to help a little bit with the patrons who had multiple promotion codes across multiple performances.

2 Likes