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:


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:


 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        


 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                            


 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                            


 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                            


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.

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 and the official documentation.

Query Studio for Salesforce Marketing Cloud

The ability to write and automate SQL queries in Automation Studio is an indispensable feature when it comes to working with data in Salesforce Marketing Cloud. The current SQL query editor in Automation Studio is very rudimentary and makes it almost impossible to debug SQL queries. It can be very time consuming and frustrating to work on more complicated queries as each time your run your query, you need to go to Data Extensions to check the results.

Salesforce has decided to make things a bit easier for Marketing Cloud users and launched a new app last month called Query Studio for Marketing Cloud. The app allows you to quickly run queries with a similar experience to SQL Server Studio or MySQL workbench.

You can get Query Studio for Marketing Cloud for free from Salesforce AppExchange and install in a couple of clicks. After you install the app, you need to log out and log back in, to be able to access it from the App Exchange menu:

The user interface of Query Studio is quite simple:

  • From the Query search box at the top, you can choose an existing query that you wish to run, or you can write your own query in the query editor window.
  • Click the Run button or use the keyboard shortcut CTRL + Enter to run your query – you will see a timer next to the Run button, showing you how long the query takes to execute.
  • You can also validate your query to check if query results can be inserted into target data extension – once you execute your query, choose a target data extension from the Data Extension search box, choose Data Action and click on Validate Data.

The validation function, according to Salesforce, will cross check for required fields, field lengths, and mismatched field data types. I ran a few tests and made a few mistakes on purpose to see how the validation feature works. It’s nice to see a description of the validation error in the results, but if you have more than one error, you will have to correct them one by one, as the validation tool only displays the first error it encounters:

Unfortunately, the Query Studio is still missing a debugging feature, which would show syntax errors in your query:

It is worth mentioning that Query Studio creates a new folder in your Data Extensions folder, called “QueryStudioResults”:

QueryStudioResults data extension folder will be created in your account to hold temporary data extensions, which have data retention set to auto-delete after 24 hours. Each time you run a query in Query Studio, a new Data Extension is created.

Every Query Studio user will also have a query activity created in Automation Studio for them, which is used for running one-off queries. It will contain ‘InteractiveQuery’ in the name:

Overall, the new Query Studio is great for developing new queries, when you want to see the results on-screen and be able to test and amend your queries quickly. I would say that this is a satisfying minimum viable product, which will hopefully evolve into a full-on SQL Console with proper debugging support in the future.

Read more and install here:

Related article: Data Views in Salesforce Marketing Cloud

Troubleshooting Marketing Cloud Connect

Marketing Cloud Connect integrates your Salesforce Marketing Cloud instance with Salesforce Sales, Service, and Community Clouds. It’s an easy-to-configure set of functionality that can bring great benefits to your organization. There is an official Marketing Cloud Connect troubleshooting guide available, but I’ve decided to share a few tips and tricks that usually solve most common connection problems.

To be able to correctly troubleshoot Marketing Cloud Connect, you will need the credentials of your system users, which are used to establish the connection between the clouds – your Salesforce System User (API User) and Marketing Cloud API User.

What is also very important, is to make sure that you are logged out of any active Marketing Cloud and Sales Cloud sessions – this is a rookie mistake, which leads to connecting both clouds using a “regular” user, instead of the dedicated Marketing Cloud API user. The best way to avoid making this mistake is to always use the Incognito Mode when working with Marketing Cloud Connect API users.

Change API user

You may receive an email from the Marketing Cloud Connect managed package with the subject: “Salesforce Marketing Cloud Connect – Action Required to Prevent Service Interruption.” This email would be sent if the package detects an issue when trying to connect your Salesforce org with your Marketing Cloud account through the Marketing Cloud Connect API User. To avoid interruption of service with Marketing Cloud Connect, reconnect your Marketing Cloud Connect API User:

  1. Log into your Sales or Service Cloud org as the System User (API User)
  2. Switch to Salesforce Classic interface
  3. Navigate to the Marketing Cloud tab
  4. Re-enter your Marketing Cloud API user credentials

It is possible, however, that the system won’t let you reconnect the API user and you will see the following error message:

Sorry, something went wrong.
We couldn’t retrieve the API User details. Try again or open a support case in the Help and Training portal.

