Find a Data Extension and it’s folder path using SSJS

If you work with a lot of Data Extensions in multiple folders, you know the pain of using the built-in search feature. It only searches the folder that you’re in, with the exclusion of any sub-folders, which is simply impractical.

Below, I would like to show you how to find a Data Extension and it’s path using Server-Side JavaScript: which functions to use and how to iterate through them.

If you’re not interested in the technical part and want to jump straight into building the search app, click here.

SSJS Data Extension and Folder Functions

In order to find a Data Extension, we will use two SSJS Core functions: DataExtension.Retrieve and Folder.Retrieve. The first function will help us identify the Data Extension, either by its name or the External Key. The second function retrieves an array of folders based on the specified criteria. It also retrieves details regarding the parent folder of a given folder if one exists, and that’s what we will use to build the Data Extension path.

In the above script, you will need to define two variables, DEprop and DEval, depending on whether you have the name or the External Key of the Data Extension. You can paste the script on a CloudPage, define the two variables, and once you click on the “Publish” button, the script will be executed and results will be visible on-screen, without the need to actually publish the CloudPage:

Create a simple Data Extension search app

If users of your instance of Salesforce Marketing Cloud often struggle with finding Data Extensions, you can create an app on a CloudPage to help them navigate the folders. By adding a simple form, you can enable the users to perform a search from a CloudPage:

Paste the below code onto a CloudPage and publish it. Anyone with access to the link will be able to search for Data Extensions located in the Business Unit where the CloudPage was created. If the CloudPage has been created in the Parent Business Unit, they will also be able to search the Shared Data Extensions folder.

Last but not least, always remember to put security measures into practice when setting up this kind of functionality on a CloudPage, to prevent your data from being exposed in a breach.

PS. There is a free tool available called SFMC Object Finder. It’s a Chrome extension that lets you search for a variety of objects, like images, data extensions and other assets, in your instance of Salesforce Marketing Cloud. All it requires is that you have an active session of Salesforce Marketing Cloud open in your Chrome browser. However, if you cannot install this extension or need to be able to find Data Extensions programmatically, you can use Server-Side JavaScript to achieve it!

Troubleshooting Journey Builder Integration with Debug Logs

If you have a Salesforce Data Entry Event set up in Journey Builder that is not injecting records as intended, it is necessary to look into Debug Logs to determine what the cause may be. Here is a step-by-step guide to setting up and initiating Debug Logs for Journey Builder. The whole process takes part in Sales/Service Cloud, so ensure you have Admin permissions to be able to access Setup.

Turn on Marketing Cloud Connect request logs

  1. In Sales/Service Cloud, click on the Marketing Cloud tab in the Salesforce Classic interface
  2. Click Configure Marketing Cloud Connector on the right
  3. In the Notifications section, click Initiate Log

Turn on Debug Logs

  1. Go to Setup and in the Quick Find, search for Debug Logs
  2. In the User Trace Flag section, click New
  3. Set Traced Entity Type = User
  1. Set Traced Entity Name to the user that will be creating/updating the record which fails being injected into the journey
  2. Start and Expiration Dates should be set to start immediately and expire in the next hour, as the Marketing Cloud Connect activities can be logged only for 60 minutes at a time
  3. Click New Debug Level
  4. Give it a name and make sure there are no spaces
  1. In the level settings, set Workflow = Finer and Callouts = Finest, leave the rest of the settings as they are and Save
  1. Click Save and make sure your new Debug Level shows up in the Debug Level field, then click Save.

Create/Update your record

Now make sure that you are logged in as the User who was earlier selected in the Traced Entity Name and create or update your record so that it meets the entry criteria of the Journey. Document the date and time when the record was created or updated, and the ID of the record.

Parse the Debug Logs

Go to Setup and in the Quick Find, search for Debug Logs. The Debug Logs will be below the User Trace Flags section. There might be several logs, depending on the actions of the user, so having the timestamp that the record was created or updated will help narrow it down.

Use the View link to open the log(s) in a new tab. Search for JBSystemFlow_(object name) (i.e. JBSystemFlow_Contact). If you get a result, check the subsequent line with new_object in it to see if the Id lines up with the record created earlier, that should have been injected into the Journey. If so, you have found the correct log.

Next, search for a line that starts with FLOW_START_INTERVIEW_BEGIN and has JBSystemFlow_(Object). The lines between this and FLOW_START_INTERVIEW_END…JBSystemFlow_(Object) will give you all of the data in the fields for the new and old objects (old objects will only have data if a record is updated), plus all of the Decisions that the record went through in the associated Flow, plus the results for each outcome.

Here’s an example:

