Query Building Concepts

Queries in ActiveDEMAND

Queries in ActiveDEMAND exist in the following places:

Contact Lists
Workflow Decisions
Dynamic Lead Scores
Campaign Goals

Queries work the same way in each location, with only the context of the query changing. The context of the query can be global, campaign, or workflow.

Global Query Context

The global query context will look over all records in the database, over all time. The only limits to the result set will be defined by the query itself. The global query may be looking at contacts, or at contact history items.

Contact Lists and Dynamic Lead Scores always run in the global query context. Workflow Decisions have the option of running in this query context.

Campaign Query Context

The campaign query context will only return results related to a specific campaign. This context will only look at contact history items that occurred within the specific campaign.

Workflow Decisions have an option of running in the campaign query context, if the workflow is part of a campaign. This is the only place this query context is used.

Workflow (Single Campaign Step) Query Context

The workflow query context is running the query in the context of a specific contact history item. It is checking that the specified contact history item is included within the query result set.

Campaign Goals always use the workflow query context. They are always running their queries within the context of a single contact history item. Workflow Decisions have an option of running in the workflow query context.

Query Structure

A query is structured into 3 levels; blocks, groups, and filters. The structure you use is very important to the result of the query as it determines how the results are joined together.


Blocks
Each block is executed as a separate independent query and the result sets are joined together using the operator you specify. If you create a query that says Block A AND NOT Block B, the final result set will be all results from Block A, that are not part of Block B.


Groups
Groups work exactly the same as blocks. Each group is run as a separate query, and the results of those queries are combined together with the operator specified. Use different groups or blocks when you want the filters to be checked independently.


Filters
All filters within a specific group are executed together as a single query. This means for the query to return results, each result must match all filters as a group. Place all filters within a single block when you want them to be checked together, and be dependent on each other.


Examples
All contacts that were tagged with the tag 'Customer' in the last month

This query has 3 filters but they are all dependent on each other. They are looking at a single record that matches all 3 criteria.

Block A

Group 1

Tag created at is greater than or equal to Last month start date

Tag created at is less than or equal to last month end date

Tag name is equal to 'Customer'

All contacts that are new sales ready leads in the last month

This query as 2 sets of filters that are dependent, but the 2 sets are independent of each other. The query is asking for all contacts that have a lead notification record that matches the first 2 queries, and does not have a lead notification that matches the second 3 queries.

Block A

Group 1

Lead notification created at is greater than or equal to Last month start date

Lead notification created at is less than or equal to last month end date

Lead notification type is equal to EmailedToSales

AND NOT

Group 2

Lead notification created at is less than last month start date

Lead notification type is equal to EmailedToSales

All contacts with the tag 'Potential Customer' who have an address in North America and who have opted in, or contacts who have the 'Customer' tag

In this query, all 4 filters are independent of each other, therefore they are all placed into separate groups.

Block A

Group 1

Tag name is equal to 'Potential Customer'

AND

Group 2

Contact address country is one of Canada, United States

AND

Group 3

Opted in name exists

OR

Block B

Group 1

Tag name is equal to 'Customer'

Date Math

NOTE: Date Math is no longer required. All date/time queries have UI support.

Date Math is used to check the time of execution of a query. There are two important dynamic fields used in Date Math

%EXECUTE_TODAY_DATE%

%EXECUTE_TODAY_DATETIME%


For the Dynamic Field you can do math using the following syntax:


[date System Defined Field] [+-] [digits].[minutes|hours|days|months|years]



For example:

%EXECUTE_TODAY_DATE%+1.day

or

%EXECUTE_TODAY_DATETIME%+2.hours

Updated on: 01/10/2022