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.

4
  • IMHO this can/should be solved with SQL, deduplicating records and outputting into a new sendable DE. Why isn't this a viable solution? Commented Nov 27, 2019 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? Commented Nov 27, 2019 at 9:09
  • @TammerSalem - the reference you provide is for Core only, and not for SFMC Commented Nov 27, 2019 at 11:04
  • @LukasLunow - thanks for pointing out - this is MC not Core (my mistake!) - I'll delete Commented Nov 28, 2019 at 12:16

2 Answers 2

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.

7
  • 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. Commented Nov 27, 2019 at 9:13
  • Your solution works great, except my client is nowhere near SQL savvy I'm afraid. Commented Nov 27, 2019 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. Commented Nov 27, 2019 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 :) Commented Nov 27, 2019 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. Commented Nov 27, 2019 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?

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .