This article provides you information on how Emarsys opens connection to your database in order to generate and run queries to get the personalized content for email campaigns with Relational Data.
When do Emarsys open connection to your database?
Emarsys establishes connection to your database with Relational Data Service (RDS) when you either
- utilize Relational Data features, eg.: launching email campaigns with Relational Data personalization or using an Relational Data segment in an Automation Center program,
- browse the User Interface (UI) of the Emarsys Marketing Platform to collect metadata, or
- import data using the Relational Data API.
Various connection pools
Relational Data uses separate connection pools to avoid interference between segmentation, personalization and data import. This logic ensures that a slow segment running cannot block data import through the Relational Data API.
Open connection time: Emarsys attempts to close the connections as fast as possible, if a connection pool has not been used for a while.
Connection timeout: All connectors have a configured 5 seconds connection timeout.
Exception: Amazon Redshift has 20 seconds timeout.
How to generate and run queries for personalization?
Once the connection has been established to your database Emarsys can generate and run queries to get personalized content.
Use case example
For this query example we use the view called upsell_services.
|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
Setting up reference fields
In the email message we want to use the value of
service_name based on the value of the
city and its
In order to do that, we have to set two reference fields, which are
availability. We use these 2 reference fields both together to identify the values for personalization. (Default value is not needed at this step of this use case example.)
Script to have the name of an available service located in Vienna:
Emarsys runs the following query to have the value of
service_name where the
city = "Vienna" and the
availability = "yes":
select distinct service_name from upsell_services where (city = "Vienna" AND availability = "yes") limit x
Setting up a default value
In case you expect to have some values not represented in your view and you want to make sure that even in that case you have a content there you can set a default value.
We already have the reference fields
availability set up and we added the default value "General" for the
city reference field.
Checking if the default value is needed?
Now, as a default value is set before we run the query to find the personalized content, first we check whether we need to use the default value at all, by running the following query:
select distinct city from upsell_services where city = "Los_Angeles" limit 1
If the default value query’s result is empty, Emarsys needs to use the default value instead of the original value.
select distinct service_name from upsell_services where (city = "General" AND availability = "yes") limit x
Result: Not Empty
If the default value query’s result is not empty, Emarsys runs the query with the original value (not with the default one).
select distinct service_name from upsell_services where (city = "Los_Angeles" AND availability = "yes") limit x
Personalizing multiple contacts
Generally, Emarsys can batch together and run queries per 500 contacts per personalization source (table or view). However, if the message contains the same source multiple times with different configurations (parameters), then the batch size is multiplied by the number of different configurations in the message and they are grouped together. Each query will contain up to 1,000 parameter groups in the
A parameter group contains multiple reference fields for a personalization token.
In this example, the
contact.1234 field contains the destination cities for each contact:
This would be 2x500 parameter group, because even though it is from the same source (view upsell_services) it has 2 different configurations. So, for the 500 contacts Emarsys runs 1 query containing 1000 parameters in the
Query for default handling
select distinct city from upsell_services where (city = "Vienna") OR (city = "Los_Angeles") limit 2
If the default query result is not empty
select distinct service_name from upsell_services where ((city = "Los_Angeles" AND availability = "yes") OR (city = "Vienna" AND availability = "yes")) limit x
For optimal performance the queries should finish running less than 500 ms.