Send query results to Oracle Eloqua

Oracle Eloqua is a marketing automation platform that helps your brand design and manage cross-channel marketing campaigns.

Note

The first audience that is sent from Amperity to Oracle Eloqua should be to a new or empty shared list.

This topic describes the steps that are required to send audiences to Oracle Eloqua from Amperity:

  1. Build a query

  2. Add orchestration

  3. Run orchestration

Note

Oracle Eloqua must be enabled before you can configure an orchestration to send query results. Ask your DataGrid Operator or Amperity representative to enable Oracle Eloqua for your tenant.

Build query

You will need to build a query that outputs fields that can be mapped to static lists in Oracle Eloqua.

SELECT
  email AS C_EmailAddress
  ,given_name AS C_FirstName
  ,surname AS C_LastName
  ,full_name AS C_First_And_Last_Name
  ,address AS C_Address_1
  ,city AS C_City
  ,state AS C_State_Or_Province
  ,postal AS C_Zip_Or_Postal_Code
  ,TO_HEX(MD5(TO_UTF8(UPPER(TRIM(email))))) AS C_MD5_Hashed_Email_Address
  ,TO_HEX(SHA256(TO_UTF8(UPPER(TRIM(email))))) AS C_SHA256_Hashed_Email_Address
FROM Customer_360

Tip

You can define custom attributes with Oracle Eloqua. These attributes must exist in Oracle Eloqua before Amperity can send updates and must be different than the default attributes .

To send custom attributes to Oracle Eloqua

  1. Configure the custom attribute in Oracle Eloqua.

  2. Find the database type name for that attribute. For example, a flag for customers who have made a repeat purchase within the previous 90 days: C_Early_Repeat_Purchaser.

  3. Build a query that returns a field in Amperity that is mapped to the database type name for that custom attribute. For example, if the Early Repeat Purchaser field from Transaction Attributes Extended has already been joined to your Customer 360 table:

SELECT
  email AS C_EmailAddress
  ,given_name AS C_FirstName
  ,surname AS C_LastName
  ,full_name AS C_First_And_Last_Name
  ,address AS C_Address_1
  ,city AS C_City
  ,state AS C_State_Or_Province
  ,postal AS C_Zip_Or_Postal_Code
  ,TO_HEX(MD5(TO_UTF8(UPPER(TRIM(email))))) AS C_MD5_Hashed_Email_Address
  ,TO_HEX(SHA256(TO_UTF8(UPPER(TRIM(email))))) AS C_SHA256_Hashed_Email_Address
  ,early_repeat_purchaser AS C_Early_Repeat_Purchaser
FROM Customer_360

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 Oracle Eloqua.

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

  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 Oracle Eloqua 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”.