Connecting to PostgreSQL

Connection to PostgreSQL would be the most optimal option if you need to

  • add extra fields for segmentation by contact fields;
  • configure seamless synchronization of content component;
  • get extra data on contacts in order to build precise segments based on specific conditions.

📘

Important

Please note that this integration is available only in the Growth and Enterprise plans

Step 1. Set up a Connector

  1. Go to your personal profile > Settings > Connectors > and select Connect PostgreSQL.
 Set up a Connector
  1. Fill in all fields:
  • Name. Required.
  • Host. It can be the domain name of the remote server or its IP address.
  • Port. Entered automatically by default.
  • Database. Name of the database on a remote server.
  • Username. User's database login (read only user role is enough). Required.
  • Password. Required.
Connector settings

You may click Validate connection to test whether authorization data is entered correctly. In case of a successful connection, the corresponding service notification will appear in the top right corner. If the connection fails, check the entered data and contact your system admin if needed.

  1. In Set unique contact field, select a table column that contains a unique contact key and Reteno unique contact key. They should match.
Set unique contact field

For example, you select Email. After connection, the column of the PostgreSQL table with email addresses will be mapped to the corresponding field in the Reteno system.

PostgreSQL table

Aside from email addresses, the uniqueness of contacts can be determined by a phone number, external ID, or additional contact field.

📘

Important

You can use the created connector to connect to several data sources. All of them will have the same unique contact key so we recommend using one key in all tables. To use a different key, create another connector to the same database and set another unique contact field.

  1. Click Save.

Step 2. Configure a Data Source

Your external database may contain several tables, and you need to select which one you want to connect to.

  1. Go to your profile > Settings > Data sources, click New data source and select External data source.
Configure a Data Source
  1. Select the created connector.
Select connector
  1. Select the dataset and table and enter a source name. Click Save.
Select the table
  1. Now, import contacts to Reteno and fill the external source with data.
Import contacts

Now you can build segments based on the imported contact fields.

Dynamic segment

📘

Important

Connection to the external database doesn’t presuppose contact import. Segmentation is only available for contacts that exist both in your Reteno account and in the external database. Synchronize and update contacts before creating campaigns.

Step 3. Add data to a message

To insert data, you need to reference it using a Velocity parameter $!data.get(‘sourcename’). As an example, we will use the created source with the name _promo_codes.

Bulk campaign to a segment

For example, you plan a campaign to contact [email protected].

A set of data will be extracted from the table and inserted into an object:

{
  "data": {
    "promo_codes": [
      {
        "id": "2",
        "email": "[email protected]",
        "name": "Dina Kozak",
        "birthday": "2020-09-02T00:00:00Z",
        "promo_code": "AAAA-BBBB-DDDD"
      }
    ]
  }
}

Fields of the array promo_codes can be referenced in two ways:

  • With parameters (if you know the number of parameters in the array).
$!data.get('promo_codes').get(0).get('name')

$!data.get('promo_codes').get(0).get('promo_code')
  • With a loop (most common method).
#foreach($pc in $!data.get('promo_codes'))

$!pc.get('name')

$!pc.get('promo_code')

#end

Triggered campaign

First, you need to create a dynamic segment with certain conditions. For example, your segment will include only contacts who have a birthday today.

Triggered campaign

When the workflow is triggered for contacts that match this condition, the system generates the event. The name of the event is formed from the static part (regularEventType) and the segment ID the campaign is sent to. It can be, for example, regularEventType-170531841.

The event contains contact data, contact ID in Reteno (ContactId), email address (EmailAddress), and data from an external table.

The data from each table field is converted into a key and is placed in an array with a numerical name corresponding to the ID of the data source.

Data source ID

This array is serialized and placed as a string in the field jsonParam. The body of the event may look as follows:

