Deduplication based on a Data Extension field

Question

Our client stores its customers' information in a DE called Customers. A field called IsMember describes whether the customer is a member or not. The email field in this DE is not the subscriber key. Each customer has another Id that is unique. So, more than one customer can share the same email address.

From now on, they would like to treat their customers differently based on this field. The emails are personalized and the AMPScript decides what to write to each customer based on their membership status, so that part is not a problem.

What they would like to do is to deduplicate emails when they not members and not deduplicate them when they are. But this is a policy one decides in the Send Email Activity or the User-Initiated Email.

Of course, there is a manual way of handling this and that is to have two different sends for each marketing email. But this can be quite a time-consuming task and is easy to forget.

Is there a simpler workaround to address this need?

Update: Sorry for not mentioning this. But our client has no SQL knowledge and would like to take over the email send tasks. So I am trying to prepare everything for them with little coding skills as possible.

Answer 1

Why don't you use two SQL queries to handle this?

You can deduplicate non members using something like:

SELECT

ContactID AS 'ContactID',
IsMember AS 'IsMember '

FROM
(SELECT DEST.ContactID , DEST.IsMember,
        ROW_NUMBER() OVER (PARTITION BY DEST.IsMember ORDER BY DEST.CreationDate DESC) 
        AS Row

    FROM Customers DEST WHERE DEST.IsMember = 'false'
 ) as s
 WHERE row=1

And then use another SQL query to get all contatcs that are members.

Answer 2

I think Rachid made an excellent suggestion with his query.

You could also break it up into several queries, add them to an automation, and they both add the right subscribers to a target DE.

In this case your automation would contain one query for deduplicating (i.e. what Rachid shared), and one to get remaining subscribers and append those.

However, since you mentioned your client doesn't have SQL knowledge, why not propose a drag-and-drop alternative like DESelect to segmented / create targeted lists?