Connecting Relational Data

Link data tables from an external database to SAP Emarsys. Use the flexible Segment Template builder to create rules and select data points to be queried in the segment builder. This approach adheres to strict data privacy rules by accessing data only at execution rather than storing it.

Relational Data offers several segmentation scenarios for marketers, removing IT barriers. It improves personalization by allowing users to create campaign personalization tokens using the external data. Integrate them as easily as other SAP Emarsys data elements.

With Relational Data, connect databases to SAP Emarsys using supported connectors and data for segmentation or personalization variables.

SAP Emarsys Relational Data supports the following databases:

• Amazon Redshift

• Azure SQL

• Google BigQuery

• Microsoft SQL

• MySQL

• PostgreSQL

• SAP HANA Cloud

• SAP HANA On-Premise

• Snowflake

Prepare the database:

Make sure there's a proper configuration before connecting the database to Relational Data.

Timezones, tables, and views:

Specify timestamps and dates/times with the correct timezone so SAP Emarsys doesn't default to CET (Europe/Vienna). Avoid setting the timezone as a reference parameter.

SAP Emarsys retrieves tables and views from the database upon connection and shows them on the Relational Data page in the Tables and Views tab. Each table or view uses one set of reference fields for personalization, linked by AND. Set up necessary views in advance, with the option to add later.

Security Connections- TLS/SSL:

SAP Emarsys supports server-side Transport Layer Security (TLS), the successor of the Secure Sockets Layer (SSL) protocol. SAP Emarsys uses TLS for safe server connections and encrypted communication. It verifies server certificates with root certificates for authenticity and checks certificates against the CA to prevent domain theft and attacks.

Configure server certificates in Connection Details under Relational Data Connections. Offer the root CA certificate. If signed by an intermediate CA, include the entire CA chain starting with the root CA certificate. Generally, a single root CA certificate suffices.

Connection types requiring CA certificate:

• Microsoft SQL Server

• MySQL

• PostgreSQL

• SAP HANA on-premise

SSL configuration links:

• MySQL: Configuring MySQL to Use Encrypted Connections

• PostgreSQL: Safe TCP/IP Connections with SSL

• Microsoft SQL: Enable Encrypted Connections to the Database Engine

• SAP HANA on-premise: Configure SSL for SAP HANA (CA)

CA certificates format:

The system accepts CA certificates in Privacy-Enhanced Mail (PEM) format. Databases managed by cloud providers such as SAP HANA Cloud, Azure, Redshift, or Snowflake don't need a CA certificate.

Connect the Microsoft SQL databases:

To connect an externally hosted database with SAP Emarsys, make sure the database has proper configuration and necessary preparation.

Before starting:

• Give SAP Emarsys SELECT and SHOWPLAN privileges.

• Allow IP addresses:

  • 34.89.173.3

  • 34.89.137.140

  • 35.246.249.205

  • 185.4.123.112

Set up the connection:

1. Configure the connection:

   • Connection Name: The reference name for the connection, possibly matching the database name.

   • Host: The host name’s database.

   • Port: The database connection’s port number.

   • Database Name: The database’s name.

   • User name: The connection’s login name.

   • Password: The connection’s password.

   • Connection Parameters (optional): Extra JDBC version 2.1 parameters, or default parameters.

   • CA Certificate: Root CA certificate for the connection.

2. Click 'Test' to verify functionality.

3. Click 'Save' to complete the connector.

After setting up the database connection, create Relational Segment Templates and then Relational Segments using the Relational Data. Segment Templates allow users to predefine complex segmentation scenarios with SQL queries and express them in simple sentences.

Relational Segments examples:

These examples show the power and flexibility of Relational Segments in crafting customized, effective marketing strategies.

Create Relational Segment Templates:

Segment Templates simplify complex segmentation scenarios by defining them through SQL queries. This simplification makes building segments easier.

Let's check how to create a segment template targeting contacts who qualify for a replenishment campaign.

Steps to create a Segment Template:

1. Name the segment.

2. Select a database using the drop-down menu.

3. Write the segment definition using defined parameters.

4. Create parameters and define variables using text, numeric, date inputs, or custom dropdowns. Then, reuse the parameters by dragging them into the SQL query.

   • Text input: Create a free text field that accepts alphanumeric and special characters.

   • Numeric input: Create a numeric field that only accepts integers, including negative values.

   • Date input: Create a date field in the format dd-mmm-yyyy, with an extra calendar function.

   • Custom dropdown: Create a single-choice list. The name appears in the segment, and the SQL query uses the list item values. Choose different option types and define filter operators for the dropdown.

5. Write the SQL query that underlies the segment description and extract data from the tables. Test the query first, then integrate it into the template.

6. Test the query, check the SQL syntax, and run the query. Adjust if necessary, based on database engine results.

7. Map the Relational Data reference field to the unique external contact ID (for example, externalID) for contacts in SAP Emarsys.

8. Preview and test. Examine the segment from a user’s perspective; test it with real data.

9. Run and confirm it. When seeing the correct results, click ‘Save’ to complete, confirm the query.

Relational Data with personalization allows diverse information to become meaningful, relevant, and compelling content.

Prerequisites:

To use personalization in Relational Data, confirm that these requirements meet the standards:

• Sync an identifier with the contact database.

• Sync data with a database linked to Relational Data.

• Overlap fields between Relational Data and the contact database (reference field).

Relational database:

SAP Emarsys's Relational Data interprets data sets during execution, locates precise data from the Relational Data database, and applies it correctly in campaigns.

Configure tables or views for personalization by naming reference fields and adjusting the sort order of data.

Reference fields:

A reference field functions as a key, matching data between the contact database and the Relational Database. This common key helps the personalization service identify the correct data.

Data views:

If a table lacks the necessary data point, create a view that merges tables, establishing a usable data view with a correlating, referenceable data point.

Considerations for Reference and Sorting Fields:

• Use at least one reference field to create a personalization preset with a specific data source.

• Use the reference field as the key criterion for the personalization service to identify the first entry meeting criteria.

• Use a single or various reference fields, depending on needs, to guarantee the proper order of results.

SAP Emarsys connects to databases to generate and run queries, retrieving personalized content for email campaigns using Relational Data.

SAP Emarsys opens database connections:

It connects to the database using Relational Data Service (RDS) in the following scenarios:

• Using Relational Data features, such as launching email campaigns with personalization. Also, using Relational Data segments in an Automation program.

• Browsing the User Interface (UI) of the SAP Emarsys to gather metadata.

• Importing data through the Relational Data.

Connection pools:

Relational Data uses separate connection pools for segmentation, personalization, and data import. This design prevents interference. It also means slow segment processing doesn't hinder data import.

Connection timeout:

• All connectors have a 5-second connection timeout.

• Exception: Amazon Redshift has a 15-second timeout.

Generate and run personalization queries:

After establishing a database connection, SAP Emarsys generates and runs queries to retrieve personalized content.

• Relational Data overview: Integrates client-hosted databases into SAP Emarsys to support segmentation and personalization using external business data without duplication.

• Segmentation and personalization: Relational Data uses external data directly during campaign execution, creating complex segments and customizing messages based on defined templates.

• Database connection setup: This process connects databases using supported connectors, TLS certificates, and specified credentials. It verifies the connection using a test and saves the steps.

• Segment templates and examples: It builds segment templates with SQL queries and custom parameters to define dynamic marketing segments, such as purchase history or behavior.

• Reference fields and personalization: This section defines reference fields to align external data with SAP Emarsys contacts and generate customized queries during campaign execution.