Enable Email Engagement

Email engagement data captures the history of email interactions between a customer and a brand, such as opens and clicks (by day and by month), unsubscribes, conversions, and bounces.

Amperity provides a standardized way to store email events information, from which a set of email engagement attributes are calculated.

Enable the use of email engagement data for workflows within Amperity (and downstream) by applying email event or email summary semantic tags to feeds that contain email engagement data. When these semantic tags are applied to data sources a series of standard tables are added to your customer 360 database. Use these standard tables to build email engagement workflows within Amperity.

About email engagement tables

Amperity supports email engagement workflows using a combination of the following tables:

1. The Email_Engagement_Attributes table contains all fields from the Email_Engagement_Summary table, unique by Amperity ID and by email address. This table may be extended for related email engagement and transaction attributes.

2. The Email_Engagement_Summary table contains a summary of email event statistics, such as counts for opens and clicks, the first open, and the most recent click, unique by email address.

3. The Unified_Email_Events table contains individual email event information, such as sends, opens, clicks, opt-in and opt-out preferences, bounces, and conversions. This table is added as Stitch output when email event semantic tags are applied to data sources.

   Important

   The Unified_Events table requires raw email events from your email service provider and the use of email events semantic tags.

The types of email engagement semantic tags that are applied to data sources in your tenant will determine the combination of tables that will be available in your tenant.

About data sources

There are two general types of data sources that may be used to provide email engagement data to Amperity: “raw email events” and “summarized events”.

Your tenant’s approach to adding email engagement workflows will determine which types of data sources to use, along with which sets of semantic tags will be applied to incoming data.

Raw email events

Raw email events data is captured by email service providers when users interact with emails, such as opens, clicks, opt-ins, opt-outs, sends, unsubscribes, and conversions.

A custom domain table is often necessary to filter and reshape raw email events data prior to applying email events semantic tags.

Raw email events data is available from most email service providers, though the specific data points that are available may vary from provider to provider.

Salesforce Marketing Cloud, Oracle Repsonsys, Mailchimp, Campaign Monitor, Bluecore, Klaviyo, Adobe Marketo, Adobe Campaign, Sailthru, Listrak, SendGrid, and HubSpot are all data sources that can provide usable raw email events data to Amperity.

Important

Raw email events data is designed to capture every single interaction and can very large storage requirements and may contain fields that are not useful to your email engagement workflows. Consider the volume of raw email events data you may require, and then talk with your Amperity representative before adding raw email events data to Amperity.

Summarized email events

Summarized email events data is subset of raw email events data that is scoped to support a specific set of email engagement workflows.

Email events data should be summarized prior to sending that data to Amperity. In some use cases, you may use a custom domain table to do the summarization prior to applying semantic tags, and then apply the email summary semantic tags.

Examples of summarized email events data include:

• Email opens in the previous N days

• Email clicks in the previous N days

• Most recent click (date and time)

• Filters by opt-in and opt-out customers

Tip

Summarized email events data is recommended for all email engagement workflows that do not define a complete customer journey.

About semantic tags

Email engagement semantic tags capture email events data, such as clicks, opens, bounces, opt-ins, opt-outs, and conversions from any email service provider (ESP) data source.

1. Use email events semantic tags when raw email events data is sent directly to Amperity.

2. Use email summary semantic tags when email events data is summarized prior to sending it to Amperity.

Tip

If the email service provider does not provide email addresses alongside sends and clicks data, look for a table from that email service provider that provides a list of contacts that may include email addresses along with a unique ID that can be used to link events data.

Email events

Email events associate email summary statistics to brands, email addresses, regions, event types, event dates and times, and sender IDs.

Apply email event semantic tags to data sources that contain data for raw email events. Use the built-in list of semantics when building a feed or custom domain table. Email event semantics are prefixed with email-event/ in the semantics drop-down menu in the Feed Editor.

Important

Email events semantic tags should only be applied to data sources that provide at least 15 months of raw email events data. The storage requirements for this type of data can be significant. Talk with your Amperity representative about your downstream use cases prior to applying email events semantic tags to raw email events data sources.

The following table lists the tags available to this semantic group (with required semantic tags noted by “ Required.” and recommended semantic tags noted by “ Recommended”):

