Glen Mazza's Weblog

https://glenmazza.net/blog/date/20220822 Monday August 22, 2022

Deleting subscriber lists from Marketing Cloud

At work we used to create mailing lists on various search categories of interest to our readers, resulting in thousands of lists being created over the years. Since then we've switched to triggered sends, making these lists no longer of use, and hence we were looking for an automated way of deleting them.

Note it's very easy to delete a list, and in any automated process to delete a large number of lists it's important to make sure you're not accidentally deleting a list you need. For my work I checked any List ID to be deleted against a list of IDs that I knew our systems still used, disallowing the deletion were that the case. Such a process saved me from accidentally deleting two or three needed lists.

Some further points to note:

  • Looking at the list of data views available, for some reason Marketing Cloud does not offer a "List" view to see all available lists in our account, similar to the Subscribers view to see all subscribers. Instead a ListSubscribers view is provided, which perhaps can be thought of as a link table between Subscribers and Lists. I can query this view to obtain lists that at least one person is (or was) subscribed to, probably getting me close to 100% of all lists if perhaps missing some that never had subscribers.
  • We had far more lists than were displayed in the Marketing Cloud UI Email Studio (perhaps ten times more). I don't know why some are hidden from the UI. It could be lists created via Marketing Cloud SOAP calls will not be viewable, or those without certain parameters, etc.
  • If you have comparatively few lists to delete, and they are visible in the Email Studio UI, they can simply be deleted there.
  • Removing our unused lists made the Email Studio UI dramatically faster.

My first step was to get a list of lists in the system that I could examine to see which could be removed. To generate the list, I first created a data extension to store the results of a SQL query on ListSubscribers. Once I ran the query from Automation Studio, filling the extension in the process, I then viewed the extension in Email Studio, which also allows us to download the list from the browser. Here are the columns of the Data Extension that I used. What is needed to delete a list is its list ID, the other columns were used to help me determine whether each list is still in use.

Data Extension holding list metadata

I ran this SQL query on our main (production) Business Unit as it provides the most complete information about lists and their subscribers:

select ListID, ListName, ListType, count(*) as NumSubscribers, max(CreatedDate) as MaxCreateDate, max(DateUnsubscribed) as MaxUnsubscribeDate
from _ListSubscribers
group by ListID, ListName, ListType

I primarily needed the ListNames to determine which lists I could dispose of. In some cases, the MaxCreateDate and MaxUnsubscribe dates, which indicates the last time someone subscribed and unsubscribed to each list, was helpful in making that determination.

While I eventually needed a Java program to automate the deletion of our large number of unneeded lists, to get an idea of what would be needed, I first practiced with Postman to query and delete individual lists. SFMC offers an unofficial Postman workspace for making SOAP and REST MC calls, with documentation for using same on GitHub.

While the SFMC Postman collection doesn't provide out-of-the-box SOAP calls for Lists, it does have one for data extensions that I leveraged in creating commands for lists. The below request returns lists with three filter options: by list ID, list name (exact match), and list name (substring match), with no filter providing all lists up to a SFMC-determined maximum. The list ID that is to be subsequently used for deleting the list is the "id" value (see here for available values to query.) For a list query, it will be returned under the PartnerProperties element.

List query SOAP call:

<?xml version="1.0" encoding="UTF-8"?>
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    <s:Header>
        <a:Action s:mustUnderstand="1">Retrieve</a:Action>
        <a:To s:mustUnderstand="1">https://{{et_subdomain}}.soap.marketingcloudapis.com/Service.asmx</a:To>
        <fueloauth xmlns="http://exacttarget.com">{{dne_etAccessToken}}</fueloauth>
    </s:Header>
    <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
            <RetrieveRequest>
                <ObjectType>List</ObjectType>
                <Properties>ObjectID</Properties>
                <Properties>id</Properties>
                <Properties>CustomerKey</Properties>
                <Properties>ListName</Properties>
                <Properties>Category</Properties>
                <!--Filter xsi:type="SimpleFilterPart">
                    <Property>id</Property>
                    <SimpleOperator>IN</SimpleOperator>
                    <Value>123456</Value>
                    <Value>234567</Value>
                </Filter-->
                <!--Filter xsi:type="SimpleFilterPart">
                    <Property>ListName</Property>
                    <SimpleOperator>like</SimpleOperator>
                    <Value>%News%</Value>
                </Filter-->
                <!--Filter xsi:type="SimpleFilterPart">
                    <Property>ListName</Property>
                    <SimpleOperator>equals</SimpleOperator>
                    <Value>My Sample Newsletter</Value>
                </Filter-->
            </RetrieveRequest>
        </RetrieveRequestMsg>
    </s:Body>
