Salesforce Marketing Cloud Data Model Part 4: How to work with Automation Studio

Salesforce Marketing Cloud Data Model Part 4: How to work with Automation Studio

Introduction to Automation Studio in Salesforce Marketing Cloud

Using Salesforce Marketing Cloud’s Automation Studio allows you to save time by automating various activities. If you want to effectively accelerate your automated marketing campaigns and underlying processes, read on to learn how to access, leverage, and schedule activities in Automation Studio.

What is Automation Studio?

Automation Studio is a Marketing Cloud application used to execute multi-step marketing and data management activities on an immediate, triggered, or scheduled basis. Automation Studio makes email sends, queries, imports, and more happen automatically. You’ll often hear the phrase “ETL” used in reference to Automation Studio, which stands for Extract, Transform, and Load.

Who needs access to Automation Studio in Salesforce Marketing Cloud?

Firstly, you need to know who typically uses Automaton Studio. Typically the users are Salesforce admins and marketing automation teams, but marketers themselves may also use it if they work with SQL queries.

How to give or restrict access to Automation Studio for Salesforce Marketing Cloud users?

You can manage the settings of access to Automation Studio through the setup page. There, under Users, you may choose whom you want to give or restrict access to Automation Studio or some of its functionality.  You can also give access to some of the Automation Studio functionality or define access to Migration Utility.

You’ll find Automation Studio under the permissions tab. Below you can find an overview of various permissions for Automation Studio. 

Automation Studio Access

How to give access to Automation Studio

Keep in mind that not all the functionality of Automation Studio in Salesforce Marketing Cloud (SFMC)  is available by default. For instance, you might need to request support from SFMC to gain access to data extract activity.  

Also, the transfer from Enhanced FTP to Safehouse is not enabled by default. You have to activate it by requesting a Support Case.

Where to find Automation Studio in Salesforce Marketing Cloud?

Hover over Journey Builder in the SFMC toolbar to activate Automation Studio. Click on it and you’ll be redirected to the Overview menu, which shows you the status of all the automations you’ve created. Here you can create, start, edit, and delete automations.

On the Activities tab, you can create Activities for your automations upfront. Note that they can run independently or can be a part of automations.

Automation Studio Activities Overview

Automation Studio Activities Overview

What does Automation Studio in Salesforce Marketing Cloud consist of?

If you click on New Automation you will be redirected to a workflow canvas, where you can create an automation. As you can see on the left side, the workflow is composed of starting sources and activities.

Automation Studio Overview

Automation Studio overview

How to create a new automation in Automation Studio in Salesforce Marketing Cloud?

Click on a New Automation to choose a starting source. You can choose between a scheduled or a triggered (file drop) automation. 

What is a scheduled automation in Automation Studio?

Scheduled automation runs on a recurring basis using a schedule you define, such as on a weekly or daily basis. You can also choose to run once the automation you create. You use it to manually set the time of the automation you want to run. For instance, you can use a scheduled automation if you import and refresh your birthday list daily, then send an email to all contacts whose birthday it is today.

 

For most Salesforce Marketing Cloud customers, scheduled automations are the bread and butter of Automation Studio. For example, a common use case is to use scheduled automations to do data imports in a “batch”. Data files from some of your other systems may be dropped to your Enhanced FTP folder on a regular basis, where they are picked up by automations scheduled to run once per day. The whole data files are hence imported all at once and at roughly the same time, hence this is commonly referred to as a “batch upload”.

What is a triggered automation in Automation Studio?

Adding a file to a specified folder on your Enhanced FTP location will begin a triggered automation. This allows you to bring in external data that isn’t being posted on a regular schedule. Alternatively, you may want to use other processes to update files in an FTP folder, and also want to create an automation that begins whenever activity occurs in the folder.

When a file is dropped into a designated Enhanced FTP folder, rather than being uploaded on schedule, File drop automations automatically begin. We recommend you read Salesforce’s Help article on the subject before running a triggered automation.

What is an activity in Automation Studio?

In Automation Studio, activities perform specific actions like transferring or extracting data, or sending an email. Activities are the building blocks of an automation.

What activities can you find in Automation Studio?

File Transfer Activity

Automation Studio allows users to update subscriber lists or data extensions using an outside file. You can define import file details and behavior by creating an import definition during the file transfer process. Import definitions are reused each time the activity runs. It is also possible to use FTP to export files from SFMC to your desktop.

Why do you need to define an FTP in Salesforce Marketing Cloud?

File Transfer Protocol (FTP) offers an online storage for your account’s data – not the data of other clients. File Transfer is used to securely transfer files from Marketing Cloud’s secure file transfer location (Safehouse) to an FTP location. This can be used to export and import all kinds of data and hence connect Salesforce Marketing Cloud (SFMC) to other systems and processes.

Users must set up an enhanced FTP in their SFMC account to take full advantage of Automation Studio. You can also use Secure File Transfer Protocol (SFTP) as an encrypted alternative to FTP. Read more about how to set up SFTP in this Salesforce blog.