16:23:37.142 (150821372)|FLOW_START_INTERVIEW_BEGIN|8557980ff96eae468bac8498796162d5d1cd65-6ef6|JBSystemFlow_Lead
16:23:37.142  16:23:37.142
(151572909)|FLOW_ELEMENT_BEGIN|8557980ff96eae468bac8498796162d5d1cd65-6ef6|FlowDecision|JB_SalesforceObj2d95094362a61233d7e7d05c70c5bd3a_Decision 16:23:37.142
(151749528)|FLOW_RULE_DETAIL|8557980ff96eae468bac8498796162d5d1cd65-6ef6|SalesforceObj2d95094362a61233 (151839236)|FLOW_START_INTERVIEW_END|8557980ff96eae468bac8498796162d5d1cd65-6ef6|JBSystemFlow_Lead

Each FLOW_ELEMENT_BEGIN/FLOW_ELEMENT_END and rows in between will show information about specific Decisions in the Flow, their Outcomes, and the results of those Outcomes.

Under the FLOW_START_INTERVIEW_END…JBSystemFlow(Object) line, if everything went well with the Flow, you will see a line for FLOW_BULK_ELEMENT_BEGIN…FireJBTrigger. If you see this line, unless there are several errors directly below, Salesforce ran the APEX job to fire the Entry Event. At this point, you would check the Marketing Cloud Connect logs to see if there was an issue with completing the call (click here to skip to this section of the article). 

If you don’t see the FireJBTrigger line, copy the lines with decisions to a notepad.

Find the Flow associated with your Journey

Go to Setup, search for Flows in the Quick Find box and click Flows. Click Open next to the name of the Flow from the Debug Log (i.e. JBSystemFlow_Lead).

Once the page loads, you can drag the different nodes out to see the flow of the Decisions and Jobs. Note, that there is one Flow per Object that has at least one active Journey, which means there can be multiple Journeys referenced in one flow if more than one Journey is active on that Object. If you have a few different Journeys using, for example, Lead as the Object, you will have Decisions for the separate criteria. Sometimes a record may not pass the criteria for the Journey you are looking at but will continue through the Flow to see if it meets the criteria of other Journeys.

You will now have to find a Decision, where Name in the Decision lines up with the name at the end of the FLOW_ELEMENT_BEGIN. The second line after FLOW_ELEMENT_BEGIN, FLOW_RULE_DETAIL, will give you the name of the Outcome, and the results. The details of the Outcomes show up in the lower half of the Decision properties in the Flow. Focus only on the lines from the Debug Log that end with false, as those are the ones that did not pass the criteria. Here’s an example:

16:23:37.142 (151572909)|FLOW_ELEMENT_BEGIN|8557980ff96eae468bac8498796162d5d1cd65-6ef6|FlowDecision|JB_SalesforceObj2d95094362a61233d7e7d05c70c5bd3a_Decision 16:23:37.142 (151749528)|FLOW_RULE_DETAIL|8557980ff96eae468bac8498796162d5d1cd65-6ef6|SalesforceObj2d95094362a61233d7e7d05c70c5bd3a|false 

Once you are able to match the Decision Outcome(s) from the Flow canvas with the Decision(s) from Debug Log, you will be able to find out which criteria were not met and why the Flow stopped without injecting the record to the Journey.

Check the Marketing Cloud Connect logs

  1. Navigate to the Documents object in the Salesforce Classic interface
  2. Select the Marketing Cloud Documents folder
  3. Click Go!
  1. To view the log, select the log with the corresponding date

In the log, look out for any exceptions, here’s an example:

*****2019-10-08 09:51:24.680|LOG STARTED 
*****2019-10-08 09:51:24.680|PROCESS|SecureSettingsManager 
*****2019-10-08 09:51:24.868|EXCEPTION|Expired access/refresh token.

If you see an exception related to the access token, resolve it by clearing out User Mappings and OAuth Tokens in Marketing Cloud Connect.

If there are other exceptions or errors visible in the log which you are unable to interpret yourself, it’s best to send them over to Support and ask for assistance in further troubleshooting.

Sources: https://help.salesforce.com/articleView?id=000314336&type=1&mode=1, https://help.salesforce.com/articleView?id=000314349&language=en_US&type=1&mode=1, https://help.salesforce.com/articleView?id=mc_co_initiate_log.htm&type=5, https://help.salesforce.com/articleView?id=000320849&type=1&mode=1

Unsubscribe and Log an UnsubEvent with a LogUnsubEvent Execute Call

The LogUnsubEvent provides a convenient way of handling unsubscribes when you create your own unsubscribe landing page or profile center functionality. This call allows you to unsubscribe a subscriber and log an UnsubEvent that is tracked against a specific Job, which means that you will be able to track from which email they unsubscribed and see the results on the tracking dashboard. You can configure the call to either unsubscribe a subscriber from a specific list, publication list, or all subscribers, which will effectively unsubscribe them from receiving any emails.