Semantic Name	Datatype	Description
brand	String	Required. The brand or company from which an email was sent.
campaign-id	String	The unique ID for a campaign.
campaign-name	String	The name of the campaign.
email	String	Required. The email address to which an email was sent.
event-datetime	Datetime	Required. The date and time at which email event occurred.
event-type	String	Required. The type of email event. Possible values: Open Click Sent Optin Bounce Converted Unsubscribe Important The values for email event types must spelled as expected to ensure the Email_Engagement_Summary can properly summarize email events. For example: “Optin” may not be “Opt-in”.
region	String	The region or location from which an email was sent. The region or location is typically associated to a single brand.
send-id	String	Required. The unique identifier for the email that was sent to an email address at a specific date and time. If a data source does not provide a send ID a unique key is generated.
treatment-id	String	The ID for the recipient group to which the associated campaign was sent.
treatment-name	String	The name of the recipient group to which the associated campaign was sent. One or more recipient groups, along with a control group, are used to measure the quality of a campaign.

Email summary

Email summary statistics provide fields that summarize customer engagement with your brand. Individual statistics include brand, email address, counts for opens and clicks by day (1, 3, 5, 7, and 14) and by month (3, 6, 9, and 12), engagement frequency, and engagement status.

Apply email summary semantic tags to data sources that contain email summary data for how customers interact with emails sent to them from your brands. Use the built-in list of semantics when building a feed. Email summary semantics are prefixed with email-summary/ in the semantics drop-down menu in the Feed Editor.

Warning

Email summary semantic tags cannot be applied to raw email events data.

The following table lists the tags available to this semantic group (with required semantic tags noted by “ Required.” and recommended semantic tags noted by “ Recommended”):

Semantic Name	Datatype	Description
brand	String	Required. The brand or company from which an email was sent.
email	String	Required. The email address to which an email was sent.
email-click-rate-lifetime	Decimal	The rate at which a link in any email message was clicked.
email-clicks-last-x-days	Integer	The number of email clicks in the last 1, 3, 5, 7, or 14 days.
email-clicks-last-x-months	Integer	The number of email clicks in the last 3, 6, 9, or 12 months.
email-clicks-lifetime	Integer	The number of times a link in an email was clicked from within the email events data that is provided to Amperity.
email-open-rate-lifetime	Decimal	The rate at which any email message was opened.
email-opens-last-x-days	Integer	The number of email opens in the last 1, 3, 5, 7, or 14 days.
email-opens-last-x-months	Integer	The number of email opens in the last 3, 6, 9, or 12 months.
email-opens-lifetime	Integer	The number of all email opens from within the email events data that is provided to Amperity.
engagement-frequency-last-15-months	String	A classification that measures engagement frequency click rates for email addresses that have received a low volume of emails. Possible values: Non-recipient (0 received emails) New (fewer than 5 received emails) Active (currently engaging with received emails) Inactive (not currently engaging with received emails) Important Send rates must be available.
engagement-status-last-15-months	String	A classification that measures click rates for email addresses that have received a low volume of emails. Possible values: Non-recipient (0 received emails) New (fewer than 5 received emails) Low (modeled open rate is in the lower third) Medium (modeled open rate is in the middle third) High (modeled open rate is in the upper third) Important Send rates must be available.
first-email-click-datetime	Datetime	The date and time at which an email was first clicked.
first-email-open-datetime	Datetime	The date and time at which an email was first opened.
first-email-send-datetime	Datetime	The date and time at which an email was sent.
most-recent-bounce-datetime	Datetime	The date and time for the most recent bounced email.
most-recent-email-click-datetime	Datetime	The date and time at which a customer most recently clicked a link or offer within an opened email.
most-recent-email-open-datetime	Datetime	The date and time at which a customer most recently opened an email.
most-recent-email-optin-datetime	Datetime	The date and time at which a customer most recently opted-in to receiving email.
most-recent-email-optout-datetime	Datetime	The date and time at which a customer most recently opted-out from receiving email.
most-recent-email-send-datetime	Datetime	The date and time at which an email was most recently sent.
region	String	The region or location from which an email was sent. The region or location is typically associated to a single brand.

Steps to enable

There are two workflows available to email engagement:

1. Unified workflows (from raw email events data)

2. Summarized workflows (from summarized email events data)

Choose the workflow that matches the type of email engagment data that your organization will make available to Amperity.

Important

Email engagement attributes are available to both workflows. These attributes use the Merged_Customers table to associate email events data with the Amperity ID. Add the Email_Engagement_Attributes table to your customer 360 database after the Unified_Events and/or Email_Engagement_Summary tables are available.