In that case, follow these steps to reconnect your Salesforce System User (API User) without the need to delete the Connector Configuration:

  1. Login to Sales Cloud Org – as the System User (API User)
  2. Click Setup
  3. In the quick find search box type in ‘Visualforce Pages’
  4. Click on ‘Visualforce Pages
  5. Locate the label for ‘ChangeAPIUser
  6. Click on that label
  7. Click on the ‘Preview‘ button
  8. Click ‘Connect to Marketing Cloud‘ button


Clearing out User Mappings and OAuth tokens

There are multiple scenarios where it’s necessary to remove the mapping between a Marketing Cloud User and a Salesforce User related to MC Connect, for example, if the user accidentally entered the wrong username/password when they first mapped their orgs; if permissions for a User on the Marketing Cloud side have changed; or if OAuth token is stored in Salesforce, but User is still prompted for Username and Password when selecting the Marketing Cloud Tab in Sales/Service Cloud.

Proceed with the following steps to clear OAuth tokens:

  1. Log in to Salesforce using an incognito (private) browser session.
  2. Select your Username in the upper right-hand corner, then select Developer Console.
  3. Click Debug, then select Execute anonymous window
  4. Execute the following Code, each line separately:

Note: Replace the Id_of_System_User in the code above with the Salesforce ID of your system user; replace MID with MID of the integrated Business Unit in Marketing Cloud. Use only MIDs relevant to your connection.

Afterwards, use the incognito window to access the ‘Marketing Cloud’ tab again. This should reconnect your user using the correct token. 


Clearing Marketing Cloud Connect Configuration

If none of the above helped, you can try clearing the Marketing Cloud Connect configuration and re-configuring it from scratch.

As a first step, you will have to add the “Configurations” tab to your Sales/Service Cloud:

  1. Click Setup
  2. Follow the steps based on your UI:
    • Salesforce Classic: Under the “Create” category, select Tabs
    • Salesforce Lightning: Under the “User Interface” category, select Tabs
  3. Click New
  4. Choose “Configurations” from the Object drop-down menu
  5. Pick any Tab style
  6. Click Next > Next > Save

Once the tab has been added, navigate to it by clicking the Plus symbol on the tab bar and click on the Configurations link. Now delete the existing configuration:

  1. Choose All under the “View” drop-down, and then click Go
  2. Delete the existing Configuration Object

Now you are ready to re-configure Marketing Cloud Connect:

  1. Click the Marketing Cloud tab
  2. Enter the Marketing Cloud API Username and Password
  3. Select values for Send Types, Target Audience, Exclusion Audience, Support Ticket Recipient, and Tracking Preferences
  4. Click Marketing Cloud Tab once more, re-integrate individual Users as needed


Preparing for the Salesforce Marketing Cloud Developer certification

Last week I passed the Salesforce Marketing Cloud Developer exam. Afterwards, I received a lot of questions about preparing for it and the test itself. That’s why I’ve decided to share how I prepared, what helped me pass it on the first attempt and hopefully will help you too.


First, familiarize yourself with the official exam guide. The exam guide suggests that you need at least a year of experience developing for Marketing Cloud. In particular, developing dynamic, personalized marketing assets such as emails, landing pages, and forms leveraging HTML, CSS, and AMPscript. You also need to be proficient in SQL and have experience in using Marketing Cloud APIs. I agree with that and hands-on experience with the platform, scripting and debugging is essential.

When it comes to online resources, you can start with the Develop for Marketing Cloud trail on Trailhead. There is official documentation available for AMPscript and SSJS, but I found Eliot Harper’s and Adam Spriggs’ AMPscript guide much more helpful. To learn about Marketing Cloud data architecture, download a free copy of The Data Handbook. It’s also worth reading Jeremy Garcia’s article about preparing for the exam. And of course, there’s the indispensable Salesforce Stack Exchange, where you will find tons of script examples, useful tips for debugging and Marketing Cloud development best practices.


Currently, there are no official Salesforce training offerings for Marketing Cloud developers. Fortunately, from time to time, unofficial workshops and classes are organized. Keep track of the various communication channels, like the Salesforce Marketing Cloud Developer Groups on LinkedIn or Facebook to stay up to date.

Eliot Harper
Eliot Harper during the Marketing Cloud Developer training in Munich

