A relational table is a table of columns or fields that describe a listing (or rows) of data. For example, a relational table may contain fields such as customer ID, transaction number, product purchased, product price, sale date, and purchase location. This data can be used to personalize the SMS messages you create with tags.

Note:  
SMS mailings sent with relational table personalization will take longer to send than SMS mailings that do not include relational table personalization. 

Things to remember

• You must have the following items enabled for SMS:
  • Your organization
  • Your user account
• A relational table with personalization data is mapped to your SMS-enabled database
• The following items are not supported:
  • Child relational tables
  • Link shortening and tracking if the link is inserted from a relational table
  • SMS transactional APIs (External Consent and Sent to Input Contacts)

How to begin using your 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. Scroll down to the Associated Relational Tables section, select the relational table you associated then select Create Table Query.
3. Select your chosen 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 > 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 row of data for a contact, use the OrderBy clause.

Format the personalization tags

The general format for a personalization tag is %%RT::path to query::field name%%.

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 5relational 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 allows you to control the display order of records returned by the relational table query. 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. This gives more structure and order to the records returned. It also places emphasis on the records you deem more important (as evidenced by the next parameter). 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.