Query Activities in Marketing Cloud: Comprehensive Guide 101

Are you not sure about how to use query activities in Marketing Cloud? Don’t worry, this blog post will act as your guide. You can easily get any data extension or data view information via Automation Studio’s SQL Query activity and also store it in a data extension. 

You will be looking at the following aspects in this blog:

• Need for Query Activities in Marketing Cloud
• Points to Remember
• Use the SQL Query Activity
• Conclusion

Need for Query Activities in Marketing Cloud

A query can be defined as an activity that helps you get data extension or data view information that matches your criteria and then includes that information in a data extension. SQL is used to generate the query you use in the query activity. You can give a name, external key, as well as description to specify and describe the activity within the application interface and for API calls. A query activity will execute your SQL statements for about 30 minutes. Any activity that exceeds this limit will display an error status and not give desired results.

Points to Remember

While creating a query activity, you must write the SQL statement that defines the query. Keep the following in mind:

• The query should be written against a data extension or one of the data views provided in the system.
• A Query Activity cannot take input parameters from other activities or other processes, it is isolated.
• A maximum of 20 query activities can be performed in a single automation step.
• It is possible to query up to six months’ data from any table, but this action is seldom necessary and may even result in poorly performing queries. 
• You can check the syntax of your SQL using the validation tool provided by the query activity interface.
• You may not write a query with both a join and a SELECT * statement. Instead, you will have to specify each column name, even when there are multiple columns.
• For single-line comments, you can use open-ended comment designations such as ‘–‘. Or you may use multi-line comments (/**/).
• If the query activity involves a SubscriberKey column, you can set the data type to Text given that the SubscriberKey is not an email address. In that case, you can set the data type to Email.

You have the facility to query shared data extensions in the parent account even if your account is a child account in an Enterprise. All you need to do is ensure that the Sharing tab information in Contact Builder is set up, then prefix the data extension name in the query with ENT.

Use the SQL Query Activity

Now, let us look at how to use the SQL Query Activity, Follow the given steps:

Now, let us look at how to use the SQL Query Activity, Follow the given steps:

1. Click on Activities.
2. Then, click on Create Activity.
3. Choose SQL Query.
4. Specify the query properties including name, external key and description.
5. The next step is to build an SQL query.
   1. If you want to look at the data extensions in your account that you can query against, click on a folder.
   2. If you wish to include all of its fields, drag the entire data extension into the query, or you can also double-click on the data extension. This ensures that every column in the data extension is added.
   3. If you only wish to include a single field, drag or double-click that particular field.
   4. The next step is to type or paste the query syntax.

NOTE: 

• It may be noted that the field names that have been added by double-clicking will appear at the cursor location within the query syntax.
• Dragging or double-clicking a column or the entire data extension will automatically place a comma after the last column name. Remember to remove this comma before you validate syntax.
• The SQL Query activity will not find a data extension column’s path by default. You need to explicitly designate the path. In order to test and check the query, click on Validate Syntax.

6. Select the destination data extension which will store the query results.
7. The next step is to select the data action that the query activity performs.

8. Save the activity.
9. You can start or run an activity from the Activities page or include it in automation.
   Query output is stored in the data extension you designate. View the results of each query’s run in the activity’s Action Log.

Conclusion

In this blog, you have learned how to perform query activities in Marketing Cloud. This process can be time-consuming and radius. This is where Hevo comes to the rescue.

VISIT OUR WEBSITE TO EXPLORE HEVO

Hevo is a No-code Data Pipeline. It supports pre-built integration from 100+ data sources including 30+ Free Sources like Salesforce Marketing Cloud. Hevo will completely automate your data flow from Marketing Cloud in real-time.

Want to take Hevo for a spin?

SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Have any further questions about using query activities in Marketing Cloud? Let us know in the comments section below.