1

Hello Salesforce Community, I'm new in Programming (around 6 months with JavaScript and Java) and even more rookie in Marketing Cloud (going to my second week) and I'm exploring a use case for a client but i'm still confused about what (or how) it's possible to achieve with ServerSide JavaScript or AMPScript.

Some insight to the best way to achieve this result would be greatly apreciated!

The use case is the following:

I have a General Data Extension with all the contacts and with following fields:

  • customer_id(text)
  • email(emailAddress)
  • opt_in(boolean).

Then, I have a second DE, with the exact same fields and contacts, that recieves the updated info of the contacts that changed their opt_in state.

What I want to do is the following: Using Activities from Automation Studio, update the General DE with the Second DE and change the boolean value for the ones who changed their state (meaning, from False to True and vice-versa).

In pseudo-code would be something like this:

FOREACH contact on GeneralDE THAT customer_id FROM GeneralDE IS EQUAL TO customer_id FROM SecondDE AND
   optin FROM GeneralDE IS NOT EQUAL TO optin FROM SecondDE) {

   UPDATE opt_in FROM GeneralDE to the value of opt_in in the SecondDE 
}

I've tried two diffenrent aproaches:

A) Run a Query to bring all the different data and then add a SSJS script to iterate through all contacts and change the boolean to the opposite.

Query to filter the data (name: query1)

  SELECT g.customer_id, g.email, g.opt_in
  FROM Opt_In_Geral as g
  INNER JOIN Opt_In_Test2 as t
  ON g.opt_in <> t.opt_in

SSJS:

<script runat=server language=javascript>

Platform.Load("core","1");

var query = QueryDefinition.Init("query1");
var data = query.Perform();

if(data) {
    for(var i = o; i < data.length; i++) {
        var optin = data[i]["opt_in"]
            if(optin == false) {
                data[i].Update({"opt_in" : true });
            } else {
                data[i].Update({"opt_in" : false});
            }
        });
    };
};
</script>

B)Directly update and filter the DEs's through SSJS.

var geralDE = DataExtension.Init("Opt_In_Geral");
var test2DE = DataExtension.Init("Opt_In_Test2");
var geralDEfalse = geralDE.Rows.Lookup(["opt_in"], [false]);
var geralDEtrue = geralDE.Rows.Lookup(["opt_in"], [true]);    
var test2DEtrue = test2DE.Rows.Lookup(["opt_in"], [true]);
var test2DEfalse = test2DE.Rows.Lookup(["opt_in"], [false]);

for(var i = 0; i < geralDEfalse.length; i++) {
    if(geralDEfalse[i]["customer_id"] === test2DEtrue[i]["customer_id"]) {
        geralDEfalse.Rows.Update({opt_in:"true"}, ["customer_id"], [test2DEtrue[i]["customer_id"]]);
    }
};       
    
 for(var i = 0; i < geralDEtrue.length; i++) {
    if(geralDEtrue[i]["customer_id"] === test2DEfalse[i]["customer_id"]) {
        geralDEtrue.Rows.Update({opt_in:"false"}, ["customer_id"], [test2DEfalse[i]["customer_id"]]);
    }
};

Both of them aren't working and I'm feeling there is a much easier way to do this... Sorry about the messy code and I'm already guessing that there would be some major errors but I can't unlock it by my own or find a similar example online.

Thank you in advance to everyone who care to help in any way.

  • Ok you describe what you do. But what do you want to achieve by this? Rather describe your problem better than the stuff you tried. – Johannes Schapdick Nov 24 '20 at 12:48
  • 1
    Thank you Johaness for your fast and usefull reply. This was my first post and I will take your advice from now on. I will test both your SQL solutions and check with my superior if it solves our problem. What I want to achieve is what you and Swati described , update the Main DE optin field from a Secondary DE through an automation.Thank you very much for the feedback. – Noronha Nov 24 '20 at 13:38
2

From my understanding it would be this one, (as per correction from Swati i changed the fields that should be requested)

SELECT t.customer_id, t.email, t.opt_in
FROM MASTER_DATAEXTENSION as g
INNER JOIN UPDATE_DATAEXTENSION as t
ON g.customer_id = t.customer_id
Where g.opt_in != t.opt_in

The thing with the inner join on your description: You need to join on a parameter that is identical and a unique for both dataextensions, otherwise you cannot bind them together (alias JOIN). Therefore the Join condition has to be g.customer_id = t.customer_id. To include the check of differences you would then add a where clause to this statement. This way you do not get records where opt_in stati are identical ;)

With this query you would know which customers have changed their status. You could then use this data to update the records in your master DE? And maybe there are more things to look for: Are you sure that both tables always have the same customers? If not what happens to those that are in the master_dataextension and not in the update_dataextension or vice versa.

And a guess by me: If you want to change the subscriber status for subscribers, you could also use an import_file activity. The target of that file activity would be to update the subscriber list. This way you can always change the subscribers status on a central point in the marketing cloud.

  • Hey Johannes .. Just out of curiosity, shouldn't the optin be taken from Opt_In_Test2, so that it just updates with what is in that table, for the records that have non matching values in both the tables, asOpt_In_Test2 has the latest update? – Swati Mishra Nov 24 '20 at 13:08
  • 1
    To be honest as per description yes, you are right ;) My priority was on the correction of the SQL Statement and its description. You could just take everything from Opt_In_Test. But since the DataExtensions are somewhat superficial and have fake names i did not pay close attention. – Johannes Schapdick Nov 24 '20 at 13:13
2

As I understand correctly, you have a Master DE and a Preference DE, where email opt in are updated. You can just run an hourly automation and a SQL to update your Master DE.

   SELECT g.customer_id, g.email, t.opt_in
   FROM Opt_In_Geral as g
   INNER JOIN Opt_In_Test2 as t
   ON g.customer_id = t.customer_id
   where g.opt_in <> t.opt_in

Ideally, the source that's updating the second DE, should update the General DE also, so you don't have to do this.

  • 1
    Thank you Swati for your fast and spot-on reply, as I said to Johannes I will check with my team if this solves our problem and let you know the outcome, Thank you so much for your help – Noronha Nov 24 '20 at 13:42

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.