Query query
Create a query 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.
For more information on query samples, refer to this this section.
For more on using and managing additional data, refer to Add 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 assistant. 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鈥檚 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.
Add data adding-data
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 蜜豆视频 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 蜜豆视频 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 Access 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 蜜豆视频 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鈥檚 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 Select 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 Edit additional data.
-
To add a collection of information linked to a targeted population:
-
At the first step of the assistant, 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.
note important IMPORTANT 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 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.
-
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:
- Recipients whose title is 鈥淢r鈥 (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 view the SQL matching your criteria combination:
-
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鈥).
Output parameters output-parameters
- tableName
- schema
- recCount
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.
Optimizing your queries optimizing-queries
The section below provides best practices to optimize the queries running on 蜜豆视频 Campaign to limit the workload on the database and improve user experience.
Joins and indexes joins-and-indexes
-
Efficient queries rely on indexes.
-
Use an index for all joins.
-
Defining links on the schema will determine the join conditions. The linked table should have an unique index on the primary key and the join should be on this field.
-
Perform joins by defining keys on numeric fields instead of string fields.
-
Avoid performing outer joins. Whenever possible, use the Zero ID record to achieve outer join functionality.
-
Use the correct data type for joins.
Ensure that the
whereclause is the same type as the field.A common mistake is:
iBlacklist='3'whereiBlacklistis a numeric field, and3signifies a text value.Make sure you know what the execution plan of your query will be. Avoid full table scans, especially for real-time queries or near real-time queries running every minute.
For more on this, depending on your Campaign version, refer to these sections:
Functions functions
-
Beware of functions like
Lower(...). When the Lower function is used, the Index is not used. -
Check queries using the 鈥渓ike鈥 instruction or the 鈥渦pper鈥 or 鈥渓ower鈥 instructions carefully. Apply 鈥淯pper鈥 on the user input, not on the database field.
For more on functions, refer to this section.
Filtering dimensions filtering-dimensions
Use the query鈥檚 filtering dimension instead of using the 鈥渆xists such as鈥 operator.
In queries, 鈥渆xists such as鈥 conditions in filters are not efficient. They are the equivalent of a sub-query in SQL:
select iRecipientId from nmsRecipient where iRecipientId IN (select iRecipientId from nmsBroadLog where (...))
The best practice is to use the query鈥檚 filtering dimension instead:
The equivalent of the filtering dimension in SQL is the inner join:
select iRecipientId from nmsRecipient INNER JOIN nmsBroadLog ON (...)
For more on filtering dimensions, refer to this section.
Architecture architecture
-
Build a development platform with similar volumes, parameters, and architecture as the production platform.
-
Use the same values for the development and production environments. As much as possible, use the same:
- Operating System,
- Version,
- Data,
- Application,
- Volumes.
note note NOTE A feature that works in a development environment may not work in a production environment where the data may be different. Try to identify the main differences in order to anticipate risks and to prepare solutions. -
Make configurations that match the target volumes. Large volumes require specific configurations. A configuration that worked for 100,000 recipients may not work for 10,000,000 recipients.
Consider how the system will scale when it goes live. Just because something works on a small scale does not mean that it will be suitable with greater volumes. Testing should be done with similar volumes to the volume in production. You should also evaluate the effect of changes in volumes (number of calls, size of the database) at peak hours, peak days, and across the life of the project.