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.
Below is a step by step guide to help you configure 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.
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 the top right corner.
Select Custom Integration.
Set a name. Set Source System to Eloqua. Set Mode of Ingestion to API Polling. 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.
REST API ENDPOINT INFO setup
Click REST API ENDPOINT INFO and set the following:
- Connection: select Eloqua
- API URL: select GET
- Paste the API URL here:
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 you want the data to be retrieved.
In our 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 (i.e. depth, page, and count).
Note: Pagination
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.
- Set the Pagination Execution Time Limit
Note: Pagination Execution 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: Checkpointing
The checkpoint indicates a specific point in the run when the user wants the API to pause and save the progress. This allows the user to resume the run from that point at a later time.
This is particularly useful when updating data incrementally. This can help avoid having to start over if the process fails.
Note: Epoch for Date Modified
Since we have an epoch for the Date Modified variable, we will have to supply a value for the first run. Based on that value, the API will return the data. The next time it runs, it will pick up from the JSON Field Path in Response.
Whenever we will run the API, this response will be saved. That saved response will be used for the next run so that we do not end up getting old records every time.
It will get the records based on the last timestamp which was saved.
Variables setup
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.
Response schema setup
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: Start with a sample JSON
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: Jolt Transformations shift
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. Then it informs 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 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 and Save.
In our example it is Eloqua.
- 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: Bulk Registration Upsert
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 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 values
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: epoch date conversion
If format is in epoch, you may need to convert the date and time stamp online.
Note: first run value
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: schedule edit limitation
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