Logo Help Portal
  • Getting Started

    Introduction

    • Onboarding overview
    • Project Initiation
    • Email compliance
    • Account Administration

    Data

    • Data exchange
    • Data onboarding
    • Web data collection

    Channels

    • Email onboarding
    • Smart Insight
    • Predict
  • Training
    • Online Self Learning
    • Onboarding Videos
    • Instructor Led Training
    • Webinars
    • Seminars
  • User Guides

    Strategy

    • Personalization
    • Automation
    • Data Monitoring

    Channels

    • Email
    • Mobile Apps
    • Web
    • Ads

    Add-ons

    • Smart Insight
    • Predict
    • Incentive Recommendation
    • AIM
  • News & Support

    News

    • What’s New in the Emarsys Platform
    • Pilot features
    • Articles on Data Protection and GDPR
    • Release notes - archive

    Support

    • Help and Support at Emarsys
    • Managing your user profile
  • |
  • Developers
  • System Status
Need help? Submit a request
English Deutsch Español Français Русский Türkçe 简体中文
Sign in
  • Getting Started
  • Data Onboarding
  • 0 Getting Started Data Onboarding

In this section:

  • Data onboarding - Overview
  • Uploading your product data
  • Implementing the Web Extend data collection scripts
  • Google Tag Manager and data collection
  • Mobile data collection
  • Uploading your contact data
  • Uploading your sales data
  • Relational data onboarding
  • in Emarsys42

    Expand all

    Relational data onboarding

    Updated: November 27, 2018 08:27

    With our Relational Data Service you can connect your own external 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
    • Connection setup
      • MySQL
      • Amazon Redshift
      • Google BigQuery
      • PostgreSQL
      • Azure SQL
      • Microsoft SQL
    • Reference fields

    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 Personalization 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 Campaigns -> Personalization.

    In the top right corner of the overview page, select the type of connection you would like to set up.

    The following connection types are available:

    • MySQL
    • Amazon Redshift
    • Google BigQuery
    • PostgreSQL
    • Azure SQL
    • Microsoft SQL

    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 view option () is available only with mySQL connections, regardless of whether the database is external or internal (hosted by Emarsys). This option always takes you to the MyAdmin page, where your can make the necessary settings.

    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

    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

    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

    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

    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

    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. 

    To select the reference fields click the icon by the connection.

    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.

    Was this article helpful?

    Have more questions? Submit a request
    Return to top

    You may also be interested in:

    Related articles

    • Web Channel - Overview
    • Contact Data Fields - Overview
    • Data security at Emarsys
    • Smart Insight onboarding
    • Predict onboarding
    Copyright © 2019 Emarsys eMarketing Systems. All rights reserved
    Legal Notice Privacy Policy Master Services Agreement Anti-spam Policy
    test new search