What’s more important, if you’re using Marketing Cloud Connect to connect with your Salesforce Sales or Service Cloud org, the unsubscribe will be captured on the subscriber record in Sales/Service Cloud. It will automatically check the Email Opt Out (HasOptedOutOfEmail) flag and will also be visible in the Individual Email Results for the corresponding email send.

The LogUnsubEvent Execute call uses the following parameters:

  • SubscriberID – The Marketing Cloud generated ID that uniquely identifies a subscriber.
  • SubscriberKey – The client supplied ID that uniquely identifies a subscriber.
  • EmailAddress – The email address of the subscriber.
  • JobID – The ID of the Job that sent the message.
  • ListID – The ID of the List that the subscriber belonged to. You can use subscriber or publication lists (not suppression lists).
  • BatchID – The ID of the Batch within the Job.
  • Reason – (Optional) The reason the subscriber is being unsubscribed.

The parameters can be divided into 3 sections:

  1. Subscriber context
  2. Job context
  3. Unsub reason

If you make this call from the parent unit of an Enterprise 2.0 account, ensure that you include the ClientID of the child business account to return information specific to that business unit.

Subscriber Context

The Subscriber Context is defined by the SubscriberID, SubscriberKey and EmailAddress parameters. You must supply at least one of these parameters. If you provide more than one of these parameters, we retrieve the Subscriber using one of the values and validate that the other values match the retrieved Subscriber. If they don’t match, an error returns.

If the SubscriberKey permission is turned on and you supply the EmailAddress parameter, you must supply either the SubscriberID or the SubscriberKey.

Job Context

The Job Context is defined by the JobID, ListID and BatchID parameters. These values are used to determine which Job the UnsubEvent is tracked against. The subscriber is also unsubscribed from the List that the Job was sent to. You don’t need to supply all three values. The system looks up any missing values using the following rules:

  1. If the JobID is supplied, we can lookup a missing ListID and/or BatchID.
  2. If the ListID is supplied, we can lookup a missing JobID and/or BatchID.
    1. If the JobID is missing, we use the most recent JobID that the subscriber was sent to.
    2. This may not be the Job that the Subscriber is acting upon.
  3. If only the BatchID is supplied, we cannot lookup the remaining information and the job context is not defined.

If the job context cannot be established because you did not supply any of these parameters or only supplied the BatchID, the UnsubEvent is not created. The subscriber is also Master Unsubscribed from the system of being unsubscribed from a particular list. Remove the ListID to address the All Subscribers list in an account.

Unsub Reason

This is used to specify the reason the subscriber is being unsubscribed from the system. If the reason is not supplied, the default value is used: Unsubscribed via Log Unsub Event Execute call.

Here is an example SOAP Request envelope:

We will now look at three different ways to implement this solution on a CloudPage.

LogUnsubEvent using AMPscript

This is probably the most common way to use the LogUnsubEvent call. Below script will retrieve the SubscriberKey, JobId, ListId and BatchId if you link to the unsubscribe page from your email using the CloudPagesURL function. Depending on whether a list/publication list was selected for the send, it will unsubscribe a subscriber from that particular list, or it will unsubscribe the subscriber from all emails if you used All Subscribers.

LogUnsubEvent using Server-Side JavaScript

The implementation of the LogUnsubEvent call in SSJS will be almost identical to the AMPscript solution, as the methods for accessing SOAP object data with SSJS are primarily wrappers around AMPScript functions.

Like in the previous example, the script will retrieve the SubscriberKey, JobId, ListId and BatchId if you link to the unsubscribe page from your email using the CloudPagesURL function and will unsubscribe a subscriber either from a list or All Subscribers, depending on which one you use at send time.

LogUnsubEvent using WSProxy

WSProxy is a new object for Server-Side JavaScript, introduced by Salesforce in 2018. It acts as a proxy between the Marketing Cloud SOAP Web Service and SSJS. The WSProxy object is native to the platform and simpler to use than the SSJS methods. The object reduces overhead and increases the speed of your API calls.

Therefore, the below script is the most simple, and what’s more important, the fastest way to execute the LogUnsubEvent API call. In a speed test ran using the Chrome DevTools, it proved to be the fastest one to load on a CloudPage, with AMPscript just slightly slower and SSJS the slowest one, taking twice as much time to load.

Just like in the previous examples, the script will retrieve the SubscriberKey, JobId, ListId and BatchId if you link to the unsubscribe page from your email using the CloudPagesURL function and will unsubscribe a subscriber either from a list or All Subscribers, depending on which one you use at send time.

If you would like to find out more about logging an UnsubEvent or WSProxy, check out the following articles:

Salesforce Marketing Cloud API Authentication using Server-Side JavaScript

