How to filter and delete multiple Data Extension records with a Script Activity

How to filter and delete multiple Data Extension records with a Script Activity

This article explains how to delete multiple Data Extension records with a Script Activity in a Marketing Cloud Automation.

Problem

When it comes to processing thousands of records in a Marketing Cloud Data Extension, SQL is king!

But what we can do with those records is very limited, because DELETE, UPDATE and INSERT statements are not supported by the SQL Query activities.

Thankfully, server-side JavaScript can help us bridge that gap and allow us to perform these actions to up to 100.000 records at a time, thanks to the Script activities.

Use case

For the purpose of this article, let’s consider a use case, where a merge between 2 Data Extensions went terribly wrong and we urgently need to delete several thousands of records.

Here is the structure of the Data Extension, named “MyDataExtension“:

Field namePrimary KeyTypeLength
SubscriberKeyYesText50
CampaignYesText254
SegmentNoText50
CreatedDateNoDate

What we know is that the corrupted records either have the wrong Campaign name or the wrong Segment and CreatedDate values.

Solution

Since we are building a Script activity, we are going to use the code from my previous article, that takes into account the 30 minute expiration and error handling.

But instead of processing the data and updating it in the original Data Extension, we are going to create a function to delete the records we retrieved.

Let’s have a look at 3 key elements that are different and examine them separately:

#1 Configuration object

As previously stated, we need a complex filter to extract the records that either have the wrong Campaign name or the wrong Segment and CreatedDate values.

This object holds all the information we need for our retrieve request and error logging.

var config = {
    de: "MyDataExtension",
    filter: {
        LeftOperand: {
            LeftOperand: {
                Property: "Segment", 
                SimpleOperator: "equals", 
                Value: "B2B" 
            },
            LogicalOperator: "AND",
            RightOperand: {
                Property: "CreatedDate", 
                SimpleOperator: "greaterThan",
                Value: "2020-06-12"
            }
        },
        LogicalOperator: "OR",
        RightOperand: {
            Property: "Campaign", 
            SimpleOperator: "equals", 
            Value: "June2021"
        }
    },
    error: {
        de: "Automation ErrorLog",
        source: "Script Activity #1"
    }
}

#2 Create batch function for deletion

In order to create a batch object for deleting records using the WSProxy method, there are 2 main requirements:

  • We need to provide the values for all the primary keys.
  • We need to use the name Keys instead of Properties to pass the Name/Value pairs to WSProxy.
function createBatches(key, records, primaries) {

    var batches = [];

    for (var k in records) {

        var keys = {};

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

            var primary = primaries[i];

            keys[primary] = records[k][primary];

        }

        batches.push({
            CustomerKey: key,
            Keys: wsPack(keys)
        });

    }

    return batches;

}

#3 Delete function

If the deletion batches were compiled correctly, this function will use the WSProxy method to delete all the records one by one without using any JavaScript loops.

function deleteRecords(batches) {

	return api.deleteBatch("DataExtensionObject", batches);

}

Full code

Now, let’s have a look at the full code.

