Subscription Management using SSJS and Script Activity

If you have worked with Subscribers and Email Studio in Marketing Cloud, then you should be familiar with the various Subscriber statuses like Active and Unsubscribed. And of course, the various types of Lists that a Subscriber can be a member of:

• All Subscribers List – the master list that controls if a Subscriber can be sent an email or not
• Standard List – a subscriber list that may be used for Email Sends
• Publication List – a special list that is used to map Subscribers in Data Extension Sends to the All Subscribers List

One of the common use cases that you may come across is to be able to re-activate subscribers in bulk, that had unsubscribed previously – and possibly still in the All Subscribers List. Now you can do this using a simple CSV Import by changing the status to Active – but then you have to import the file for each List individually where you need to change the status. So the obvious choices is to use programming options like APIs / AMPScript / SSJS.

In this article, we will look at how to use a Data Extension and Script Activity (of course using SSJS) to manage subscription status. The use case covers the following requirements:

• Specify a set of Subscribers whose subscription status needs to be changed
• Have a simple mechanism to be able to specify the status changes
• Need to be able to change status across All Subscribers, Standard Lists and Publication Lists

Solution Approach

Data storage

The first step is to identify how to store the data that needs to be processed and managed easily. We shall create a Data Extension for this – let’s call it SubscriptionMgmtDE. This DE shall have the below attributes:

The SubscriberKey and EmailAddress fields are self-explanatory. The next 3 attributes shall denote what status changes are to be made for the subscriber if he / she belongs to the All Subscribers List (ALLS), any subscriber List (LISTS) and any Publication Lists (PUBL). We shall be using the below values for these fields:

• AC = > Active
• UN => Unsubscribed
• IG => Ignore

If a value is not entered for any of these fields, then the value will default to “IG” (Ignore). These values give us the flexibility to specify if we should ignore the current status of the subscriber in Lists and / or Publication Lists when re-activating them in the All Subscribers List. If there arises a need where you would want to re-activate the user across all the lists that he / she is a member of, then we should specify “AC” (Active) for all the 3 fields. As you may know, re-activating a Subscriber in All Subscribers List does not automatically change the status of the Subscriber in the other lists by default – hence this is quite useful if you wish to re-enable the Subscriber in all the other lists he/she belongs to, without manually having to go and check each one.

Data preparation and Subscription Rules

The next step is to prepare the subscriber data that needs to be processed and then import it into the DE that we created above. One way is to export the Subscribers from the All Subscribers list and manually edit the CSV file to the above format. A better and easier way would be to use Query Studio – for those of you who do not know about Query Studio, this is a free AppExchange package that you can install for your Marketing Cloud instance. The advantage of having Query Studio is that you can run queries against your Data Extensions and Data Views – and the results will be available temporarily in Data Extensions for 24 hours that you can then export into a CSV file.

For our solution, we will use Query Studio and extract the Subscribers from the _ListSubscribers Data View as shown below:

select
Subscriberkey,
EmailAddress,
'AC' as ALLS,
'IG' as LISTS,
'IG' as PUBL 
from _ListSubscribers
where ListName='All Subscribers'
and Status='unsubscribed'

In the above query, we have kept the “AC” – Activate option only for the All Subscriber List. If you need the status to be changed for the other lists as well, then you can specify that in the query by changing the “IG” to “AC”. If “IG” is specified, then we will be ignoring changes to those List types for the subscriber. Once the results are generated, you can export them by clicking on the “Export in Contact Builder” link in Query Studio – this takes you to the temporary DE that holds the Query Studio results – from where you can export the data to a CSV file. You may make additional changes if needed to this CSV file – and then import this into the SubscriptionMgmtDE. Below is how the DE will look like once we have imported Subscriber data based on the options we specify:

Do note that this solution gives you the option to do Unsubscribes as well – and not just Activation. So instead of specifying “AC”, you can specify “UN” if you wish to unsubscribe Subscribers. A word of caution though – if you use “UN” in the ALLS field (All Subscriber List), then the status of the Subscriber is automatically updated to “Unsubscribed” for the other lists – irrespective of whether you make it as “AC” or “IG” – this is the way Marketing Cloud works. However you can change the status of the regular Lists and Publication Lists to “Unsubscribed” or “Active” if the status in the All Subscribers List is “Active”

The Code that does all the Magic!