In order to interact with Salesforce Marketing Cloud APIs you need to create an installed package in your Marketing Cloud account first. Marketing Cloud uses installed packages to help authenticate users and API requests. To create and install packages you must have the Administrator or Marketing Cloud Administrator role assigned to your profile.

Marketing Cloud has two types of installed packages: packages with enhanced functionality (v2) and packages with legacy functionality (v1). If you’re not sure which package has been installed in your account, check the “Details” tab of your installed package – all legacy packages have a banner at the top indicating that it’s a legacy package and a Licenses tab. Enhanced packages have an Access tab.

Note the Authentication Base URI , Client Id and Client Secret as you will need them to authenticate. Below code examples are meant for Server-to-Server Integrations with Client Credentials Grant Type.

Request access token for a Legacy Package

The following script will allow you to authenticate if you’re using the Legacy Package. As of August 1, 2019, Marketing Cloud has removed the ability to create legacy packages, but you can still use legacy authentication and API requests with existing legacy packages.

You will need to insert the Client Id and Client Secret and your tenant-specific authentication endpoint (Authentication Base URI). Bare in mind, that it is not a good idea to publish Client credentials on a CloudPage.

The access token will be valid for 60 minutes and it is issued with the scopes specified on the API integration in Installed Packages. With Legacy Packages, the access token can only be used in the context of the business unit that created the integration.

Request access token for Enhanced Packages

For this package, you will need to insert the Client Id and Client Secret, your tenant-specific authentication endpoint (Authentication Base URI) and define grant_type as "client_credentials" for server-to-server integrations. You can also specify two optional parameters, scope and account_id. If you don’t include the scope parameter in the request, the token is issued with the scopes specified on the API integration in Installed Packages. For a full list of permissions click here. Account ID is the MID of the target business unit. Use this parameter to switch between business units. If you don’t specify account_id, the returned access token is in the context of the business unit that created the integration. Again, bare in mind, that it is not a good idea to publish Client credentials on a CloudPage.

The access token has a lifetime of 20 minutes. In the response you will also see the scope, rest_instance_url (your tenant’s REST base URL for making REST API calls) and soap_instance_url (your tenant’s SOAP base URL for making SOAP API calls).

Token type will always be “Bearer”, regardless of the package you are using.

To learn more about Marketing Cloud APIs, visit the Trailhead module Marketing Cloud APIs and the official documentation: Intro to Marketing Cloud APIs.

Create a Sales Cloud-integrated lead capture form using AMPscript

SmartCapture forms provide a quick, user-friendly way to create simple forms and use them in Salesforce Marketing Cloud journeys, but they are not very versatile and often can create more problems than they actually solve.

In this tutorial, you will learn how to create a form using HTML and AMPscript and include functions that will let you quickly push the collected data into your instance of Sales/Service Cloud.

Create an HTML form

First, let’s create a very simple HTML form. If you’ve never created forms in HTML, you can start with reading this short overview on w3schools. The form action method attribute specifies how to send form-data. In this case, we are using post, which appends form-data inside the body of the HTTP request (data is not shown is in URL).

The RequestParameter('PAGEURL') function reloads the page when the form is submitted, posting the form parameters back to the same page, which are then retrieved by the RequestParameter() AMPscript functions. The form has a hidden submitted field. If this value is set to true, which it would after submitting the form, it will trigger the evaluation of the AMPscript functions. Here is a simplified diagram to help you understand this concept:

That’s why in the first step we check if the form has already been submitted or not. Let’s add the AMPscript functions to the form now.

Create form logic using AMPscript

Let’s start simple with creating a Sales Cloud Lead using the data collected in the form. The AMPscript needs to be placed at the top, as it should be evaluated upon form submission and prevent the form fields from being displayed after it’s been submitted. In the below script, we use the CreateSalesforceObject function to create a new Lead:

We can extend the logic a bit by checking first if the Lead with the given email address already exists in Sales Cloud, and if yes – updating their data, or if not – creating a new Lead:

Let’s now add the Lead to a Campaign in Sales Cloud. You will need to hardcode the Campaign Id into the script. Note that you cannot add the same Lead to a campaign twice, so if the Lead has already been added to the campaign, you will get an error. Let’s add creating a new Campaign Member to the script:

And now, let’s put the icing on the cake and send a confirmation email to the Lead. You will need to create a Triggered Send beforehand – if you’re not sure how, read my blog post: Send a triggered email using AMPscript. We will hardcode the Triggered Send external key as @ts_extkey, pass the Lead ID and Email Address to a new Subscriber object and invoke creation of a new Triggered Send object:

You now have a fully functional lead capture form integrated with Sales Cloud!

If you’d like to see this in action, fill in this example form that I created to receive a confirmation email:
https://pub.s10.exacttarget.com/bhu3kciqhjk.

