Adding a Database Query

Database Queries allow you to request and retrieve information from a specific database. Recipient queries retrieve the list of email addresses that you want to mail. Context queries retrieve structured data that may be used during the template merge process. The SQL database queries and are saved in Accelerator locally.

From the Database Queries list page, you can:

• click column headers to re-sort the summary table.
• enter text to search across the query or database connection names.
• click the Show Archived checkbox to view all queries.

You can also duplicate, archive, edit, or delete any database query.

After clicking the Add Query button from the Database Queries list page, you can enter and name the query, select the database connection and related objects, indicate the recipient or context data type, and add criteria to further segment the resulting data.

Tip: If you need to create a variation rather than change an existing query, select the appropriate query, then select Action > Duplicate to apply changes to a copy of the query.

A Query Name, Database Connection, and SQL Query are required for each database query. All other fields are optional. To add a database query:

1. Enter a unique and meaningful Query Name.
2. Select the Database Connection from the drop-down list. You can drill down further to a specific data location by selecting a system object from the right viewing window.
3. Select the appropriate Data Type.
4. Enter the SQL Query.
5. (optional) SQL Query Segmentation Criteria: click Add Criteria to define conditions and further segment your results.
• Name is the Freemarker substitution.
• Label is the natural name.
• Default Value is the information that populates if no custom data is present.
• Help Text is an expanded description that displays as the cursor hovers.
6. Click Save.

Any columns returned in the result set of the query (the “select” portion) are automatically available for use during personalization in templates referencing the query. These fields are referenced by the label given in the query, so column aliasing may be used to provide more useful labels to attributes. One field, required in each Recipient query, is EmailAddress. The EmailAddress field designates the recipient’s email address that will be used in the “To” portion of the outgoing email.

A second, optional field, is RecipientID. The RecipientID field is intended to be used as a unique key managed by your internal system, that can be used to identify items related to this recipient, such as clicks and opens. This is a freeform test field that will be returned with each activity associated with this recipient.

Only values returned within the result set of the query will be shared with our cloud component, allowing for sensitive values to be used for segmentation, with exposing that information outside of your internal database. For example, consider a case where you want to generate an audience containing only recipients with a credit score above a specific measure, but consider the credit score value too sensitive to share with third party systems. In this case, you may reference the credit score value within your database query in Accelerator, as part of the “where” clause, but not a part of the result set (“select” clause). In this scenario, the credit score value is used to determine who qualifies for the audience, but the actual credit score value is never referenced outside of the audience query itself.