We are trying to write criteria for a mailing list. We want it to pull single-ticket buyers who bought 2 or more tickets to multiple shows within a season.
THOUGHTS?
This is not a problem that I already had a solution to, but I was bored tonight and thought I’d give it a shot. There may be others in this group that have a more eloquent way of doing it, but here’s what I came up with.
Going 1 production at a time, I added a group of patrons who purchased 2 or more tickets to each show. Then, once all of the patron data for each individual show was added, I went back and used the Having tool to remove any patron from the list who had a Unique Count of Event #'s that was less than 2.
Here’s what my criteria looked like:
In this example, Event # is obviously each individual show that I wanted to include in the results. Promotion # was all of the ticket promotions that we use for single ticket buyers (so this would exclude subscribers, groups, comps, etc). I used the Having tool for a unique count of ticket #'s to get patrons for each show that have more than 1 ticket. (Ticket Quantity was my first attempt, but that lead to a dead end). I found that trying to put all of the event #'s into a single group and THEN looking for event #'s having a unique count of 2 or more made resulted in patrons who bought 1 ticket to 2 shows, and you said you were looking for patrons who bought 2 or more tickets to 2 or more shows.
Once you add the group for the first production, it’s easy enough to duplicate the criteria and then add all of the rest of the productions. It looks like your box office handles a lot of different companies–so it might take some time to add all of the productions you’re interested in. Ultimately though, this will give you a list of anyone who has bought 2 or more tickets to any production.
Finally, you can create a group to remove patrons from the list having a unique event # count less than or equal to 1. This should get rid of the patrons who bought 2 or more tickets to just 1 show and result in just the patrons who purchased 2 or more tickets to 2 or more shows.
I’m 97.44% sure that this works. I did a quality check on a random sample of about 20% of the patrons that ended up on my mail list and they all fit the criteria.
Hope this helps!
Jeffrey
SLOC Musical Theater
1 Like
Hi Jeffrey, your Mailing List approach was going to be my example as well! I’m going to build off your idea but with a simplified example of:
- Order Date is Greater Than or Equal To
- Promotion is NOT Subscription (might capture comps)
- Having a Unique Count of Event # is Greater Than or Equal to = 2
2 Likes
Thanks Frank! Your solution is much simpler, but I think it would end up including patrons who bought single tickets to multiple events, and the OP was looking for 2 more more tickets to multiple events. That’s why I included the unique ticket number >= 2 for each event so that it would remove those single seats. Probably only a small subsection of patrons, so it might not matter.
2 Likes
Thank you both. We were able to get the data we were looking for thanks to both of your suggestions!
Happy New Year!
2 Likes