Send a triggered email using AMPscript

Triggered sends allow you to automatically send personalized and timely messages to your subscribers. They are sent to an individual subscriber in response to a subscriber action. Common use cases include sending welcome emails to new subscribers, service case notifications or post-purchase thank you notes to clients. They can be personalized just as any other email sent from Salesforce Marketing Cloud and triggered in various ways, among others, using AMPscript. You can read more about Triggered Sends in Salesforce Marketing Cloud here: Triggered Emails.

Create a Triggered Email Message Interaction

In order to create a Triggered Email Message Interaction, we need all the elements that we would use for a user-initiated email: an email, a sender profile and a list for managing subscriber statuses. Once you have all of the above ready, you can create a Triggered Send:

  • Go to Email Studio > Interactions > Triggered Sends
  • Click on Create
  • Provide a name, select your sender profile, email and list used for subscriber management
  • Save your Triggered Send
  • Check the checkbox on the list of your Triggered Sends and click on “Start/Restart”

Now copy the “External Key” of the Triggered Send you just created, you will need to insert it into the script in the next step.

Trigger the email using AMPscript

In order to trigger the email, we will use the InvokeCreate function, which invokes the Create method on an API object. The following script uses the CreateObject function to create the TriggeredSend and TriggeredSendDefinition objects as @ts and @tsDef, and to create a new Subscriber record as @ts_sub. We will specify the attributes of the Triggered Send object and the Subscriber object using the SetObjectProperty function. The AddObjectArrayItem function is used to add the object attributes, and everything is then passed into the InvokeCreate function. The InvokeCreate function returns a status code and a status message, by which exception handling can be built using the RaiseError function.

In the above script, replace ExternalKey with the External Key of the Triggered Send you created earlier and replace email@example.com with your email address. You are now ready to run this script on a CloudPage by publishing it. Upon success, you should be able to see the following values:

Triggered Send status code: OK
Triggered Send status message: Created TriggeredSend
Triggered Send error code: 0

The above script is simplified, as it uses the email address of a subscriber as the subscriber key. If you would like to pass the subscriber key separately, you will need to add one more variable @ts_subkey and change the SetObjectProperty for the Subscriber record:

To see how this script works in action, visit the CloudPage that I created and submit your email address here.

The delivery speed for Triggered Send emails is usually quicker than for user-initiated emails, so if the email doesn’t make it into your inbox in a couple of minutes, check your spam folder.

Here are additional resources to learn more about Triggered Sends:

Data Views in Salesforce Marketing Cloud

Data Views are a powerful feature of Salesforce Marketing Cloud. They store subscriber information and the last six months of tracking data for your account. While they cannot be accessed or viewed from the UI, they can be used like any other data extension: you can write a query against a data view in Automation Studio or in the new Query Studio and populate the results into a new data extension. You can join them with each other and with other data extensions. You can also reference them in AMPscript, for example in a Lookup function. Remember, that in order to be able to use Data Views, you first need to ask support to enable them in your account.

Data Views provide the data and metrics that can help you drive your analytics efforts to the top. Understanding how they relate to each other is key to getting the maximum value from your Marketing Cloud data. The official Data Views documentation provides details about (almost) all available Data Views, table names, fields names and data types. The below visualization shows how the most important of the tables are related to each other (click on the image to enlarge it or download a high-quality PDF here).

Data Views in SQL queries

If you’re looking for some example SQL queries for use with Data Views, visit the SQL Reference. You can basically join any of the email-related Data Views, as all of them have SubscriberKey, SubscriberID and JobID fields. Here’s an example:

Unengaged subscribers (no opens or clicks) in the last 7 days

Here’s an example of a very generic query, that will pull all the tracking data from Data Views into one table. All you need to do, is to create a Data Extension with the following fields:

SubscriberKey
EmailName
SentDate
OpenDate
ClickDate
BounceDate
BounceCategory
UnsubscribeDate

and run the following query:

In order to combine the data about your subscribers kept in the Profile Attributes with any of the Data Views, you will need to join them on SubscriberID with the EnterpriseAttribute data view. If you are running your query outside the parent business unit, it has to be used with the ENT. prefix. Here’s an example of a query that will pull all subscribers who opened an email in the last 7 days and their gender, stored in a Profile Attribute, is “Female”:

It’s a good idea to mirror Data Views in your account if you need access to more than the last 6 months of tracking data. This can be easily achieved in Automation Studio, where you can create a scheduled automation to regularly run SQL queries on Data Views and populate results into a data extension, here’s an example: Track Email Status logs using Data Views.

Data Views in AMPscript

If you want to use Data Views in AMPscript, you can reference them like any other data extension, for example:

Hidden Data Views

