Query query
Description description
The Query activity allows you to filter and extract a population of elements from the 蜜豆视频 Campaign database. You can define Additional data for the targeted population via a dedicated tab. This data is stored in additional columns and can only be used for the workflow in progress.
The activity uses the query editor tool. This tool is detailed in a dedicated section.
Related topics:
Context of use context-of-use
The Query activity can be used for various types of uses:
- Segmenting individuals to define the target of a message, audience, etc.
- Enriching data of the entire 蜜豆视频 Campaign database table.
- Exporting data.
Configuration configuration
-
Drag and drop a Query activity into your workflow.
-
Select the activity, then open it using the
-
If you would like to run a query on a resource other than the profile resource, go to the activity鈥檚 Properties tab and select a Resource and a Targeting dimension.
The Resource allows you to refine the filters displayed in the palette whereas the Targeting dimension, contextual with regard to the resource selected, corresponds to the type of population that you would like to obtain (identified profiles, deliveries, data linked to the selected resource, etc.).
For more on this, refer to Targeting dimensions and resources.
-
In the Target tab, run your query by defining and combining rules.
note note NOTE When targeting an audience, note that the audience鈥檚 definition is not referenced but copied into the query. If you make any change to the audience after it has been targeted in a query, make sure you configure the query again to take the new definition into account. -
You can define Additional data for the targeted population via a dedicated tab. This data is stored in additional columns and can only be used for the workflow in progress. In particular, you can add data from the 蜜豆视频 Campaign database tables linked to the query鈥檚 targeting dimension. Consult the Enriching data section.
note note NOTE By default, the Remove duplicate rows (DISTINCT) option is checked in the Advanced options of the Additional data tab of the query. If the Query activity contains many (from 100) additional data defined, it is recommended to uncheck this option, for performance reasons. Beware that unchecking this option can result in getting duplicates, depending on the data queried. -
In the Transition tab, the Enable an outbound transition option lets you add an outbound transition after the query activity, even if it retrieves no data.
The outbound transition鈥檚 segment code can be personalized using a standard expression and events variables (see this page).
-
Confirm the configuration of your activity and save your workflow.
Targeting dimensions and resources targeting-dimensions-and-resources
Targeting dimensions and resources let you define on which elements a query will be based to determine the target of a delivery.
They are configured in target mappings, and are defined when creating a workflow, in the Properties tab of a Query activity.
Targeting dimensions and resources are linked. The available targeting dimensions therefore depend on the selected resource.
For example, for the Resource Profiles (profile), the following targeting dimensions will be available:
While for Deliveries (delivery), the list will contain the following targeting dimensions:
Once the targeting dimension and resource are specified, different filters are available in the query.
Example of available filters for the Profiles (profile) resource:
Example of available filters for the Deliveries (delivery) resource:
By default, the targeting dimension and resource are set in order to target profiles. However, it may be useful to use a different resource from the targeting dimension if you want to look up for a specific record in a distant table.
For more on this, refer to this use case: Using resources different from targeting dimensions
Enriching data enriching-data
The Additional data tab of the Query, Incremental query and Enrichment activities allows you to enrich the data targeted and transfer this data to the following workflow activities, where it can be utilized. In particular, you can add:
- Simple data
- Aggregates
- Collections
For aggregates and collections, an Alias is automatically defined to give a technical ID to a complex expression. This alias, which must be unique, allows the aggregates and collections to be found easily afterwards. You can modify it to give it an easily recognizable name.
After adding any additional data, you can apply an additional filter level to the data initially targeted by creating conditions based on the additional data defined.
A use case on how to personalize an email with additional data is presented in this section.
Adding a simple field adding-a-simple-field
By adding a simple field as additional data, that field becomes directly visible in the outbound transition of the activity. This allows the user to check, for example, that the data from the query is the desired data.
-
From the Additional data tab, add a new element.
-
In the window that opens, in the Expression field, select one of the fields available directly in the targeting dimension or in one of the linked dimensions. You can edit expressions and use functions or simple calculations (except aggregates) from the dimension fields.
An Alias is automatically created if you edit an expression that is not a simple XPATH path (for example: 鈥淵ear(@birthDate)鈥). If you like, you can modify it. If you only select one field (for example: 鈥淍age鈥), you do not need to define an Alias.
-
Select Add to confirm adding the field to the additional data. When the query is executed, an additional column corresponding to the field added will be present in the activity鈥檚 outbound transition.
Adding an aggregate adding-an-aggregate
Aggregates allow values to be calculated from fields of the targeting dimension or from fields of dimensions linked to the targeting dimension. For example: the average amount purchased by a profile.
When using aggregate with query, its function can return to zero which is then considered as NULL. Use the Output filtering tab of your query to filter the aggregated value:
- if you want zero values you should filter on is null.
- if you do not want zero values filter on is not null.
Note that if you need to apply sorting on your aggregate, you should filter out zero values or else the NULL value will appear as the greatest number.
-
From the Additional data tab, add a new element.
-
In the window that opens, select the collection that you want to use to create your aggregate in the Expression field.
An Alias is created automatically. If you like, you can modify it by going back to the query鈥檚 Additional data tab.
The aggregate definition window opens.
-
Define an aggregate from the Data tab. Depending on the type of aggregate selected, only the elements whose data is compatible are available in the Expression field. For example, a sum can only be calculated with numerical data.
You can add several aggregates for the fields of the collection selected. Make sure to define explicit labels to distinguish the different columns in the detail of the activity鈥檚 outbound data.
You can also change the aliases that are automatically defined for each aggregate.
-
If needed, you can add a filter to limit the data taken into account.
Refer to the Filtering added data section.
-
Select Confirm to add aggregates.
Adding a collection adding-a-collection
-
From the Additional data tab, add a new element.
-
In the window that opens, select the collection that you want to add in the Expression field. An Alias is created automatically. If you like, you can modify it by going back to the query鈥檚 Additional data tab.
-
Select Add. A new window opens, allowing you to refine the collection data that you want to display.
-
In the Parameters tab, select Collection and define the number of lines of the collection that you want to add. For example, if you want to get the three most recent purchases carried out by each profile, enter 鈥3鈥 in the Number of lines to return field.
note note NOTE You must enter a number larger than or equal to 1. -
From the Data tab, define the fields of the collection that you want to display for each line.
-
If you like, you can add a filter to limit the collection lines taken into account.
Refer to the Filtering added data section.
-
If you like, you can define a data sorting.
For example, if you have selected 3 lines to return in the Parameters tab, and you want to determine the three most recent purchases, you can define a descending sort on the 鈥渄ate鈥 field of the collection that corresponds to the transactions.
-
Refer to the Sorting additional data section.
-
Select Confirm to add the collection.
Filtering added data filtering-added-data
When you add an aggregate or a collection, you can specify an additional filter to limit the data that you want to display.
For example, if you want to only process the collection lines of transactions with amounts of 50 dollars and above, you can add a condition on the field corresponding to the transaction amount from the Filter tab.
Sorting additional data sorting-additional-data
When you add an aggregate or a collection to the data of a query, you can specify whether you want to apply a sort - whether it be ascending or descending - based on the value of the field or the expression defined.
For example, if you want to save only the transaction that was carried out most recently by a profile, enter 鈥1鈥 in the Number of lines to return field of the Parameters tab, and apply a descending sort on the field corresponding to the transaction date via the Sort tab.
Filtering the targeted data according to additional data filtering-the-targeted-data-according-to-additional-data
Once you have added additional data, a new Output filtering tab appears in the Query. This tab allows you to apply an additional filter on the data initially targeted in the Target tab, by taking into account the added data.
For example, if you have targeted all of the profiles that carried out at least one transaction and an aggregate calculating the average transaction amount carried out for each profile was added to the Additional data, you can refine the population initially calculated using this average.
To do this, in the Output filtering tab, simply add a condition on this additional data.
