0

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.

  • IMHO this can/should be solved with SQL, deduplicating records and outputting into a new sendable DE. Why isn't this a viable solution? – Lukas Lunow Nov 27 '19 at 9:08
  • @LukasLunow but on that destination sendable DE we again have to choose a single strategy between deduplicating or not. So a single DE would probably not suffice? – Disasterkid Nov 27 '19 at 9:09
  • @TammerSalem - the reference you provide is for Core only, and not for SFMC – Lukas Lunow Nov 27 '19 at 11:04
  • @LukasLunow - thanks for pointing out - this is MC not Core (my mistake!) - I'll delete – Tammer Salem Nov 28 '19 at 12:16
2

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.

  • Sorry for not mentioning this. I updated the question. 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. – Disasterkid Nov 27 '19 at 9:13
  • Your solution works great, except my client is nowhere near SQL savvy I'm afraid. – Disasterkid Nov 27 '19 at 9:14
  • 1
    Oh okey. I think it would be touchy without coding. Especially because deduplicating is not available on Journey Builder, otherwise it would have been easy on a journey. – Rachid Mamai Nov 27 '19 at 9:18
  • 1
    Trust me, it is the case for mostly all clients. In my opinion, SQL is inescapable when using SFMC. Exept for really simple use cases with no complex requirements. Usually, I have a hard time in the beginning explaining this to my clients and at the end they just accept it and get used to it :) – Rachid Mamai Nov 27 '19 at 9:39
  • 1
    Still, I take time with my clients to gather their needs and then normalize data on the data model. This way, it is easy for them to create audiences using Data Filter. – Rachid Mamai Nov 27 '19 at 9:44
0

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.