As mentioned in the beginning, almost all Data Views are documented, but there are at least four, which you won’t find in the official documentation. Two of them hold data related to Mobile Connect and you can access them by referencing _MobileAddress and _MobileSubscription. The other two hold data related to Mobile Push and their names are _PushAddress and _PushTag. Below is a full list of their attributes, courtesy of the Salesforce Stack Exchange community:

_MobileAddress

 Field Name       Data Type Required Data Default 
 _ContactID       Text                Y                            
 _MobileNumber    Text(15)            Y                            
 _Status          Text                N                            
 _Source          Text                N                            
 _SourceObjectId  Text(200)           N                            
 _Priority        Text                N              1
 _Channel         Text(20)            N                            
 _CarrierID       Text                Y              0
 _CountryCode     Text(2)             Y                            
 _CreatedDate     Date                Y               GETDATE()    
 _CreatedBy       Text                N                            
 _ModifiedDate    Date                Y               GETDATE()    
 _ModifiedBy      Text                N                            
 _City            Text(200)           N                            
 _State           Text(200)           N                            
 _ZipCode         Text(20)            N                            
 _FirstName       Text(100)           N                            
 _LastName        Text(100)           N                            
 _UTCOffset       Decimal(4,2)        N              0
 _IsHonorDST      Boolean             N               false        

_MobileSubscription

 Field Name                 Data Type RequiredData Default 
 _SubscriptionDefinitionID  Text(200)           Y                            
 _MobileNumber              Text(15)            Y                            
 _OptOutStatusID            Text                N                            
 _OptOutMethodID            Text                N                            
 _OptOutDate                Date                N                            
 _OptInStatusID             Text                Y                            
 _OptInMethodID             Text                N                            
 _OptInDate                 Date                N                            
 _Source                    Text                N                            
 _CreatedDate               Date                Y               GETDATE()    
 _CreatedBy                 Text                N                            
 _ModifiedDate              Date                Y               GETDATE()    
 _ModifiedBy                Text                N                            

_PushAddress

 Field Name        Data Type Required  Data Default 
 _ContactID        Text                Y                            
 _DeviceID         Text(200)           Y                            
 _APID             Text(38)            Y                            
 _Status           Text                N                            
 _Source           Text                N                            
 _SourceObjectId   Text(200)           N                            
 _Platform         Text(100)           N                            
 _PlatformVersion  Text(100)           N                            
 _Alias            Text(100)           N                            
 _OptOutStatusID   Text                N                            
 _OptOutMethodID   Text                N                            
 _OptOutDate       Date                N                            
 _OptInStatusID    Text                Y              0
 _OptInMethodID    Text                N                            
 _OptInDate        Date                N                            
 _Channel          Text(20)            N                            
 _CreatedDate      Date                Y               GETDATE()    
 _CreatedBy        Text                N                            
 _ModifiedDate     Date                Y               GETDATE()    
 _ModifiedBy       Text                N                            
 _City             Text(200)           N                            
 _State            Text(200)           N                            
 _ZipCode          Text(20)            N                            
 _FirstName        Text(100)           N                            
 _LastName         Text(100)           N                            
 _UTCOffset        Decimal(4,2)        N              0
 _IsHonorDST       Boolean             N               false        
 _SystemToken      Text(4000)          N                            
 _ProviderToken    Text(200)           N                            
 _Badge            Number              N                            
 _LocationEnabled  Boolean             N                            
 _TimeZone         Text(50)            N                            
 _Device           Text(100)           N                            
 _HardwareId       Text(100)           N                            
 _DeviceType       Text(20)            N                            

_PushTag

 Field Name     Data Type Required Data Default 
 _DeviceID      Text(200)           Y                            
 _APID          Text(38)            Y                            
 _Value         Text(128)           N                            
 _CreatedDate   Date                Y               GETDATE()    
 _CreatedBy     Text                N                            
 _ModifiedDate  Date                Y               GETDATE()    
 _ModifiedBy    Text                N                            

Sources: https://salesforce.stackexchange.com/questions/73127/how-to-bulk-export-mobileconnect-sms-subscriptions, https://salesforce.stackexchange.com/questions/174179/attributes-in-pushaddress-and-pushtag-data-views/174180#174180

If you’re looking for more information about Data Views and SQL queries in Marketing Cloud, check out the official documentation, topics related to Data Views on Salesforce Stack Exchange and Adam’s post about Troubleshooting Queries in SFMC.

Create a SmartCapture form prefilled (prepopulated) with Salesforce data

Smart Capture allows you to easily create forms on your Cloud Pages to collect information about your subscribers and then use that data for future sends and campaigns. While creating a Smart Capture form doesn’t require any coding skills and can be done using only the drag-and-drop editor, adding a simple script to your form can significantly increase the user experience. Forms prepopulated with data can be used for any type of campaign, as long as you already have some data about your subscriber in your database. Subscribers are more likely to register for an event or a raffle when they don’t need to manually fill in all the form fields, and the only thing they have to do is to submit the form.

