Query with Data Extensions
Learning Objectives
After completing this unit, you’ll be able to:
- Connect to a data extension.
- Create a query with a data extension.
Why Use Data Extensions?
Connect to your Marketing Cloud Data Extensions directly from Intelligence Reports Advanced in order to query both your engagement and data extension data in one place. For example, if you’re interested in the number of opens and clicks based on the user’s geolocation, connect to a data extension that holds your subscriber geolocation data. You can then see where your users are engaging with your data so that you can identify trends and get marketing insights based on performance by audience attributes from your data extensions.
Query your engagement data with data extension data to retrieve additional attributes and measurements. For example, if you’re interested in the number of opens and clicks based on the user’s geolocation, connect to a data extension that holds your subscriber geolocation data. You can then see where your users are engaging with your data.
You first have to connect to a data extension so you can pull in additional attributes along with your engagement data. You can connect to any data extension that’s in your business unit and is below 50 million rows. You can connect up to 30 data extensions.
To connect to a data extension, go to the Setup tab (1), select Connected Data Extensions (2) and then click Connect Data Extension (3).
All data extensions that exist in your business unit appear in folders, just like they’re set up in Marketing Cloud.
Follow the steps in the wizard to connect to the data extension and click Next after each step. First, select the data extension you want to connect to. Next, review the fields included in the data extension.
If you want to connect your engagement data and data extension data, set a join between the fields. When creating a join, you select the field from your engagement data and data extension data that represents the same value. This way, when querying, you pull in data from both datasets.
In the example below, we defined the field for subscriber key. For more detailed information about how to create joins, see Create a Join Between Engagement and Data Extension Data.
After the data extension is connected, you can create a query with the fields from the data extension.
It can take up to 24 hours until the data is processed from a data extension.
Query with Data Extensions
To query the data extension, use SQL or the Query Builder. Use SQL to create complex use cases. If you’re using the Query Builder and you want to query data extension and engagement data in the same query, you must make sure you created a join before creating the query.
To query with data extensions navigate to the Queries tab (1) and create a new query (2). You can either use Query Builder or SQL to query with a data extension.
Add your dimensions and metrics. In this example, we selected city for the dimension, and email unique clicks, email unique opens, and email sends for the metrics in order to see insights of our engagement data per city.
As you can see, Intelligence Reports Advanced lets you access information from the database so you can pull subscriber-level data, to gain insights on data that is not available directly in your dashboards. You learned about the different methods available for querying data, including using the Query Builder, and SQL. You also learned how to connect to data extensions so you can query engagement data with data extension data.