{
  "params": [
    {
      "name": "ContactId",
      "value": "623927159"
    },
    {
      "name": "jsonParam",
      "value": "{\"1043\":[{\"id\":,\"email\":\"[email protected]\",\"name\":\"Dina Kozak\",\"birthday\":\"2020-09-02T00:00:00Z\",\"promo_code\":\"AAAA-BBBB-DDDD\"}]}"
    },
    {
      "name": "EmailAddress",
      "value": "[email protected]"
    }
  ]
}

To deserialize a string into a set of objects, in the workflow > block Email > JSON specify the data source from the event - ${jsonParam}.

Workflow

Having set up the workflow, configure triggers:

Configure triggers

The same approach as for a bulk campaign is used to extract data from the event, with little difference in the name of the array. The following object will be in the email:

{
  "data": {
    "1043": [
      {
        "id": "2",
        "email": "[email protected]",
        "name": "Dina Kozak",
        "birthday": "2020-09-02T00:00:00Z",
        "promo_code": "AAAA-BBBB-DDDD"
      }
    ]
  }
}

The fields of the array 1043 can be referenced in two ways:

  • With parameters (if you know the number of parameters in the array).
$!data.get('1043').get(0).get('name')
$!data.get('1043').get(0).get('promo_code')
  • With a loop (most common method).
#foreach($pc in $!data.get('1043'))

$!pc.get('name')

$!pc.get('promo_code')

#end

Connect PostgreSQL as an external data source to fulfill a wide range of marketing tasks. For example, substitute promo codes in triggered campaigns or build complex segments based on data collected on different platforms.

Step 4. Set up Data Export

To regularly update information about the audience, orders and campaign results in PostgreSQL, set up data export from the Reteno to tables. For example, you can export responses to an NPS survey, purchase history, date of the last click in a message, etc.

Available datasets for export:

  • contactActivities;
  • contacts;
  • orderItems;
  • orders;
  • revenue.

List of Data Export Parameters

ContactActivities

ParameterTypeDescription
activitystringActivity status:
• DELIVERED – the message was delivered.
• UNDELIVERED – the message was undelivered (statusDescription contains the reason).
• RECEIVED – the message was opened.
• UNSUBSCRIBED – a contact unsubscribed from the broadcasting list.
• CLICKED – a contact clicked links in the message.
• SPAM – a contact reported spam.
• SUBSCRIPTION_CHANGED – a contact changed the subscription category.
• PUSH_SUBSCRIBED — a contact subscribed to push notifications.
broadcastIdintBroadcast ID
campaignTypestringCampaign type:
• IM — triggered message,
• Group — bulk campaign.
clickEventLink stringContains the link clicked by a contact (when the status is CLICKED)
contactIdintContact ID in Reteno (Internal)
errorCodestringDelivery error SMTP and description
eventKeystringEvent key
eventTypeKeystringEvent type key
externalCustomerIdstringContact ID in your system (External)
mediaTypestringMedia type (Sms, Email, WebPush, Viber, MobilePush, AppInbox, Widget)
messageInstanceIdintService field
messageLanguageCodestringMessage language code
messageNamestringMessage name in Reteno account
messageTagsstringMessage tags
messageURLstringContains the link to the email web-version
senderNamestringViber sender name
startedtimestampDay and time of message sending (format: '2021-10-08 11:11:02')
utmCampaign stringCampaign UTM
workflowIdintWorkflow ID
workflowInstanceIdintThe identifier of a particular workflow launch. Use it to group mailings within the launch of a workflow.

Contacts

