Using this integration, you can use TD query jobs to

  • Add contacts to your Eloqua contacts lists
  • Add accounts to Eloqua account groups
  • Add data to Eloqua custom data objects (CDO)

Learn more about:

Prerequisites

  • Basic knowledge of Treasure Data, including the TD Toolbelt

  • An Eloqua account with API access enabled

Obtaining the Custom Data Object Name from Oracle Eloqua Console

1. In your web browser, go to https://login.eloqua.com.
2. Log in to the Oracle Eloqua Console using the same Company Name, Username, and Password you used when specifying the CSF authentication keys in Oracle JDeveloper and Oracle Enterprise Manager Fusion Middleware Control.

3. After logging in, select Audience > Custom Objects.


4. Make note of the custom object name and the field names. 

Optionally, you can navigate to Custom Object Record Reporting and select the desired report:
    • Custom Object Data —Use this report to view all the custom object records within the individual custom object. You can print and export the report to Excel and copy, delete, or move the custom object records.
    • Custom Object and Contact Data — Use this report to view all the custom object records within the individual custom object with the contact information they are mapped to. The report can be viewed in a specified time frame. The default is the past month, but you can change the time range (e.g., Start Date or End Date) or time span (e.g., Last week or Last month). You can print and export the report to Excel.

Use the TD Console to Create a Connection

In Treasure Data, you must create and configure the data connection before running your query. As part of the data connection, you provide authentication to access the integration.

Create a New Authentication

1. Open TD Console.
2. Navigate to Integrations Hub Catalog.
3. Search for Eloqua in the Catalog; hover your mouse over the icon and select Create Authentication.


4. Type the credentials to authenticate:
ParameterDescription
Authentication MethodBasic (Default)
Site NameURL for the Oracle Eloqua site
UsernameThe username of your Oracle Eloqua account
PasswordThe password for your account


5. Select Continue.
6. Enter a name for your authentication, and select Done. 

About Eloqua Query Constraints

The following constraints apply to your queries:

  • Your query requires that each type of resource has specific columns with exact column names (case sensitive) and data types.
  • Your query must define the column mapping.
  • Custom object or account fields are case insensitive.
  • If the custom object or account field contains a space, you must replace it with an underscore.
  • If your query contains any field name that does not exist in the custom object or account field name, an error is returned, and the job fails. 

Define your Query

  1. Complete the instructions in Creating a Destination Integration.
  2. Navigate to Data Workbench > Queries.

  3. Select a query for which you would like to export data.

  4. Run the query to validate the result set.

  5. Select Export Results.

  6. Select an existing integration authentication.
  7. Define any additional Export Results details. In your export integration content review the integration parameters.
    For example, your Export Results screen might be different, or you might not have additional details to fill out:
  8. Select Done.

  9. Run your query.

  10. Validate that your data moved to the destination you specified.

Integration Parameters for Eloqua 


Parameters

Value

Description

DestinationContactThe Eloqua object to export data (contact list or custom object or account)

Custom Object DataThe Eloqua object to export data (contact list or custom object or account)

AccountThe Eloqua object to export data (contact list or custom object or account)
Add a prefix to the identifier
Check if you want to modify the identifier value by adding a prefix value to the identifier column. This option is supported for an identifier column with a data type of string type
Prefixstring valueThis option is supported for an identifier column with a data type of string. The prefix value will be added to identify the column value. 
List/Object name to import

Custom object names can be case-insensitive.

Group account name that can be case insensitive (optional)

The contact list name or object name for the exported data.

The custom object name that you want to export data into.

Group account name for the exported data

Identifier Field Name

For destination Contact

- Contact's email address (c_emailaddress)
- Contact's external ID (c_externalid)
- Contact's ID (c_id)

The field name is used to identify the contact entity.

If you chose a Contact's email address, you must have the  c_emailaddress field in your query with the Contact's external ID as c_externalid field and the Contact's ID as a c_id field.

For destination Custom Object Data or Account

It is one of the field names from your custom object or account data query.

For example, if your query is:

SELECT col_a, col_b, col_c FROM your_table;

then the value should be col_a or col_b or col_c.

The field name is used to identify the custom object or account row. 
Update Rule
  • Always update
  • Update if the new value is not blank
  • Update if the existing value is blank
  • Use the rule defined at the field level
The rule is used when doing updates on existing data. For more information about this parameter, see the Oracle Eloqua Developer Help Center.
Skip invalid records
If checked, Eloqua will return success even if it encounters data row errors. Otherwise, the job will fail if there are invalid records.
Map custom object to contact
If you checked, the job tries to map a custom object to a contact.
Perform case-sensitive search when mapping custom object to contact
Perform a case-sensitive search when mapping a custom object to a contact.
CDO source fieldColumn name of the field Input the column name of the field to be mapped, as used in the export schema. Required when Map custom object to contact is enabled.
Contact map fieldColumn name of the field Input the contact field for mapping. Required when Map custom object to contact is enabled.
Auto Delete DurationintegerThe length of time (in seconds) before the import definition will be automatically deleted, 0 or greater than 3600

Example Query

Your query requires each resource type to have specific columns and exact column names (case sensitive) and data types. 

  • Custom object or account fields, such as col_a, col_b, and col_c, can be case insensitive.
  • If the custom object or account field name on the Eloqua server contains a space, replace it with an underscore in your query.
  • If your query contains any field name that does not exist in the custom object or account fields name, an error is returned, and the job fails. 


custom object query
SELECT col_a, col_b, col_b 
FROM your_table;

 For example, to export a contact, you could use queries similar to these:

