Create a relational table query
Relational table queries are used to add relational table markup in emails.
A relational table query must be built before the markup HTML can be inserted into an email.
- Create and name the query.
- Go to Relational Tables.
- Select the relational table that you want to use.
- Click Create Table Query.
- Enter the name of your query in the Save Query As field.
- Click Browse to choose where to save the query.
- Click OK.
The Query dialog box displays for editing the new query.
- Define your query data.
- (Optional) Select the fields that can be used for personalization. Either individual or all fields in the relational table can be selected for personalization. You can also add or remove fields later by editing the query.
- When you are done constructing your query, you can either click Save or Save & Calculate to run the query.
- To save and calculate the query, insert relational table markup into your email template.
- (Optional) Select the fields that can be used for personalization. Either individual or all fields in the Relational Table can be selected for personalization. You can also add or remove fields later by editing the query.
- Click the Fields Available for Personalization tab.
- If you have Org Admin rights you may select the Permission check box to allow others to select or de-select Personalization fields when creating a query.
- Select the fields.
To make all fields available, select the Include all fields check box (do not click the Add button).
To select individual fields for personalization use, remove the selection from the Permission check box.
- Select individual fields (use the Shift + Alt keys for multiple selections) and click the Add button.
- Click Save.
- When you’re done constructing your query, you can click Save or click Save and Calculate to run the query. If you run the query, take the following steps.
- Review the query summary information.
If you want to revise your query, click the Query Summary breadcrumb.
- (Optional) Check 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 the Submit button.
- Click the Data Job ID link to view the data job’s progress.
when the data job is complete, you find the number of contacts in your relational table.
- Review the query summary information.
- Insert relational table markup into your email template.
- Create a new template or go to an existing one.
- Click Settings > Contacts to assign a contact source to the email template.
The contact source must have an association with the relational table where you built your query.
- Click Insert Relational Table Data in the top toolbar.
- Check the box next to your relational table query and click Select.
- Click Insert Markup to add lookup code, a basic HTML table, and personalization fields from your relational table. Edit the relational table markup as needed.
- Click Submit.
Note If you use a relational table query to retrieve a URL value for use as a link in an email, the entire anchor
<a> element for your link must be created using a Dynamic Link Leader (DLL). You cannot reference the
href value for your link, for example:
href="%%MY_URL_VALUE%%". If you do, Acoustic Campaign will rebuild and redirect the link with tracking information, and the resulting link will be incorrect in the actual email that is sent. If you format the
href value in your link as
href="%%MY_URL_VALUE%%" and use the Personalization function to test, the correct URL value is pulled in from the relational table but you receive a false positive where it appears to work correctly. However, after the email is sent, the link is incorrect.
Include relational table fields in a database query
Note: Relational table expressions in both database and program queries are only available to organizations that have relational tables enabled.
To include relational table fields in a database query, you must Associate the relational table with the database. Databases that have relational tables associated with them allow you to include relational table fields in your database and program queries.
In order to Associate a relational table with a database, you must first have at least one corresponding field in both the relational table and the database that contains the same data. This could be a customer ID or purchase order number.
Create a query using standard or custom zip code relational table
You can create a query using the standard or custom zip code relational table using query.
- From the Data menu on the main toolbar, select Create under Queries.
This opens a Save Query As dialog box where you select which database to query, as well as a name for your query. Make sure you select Query from the Type drop-down.
- Click Relational Table and choose a relational table from your Shared or Private folder and then click Done.
- Select Standard Zip Codes or Custom Zip Codes and then click Done.
- Save your query.
Rename a relational table query
Campaign allows users to change the name of a relational table query (not a database query). However, there is a risk comes with this name change.
The main purpose of a relational table query, is to insert relational table data into a email, if the contact source has been associated to the relational table. When relational table data has been inserted into the email, the source code of the relational table data reference is using relational table query name.
For example, here is the source code of relational table data in email:
%%RT_LOOKUP_BEGIN query='/Private/Order Information order_by='' max_rows='10' %% %%RT_HEADER_BEGIN%% <table> <tr> <td>Order Number</td> <td>Price</td></tr> %%RT_HEADER_END%% %%RT_ROW_BEGIN%% <tr> <td>%%Order Number%%</td> <td>%%Price%%</td></tr> %%RT_ROW_END%% %%RT_FOOTER_BEGIN%% <tr><td colspan='2'></td></tr> </table> %%RT_FOOTER_END%% %%RT_LOOKUP_END%%
Note: the above example is using relational table query named 'Order Information'. If you change the relational table query name 'Order Information' to something else, this email will fail if it is sent.
Capture specific dates in relational table queries
Relational table queries can be used to harvest data to populate emails. Relational table queries are universally based on the classic query structure, and as such lack some of the tools and functionality new queries offer.
One such criteria is the ability to capture specific dates, such as yesterday.
A workaround criteria can be used to capture such date metrics. You can use the Is Between operator, as follows:
[DATE_FIELD] is between [Current Date] -1 and [Current Date] -1
This captures contacts that have a date field with a value 1 day behind the current date.