List Query Tool

Queries allow you to search a list, or multiple lists at once - including standard suppression lists. You can search deployment results as well as list fields. If you are an Administrator of your group or Client, you can search the activities of specific users.

You can preview the results, download them as a CSV file, or save them back to Email Manager as a new list.

Under Select List, click on a list and select the icon. This will display the Query List window.

Selecting Lists to Query

You can add additional lists to your query by clicking the Selected Lists > Change button. You can query multiple lists from different categories, but only if they have got the same primary key column. In the example below, the list Website Registration is not available to select because it has a different primary key. It is recommended best practice for the same primary key to be used throughout your data.

Click Done when you have selected the fields that you wish to query.

Your selected lists will be displayed.

Entering Query Criteria

You can enter two types of criteria - List Field Criteria and Event Criteria - both of which enable you to isolate the records that you want by manipulating a variety of criteria.

List Field Criteria

Click the Change button, which will open a new window where you can enter criteria based on the columns in the selected list. Tick the enable field criteria check box to activate the functionality.

To add more sophisticated criteria, use the drop down menu to select the AND OR operators. Date and Email address fields have additional criteria - 'Email' fields include User, Domain and Health options and 'Date' fields have Year, Month, Day,Time, and a Days from Now option for setting up date relative queries.

In the example below we are selecting people who use 'Acmeco' as their email service provider, and who were added to teh database after January. Click in the grey box to add parenthesis. Right click to remove them.

Click Done and then Execute Query to display the results - in this case the seven records that meet the criteria.

Event Criteria

To enter Event Criteria, click the Change button, which will open a new window where you can enter criteria based on the past deployment of messages. This is useful for building up contact history, calculating bounce rates over time etc. Again, you will need to check the 'enable event criteria' check box to enable the functionality.

The first step is to select the Creatives and users that you would like to include in your query. In the following example it is all Creatives in the category 'Mal' that have been deployed by 'Brian Chen'.

The next step is to enter the actual event criteria. You can build criteria using items like Message Sent; Message Open; Hard Bounce; Soft Bounce, or the Any events such as Any Bounce. As above, you can use the AND OR operators to build up more complex criteria. You should note the criteria here are based on counts of how often an event happened. For example, if you simply wanted a list of people that had opened a particular message, you would enter 'Message Open greater than 0'.

In the example below we want a list of people who opened messages, or that we received auto replies from, in the specified date range.

Click Done, which takes you back to the main query list screen, now showing a summary of the criteria that we have entered. Click Execute Query to run the query.

Saving Queries

At the bottom of the window you will see two buttons - Save Query and Load Query. These allow you to save the criteria logic used when querying a list. This can then be used at a later stage when creating similar queries.

Save to List - Enables you to create a new list, or lists, from the results of your query. Give the new list a Name, select a Category and include an optional Description. You have also got the option to save the list as a Smartlist. Lists that are saved as SmartLists will be highlighted yellow. The check box "how in recipient preferences page. (allow opt-in), should be checked if you want recipients on this list to be able to opt-out of the list. If you want recipients on other lists to be able to opt-in to this list, select the second check-box Show for recipients from any list (allow opt-in).

What to do with Query Results?

Save to List

Enables you to create a new list, or lists, from the results of your query.

Give the new list a Name, select a Category and include an optional Description.

You have also got the option to save the list as a Smartlist. Lists that are saved as SmartLists will be highlighted yellow. The check box "Show in recipient preferences page. (allow opt-in)", should be checked if you want recipients on this list to be able to opt-out of the list. If you want recipients on other lists to be able to opt-in to this list, select the second check-box "Show for recipients from any list (allow opt-in)".

Split Query Results

This screen gives you a number of options.

You can create a new list with all or some of the columns from your original list.

You can split the list into two or more lists, specifying how many records will be in each one by entering a number under Record Count.

The Randomize check box allows you to create a new list with a random sample of records.

The Exclusive check box allows you to create mutually exclusive lists. If you do not want mutually exclusive lists, uncheck Exclusive and enter an Offset into the Offset column.

The Offset allows you to take recipients from a particular point in the list - like the top or bottom - or build lists that overlap. The number that you enter will be the row number that the records will start from.

Download Results

Allows you to download the results of your query as a comma-delimited (.csv) or text file (.txt).

Edit Row

Selecting a recipient row and clicking the icon will display the Edit Row screen where you can edit recipient field values and recipient preferences.

To edit a value, click into the value field and edit or overwrite the existing text. Note that you are editing the recipient record in the database and not just for the current list. The edited value will appear in other lists too.

We recommend that you read the List Management Troubleshooting section before editing recipient values.

Delete Row

Selecting a recipient row and clicking the icon will delete the recipient from the list, but not from the database.

The recipient will still appear in other lists that they are included in.

Querying preferences

Click the Change button, which will open a new window.

Tick the 'enable preferences criteria' check box to activate the functionality.

Unlike the List Query functionality this does not narrow the possible selections to only include those found in the lists selected.

You select the lists of interest and you query a separate table that holds the Preferences for each recipient.

Your options are:

True = Currently opted into this Preference

False = Currently opted out of this Preference

Not Set = Neither opted in or opted out

To add more sophisticated criteria, use the drop down menu to select the AND OR operators where you can query multiple preferences.

If you utilize Preferences the first step of any campaign will be to understand which customers can be emailed. This will be led by querying the correct Preference to form an audience.

To make this an efficient process create a list folder called Preferences. Then create SmartLists for all your preferences created around opt ins to your core lists. These can form the standard of your ongoing campaigning and provides you with a view on how many recipients are currently opted into each Preference.

• Introduction
• Adding a New List
• Suppression Lists
• Editing a List
• List Query Tool
• Smart Lists
• Unsubscribe and User Preferences
• Troubleshooting Lists