Build your first query build-query
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 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 鈥楽ports鈥 newsletter. Recipients living in New York, Recipients living in San Francisco
-
Group operators (AND, OR, EXCEPT) allow you to group together 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 鈥淪ports鈥 newsletter AND who live in New York OR San Francisco.
Distribution of values in a query distribution-values-query
The distribution of values shows the percentage of each value of a field within a table, as per the current query parameters. Knowing the distribution of values within a query can help you refining your 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 have access to the Distribution of values button.
-
For fields with many values, only the first twenty values are displayed. In such case, a notification Partial load warns you.
-
The Distribution of values option is accessible in every attribute picker.
-
You can add conditions on the results by using the [!Advanced filters]. Learn more here.
Add filtering components filtering
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 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 leverage to create your condtion. The attributes list includes all the attributes from your Campaign database, including attributes from linked tables.
note note NOTE The Edit expression button allows you to leverage 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鈥檚 data type.
accordion List of available operators table 0-row-3 1-row-3 2-row-3 3-row-3 4-row-3 5-row-3 6-row-3 7-row-3 8-row-3 9-row-3 10-row-3 11-row-3 12-row-3 13-row-3 14-row-3 15-row-3 16-row-3 Operator Purpose Example Equal to Returns a result identical to the data entered in the second Value column. Last name (@lastName) equal to 鈥楯ones鈥, will return only recipients whose last name is Jones. Not equal to Returns all values not identical to the value entered. Language (@language) to equal to 鈥楨nglish鈥 Greater than Returns a value greater than the value entered. Age (@age) greater than 50, will return all values greater than 鈥50鈥, i.e. 鈥51鈥, 鈥52鈥, etc. Less than Returns a value smaller than the value entered. Creation date (@created) before 鈥楧aysAgo(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 have to 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, we want to exclude recipients based on the values entered. Birth date (@birthDate) is not included in 鈥12/10/1979,12/10/1984鈥. Unlike in the previous example, recipients born within these dates will not be returned. Is empty In this case, the result we are looking for matches 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 the results starting with the value entered. Account # (@account) starts with 鈥32010鈥. Does not start with Returns the results not starting with the value entered Account # (@account) does not start with 鈥20鈥 Contains Returns the results containing at least the value entered. Email domain (@domain) contains 鈥榤ail鈥, will return all domain names that contain 鈥榤ail鈥. So the 鈥榞mail.com鈥 domain will also be returned. Does not contain Returns results not containing the value entered. Email domain (@domain) does not contain 鈥榲o鈥. In this case, domain names which contain 鈥榲o鈥 will not be returned. The 鈥榲oila.fr鈥 domain name will not appear in the results. Like Like is very similar to the Contains operator. It lets you insert a % wild card character in the value. Last name (@lastName) like 鈥楯on%s鈥. Here, the wild card character is used as a 鈥渏oker鈥 to find the name 鈥淛ones鈥, should the operator have forgotten the missing letter between the 鈥榥鈥 and the 鈥榮鈥. Not like Like is very similar to the Contains operator. It lets you insert a % wild card character in the value. Last name (@lastName) not like 鈥楽mi%h鈥. Here, the recipients whose last name is 鈥楽mi%h鈥 will not be returned. -
In the Value field, define the expected value. You can also leverage 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) 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.
Here, the query is targeting brands whose label is 鈥渞unning鈥.
-
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.
For a 1-N link, you can define sub-conditions to refine your query, as shown in the example below.
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 aggregate
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 audiences
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 鈥淔estival Goers鈥 audience":
Use a predefined filter predefined-filters
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 鈥淚nactive customers鈥 predefined filter:
Copy-paste components copy
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.
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鈥檚 properties pane or in the blue ribbon at the bottom of the screen if you have selected multiple components.
table 0-row-2 1-row-2 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 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 鈥淢adridians鈥 predefined filter AND belonging to the 鈥淒iscount hunters鈥 audience.
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.
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 鈥淰IP to reward鈥 or 鈥淪uper VIP鈥 audiences.
Check and validate your query
Once you鈥檝e 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
note important 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.
