How to correctly read response from a WSProxy call to retrieve DE rows (more than 2500 using HasMoreRows) and update All Subs List

Question

I have a Data Extension holding 80,000 rows of USA customer data. Within the DE is a field named "Country_Code" which sets a default value of "US" for everyone. My All subs list also has a custom attribute field named "Country Code".

I'm trying to run a script on the parent BU that matches all contacts from the USA DE, on the All subs list, and if found update the Country Code to "US".

I'm using the list update ssjs function:

var subscriber = {

"Attributes": {"Country_Code":Country_Code}};

var subObj = Subscriber.Init(sub_key);

var response = subObj.Update(subscriber); 

Combined with a "Has more rows" WSProxy loop.

All I'm doing is pulling the "Id" field from the USA DE and matching on that (as the country code value will always be "US" for this pool of customers)

Currently, when I run the script, it times out in automation studio after 30 mins, and It updates about 5,000 rows.

I cannot re-run the script again to pick up the rest of the contacts, or automate it to run consistently, because it will always look at the same top 5,000 rows.

Right now my USA customer DE is at 80,000 but will potentially grow. This DE will only get bigger.

When I run the script in a cloud page and write the results Write("SubscriberID: " + val + "<br>"); I can see that the first 2,500 rows appear successfully but after the "Has more rows" function kicks in a counter appears between each result like so:

(Row 2,498) SubscriberID: US1111111111
(Row 2,499) SubscriberID: US1111111112
(Row 2,500) SubscriberID: US1111111113
(Row 2,501)SubscriberID: 126530 <-- ?????
(Row 2,501) SubscriberID: US1111111114
(Row 2,502)SubscriberID: 126531 <-- ?????
(Row 2,502) SubscriberID: US1111111115
(Row 2,503) SubscriberID: 126532 <-- ?????
(Row 2,503) SubscriberID: US1111111116

Which leads me to believe I'm not reading the data right, and that the WSProxy isn't being as efficient or fast as it's supposed to be.

Any help or pointers here would be greatly appreciated.

Here's the full code:

<script runat="server">

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

var config = {

  name: "[MY DE CUSTOMER KEY]",

  cols: ["Id"],

  filter: {

    Property: "Id",

    SimpleOperator: "isNotNull",

    Value: " "

  }

}

var records = retrieveRecords(config);

//Write(Stringify(records));

function retrieveRecords(config) {

  var prox = new Script.Util.WSProxy();

  var records = [],

    moreData = true,

    reqID = data = null;

  while (moreData) {

    moreData = false;

    if (reqID == null) {

      data = prox.retrieve("DataExtensionObject[" + config.name + "]", config.cols, config.filter);

    } else {

      data = prox.getNextBatch("DataExtensionObject[" + config.name + "]", reqID);

    }

    if (data != null) {

      moreData = data.HasMoreRows;

      reqID = data.RequestID;

      for (var i = 0; i < data.Results.length; i++) {

        var result_list = data.Results[i].Properties;

        var obj = {};

        for (k in result_list) {

          var key = result_list[k].Name;

          var val = result_list[k].Value

          if (key.indexOf("_") != 0) obj[key] = val;

//Write("ID: " + val + "<br>");

var subscriber = {

          "Attributes": {"Country_Code":"US"}};

          var subObj = Subscriber.Init(val);

          var response = subObj.Update(subscriber);

        }

        records.push(obj);

      }

    }

  }

  //return records;

}

</script>

Answer 1

This is a script I usually use as a starting point for this type of thing:

<script runat="server">

var mid = 1234567; //MID of target BU -- OPTIONAL Default is current BU of script/page
var deCustKey = 'gg_de1'; //your DE's CustomerKey / External Key
var prox = new Script.Util.WSProxy(); //creates proxy

var subArr = [];
var moreData = true;
var reqID = null;

while(moreData) {

    var moreData = false;

    var deReturn = retrieveDERows(mid,deCustKey,reqID);

    var moreData = deReturn.HasMoreRows;
    var reqID = deReturn.RequestID;

    for (var a = 0; a < deReturn.Results.length; a++) {

        var attributes = [];
        var updateObject = {};

        var results = deReturn.Results[a];

        for (var i = 0; i < results.Properties.length; i++) {

            var name =  results.Properties[i].Name;
            var value = results.Properties[i].Value;

            if (name == "FirstName" || name == "LastName") {
                name = name.replace(/tName/g,"t Name")
            }

            if (name == "SubscriberKey") {
                var subkKey = value;
            }
            else if (name == "EmailAddress") {
                var email = value;
            }
            else if (name == "Status") {
                var status = value;
            }
            else {
                var object = Platform.Function.ParseJSON('{Name:"' + name + '",Value:"' + value + '"}')
                attributes.push(object);
            }

        }
        
        updateObject.SubscriberKey = subKey;
        updateObject.EmailAddress = email;
        if(status) {
            updateObject.Status = status;
        }
        updateObject.Attributes = attributes;

        subArr.push(updateObject);
    }

    var subUpdate = upsertIntoSubscribers(mid,subArr);

}

function retrieveDERows(mid,deCustKey,reqID) {

    if(mid) {
        prox.setClientId({ "ID": mid }); //Impersonates the BU
    }
    var cols = ["EmailAddress","SubscriberKey","FirstName", "LastName"]; //Columns you want retrieved

    if (reqID == null) {
        var desc = prox.retrieve("DataExtensionObject[" + deCustKey + "]", cols); //executes the proxy call
    } else {
        desc = prox.getNextBatch("DataExtensionObject[" + deCustKey + "]", reqID);
    }

    return desc;
}

function upsertIntoSubscribers(mid,subArr) {

    if(mid) {
        prox.setClientId({ "ID": mid }); //Impersonates the BU
    }
    var options = {SaveOptions: [{'PropertyName': '*', SaveAction: 'UpdateAdd'}]};

    var res = prox.updateBatch('Subscriber', subArr, options);

    return res;
}

</script>

This one pulls Subscriberkey and a couple random fields to pass back to All Subscribers from a Data extension, but you can easily alter it to fit your need.

It does bulk retrieve and bulk upsert via WSProxy to help optimize processing. But even with that, you might want to watch out for potential time-outs on the script in your script activity.

Update var cols = ["EmailAddress","SubscriberKey","FirstName", "LastName"]; //Columns you want retrieved to hold the columns you want retrieved from your DE. You then update the following if conditionals to match those columns.