Colleagues,
Can you help me thing through the criteria building process for a list/extraction segment that seeks to find multi-buyers in Tessitura? The outcome should provide me with:
* Buyers who have made multiple purchases within the past 24 months;* Buyer type can be subscription or single ticket purchase;* All transactions should be >= 0.01 (no comps)* Straight exchanges should not be included, but exchange upgrades can be.
I like to think of myself as a master extraction user, but all of the OR logic in this one stumps me. Thanks in advance for your ideas!
I’m sorry the picture didn’t post:
Ticket Date between ‘05/18/2008’ and ‘05/17/2010’ -- anything in past 24 months
Ticket Single Ticket Amount >= 1 -- not comps
Ticket Number of Unique Perfs >= 2 -- more than 1 performance (exchanges are netted out of the history tables, so not an issue)
The SQL version if you did a manual edit for list builder would be:
Select distinct A.customer_no
From t_customer a (nolock)
Where IsNull(a.inactive, 1) = 1
and exists (select * from vs_tck_hist e (nolock)
where a.customer_no = e.customer_no
and e.performance_dt between '05/18/2008' and '05/17/2010'
and e.tck_amt/e.num_seats > 0 group by e.customer_no
having count(distinct convert(char(12), e.performance_dt) + e.matinee_or_evening) > 2 )
The above should work if your site has not customized their performance history table; however, even if yours is customized selecting the equivalent fields should produce the correct results.
From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Lisa Lindvall Sent: Tuesday, May 18, 2010 3:54 PM To: llindvall@cfl.rr.com Subject: RE: [Tessitura Marketing Forum] Identifying multi-buyers with Extractions and Lists
Couldn’t you use the following?
From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Jim DeGood Sent: Tuesday, May 18, 2010 10:04 AM To: llindvall@cfl.rr.com Subject: [Tessitura Marketing Forum] Identifying multi-buyers with Extractions and Lists
* Buyers who have made multiple purchases within the past 24 months; * Buyer type can be subscription or single ticket purchase; * All transactions should be >= 0.01 (no comps) * Straight exchanges should not be included, but exchange upgrades can be.
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Marketing Forum. You may reply to this message to post to the Marketing 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!
Lisa,
I don't believe we have the "Ticket Number of Unique Perfs" as a criteria. Would you be able to post the values from T_KEYWORD system table for that? It sounds very useful.