Classic queries

Classic queries are the legacy version of the current query feature. Some customers may prefer to use classic queries.

Note: The new input/date picking experience is now available in edit mode.

The overall structure of a classic query is similar to relational table queries, contact list queries, and classic dynamic content rule builder. This feature is supported and is not scheduled to be removed shortly.

You can define three types of criteria:

Locked Criteria - Use locked criteria to define criteria for all queries against this database. This is essentially the same as editable criteria but can only be set or changed by those with organization administrator rights. The organization administrator must enable locked criteria
Editable Criteria - These criteria are based on any information in the database fields.
Behavior Criteria - These are based on specific contact behaviors, such as 'Opened Mailing' or 'Clicks.'

Create classic queries

Create and name the query.
Go to the Database Summary of the parent database to be used in your query.
In the Fields tab, click Create Query.
Enter the name of your query in the Save Query As field.
Click Browse to choose the location where the query will be saved.
Under the Type drop-down, select Classic Query.
Click OK. The Query dialog box displays for editing the new query.
To create editable field criteria, do the following.
1. Click the Criteria tab.
2. Under Criteria, select a field from the drop-down.
  The Type field changes in response to the field you select.
  - Text: Check for or compare text, such as married, yes, or baseball
  - Date: Compare one date with another
  - Number: Compare one numeral with another
3. Select the operator that defines the comparison. Only operators that are compatible with the field type are available for use.
4. In the Value field, enter the data to be checked for or compared.
  Query criteria are case-sensitive. Select or deselect the Case Sensitive field as needed.
  
  To check against another field, click the Value icon. A dialog box displays field names available for selection. The field appears in brackets and can be augmented with field operators.
  
  To compare dates, click the Date icon to choose the date. Only the date format dd/mm/yyyy is accepted.
5. Click ADD. The query criteria appear in the box under the Add Criteria button.
  Optional: Adding parentheses allows you to group criteria so they are filtered in a certain way. For example, you may group two Country fields that share associations. Note: Make sure Enable Parentheses is checked. In the first Parentheses field, add an opening parentheses "(. "After you have grouped your criteria, add a closing parenthesis at the end of the last criterion ")."
6. Follow the above steps to add additional criteria.
Create behavior criteria in the Behavior Criteria tab.
You may base your query on mailing behaviors such as Opens, Clicks, and Sent. Note: Only one mailing can be used.
1. Select include contacts who have and select one of three behaviors from the drop-down.
  - Opened an email
  - Clicked an email
  - Bounced on an email
2. If automated behavior updates are enabled on your database, select Any Email Since and select a date.
3. Select the email to use in your query.
4. (Optional) Select Include contacts who only clicked link and select a link from the drop-down.
5. (Optional) Select an activity option, its operator, and its value.
  The value type changes based on the activity selected.
(Optional) You can just select the fields to use for personalization. For example, if you want to select only contacts who live in Atlanta, choose the database field City, text = Atlanta.
This can be individual fields or all fields.
1. Click the Fields Available for Personalization tab.
2. If you have org admin rights, you may select the Permission checkbox to allow others to select or deselect personalization fields when they create a query.
3. Select the fields.
  - To make all fields available, select the Include all fields checkbox (do not click the Add button).
  - Remove the selection from the Permission checkbox to select individual fields for personalization use.
4. Select individual fields (use the Shift + Alt keys for multiple selections) and click the Add button.
5. Click Save.

When you're done constructing your query, you may either click Save or Save & Calculate to run the query.

(Optional) Select Upon completion of the data job, send an email notification to the address below and enter your email to be notified when the query calculation data job is complete.
Click Submit.
Click the Data Job ID link to view the progress of the data job. After it is complete, you can use the query as a contact source for your email.

Note: The new query is on the Queries tab for the database (with the name you selected from the steps above). The number of contacts is listed (if the number of contacts in the parent database changes, you can force a recalculation of the amount by clicking the calculator icon).

Edit classic queries

