Send query results to Persado

Persado is an AI-driven content generation and decisioning platform that uses segment data to personalize words, concepts, and ideas to help your business grow purchases, loyalty registrations, automatic payment sign-ups, and self-service enrollments.

Use data from Amperity in Persado to:

• Dynamically use segments to identify high value or high potential customers

• Generate personalized messaging using precise segments across channels, including email, web, short message service (SMS), push, paid media, and interactive voice response (IVR)

• Automatically track user-level data for conversions and revenue

This topic describes the steps that are required to send marketing campaign engagement and/or segment data files to Persado from Amperity:

1. Build a query

2. Add orchestration

3. Run orchestration

Note

Persado must be enabled before you can configure an orchestration to send query results.

Build query

You may need to build queries that shape data into one of the following schemas:

• Marketing engagement data

• Segment data

Marketing engagement data

Marketing engagement data should be sent every 24 hours and contain only data for the previous day. You must define a segment that maps columns to the marketing engagement data schema using a query similar to:

SELECT
  LOWER(TO_HEX(MD5(CAST(LOWER(REGEXP_REPLACE(field_name, '\+.*@', '@')) AS varbinary)))) AS Hashed_Email
  ,field_name AS Campaign_Name
  ,field_name AS Subject_Line
  ,sendid_or_jobid AS Send_ID
  ,CAST(TO_UNIXTIME(MIN(field_name)) AS bigint) AS Sent_Timestamp
  ,CAST(TO_UNIXTIME(MIN(field_name)) AS bigint) AS Bounced_Timestamp
  ,CAST(TO_UNIXTIME(MIN(field_name)) AS bigint) AS Opened_Timestamp
  ,CAST(TO_UNIXTIME(MIN(field_name)) AS bigint) AS Clicked_Timestamp
  ,CAST(TO_UNIXTIME(MIN(field_name)) AS bigint) AS Unsubscribed_Timestamp
FROM Marketing_Engagement_Table

Note

The Marketing_Engagement_Table from which you will map data to the Persado schema will vary. For example, if Salesforce Marketing Cloud is used as part of a combined marketing effort with Persado, some combination of tables from Salesforce will be the source of this data.

Segment data

Segment data should be sent weekly. You must define a segment that maps columns to the segment data schema using a query similar to:

SELECT
  LOWER(TO_HEX(MD5(CAST(LOWER(REGEXP_REPLACE(field_name, '\+.*@', '@')) AS varbinary)))) AS Hashed_Email
  ,MIN(field_name) AS birth_dt
  ,MAX(field_name) AS gender
  ,MAX(field_name) AS state_province_cd
  ,field_name AS anniversary
  ,field_name AS wedding_dt
  ,MAX(field_name) AS loyalty
  ,MAX(field_name) AS children_count
  ,MAX(field_name) AS registration_source
  ,MAX(field_name) AS profile_compl_flag
  ,field_name AS segment_name
  ,MAX(field_name) AS segmentation_tag
FROM Weekly_Segment_Table

Note

The Weekly_Segment_Table from which you will map data to the Persado schema will vary.

Add orchestration

An orchestration defines the relationship between query results and a destination, including the location to which those query results will be sent and the frequency at which the orchestration will be run.

To add an orchestration

1. From the Destinations tab, click Add Orchestration. This opens the Add Orchestration dialog box.

2. From the Object Type drop-down, select Query.

3. From the Object drop-down, select the query for which results will be sent to Persado.

4. From the Destination drop-down, select a destination that is configured for sending data to Persado.

5. From the Data Template drop-down, select a data template.

6. Verify all settings.

7. Set the workflow to Manual. (You can change this to automatic later, after verifying the end-to-end workflow.)

8. Click Save.

Run orchestration

Run the orchestration manually to validate that it works.

To run the orchestration

1. From the Destinations tab, under Orchestrations, open the    menu for the Persado orchestration, and then select Run.

2. The Status column for the orchestration will update to say “Waiting to start…”, after which the notifications pane will update to include a notification that shows the current status.

3. When the orchestration has run successfully, the status is updated to “Completed”.

Persado data schemas

Persado supports the following schemas:

• Marketing engagement data

• Segment data

for marketing engagement data

The marketing engagement data schema supports the following fields:

Persado Column	Description
Bounced_Timestamp	The timestamp at which the email bounced. For example: “1614080616D”.
Campaign_Name	The name of a campaign. May be empty.
Clicked_Timestamp	The timestamp at which the customer clicked an element in the email. For example: “1614080616D”.
Hashed_Email	The MD5 hash of the normalized email address. A normalized email address: Converts the characters to lower case Removes the + character, along with any characters between the + and the @ symbol Uses the VARCHAR data type
Opened_Timestamp	The timestamp at which the customer opened the email. For example: “1614080616D”.
Send_ID	An ID from Salesforce Marketing Cloud for the send. Also referred to as a jobID.
Sent_Timestamp	The timestamp at which the email was sent. For example: “1614080616D”.
Subject_Line	The Persado ID.
Unsubscribed_Timestamp	The timestamp at which the customer unsubscribed. For example: “1614080616D”.

for segment data

The segment data schema supports the following fields:

Persado Column	Description
anniversary
birth_dt	The date of birth that is associated with a customer. Caution This field contains PII data.
closest_mik_distance
children_count	Associated with the loyalty, registration_source, and profile_compl_flag columns.
gender	The gender that is associated with a customer. Caution This field contains PII data.
Hashed_Email	The MD5 hash of the normalized email address. A normalized email address: Converts the characters to lower case Removes the + character, along with any characters between the + and the @ symbol Uses the VARCHAR data type
loyalty	Associated with the children_count, registration_source, and profile_compl_flag columns.
profile_compl_flag	Associated with the children_count, loyalty, and registration_source columns.
registration_source	Associated with the children_count, loyalty, and profile_compl_flag columns.
segment_name
segmentation_tag
state_province_cd	The state or province that is associated with the location of a customer. Caution This field contains PII data.
wedding_dt