Integration: Emarsys

This article will help you learn about how Daasity replicates data from Emarsys, limitations to the data we can extract and where the data is stored in the Emarsys schema.

Key Topics

Click on the links below to take you to the section where you can learn more about this Integration

Integration Overview

This document provides context on what kind of data is being gathered through this extractor, which endpoints that data is coming from, and how the extracted tables relate to each other.

The Daasity integration with Emarsys uses two different methods to extract data:

  1. API Integration - we connect to Emarsys APIs to pull data
  2. BigQuery Open Data Integration - we leverage our BigQuery extractor to replicate data from the Emarsys Open Data Google Cloud environment

Integration Availability

This integration is available for:

  • Pro (V1 and V2)

NOTE: You are considered V2 if...

  • You are a Growth customer
  • Your account says Pro V2 in the Daasity App

Not sure? Reach out to Support@Daasity.com

API Endpoints

The Daasity Emarsys extractor is built based on this Emarsys API documentation.  The following endpoints are used by Daasity to replicate data from Emarsys:

Big Query Open Data

Data is replicated from Emarsys Google Cloud environment and uses queries based on Emarsys Open Data.  Tables that are extracted from Big Query have [bq] in the schema name to indicate the source is Emarsys Google Cloud

  • BigQuery Campaigns
  • BigQuery Sends
  • BigQuery Opens
  • BigQuery Clicks
  • BigQuery Bounces
  • BigQuery Cancels
  • BigQuery Unsubscribes

Entity Relationship Diagram (ERD)

The embedded diagram houses the ERD for the Daasity Emarsys integration illustrating the different tables and keys to join across tables. Hover over the embedded diagram to reveal controls to zoom and scroll.

Emarsys Schema

The Daasity Emarsys extractor creates these tables using the endpoints and replication methods listed. The data is mapped from source API endpoint to the table based on the mapping logic outlined in each table.

Contacts

  • Endpoint: Contacts
  • Update Method: UPSERT
  • Table Name: emarsys.contacts
JSON Element Database Column
id id
contact_id contact_id
first_name first_name
last_name last_name
email email
dob dob
gender gender
marital_status marital_status
opt_in opt_in
email_valid email_valid
date_first_registration date_first_registration
Daasity: account_id _account_id
Daasity: timestamp when loaded into DB __synced_at

Contact List

  • Endpoint: Contact List
  • Update Method: UPSERT
  • Table Name: emarsys.contact_lists
JSON Element Database Column
MD5(list_id + contact_id) id
list_id list_id
contact_id contact_id
Daasity: account_id _account_id
Daasity: timestamp when loaded into DB __synced_at

Lists

  • Endpoint: List
  • Update Method: UPSERT
  • Table Name: emarsys.lists
JSON Element Database Column
id id
name name
type type
created created_at
Daasity: account_id _account_id
Daasity: timestamp when loaded into DB __synced_at

Segments

  • Endpoint: Segments
  • Update Method: UPSERT
  • Table Name: emarsys.segments
JSON Element Database Column
id id
name name
type type
predefinedSegmentId predefined_segment_id
Daasity: account_id _account_id
Daasity: timestamp when loaded into DB __synced_at

Contact Segment

  • Endpoint: Segment Contacts
  • Update Method: UPSERT
  • Table Name: emarsys.contact_segment
JSON Element Database Column
MD5(segment_id + contact_id) id
segment_id segment_id
contact_id contact_id
Daasity: account_id _account_id
Daasity: timestamp when loaded into DB __synced_at

Email Campaigns

  • Endpoint: Email Campaigns
  • Update Method: UPSERT
  • Table Name: emarsys.email_campaigns
JSON Element Database Column
id email_campaign_id
language language
created created
deleted deleted
fromemail_host fromemail_host
name name
status status
api_status api_status
api_error api_error
fromemail fromemail
fromname fromname
subject subject
email_category email_category
filter filter
contactlist contactlist
template template
cc_list cc_list
source source
content_type content_type
root_campaign_id root_campaign_id
version_name version_name
features features
tags tags
Daasity: account_id _account_id
Daasity: timestamp when loaded into DB __synced_at
id __sync_key

BigQuery Campaigns

  • Source: Open Data
  • Update Method: UPSERT
  • Table Name: emarsys.bq_campaigns