All criteria in a query can be edited after creating the query, even if an email has been done using the query. To edit, reopen the query and change the criteria.

On the Database Summary page, access the Queries tab and use the following steps:
1. Click the query name.
  The Query Summary for that query displays.
2. Click Edit Query.
  The Query Criteria form displays.
3. To edit criteria, click the Criteria tab.
4. To edit behavior criteria, click the Behavior tab and change any selection.
5. To change the fields available for personalization, click the Fields Available for Personalization tab and use the Add and Remove buttons to change the list of fields available for personalization.
Click Save and submit the query as a data Job.
The query is rerun on the database, and a new contact count is displayed.

To make the Editable Criteria section larger, we've reduced the Locked Criteria text box size, giving you more space to edit queries. Acoustic Campaign closes the Locked Criteria text box by default when you click the Criteria tab in the Query: new query window. Click the plus sign (+) to expand it. If there are no locked criteria to display, a notification shows next to the Locked Criteria icon. The plus sign is only viewable if you have permission to work with locked criteria.

Previously, the VMTA setting did not display on a database or query unless the user had VMTA permissions. Now you can see VMTA settings on a database or in the query, regardless of whether you have permission to edit the VMTA settings. If permissions are available, Acoustic Campaign enables the setting.

Set up a classic query with locked criteria

An organization administrator can set up a classic query with locked criteria if the locked query is enabled.

Select Create under Query from the Acoustic Campaign menu. The Save Query As page displays.
Select Browse and choose a database to query.
Give the query a name in the Save Query As field.
Enter Shared or Private in the Location field.
Select Classic Query from the Type drop-down menu.
Select a Query.
Click the Locked Criteria expander.
Ensure that the Enable Locked Query Functionality control is set to Yes in Organization Settings. If set to No, this option type does not display in the drop-down.
Add the criterion and click Add.
If you are adding editable criteria, click the Locked Criteria expander.
Click Save or Save and Calculate.
The Calculate Query Size Data Job page displays.
Submit the data job.
Click the data job link to view the Data Job Detail page.

Limits of classic queries and behavioral criteria

Classic queries are limited to 100 criteria. Are behavioral queries also limited to the same number?

There is a limit to the number of criteria; however, this is an Oracle database maximum and not an Acoustic Campaign product limitation. Your criteria must have fewer than 1,000 expressions totaling no more than 32,767 characters.

It is possible to create smaller behavioral queries, ensuring that the performance of calculating them is not compromised.

Make results for the "does not contain" statement the same for queries and classic queries

For queries, "does not contain" evaluates blanks or fields without a value, and results for classic queries and queries can be different.

To make them equivalent, do the following:

Example 1

I already have "does not contain" in the classic query, and I want to modify the query to match.

For classic query, use the AND [name] does not contain sunshine.
For query, use AND ([name] does not contain sunshine AND [name] is not blank).

Example 2

I already have 'does not contain' in the query and want to modify the classic query to match.

For classic query, use AND ([name] does not contain sunshine OR [name] is empty).
For query, use AND [name] does not contain sunshine.

Timestamp fields and comparisons in classic queries

Using the [Today] parameter does not work with timestamps in classic queries. You must use [Current Date].

For example:

Birthday Anniversary is equal to [Current Date]

This retrieves contacts who have birthdays today when Birthday is a Timestamp field.

Compare the query and classic query builders

The classic query builder was replaced by the query builder after the Campaign's 8.4 release.

The query builder uses the user interface based on the Acoustic Campaign program's drag-and-drop decision diamonds and includes new behaviors. This query builder does almost everything that the classic builder does but contains many extras that enable you to add exciting behavior elements to your segmentation. The UI is also more intuitive, easier to use than the classic query builder, and easier on the eyes.
The classic query builder is available to Acoustic Campaign users for segmenting data and helping marketers deliver more engaging content to their customers and prospects.

Compare the query and classic query builder

Whether you should use the query or classic query builder depends on which features you want to use for your emails. First, let's look at a comparison of both query builder types. The table below provides an overview of features each query builder may or may not support.


