Creating a query
A query lets you select a target according to criteria. You can associate a segment code to the query result and insert additional data into it.
Query samples are presented in this section .
For more on using and managing additional data, refer to Adding data .
The Edit query... link lets you define the targeting type, the restrictions, and the selection criteria for the population in the following way:
- Select the targeting and filtering dimension. By default, the target is selected from the recipients. The list of restriction filters is the same as those used for delivery targeting.The targeting dimension coincides with the type of element we will be working on, e.g. the population targeted by the operation.The filtering dimension enables to collect these elements, e.g. information related to the targeted person (contracts, full and final settlements, etc.).For more on this, refer to Targeting and filtering dimensions .A query can be based on data from the inbound transition, if necessary, by selecting Temporary schema when choosing targeting and filtering dimensions.
- Define the populations using the wizard. The fields to be entered can differ according to the type of target. You can preview the targeted population with your current criteria using the Preview tab.For more on creating and using filters or queries, refer to this section .
- If you have selected Filtering conditions at step 1 or using the Filters > Advanced filter... option, then you will have to manually add filtering criteria later on.You can also add data grouping conditions by checking the corresponding box. To do this, the filtering dimension must be different to the query's targeting dimension. For more information on grouping, refer to this section .You can also add more criteria by using the Expression builder and combining it with the logical options AND, OR, and EXCEPT. You can then preview the Corresponding SQL query... for your criteria combination. For more on this refer to this section .Save your filter if you wish to re-use it later.
The additional columns let you collect additional information on the targeted population, e.g. contract numbers, subscriptions to newsletters or origin. This data can be stored in the Adobe Campaign database or in an external database.
The Add data... link lets you select the additional data to collect.
Start by selecting the type of data to add:
- Select Data linked to the filtering dimension to select the data in the Adobe Campaign database.
- Select External data to add data from an external database. This option is only available if you have purchased the Federated Data Access option. For more on this, refer to Accessing an external database (FDA) .
- Select the An offer proposition option to add a set of columns which let you store the best proposition generated by the offer engine. This option is only available if you have purchased the Interaction module.
If no optional module is installed on the platform, this stage is not displayed. You will be taken straight to the next stage.
To add data from the Adobe Campaign database:
- Select the type of data you want to add. This can be data belonging to the filtering dimension or data stored in linked tables.
- If the data belongs to the query's filtering dimension, simply select it in the list of available fields to display it in the output columns.You can add:
- A field computed based on data taken from the targeted population or an aggregate (number of pending purchases within the last month, average amount of a receipt, etc.). For an example, go to Selecting data .
- A new field, created using the Add button to the right of the list of output columns.You can also add a collection of information, for example a list of contracts, the last 5 deliveries, etc. Collections coincide with fields that can have multiple values for the same profile (1-N relationship). For more on this, refer to Editing additional data .
To add a collection of information linked to a targeted population:
- At the first step of the wizard, select the Data linked to the filtering dimension option:
- Select the table which contains the information you want to collect and click Next .
- If necessary, specify the number of elements of the collection that you want to keep by selecting one of the values in the Data collected field. By default, all the lines of the collection are recovered then filtered according to the conditions specified at the following step.
- If a single element of the collection coincides with the filtering conditions for this collection, select Single row in the Data collected field.This mode optimizes the SQL query generated thanks to a direct juncture on the collection elements.If the initial condition is not respected, the result may be flawed (missing or overlapping lines).
- If you choose to recover several lines ( Limit the line count ) you can specify the number of lines to collect.
- If the collected columns contain aggregates, for example the number of failures declared, average expenditure on a site, etc. you can use the Aggregates value.
- Specify the sub-selection of the collection. For example: purchases over the last 15 days only.
- If you have selected the Limit the line count option, define the order in which the collected data is to be filtered. Once the number of lines collected is more than the number of lines that you specified to keep, the filtering order allows you to specify which lines to keep.
Example: Targeting on simple recipient attributes
In the following example, the query seeks to identify men aged between 18 and 30 and living in France. This query will be used in a workflow that aims to make them an exclusive offer for example.
Additional query samples are presented in this section .
- Name your query then select the Edit query... link.
- Select Filtering conditions in the list of types of filter available.
- Enter the different criteria for the proposed target. Here criteria are combined using the AND option. To be included in the selection, the recipients will have to fulfill following four conditions:
You can view the SQL matching your criteria combination:
- Recipients whose title is "Mr" (can also be found using the Gender field and selecting Male as a value).
- Recipients aged under 30.
- Recipients aged over 18.
- Recipients living in France.
- You can check your criteria is correct by previewing the recipients that match your query in the relevant tab:
- Save your filters so that you can use them again at a later date by clicking Finish > OK .
- Continue editing your workflow by adding other activities to it. Once it has been launched and the previous query step finished, the number of recipients found will be displayed. You can display further details using the mouse pop-up menu (right click the transition > Display the target... ).
This set of three values identifies the population targeted by the query. tableName is the name of the table that records the target identifiers, schema is the schema of the population (usually nms:recipient) and recCount is the number of elements in the table.
This value is the schema of the work table. This parameter is valid for all transitions with tableName and schema .