JSON Element/SQL Column Database Column
MD5(campaign_id + origin_campaign_id + bq_load_time) id
campaign_id campaign_id
origin_campaign_id origin_campaign_id
is_recurring is_recurring
name campaign_nam
timezone campaign_timezone
version_name version_name
suite_type suite_type
suite_event suite_event
campaign_type campaign_type
defined_type defined_type
category_name category_name
MIN(event_time) first_sent_date
customer_id company_id
loaded_at bq_load_time
Daasity: account_id _account_id
Daasity: timestamp when loaded into DB __synced_at

BigQuery Sends

  • Source: Open Data
  • Update Method: UPSERT
  • Table Name: emarsys.bq_sends
JSON Element/SQL Column Database Column
MD5(contact_id + launch_id + campaign_id + message_id + send_date) id
contact_id contact_id
launch_id launch_id
campaign_id campaign_id
message_id message_id
campaign_type campaign_type
domain domain
event_time send_date
customer_id company_id
loaded_at bq_load_time
Daasity: account_id _account_id
Daasity: timestamp when loaded into DB __synced_at

BigQuery Opens

  • Source: Open Data
  • Update Method: UPSERT
  • Table Name: emarsys.bq_opens
JSON Element/SQL Column Database Column
MD5(contact_id + launch_id + campaign_id + message_id + open_date) id
contact_id contact_id
launch_id launch_id
campaign_id campaign_id
message_id message_id
is_mobile is_mobile
user_agent user_agent
ip ip
platform platform
geo.postal_code geo_postal_code
geo.latitude geo_latitude
geo.longitude geo_longitude
geo.accuracy_radius geo_accuracy_radius
geo.continent_code geo_continent_code
geo.country_iso_code geo_country_iso_code
geo.city_name geo_city_name
event_time open_date
customer_id company_id
loaded_at bq_load_time
Daasity: account_id _account_id
Daasity: timestamp when loaded into DB __synced_at

BigQuery Clicks

  • Source: Open Data
  • Update Method: UPSERT
  • Table Name: emarsys.bq_clicks
JSON Element/SQL Column Database Column
MD5(contact_id + launch_id + campaign_id + message_id + section_id + click_date) id
contact_id contact_id
launch_id launch_id
campaign_id campaign_id
message_id message_id
section_id section_id
link_id link_id
is_img is_img
is_mobile is_mobile
user_agent user_agent
ip ip
platform platform
geo.postal_code geo_postal_code
geo.latitude geo_latitude
geo.longitude geo_longitude
geo.accuracy_radius geo_accuracy_radius
geo.continent_code geo_continent_code
geo.country_iso_code geo_country_iso_code
geo.city_name geo_city_name
event_time click_date
customer_id company_id
loaded_at bq_load_time
Daasity: account_id _account_id
Daasity: timestamp when loaded into DB __synced_at

BigQuery Bounces

  • Source: Open Data
  • Update Method: UPSERT
  • Table Name: emarsys.bq_bounces
JSON Element/SQL Column Database Column
MD5(contact_id + launch_id + campaign_id + message_id + bounce_type + bounce_date) id
contact_id contact_id
launch_id launch_id
campaign_id campaign_id
message_id message_id
bounce_type bounce_type
event_time bounce_date
customer_id company_id
loaded_at bq_load_time
Daasity: account_id _account_id
Daasity: timestamp when loaded into DB __synced_at

BigQuery Cancels

  • Source: Open Data
  • Update Method: UPSERT
  • Table Name: emarsys.bq_cancels
JSON Element/SQL Column Database Column
MD5(contact_id + launch_id + campaign_id + message_id + cancel_date) id
contact_id contact_id
launch_id launch_id
campaign_id campaign_id
message_id message_id
campaign_type campaign_type
suite_type suite_type
event_time cancel_date
customer_id company_id
loaded_at bq_load_time
Daasity: account_id _account_id
Daasity: timestamp when loaded into DB __synced_at

BigQuery Unsubscribes

  • Source: Open Data
  • Update Method: UPSERT
  • Table Name: emarsys.bq_unsubscribes
JSON Element/SQL Column Database Column
MD5(contact_id + launch_id + campaign_id + message_id + unsubscribe_date) id
contact_id contact_id
launch_id launch_id
campaign_id campaign_id
message_id message_id
campaign_type campaign_type
source unsubscribe_source
email_sent_at email_sent_at
event_time unsubscribe_time
customer_id company_id
loaded_at bq_load_time
Daasity: account_id _account_id
Daasity: timestamp when loaded into DB __synced_at

Related Resources