My intention for this post was to provide another avenue to access important information on some quirks of the Automation Studio API objects and their relations to each other. There are some fundamental knowledge nuggets buried deep in the official documentation, plus, some documentation is simply flat wrong.

I like to primarily work with SSJS / WS Proxy to interact with the SFMC Soap endpoints. I can recommend this great resource from Ivan:

Firstly, I want to touch on the “undocumented” objects. The objects and listed in the documentation cannot be referenced by these names. They must be…


Analysing your users’ role and permission settings can be troublesome to capture all these settings from within the Admin Setup interface.

There is a way to export all roles and permissions from marketing cloud and you can retrieve this information by using the SOAP API on the AccountUser object.

Below I outline a solution you can use to capture this information in Marketing Cloud and save to Data Extensions for easy use. We will be using WSProxy and SSJS in a script activity. Before we begin, the setup needs to be built in the Enterprise / Parent / Top level business unit.

Data Extension Setup

USERS_INFORMATION Data Extension


By getting the fields available for retrieval, you can identify what kind of information is returned on a Retrieve call.

Use the Describe method to get information about the metadata associated with an object.

The Describe method supports a request for object metadata in the form of an ObjectDefinitionRequest and returns a single ObjectDefinition object.

Particularly useful when combined with WSProxy retrieve request whereby you must specify the fields to return.

<table border="1" cellspacing="0" cellpadding="1" bordercolor="#000000" style="color:#000000;font-family:Arial;">
<tr>
<th>Name</th>
<th>isRetrievable</th>
</tr>
<script runat="server">
Platform.Load("Core","1.1.1");
try{
var prox…


Scenario

We have a Journey Builder Update Contact Activity writing to a DE field called with the current system timestamp as each contact passes through this canvas activity. In a subsequent email the journey, we wish to add +14 days to this DE datetimestamp and then convert it to the local time.

PROBLEM:

It can be tricky converting a datetime field from a Data Extension to the local date using the function. Error returned “string was not recognized as a valid DateTime.

%%=SystemDateToLocalDate(AttributeValue('JourneyEntryDate'))=%%
AMPscript error message

It won’t work because the DE data field we are trying to convert is not a “system” data…


By default MS SQL Server has configured Sunday as the first day of a week. Unfortunately you’re unable to set the server config for the first day of a week in Salesforce Marketing Cloud, so you’ll need a SQL approach.

In the expression below, we add the specified number of weeks to the 0 date. What does that mean? Well, the date 0 represents midnight on Monday, 1 January 1900.

DATEADD(wk, FLOOR(DATEDIFF(day,0,current_timestamp)/7.0), 0)
  • will give the number of days since 1900-01-01 (which importantly was a Monday)
  • Converts days since 1900–01–01 into weeks and rounds…


Photo by Caspar Camille Rubin on Unsplash

What we have below is an edited response from a WSProxy api retrieve call to the SubscriberStatusEvent SOAP object. Within the “Results” array, we can see that the “Properties” array is an array of objects, with each object having two attributes, Name and Value.

....
var res = prox.retrieve("SubscriberStatusEvent", cols, filter);
Write("Output:<br><br>" + Stringify(res));****************
Output:
{
"Status": "OK",
"RequestID": "3558ebd5-4cb2-4cfb-83e6-5cc9b0f29774",
"Results": [
{
"ObjectID": "39a0d2c7-752e-e911-80d8-1402ec74dd75",
"CreatedDate": "2019-02-11T21:25:04.640", …


Photo by Philipp Katzenberger on Unsplash

This post comes off the back of a webinar hosted by Eliot Harper and Ivan Razine in which the very important topic of secure development practices was covered. One of the key learnings was how to protect your API credentials (clientID and clientSecret) on CloudPages. I’d encourage you to watch the webinar recording for some excellent nuggets of information. This blog post is my takeaway from the presentation, with the aim of helping to spread good development practices.

All AMPscript and Server Side JavaScript (SSJS) is processed on the Marketing Cloud’s server side instead of the client-side computer. Thus any…


Many times during troubleshooting and working remotely have I needed to share the setup of data extension within Salesforce Marketing Cloud with colleagues or clients. And you know what they say; a picture is worth a thousand words. Having said that, its actually quite difficult to get a screenshot of your Data Extension setup and have all the fields included in the picture. So what are you to do?

If you’ve ever been in a similar situation I have the perfect solution….

Step 0: Open SFMC inside Google Chrome browser.

Step 1: Load your Data Extension on the properties on…


Photo by noor Younis on Unsplash

Date fields in Marketing Cloud can be challenging. They are stored in a specific format that includes a time component, and you may have to deal with different time zones.

  • ExactTarget/Marketing Cloud system time is hard coded to UTC-6 (UTC minus six hours) and the system time does not change with standard versus daylight savings time.

(Unless your account is on Stack 4, which is in US Mountain time — UTC minus 7 hours).

  • In SQL, it’s equivalent to UTC-06. This is NOT to be confused with which often does


The and Data Views in Salesforce Marketing Cloud have a wealth of information that can be used to uncover when a subscriber was added to the system, when they unsubscribe and their current status (active, unsubscribed, bounced or held).

Amongst the most valuable information nested in these views is the subscriber status which signifies whether a subscriber can be contacted or not.

Charlie Fay

I am a certified Salesforce Marketing Cloud Consultant at Bower House Digital in Melbourne, Australia.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store