Now that we have the data ready, let’s look at the SSJS code which will be used to process the status updates for each of the Subscribers in the DE. We will then store this code in a Script Activity and run it in Automation Studio as needed.

We will be using the Core Library functions for this solution – primarily List, Subscriber and Data Extension functions. These functions may not work in the context of Emails – they can be used in Landing pages and Script Activities. I would highly recommend you check out my previous article on “Working with SSJS in Script Activities” to get an idea about Script Activities and basics of SSJS.

Reading each Subscriber from the Data Extension

We will use the DataExtension.Init function to initiate the SubscriptionMgmtDE using its External Key – and then retrieve all the Subscriber records using the Rows.Retrieve function to the subRows variable. The Rows.Retrieve function has an optional filter parameter in case you want to retrieve only some records based on a filter. In our case, we will be pulling all records for processing. We will then iterate through each of the rows in subRows using a for loop and process the list updates within it.

Please do note that the Rows.Retrieve function returns a max of 2500 records at a time. So if you are expecting to update more than 2500 records in your DE, then you’ll need to call this function multiple times till all the records have been retrieved and processed. One way how you can make this work is to call the Rows.Retrieve function within a Do..While loop – and then within the loop, ensure the records that have been processed are deleted (or moved to another Archive DE if you need to store them for audit purposes). The loop should continue to retrieve the next batch of max 2500 records as long as the retrieved length is greater than 0. For our use case, we will assume there are less than 2500 records in our DE for processing.

var subDE = DataExtension.Init("SubscriptionMgmtDE External Key");   //Initiating the SubscriptionMgmtDE
var subRows = subDE.Rows.Retrieve();                                      //Retrieving all the Subscriber records for Subscription processing
  

for (var j=0; j<subRows.length; j++)                      //Iterate through each Subscriber record
 {
      //Process all the list updates
 }

Data variable initializations

We will define 5 variables to store the attributes for each of the Subscriber records in each iteration as shown below. We will also create a json object with the SubscriberKey and EmailAddress attributes to be used as a parameter in a function later.

var subKey = subRows[j]["SubscriberKey"];
var subEmail = subRows[j]["EmailAddress"];
var chgAllSub = subRows[j]["ALLS"];                   //values of "AC"=Active, "UN"=Unsubscribed, "IG"=Ignore
var chgAllList = subRows[j]["LISTS"];                 //values of "AC"=Active, "UN"=Unsubscribed, "IG"=Ignore
var chgAllPubList = subRows[j]["PUBL"];               //values of "AC"=Active, "UN"=Unsubscribed, "IG"=Ignore
var subJson = {"SubscriberKey":subKey, "EmailAddress":subEmail};  //Creating Json record for Subscriber for use in Update Function

Retrieving all Lists for a Subscriber

We need to find all the lists that a Subscriber is a member of – and then process for each of the List types based on the values in chgAllSub, chgAllList and chgAllPubList variables. For this, we will use the Subscriber.Init function which requires the SubscriberKey as the parameter. Once the Subscriber is initiated, then we can use the Subscriber.Lists.Retrieve function to get all the Lists that the Subscriber is a member of and store it in the subLists variable. We will then iterate for each List in subLists, check the type and then process accordingly.

var subObj = Subscriber.Init(subKey);                             //Initiating each Subscriber from the DE
var subLists = subObj.Lists.Retrieve();                           //Retrieve all Lists the Subscriber is a member of

var chkLists;
var subStatus;
    
for (var i=0; i<subLists.length; i++)
  {
       //process update for each List type as per chgAllSub, chgAllList and chgAllPubList values
  }

Processing each List type

If you go through the SSJS documentation, you will find that the Subscriber related functions allow you to make updates to a specific Subscriber record and retrieve all Lists associated with the Subscriber – but there’s no Subscriber.Lists.Update function yet. Hence we have to use the List.Subscribers.Update function to update the status of the Subscriber in each List.

In the subLists object array, we will be able to view the List.ID and List.Name of each of the Lists retrieved for the Subscriber. Our next objective is to identify the type of List in the current iteration of subLists. First, we shall store the List.ID in the listID variable – we will be passing this as a parameter to the update function later.

