Get Familiar with Queries in Intelligence Reports Advanced
Learning Objectives
After completing this unit, you’ll be able to:
- Describe what a query is and what fields you can query.
- Create a query in SQL.
- Create a query in Query Builder.
What Are Queries in Intelligence Reports Advanced?
Query is another word for question. In Marketing Cloud Intelligence, a query is a way to request information from a database. And with Intelligence Reports Advanced you can use the Queries tab to pull subscriber-level data and gain insights on data that is not available directly in your dashboards. Let’s say you want to know which subscribers engaged in a particular journey, or you want to get more details on messages you sent to a specific subscriber. Subscriber-level data isn’t found in your dashboards, but you can query the database to get that information.
You can query your data in different ways.
- Query Builder is a clicks-not-code tool you can use to automatically create queries by selecting the dimensions and measurements you want to query.
- SQL is a language used by databases to effectively store and retrieve data. If you want to write queries manually, or need to create complex queries that the Query Builder interface doesn't support, you can use an SQL editor.
What Fields Can You Query?
When querying your data in Intelligence Reports Advanced, there are many fields available. View the available data set fields in the Queries tab (1), under the Data Set tab (2) in the left pane.
Queries unify all the available fields into one Engagement Data table. This makes it easier to find what you are looking for. You can include calculated key performance indicators (KPI) formulas in your queries if you need. For example, if you want to see results for “push open rate” you can create a calculated formula using the SQL tool in Queries. You can also use Data Extensions to create queries with your engagement data and data extension data all in one place. We'll learn more about how to query with data extensions later on.
Create a Query Using the Query Builder
The Query Builder is a dedicated tool you can use to easily create queries without having to use code. You simply select the fields you want to query. You can create a query in the Queries tab using the Query Builder, or you can select a saved query from the Query Library. Save your queries and refer back to them at a later time from the query library.
To create a Query, navigate to the Queries tab (1) and click the plus icon (2).
Next, name your query. In this example, we want to query the number of bounced email subscribers, so we’ll name the query Bounced Email Subscribers (2). Select Builder (3). Then click Create (4).
Now you can start defining the query.
To add fields to your query, click the plus icon next to Dimensions (1), Metrics (2), and Filter (3).
In our example, we are querying Bounced Email Subscribers, and have added these dimensions: Event_date, Subscriber_key, Email_domain and Email_name. We also added the email_bounces metric.
Once all the fields have been added click Run (4). After the query runs, you can see the results at the bottom of the page (5). You can save this query to your query library and use it again in the future. To do this, just click Save Query As (6), name your query, and save it. You can also download (7) or export query results, and define an ongoing schedule (8).
Create a Query Using SQL
You can create queries manually using an SQL editor. You can also use or modify one of the predefined queries in the Query Library. Simply select a query from the library and run it. Or you can modify the query and then run it.
To manually create a query using SQL, navigate to the Queries tab (1) and click the plus icon (2).
When the Create New Query window opens, you can name your query (1), select SQL (2) and click Create (3). In this example we’ll create a query called "Email Subscribers that Bounced Yesterday". 
Now it’s time to write your query (1). After you have written your query, click Run (2). You can see the query results at the bottom of the page (3).
You can also save the query (4) to the Query Library for future reference (5). You can download your query results locally (6) or schedule an export of your results (7).
Now that you know more about queries and how to create them, let's move on to the next unit to learn about how to query with data extensions.
