Lesson 7 of 10
In Progress

Day 7 – Automation Studio and SQL Editor

Ajeet March 12, 2022

Supporting Materials 

·        Salesforce Marketing Cloud Automation Studio 1 – Video

·        Salesforce Marketing Cloud Automation Studio 2 – Video

·        SQL Basics –Video

Power Up with Trailhead

·        SQL Basic – Trailhead

·        Automation Studio Activities – Trailhead

·        Automation Studio Overview – Trailhead

Marketing Cloud Automation Studio: Quick Overview

Automation Studio is a module of Salesforce Marketing Cloud where you can create marketing automation and data management activities, then add them to a canvas to make multi-step automated workflows. Activities that you can automate include send email, import file, file transfer, data extract, SQL query, filter, and script.

Here is a high-level overview of Automation Studio’s two tabs: ‘overview’ and ‘activities’.

A note about the post & author: This post has been adapted from “Introduction to Automation Studio”, the first video in the SFMC Automation Studio video series. Since there’s no free Marketing Cloud developer org available for community members, this series aims to help those who are new to Automation Studio – especially if you are planning on taking any of the Marketing Cloud certification exams.

Shibu also has a great collection of ‘tidbits’ on his blog, designed to be shorter digestible posts on specific subjects.

Getting Started with Automation Studio

The Marketing Cloud dashboard – you may be already familiar with it? This is what it looks like when you log in. At the top, you will see the various studios and builders that are available based on your Marketing Cloud edition.

Find Automation Studio under the ‘Journey Builder’.

Inside Automation Studio there are two primary tabs – the overview, and the activities.

  • Overview tab: where you will actually see all the automations that you have configured, what’s the progress, which is ones that have been run last and if there’s any errors.
  • Activities tab: define activities here that you can reuse in multiple automation workflows (more on this later).

One more thing. You will (or should) have a folder structure and you can store automations under the various folders.

Create a New Automation

When you want to create a new automation, you click the ‘new automation’ button (top right hand side). Then, you can define the workflow.

As you will see, the left side will show the different building blocks that you have for the automation workflow. You have the starting sources which are schedule and file drop. The first step in building automation is to choose one of the starting sources, either schedule or file drop.

Note: don’t be alarmed – there are future videos in the series where we will go into each in detail, and explain when to use which starting source.

Then, there are a bunch of activities that you can use as you build the workflow, which you can drag and drop to build the different parts of the workflow.

Create Automation Studio Activities

Now, let’s look at the ‘activities’ tab.

We saw those building blocks earlier on the ‘overview’ tab. You’ll find there are common ones, like send email, import file, file transfer, data extract, SQL query, filter, and script.

Note: In the ‘overview’ tab, you would have seen multiple types of activities, but the ones I just mentioned are the major ones you will be using.

You can define activities here. For example, if you want to import a specific file, you can create activities for each of them here, so that you can reuse them in multiple automation workflows.

Create once, and then reference the activity multiple times in your automation.

Maximise your Marketing Cloud Data with Automation Studio

Segmenting data in Salesforce Marketing Cloud means you can reach your target audience. While that’s obvious, it’s not always smooth sailing.

Do you find you need to segment (or apply filters) to your data on a regular basis to get the latest data? Think about the times you prepare to run a campaign, even as small as sending an email.

Marketing Cloud provides a manual process to filter data extensions or lists, but there is also another option to automate your frequently used segmentation: Automation Studio. First, let’s understand where you can access your data in Marketing Cloud.

How is Data Stored in Marketing Cloud?

Before you can start segmenting Marketing Cloud data, it’s key to understand what data points you are starting with. Marketing Cloud typically stores your data within Lists or Data Extensions. Here is a quick overview of both Lists or Data Extensions:


You would typically use Lists when you have less than 500,000 subscribers and also prefer simplicity over performance. Lists also limit the number of attributes you can use for a subscriber and are not recommended for when you are connected to Salesforce Sales Cloud.

Data Extensions

You would typically use Data Extensions (DE) when you have more than 500,000 subscribers (but can be used when you have less). DEs support multiple data sets and relationships with complex subscriber attributes.

When using Salesforce Sales Cloud Connect, the Salesforce Objects synced are automatically created as Data Extensions, which provide more long-term capabilities than Lists. Data Extensions are recommended for advanced segmentation and email personalization.

How Do You Speed Up Marketing Segmentation?

You have a List or Data Extension ready and want to segment this data set down – so, use a Data Filter.

Data Filters allow marketers to select specific attributes or combinations of attributes as filter criteria. The results of this filter makes a Data Extension, which is called a Filtered Data Extension.

Note: once you’ve created the Filtered Data Extension, you should remember that it will not automatically refresh for you in the future – so be careful when using old filtered Data Extensions! There is an option to manually refresh the Filtered Data Extension; this will re-apply the filter to the Data Extension and show you new records results.

One of the drawbacks is that it’s a manual process. To automatically refresh a Filtered Data Extension, we recommend you use Automation Studio.

TipThere are use cases when you do not need to refresh the filtered Data Extension automatically, such as a one-time email send or a ‘moment in time’ you want to preserve for a future marketing campaign. Our takeaway here is to be sure you note your requirements on segmentation before you set up anything automated.

How to Refresh Your Data with Automation Studio: Filter Activity

With the power of Automation Studio, you are able to create a scheduled refresh of your key Filtered Data Extensions.

Use case: Birthday Email campaign

You have a Data Extension of Contacts with a ‘birthday’ attribute. You want to send an email to those contacts with a birthday in that month.

You can achieve this by creating a Filter Activity that includes a Data Filter where the Contact’s birthday is in the current month. An Automation Studio job would be set to run monthly.

A refreshing Filtered Data Extension, starts with the Data Filter:

Step 1: Data Filter in Email Studio

Navigate to Email Studio and create a Data Filter from a Standard Data Extension. Ensure that you save this filter so that you can reference it within Automation Studio later.

Once you are satisfied with the results of your saved Data Filter, you can now move over to Automation Studio.

Step 2: Create an Automation Studio Job

In Automation Studio, create a new Automation Studio job.

Give it a name, then drag over a Filter Activity Action.

Filter Activity Actions are composed of a Data Filter from Email Studio. The second step on the filter will be to name the resulting Data Extension (when these Filters are applied within Automation Studio, they result in a New Data Extension).

Every time these filters are run, it will automatically refresh or apply the listed Email Studio Data Filter, therefore, refreshing your Data Extension.

Tip: If you need to make a change to the Filter, you can do so in Email Studio. Data Filters and the Automation Studio Activity Filter will automatically be updated with the new filter criteria. The first time you run the Automation it will create the Data Extension (be sure to add a description to the Data Extension so that you know it’s from Automation Studio).

Step 3: Schedule the Automation Studio Job

The final step is to set the schedule of the Automation Studio job. The schedule will determine when and how often this automation will run. You can also add additional Automation Studio Activities as needed to string key processes together.

Select the ‘run once’ option or ‘save’. Once saved, you can activate the automation and you’re all set with your automatic refreshing Data Extension.

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.