Create a query
- In Data, go to Queries > Create or New > Query.
- Complete the Save Query As dialog box.
- Choose Database to Query - Select the database you want to query.
- Save Query As - Name the query
- Location - Save the query in a Shared or a Private folder.
- Type - Select Query or Classic Query.
- Click OK to open the query builder.
- Click an option to add your criteria:
- Profile (from Data Fields, Contact List)
- Behavior (based on behaviors performed in email, web form, program, universal behaviors, website)
- Relational Table
- Criteria (from Data Fields, Contact List)
- Behavior Criteria (based on a contact who opened an email, clicked a link in an email or bounced on an email)
- Fields Available for Personalization (allows you to include all or some fields available for personalization within emails to this query).
- Create a query based on an option selected in Step 4.
- Click one of the Save options (Save, Save & Close, Save & Calculate).
Capitalization matters in classic queries.
For example, if there is a city field in your database that contains atlanta for one entry and Atlanta for another entry and you want to capture ALL recipients who reside in Atlanta, then you have to construct your query in the following manner:
Criteria: City equals atlanta OR City equals Atlanta
This ensures that ALL recipients who reside in Atlanta/atlanta are returned by the query.
Another option is to use the 'case sensitive' box.
Clearing this box allows you to search for 'Atlanta' and return results with both 'Atlanta' and 'atlanta' in the field.
The new query type is NOT case-sensitive.
Edit queries
Edit your query by making modifications to the rules.
- In Data, go to Queries.
- Click the query you want to edit. The Query Summary displays.
- Click the Edit Query button to enter the query editor window.
- Hover over the criteria you want to change and select the pencil icon. Make the modification(s) and select Done.
To delete a criterion, hover over the criterion you want to delete and select the red X.
- Repeat Step 4 for each line of criteria you want to change.
- Select one of the Save options at the bottom of the editor window to complete the edit.
- Save - Saves the logic of the query.
- Save & Close - Saves the logic of the query and closes out the editor window.
- Save & Calculate - Saves the logic of the query, closes the editor window, and calculates the criteria of the query.
- Close - Cancels the edits.
- To add additional criteria to a query, follow steps 1-3 and then select the criteria from the Add Criteria area.
Create a query using the CRM Contact Type field
When you view the Edit Contact page for a particular contact in your database, you see values of Lead or Contact for the CRM Contact Type field.
To create a query based on these values, use 1 for Leads and 2 for Contacts.
Please reference the "CRM" feature database field usage article for more details.
Change the name of a query
You can change the name of an existing query.
- In Data, go to Queries.
- Select the Queries tab.
- Select the check box next to the query you want to change.
- Click Rename.
- Type the new name of your query and click OK. Note: The name change will be reflected if the query is being used with any other query via the is in query criteria.
Changing a link name affects query results
Changing a link name can affect the query results because Acoustic Campaign cannot find the original link in the search.
When you change the name of the link in the template, Acoustic Campaign sees this as a new link, not the original link you sent. This changes your query numbers because now those contacts have not clicked this new link. To fix this, you can change the link name back to its original name.
- Navigate to Single Mailing Report.
- Click the correct sent mailing.
- Change the drop-down box from Summary to Clickthroughs.
- Click Update.
- Copy the link name from the list of hyperlinks in the email.
- Go back to the template and edit the hyperlink name, pasting in the original link name.
- Edit your query to point to the correct link name.
- Re-run the query.
This should return the correct number of recipients that have or have not clicked the link within the time frame you provided in the query.
Copy a query
You can use the Create Like feature to make a copy of a query.
- In Data, go to Queries.
- Check the check box next to the query to be copied.
- Select Create Like and give the new query a name.
- All the query criteria are copied over into the new query.
Delete a query
If you delete a query, you also delete associated items, including templates and reports.
If you want to keep your templates, you must associate them with a different contact source. You can check the Templates tab to see the list of templates associated with this query and if you want to keep these templates, associate them with a different data source.
- In Data, select the query that needs to be deleted.
- Click Delete and click OK in the confirmation dialog.
The following message displays: You are about to submit a delete contact source data job that will run in the background.
- Review the job details and choose whether you want to receive an email notification once the job is complete.
- Click Submit to submit the data job. A data job runs in the background.
Note: You also won’t be able to delete any queries in private folders unless you're the owner. You would need to ask the other user or Become that user to delete those queries.
Export a query
You can export the contacts in a query for use in other applications.
- In Data, go to Queries.
- Click the name of the query that you want to export.
- Click Export.
- Select the contacts and fields that you want to export and click Next.
- Select if you want to be notified via email when the export is complete and click Submit.
- Go to Export Files and select the checkbox next to your file.
- Select Download and enter your Acoustic Campaign password.
- Select Authenticate.
Note: The first 100 contacts of a database, contact list, or query can be viewed in the Campaign interface with no exporting necessary.
Operators and blanks in queries
You can construct queries, rules, and programs that either consider or ignore blanks.
Example 1 - ignore blanks
You can ignore blanks when the operator is not equal to one of the following in the query builder, rulebuilder for dynamic content, and programs actions.
In addition to the criteria added to create the rule Birthday is not equal to one of the following, to ignore blanks you must add another operator that looks for is not blank.
- Select Birthday from the Data Field drop-down list.
- Select is not blank from the Operator drop-down list.
- Make sure the rules are connected with AND. (AND is the default setting.)
- Click Done.
Example 2
The screen below shows that the criteria for this example is Birthday and the Type is Date. In addition to the criteria added to create the rule [Birthday] is not in list, to consider blanks you must add another operator that looks for is empty.
- Select is empty from the Operator drop-down list and click ADD.
- Then, connect the rules with OR.
Blank or null values in queries and classic queries
Null and blank values are handled differently in classic queries and queries, which causes queries with the same criteria to return different results.
The classic query builder and the query builder treat empty fields differently. To illustrate, let's include a field in the database called Product
, and have three contacts with values: product a, product b, and [nothing].
For Acoustic Campaign users that are familiar with the classic query builder, you know that the following query returns contacts that have a value in the product field that is not "product a". That means only the contact with product b qualifies.
With the query builder, this is not the same. The query below, which looks just like the query above, returns all contacts that don't have "product a", including blanks. That means that both the contact with product b and the [nothing] contact qualify
This is important to know when building queries against databases and within programs. After you decide what query builder you're using, and if you want to include blanks or not include blanks, now you can make sure you build a query that returns the results you want.
Create a query for AOL email addresses
AOL domains are listed below.
- @aol.com
- @aim.com
- @netscape.net
- @cs.com
- @email.com
- @aol.co.uk
- @usa.com
- @wmconnect.com
- @mail.com
Classic Query
Enter one domain, then click Add. Then enter the next domain.
Query
Under "Add Criteria" - Profile tab - Data Fields, choose Email > Operator, and choose Contains one of the following (One item per line).
Find queries that were used in A/B tests
In the queries area, look in the Shared or Private folder (depending on where the email was selected from) and find the folder named the same as the email used for the A/B test. You see all queries used to send the A/B test and any remainder queries. The A/B test query folder is not created until the winner is determined.
Does changing query criteria after an email is scheduled, but before it is sent allow the email to use the updated query criteria?
It would ultimately depend on what is set for the pre-processing time when the email is scheduled.
When you schedule an email to a query, Acoustic Campaign locks in a query ID and a pre-processing time. If an email is scheduled for noon and Acoustic Campaign begins processing the query at 10 AM, any changes to the query criteria before 10 AM are applied to the email.
However, if you make any changes to the query criteria after 10 AM, those changes are not applied to the email, as Acoustic Campaign already took a snapshot of the contacts to whom the email is sent due to the 2-hour pre-processing time selection.