Identifying multi-buyers with Extractions and Lists

Jim –

I doubt this will fully answer your question, but I hope it’s helpful in moving past your ‘extractor’s block.’ This is the custom query our IT director wrote when we needed to pull a list/extraction based on a customer having a specific number of shows (here, 2) in our current season. We run it as one segment within the extraction and carefully use the suppression option within other segments to help filter out others we don’t want to include.

Select a.customer_no

FROM dbo.T_CUSTOMER a (NOLOCK)

JOIN (                    SELECT customer_no, perf_name

                                from LVS_TKT_HIST h

                                WHERE h.season = 30

                                AND comp_code IS NULL

                                GROUP BY customer_no, perf_name) hist

ON hist.customer_no = a.customer_no

WHERE  IsNull(a.inactive, 1) = 1

GROUP BY a.customer_no

HAVING COUNT(hist.perf_name) = 2

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!

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!

Jim –

I doubt this will fully answer your question, but I hope it’s helpful in moving past your ‘extractor’s block.’ This is the custom query our IT director wrote when we needed to pull a list/extraction based on a customer having a specific number of shows (here, 2) in our current season. We run it as one segment within the extraction and carefully use the suppression option within other segments to help filter out others we don’t want to include.

Select a.customer_no

FROM dbo.T_CUSTOMER a (NOLOCK)

JOIN (                    SELECT customer_no, perf_name

                                from LVS_TKT_HIST h

                                WHERE h.season = 30

                                AND comp_code IS NULL

                                GROUP BY customer_no, perf_name) hist

ON hist.customer_no = a.customer_no

WHERE  IsNull(a.inactive, 1) = 1

GROUP BY a.customer_no

HAVING COUNT(hist.perf_name) = 2

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!

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!