Integrations
      Back to home
      1. Help Center
      2. ELT+ | Technical User Documentation
      3. Integrations

      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