Documentation Campaign Campaign Web User Interface

Build your first query

Last update: Fri Apr 11 2025 00:00:00 GMT+0000 (Coordinated Universal Time)

To start building a query, access the query modeler from the location of your choice, depending on the action you want to perform. The query modeler opens with a blank canvas. Click the + button to configure the first node of your query.

You can add two types of elements:

Filtering components (Custom condition, Select audience, Predefined filter) allow you to build your own rules, select an audience, or use a predefined filter to refine your query. They are added at the start of your query and on dotted transitions. Learn how to work with filtering components

Example: Recipients who subscribed to the ‘Sports’ newsletter, Recipients living in New York, Recipients living in San Francisco
Group operators (AND, OR, EXCEPT) allow you to group filtering components in the diagram. They are added on existing transitions before a filtering component. Learn how to work with operators

Example: Recipients who subscribed to the “Sports” newsletter AND who live in New York OR San Francisco.

Distribution of values in a query

The distribution of values shows the percentage of each value of a field within a table, based on the current query parameters. Knowing the distribution of values within a query helps refine segmentation.

To access this option, in your query, click the attribute selection button as shown below. Then, click on the Information icon next to the selected attribute. You can access the Distribution of values button.

Description: Accessing the distribution of values option in a query.

NOTE

For fields with many values, only the first twenty values are displayed. In such cases, a notification Partial load warns you.
The Distribution of values option is accessible in every attribute picker. Learn how to select attributes
You can add conditions on the results by using the [!Advanced filters]. Learn more here.

Add filtering components

Filtering components allow you to refine your query by using:

Custom conditions: Filter your query by building your own condition with attributes from the database and advanced expressions.
Audiences: Filter your query using an existing audience.
Predefined filter: Filter your query using existing predefined filters.

Configure a custom condition

To filter your query using a custom condition, follow these steps:

Click the + button on the desired node and select Custom condition. The custom condition properties pane opens on the right-hand side.
In the Attribute field, select the attribute from the database that you want to use to create your condition. The attributes list includes all the attributes from your campaign database, including attributes from linked tables. Learn how to select attributes and add them to favorites

NOTE
The Edit expression button allows you to use the Campaign Web expression editor to manually define an expression using fields from the database and helper functions. Learn how to edit expressions
Select the operator to apply from the drop-down list. Various operators are available for use. Note that operators available in the drop-down list depend on the attribute’s data type.

List of available operators

Operator
Purpose
Example
Equal to
Returns a result identical to the data entered in the second Value column.
Last name (@lastName) equal to ‘Jones’ will return only recipients whose last name is Jones.
Not equal to
Returns all values not identical to the value entered.
Language (@language) not equal to ‘English’.
Greater than
Returns a value greater than the value entered.
Age (@age) greater than 50 will return all values greater than ‘50’, such as ‘51’, ‘52’.
Less than
Returns a value smaller than the value entered.
Creation date (@created) before ‘DaysAgo(100)’ will return all recipients created less than 100 days ago.
Greater than or equal to
Returns all values equal to or greater than the value entered.
Age (@age) greater than or equal to ‘30’ will return all recipients aged 30 or more.
Less than or equal to
Returns all values equal to or lower than the value entered.
Age (@age) less than or equal to ‘60’ will return all recipients aged 60 or less.
Included in
Returns results included in the values indicated. These values must be separated by a comma.
Birth date (@birthDate) is included in ‘12/10/1979,12/10/1984’ will return the recipients born between these dates.
Not in
Works like the Is included in operator. Here, recipients are excluded based on the values entered.
Birth date (@birthDate) is not included in ‘12/10/1979,12/10/1984’. Recipients born within these dates will not be returned.
Is empty
Returns results matching an empty value in the second Value column.
Mobile (@mobilePhone) is empty returns all recipients who do not have a mobile number.
Is not empty
Works in reverse to the Is empty operator. It is not necessary to enter data in the second Value column.
Email (@email) is not empty.
Starts with
Returns results starting with the value entered.
Account # (@account) starts with ‘32010’.
Does not start with
Returns results not starting with the value entered.
Account # (@account) does not start with ‘20’.
Contains
Returns results containing at least the value entered.
Email domain (@domain) contains ‘mail’ will return all domain names that contain ‘mail’, such as ‘gmail.com’.
Does not contain
Returns results not containing the value entered.
Email domain (@domain) does not contain ‘vo’. Domain names containing ‘vo’, such as ‘voila.fr’, will not appear in the results.
Like
Similar to the Contains operator, it lets you insert a % wildcard character in the value.
Last name (@lastName) like ‘Jon%s’. The wildcard character acts as a “joker” to find names like “Jones”.
Not like
Similar to the Contains operator, it lets you insert a % wildcard character in the value.
Last name (@lastName) not like ‘Smi%h’. Recipients whose last name is ‘Smith’ will not be returned.
In the Value field, define the expected value. You can also use the Campaign Web expression editor to manually define an expression using fields from the database and helper functions. To do this, click the Edit expression button. Learn how to edit expressions