Unified workflows

Unified workflows are based on raw email events that contain data for opens and clicks (required), along with sends, unsubscribes, opt-ins, opt-outs, bounces, and conversions (recommended).

The unified workflow starts with semantically tagging raw email events data, and then adding the Unified_Email_Events and Email_Engagement_Summary tables to your customer 360 database.

To add the Unified_Email_Events table

1. From the Sources tab add a custom domain table. Use a prefix like Email_Events_ to ensure this table can be easily identified as part of the email engagement workflow.

2. Use Spark SQL to build a custom domain table that maps the incoming fields to the following field names: Sent, Open, Click, Unsubscribe, Optin, Bounce, and Converted. For example:

   ,CASE
     WHEN EventType = 'Sent' THEN 'Sent'
     WHEN EventType = 'Opened' THEN 'Open'
     WHEN EventType = 'Clicked' THEN 'Click'
     WHEN EventType = 'Unsubscribed' THEN 'Unsubscribe'
     WHEN EventType = 'Convert' THEN 'Converted'
     WHEN EventType = 'Optin' THEN 'Optin'
     WHEN EventType = 'Bounce' THEN 'Bounce'
   END AS EventType
   

3. A field for Brand must be added if it’s not already present in the source data. Most email service providers do not provide brand unless there is multi-brand data. Brands may be identifiable by an account ID or some other indicator.

4. A field for SendID must be added if it’s not already present in the source data. The send ID is a unique identifier for a given email send action. An open and click data point may share the same send ID.

   Some email service providers do not provide a send ID directly. When a SendID is not directly available, build one using a concatenation similar to:

   ,CONCAT(email, '-', EventType, '-',EventDatetime) AS SendID
   

5. Validate your SQL, and then click Next.

6. Apply the email events semantic tags to the corresponding columns. If an email field is tagged with the customer profile email semantic tag it must also be tagged with the email-event/email semantic tag.

7. Save the custom domain table.

8. Run Stitch.

9. Run the customer 360 database.

10. Add the Unified_Email_Events table to the customer 360 database as a passthrough table.

11. Activate the customer 360 database, and then run it again.

To add the Email_Engagement_Summary table

1. Add the Email_Engagement_Summary table to the customer 360 database as a passthrough table.

2. Activate the customer 360 database, and then run it again.

Summarized workflows

Summarized workflows are based on summarized email events that contain specific sets of data for opens, clicks, and other data points required to support your email engagement workflows.

The summarized workflow starts with semantically tagging summarized email events data, and then adding the Email_Engagement_Summary table to your customer 360 database.

To add the Email_Engagement_Summary table

1. From the Sources tab apply email/summary semantic tags to data source that contain summarized email events.

2. Run Stitch.

3. Run the customer 360 database.

4. Add the Email_Engagement_Summary table to the customer 360 database as a passthrough table.

5. Activate the customer 360 database, and then run it again.

Engagement attributes

The Email_Engagement_Attributes table is available to both workflows and may be added as a SQL table after the Unified_Email_Events and/or Email_Events_Summary tables have been added as passthrough tables.

Important

To improve the quality of email engagement workflows be sure that each email address is associated to only one Amperity ID.

Example: Custom domain tables

The following are examples of using custom domain tables to define how raw email events data should be handled from Salesforce Marketing Cloud and Oracle Responsys.

Important

The following examples are not templates. The Spark SQL that is required to enable email engagement workflows will vary across tenants. The examples for <Brand_Name> is a placeholder. SendID represents the ID for a given email send.

Salesforce Marketing Cloud

WITH union_data AS (
SELECT
  _uuid_pk
  ,SendID
  ,EventType
  ,EventDate AS EventDatetime
  ,EmailAddress AS EMAIL
FROM SFMC_Sent

UNION

SELECT
  _uuid_pk
  ,SendID
  ,EventType
  ,EventDate AS EventDatetime
  ,EmailAddress AS EMAIL
FROM SFMC_Opens

UNION

SELECT
  _uuid_pk
  ,SendID
  ,EventType
  ,EventDate AS EventDatetime
  ,EmailAddress AS EMAIL
FROM SFMC_Clicks

UNION

SELECT
  _uuid_pk
  ,SendID
  ,EventType
  ,EventDate AS EventDatetime
  ,EmailAddress AS EMAIL
FROM SFMC_Unsubs

UNION

SELECT
  _uuid_pk
  ,SendID
  ,EventType
  ,EventDate AS EventDatetime
  ,EmailAddress AS EMAIL
FROM SFMC_Bounces

UNION

SELECT
  _uuid_pk
  ,NULL AS SendID
  ,'Optin' AS EventType
  ,DateCreated AS EventDatetime
  ,EmailAddress AS EMAIL
FROM SFMC_Subscribers

WHERE Status = 'Active'
)