</s:Envelope>

Response fragment:

<Results xsi:type="List">
    <PartnerKey xsi:nil="true" />
    <PartnerProperties>
        <Name>id</Name>
        <Value>123456</Value>
    </PartnerProperties>
    <ObjectID>....</ObjectID>
    <CustomerKey>....</CustomerKey>
    <ListName>My Newsletter</ListName>
    <Category>...folder ID...</Category>
</Results>

A SOAP call for deleting list(s) by their List IDs are as follows:

<?xml version="1.0" encoding="UTF-8"?>
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    <s:Header>
        <a:Action s:mustUnderstand="1">Delete</a:Action>
        <a:To s:mustUnderstand="1">https://{{et_subdomain}}.soap.marketingcloudapis.com/Service.asmx</a:To>
        <fueloauth xmlns="http://exacttarget.com">{{dne_etAccessToken}}</fueloauth>
    </s:Header>
    <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <DeleteRequest xmlns="http://exacttarget.com/wsdl/partnerAPI">
         <Options></Options>
         <Objects xsi:type="List">
            <ID xsi:nil="true">123456</ID>
         </Objects>
         <!-- Add as many additional List IDs as desired to delete all in one call-->
         <!--Objects xsi:type="List">
            <ID xsi:nil="true">234567</ID>
         </Objects-->
      </DeleteRequest>
    </s:Body>
</s:Envelope>

Switching over to Java, we created a utility that would read a list of ET List IDs that we no longer needed, and proceeded to delete them after confirming they weren't on our "needed" list. As shown in the above SOAP call, multiple lists can be deleted in just one call -- for the thousands we needed to delete, deleting 50 at a time seemed to work fastest, and our code managed about 3000 lists per minute.

Using the FuelSDK, pseudocode to delete lists would be as follows:

public DeleteResponse deleteListsInMC(java.util.List<Integer> etListIds) throws ETSdkException {
    // etListIds checked earlier to confirm they are not needed (e.g., query against a table of in-use lists)

    DeleteRequest deleteRequest = new DeleteRequest();

    DeleteOptions options = new DeleteOptions();
    options.setRequestType(RequestType.SYNCHRONOUS);
    options.setQueuePriority(priority);

    deleteRequest.setOptions(options);

    for (Integer i : etListIds) {
        List list = new List();
        list.setId(i);
        deleteRequest.getObjects().add(list);
    }

    // configure ETClient similar to here: https://salesforce.stackexchange.com/a/312178
    // ETClient etClient = ....
    return etClient.getSoapConnection().getSoap().delete(deleteRequest);
}

The returned DeleteResponse object returns a list of DeleteResult objects, one object per list deleted. The result objects have an ordinal ID which map 1-to-1 to the order of lists given in the delete request. Sample result processing code:

DeleteResponse dr = deleteListsInMC(listToDelete);
List<DeleteResult> delResult = dr.getResults();

for (DeleteResult drTemp : delResult) {
    Integer drTempListID = listToDelete.get(drTemp.getOrdinalID());

    // error codes: https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/13000_13099_list_object.html
    // Postman showing 13000 ErrorCode with ListNotFound status message in case of unknown lists
    if ("OK".equals(drTemp.getStatusCode())) {
        // OrdinalID is 0-based
        LOGGER.info("Deleted list {} from MC", drTempListID);
    } else if ("Error".equalsIgnoreCase(drTemp.getStatusCode())) {
        if (drTemp.getErrorCode() == 13000) {
            LOGGER.info("List {} not in MC, nothing to delete", drTempListID);
        } else {
            LOGGER.warn("Could not delete list {}: error code {} status message {}",
                    drTempListID, drTemp.getErrorCode(), drTemp.getStatusMessage());
        }
    }
}

Posted by Glen Mazza in Salesforce at 07:00AM Aug 22, 2022 | Tags:  marketingcloud | Comments[0]

Post a Comment:

Calendar
« February 2023
Sun Mon Tue Wed Thu Fri Sat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Today
About Me
Java Software Engineer
TightBlog project maintainer
Arlington, Virginia USA
glen.mazza at pm dot me
GitHub profile for Glen Mazza at Stack Overflow, Q&A for professional and enthusiast programmers
Blog Search


Blog article index
Navigation
About Blog
Blog software: TightBlog 3.7.2
Application Server: Tomcat
Database: MySQL
Hosted on: Linode
SSL Certificate: Let's Encrypt
Installation Instructions