Extract Salesforce Marketing Cloud User Data with a CloudPage Script

  As a Salesforce Marketing Cloud user, you might occasionally need to export a list of all users along with their roles and other information. Unfortunately, Marketing Cloud doesn't offer a built-in way to do this. That's where this blog post comes in handy. It will provide you with a straightforward CloudPage script that allows you to extract user data and store it in a data extension.

Why a CloudPage Script?

While there are alternative methods to gather user information, such as querying the AccountUser object via the API or using data views, these methods can be complex and time-consuming. A CloudPage script offers a simple and efficient solution, enabling you to extract the exact data you need without any hassle.

The Script

Here's the CloudPage script you can use:

JavaScript

<script runat="server">
Platform.Load("Core","1");
try {
  var prox = new Script.Util.WSProxy();
  var cols = ["Name","CustomerKey","NotificationEmailAddress", "UserID", "ActiveFlag", "Email", "IsAPIUser", "AccountUserID", "LastSuccessfulLogin", "CreatedDate", "Roles"];
  var filter = {
    LeftOperand: {Property: "Email",SimpleOperator: "like",Value: "@"},
    LogicalOperator: "AND",
    RightOperand: {Property: "ActiveFlag",SimpleOperator: "equals",Value: "true"}
  };
  var res = prox.retrieve("AccountUser", cols, filter);

  // Define the Data Extension name
  var deName = "UserRolesDE";
  var de = DataExtension.Init(deName);

  // Clear the Data Extension before inserting new data
  de.Rows.Remove(["Email"], ["@"]);

  // Insert data into the Data Extension
  for (i = 0; i < res.Results.length; i++) {
    var roles = [];
    for (r = 0; r < res.Results[i].Roles.length; r++) {
      roles.push(res.Results[i].Roles[r].Name);
    }
    de.Rows.Add({
      "Name": res.Results[i].Name,
      "Email": res.Results[i].Email,
      "CreatedDate": res.Results[i].CreatedDate,
      "LastSuccessfulLogin": res.Results[i].LastSuccessfulLogin,
      "Roles": roles.join(", ")
    });
  }

  Write("Data successfully stored in the Data Extension.");

} catch(error) {
  Write('Message: ' + error);
}
</script>

Data Extension Setup

Before running the script, create a data extension named UserRolesDE with the following columns:

Column Name	Data Type
Name	Text
CustomerKey	Text
NotificationEmailAddress	EmailAddress
UserID	Number
ActiveFlag	Boolean
Email	EmailAddress
IsAPIUser	Boolean
AccountUserID	Number
LastSuccessfulLogin	Date
CreatedDate	Date
Roles	Text

How to Use

1. Create a new CloudPage in your Salesforce Marketing Cloud account.
2. Paste the script into the CloudPage's HTML content block.
3. Publish the CloudPage.
4. Access the published CloudPage URL in your browser.

The script will retrieve all active users with their roles and other details and store them in the UserRolesDE data extension.

Conclusion

This simple CloudPage script provides an efficient way to extract Salesforce Marketing Cloud user data. By following the steps outlined in this blog post, you can easily gather the information you need without relying on complex API calls or data views. This method empowers you to manage and analyze user data more effectively, ultimately improving your Marketing Cloud experience.

Additional Resource:

For a more detailed explanation of the process, you can refer to this video by Cameron Robert: https://www.youtube.com/watch?v=jfiSrcFrJSY

This video provides a step-by-step walkthrough of using SSJS and WSProxy to retrieve a list of all Marketing Cloud users. It's a great resource for those who want a deeper understanding of the underlying concepts.

By combining this CloudPage script with the video tutorial, you have all the tools you need to efficiently extract and manage user data in Salesforce Marketing Cloud.