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:
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.
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.
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.
Steps to enable¶
There are two workflows available to email engagement:
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
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.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
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.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
Validate your SQL, and then click Next.
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.
Save the custom domain table.
Run Stitch.
Run the customer 360 database.
Add the Unified_Email_Events table to the customer 360 database as a passthrough table.
Activate the customer 360 database, and then run it again.
To add the Email_Engagement_Summary table
Add the Email_Engagement_Summary table to the customer 360 database as a passthrough table.
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
From the Sources tab apply email/summary semantic tags to data source that contain summarized email events.
Run Stitch.
Run the customer 360 database.
Add the Email_Engagement_Summary table to the customer 360 database as a passthrough table.
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