Feature	Query builder	Classic query builder
Field type change option for operators	No, only operators that match the field type are presented in the drop-down.	Yes, the type can be changed to get operators that don't match the field type.
Calculate	Yes, triggers the data job.	Yes, triggers the data job.
Math in value	No, but most things that required math are now covered in new operators	Yes
Has (not) Been Sent Email - mailing type	Queries all send to selected mailing template	Queries specific sent mailings
Relational table data	Yes. Acoustic Campaign does not yet offer aggregate operators.	Yes
Locked criteria	Yes	Yes
CRM opportunity data/criteria	Yes	Yes (CRM lead source only)
Create via API	Yes	Yes
Dynamic content	Yes, includes more options like 'is in query', behaviors, and relational table data	Yes
Segmenting	Yes	Yes
Purge function	Yes	Yes
Contact source	Yes, you can choose a new email query as the contact source for emails, landing pages, and sending.	Yes
Contact source for automated A/B testing	Yes	No
Contact lists	Yes, can be associated with your web forms and selected by the user or imported/added by you.	Yes
Sending to multiple queries	Yes	No
Case sensitivity toggle	No, everything is case insensitive	Yes, a setting you can toggle for each profile criteria
Behavior criteria	Yes Clicked any link in an email Opened an email Clicked a specific link in an email Been sent email Submitted a web form Has been in a program Viewed a web page Visited a web site Downloaded a file CRM opportunity	Yes Opened an email Clicked in an email Bounced an email
Is In query	Yes, but watch out for circular references. Circular references are when two queries A and B reference on another.	No
Queries created as classic queries	No	Yes
Fields for personalization	No	Yes
Export results	Yes	Yes
Current date as value option	No, but 'Is Today' operator is used for same result as classic queries' Current Date' value	Yes
Easy to use	Yes	Sufficient

Differences in results for the query and classic query builders

With the ability to create two different queries in Acoustic Campaign, you might find that even if the criteria appear to be the same, the results can be different. For example, the following are two queries with different results.

Query builder

WHEN customer_opt_in_opg is equal to Yes

OR language is equal to EN

OR country is equal to GB

OR partner_account_type is not equal to Press

Result: Count = 3612

Classic query builder

[customer_opt_in_opg] equals Yes

OR [language] equals EN

OR [country] equals GB

OR [partner_account_type] does not equal Press

Result: Count = 96

Reason for different results

The two queries resulted in different results because of the blank/null in the parent database. Even though the criteria is the same, looking for the same recipients in the database, the classic query needs to be further defined to include other values. In contrast, the query does include these other values.

Examples of criteria differences

The following table lists the differences and how both queries can populate the same results.

