New Subscribers for next day triggered email

Hey Chris,

I'm interested to see what other folks' responses are—we've also had a heck of a time pulling subscriber data (both current and past subs) through List Manager/Extraction Manager.

Here's what we do at our organization:

We run a Package Order Listing report through Reports & Utilities, update the Season field, and make the Order Date Start/Order Date End both the day we're trying to pull the list for. Then I save it as a list so I can use it for various other lists/extractions. As far as I can tell, this gives us the most accurate info for folks who have subscribed.

I hope this works for you, or that you're able to find an easy solution!!

Best,
Emily

Hey Emily,

Unfortunately we need the list to be dynamic and just generate each morning so WordFly can send a triggered email.

I thought about just doing a scheduled report...but it would still require some clean up. I'm really someone has some SQL code I can just plot into a new list and be done. LOL

Also..fun fact....I used to be the Senior Manager of Ticketing and Patron Services at LOKC.

Chris

Hey Emily,

Unfortunately we need it to be dynamic list for WordFly to send triggered emails. I thought about a report but it would still require some cleaning as I'm sure incorrect people would be brought in that we don't want.

Also...fun fact.....I used to be Senior Manager of Ticketing and Patron Services at LOKC what feels like a hundred years ago no. LOL

Chris

Have you dabbled with the Subs Summary criteria set in List/Extraction Manager? I usually go to those if I need real-time sub info since Package History is slower to update. Usually a combo of the three criteria:

- Subs Summary Season = [season_no in question]

- Subs Summary Amount Recd >= $1 (to pull in all paid orders)

- Subs Summary Balance Due = $0 (to pull in fully paid orders, exclude partially paid if you like)

If you have any comp subs floating around (we have a couple for our artistic/managing directors, for example), you can do an alternate set of criteria where the Amount Recd and Balance Due are both $0, to grab those as well.

The trickiest part would be limiting that to just the past 24 hours, I've never actually tried that myself. There are Subs Summary criteria for Last Confirmed and Last Seat Change, and a last_update_dt column in the underlying vs_cust_subscription_summary view, but I fear any of those might lead to false positives if a sub order gets opened or updated again by a user, and then pulls into your daily list as if it was a new order even though the renewal already happened. Maybe an extra suppression list of all renewals could help ensure that folks only pull into the 'new subscriber' list once and then are always suppressed afterward. I'm also not sure if you want to target just brand new subs, or all subs from the previous day (including both new subs and rollovers being renewed), but some extra lists could probably cover that area as well.

That's as far as my brainstorming goes!

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?

Second question....are these seated package orders?

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.