How to Update a Data Extension through another Data Extension?

Question

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.

Answer 1

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.

Answer 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.