Author: Lizaveta Trafimuk

Published: December 16, 2024

When working with Salesforce Data Cloud activations, multiple related attributes are combined and stored as a single JSON attribute. This means that when activating to Marketing Cloud, all this data ends up in a single row within a Data Extension.

Salesforce has created a detailed article explaining how to parse this JSON data. The article covers two scenarios:

1. Using the data in an email: A guide for parsing JSON with the AMPscript function BuildRowSetFromJSON.
2. Using the data in Journey Decision Splits: A script for parsing JSON data and storing it in a separate Data Extension.

While the AMPscript function worked without issues, the SSJS script faced two key limitations:

1. The Rows.Retrieve function can only retrieve up to 2,500 rows of data from a Data Extension.
2. WSProxy does not allow deleting data from a Data Extension within a Shared Folder.

This article presents an enhanced version of the SSJS script that overcomes the limitations of the original Salesforce solution. The improved script can process more than 2,500 rows in a single Automation Studio activity and store data in a Shared Folder Data Extension. The schema below illustrates how data flows into Marketing Cloud and is processed in the Automation Studio Script Activity.

Here are the key improvements made to the code:

1. ThesetClientIdfunction has been added with MID and UserID to enable row deletions in a shared Data Extension. This is necessary because only the Data Extension's owner has permission to clear its data. If the user is not correctly set or is invalid, the error message "User not authorized to perform ClearData operation on DataExtension: DATA_EXTENSION_KEY" will persist. The ownership of the Data Extension can be changed by navigating to Email Studio > Subscribers > Shared Data Extension, locating the relevant Data Extension, and updating the owner. Additionally, there is a list of associated UserIDs available for reference.

   

2. To process more than 2,500 rows from a Data Extension, WSProxy was used with two important parameters: RequestID and HasMoreRows. The HasMoreRows parameter indicates whether additional records remain in the Data Extension and works in conjunction with the RequestID. If more records are available, the WSProxy function calls getNextBatch() to retrieve the next set of rows.

3. Based on my testing, processing approximately 10,000 rows took about 1.8 minutes to complete.

<script runat="server">
Platform.Load("core", "1.1.1");
try{
    var adjustedDE = "CUSTOMERS_WITH_FINANCIAL_ACCOUNTS_ADJUSTED";
    
    // Step 1: Deleting old data 
    var apiDel = new Script.Util.WSProxy();
    
    // For the Shared Data Extension add the code below with MID and UserId
    api.setClientId({
		"ID": "MID",
		"UserID":"UserID"
		});
		
    var properties = { "CustomerKey": adjustedDE }; 
    var action = "ClearData";
    var opts = {};
    var result = apiDel.performItem("DataExtension", properties, action, opts );
    
    // Step 2: Generating the Data Extension Adjusted normalizing the JSON Field
    var config = {
        name: "CUSTOMERS_WITH_FINANCIAL_ACCOUNTS", 
        cols: ["SubscriberKey", "Id", "EmailAddress", "FinancialAccount"]
    }
    
    var prox = new Script.Util.WSProxy();
    
    var moreData = true,
        reqID = Rows = null;
    
    while (moreData) {
    
        moreData = false;
    
        if (reqID == null) {
            Rows = prox.retrieve("DataExtensionObject[" + config.name + "]", config.cols);
        } else {
            Rows = prox.getNextBatch("DataExtensionObject[" + config.name + "]", reqID);
        }

        var GeneratedRows = 0;
        
          if (Rows.Results.length > 0) {
            moreData = Rows.HasMoreRows;
            reqID = Rows.RequestID;
            var convertedResults = [];
            // Loop through each item in the Results array
            for (var i = 0; i < Rows.Results.length; i++) {
                var properties = Rows.Results[i].Properties;

                // Initialize an empty object to store the property map
                var propertyMap = {};

                // Loop through each property and convert it into a key-value pair in the object
                for (var j = 0; j < properties.length; j++) {
                    if (properties[j].Name && properties[j].Value) {
                        propertyMap[properties[j].Name] = properties[j].Value;
                    }
                }

                // Push the property map to the results array
                convertedResults.push(propertyMap);
            }
            var api = new Script.Util.WSProxy(); 

            if (convertedResults.length > 0) {
                for(var i in convertedResults) {
                    var jsonObj = Platform.Function.ParseJSON(convertedResults[i]["FinancialAccount"]);
                    if (jsonObj.length > 0) {
                        for(var j = 0; j < jsonObj.length; j++ ) {
                            var item = jsonObj[j];
                            var result = api.createItem("DataExtensionObject", { 
                                CustomerKey: adjustedDE,
                                Properties: [
                                { Name: "SubscriberKey", Value: convertedResults[i]["SubscriberKey"] },
                                { Name: "Id", Value: convertedResults[i]["Id"] }, // Here the IDs have to be unique; otherwise it will throw an error
                                { Name: "FirstName", Value: item["FirstName"] },
                                { Name: "LastName", Value: item["LastName"] },
                                { Name: "EmailAddress", Value: convertedResults[i]["EmailAddress"] },
                                { Name: "OpenDate", Value: item["OpenDate"] },
                                { Name: "CloseDate", Value: item["CloseDate"] },
                                { Name: "Name", Value: item["Name"] },
                                { Name: "IndividualId", Value: item["IndividualId"] },
                                { Name: "FinancialAccountType", Value: item["FinancialAccountType"] },
                                { Name: "ContactEmailId", Value: item["ContactEmailId"] },
                                { Name: "FinancialAccountStatus", Value: item["FinancialAccountStatus"] } 
                                ] 
                            });
                        }
                    }
                }
            }
          }        
    }
}
catch(error) {
    var logDE = DataExtension.Init('ErrorLogs'); 
    logDE.Rows.Add({"Source":"Data Cloud Demo Customers 360","ErrorDescription":Stringify(error)});
}
</script>

For more convenient code review, here is the link to my GitHub repository.

With these improvements, Data Cloud implementation projects with activations to Marketing Cloud will become smoother and faster, delivering better results and meeting client expectations. Special thanks to my colleague Anna Peshko, who worked with me and helped tackle these challenges.