<script runat="server">

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

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

    var config = {
        de: "MyDataExtension",
        filter: {
            LeftOperand: {
                LeftOperand: {
                    Property: "Segment", 
                    SimpleOperator: "equals", 
                    Value: "B2B" 
                },
                LogicalOperator: "AND",
                RightOperand: {
                    Property: "CreatedDate", 
                    SimpleOperator: "greaterThan",
                    Value: "2020-06-12"
                }
            },
            LogicalOperator: "OR",
            RightOperand: {
                Property: "Campaign", 
                SimpleOperator: "equals", 
                Value: "June2021"
            }
        },
        error: {
            de: "Automation ErrorLog",
            source: "Script Activity #1"
        }
    }

    try {

        var result = retrieveAttributes(config);

        result.records = retrieveRecords(result.customerKey, result.columns.all, config.filter);

        result.records = formatRecords(result.records, result.columns);

        result.count = result.records.length;

        result.batches = createBatches(result.customerKey, result.records, result.columns.prims);

        result.deleted = deleteRecords(result.batches);

        if(result.deleted.Status == "OK") {
            Write(Stringify(result));
        } else {
            throw result.deleted;
        }

    } catch(err) {

        logError(err, config);

    }

    function logError(err, config) {

        var props = {
            Id: GUID(), 
            Message: Stringify(err.message), 
            Description: Stringify(err.description), 
            Source: config.error.source, 
            CreatedDate: DateTime.SystemDateToLocalDate(Now())
        }

        err.log = api.createItem("DataExtensionObject", {
            Name: config.error.de,
            Properties: wsPack(props)
        });

        if(Platform.Request.RequestURL() != null) {

            Write(Stringify(err));

        } else {

            createAutomationError();

        }

    }

    function deleteRecords(batches) {

        return api.deleteBatch("DataExtensionObject", batches);

    }

    function createBatches(key, records, primaries) {

        var batches = [];

        for (var k in records) {

            var keys = {};

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

                var primary = primaries[i];

                keys[primary] = records[k][primary];

            }

            batches.push({
                CustomerKey: key,
                Keys: wsPack(keys)
            });

        }

        return batches;

    }

    function formatRecords(records, columns) {

        for(var k in records) {

            var record = records[k];

            for(var name in record) {

                if(record[name].length > 0) {

                    if(inArray(columns.dates, name)) record[name] = new Date(record[name]);
                    if(inArray(columns.nums, name)) record[name] = Number(record[name]);
                    if(inArray(columns.decs, name)) record[name] = Number(record[name]);
                    if(inArray(columns.bools, name)) record[name] = (record[name].toLowerCase() == "true") ? true : false;

                }

            }

        }

        return records;

    }

    function retrieveRecords(key, cols, filter) {

        var now = new Date(),
            start = now.getTime(),
            timeOut = (60000 * 15); // 15 minutes

        var records = [],
            moreData = true,
            reqID = data = null;

        while (moreData && (new Date().getTime() - start) < timeOut) {

            moreData = false;

            if (reqID == null) {
                data = api.retrieve("DataExtensionObject[" + key + "]", cols, filter);
            } else {
                data = api.getNextBatch("DataExtensionObject[" + key + "]", reqID);
            }

            if (data != null) {

                moreData = data.HasMoreRows;
                reqID = data.RequestID;

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

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

                    records.push(wsUnpack(props));

                }

            }
        }

        if(records.length === 0) throw "No records could be found!";

        return records;

    }

    function retrieveAttributes(config) {

        var req = DataExtension.Retrieve(
            { 
                Property: "Name", 
                SimpleOperator: "equals", 
                Value: config.de 
            }
        );

        if(req[0].CustomerKey != null) {
            var customerKey = req[0].CustomerKey;
        } else {
            throw "The DataExtension \"" + config.de + "\" could not be found!";
        }

        var de = DataExtension.Init(customerKey);

        var fields = de.Fields.Retrieve();

        fields.sort(function (a, b) { 
            return (a.Ordinal > b.Ordinal) ? 1 : -1 
        });

        var columns = [],
            dates = [],
            bools = [],
            nums = [],
            decs = [],
            prims = [];

        for (var k in fields) {

            var field = fields[k];

            columns.push(field.Name);

            if(field.FieldType == "Date") dates.push(field.Name);
            if(field.FieldType == "Number") nums.push(field.Name);
            if(field.FieldType == "Boolean") bools.push(field.Name);
            if(field.FieldType == "Decimal") decs.push(field.Name);
            if(field.IsPrimaryKey == true) prims.push(field.Name);

        }

        return {
            name: config.de,
            customerKey: customerKey,
            fields: fields,
            columns: {
                all: columns,
                dates: dates,
                bools: bools,
                nums: nums,
                decs: decs,
                prims: prims
            }
        };

    }

    function inArray(arr, k) {

        var out = false;

        for (var i in arr) {

            if (arr[i] == k) out = true;

        }

        return out;

    }

    function wsPack(obj) {

        var out = [];

        for (k in obj) {

            out.push({
                Name: k,
                Value: obj[k]
            });

        }

        return out;

    }

    function wsUnpack(props) {

        var out = {};

        for(var k in props) {

            var item = props[k];

            out[item.Name] = item.Value;

        }

        return out;

    }

</script>

Result

Here is what the output looks like if you run the code from a Cloud page.

