Marketing Cloud developers can use Automation Studio and the SQL Query activity to perform repetitive queries to retrieve analytics for past and future email sends. This approach will ultimately free up a lot of valuable time for developers. In this blog post, we will explore some example queries and demonstrate how to test, validate, and view errors of SQL Query activities. Finally, we will share some best practices, so that you can build efficient query activities that avoid taxing the system’s performance.

What is Automation Studio?

Automation Studio is an application on the Marketing Cloud platform that is used to execute tasks on an immediate, triggered, or scheduled basis. It is most useful with tasks that are repetitive. For example, a marketer may want to target people that haven’t opened any emails in a month to send them mobile messages instead (if they opted-in to receive mobile messages).

Automation Studio is easy for anyone to use! It has a sleek drag-and-drop workflow canvas to help you build your automations, and includes a variety of activities available to use out of the box.

What are Automation Studio activities?

Automation Studio activities perform specific actions, such as importing data, filtering data, running JavaScript, and sending emails. For this blog post, we will focus on the SQL Query activity. If you are curious, learn more about other activity types on Trailhead. But we’ll also be covering other activity types in future blog posts, so keep an eye out!

What is a SQL Query activity?

The SQL Query activity lets you write your own SQL queries and run them immediately or on a schedule. This activity will scan your data extensions and retrieve the information specified in your query. Since these queries are capable of retrieving and processing large amounts of data, you should narrow your queries’ scope as much as possible. Try to limit the date range of queries to only a span of six months of data when possible.

A query activity is ideal for clean, structured datasets, where audiences have already been established. If you want to unify or segment a large dataset, it is recommended that you use an internal data warehouse or an application like Genie Customer Data Cloud. Before you start creating your SQL Query activities, you will first need to create a data extension to store the data that is retrieved from your queries.

SQL query examples

Let’s dive into our first SQL query example. In this example, we are querying unique opens a particular email within 30 days after the send date. This will return a list of subscribers who have opened our email within that timeframe in our results data extension.

Now, let’s retrieve data about opens within a specific time range. In our next query example, we are querying the number of opens for the same email within a date range of 30 to 60 days after the send date. This will return a list of subscribers who opened our email within that timeframe in our results data extension.

You may already be familiar with the fact that data extensions do not typically store the subscriber status of a subscriber. If you want to know the subscriber status for a particular subscriber, you must retrieve it from a list like the All Subscribers list. In our last example, we retrieved the subscriber status for subscribers on a specific data extension by checking their status for a specific list. This will return records where the subscribers match on our list and data extension that we are querying, and output the records onto our target data extension.

How do I test and validate my queries?

It is important that you test and validate your Automation Studio activities. If an automation fails repeatedly over an extended time period, the automation will be deactivated to improve system stability and performance. When an automation is deactivated, you will receive an email from Marketing Cloud. An inactive automation can’t run again until it’s fixed and reactivated. This feature is turned on for all accounts and can’t be turned off. A SQL Query activity will be deactivated if it fails 24 times consecutively.

To view the cause of the failure, locate the failed automation in the Overview section of Automation Studio. Errors are listed in the Progress column and appear in red. Hover over an error to view the cause. See the Automation Studio Errors documentation for more information.

If you want to test your queries and the outputs of your data without having to create a permanent data extension, try Query Studio for Marketing Cloud from Salesforce Labs, which is free on the AppExchange. This app allows you to quickly run queries in Marketing Cloud with a similar experience to SQL Server Studio or MySQL Workbench, so that you can validate that the information you are fetching is what you expect.

Automation Studio best practices

Before we wrap up, let’s review some best practices that you should follow for Automation Studio.

• Avoid conflicting processes. Review your automations and identify potential overlap. For example, it is not good practice to perform data import in an Import activity and perform a SQL query in a SQL Query activity step against the same data extension. This will create contention in the data store.

• Prioritize automations. Avoid clustering automations around the same time each day. Even if these automations do not have directly conflicting processes, the extra workload to the system could cause errors or inefficient performance. Generally, it’s good to stagger hourly automation from five to 15 mins apart, for example, set hourly automation to start at 12:05 a.m. or 12:15 a.m. to avoid a top-of-the-hour (12:00 a.m.) workload all executing at the same time.

• Review query activities. Query activities can cause problems if they aren’t using SQL best practices. As a rule, your SQL should minimize the data being processed. SQL code performs best with designated index fields. If possible, isolate fields referenced in function calls (JOIN, WHERE, GROUP BY, and ORDER BY) to indexed fields. See the Optimizing a SQL Query Activity documentation for more tips.

Conclusion

You should now understand the use cases for both Automation Studio and SQL Query activities. Remember to use the best practice guidance that we stated in this article. Listed below are some additional resources to assist you. Now, let’s get querying in Marketing Cloud!

Resources

• Automate Marketing Tasks Trailhead module
• SQL Reference Guide
• Run SQL Queries in Automation Studio Trailhead module
• SQL Query Examples

About the author

Danielle Larregui is a Senior Developer Advocate at Salesforce focusing on the Marketing Cloud platform. She enjoys digital marketing, user experience design, user interface design, and mobile development. You can follow her on Twitter @DnLarregui.