API Fetch - Eloqua step by step configuration

Certain API Fetch overview

Certain API Fetch transforms the synchronizing of your event data from various external sources such as social media, registration systems, attendee tracking tools, CRMs, and marketing automation tools, giving you:

• Automated Data Synchronization - to sync data on a schedule
• Comprehensive Centralized Data - from various sources in 1 platform
• Accurate & Available Data - for you to act on when you need it

All while saving you time and effort by removing the need for doing this manually. You schedule it once, and you are done.

Goal of this guide

This guide configures an API Fetch/Eloqua integration with the objective of ingesting Eloqua contacts as Certain registrations for your event.

Prerequisites

• Be both a Signal and an Eloqua customer with the right permissions set
• Have an event created in Certain and ready to get registrations synced into
• Have the Eloqua REST API doc ready.
• Endpoints needed depend on the type of data you need from Eloqua: https://docs.oracle.com/en/cloud/saas/marketing/eloqua-rest-api/rest-endpoints.html

Step 1: Create connections

Log into Signal and select Connections in the left menu.

Create the connection to Eloqua that will be the source of the data.

1. Click on the top right corner. 2. Setup the Eloqua connection according to the screenshot. 3. Click Save & Test. 4. Confirm success by checking the Test Status green checkmark.

Tips (Eloqua connection)

• The Service URL corresponds to the first main part of the URL used to access the Eloqua instance.
• The Username is actually a combination of {Company Name}\{Username}.

Create a connection to Certain that will be the destination of the data.

1. Click again and setup the connection according to the screenshot. 2. Click Save & Test.

Step 2: Create a datasource

Click Data Sources in the left menu.

1. Click on the top right corner. 2. Select Custom Integration. 3. Set a name. 4. Set Source System to Eloqua. 5. Set Mode of Ingestion to API Polling. 6. Click Save.

Next, create an API Fetch configuration.

1. Click API FECTH in the left menu. 2. Set Endpoint Type to REST API Endpoint. 3. Click Save.

Two new options should appear in the left menu.

• REST API ENDPOINT INFO
• ADD REST LOOKUP

REST API ENDPOINT INFO settings

Click REST API ENDPOINT INFO and set:

• Connection: select Eloqua
• API URL: select GET
• Paste the API URL here:
• https://secure.p03.eloqua.com/api/REST/1.0/data/contacts?lastUpdatedAt>'{{C_DateModified}}'&depth={{depth}}&page={{page}}&count={{count}}

Tips (URL parts)

1. Hostname: https://secure.p03.eloqua.com

2. API endpoint: /api/REST/1.0/data/contacts

3. Parameters: ?lastUpdatedAt>'{{C_DateModified}}'&depth={{depth}}&page={{page}}&count={{count}}

More details here:

• https://docs.oracle.com/en/cloud/saas/marketing/eloqua-rest-api/op-api-rest-1.0-data-contacts-get.html

• Pagination Scheme: select from the list depending on how the data is retrieved.
• In this case, Eloqua uses Page No. & Page Size.
• Select Substitute pagination variables already present in the URL since the pagination variables are already in the URL.
• The pagination variables are depth, page, and count.
• Set the Pagination Execution Time Limit.

Note (Pagination time limit)

• This time limit specifies the maximum amount of time that the API is allowed to run before timing out.
• If the API times out before completing its task, any data pages that were retrieved before the timeout will be processed.
• The incremental checkpoints will be updated appropriately to reflect where the API left off.
• This ensures that the next pull will pick up data from where it was dropped.

• Under Incremental Data Collection URL Parameters: check the Enable Checkpointing checkbox.
• Update Checkpoint Request Parameter Name to C_DateModified.

Note (Checkpoint meaning)

• The checkpoint indicates a specific point in the run when the user wants the API to pause and save progress.
• The user can then resume the run from that point at a later time.
• This is useful when updating data incrementally.
• This can help avoid having to start over if the process fails.

Note (Epoch and first run)

• The Date Modified variable uses an epoch.
• A value for the first run must be supplied.
• Based on that value, the API returns the data.
• On the next time the API runs, it picks up from the JSON Field Path in Response.
• The response is saved whenever the API runs.
• The saved response is used for the next run.
• This prevents getting old records every time.
• The API gets records based on the last timestamp that was saved.

VARIABLES setup

Set the variables in the data source.

1. Click VARIABLES in the left menu. 2. Type in depth. 3. Check the External checkbox. 4. Click Add. 5. Click Save.

RESPONSE SCHEMA SETUP setup

1. Click RESPONSE SCHEMA SETUP in the left menu.

2. Within Sample JSON from Data Source... copy the JSON sample below.

