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. You will find here the endpoints needed depending 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.

 

We are first going to create the connection to Eloqua that will be the source of our data. Click   on the top right corner.

 

Setup the Eloqua connection according to the below screenshot.

 

Tips:

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

Click Save & Test. If successful you will get a green checkmark next to Test Status.

 

We also need a connection to Certain that will the destination of our data.

Click again on and setup the connection as below then click Save & Test.

 

Step 2: Create a datasource

Click on Data Sources in the left menu.

 

Click on in the top right corner.

Select Custom Integration.

Set a name, and then set Source System to Eloqua and Mode of Ingestion to API Polling and click Save.

 

Then click on API FECTH in the left menu.

 

Set Endpoint Type to REST API Endpoint and click Save.

Now in the left menu you should notice 2 new options: REST API ENDPOINT INFO and ADD REST LOOKUP

Click REST API ENDPOINT INFO and set the following:

• 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: These are the parts of the URL above:

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 you want the data to be retrieved. In  our case, Eloqua uses Page No. & Page Size.

Note: Pagination is a process that's used to divide a large dataset into smaller chunks (pages). It is essential if you're dealing with a lot of data and endpoints.

• Select Substitute pagination variables already present in the URL since the pagination variables are already in the URL (i.e. depth, page, and count)
• Set the Pagination Execution Time Limit

Note: 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, and 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: The checkpoint indicates a specific point in the run when the user wants the API to pause and save the progress, allowing them to resume the run from that point at a later time. This is particularly useful when updating data incrementally and can help avoid having to start over if the process fails.

Note: Since we have an epoch for the Date Modified variable, we will have to supply a value for the first run and based on that value, the API will return the data and the next time it runs, it will pick up from the JSON Field Path in Response. Whenever we'll run the API, this response will be saved and that will be used for the next run so that we don’t end up getting old records every time. It will get the records based on the last timestamp which was saved.

 

Now we are going to set the variables in the data source. Click on VARIABLES in the left menu.

 

Type in depth, check the External checkbox and click Add. Then hit Save.

 

Click on RESPONSE SCHEMA SETUP in the left menu.

• Within Sample JSON from Data Source... copy the below json sample

{ 
   "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: The easiest way to start setting up a Schema is to obtain a sample JSON from the source.

 

• 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: For Jolt Transformations, the ‘shift’ operation is Used to change the structure of a JSON, keeping the values contained in that same JSON. Its use consists in navigating the JSON structure to the field or object that we want to get its value and then informing us where this value should be placed in the new JSON that we want.

 

• Select Parse JSON.
• Under Select the list element that contains the data select elements.
  
• Click Save.

 

Step 3: Create an inbound flow

Click on Inbound Flows in the left menu.

 

Then click on in the top right corner and configure it as follow:

• Give a name to the inbound flow.
• Select the Data Source that was created in step 2 (in our example it is Eloqua) and Save.

 

• Select VARIABLES in the left menu.
• Enter variable name eventcode and set as External, then Add and click Save.

 

• Select ADD DESTINATION in the left menu.
• Select Certain.
• In Connection Used, select the Certain connection that was created in step 1
• In Available Actions for this Connection, select Bulk Registration Upsert

Note: This action will pull bulk contacts from Eloqua and push them to a Certain event as registrations.

• 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
• Save Destination

 

Step 4: Schedule API Fetch

Click on Schedule API Fetch  in the left menu.

 

Then click on in the top right corner.

• 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 can either be minimal, partial, or complete. You may refer to Eloqua’s REST API documentation for more details on the fields returned.

• • Click Save.

 

• 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.

 

 

• 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: If format is in epoch, you may need to convert the date and time stamp online.

Note: Initially, Signal would not know what records to fetch. Value set here needs to be provided for the first run.

• • Click Save

 

Optionally, you can also force execute the scheduled API Poll by going back to Schedule API Fetch, select All Schedules, and then click the force execute button.

Note: Once a schedule is created, you can no longer edit it. What you can do would be to delete it and create a new schedule

 

 

• Under Activity, you will find the Execution ID, and whether the run is successful or if it returns an error