Run SQL Queries in Automation Studio

Learning Objectives

After completing this unit, you’ll be able to:

• Insert SQL into an SQL Query activity.
• Use the available sample SQL queries.
• Validate and implement SQL queries in Automation Studio.

SQL Query Activity

The SQL Query activity requires more attention than the others, simply because it’s so customizable and open-ended. This activity lets you write your own SQL queries and place them in an activity to run immediately or on a schedule.

This query burrows into your data extensions or data views, retrieves the information specified in your query, and inserts that information into a new data extension for further use. Exactly what information goes into that data extension depends on how you construct your query.

If you’re familiar with SQL, you know how powerful and resource-intensive some queries can be. If you’re not familiar with SQL—trust us, SQL queries can tie up a lot of processing if you’re going through a ton of data. That’s why it’s important to familiarize yourself with our guidelines on how to write your queries. Ultimately, it’s about narrowing your query’s scope as much as possible, segmenting queries that might produce a lot of information, and limiting your request to the previous six months of data.

Put SQL to Work

Let’s take a look at how you can implement your SQL Query activity. Navigate to Automation Studio in Marketing Cloud Engagement and create a new automation, then follow these steps.

1. Drag the activity into an automation just like any other activity, then click Choose.
2. Click Create New Query Activity.
3. Give the query a name, external key, and description. Then, choose where you save the query.
4. Click Next.

Those steps get you to a screen with a blank SQL Query field, and now it’s time to flex those SQL skills.

Don’t let the blank space intimidate you, there’s some help to guide you. The list of data extensions next to the query canvas lets you drag a data extension or one of its fields onto the canvas to include it in the query. It’s a handy tool to make sure all of the field names match.

Your actual query depends on exactly what you’re looking for and how you structure and name your data, but our help documentation provides some common examples that detail what kind of data extensions you want to build, as well as sample query language to get the information itself. Whatever you decide to include, follow these steps to continue.

1. Write your query in the SQL Query canvas and click Next.
2. Choose your target data extension. This data extension stores the results produced by the SQL Query activity.
3. Choose whether the data is appended to existing data, updates any existing data while appending new data, or totally overwrites the data extension with new information.
4. Save the activity.

Now, let’s get specific and take a look at how consulting firm Get Cloudy Consulting uses an SQL Query activity.

Alex Driskel is a technical solution designer for Get Cloudy Consulting, and he wants to help one of his financial services clients with a problem. When they send important notices via email, some of their messages bounce. Unfortunately, that means the client must then send paper communications, even if the customer specifically requested only electronic communications. It’s regulatory compliance, after all! 

Alex wants to build an SQL Query activity that pulls all bounced email messages and the associated subscribers into a data extension for further contact (either to send postal messages or resolve the bounce issue). 

To start, Alex decides to pull information from a data extension called ContactProfileData, with these properties.

He’s going to use the SQL Query activity to move information into the EmailBouncesByJourney data extension, which includes these properties.

Finally, Alex writes this SQL to include in the activity.

select
j.JourneyName,
j.VersionNumber,
ja.ActivityName as 'EmailName',
s.EventDate as 'SendTime',
b.EventDate as 'BounceTime',
su.EmailAddress,
su.SubscriberKey as 'ContactKey',
su.SubscriberID as 'ContactID',
s.JobID,
s.ListID,
s.BatchID,
cpd.[Address Line 1],
cpd.[Address Line 2],
cpd.City,
cpd.State,
cpd.Zip,
cpd.HomePhone,
cpd.MobileNumber
from [_Sent] s
join [_JourneyActivity] ja
on s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join [_Journey] j
on ja.VersionID = j.VersionID
join [_Bounce] b
on s.JobID = b.JobID
and s.ListID = b.ListID
and s.BatchID = b.BatchID
and s.SubscriberID = b.SubscriberID
join [_Subscribers] su
on s.SubscriberID = su.SubscriberID
join ContactProfileData cpd
on s.SubscriberKey = cpd.ContactKey
where ja.ActivityType in ('EMAIL','EMAILV2')
and s.EventDate < cast(cast(dateadd(hh,-72,getdate()) as date) as datetime)
and b.SubscriberID is not null

This query pulls all bounces resulting from Journey Builder sends older than 72 hours (to ensure bounces get time to resolve) and puts relevant information into the target data extension so Alex’s client can get to writing letters and resolving bounces.

Validate Your SQL

Writing SQL queries takes some practice, so Automation Studio includes a Validate Syntax button that makes sure your language is structured correctly and that all referenced data sources are available. Always use that button! While this tool helps you avoid some obvious errors, it’s still important that you make sure all attributes match exactly. You won’t get an error message if they don’t match—you also won’t get the data you’re looking for. Click away, resolve your errors, and you’re good to go!

Resources

• Salesforce Help: SQL Query Activity