How to set up an FTP in Salesforce Marketing Cloud?

Under Data Management, the Setup subcategory includes a tab named FTP Accounts. You can define your own FTP account by adding an FTP User and creating a password for said user. Later, your FTP account can be found in the overview panel. Be sure to write down your username, password,  FTP site information, and port, so you can access your Marketing Cloud FTP Account in the future.

FTP Account

How to set up an FTP in Salesforce Marketing Cloud

How to use the data from your FTP on your desktop?

If you need to post and extract a file on your own FTP, on your desktop, you need to do the following: download Filezilla on your computer and fill in the necessary credentials to get the access to your Salesforce Marketing Cloud FTP. You have to fill in your host, username, password, and port. All of them, except the password, are provided in SFMC in your FTP account

Types of File Transfer Activities

File Transfer Activities can be classified as either Inbound or Outbound.

Inbound: The Manage File option in File Transfer Activity is used for inbound file transfers. After you decrypt or unzip the compressed files, they are then sent to the SFMC Safehouse location. You then use Import Activity to read these files from Safehouse and update the SFMC Data Extension.

The below illustration shows how FTP uses safehouses to work with SFMC.

File Transfer Activity Inbound Flow

Source: https://tidbitsforu.com/file-transfer-activity/

Outbound activities are used when you export the data from Salesforce Marketing Cloud and would like to use it for further manipulations on your desktop. Later in the article, we are going to provide a use case of an Outbound File Transfer Activity. 

The below illustration shows the process for an Outbound activity in Salesforce:

File Transfer Activity Inbound Flow

Source: https://tidbitsforu.com/file-transfer-activity/

Upload File Transfer Activity moves a file from your Marketing Cloud account to an external location. Once complete, Marketing Cloud encrypts these files using PGP or GPG.

When using Upload File Transfer Activity, be sure to choose the appropriate key from the File Transfer Activity drop-down menu. This key will then be part of the file encryption process.

When importing an encrypted file using the Marketing Cloud public key, decrypt the file from the Marketing cloud FTP site in a safehouse location. After, import the file into your Marketing Cloud account.

The below illustration shows how the encrypted FTP process works:

File Encryption Cycle

Source: https://tidbitsforu.com/file-transfer-activity/

File location configuration in Salesforce Marketing Cloud

A file transfer location is an FTP site or other file storage location used to transfer files to or from your Marketing Cloud account. File transfer locations are maintained on the Admin tab of the application. You must have administrative permissions to access this screen.

File Location

File location configuration in Salesforce Marketing Cloud

Types of folders that you can find in File Locations:

  • Import Folder: the place to import Marketing Cloud Data Extensions. If files are configured using custom locations, they will be exported from Marketing Cloud and into the Import Folder on your FTP.
  • Safehouse: the internal secure storage for Marketing Cloud used for temporary processing, specifically for importing zipped and/or encrypted files from FTP, or for Extracts from Marketing Cloud.
  • Custom Import Folder: the place to configure custom sub-folders within the Import Folder. Use this location for Import activity in Automation Studio.

SQL Query Activity

A SQL Query retrieves data extension or data view information according to criteria you set, then includes that data in a data extension. Use SQL (Structured Query Language) to create the query used in this activity. 

No more than 20 query activities are permitted in a single automation step. You use SQL queries for advanced segmentation needs, or you can write a query to use for reporting options.

How to create a SQL query in Salesforce Marketing Cloud?

If you want to segment the data using SQL queries, first you need to create a data extension, which is essentially a table which will be later populated with the segmented data. Then, you have to actually write a SQL query and run it. Check out our introduction to SQL queries here

After your query is finished and is validated, you can move on to the next step. Here, you need to find your target data extension (the one you created in advance), which you want to populate with the query. You can choose from 3 options of how you want to add the data to your target data extension: Append, Update, or Overwrite.

Example of a SQL Query

The downside here is that you have to know the SQL language in order to leverage the functionality of the SQL. You can use an easy yet powerful alternative to SQL queries – DESelect. With DESelect, you can create advanced segments without code. Instead, you use simple drag-and-drop functionality. Moreover, you don’t have to create a target data extension in advance, as DESelect allows you to create new ones on the fly.

So how can you use DESelect in combination with Automation Studio?

Creating a segment in DESelect causes a SQL query to be written simultaneously, allowing you to use it later in your automation. Because all other SQL queries start with “DES,” you can easily differentiate this SQL query from the rest.

DESelect Selection

DESelect selection creation canvas

Script Activity

You can use a Script Activity for which you need to write a code in SSJS (Server-Side JavaScript) for multiple marketing automation activities in emails or landing pages. Here you can find the example of SSJS used in the Marketing Cloud. Also, check out this blog if you want to learn more tips and tricks for SSJS in Salesforce Marketing Cloud.

Script activity

Script Activity

Filter Activity

