• Replies 2 replies
  • Subscribers 332 subscribers
  • Views 75 views
  • Users 0 members are here
Related

New email subscribers in a dynamic list

Genevieve Burgett
$organization

Hi!

We have a large list of email subscribers that we need to enter into Tessitura (and are hoping to import a CSV to do this) and we would like to start sending a new subscriber welcome email through WordFly. I would like to set up a dynamic List Manager list that is connected to a triggered email in WordFly and pulls all new subscribers entered within the last week, but I am not sure the best way to create that list so that it doesn't pull all email subscribers in each time it runs.

A date associated attribute/interest/purpose would help with that because then I could use relative date criteria, but I am not seeing a create date for any of those data points that I could use as criteria. Any and all suggestions are welcome!

Thanks!

Genevieve

Parents
  • $organization

    I can give you an example for Interest since it's probably the simplest.  Requires you to build your list like normal for all but the create_dt, then save a copy, then in the copy, click "Show Query" and insert code as indicated below to get to create_dt.  Watch out for the end parenthesis before 'AS e' so it doesn't get commented out by the double hyphen preceding. If you understand how this works, you can use it just about anywhere with a create_dt. 

    SELECT DISTINCT a.customer_no
    FROM V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
    INNER JOIN
    (SELECT a1.customer_no
    FROM VXS_CUST_TKW AS a1 WITH (NOLOCK)
    WHERE a1.tkw IN (271) --whatever your tkw number is as selected using normal criteria dropdown method
    --begin add code--
    group by a1.customer_no
    having min(a1.create_dt)
    between
    DateAdd(dd, -7, CONVERT (VARCHAR, GetDate(), 112) + ' 23:59:59.997')    -- -7 days to get the last 7 days, or change to whatever
    and CONVERT (DATETIME, CONVERT (VARCHAR, GetDate(), 112))   -- TODAY
    ---end of add code ---
    ) AS e
    ON e.customer_no = a.customer_no
    WHERE a.inactive = 1

Reply
  • $organization

    I can give you an example for Interest since it's probably the simplest.  Requires you to build your list like normal for all but the create_dt, then save a copy, then in the copy, click "Show Query" and insert code as indicated below to get to create_dt.  Watch out for the end parenthesis before 'AS e' so it doesn't get commented out by the double hyphen preceding. If you understand how this works, you can use it just about anywhere with a create_dt. 

    SELECT DISTINCT a.customer_no
    FROM V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
    INNER JOIN
    (SELECT a1.customer_no
    FROM VXS_CUST_TKW AS a1 WITH (NOLOCK)
    WHERE a1.tkw IN (271) --whatever your tkw number is as selected using normal criteria dropdown method
    --begin add code--
    group by a1.customer_no
    having min(a1.create_dt)
    between
    DateAdd(dd, -7, CONVERT (VARCHAR, GetDate(), 112) + ' 23:59:59.997')    -- -7 days to get the last 7 days, or change to whatever
    and CONVERT (DATETIME, CONVERT (VARCHAR, GetDate(), 112))   -- TODAY
    ---end of add code ---
    ) AS e
    ON e.customer_no = a.customer_no
    WHERE a.inactive = 1

Children