For the All Subscriber list, it’s easy to identify by using the name as the List.Name will be equal to “All Subscribers“. In this case, if the chgAllSub variable is not set to “IG” (Ignore), then we will call the updLists function to update the current Subscriber in this All Subscribers list. We will pass the List Id using the listID variable, the Subscriber details using the subJson variable created earlier and the chgAllSub variable (either “AC” or “UN”). We cannot use the List.Name for the other List Types as they will refer to custom names provided by us when creating them – and there is no List Type or Classification value returned by the Subscriber.List.Retrieve function.

In the else condition, we will check if it’s either a Publication List or a regular subscriber List. For this we will use the List.Retrieve function and a filter criteria. We can check if the “ID“=listID and if the “ListClassification” is a PublicationList by specifying this in the filter parameter for the Retrieve function. Now you may be wondering why did we give the second condition in the filter – why couldn’t we just check the ListClassification value once the List is retrieved using the ID=listID condition. This is because the List.Retrieve function response is not as you would expect it to be – it will search and return the Publication List if it exists when you specify the ListClassification=PublicationList – but the value that is displayed in the response received back will always be shown as ExactTargetList – irrespective of whether it’s a Publication List or a regular Subscriber List. Hence it’s difficult to rely on the List ID alone to determine the List type. In our case, when we use the complex filter, if the List ID passed is indeed a Publication List, then the chkLists variable will have length > 0 – and then processing proceeds into the if section. However if the chkLists variable length = 0, then it means the List is not a Publication List and will proceed into the else section where it will be processed as a regular List. We will again check for the chgAllPubList and chgAllList variable values respectively and then call the updLists function. The subStatus variable gets the return value from the updLists function and can be used to log the status of the processing to a logging DE for debugging or audit purposes.

var listID = subLists[i]["List"]["ID"];                    //Get the ListID for each List

if (subLists[i]["List"]["Name"] == "All Subscribers")      //If the List Name is All Subscribers then process here
{
    if (chgAllSub != "IG")
    {
          subStatus = updLists(listID,chgAllSub,subJson); //Call function to update Subscriber's status in All Subscribers List
    }
}
else
{
    var compFilter = {LeftOperand:{Property:"ID",SimpleOperator:"equals",Value:listID},LogicalOperator:"AND",RightOperand:{Property:"ListClassification",SimpleOperator:"equals",Value:"PublicationList"}};
    chkLists = List.Retrieve(compFilter);
    
    if (chkLists.length > 0)                        //If the List is a Publication List then process here
    {
         if (chgAllPubList != "IG")
         {
              subStatus = updLists(listID,chgAllPubList,subJson); //Call function to update Subscriber's status in the Publication List
         }
    }
    else if (chgAllList != "IG")                         //If the List is a regular List then process here
    {
          subStatus = updLists(listID,chgAllList,subJson);  //Call function to update Subscriber's status in the List
    }
}

The Update Function

In the updLists function, we will use the List.Subscribers.Update function to change the status of the Subscriber (identified by the sJson parameter) in the specific list (identified by the chkListID parameter) to either Active or Unsubscribed – as passed in the chgVal parameter. Unfortunately, the List.Init function does not use the List ID but the CustomerKey value. We will use the List.Retrieve function to get the CustomerKey of the list that is passed into the function – and then use this initiated list object to execute the List.Subscribers.Update function. The latter requires either an email address or a Json object with the Subscriber Key and Email Address of the Subscriber – which we pass through the sJson parameter. The function will return the response from the List.Subscribers.Update function through the sStatus variable – if the update is successful, then the value returned usually will be OK.

function updLists(chkListID,chgVal,sJson)
{
  var cLists;
  var uList;
  var sStatus;

  cLists = List.Retrieve({Property:"ID",SimpleOperator:"equals",Value:chkListID});    //Retrieve passed List's properties including CustomerKey
  if (cLists.length > 0)
  {
       uList = List.Init(cLists[0].CustomerKey);                           //Initiate the List using CustomerKey in order to update the subscriber status
       witch (chgVal)
       {
            case "AC":
                 sStatus = uList.Subscribers.Update(sJson,"Active");            //If "AC" then update Status to "Active"
                 break;
            case "UN":
                 sStatus = uList.Subscribers.Update(sJson,"Unsubscribed");      //If "UN" then update Status to "Unsubscribed"
                 break;
       }
       return sStatus;                                                     //Will return OK if no error - can use to store in Logging DE if needed
   }
}

Putting it all together

