Enable email engagement

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

Amperity provides a standardized way to capture email event details, from which a set of email engagement attributes are calculated.

Use email engagement data by applying email events OR email summary semantic tags to feeds that contain email engagement data.

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 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 have very large storage requirements and may contain fields that are not useful. 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 data.

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 email engagement that does 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. Use only one of the following sets of semantic tags:

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.

Caution

Apply email events semantic tags OR email summary semantic tags to your tenant, AND NOT both. The set of semantic tags that you should use depends on how email events are made available to your tenant:

1. Raw email events

2. Summarized email events

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 dropdown 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 treatment group.
treatment-name	String	The name of the treatment group for a campaign. All treatment groups, along with the control group, combine for measuring campaign quality.

Email summary

Email summary statistics offer fields that summarize customer engagement with your brand. Individual statistics include brand, email address, counts for opens and clicks by day and by month, 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 dropdown 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 options available for email engagement:

1. Unified (from raw email events data)

2. Summarized (from summarized email events data)

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

Important

Email engagement attributes are available to both options. 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 Email Events and/or Email Engagement Summary tables are available.

Unified

Unified is 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).

This option 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 page add a custom domain table. Use a prefix like Email_Events_ to ensure this table can be easily identified as part of email engagement.

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

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

This option 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 page 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 options 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 data be sure that each email address is associated to only one Amperity ID.

Example: Raw email events

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 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