Classic query builder	Query builder
Date: does not equal ignores blanks. If you want them considered, you'll want to use the OR operator or ADD criteria that look for "is empty"	Date: is not equal to considers blanks. If you want them ignored, you'll want to use the OR operator or ADD criteria that look for "is not blank"
Date: is not empty ignores blanks. If you want them considered, you'll want to use the OR operator or ADD criteria that look for "is empty"	Date: is not blank considers blanks. If you want them ignored, you'll want to use the OR operator or ADD criteria that look for "is not blank"
Date: is not in list ignores blanks. If you want them considered, you'll want to use the OR operator or ADD criteria that look for "is empty"	Date: is not equal to one of the following considers blanks. If you want them ignored, you'll want to use the OR operator or ADD criteria that look for "is not blank"
Numeric: does not equal ignores blanks. If you want them considered, you'll want to use the OR operator OR ADD criteria that look for "is empty"	Numeric: is not equal to considers blanks. If you want them ignored, you'll want to use the OR operator or ADD criteria that look for "is not blank"
Numeric: is not empty ignores blanks. If you want them considered, you'll want to use the OR operator or ADD criteria that look for "is empty"	Numeric: is not blank considers blanks. If you want them ignored, you'll want to use the OR operator or ADD criteria that look for "is not blank"
Numeric: is not in list ignores blanks. If you want them considered, you'll want to use the OR operator or ADD criteria that look for "is empty"	Numeric: is not equal to one of the following considers blanks. If you want them ignored, you'll want to use the OR operator or ADD criteria that look for "is not blank"
Text: does not equal ignores blanks. If you want them considered, you'll want to use the OR operator or ADD criteria that look for "is empty"	Text: is not equal to considers blanks. If you want them ignored, you'll want to use the OR operator or ADD criteria that look for "is not blank"
Text: is not empty ignores blanks. If you want them considered, you'll want to use the OR operator or ADD criteria that look for "is empty"	Text: is not blank considers blanks. If you want them ignored, you'll want to use the OR operator or ADD criteria that look for "is not blank"
Text: is not in list ignores blanks. If you want them considered, you'll want to use the OR operator or ADD criteria that look for "is empty"	Text: is not equal to one of the following considers blanks. If you want them ignored, you'll want to use the OR operator or ADD criteria that look for "is not blank"
Text: does not contain ignores blanks. If you want them considered, you'll want to use the OR operator or ADD criteria that look for "is empty"	Text: does not contain considers blanks. If you want them ignored, you'll want to use the OR operator or ADD criteria that look for "is not blank"
Text: does not start with ignores blanks. If you want them considered, you'll want to use the or operator OR ADD criteria that look for "is empty"	Text: does not start with considers blanks. If you want them ignored, you'll want to use the OR operator or ADD criteria that look for "is not blank"
Text: does not end with ignores blanks. If you want them considered, you'll want to use the OR operator or ADD criteria that look for "is empty"	Text: does not end with considers blanks. If you want them ignored, you'll want to use the OR operator or ADD criteria that look for "is not blank"

Relative date criteria differences between classic queries and new queries

Relative date criteria in classic queries and their equivalent in new queries.

In classic queries, you can build relative date criteria using the format below:[Event Date] equals [Current Date]+5 [Event Date] equals [Current Date]-5

However, new queries use a different approach, using "is within" and "is exactly" operators.

Examples: Event Date is within the last 5 days/weeks/months Event Date is not within the last 5 days/weeks/months Event Date is within the next 5 days/weeks/months Event Date is not within the next 5 days/weeks/months Event Date is precisely 5 days ago/away Event Date is not precisely 5 days ago/away

For example, if you want to translate classic query criteria to new query criteria: Classic Query: [Event Date] equals [Current Date]+5

New query: Event Date is precisely 5 days away

To properly retrieve contacts meeting these criteria a 'minus 1' has to be applied to the [Current Date] value in the query as shown:

Here the expression'- 1' was used after the value [Current Date] since dates here are looked at numerically.

Note: The OR operator separates the criteria for today and yesterday.

Use subtraction in a classic query with a date or numeric field types

You want to get a list of contacts by subtracting from a database's date or numeric field type value.

The negative number must be outside the brackets to subtract from a date field type within a classic query.

For example, this is how to set update subtraction query criteria: [PurchaseDate] greater than [Last Modified Date]-5.

The same can be done for numeric field types. For example, [PurchaseAmount] greater than [PreviousPurchaseAmount]-2

Classic queries and case sensitivity

Classic queries are case-sensitive when using the 'equals' operator. Case sensitivity must be unchecked at the element level to bypass the case sensitivity default value.

When creating a classic query, the results are different when searching for contacts by email, name, or anything with text in it using the 'equals' operator. However, the new query gives the correct count.

Why? Classic queries are case-sensitive by default when using the 'equals' operator. The option needs to be unchecked on the individual item.

Resolving the problem

Go to View under Queries.
Place a checkmark next to the name of the Query.
Click Edit Query.
Click the Edit icon.
Uncheck Case Sensitive, then click Apply Changes.
Click Save and Calculate.

Note: Unchecking the Case Sensitive box for the entire query does not work.