HOW TO: Manage filtering data in Campaign Enterprise

Making an email message as personal to subscribers as possible in a mass email marketing campaign involves the ability to effectively use your database to send the right message at the right time to the right people. A key part of this is the ability to segment your email subscriber list by filtering data that will target specific subscribers on your email list.

To manage this type of filtering in Campaign Enterprise, open the campaign you need to modify and select the Data Source tab. Under this tab, if the Feature Set Selector is set to Basic, no filtering options will be visible. You'll need to change the features to Intermediate or Advanced to see the two filtering options.

Intermediate filtering
The Intermediate filter contains six columns, and five rows. Select the records in the first column using the specified database table. The fields selected in the first column need to equal some value. In this example

WHERE bounce = 0

There are no additional values specified in the rows across, so only records that have not previously bounced are included in the record set.

Each row can be thought of as an AND parameter of a select statement.

WHERE bounce = 0
AND
WHERE unsubscribe = 0

Now only records that have not previously bounced or have not submitted an unsubscribe request are included in the record set.

The values going across from left to right can be thought of as OR parameters of a select statement.

WHERE salesrep LIKE Stan OR LIKE Beth

In this case, the records where the sales rep are either Stan or Beth and that have not bounced or submitted an unsubscribe request are included in the record set. Any other records associated with sales reps other than Stan or Beth will not appear. Depending on the type of database, wildcards like * or % may need to be used when filtering text data. For example:

*Stan* or %Beth%

To verify that the correct records are selected, click the Preview button.

Advanced filtering
The Advanced filter is much more flexible and allows full SQL (structured query language) select statements. To use the full SQL select statement option, first make sure you have chosen Advanced in the feature set selector. When using a select statement, the table or tables from which data is pulled need to be specified in the statement. For example:

SELECT * FROM dbo.tablename

The * sign indicates all the fields in the table. All fields are included in the record set, and any field is available as a merge field in the email message.

To exclude tables, use the following format:

SELECT(ID),(email),(firstname),(lastname),(bounce),(unsubscribe),(salesrep) FROM dbo.tablename

Only those fields specified are included in the source record set, and only those fields are available as merge fields in the email message. The ID and the email address field must be included. A field not listed can still be used as a filter, but it is harder to determine what is going on if it is not included in the output.

SELECT (ID),(email), FROM dbo.tablename WHERE bounce = 0

After selecting the fields, the data can be filtered in the same way as shown above. Here is the select statement representation of the simple filter example.

SELECT * FROM dbo.tablename WHERE bounce = 0 AND WHERE unsubscribe = 0 AND WHERE salesrep LIKE %Stan% OR LIKE %Beth%

To verify the correct records are selected, click the Preview button.

Complex queries
Another filter option for email campaigns is to connect to a pre-filtered query or view that resides on the database. This is the same thing as the advanced select statement, but it is placed on the database rather than Campaign Enterprise. This option is highly recommended for extremely complex queries or views, since such functions are best handled on the database itself. The query or view is selected in the Table/Query drop down, if the database is properly configured and accessible through the database connection in use. The simple or advanced filtering options can further apply to a query or view, but it is best to manage everything on the database when using this option.

Write back table selection
When using the Advanced filtering option there must be a write back table selected. Campaign Enterprise cannot write back to the source record set, it is temporary. The write back table works best if it is the source table is specified in the view itself. If there is a join, the write back table needs to contain the email address fields and the ID used in the select statement, otherwise writing back using the advanced features will not work correctly. This also applies when connecting to a view or query on the database, most of the time those are also temporary and are not updateable.

The more data collected, the more versatile the database becomes. Use the filtering available in Campaign Enterprise to bring your email campaigns to their full potential by delivering highly targeted, personalized email messaging that meets individual customer needs. -- Arial Software

Return to Tech Tips