Query Studio for Marketers

The Query Activity in Automation Studio has been a staple for technical marketers. In late 2019, Marketing Cloud released a new feature called Query Studio. It has quickly become my go-to tool to cut down the time to answer my  burning questions about data and quickly create audiences.

This is the first of a three-blog series that will provide tips for marketers with limited SQL knowledge to leverage this helpful tool. If you’re brand new to SQL, check out this online tutorial with interactive exercises. 

First Things First

To leverage Query Studio, you must first download the tool. To do this, you must have administrator-level privileges for Marketing Cloud. 

From your Marketing Cloud instance, navigate to “App Exchange.”

+ Search “Query Studio” and then “Get It Now.”

+ Follow the prompts and “Install.”

+ Marketing Cloud will now push you to the “Installed Packages” section of your “Setup” menu.

+ In the top right corner, there is another button to “Install.”

+ Agree to the terms, and Marketing Cloud will show a “Manage Licenses” call out.

+ Under licensees, you can now provision the app to other users and business units, as required.

Depending on your preferences, you can also check a box to make the app available to all current and future users in a business unit. Hover over the AppExchange icon in the navigation bar, and Query Studio will now appear in the drop-down menu.

Now, you’re ready to start using Query Studio!
 

4 Queries for Common Problems

While developers will enjoy the improved Query Studio functionality to cut down time for building new audiences, non-technical marketers can also use the tool to improve their marketing. These short queries will help answer data questions and provide the freedom to improve audiences without assistance from SQL developers.

Please note: In my work, I always start in Query Studio to build these queries. Once I’m ready to use the queries, I copy the query into Automation Studio and run a “Query Activity” to  build sendable data extensions for exclusions or targeting.

1. Why isn’t my filter returning records?

If you’re using filters to build email audiences, and you’re not getting the results you expect, it may be due to incorrect values in your filter statements.

Example: I want to pull users where Status = Converted. I build a filter, but too few records are returned, or no records are returned.                      

Query Studio Solution: Use a query to identify all the possible values of the Status column, which will inform necessary changes to your filter.

The query above selects all records in my table, groups each status as a single row, and provides a count of each status. 

You can edit this query to look at any column by replacing Status. And you can query off any table by replacing FROM ENT.My_Data_With_Unknown_Values with the name of the data extension you’re referencing. ENT. (note: ENT. is only added to data extension names if the table is in a shared folder).

With this information, I can see that using Status = Converted in my filter is not returning the desired results because there are multiple types of converted Status values. Using the information in the query results, I can now customize my filter to get the exact Converted types I'm interested in targeting.
 

2. I’ve got duplicates in my data!

The out of the box functionality in marketing cloud can remove duplicate users by email address, but sometimes, that isn’t sufficient.

Example: I have a simple send with only QuoteIDs and EmailAddresses. I want to send an email for each unique QuoteID record, but for some reason, my data is returning duplicate QuoteIDs. I can’t use the “Deduplicate by EmailAddress” function in marketing cloud because some EmailAddresses have multiple valid quotes to send.

Query Studio Solution: Use a query to group by QuoteID to return one record per quote. This is similar to the solution in Number 1; now the grouping method is used to ensure each quote ID only appears a single time in my data. 
                                                                                                                                                                                                                                  

3. I’ve STILL got duplicates in my data!

Example: I have a more complicated send, with lots of columns. The grouping method in Number 2 is still returning duplicates.

Query Studio Solution: Use a query to create a row number for each duplicate record type, then select only a single record from each duplicate. 

This time, we use a row number to remove duplicate IDs. The PARTITION in line 20 tells the query how to identify a duplicate (in this case, ID). The ORDER BY tells it how to apply row numbers for each partition (in this case, Created_Date__C). 

Lines 10 to 23 are a sub-query. The sub-query runs first and applies the row number to the data.  The outer query runs second and can now reference the row number. Line 25 is the most important line, as it specifies the query should only return the first row from each partition. 
 

4. I need to exclude some specific users from my send.

Sometimes, using the “Exclusions” function when building an audience for an email doesn’t cut it.

Example: Specific users from a previous send should not be in my new audience. Because the sendable data extension from the previous send has more than one type of user, I can’t just apply it as an exclusion data extension on the new send.

Query Studio Solution: Use a sub query to remove the specific records only. 

This time, I'll select users from the data extension on line 6, while excluding email addresses that are found in the sub-query. The sub-query on line 8 references an Exclusion Data Extension and specifies Converted Status type.

You can play with IN or NOT IN, LIKE and NOT LIKE to customize this for either inclusion or exclusion of certain users.

I hope these queries will help marketers begin to take charge of their data. Over the coming months, look for additional posts related to Query Studio features, common error messages when writing queries, and a deep dive into using the data views to power your marketing.

About the Author
Kaleigh is a Senior Solution Architect with Lev. In her early career she experimented with marketing, business development, sales, nonprofit management, and account management. Along the way, she fell into solutioning and has a particular affinity for solving problems through SQL and Ampscript. For her, solutioning is that sweet spot she always hoped for in a career where talent and passion align. In her free time, Kaleigh loves bringing family together for a large home cooked meal, finding new hiking locations in the Denver Rockies, or burying herself in a good fantasy novel.