ParameterTypeDescription
contactIdintContact ID in Reteno (Internal)
contactSource stringstringContact source:
• SITE_AUTOMATED - binding an email to a push subscriber (webPush collection script),
• I_MESSAGE - sending a single message,
• CAMPAIGN - workflow (block Create contact or Add to segment),
• IMPORT - file import or Add contacts method,
• MANUAL - manually created,
• SUBSCRIPTION - subscription form (Subscribe a contact API method),
• Add contact API method,
• ORDER - order on the site (Add orders API method).
createdDatetimestampContact creation date and time (format: '2021-10-08 11:11:02')
email stringContact email
emailDomainstringEmail domain
emailStatusstringEmail status
externalCustomerId stringContact ID in your system (External)
firstName stringContact first name
languageCode stringContact language code
lastClickedDate timestampDay and time of the last click (format: '2021-10-08 11:11:02')
lastNamestringContact last name
lastReceivedDate timestampDay and time of the last delivery (format: '2021-10-08 11:11:02')
lastSentDate timestampDay and time of the last sending (format: '2021-10-08 11:11:02')
lastViewedDatetimestampDay and time of the last opening (format: '2021-10-08 11:11:02')
smsstringPhone number
totalClickedintTotal clicks amount
totalReceivedintTotal received messages amount
totalSentintTotal sent messages amount
totalViewedintTotal viewed messages amount

OrderItems

ParameterTypeDescription
cost floatfloatProduct price
descriptionstringProduct description
externalProductIdstringExternal product ID
imageUrlstringProduct image link
namestringProduct name
orderDatetimestampOrder creation date (format: '2021-10-08 11:11:02')
orderIdintOrder ID in Reteno (Internal)
quantityintNumber of products
urlstringProduct URL

Orders

ParameterTypeDescription
contactIdintContact ID in Reteno (Internal)
deliveryAddress stringDelivery address
deliveryMethodstringDelivery method
discountfloatDiscount
emailstringEmail
externalOrderIdstringExternal order ID
firstName stringContact first name
lastNamestringContact last name
orderCreatedDatetimestampDate of order creation in Reteno (format: '2021-10-08 11:11:02')
orderDate timestampOrder creation date (format: '2021-10-08 11:11:02')
orderId intOrder ID in Reteno (Internal)
paymentMethodstringPayment method
PhonestringPhone number
StatusstringOrder status:
• INITIALIZED,
• IN PROGRESS,
• DELIVERED,
• CANCELED.
totalCostfloatTotal order cost

Revenue

ParameterTypeDescription
activitystringActivity status:
• DELIVERED – the message was delivered.
• UNDELIVERED – the message was undelivered (statusDescription contains the reason).
• RECEIVED – the message was opened.
• UNSUBSCRIBED – a contact unsubscribed from the broadcasting list.
• CLICKED – a contact clicked links in the message.
• SPAM – a contact reported spam.
• SUBSCRIPTION_CHANGED – a contact changed the subscription category.
• PUSH_SUBSCRIBED — a contact subscribed to push notifications.
campaignType stringCampaign type:
• IM — triggered message,
• Group — bulk campaign.
clickEventLink stringContains the link clicked by a contact (when the status is CLICKED)
contactIdintContact ID in Reteno (Internal)
currency floatCurrency
externalCustomerId stringContact ID in your system (External)
externalOrderId stringExternal order ID
mediaType stringMedia type of the message (Sms, Email, WebPush, Viber, MobilePush, AppInbox)
messageInstanceIdintService field
messageName stringMessage name in Reteno account
messageTags stringMessage tags
messageUrl stringContains the link to the email web-version
orderDate timestampOrder creation date (format: '2021-10-08 11:11:02')
senderName stringViber sender name
startedtimestampDay and time of message sending (format: '2021-10-08 11:11:02')
totalCostfloatTotal order cost
utmCampaign stringCampaign UTM

This information will be transferred to PostgreSQL and will update the data in the tables.

📘

Important

You don’t need to pre-create PostgreSQL tables. They will be generated automatically at the first export, and since that all the incoming data will be updated. All the table names will correspond to the data sets (contact activities, contacts, order items, orders, revenue)

Setting up PostgreSQL connector for data export

  1. Go to SettingsData export _and click _New data export. Select one of the created PostgreSQL connectors.
Data export
  1. Select upload interval and check the boxes for the data sets you want to upload. The data set type by default is public, you can select another type if you have specified it in your PostgreSQL settings.
    Click Save.
PostgreSQL settings

The connection will be displayed in the Data export section, here you can edit its settings.