Query example returning all profiles aged 21 or more:

For date-type attributes, predefined values are available using the Presets option.

Custom conditions on linked tables (1-1 and 1-N links)

Custom conditions allows you to query tables linked to the table currently used by your rule. This includes tables with a 1-1 cardinality link, or collection tables (1-N link).

For a 1-1 link, navigate to the linked table, select the desired attribute and define the expected value.

You can also directly select a table link in the Value picker and confirm. In that case, values available for the selected table need to be selected using a dedicated picker, as shown in the example below.

Query example

Here, the query is targeting brands whose label is “running”.

Navigate inside the Brand table and select the Label attribute.
Define the expected value for the attribute.

Here is a query sample where a table link has been selected directly. Available values for this table must be selected from a dedicated picker.

Example of a query sample

For a 1-N link, you can define sub-conditions to refine your query, as shown in the example below.

Query example

Here, the query is targeting recipients who made purchases related to the BrewMaster product, for a total amount of at least 100$.

Select the Purchases table and confirm.
An outbound transition is added, allowing you to create sub-conditions.
Select the Price attribute and target purchases of 1000$ or more
Add sub-conditions to suit your needs. Here we have added a condition to target profiles who purchased a BrewMaster product.

Work with aggregate data

Custom conditions allow you to perform aggregate operations. To do this, you need to directly select an attribute from a collection table:

Navigate inside the desired collection table and select the attribute on which you want to perform an aggregate operation.
In the properties pane, toggle on the Aggregate data option and select the desired aggregate function.

Select an audience

To filter your query using an existing audience, follow these steps:

Click the + button on the desired node and choose Select audience.
The Select audience properties pane opens on the right hand side. Choose the audience that you want to use to filter your query.

Query example returning all profiles belonging to the “Festival Goers” audience":

Use a predefined filter

To filter your query using a predefined filter, follow these steps:

Click the + button on the desired node and select Predefined filter.
The Predefined filter properties pane opens on the right hand side. Select a predefined filter from the list of custom filters or from favorites.

Query example returning all profiles corresponding to the “Inactive customers” predefined filter:

Copy-paste components

The query modeler allows you to copy one or multiple filtering components and paste them at the end of a transition. This operation can be executed within the current query canvas, or in any canvas within your instance.

NOTE

The copied selection is kept as long as you are working in your instance. If you log off and log back in, your selection will no longer be available for pasting.

To copy-paste filtering components, follow these steps:

Select the filtering component that you want to copy by clicking on it in the query canvas. To select multiple components, use the multiple selection tool available in the toolbar located at the upper-right corner of the canvas.
Click the Copy button in the component’s properties pane or in the blue ribbon at the bottom of the screen if you have selected multiple components.

Copy a single component
Copy multiple components
To paste the component(s), click the + button at the end of the desired transition and select Paste n items.

Combine filtering components with operators

Each time you add a new filtering component to your query, it is automatically linked to the other component by an AND operator. This means that results from the two filtering components are combined.

In this example, we have added a new audience-type filtering components on the second transition. The component is linked to the predefined filter condition with an AND operator, meaning that the query results include recipients targeted by the “Madridians” predefined filter AND belonging to the “Discount hunters” audience.

Example of a query

To change the operator used to link filtering conditions together, click on it and select the desired operator in the Group pane that opens on the right hand side.

Available operators are:

AND (Intersection): Combines results matching all the filtering components in the outbound transitions.
OR (Union): Includes results matching at least one of the filtering components in the outbound transitions.
EXCEPT (Exclusion): Excludes results matching all the filtering componentns in the outbound transition.

Example of a query

In addition, you can create intermediate groups of components by clicking the + button on a transition. This allows you to add an operator at this specific location to group together multiple components and refine your query.

In the example below, we have created an intermediate group to include results from either the “VIP to reward” or “Super VIP” audiences.

Example of a query

Check and validate your query

Once you’ve built your query in the canvas, you can check it using the Rule properties pane located on the right hand side This pane displays when building a query to create an audience. Available operations are:

View results: Displays the data resulting from your query.
Code view: Displays a code-based version of the query in SQL.
Calculate: Updates and displays the number of records targeted by your query.
Select or save filter: Choose an existing predefined filter to use in the canvas, or save your query as a predefined filter for future reuse. Learn how to work with predefined filters

IMPORTANT
Select a predefined filter from the Rule properties pane replaces the query that has been built in the canvas with the selected filter.

When your query is ready, click the Confirm button in the upper-right corner to save it.

You can modify your query at any time by opening it. Keep in mind that upon opening an existing query, it displays in a simplified view without the visiblity of + buttons. To add new elements to the query, select a component or operator on the canvas to display the + buttons.

Example of a query

recommendation-more-help