You can integrate your business-specific data with Emarsys to extend personalization capabilities. Relational Data enables you to boost your contacts’ multi-channel personalized experience and improve customers’ engagement using business-specific data.
This article provides you information for creating personalized email campaigns to target your customers with one-to-one personalized content based on predefined business rules.
You can see the process how to connect your contact to your Relational Data content and how to use Relational Data features, such as setting reference fields, sorting fields, default value handling for personalization. We describe you a real-life use case and walk you through the workflow of using Emarsys Relational Data, Personalization in Email campaigns.
1. Use case example
An airline company wants to send special upsell offers with recommendations in personalized email campaigns to their contacts according to the destination city and date of arrival. The upsell email campaign is sent with personalized offers for services to buy, such as renting cars or booking hotels at discount prices in the destination city, before the date of arrival.
Example for upsell email content:
Based on the contact information we determine to which city and at what time the contact is traveling to, furthermore which service is available for him at the best price in the local currency, for the proper service duration at that city, e.g.:
We have seen that you will travel to Vienna on the
Did you know that you can buy the following services at a discount price?
<company_name> <price> <currency> <service_duration>
2. Setting up your database
Relational databases store data in tables, which are made up of named columns and rows of data. The rows are also called relations, hence their name. For our use case example, you have your customers’ bookings data in your bookings table (Columns:
destination_city). The upsell services can be obtained from a view called upsell_services. (Columns:
service_type, service_name, company_name, city, price, currency, price_duration, availability). You also store information about your contact in the built-in Emarsys contact database. With Relational Data Service Emarsys can connect to your own (external) databases (which needs to be pre-configured), for details, click here.
In order to use a table or view for personalization, you must select which fields will be used as reference fields. Each table or view can only be used with one set of reference fields (used together). Relational Data uses reference fields as a key to populate content into placeholders in your messages. Typically, reference fields contain information that can be tied to a contact and it is also available in the Emarsys platform.
For segmentation the reference field is not required, but If you want to use your data for personalization, you need to set at least one reference field (in our example these are marked with red color).
Reference fields are the means by which you determine which records are made available for personalization. To use the reference fields you have to specify how the personalization service should use them during token creation.
When you connect an external database to Emarsys, we will automatically pull all the tables and views from the database and display them on the Relational Data page, Tables & Views tab.
Tables & views are displayed only, from the schema specified in the
CurrentSchema connection parameter. If it's omitted, the default schema is used. Ensure that your schema name is set correctly.
In our use case example, you need the following data tables and views:
In our example, we can connect the bookings table to the Emarsys database. In the bookings table we use
destination_city as reference fields. We use these two fields, because in our example we want to use data which can be matched to a specific contact and their destination.
This view returns services, availability and related information for each city.
|rent_a_car||Rent a car||ViaRent||Vienna||10||Euro||hour||yes
|rent_a_car||Rent a car||BerliRent||Berlin||14||Euro
|rent_a_car||Rent a car||KaiRent||Cairo||6||Egyptian Pound||hour
||Rent a car||LDRent||London||8||Pound||hour
||Rent a bike||TwoWheelsBerlin||Berlin||4||Euro||hour
||Rent a bike||TwoWheelsBerlin||Vienna||2||Euro||hour
What is the difference between the reference field and the data to be displayed?
Reference fields are used to find relevant records from your table or view. These records can have many more fields, all of which can be displayed. You can display any field of your table or view, including, but not limited to the reference fields. The content of the reference fields are often not displayed though, as they typically contain some kind of identifiers.
Example: In our use case, it is the
contact_id, which helps us find the relevant content for that exact contact we will send out in our message. By using the
contact_id and the
destination_city as reference fields together, we can determine which value of the
date_of_arrival field we have to use in the message.
Setting more reference fields
By setting multiple reference fields, we will have more criteria to filter so we can get more information to decide and send back as personalized content.
Example: In our use case, we need two reference fields to filter data based on the
contact_id and the
destination_city values. This way, we can find the specific contact-related field (
date_of_arrival) according to the contact’s identifier and the destination city.
Imagine, if we set only one reference field, and we did not apply the
destination_city field as a reference field, we would not be able to filter the destination_city to the “Vienna” value, only for the
contact_id. In this case, we would get multiple matches, multiple destination cities (e.g: Berlin, Vienna, London) for the filtered
contact_id and we could get the
date_of_arrival for the Berlin or London, not for Vienna.
For information how to set up in the Emarsys Platform, see Setting up reference fields.
You can define a default value for the reference fields, in case the original value cannot be found in the reference field of your table or view. You can think of this as using a constant, the same value for all requests with a missing value. Tick the check box to define the default value to be used if the original value cannot be set in the reference field.
Example: Imagine sending an email to Los Angeles instead of Vienna. We also have a contact who is going there recently, but there is no discounted service available in Los Angeles.
So in the
upsell_services view there is no row for the city of Los Angeles. If you do not use default handling, the system cannot find data to display for Los Angeles. We won't be able to populate for example the
service_name, so it will be left empty. Of course, there is a way to ensure that we do not send an empty email. What if we want to recommend something to those contacts who cannot be offered a service for their specific city? In this case, you should use default handling.
If you add a row to the table with, for example, written “General” in the city field, you can set it to use it as a default value. If a default value is set and the reference field does not have a match in our table then it will return the default value.
According to our example, if you set a default value to the city field, so instead of an empty result we return the
service_name and all others corresponding to the “General” city. For information how to set up a default value, see Setting a default value.
In some cases, the filtering based on the reference field returns not only one, but more records as a result. In such cases you have to define by what field to sort the records. By what field we can decide which is the first row, the second, and so on. It is worth setting up multiple fields for sorting when there are several fields that can have the same values. In this case, we can sort by the value of the second field and so on.
It is worth setting up multiple fields for sorting when one field can have the same value in multiple records, like in the bookings table, the
destination_city can have the Vienna value for multiple records. In this case, we can sort by the value of the second field and, so on.
Example: In our use case, we need the nearest date to the date of arrival, so we have to sort the fields by the date of arrival in ascending order. For information how to set up in the Emarsys Platform, see Setting fields to sort.
Known limitations of the reference fields
Currently, due to technical limitations, it is not possible to apply variables for reference fields.
Example: In our use case example, the destination city is a constant value (Vienna) of the bookings table.
If we want to have a dynamic connection between the
city field of upsell_services view and
destination_city of the Bookings table, we should set up a variable for the
city value. At the moment, this is only possible with Emarsys Scripting Language (ESL) codes.
3. Setting up your connection
Relational Data setup can either be performed
- by you (Self-service),
- can be a hosted self-service setup or,
- performed by Emarsys (Fully Managed).
For more information, see Level of services.
Set up your connection before you can begin to use Relational Data to personalize your messages in either of the following ways:
Emarsys-hosted: Relational Data onboarding for clients using Emarsys-hosted databases:
You can upload your data in any of the following options:
- API: Emarsys API provides access for clients to upload their data into Emarsys servers. For optimum performance and data transfer, you can use the Emarsys API, for details, see Transferring data via the API.
- SFTP: You can upload your data files into an Emarsys-hosted SFTP server.
NOTE: A public SSH key is needed for each SFTP file where you want to access your SFTP account, for details, see Accessing the SFTP server.
- Client-hosted: You can set up the connection between your own (external) database and Emarsys, for details, see: Using client-hosted databases.
4. Setting up the reference fields
First, you need to set up your reference fields, so that you could use your tables for personalization.
- Go to Add-ons-> Relational Data.
- Click the cogwheel icon to open the Table & View settings.
3. In your table, select the desired reference fields from the drop-down, and click Save when finished.
Example: In our use case, you need to select the bookings table and set 2 reference fields:
For more information, see Setting more reference fields.
Be aware that reference field values will be passed to your database as strings. This means that in case of strongly typed databases such as PostgreSQL and Microsoft SQL Server, only those fields can be reference fields which are equal with strings.
Setting a default value
4. In your table or view, for the previously set reference fields add a default value, and click Save when finished.
Example: In our use case, you need to select the upsell_services view and set a default value (General) for the reference field:
For more information, see Default value.
Setting fields to sort
5. To sort your table or view you can select the field from the drop-down list and click Save when finished. In case of equal values, the next field in the list is used.
Example: In our use case, we need the nearest date to the date of arrival, so we have to sort the
date_of_arrival values in ascending order.
5. Creating presets and tokens
Tokens are reusable variables across email campaigns. Tokens define which record is used for personalization in your messages.
As a prerequisite to creating Relational Data tokens, first you need to have a Relational Data preset to be used.
Creating a preset
Relational Data presets are preconfigured sets of values for the reference fields of a particular table or view. They determine which data records are referenced by the placeholder that you add to your content and in what way.
- Go to Content -> Personalization and click Create Token.
- Select Relational Data as the type of token to be created in the Personalization capability window.
- In the Create Token window, click New in the Relational Data preset line, which opens the Create RDS Preset window. First, add a meaningful name to your preset.
Name your Relational Data preset.
NOTE: You will have to use this name to reference your preset during token creation, so, it is worth using a descriptive name.
- Select the database connection. It is internal_dummy in our example.
- Select the desired table or view. We select the bookings table here.
- Set the available parameters, by editing their Type and Value.
Contact field: Use Contact field as type to define a field in your contact database so its value can be used to filter your table in the Relational Data. As the example shows, the field can have a different name (e.g.:
rds_docu_id), it does not have to be the same as the field in the Relational Data table (
External Event: Use the external event parameter when you want your personalization token set by data in the JSON file of an external event. For more information, see Creating an External Data token.
The external event parameter has the following values:
- Event: Select the relevant event name.
Variable: Each JSON object in the body of the external event can be selected as a variable.
When the external event is triggered, the received objects can be used to filter data in the selected table or connection.
Constant: Use this type to enter a “fixed” value which is present in your database. By entering a single value, you can filter for a category or group.
Example: In the current example, the bookings table has rows for many destination cities, but we are only interested in rows with "Vienna".
8. Confirm with the Tick Mark icon when you are ready with each parameter.
9. Click Continue when finished with the parameter settings.
Now, you are ready to create the Relational Data token.
Creating a Relational Data token
After you have successfully created your preset, you can continue creating your token.
- Give a descriptive name to your token to make it easier to find it while using a content editor.
- Click Create Category in the token creation window. Add a descriptive name to your category and click Create.
NOTE: This is important, since you will be using these tokens in many categories, so giving descriptive names will be beneficial later while searching for your token.
Select the category you want to add the token to. A single token can be used in more than one category. You can also create a new category, for more information, see Creating a token category.
NOTE: Your tokens can be reused across campaigns. Your token category will be listed in the Personalization sidebar within the Content Creation step of the Email content creation process.
3. Select the Relational Data preset that will be used for the token. You can select and Edit one of your previous presets or even create a New one.
4. Select the field that you want to display in your message. In our example we used
5. This line shows a preview of the token and the Emarsys Scripting Language code that is used for it. This code can be copied and inserted into blocks, too. For more information on ESL, see Emarsys Scripting Language (ESL).
6. The item index selects the item to be used from the array. Please note that the list starts with "0", so this means the first item, "1" denotes the second, "2" the third and so on. Use this option to choose from the values, when your database return multiple results to query.
If you want to use an index, you should check that sorting is set up properly, as it defines which row will be the first, second, and so on.
7. Check this box, if you want to make sure that the message will not be sent out, if we were not able to populate the personalization token. In our example if we had not set the default value for the
city field in the upsell_service view, we could not have populated the personalization token. In that case, if you check this box, the message will not be sent.
8. Add a fallback text that will be displayed if the value of the field cannot be obtained for some reason. Using a fallback text and marking the field as a requirement for sending are mutually exclusive.
9. You can add formatting details of the token.
6. Defining the personalized email content
5. Select your token category.
6. Drag and drop your predefined tokens from the Personalization sidebar into the desired email block.
7. Check the Contact Preview of your email.
You can select any one of the contacts in your database and see exactly how your email will look when it has been personalized with their properties. For more information, see Contact preview.
a. Click the Contact Preview icon.
b. Select the desired contact from the drop-down list.
c. Click the eye icon to display the Preview.
8. Close the Preview and click Save.