A relational table is a table of columns or fields that describe a listing (or rows) of data, similar to a Campaign database. What makes this feature different is that it lets you expand the contact data potential by mapping many databases to the same relational table. As a result, you can leverage the one-to-many relationship from your contact data, such as assign multiple offers or products to one contact and associate this information quickly.

Note: SMS relational table data personalization doesn't support child relational tables, link shortening, and tracking for links inserted from a relational table, and SMS transactional APIs (external consent and sent to input contacts).

Before you begin

Enable the following items for SMS:

• Your organization
• Your user account
• A relational table with personalization data mapped to your SMS-enabled database

Note: This method of personalization doesn't iterate the relational table itself. It only extracts a value from a specific table field and copies it to the content of the SMS.

Enable relational table data

1. Go to Data > Databases and associate the relational table you plan to use for personalization data to the SMS-enabled database. You may need to create or import a new relational table first.
2. In the Associated relational tables section, select the relational table you associated, then select Create table query.
3. Select your criteria for the table that you want queried. For example, if you want to target gold-level customers in your message, you may enter Member status in your table field, is equal to as your operator, and Gold as your value.
4. Select Save & calculate and then Submit when you are done. Allow your query to complete; the run time will depend on the size and complexity of your query.
5. You can now go to your SMS message drafts and personalize an existing or new message by using the relational table personalization tags. Go to SMS and group messages > SMS drafts and either select the draft you'd like to edit or New draft to create a new message.
6. If your relational table query will return more than one record for a contact, use the OrderBy clause. SMS only does single replace (it doesn’t iterate the relational table). When applying the OrderBy clause, it will use the first value of the multiple ones that could be returned.

Note: SMS sent with relational table personalization will take longer to send than the ones that don't include this type of personalization.

Format personalization tags

The general format for this placeholder is %%RT::path to query::field name%%. You can find additional examples below, but first, review the rules to ensure that you are formatting the tag correctly and placing the tag in the correct fields.

See the sample uses below for more examples.

General example

If:

• Relational table location = Shared
• Relational table query name = Gold9Sept19
• Field name = Points

Then:

 %%RT::/Shared/Gold9Sept19::Points%%

General formatting rules

• %%RT denotes relational table personalization. The RT is case-sensitive.
• Specify the full path of the relational table query. /Shared and /Private are not case-sensitive.
• MM, dd, yy are case sensitive when formatting dates.

%%RT::/Shared/BirthdayQuery:::format(Birthday,“MM/dd/yy”)%%Date is displayed as '10/31/80'.

%%RT::/Shared/BirthdayQuery:::format(Birthday,“yyyy”)%%Date is displayed as '1980'.

%%RT::/Shared/BirthdayQuery:::format(Birthday,“dd-MM-yyyy”)%%Date is displayed as '31-10-1980'. MM, dd, yy are case sensitive.

Sample uses

Personalization referencing a single relational table

SMS message draft:

Here's a special offer just for you! All %%RT::/Shared/All_Mobile_Users::Favorite_Team%% gear is on sale, plus get free shipping when you order through the app!

In this example, All_Mobile_Users is the relational table query to be used and Favorite_Team is the name of one of the columns in the corresponding relational table.

Result message: Here's a special offer just for you! All Broncos gear is on sale, plus get free shipping when you order through the app!

Personalization referencing multiple relational tables

You can add up to 5 relational table personalization tags per message.

SMS message draft with 2 personalization tags:

As a %%RT::/Shared/All_Mobile_Users::Member_Level%% member, check out all the latest rewards! Plus,use promo code %%RT::/Shared/All_Parents_New::Member_Code%% on your next reservation.

Result message: As a Goldmember, check out all the latest rewards! Plus,use promo code GOLD2017 on your next reservation.

Relational table personalization with other personalization types

Relational table personalization can be used with regular (Database) personalization in the same field.

SMS message draft:

Hi %%First Name%%, still interested in seeing %%RT::/Shared/All_Mobile_Users::Last_Country_Page%%? Visit us online for a special offer.

Result message: Hi Paul, still interested in seeing Italy? Visit us online for a special offer.

Relational table personalization with OrderBy clause

The OrderBy clause lets you control the display order of records returned by the relational table query. This gives order to the records returned and places emphasis on the ones you deem more important. For example, if you pulled the recipient's last purchases into the template, you could state that you want them to be sorted by the "Purchase Date" field. Otherwise, records would be returned in the order that they appear in the relational table. Insert asc after your OrderBy field to sort in ascending order or desc after your OrderBy field to sort in descending order. You can use multiple OrderBy fields as long as they're separated by a comma.

SMS message draft:

You saved $%%RT::/Shared/All_Services_New::Savings::PurchaseDate desc%% on your last order! Visit our website to redeem more valuable offers.

Result message: You saved $10 off your last order! Visit our website to redeem more valuable offers.

Specify date formatting

You can display a date in a specific format in your SMS messages. For example, you might want to format the date differently depending on your contact’s country instead of how the date was entered into the relational table field. In the following situation, your relational table contains a date field called Birthday that contains the value “10/31/1980” for a contact. You decide to query the relational table and name your query BirthdayQuery.

Keep in mind that MM, dd, yy are case-sensitive.

Example 1 SMS message draft

%%RT::/Shared/BirthdayQuery:::format(Birthday,“MM/dd/yy”)%% was a special day. Happy birthday, %%First Name%%! Visit us online for a special offer on your special day.

Example 1 Result message: 10/31/80 was a special day. Happy birthday, Kelly! Visit us online for a special offer on your special day.

Example 2 SMS message draft

%%RT::/Shared/BirthdayQuery:::format(Birthday,“yyyy”)%% is a good year. Cheers, %%First Name%%! Visit us online for a special offer for your special day.

Example 2 Result message: 1980 is a good year. Cheers, Kelly! Visit us online for a special offer for your special day.

Example 3 SMS message draft

%%RT::/Shared/BirthdayQuery:::format(Birthday,“dd-MM-yyyy”)%% was a special day. Happy birthday, %%First Name%%! Visit us online for a special offer for your special day.

Example 3 Result message: 31-10-1980 was a special day. Happy birthday, Kelly! Visit us online for a special offer for your special day.