Contact
SELECT c_emailaddress, c_firstname, c_lastname 
FROM contact;
SELECT identifier_type, identifier
FROM table my_table


Optionally Schedule the Query Export Jobs

You can use Scheduled Jobs with Result Export to periodically write the output result to a target destination that you specify.


1. Navigate to Data Workbench > Queries.
2. Create a new query or select an existing query.
3. Next to Schedule, select None.

4. In the drop-down, select one of the following schedule options:

Drop-down ValueDescription
Custom cron...

Review Custom cron... details.

@daily (midnight)Run once a day at midnight (00:00 am) in the specified time zone.
@hourly (:00)Run every hour at 00 minutes.
NoneNo schedule.

Custom cron... Details

Cron Value

Description

0 * * * *

Run once an hour.

0 0 * * *

Run once a day at midnight.

0 0 1 * *

Run once a month at midnight on the morning of the first day of the month.

""

Create a job that has no scheduled run time.

 *    *    *    *    *
 -    -    -    -    -
 |    |    |    |    |
 |    |    |    |    +----- day of week (0 - 6) (Sunday=0)
 |    |    |    +---------- month (1 - 12)
 |    |    +--------------- day of month (1 - 31)
 |    +-------------------- hour (0 - 23)
 +------------------------- min (0 - 59)

The following named entries can be used:

  • Day of Week: sun, mon, tue, wed, thu, fri, sat.

  • Month: jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec.

A single space is required between each field. The values for each field can be composed of:

Field ValueExampleExample Description

A single value, within the limits displayed above for each field.



A wildcard ‘*’ to indicate no restriction based on the field. 

‘0 0 1 * *’ Configures the schedule to run at midnight (00:00) on the first day of each month.
A range ‘2-5’, indicating the range of accepted values for the field.‘0 0 1-10 * *’ Configures the schedule to run at midnight (00:00) on the first 10 days of each month.
A list of comma-separated values ‘2,3,4,5’, indicating the list of accepted values for the field.

0 0 1,11,21 * *’


Configures the schedule to run at midnight (00:00) every 1st, 11th, and 21st day of each month.
A periodicity indicator ‘*/5’ to express how often based on the field’s valid range of values a schedule is allowed to run.

‘30 */2 1 * *’


Configures the schedule to run on the 1st of every month, every 2 hours starting at 00:30. ‘0 0 */5 * *’ configures the schedule to run at midnight (00:00) every 5 days starting on the 5th of each month.
A comma-separated list of any of the above except the ‘*’ wildcard is also supported ‘2,*/5,8-10’‘0 0 5,*/10,25 * *’Configures the schedule to run at midnight (00:00) every 5th, 10th, 20th, and 25th day of each month.
5.  (Optional) You can delay the start time of a query by enabling the Delay execution.

Execute the Query

Save the query with a name and run, or just run the query. Upon successful completion of the query, the query result is automatically imported to the specified container destination.

Scheduled jobs that continuously fail due to configuration errors may be disabled on the system side after several notifications.

Optionally Configure Export Results in Workflow


Within Treasure Workflow, you can specify the use of a data connector to export data.

Learn more at Using Workflows to Export Data with the TD Toolbelt.


Example Workflow for Exporting Contacts

The identifier_field_name accepts the following values:

  • C_EmailAddress
  • C_ExternalID
  • C_ID

The update_rule accepts the following values:

  • always
  • ifNewIsNotNull
  • ifExistingIsNull
  • useFieldRule
_export:
  td:
    database: eloqua_db

+eloqua_custom_object_export_task:
  td>: export_contact.sql
  database: ${td.database}
  result_connection: new_created_eloqua_auth
  result_settings:
    type: eloqua
    target: contact
    identifier_field_name: C_EmailAddress
    list_name: td_shared_list2
    update_rule: always     
    skip_invalid_records: true     
    auto_delete_duration: 3600

Example Workflow for Exporting Custom Object

The custom_object_identifier_field is one of the fields named in the export_custom_object.sql query.

The update_rule accepts the following values:

  • always
  • ifNewIsNotNull
  • ifExistingIsNull
  • useFieldRule

You can get custom_object_name from the Oracle Eloqua Console.

_export:
  td:
    database: eloqua_db
 
+eloqua_custom_object_export_task:
  td>: export_custom_object.sql
  database: ${td.database}
  result_connection: new_created_eloqua_auth
  result_settings:
    type: eloqua
    target: custom_object
    custom_object_identifier_field: {xxxxx}
    list_name: {custom_object_name}
    update_rule: always
    skip_invalid_records: true
    map_custom_object_to_contact: true,
    perform_case_sensitive_search: false,
    cdo_source_field: Email_Address,
    contact_map_field: C_EmailAddress
    auto_delete_duration: 0


Example Workflow for Exporting Account

The account_identifier_field is one of the fields named in the export_account.sql query.

The update_rule accepts the following values:

  • always
  • ifNewIsNotNull
  • ifExistingIsNull
  • useFieldRule

You can get the group_account_name from the Oracle Eloqua Console.

_export:
  td:
    database: eloqua_db
 
+eloqua_account_export_task:
  td>: export_account.sql
  database: ${td.database}
  result_connection: new_created_eloqua_auth
  result_settings:
    type: eloqua
    target: account     
    account_identifier_field: {xxxxx}
    list_name: {group_account_name}
    custom_identifier: true
    prefix_identifier: prefix_value
    update_rule: always
    skip_invalid_records: true
    auto_delete_duration: 0



  • No labels