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, I just rebuilt a report just like this for our telemarketing campaign. The only parameter is a list number.
Our pulls all the patron info and the following:
Last five ticketed perfs and dates , total payed amount , num seats
Last five fyear subs if they had them including the Fyear, description, price, num seat
Last five Contributions, dates, amounts and a suggested ask
If you want to see it or need help shoot me an email.
Travis
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
Travis!!! Your solution sounds like my dream scenario ... I'm emailing you now!
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!
We use an output set to pull data for our telemarketing firm – using the Ticket season and ticket performance name, as well as subscription season and pkg. It returns a million rows, but the telemarketing firm uses that.
Adrienne
From: Tessitura Shared Reports Forum [mailto:forums-shared.reports@tessituranetwork.com] On Behalf Of Brian JonesSent: April-22-13 2:35 PMTo: Adrienne SteerSubject: [Tessitura Shared Reports Forum] Telemarketing Data Pull
Normal 0 false false false EN-US X-NONE X-NONE
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 IDSalutationFirst Name & Last NameAddressCityStateZipPhone #sEmailAll 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 DatePerformance NameSubscription Year & Series (if applicable)Total Cost of ticket or Subscription Purchase# of Seats purchasedSeating section/location
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Shared Reports Forum. You may reply to this message to post to the 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!
Hi Travis,
I am interested in your report too. Thanks !
Sabina ( sspilkin@sandiegosymphony.org )
From: Tessitura Shared Reports Forum [mailto:forums-shared.reports@tessituranetwork.com] On Behalf Of Travis ArmbusterSent: Monday, April 22, 2013 1:02 PMTo: Sabina SpilkinSubject: Re: [Tessitura Shared Reports Forum] Telemarketing Data Pull
From: Brian Jones <bounce-brianjones7980@tessituranetwork.com>Sent: 4/22/2013 1:28:58 PM
May I have a copy as well? Thanks
bcrowe@kcballet.org
From: Tessitura Shared Reports Forum [mailto:forums-shared.reports@tessituranetwork.com] On Behalf Of Sabina Spilkin Sent: Thursday, April 25, 2013 1:20 PM To: Brenda Crowe Subject: RE: [Tessitura Shared Reports Forum] Telemarketing Data Pull
From: Tessitura Shared Reports Forum [mailto:forums-shared.reports@tessituranetwork.com] On Behalf Of Travis Armbuster Sent: Monday, April 22, 2013 1:02 PM To: Sabina Spilkin Subject: Re: [Tessitura Shared Reports Forum] Telemarketing Data Pull
From: Brian Jones <bounce-brianjones7980@tessituranetwork.com> Sent: 4/22/2013 1:28:58 PM