{
"elements": [
{
"type": "Contact",
"currentStatus": "Awaiting action",
"id": "2119",
"createdAt": "1444748965",
"depth": "partial",
"name": "mabraham@r123test.certain.com",
"updatedAt": "1515654982",
"accountName": "Strickland Propane",
"address1": "Reston",
"businessPhone": "703-438-2052",
"city": "Reston",
"country": "AR",
"emailAddress": "mabraham@r123test.certain.com",
"emailFormatPreference": "unspecified",
"firstName": "Magid????Magid????123",
"isBounceback": "false",
"isSubscribed": "true",
"lastName": "SCHR??DERSCHR??DER",
"postalCode": "20190",
"province": "VA",
"subscriptionDate": "1515654982",
"title": "BY"
}
],
"page": 1,
"pageSize": 1,
"total": 699672
}

Note (Schema start)

• The easiest way to start setting up a Schema is to obtain a sample JSON from the source.

3. Add Jolt Expression.

[
{ "operation": "shift",
"spec": {
"elements": {
"*": {
"type": "elements[&1].type",
"currentStatus": "elements[&1].currentStatus",
"id": "elements[&1].id",
"createdAt": "elements[&1].createdAt",
"depth": "elements[&1].depth",
"name": "elements[&1].name",
"updatedAt": "elements[&1].updatedAt",
"accountName": "elements[&1].accountName",
"emailAddress": "elements[&1].emailAddress",
"emailFormatPreference": "elements[&1].emailFormatPreference",
"firstName": "elements[&1].firstName",
"isBounceback": "elements[&1].isBounceback",
"isSubscribed": "elements[&1].isSubscribed",
"lastName": "elements[&1].lastName",
"subscriptionDate": "elements[&1].subscriptionDate",
"title": "elements[&1].title"
}
},
"page": "page",
"pageSize": "pageSize",
"total": "total"
}
}
]

Note (Jolt “shift”)

• For Jolt Transformations, the shift operation is used to change the structure of a JSON.
• The operation keeps the values contained in that same JSON.
• The operation navigates the JSON structure to the field or object where a value is wanted.
• The operation informs where this value should be placed in a new JSON that is wanted.

4. Select Parse JSON. 5. Under Select the list element that contains the data select elements. 6. Click Save.

Step 3: Create an inbound flow

Click Inbound Flows in the left menu.

1. Click on the top right corner. 2. Configure the inbound flow:

• Give a name to the inbound flow.
• Select the Data Source that was created in step 2.
• In the example, the data source is Eloqua.
• Click Save.

3. Select VARIABLES in the left menu. 4. Enter variable name eventcode. 5. Set as External. 6. Click Add and then click Save.

7. Select ADD DESTINATION in the left menu. 8. Select Certain. 9. In Connection Used, select the Certain connection created in step 1. 10. In Available Actions for this Connection, select Bulk Registration Upsert.

Note (Bulk Registration Upsert)

• This action will pull bulk contacts from Eloqua.
• This action will push bulk contacts to a Certain event as registrations.

11. In Available Mappings, click New Mapping. 1. For eventCode, select the eventcode variable created. 2. For contactd, select elements.id. 3. For defaultRegStatusForNewRegistration, type in New. 4. For pin, select elements.id. 5. For profileIdentifier, enter SYNC_EMAIL. 6. Map other fields as needed. 7. Save 12. Click Save Destination.

Step 4: Schedule API Fetch

Click Schedule API Fetch in the left menu.

1. Click on the top right corner.

2. Configure the schedule:

• Name the schedule.
• Click ASSIGN DATA SOURCE in the left menu.
• In Data Sources, select the Eloqua data source created in Step 2.
• In Datasource Variables, add a value for the depth variable.

Note (depth values)

• Depth can be minimal, partial, or complete.
• The Eloqua REST API documentation can be referenced for more details on fields returned.

• Click Save.

3. Click ASSIGN INBOUND FLOWS in the left menu.

• Select the Inbound Flow created on Step 3.
• In Inbound Flow Variable, enter the Event Code where you would want the Eloqua contacts to be stored as registrations.
• Click Save.

4. Click SETUP SCHEDULE in the left menu.

• Select Frequency.
• Set the Start Date, End Date, Preferred Start Time, and Timezone.
• Set the Initial Value for C_DateModified.

Note (epoch date conversion)

• If the format is in epoch, convert the date and time stamp online.

Note (first run value)

• Initially, Signal would not know what records to fetch.
• The value set here needs to be provided for the first run.

• Click Save.

Optional: force execute

Optionally, force execute the scheduled API Poll.

1. Go back to Schedule API Fetch. 2. Select All Schedules. 3. Click the force execute button.

Note (schedule edit behavior)

• Once a schedule is created, the schedule can no longer be edited.
• The solution is to delete the schedule.
• A new schedule can then be created.

Activity output

Under Activity, find:

• The Execution ID
• Whether the run is successful
• Whether the run returns an error