I have been able to attend an intensive one-day Marketing Cloud Developer Masterclass organized by Eliot Harper in Munich this year. The scope of this training was much broader and advanced than what is needed for this exam, so do not be discouraged if you did not attend the training – hands-on experience with the platform is what counts the most for this exam.

Now let’s take a closer look at each of the five areas outlined in the exam guide. 

Data Modeling: 14%

For this part, you will need to fully understand Contact Builder, the various types and uses of data extensions and how to link them. You will need to be able to describe how Contact Records relate across channels. Make sure you know how Contact Deletion in Contact Builder works – all the possible methods and how they are processed by the system. Learn about Contact Delete Best Practices and how to track statuses of contacts during the deletion process. You also need to be familiar with a legacy Contact Builder feature called Channel Address Order.

Programmatic Languages: 35%

This is probably the most important part of the exam, and you will see questions not only related to AMPscript and SSJS syntax but also their performance and processing.

You can start with reading about AMPscript syntax and best practices. Even if you already have experience with scripting, it’s worth a thorough read before taking the exam. Make sure you fully understand the AMPscript Data Modification Functions and how Marketing Cloud processes them. Familiarize yourself with Date and Time FormattingOrder of Operationsand Personalization Strings in Email Studio. You need to know the most important Data Extension Functions by heart and be able to use them without looking at the documentation, so memorize all the required parameters. Make sure you know how to use the most common Content FunctionsDate & Time Functions, HTTP FunctionsMath FunctionsString Functions and Utility Functions. Read about some of the Site-Based Functions, like CloudPagesURLRequestParameter and QueryParameter. Understand where and how to use Exclusion Scripts and the RaiseError function. You also need to fully understand if/else statements and loops. The official guide clearly states that a candidate for this exam is not expected to know how to configure Marketing Cloud Connect, so you don’t need to worry about the Sales & Service Cloud Functions.

Familiarize yourself with Server-Side JavaScript syntax and the most common Core and Platform functions. Make sure you understand what is possible with SSJS and when, where and how to use it. Learn how AMPscript and SSJS talk to each other and how to combine them for debugging.

You will see all types of questions in this part of the exam, for example: which function to use for ABC use case; which of the following is the correct way to use XYZ function; or what the outcome of a script snippet will be.

API: 22%

Same as with scripting, you will need hands-on experience with API. You can use free software like POSTMAN to interact with your instance of Marketing Cloud and download the SFMC Postman collection or the Salesforce Marketing Cloud Enhanced API Collection.

First of all, you will need to know how to Create an installed package in your Marketing Cloud account, about Subdomains and Tenant’s Endpointsauthentication and how to use the access token in SOAP and REST calls.

Learn about the use cases for REST and SOAP API and memorize the most common routes,methods and objects – I was surprised how specific some of the questions were. Familiarize yourself with Errors in REST APISOAP Error Codes and how to resolve them.

Learn how to Optimize API Calls and Data Structures to Improve Performance.

Data Management: 22%

There were quite a few SQL questions on the exam, and similarly to the Programmatic Languages, you will need to know not only the correct syntax but also about the performance and processing, so start with reading about Query Activities Best Practices. You need to be proficient in using all the statements from the SQL Reference and to know the different kinds of JoinsString Functions and Date Functions. Take a look at the Query Examples in the official documentation and read how to Optimize the Query Activity. Pay attention to Data Views, think about the most common use cases and familiarize yourself with column names for the most frequently used data views. Learn how to Troubleshoot SQL Queries in Marketing Cloud.

Read about Send Logging – you will need to know how send logging works, how to Create an Email Send Logging Data Extension and about Send Logging Best Practices and Tips.

You also need to be familiar with importingtransferring and extracting data from Marketing Cloud.

Security: 7%

For the Security portion of the exam, you will need to learn about the different methods to secure data in Marketing Cloud, for example, Field-Level EncryptionTokenized SendingTransparent Data EncryptionKey Management and AMPscript Encryption and Encoding Functions. Read about API Integration Securityfile transfer security and SSL Certificationfor Cloud Pages. Understand data security best practices.


Overall, I found this exam a bit easier than the Certified Marketing Cloud Consultant one, as there was less room for ambiguity. For example, a lot of questions contained a script or a query snippet, and you simply had to choose the correct outcome.

Thank you for reading and good luck with your exam!