Hey friends,
I've tried using graphical edit in list builder to find accounts that have and do not have email addresses but no matter how I set the operators, I find accounts with email addresses coming in when I am saying "not in" or "does not have". We want to make sure we can call guests who do not have an email address to advise about show postponements or cancellations.
- Chris
Chris,
Have not tried List Manager elements, but if you want SQL for it, this should do the trick:
SELECT *FROM T_CUSTOMER c WITH (NOLOCK) LEFT JOIN T_EADDRESS e WITH (NOLOCK) ON c.customer_no = e.customer_noWHERE e.customer_no IS NULL
John
Have you tried creating a list of customers who have and email address? That might be more precise, and then you can use it to create another list of people not in that list.
Households are going to potentially complicate this for you, of course, if you are planning on one communication per household. You'll want to use the affiliate rules on the first list to include customers where some other affiliate on the household does have an email address.
I've tried to get that to work in list manager and I get this error: Syntax Error in Query: Column names in each view or function must be unique. Column name 'customer_no' in view or function 'list_temp' is specified more than once. (List Sql)
It's times like this I wish I knew SQL better.
My bad. Replace the * with c.customer_no.
I was just pulling all the details, list manager always just wants the customer_no.
In order to make sure we get an (or as many email addresses as possible) I am setting the search to pull replace individual with household and add all primary affiliates. This is causing a headache it seems as it's pulling all accounts making it hard to not have accounts that don't have any email at all. Le sigh...this is when I wish list builder was easier to use.
Thank you John. It's getting there. I need to figure out a few things like removing inactive accounts and such. It's just so not easy to get this data if you aren't a SQL genius.
Sorry, I have been in the back end and out of list manager often enough that I forget about things like inactive accounts. Also, I am often asked to include those numbers in the data I pull.
Add in this to the end to clear out inactive accounts:
AND c.inactive = 1
Hi Chris,
Gawain's approach is one I have used before to tackle this. Have you tried it this way?
List 1: Criteria are EAddress Type IN (select all types available), and EAddress Inactive IN (select No). And given your requirements, you'd set this list to "Replace individual constituents with household and add all primary affiliates."
List 2: Criteria is List DOES NOT HAVE (select List 1). You would then add whatever other criteria that are qualifying your constituents.
Hope that helps!
-Michael Flaherty-Wilcox, Tessitura Network