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
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.
Hevo is a No-code Data Pipeline. It supports pre-built data integrations from 100+ data sources(including 30+ Free Sources). Hevo offers a fully managed solution for your data migration process to the data warehouse. It will automate your data flow from Salesforce Marketing Cloud in minutes without writing any line of code for free. Its fault-tolerant architecture makes sure that your data is secure and consistent. Hevo provides you with a truly efficient and fully automated solution to manage data in real-time and always have analysis-ready data at your destination.
GET STARTED WITH HEVO FOR FREE
Let’s look at some salient features of Hevo:
- Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
- Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer.
- Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
- Schema Management: Hevo can automatically detect the schema of the incoming data and maps it to the destination schema.
- Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within pipelines.
- Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
Simplify your Data Analysis with Hevo today!
SIGN UP HERE FOR A 14-DAY FREE TRIAL!
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:
- Click on Activities.
- Then, click on Create Activity.
- Choose SQL Query.
- Specify the query properties including name, external key and description.
- The next step is to build an SQL query.
- If you want to look at the data extensions in your account that you can query against, click on a folder.
- 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.
- If you only wish to include a single field, drag or double-click that particular field.
- 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.
- Select the destination data extension which will store the query results.
- The next step is to select the data action that the query activity performs.
- Save the activity.
- 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.
With an analytical perspective and a problem-solving approach, Shruti has extensive experience in solving business problems of data teams by delivering thoroughly researched content pivotal for data practitioners.
No-Code Data Pipeline for all your Marketing Data