{
    "name": "MyDataExtension",
    "customerKey": "xxxxxxxx-xxxx-xxxx-xxxx-4BFBDA53FC1E",
    "fields": [
        {
            "Name": "SubscriberKey",
            "ObjectID": "xxxxxxxx-xxxx-xxxx-xxxx-f5e54415536f",
            "FieldType": "Text",
            "IsPrimaryKey": true,
            "MaxLength": 18,
            "Ordinal": 0,
            "DefaultValue": "",
            "StorageType": "Plain"
        },
        {
            "Name": "Campaign",
            "ObjectID": "xxxxxxxx-xxxx-xxxx-xxxx-1792e9c0c423",
            "FieldType": "Text",
            "IsPrimaryKey": false,
            "MaxLength": 255,
            "Ordinal": 2,
            "DefaultValue": "",
            "StorageType": "Plain"
        },
        {
            "Name": "Segment",
            "ObjectID": "xxxxxxxx-xxxx-xxxx-xxxx-3b52d27bf806",
            "FieldType": "Text",
            "IsPrimaryKey": false,
            "MaxLength": 255,
            "Ordinal": 2,
            "DefaultValue": "",
            "StorageType": "Plain"
        },
        {
            "Name": "CreatedDate",
            "ObjectID": "xxxxxxxx-xxxx-xxxx-xxxx-6d07755a6281",
            "FieldType": "Date",
            "IsPrimaryKey": false,
            "MaxLength": 0,
            "Ordinal": 62,
            "DefaultValue": "GETDATE()",
            "StorageType": "Plain"
        }
    ],
    "columns": {
        "all": [
            "SubscriberKey",
            "Campaign",
            "Segment",
            "CreatedDate"
        ],
        "dates": [
            "CreatedDate"
        ],
        "bools": [],
        "nums": [],
        "decs": [],
        "prims": [
            "SubscriberKey",
            "Campaign"
        ]
    },
    "records": [
        {
            "SubscriberKey": "XXXXXXXXXXXXXXXPQAR",
            "Campaign": "June2021",
            "Segment": "B2B",
            "CreatedDate": "2021-08-12T09:18:02.000"
        },
        {
            "SubscriberKey": "XXXXXXXXXXXXXXXxQAN",
            "Campaign": "June2021",
            "Segment": "B2B",
            "CreatedDate": "2021-08-12T09:18:02.000"
        },
        {
            "SubscriberKey": "XXXXXXXXXXXXXXXYQAV",
            "Campaign": "June2021",
            "Segment": "B2B",
            "CreatedDate": "2021-08-12T09:18:02.000"
        }
    ],
    "count": 3,
    "batches": [
        {
            "CustomerKey": "xxxxxxxx-xxxx-xxxx-xxxx-4BFBDA53FC1E",
            "Keys": [
                {
                    "Name": "SubscriberKey",
                    "Value": "XXXXXXXXXXXXXXXPQAR"
                },
                {
                    "Name": "Campaign",
                    "Value": "June2021"
                }
            ]
        },
        {
            "CustomerKey": "xxxxxxxx-xxxx-xxxx-xxxx-4BFBDA53FC1E",
            "Keys": [
                {
                    "Name": "SubscriberKey",
                    "Value": "XXXXXXXXXXXXXXXxQAN"
                },
                {
                    "Name": "Campaign",
                    "Value": "June2021"
                }
            ]
        },
        {
            "CustomerKey": "xxxxxxxx-xxxx-xxxx-xxxx-4BFBDA53FC1E",
            "Keys": [
                {
                    "Name": "SubscriberKey",
                    "Value": "XXXXXXXXXXXXXXXYQAV"
                },
                {
                    "Name": "Campaign",
                    "Value": "June2021"
                }
            ]
        }
    ],
    "deleted": {
        "Status": "OK",
        "RequestID": "xxxxxxxx-xxxx-xxxx-xxxx-0d21bb23b5ac",
        "Results": [
            {
                "ErrorMessage": null,
                "KeyErrors": null,
                "Object": {
                    "CustomerKey": "xxxxxxxx-xxxx-xxxx-xxxx-4BFBDA53FC1E",
                    "Properties": null,
                    "Name": null,
                    "Keys": [
                        {
                            "Name": "SubscriberKey",
                            "Value": "XXXXXXXXXXXXXXXPQAR"
                        },
                        {
                            "Name": "Campaign",
                            "Value": "June2021"
                        }
                    ],
                    "Type": null,
                    "Client": null,
                    "PartnerKey": null,
                    "PartnerProperties": null,
                    "CreatedDate": "0001-01-01T00:00:00.000",
                    "ModifiedDate": null,
                    "ID": 0,
                    "ObjectID": null,
                    "Owner": null,
                    "CorrelationID": null,
                    "ObjectState": null,
                    "IsPlatformObject": false
                },
                "StatusCode": "OK",
                "StatusMessage": "Deleted DataExtensionObject",
                "OrdinalID": 0,
                "ErrorCode": 0,
                "RequestID": null,
                "ConversationID": null,
                "OverallStatusCode": null,
                "RequestType": "Synchronous",
                "ResultType": null,
                "ResultDetailXML": null
            },
            {
                "ErrorMessage": null,
                "KeyErrors": null,
                "Object": {
                    "CustomerKey": "xxxxxxxx-xxxx-xxxx-xxxx-4BFBDA53FC1E",
                    "Properties": null,
                    "Name": null,
                    "Keys": [
                        {
                            "Name": "SubscriberKey",
                            "Value": "XXXXXXXXXXXXXXXxQAN"
                        },
                        {
                            "Name": "Campaign",
                            "Value": "June2021"
                        }
                    ],
                    "Type": null,
                    "Client": null,
                    "PartnerKey": null,
                    "PartnerProperties": null,
                    "CreatedDate": "0001-01-01T00:00:00.000",
                    "ModifiedDate": null,
                    "ID": 0,
                    "ObjectID": null,
                    "Owner": null,
                    "CorrelationID": null,
                    "ObjectState": null,
                    "IsPlatformObject": false
                },
                "StatusCode": "OK",
                "StatusMessage": "Deleted DataExtensionObject",
                "OrdinalID": 1,
                "ErrorCode": 0,
                "RequestID": null,
                "ConversationID": null,
                "OverallStatusCode": null,
                "RequestType": "Synchronous",
                "ResultType": null,
                "ResultDetailXML": null
            },
            {
                "ErrorMessage": null,
                "KeyErrors": null,
                "Object": {
                    "CustomerKey": "xxxxxxxx-xxxx-xxxx-xxxx-4BFBDA53FC1E",
                    "Properties": null,
                    "Name": null,
                    "Keys": [
                        {
                            "Name": "SubscriberKey",
                            "Value": "XXXXXXXXXXXXXXXYQAV"
                        },
                        {
                            "Name": "Campaign",
                            "Value": "June2021"
                        }
                    ],
                    "Type": null,
                    "Client": null,
                    "PartnerKey": null,
                    "PartnerProperties": null,
                    "CreatedDate": "0001-01-01T00:00:00.000",
                    "ModifiedDate": null,
                    "ID": 0,
                    "ObjectID": null,
                    "Owner": null,
                    "CorrelationID": null,
                    "ObjectState": null,
                    "IsPlatformObject": false
                },
                "StatusCode": "OK",
                "StatusMessage": "Deleted DataExtensionObject",
                "OrdinalID": 2,
                "ErrorCode": 0,
                "RequestID": null,
                "ConversationID": null,
                "OverallStatusCode": null,
                "RequestType": "Synchronous",
                "ResultType": null,
                "ResultDetailXML": null
            }
        ]
    }
}

Conclusion

Although this method seems overly complicated, us developers, we don’t have much of a choice when it comes to bridging the gaps between languages.

Let’s hope that one day Marketing Cloud developers will hear our prayers and we won’t have to write hundreds of lines of code to replace a simple SQL statement that should have been there in the first place.

Have I missed anything?

Please poke me with a sharp comment below or use the contact form.

Leave a Reply

Your email address will not be published. Required fields are marked *

Salesforce Marketing Cloud
Up Next:

How to perform server-side form validations using Bootstrap 5, plain JavaScript and Marketing Cloud API

How to perform server-side form validations using Bootstrap 5, plain JavaScript and Marketing Cloud API