• Replies 1 reply
  • Subscribers 333 subscribers
  • Views 168 views
  • Users 0 members are here
Related

Post-Show Email Dynamic Lists

Laura Scholl
$organization

Hi all,

We work in WordFly to send triggered post-show emails to our audiences each night. We use a dynamic list for every production which includes Performance Code (we select all of the performance codes for a single production) and performance date (where Performance Date <= Run Date and Performance Date >= Run Date -1). 

We are running into an issue where if a customer has tickets to two different productions on consective days, they are getting the post show note for the performance on the second day one day early. In other words, they are receiving their post show note before they ever attend the show.

I am guessing this has to do with the Performance Date criteria in our list, but we have tried to change the Performance Date criteria to just read "= Run Date" and the post show note list pulled in 0 constituents.

Is anyone else having this issue? Does anyone have a solution??

Thanks!

Parents
  • $organization
    Laura - 

    I'm pasting my custom SQL below so you have a complete reference, but I would guess that your solution will be that you want something like Performance Date <= Run Date and Performance Date >= Run Date. I've been told it has to do with the minutes and the seconds. So, your existing set up works more accurately than the new attempt, but is itself too inclusive.

    The below query covers the following ideas, and once set up, is valid for the entire season regardless of the production:
    • ATTENDED date (via NScan function)
    • perf of reference is in specified season and venue (we sometimes have public shows and ed shows and multiple venues running--I build separate campaigns to cover these variables)
    • has a valid email address + permissions
    • has certain constituencies (my best route for suppressing press comps etc without having to get super complicated)
    • plus our two seed lists

    JAMIE O'BRIEN

    ASSISTANT DIRECTOR OF DIGITAL SERVICES

    THE NEW 42ND STREET
    229 W 42ND STREET, NEW YORK, NY 10036
    JOBRIEN@NEW42.ORG
    646.223.3063
    WWW.NEW42.ORG
    The New 42nd Street



    --------------------------------------------------------------------






    Select Distinct a.customer_no 
     From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)
     JOIN (Select a1.customer_no From LV_ATTENDANCE_HIST a1 WITH (NOLOCK) 
    JOIN LVS_TKT_HIST a2 WITH (NOLOCK) ON a2.season in (74) and a2.venue_id in (2) and a1.customer_no = a2.customer_no and a1.perf_no = a2.perf_no
    Where a1.perf_dt = DateAdd(dd,-1,Convert(varchar,GetDate(),112))) as e ON e.customer_no = a.customer_no
     JOIN (Select a1.customer_no From T_CUSTOMER a1 WITH (NOLOCK) Where ISNULL(a1.emarket_ind,3) in (3)) as g ON g.customer_no = a.customer_no 
     JOIN (Select a1.customer_no From vs_eaddress a1
       Where a1.address like '%@%'
       AND IsNull(a1.inactive,'Y')='N') as i ON i.customer_no = a.customer_no
     Where  IsNull(a.inactive, 1) = 1 
     AND EXISTS (select * from vxs_const_cust WITH (NOLOCK) where a.customer_no = vxs_const_cust.customer_no and vxs_const_cust.constituency IN (23,101,45)) 
    UNION
    Select Distinct a.customer_no 
     From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)
     JOIN (Select a1.customer_no From t_list_contents a1 WITH (NOLOCK) Where a1.list_no in (2362,19773)) as e ON e.customer_no = a.customer_no
     Where  IsNull(a.inactive, 1) = 1

      
      




    On Mon, Sep 28, 2015 at 12:57 PM, Laura Scholl <bounce-laurascholl8097@tessituranetwork.com> wrote:

    Hi all,

    We work in WordFly to send triggered post-show emails to our audiences each night. We use a dynamic list for every production which includes Performance Code (we select all of the performance codes for a single production) and performance date (where Performance Date <= Run Date and Performance Date >= Run Date -1). 

    We are running into an issue where if a customer has tickets to two different productions on consective days, they are getting the post show note for the performance on the second day one day early. In other words, they are receiving their post show note before they ever attend the show.

    I am guessing this has to do with the Performance Date criteria in our list, but we have tried to change the Performance Date criteria to just read "= Run Date" and the post show note list pulled in 0 constituents.

    Is anyone else having this issue? Does anyone have a solution??

    Thanks!




    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!

