While I'm putting in a ticket on TASK, I'm curious if the community has any thoughts on a situation I presently find myself in ...
I'm trying to cobble together a few data sets for a telemarketing firm that's working with us, and I'm wondering what the easiest way is make the dream a reality. Here's what my telemarketing friends need-- All the standard constituent information like: Constituent ID Salutation First Name & Last Name Address City State Zip Phone #s Email All of that's pretty easy to get at in a single place, so I'm not too terribly stressed about that. I have my extractions already together and have saved the lists, so this info is easy. HOWEVER, I'm having trouble figuring out how best to also include the following information that my tele-friends desire: Up to 8 year ticket purchase info for subscriptions and single ticket purchases, including ... Performance Date Performance Name Subscription Year & Series (if applicable) Total Cost of ticket or Subscription Purchase # of Seats purchased Seating section/location
So what I'm currently considering is cross referencing different reports and combining the data into one sheet for my telemarketers. However, I've yet to find the combination of reports to make this happen. That said, if there's a smarter (or easier) way to do this using something like SQL, I'm game to giving it the old college try (with a little bit of coaching). Knowing what I know about SQL, I'm actually wondering if this would be terribly easy in SQL if one were so inclined as to know how to tease out such information? I presume somebody somewhere out there has done this without creating custom reports and the like?
Brian,
The easiest way to do this (and I can't think of a way without SQL unless someone has a custom report out there) is to pull it from the ticket and subscription tables. The names for these vary by org but you can look them up. If you look at the Ticket History tab of a constituent record you should see the fields you'd have available.
Displaying all that information coherently in one layout is going to take some thought. What I would suggest (if the telemarketers have the resources) is to send them three files. Constituent data, ticket history and subscription history. Include the constituent number on each file and let them marry it up in their database.
If what they are wanting is a call sheet, you'd need to do a custom report which would look similar to a constituent bio. Not too tricky if you have a report writer/dba around. If you don't have that resource, it may be worth contacting Tessitura consulting for a quote on a custom report.
Thanks, Levi!
I figured SQL would be my destination, I'll investigate that further. They're definitely looking for a call sheet, but I'm afraid I'm the closest thing to a report writer/dba around this joint (HA!), and the custom report option, well, isn't an option for me.
Is there a way that I focus my SQL queries on lists that I have built in Tessitura rather than blindly combing through the entirety of the tables?
Hey Brian,
What system do your TM folks use for their lead creation?
For example, using the following SQL query I can *almost* fill in all of the blanks from a single table--
select customer_no, num_seats, section, season, scrp_amt, series_no fromT_SUBSCRIPTION_HISTwhere season = '41'
Though I'd like to be able to restrict my search solely to the lists I've already pulled in Tessitura that contain only the names I need to be looking at. I feel like there's a way to do this?
Hi Brian,
Perhaps Travis's report will help you - but to answer your question, if you are querying any table with a customer_no column (or if you're not, you can still do this by joining T_CUSTOMER) you can use the following in your WHERE clause to limit to a list:
WHERE customer_no in (select customer_no from T_LIST_CONTENTS where list_no = [your list #]
Let me know if that is confusing!
Frannie
Yes, using a list to filter is pretty straighforward in SQL. The data you are looking at will have to have the customer number in it. The simplest example is to pull customer records by a list:
select *
from t_customer
where customer_no in (select customer_no from t_list_contents where list_no = ##your list number here##
Frannie--
THANK YOU!!! I'm going to look into Travis' solution but I'm also collecting my notes for the raw SQL side of things as well.
Thank you!