Below is the full code if you wish to use in a Script Activity – please ensure you replace the external key of your SubscriptionMgmtDE in the code prior to saving and executing the script.

<script  runat="server">
  Platform.Load("core","1");
  
  function updLists(chkListID,chgVal,sJson)
  {
  var cLists;
  var uList;
  var sStatus;

        cLists = List.Retrieve({Property:"ID",SimpleOperator:"equals",Value:chkListID});    //Retrieve passed List's properties including CustomerKey
        if (cLists.length > 0)
        {
            uList = List.Init(cLists[0].CustomerKey);                           //Initiate the List using CustomerKey in order to update the subscriber status
            switch (chgVal)
            {
               case "AC":
                 sStatus = uList.Subscribers.Update(sJson,"Active");            //If "AC" then update Status to "Active"
                 break;
               case "UN":
                 sStatus = uList.Subscribers.Update(sJson,"Unsubscribed");      //If "UN" then update Status to "Unsubscribed"
                 break;
            }
            return sStatus;                                                     //Will return OK if no error - can use to store in Logging DE if needed
        }
  }
  

  var subDE = DataExtension.Init("SubscriptionMgmtDE ExternalKey");   //Initiating the SubscriptionMgmtDE
  var subRows = subDE.Rows.Retrieve();                                      //Retrieving all the Subscriber records for Subscription processing
  

  for (var j=0; j<subRows.length; j++)                      //Iterate through each Subscriber record
  {

      var subKey = subRows[j]["SubscriberKey"];
      var subEmail = subRows[j]["EmailAddress"];
      var chgAllSub = subRows[j]["ALLS"];                   //values of "AC"=Active, "UN"=Unsubscribed, "IG"=Ignore
      var chgAllList = subRows[j]["LISTS"];                 //values of "AC"=Active, "UN"=Unsubscribed, "IG"=Ignore
      var chgAllPubList = subRows[j]["PUBL"];               //values of "AC"=Active, "UN"=Unsubscribed, "IG"=Ignore
    
      var subJson = {"SubscriberKey":subKey, "EmailAddress":subEmail};  //Creating Json record for Subscriber for use in Update Function

      var subObj = Subscriber.Init(subKey);                             //Initiating each Subscriber from the DE
      var subLists = subObj.Lists.Retrieve();                           //Retrieve all Lists the Subscriber is a member of

      var chkLists;
      var subStatus;
    
      for (var i=0; i<subLists.length; i++)
      {
         var listID = subLists[i]["List"]["ID"];                    //Get the ListID for each List
         if (subLists[i]["List"]["Name"] == "All Subscribers")      //If the List Name is All Subscribers then process here
            {
               if (chgAllSub != "IG")
                {
                    subStatus = updLists(listID,chgAllSub,subJson); //Call function to update Subscriber's status in All Subscribers List
                }
           }
         else
            {
                var compFilter = {LeftOperand:{Property:"ID",SimpleOperator:"equals",Value:listID},LogicalOperator:"AND",RightOperand:{Property:"ListClassification",SimpleOperator:"equals",Value:"PublicationList"}};
                chkLists = List.Retrieve(compFilter);
                if (chkLists.length > 0)                        //If the List is a Publication List then process here
                {
                   if (chgAllPubList != "IG")
                    {
                        subStatus = updLists(listID,chgAllPubList,subJson); //Call function to update Subscriber's status in the Publication List
                    }
                }
                else if (chgAllList != "IG")                         //If the List is a regular List then process here
                {
                   subStatus = updLists(listID,chgAllList,subJson);  //Call function to update Subscriber's status in the List
                }
            }
      }
  
  }

</script>

Summary

This is just one way of how you could manage subscription status in your Marketing Cloud org for multiple Subscribers. In future, we may explore how to extend this functionality to a Landing page, where we can search and update a Subscriber’s status across all their Lists.

Do note that the above code does not contain exception handling and logging – please do use the try-catch statements for exception handling and also logging the process / errors into a separate Logging DE – you can refer to my previous article on how to do this from within a Script Activity using SSJS.

Note (Jul-27-2020): I’ve made a few updates based on the feedback from Greg Gifford – thank you Greg for pointing out that we can in fact update status of Subscribers to Active by importing in a CSV and that there’s also a 2500 record limit to the Rows.Retrieve function and suggesting a solution to resolve this.