Reply
  • $organization
    Laura - 

    I'm pasting my custom SQL below so you have a complete reference, but I would guess that your solution will be that you want something like Performance Date <= Run Date and Performance Date >= Run Date. I've been told it has to do with the minutes and the seconds. So, your existing set up works more accurately than the new attempt, but is itself too inclusive.

    The below query covers the following ideas, and once set up, is valid for the entire season regardless of the production:
    • ATTENDED date (via NScan function)
    • perf of reference is in specified season and venue (we sometimes have public shows and ed shows and multiple venues running--I build separate campaigns to cover these variables)
    • has a valid email address + permissions
    • has certain constituencies (my best route for suppressing press comps etc without having to get super complicated)
    • plus our two seed lists

    JAMIE O'BRIEN

    ASSISTANT DIRECTOR OF DIGITAL SERVICES

    THE NEW 42ND STREET
    229 W 42ND STREET, NEW YORK, NY 10036
    JOBRIEN@NEW42.ORG
    646.223.3063
    WWW.NEW42.ORG
    The New 42nd Street



    --------------------------------------------------------------------






    Select Distinct a.customer_no 
     From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)
     JOIN (Select a1.customer_no From LV_ATTENDANCE_HIST a1 WITH (NOLOCK) 
    JOIN LVS_TKT_HIST a2 WITH (NOLOCK) ON a2.season in (74) and a2.venue_id in (2) and a1.customer_no = a2.customer_no and a1.perf_no = a2.perf_no
    Where a1.perf_dt = DateAdd(dd,-1,Convert(varchar,GetDate(),112))) as e ON e.customer_no = a.customer_no
     JOIN (Select a1.customer_no From T_CUSTOMER a1 WITH (NOLOCK) Where ISNULL(a1.emarket_ind,3) in (3)) as g ON g.customer_no = a.customer_no 
     JOIN (Select a1.customer_no From vs_eaddress a1
       Where a1.address like '%@%'
       AND IsNull(a1.inactive,'Y')='N') as i ON i.customer_no = a.customer_no
     Where  IsNull(a.inactive, 1) = 1 
     AND EXISTS (select * from vxs_const_cust WITH (NOLOCK) where a.customer_no = vxs_const_cust.customer_no and vxs_const_cust.constituency IN (23,101,45)) 
    UNION
    Select Distinct a.customer_no 
     From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)
     JOIN (Select a1.customer_no From t_list_contents a1 WITH (NOLOCK) Where a1.list_no in (2362,19773)) as e ON e.customer_no = a.customer_no
     Where  IsNull(a.inactive, 1) = 1

      
      




    On Mon, Sep 28, 2015 at 12:57 PM, Laura Scholl <bounce-laurascholl8097@tessituranetwork.com> wrote:

    Hi all,

    We work in WordFly to send triggered post-show emails to our audiences each night. We use a dynamic list for every production which includes Performance Code (we select all of the performance codes for a single production) and performance date (where Performance Date <= Run Date and Performance Date >= Run Date -1). 

    We are running into an issue where if a customer has tickets to two different productions on consective days, they are getting the post show note for the performance on the second day one day early. In other words, they are receiving their post show note before they ever attend the show.

    I am guessing this has to do with the Performance Date criteria in our list, but we have tried to change the Performance Date criteria to just read "= Run Date" and the post show note list pulled in 0 constituents.

    Is anyone else having this issue? Does anyone have a solution??

    Thanks!




    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!

Children
No Data