,email_event_types AS (
  SELECT DISTINCT
  _uuid_pk
  ,EventDatetime
  ,EMAIL
  ,SendID
  ,CASE
    WHEN EventType = 'Sent' THEN 'Sent'
    WHEN EventType = 'Open' THEN 'Open'
    WHEN EventType = 'Click' THEN 'Click'
    WHEN EventType = 'Unsubscribe' THEN 'Unsubscribe'
    WHEN EventType = 'Convert' THEN 'Converted'
    WHEN EventType = 'Optin' THEN 'Optin'
    WHEN EventType = 'Bounce' THEN 'Bounce'
  END AS EventType
  FROM union_data
)

,email_events AS (
  SELECT
    _uuid_pk
    ,<Brand Name> AS brand
    ,email
    ,EventType
    ,EventDatetime
    ,SendID
  FROM email_event_types
)

SELECT * FROM email_events

Oracle Responsys

WITH union_data AS (
  SELECT
    ACCOUNT_ID
    ,_uuid_pk
    ,RIID
    ,Event_Type_ID
    ,EVENT_CAPTURED_DT AS EventDatetime
    ,EMAIL
  FROM Responsys_Sent

  UNION

  SELECT
    ACCOUNT_ID
    ,_uuid_pk
    ,RIID
    ,Event_Type_ID
    ,EVENT_CAPTURED_DT AS EventDatetime
    ,EMAIL_ADDRESS_ AS EMAIL
  FROM Responsys_Open

  UNION

  SELECT
    ACCOUNT_ID
    ,_uuid_pk
    ,RIID
    ,Event_Type_ID
    ,EVENT_CAPTURED_DT AS EventDatetime
    ,EMAIL_ADDRESS_ AS EMAIL
  FROM Responsys_Click

  UNION

  SELECT
    ACCOUNT_ID
    ,_uuid_pk
    ,RIID
    ,Event_Type_ID
    ,EVENT_CAPTURED_DT AS EventDatetime
    ,EMAIL
  FROM Responsys_OptOut

  UNION

  SELECT
    ACCOUNT_ID
    ,_uuid_pk
    ,RIID
    ,Event_Type_ID
    ,EVENT_CAPTURED_DT AS EventDatetime
    ,EMAIL
  FROM Responsys_Bounce

  UNION

  SELECT
    ACCOUNT_ID
    ,_uuid_pk
    ,RIID
    ,Event_Type_ID
    ,EVENT_CAPTURED_DT AS EventDatetime
    ,EMAIL
  FROM Responsys_OptIn

  UNION

  SELECT
    ACCOUNT_ID
    ,_uuid_pk
    ,RIID
    ,Event_Type_ID
    ,EVENT_CAPTURED_DT AS EventDatetime
    ,EMAIL_ADDRESS_ AS EMAIL
  FROM Responsys_Convert
)

,email_event_types AS (
  SELECT DISTINCT
    ACCOUNT_ID
    ,_uuid_pk
    ,EventDatetime
    ,EMAIL
    ,CASE
      WHEN Event_Type_ID = '1' THEN 'Sent'
      WHEN Event_Type_ID = '4' THEN 'Open'
      WHEN Event_Type_ID = '5' THEN 'Click'
      WHEN Event_Type_ID = '7' THEN 'Unsubscribe'
      WHEN Event_Type_ID = '19' THEN 'Converted'
      WHEN Event_Type_ID = '21' THEN 'Optin'
      WHEN Event_Type_ID = '2' THEN 'Bounce'
    END AS EventType
  FROM union_data
)

,email_events AS (
  SELECT
    ACCOUNT_ID
    ,_uuid_pk
    ,CASE
      WHEN ACCOUNT_ID = <> THEN <>
      WHEN ACCOUNT_ID = <> THEN <>
    END AS brand
    ,email
    ,EventType
    ,EventDatetime
  FROM email_event_types
)

SELECT
  _uuid_pk
  ,brand
  ,email
  ,EventType
  ,EventDatetime
FROM email_events