New Subscribers for next day triggered email

Hey Chris, 

I have an overall subscription acquisition list that Subscription Season with "has [future season], does not have [current/past season]". Adding Subscription Order Date = RunDate -1 seems to do the trick for me. I think the sub season is pulling from a local table, so not sure how useful this will be to you, but this is the query:

SELECT DISTINCT a.customer_no
FROM V_CUSTOMER_WITH_PRIMARY_GROUP a WITH(NOLOCK)
JOIN(
SELECT a1.customer_no
FROM lvs_sub_hist a1 WITH (NOLOCK)
WHERE a1.order_dt = DateAdd (dd,-1, CONVERT (varchar, GetDate(), 112))) AS e ON e.customer_no = a.customer_no
WHERE a.inactive = 1
AND EXISTS (
SELECT *
FROM lvs_sub_hist WITH (NOLOCK)
WHERE a.customer_no = lvs_sub_hist.customer_no
AND lvs_sub_hist.season IN (135, 134))
AND NOT EXISTS(
SELECT *
FROM lvs_sub_hist WITH (NOLOCK)
WHERE lvs_sub_hist.customer_no IN (
SELECT customer_no
FROM V_CUSTOMER_WITH_PRIMARY_GROUP
WHERE customer_no = a.customer_no)
AND lvs_sub_hist.season IN (126, 127))

PS - I used to be Senior Ticketing and Patron Services Representative at LOKC myself :) 

LOL....it is such a small world. 

Quick question....where are the season ID numbers pulling? Is that from TR_SEASON or from the Production Seasons tab of ticketing setup?

The season ID numbers are from TR_SEASON. And I believe it's pulling seated, paid package orders (or at least partially paid). The Subscription Season criteria element would avoid pulling any single tickets once those are on sale. 

The season ID numbers are from TR_SEASON. And I believe it's pulling seated, paid package orders (or at least partially paid). The Subscription Season criteria element would avoid pulling any single tickets once those are on sale.