With our Relational Data Service you can connect your own external (client-hosted) databases to Emarsys and use the data fields contained in them to create segments or populate personalization variables.
For more information, see: Relational Data - Overview.
Contents:
Preparing your database
-
Tables and views
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.
Each table or view can only be used with one set of reference fields for personalization (which are linked by AND) therefore you need to create as many views as you will need in your database before you start. -
Duplicates
Duplicate contacts are rejected by our connectors, so you will need to deduplicate your database before you start.
Connection setup
Before you start, please note the following restrictions:
- The supported mySQL versions are: 5.5, 5.6 and 5.7.
- With Redshift connection, there is no need for a CA Certificate.
- In order for us to have full access to your database, you need to whitelist our IP: 185.4.123.112.
The first step in using Relational Data is to set up the connection between your database and Emarsys. To do this, go to Add-ons -> Relational Data -> Connections tab.
In the top right corner of the Connections tab, click the three dots and select the type of connection you would like to set up.
The following connection types are available:
Some of the connection types require a CA Certificate. Read more about how to obtain one of these here.
MySQL
Make sure you fill in all fields marked with an asterisk.
When you have added all necessary data, you can Test your connection and if all works, click Save to create your connector.
In your MySQL database, the following roles must be assigned to the database user you share with Emarsys:
-
SELECT,SHOW VIEW
If you want to use MyAdmin, you should additionally assign the following roles to the database user you share with Emarsys:
-
ALTER,CREATE,CREATE VIEW,DELETE,DROP,INSERT,INDEX,UPDATE
MyAdmin
Please note that edit and delete are only available with connections where the database is not hosted by Emarsys.
The information and controls on the MyAdmin page are maintained by phpMyAdmin, not Emarsys.
Amazon Redshift
Make sure you fill in all fields marked with an asterisk.
When you have added all necessary data, you can Test your connection and if all works, click Save to create your connector.
In your Amazon Redshift database, the following role should be assigned to the database user you share with Emarsys:
SELECT
PostgreSQL
Make sure you fill in all fields marked with an asterisk.
When you have added all necessary data, you can Test your connection and if all works, click Save to create your connector.
In your PostgreSQL database, the following role should be assigned to the database user you share with Emarsys:
SELECT
Google BigQuery
The Google BigQuery database connector can be used to pull data into Emarsys for segmentation purposes only. It can not be used for personalization fields.
This is because BigQuery is optimized for massive datasets and its queries take 2-5 seconds, regardless of the size of the dataset. Using it for personalization queries would have a negative impact on email sending performance.
Make sure you fill in all fields marked with an asterisk. You can also import all the information in a JSON file directly from your Google Cloud Console. For detailed instructions on how to do this, click here.
When you have added all necessary data, you can Test your connection and if all works, click Save to create your connector.
In your Google BigQuery database, the following roles should be assigned to the database user you share with Emarsys:
-
BigQuery Data Viewer,BigQuery Job User
Microsoft SQL
Make sure you fill in all fields marked with an asterisk.
When you have added all necessary data, you can Test your connection and if all works, click Save to create your connector.
In your Microsoft SQL database, the following role should be assigned to the Emarsys user:
-
DELETE,INSERT,SELECT,UPDATE,VIEW DEFINITION
Azure SQL
Make sure you fill in all fields marked with an asterisk.
When you have added all necessary data, you can Test your connection and if all works, click Save to create your connector.
In your Azure SQL database, the following role should be assigned to the Emarsys user:
-
DELETE,INSERT,SELECT,UPDATE,VIEW DEFINITION
Reference fields
Reference fields are the means by which you determine which records are made available for personalization.
When you come to define a personalization variable in your message content, you will be asked to provide the values for these fields. The fields and values you provide will act as filter criteria (linked by AND) and will link to the data records that you can then use to personalize the content.
Select the desired reference fields from the drop-down, add a default value if needed, and click Save when finished.
The default value will be used if the reference field contains no value in a given record.
There is no need for a default value for unique value fields such as ID, order, price, etc. However, defining a default value might be helpful if you need a fallback value, for example when you have language settings.
The fields here only filter the database records. For the actual personalization variable, you can include any field contained in the record.
When you are done, you can now continue and create segments with Relational Data or personalize content with Relational Data.