Create a SmartCapture form

The first thing you will have to do is to create a Smart Capture form on your Cloud Page. Depending on your requirements, you can either create your own Data Extension to collect data submitted by your subscribers or use the system Data Extension called CloudPages_DataExtension.

For the purpose of this tutorial, let’s use the system data extension and create a very simple form with three fields: First Name, Last Name and Email Address.

SmartCapture form on a CloudPage

Add AMPscript to retrieve Salesforce data

In this example, we will be retrieving Sales Cloud data to prepopulate the form using the RetrieveSalesforceObjects function. We will be looking up Contacts in Sales Cloud based on the Subscriber Key. Add the following code in a separate content area, above your Smart Capture form:

If you’re using Campaigns in Sales Cloud, which can admit both Contacts and Leads, you can extend your code with a Substring function, so that it’s able to correctly process both:

You can also use data stored in Marketing Cloud to prefill the form – just remember, that you will need to use the LookupRows function instead of RetrieveSalesforceObjects.

Prepopulate the data in the form

In order to add the data retrieved from Salesforce to your form, you will have to modify each of the form input fields separately. Click once on the Smart Capture form, and then once again on the field you wish to modify.

Now edit the field in the HTML view and add an inline display of the adequate field retrieved from Sales Cloud as the default value at the end of the HTML input tag:

Do this for all the fields that should be prepopulated with data. Publish your CloudPage and test it – the fastest way to test the form is to create an email with a button leading to your CloudPage and previewing the email against a subscriber, who is present in your Salesforce database.

You can also connect this form to a journey in Journey Builder to send a confirmation email to anyone who filled it in, update their Salesforce data or add them to a consecutive campaign.

Read more here about Using a Smart Capture Form as a Journey Builder Entry Event.

Make a simple API call in Salesforce Marketing Cloud using AMPscript

In this tutorial, I would like to show you how to make a simple API call using the HTTPGet function. In AMPscript, there are three HTTP functions used to interact with third-party APIs (HTTPGet, HTTPPost, HTTPPostTo). The HTTPGet function retrieves content from a publicly accessible URL. In the ampscript.guide, you can see an example of the HTTPGet function that pulls the content from httpbin.org, a simple HTTP request and response service that doesn’t require any authentication. In this tutorial, we are going to connect to NASA’a open API and display the Astronomy Picture of the Day on our CloudPage.

NASA’s Astronomy Picture of the Day website

Get your NASA API key

The first thing that you will need to do, is to sign up to receive your own API key to access and use NASA’s APIs: get your NASA API key. After you sign up, you will get your api_key in the email, along with the URL used to make the request. When you click the URL with your api_key appended, you will get a response in JSON format with all the details about today’s image, including a URL to the image itself:


"date":"2019-08-14",
"explanation":"What's that next to the Moon? Saturn. In its monthly trip around the Earth - and hence Earth's sky - our Moon passed nearly in front of Sun-orbiting Saturn earlier this week.",
"hdurl":"https://apod.nasa.gov/apod/image/1908/MoonSaturn_Patonai_1280.jpg",
"media_type":"image",
"service_version":"v1",
"title":"Saturn Behind the Moon",
"url":"https://apod.nasa.gov/apod/image/1908/MoonSaturn_Patonai_960.jpg"
}

Create a CloudPage

Now let’s create a CloudPage where we will make the API call and display the APOD image. In your CloudPage, you will need to include three variables: @apikey, @url and @response. For the purpose of this tutorial, you can hardcode the @apikey into your CloudPage, but as a good practice, it’s better to store any Keys, IDs or Secrets outside of CloudPages and reference them in your AMPscript. The @url we are going to use is the same as the one you got in your email: https://api.nasa.gov/planetary/apod and we are going to append the @apikey to it. The third variable, @response, is the response in JSON format with the details of today’s image. Your code should now look like this:

The parameters used in the HTTPGet call are:

HTTPGet(1,2,3,4)

  1. String with URL used to retrieve content
  2. True/false string that controls whether the process continues on an error
  3. String that defines whether the function allows empty content
  4. String which outputs the function status (a value of 0 indicates the status is successful). You can display the status inline after you make the call by adding %%=v(@CallStatus)=%% to the script.

Source: https://ampscript.guide/httpget/

Let’s now add an inline output of the response and publish the CloudPage. Your code should now look like this:

When you publish the CloudPage, you will see the response in JSON format, that contains the details about the image, along with the image URL, which we now need to extract in order to display the image in our CloudPage.

Parse JSON using Server-Side JavaScript