Use a data filter to create a segmented group or segmented data extension in Automation Studio. You can then filter subscribers or contacts to target specific subscribers based on their attributes and other characteristics. 

Note that to use this activity you need to create a filter activity in Email Studio in advance. Also, be informed that using this activity to apply to a data extension that already contains certain data will overwrite the existing data in that data extension. 


The Filtering option is a great opportunity to segment your data in a relatively easy no-code manner, however, your options will be rather limited. For instance, it is not possible to segment on more than two data extensions, and it’s impossible to create advanced statements for segmentation like creating subqueries or leverage custom values and picklists. Previously, we covered filters in Salesforce Marketing Cloud, so read this article to freshen up your knowledge.

Filter Activity

Filter Activity 

How can you leverage advanced segmentation without coding? Just book a demo with DESelect to see how it could work for you, using your data. Enjoy the drag-and-drop interface and create advanced segments using filtering criteria, use custom values, make the most out of picklists, and much more.

Data Extract Activity

Data extracts can be used to export certain events such as key activity metrics like bounces, clicks, and conversions. Data Extract creates a file to use outside of Salesforce Marketing Cloud. You can also use this activity to extract data from data extensions. When creating a data extract activity, remember to fill in an appropriate file naming pattern, where you specify the file type, for instance “.csv”.

Data Extract

Data Extract Activity

Verification Activity

A Verification activity allows you to avoid unintentional automation outcomes. This activity lets you select a target data extension in your automation and evaluate target data extensions based on the conditions you have set.

Once the conditions are met, the activity will stop the automation or send you a notification. You can also choose to receive an email with more context to help with troubleshooting.

Verification Activity

Verification Activity 

Wait Activity

A Wait activity pauses automation for a specified time until performing the next step. You can add one or multiple wait activities in a single automation.

Wait Activity

Wait Activity

Email Send Activity

Email Send Activity enables you to select and configure email messages to send singularly or within a scheduled sequence. You can also create a customized Send Definition to apply to other automations. 

Note that Email Sends are more often used via Journey Builder, since it allows more reporting and tracking possibilities.

Email Send Activity

Email Send Activity 

Automation Studio use case: Analyze recurring customers

In this use case, we will show you how to analyze your recurring customers for reporting purposes. In order to do that, you have to run a weekly automation and export the file from FTP to your desktop.

For instance, let’s say you have implemented a web form on your website on a normal webpage or a Cloud Page and you receive the data in a Web Leads data extension (DE). At the same time, you have a DE with your customers. In this scenario, you would like to identify your recurring customers and export that data to your desktop for further reporting in Excel on a weekly basis.

Below is an illustration showing the steps that you have to take to implement this scenario in Automation Studio.

Automation Studio Use Case

This is how it looks like in the Automation Studio workflow canvas.

Automation Studio - Scenario Canvas
  • The first step is to choose a starting source, and in our example, it is a schedule that you set on a weekly basis. 
  • You then add a SQL Query activity. In our example, you need to compare two data extensions, and for that, you create a relationship between them based on the Contact Id. In this case, you are aiming to identify the web leads who are also your customers. Alternatively, use DESelect to simply drag-and-drop the data extensions to create the relationship, instead of writing a SQL Query.
  • Later you create a data extract activity where you choose to create a CSV file by following the guidance for a file naming pattern. 
  • Create a File Transfer activity.
  • In order to get the automation on the desktop, open FilzeZilla and connect to the FTP.
  • In the folder “Import” you can find the ready-to-use CSV file with your recurring customers.

How to run your automation in Salesforce Marketing Cloud?

After setting up your automation, you have to actually run it. What you have to consider is the following: click on Run Once and choose the steps to run. In our example, we select all the steps of the automation to run. You can also choose the option to run one step of the automation if you want to test only a part of it first. 

You can also choose the option to receive a notification when your automation has finished or an error occurred. In order to do that, in the Activity tab of your automation under Notification Settings, fill in the email address of the person to whom notifications should be sent.

Notifications Automation Studio

Notifications in Automation Studio

Now you’ll receive a file with the information on the web leads who are also existing customers in order to create a campaign for them. Another option is to use the resulting CSV file for further analysis in Excel. You may want to look at the data more in-depth depending on your company and role.

There are a number of powerful use cases for automations in Automation Studio. If you have an interesting or novel way of using automations, please send us your ideas at [email protected], and we may include your case in one of our next articles.

Automation Studio in Salesforce Marketing Cloud: Conclusion

In general, Automation Studio in Salesforce Marketing Cloud allows a variety of possibilities in terms of automating your marketing processes. It opens the door to advanced segmentation, reporting, and data integration.

Automation Studio can also be leveraged for individual business needs, like creating custom account notifications or to be used for linear customer journeys like drip and birthday campaigns. However, for more refined customer journeys, including splits and more extensive reporting capabilities, Journey Builder may be a better fit. Automation Studio is a useful tool when you work with big sets of data and need to use FTP for your routine marketing operations.

Receive Salesforce Marketing Cloud best practices
and DESelect updates