[Also applies to v8]{class="badge positive" title="Also applies to Campaign v8"}
Define filter conditions defining-filter-conditions
Choose the operator choosing-the-operator
Within filtering conditions, you need to link two values together using an operator.
Below is a list of the operators available:
Use AND, OR, EXCEPT using-and--or--except
For queries using several filtering conditions, you need to define links between the conditions. There are three possible links:
- And lets you combine two filtering conditions,
- Or lets you offer an alternative,
- Except lets you define an exception.
Click And (offered by default) and choose from the drop-down list.
-
And: adds a condition and enables overfiltering.
-
Or: adds a condition and enables overfiltering.
The following example lets you find recipients whose email domain contains “orange.co.uk” OR whose post code starts with “NW”.
-
Except: if you have two filters and the first one does not return a value, this type of link creates an exception.
In the following example, we want to return recipients whose email domain contains “orange.co.uk” EXCEPT if the recipient’s last name is “Smith”.
This example shows a filter which lets you display: recipients who either speak Spanish, OR are women with mobile numbers, OR recipients without an account number and whose company name starts with the letter “N”.
Prioritize conditions prioritizing-conditions
This section explains how to prioritize conditions thanks to the blue arrows in the toolbar.
-
The arrow pointing to the right lets you add a level of parentheses to the filter.
-
The arrow pointing to the left lets you delete a selected parenthesis level from the filter.
-
The vertical arrows let you move a condition, thereby changing their execution sequence.
This example shows you how to use the arrow to delete a parenthesis level. Start from the following filtering condition: City equal to London OR gender equal to male and mobile not indicated OR account # starts with “95” and company name starts with “A”.
Place your cursor on the Gender (@gender) equal to Male filtering condition and click the Remove a parenthesis level arrow.
The Gender (@gender) equal to Male condition has been taken out of its parenthesis. It has moved to the same level as the “City equal to London” condition. These conditions are linked together (And).
Select data to extract selecting-data-to-extract
The available fields vary from one table to another. All fields are stored in a main node known as the Main element. In the following example, the available fields are in the recipient table. Fields are always displayed alphabetically.
The detail the selected field is visible at the bottom of the window. For example, the Email domain field is a Calculated SQL field and its extension is (@domain).
Double-click an available field to add it to the output columns. At the end of the query, each selected field creates a column in the Data preview window.
Advanced fields are not displayed by default. Click Display advanced fields in the bottom right-hand corner of the available fields to display everything. Click again to return to the former view.
For example, in the recipient table, the advanced fields are Boolean 1, Boolean 2, Boolean 3, Foreign key of “Folder” link, etc.
The following example shows the advanced fields of the recipient table.
The various categories of fields:
Link to a table and collection element:
- Use the Add button (above the side icon bar) to add an output column in which we wish to edit the expression. For more on editing an expression, refer to this section.
- Delete an output column by clicking the red ‘x’ (Delete).
- Change the order of the output columns using the arrows.
- The Distribution of values serves as a way to view the distribution of the values of the field selected (for example, the distributions linked to recipient towns, recipient languages, etc.).
Create calculated fields creating-calculated-fields
If necessary, add a column during data formatting. A calculated field adds a column to the data preview section. Click Add a calculated field.
There are four types of calculated fields:
-
Fixed string: lets you add a string of characters.
-
String with JavaScript tags: the value of the calculated field combines a string of characters and JavaScript directives.
-
JavaScript expression: the value of the calculated field is the result of a JavaScript function evaluation. The returned value can be typed (number, date, etc.).
-
Enumerations: This type of field lets you use/modify the content of one of the output columns in a new column.
It’s possible to use the source value of a column and give it a destination value. This destination value will be displayed in the new output column.
An example of adding calculated field type Enumerations is available, refer to this section.
The Enumerations type calculated field can include 4 conditions:
- Keep the source value restores the source value to the target without changing it.
- Use the following value lets you enter a default destination value for non-defined source values.
- Generate a warning and continue warns the user that the source value cannot be changed.
- Generate an error and reject the line prevents the line from being calculated and imported.
Click the Detail of calculated field to view the detail of the inserted field.
To remove this calculated field, click the Remove the calculated field cross.
Build expressions building-expressions
The expression editing tool lets you calculate aggregates, generate function, or edit a formula using an expression.
The following example shows you how to run a count on a primary key.
Apply the following steps:
-
Click Add in the Data to extract window. In the Formula type window, select a type of formula to enter the expression.
There are several types of formulas available: Field only, Aggregate, Expression.
Select Process on an aggregate function, and Count. Click Next.
-
The primary key is calculated.
Here is a detailed view of the choices available in the Formula types window:
-
Field only lets you return to the Field to select window.
-
Aggregate (Process on an aggregate function). Here are some examples of aggregate use:
-
Count lets you run a primary key count.
-
Sum lets you add up all purchases made by a customer over one year.
-
Maximum value lets you find the customers having purchased the most “n” products.
-
Minimum value lets you sort through customers and find those having subscribed to an offer most recently.
-
Average. This function lets you calculate the average age of your recipients.
The Distinct box lets you recover unique and non-zero values of a column. For example, you can recover all of a recipient’s tracking logs and these tracking logs are changed to the value 1 since they all concern the same recipient.
-
-
Expression opens the Edit the expression window. This lets you detect telephone numbers with too many figures, likely to be input errors.
For a list of all available functions, refer to List of functions.
List of functions list-of-functions
If an Expression type formula is chosen, you will be taken to the “edit the expression” window. Various categories of functions can be associated to the available fields: Aggregates, String, Date, Numerical, Currency, Geomarketing, Windowing function and Others.
The expression editor looks like this:
It lets you select fields in the database tables and add advanced functions to them. The following functions are available:
Aggregates
String
Date
Numerical
- Currency
Geomarketing
Others
Windowing functions