Unfortunately, AMPscript doesn’t have a function that we could use to parse JSON. If you know the format of the response, you could use the Substring function to extract the URL, but this would be a very clumsy solution – that’s why it’s much better to use the Server-Side JavaScript ParseJSON function. We will first have to pass the @response from AMPscript to SSJS using Variable.GetValue function, then parse it and pass the URL of the high definition image back to AMPscript using Variable.SetValue function. This is the script that you need to add to your CloudPage:

Display the image in HTML

Now it’s time to add one final line of code to your CloudPage. We are passing the URL of the high definition image from SSJS to AMPscript in the @hdurl variable and now we need to display it. You can use the HTML image tag:

The complete code on your CloudPage should now look like this:

Publish your CloudPage and enjoy!

Here’s the link to my CloudPage with the Astronomy Picture of the Day: https://pub.s10.exacttarget.com/c1my0fe3fjn

Debugging AMPscript

Debugging AMPscript in Salesforce Marketing Cloud can be a pain, as there is no built-in feature that would show script errors in CloudPages. The idea to add a debugging feature to CloudPages has been hanging in the Traiblazer Community’s “Ideas” section for two years now and haven’t yet reached the point threshold set by Salesforce – so if you’re reading this, visit the Add Debugging Support for CloudPages site and give it a thumb up!

Debugging AMPscript in CloudPages

When you’re working on a script in a CloudPage, the first indication that there is something wrong is the fact that when you try to publish your page, you never get a preview and the throbber just keeps spinning:

If you publish the page anyway and later try to access it, you will get the infamous “500 – Internal server error. There is a problem with the resource you are looking for, and it cannot be displayed.” message without any indication of what went wrong:

To get some details about the error behind this, try wrapping your AMPscript with a Server-Side JavaScript try/catch block:

Here is a snippet of an actual script:

This will allow you to catch some of the possible errors related to your AMPscript – here are some example error messages:

Error Message: "Call to create the salesforceobject Contact failed! Error status code: REQUIRED_FIELD_MISSING\nError message: Required fields are missing: [LastName]"

Description: "ExactTarget.OMM.FunctionExecutionException: Call to create the salesforceobject Contact failed! Error status code: REQUIRED_FIELD_MISSING\nError message: Required fields are missing: [LastName]\r\n Error Code: CREATESFOJBECT_FUNC_ERROR\r\n - from Jint\r\n\r\n"

As you can see from the above example, the error message and description will give you a clue which AMPscript function is causing the problems.

If you add a console log to the try/catch script, you will be able to see the message in your browser’s console, while it will remain hidden from the subscriber visiting your page:

Now when you open your CloudPage, you will be able to see the error message when you access the console (press F12 in Chrome or press Command+Option+C on Mac or Control+Shift+C on Windows):

Debugging with the try/catch statement will show you some errors related to the functions you used or if a record referenced in your lookup function wasn’t found in the Data Extension. Unfortunately, you will still get the 500 error in other cases, for example, if you are referencing a non-existing Data Extension in your lookup function, or if you are trying to query a parameter which hasn’t been passed correctly. In that case, the best thing you can do is to go through your script line by line, and display all the variables in your script one by one, until you find the function which is not resolving correctly, here is an example:

You can also use the Output function to output nested functions at the location where the code block appears in your CloudPage, without having to output them inline later using %%=v()=%%. Remember that it will only output a nested function, and it won’t work if you try to resolve a single variable, for example Output(@UID).

One last thing worth mentioning is to always remember that the purpose of AMPscript is to personalize a CloudPage for each subscriber, so you cannot expect your CloudPage to resolve correctly from the CloudPage link in the editor. You will always need to pass the required parameters used in your script, either by adding them to the URL (?myparam1={id1}&myparam2={id2}) or by creating a link or a button in an email using the CloudPagesURL function, and previewing that email against a subscriber.

Debugging AMPscript in Email Studio

It is a little bit easier to debug AMPscript in Email Studio because the system will show you the errors as soon as you try to preview the email against a subscriber. It is also a bit faster because you will see the results on-screen, without the need to publish the changes every time. Unfortunately, not all AMPscript functions will work in an email, but in general, it’s good for the cases when you are trying to find a bug in your script and have to go line by line.

Note that there are two separate error messages – for the HTML version and the Text version of your email. It often happens, that you copy an existing email to keep a consistent design, and while you make changes to the HTML version, you forget to update the text version of your email. This can lead to errors, with outdated AMPscript functions hidden in the text version.

AMPscript best practices

Always remember to follow the best practices and wherever possible, use blocks of AMPscrip. Make comments in your script so that it’s easy to read when someone else has to take over from you and use indentation. Use the RaiseError function in emails and crate rules for exception handling in CloudPages.

If you are looking for more resources on AMPscript debugging and best practices, check out the ampscript.guide and the official documentation.