Any list gurus able to help me set up a report?
Hey Amy!
Assuming your subscribers purchase a package of some kind and your package history tables are recording correctly, you should be able to run a list of "Package History Season" against all of your past seasons and get a list of anyone who has been a past subscriber.
Regarding a season ticket every year for the last 5 years, the only way that occurs to me is compounding a number of lists on top of each other and working out carefully the "ands" and "has" vs. "in" situations which does not seem very fun or else just writing an SQL query (if you can do that or have a database person willing to do it for you) and pasting that into the List Manager window (which is what we did).
Good luck!
John
Good ideas! I also just remembered I can import a list into an Attendance By Performance, so that helped for one of the lists I was trying to find. Thanks for the input!!!!!
Here's a sample query using the manual edit, which you could adjust to your package seasons.
Select Distinct a.customer_no
From V_CUSTOMER_WITH_PRIMARY_GROUP a WITH (NOLOCK)
JOIN
vs_package_history s (nolock) on s.customer_no = a.customer_no
Where IsNull(a.inactive, 1) = 1
AND EXISTS
(
select 1 from vs_package_history (nolock)
where a.customer_no = vs_package_history.customer_no
and
vs_package_history.season in (242,285,147,181,208 REPLACE WITH YOUR PACKAGE SEASON NUMBERS)
having count (distinct vs_package_history.season) >=3 REPLACE WITH HOWEVER MANY OUT OF THE SEASONS JUST ABOVE YOU WANT INCLUDED-MY EXAMPLE IS 3 OUT OF FIVE
)
Honestly, I would use T-STATS for this. You could then separate out by season. You could run a packages, seats, or tickets cube. If you give all your season ticket price types a category of season or something like that, you could easily compile a report of season ticket holders. You could even break it down further into season. Once you have your cube set, you can then export that information as a list directly into Tessitura. The other huge benefit of setting up a T-STATS cube for this is it will update nightly.
Chris Cuhel
The 5th Avenue Theatre
You can run a report by Price Type or MOS if you have separate ones just for subs.
Ellen Holt
Box Office Manager
Celebrity Series of Boston
20 Park Plaza, Suite 1032
Boston, MA 02116
617-598-3224 (p)
617-598-3291 (f)
www.celebrityseries.org
From: Tessitura Ticketing Forum [mailto:forums-ticketing@tessituranetwork.com] On Behalf Of Amy Lehman Sent: Tuesday, August 16, 2016 9:16 AM To: Ellen Holt Subject: [Tessitura Ticketing Forum] List builder Question
My question is:
I am constantly asked for lists that include anyone who has EVER been a subscriber/season ticket holder and I just can't think of the easy way to do that. We put a constituency on current subscribers, but that constituency expires at the end of the fiscal year.
Also, I asked for reports such as: "Give me a list of anyone who has purchased a season ticket every year the last 5 years." How do I set those up correctly!?
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Ticketing Forum. You may reply to this message to post to the Ticketing forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
I would also use T-STATS to create the list/lists that you need. I use it all the time to create list segments with specific price types, seasons etc.
BRENDA FEHLBERG | Customer Relationship Executive |
Tasmanian Symphony Orchestra Pty Ltd
From: Amy Lehman <bounce-amyfranklin2534@tessituranetwork.com> Sent: 8/16/2016 9:12:18 AM