How to import and export bulks of data using the Eloqua Bulk API and Postman

The Eloqua Bulk API is very complex and – well, to be honest – its documentation isn't the best. But it’s also a very powerful API which allows you to import/export or filter bulks of data.

In this blogpost we will demonstrate a simple use case for the Bulk API, how to test it and give some examples on how to handle troubleshooting.

Every import / export with the Bulk API follows this pattern:

  1. Create an import / export definition
  2. Moving the data to the staging area
  3. Moving the data to the final destination (synchronize the database)

When using this pattern you have the advantage of fast requests while other longer actions are performed asynchronously.

Testing the Bulk API with „Postman“

Postman is a very useful tool when it comes to creating and sending HTTP Requests for testing. It allows you to simply test requests without coding a single line, which makes APIs easier to understand for developers and non-developers alike.

To work with the Bulk API, we need to know which Base-URL our Eloqua Instance is referenced to. Getting this URL is pretty simple: We use a ‚GET‘ on the following URL „https://login.eloqua.com/id“. For the sake of simplicity, we choose Basic Authorization Header to authenticate with Eloqua.

For HTTP basic authentication, each request must include an Authentication header with a Base64 encoded value. After clicking the second tab ‘Headers’, two input fields appear, in the first we choose the type of Header, which is ‘Authorization’. In the second input field, we have to enter “Basic” and a Base64 encoded String which has the following format:

siteName + \ + username + : + password
for example: Company\user.name:password

You can find various online tools for Base64 encoding which will help you with converting this string; just search for „Base64 encoder“.

Our example looks like this: “Q29tcGFueVx1c2VyLm5hbWU6cGFzc3dvcmQ=”.

Now, copy the encoded string and paste it into the value input field, but write “Basic” in front of it.

Header: Authorization
Value: “Basic Q29tcGFueVx1c2VyLm5hbWU6cGFzc3dvcmQ=”

Finally, hit the ‘Send’ button to send the request. The response should look like this:

{
  "site": {
          "id": 42,
          "name": "MyTestInstance"
  },
 "user": {
        "id": 314,
        "username": "user.name",
        "displayName": "user name",
        "firstName": "user",
        "lastName": "name",
        "emailAddress": "user.name@Company.com"
  },
 "urls": {
       "base": "https://secure.p01.eloqua.com",
       "apis": {
       "soap": {
       "standard": "https://secure.p01.eloqua.com/API/{version}/Service.svc",
       "dataTransfer": "https://secure.p01.eloqua.com/API/{version}/DataTransferService.svc",
       "email": "https://secure.p01.eloqua.com/API/{version}/EmailService.svc",
       "externalAction": "https://secure.p01.eloqua.com/API/{version}/ExternalActionService.svc"
       },
       "rest": {
       "standard": "https://secure.p01.eloqua.com/API/REST/{version}/",
       "bulk": "https://secure.p01.eloqua.com/API/Bulk/{version}/"
       }
       }
  }
 }

So our BaseURL is “https://secure.p01.eloqua.com”. If you already know which API you want to use, you can just select it from the response. In our case we want to use the Bulk API Version 2.0, therefore our URL is “https://secure.p01.eloqua.com/BULK/2.0/”.

After all this preparation, we are now ready to dive into our use case: Importing new Contacts

1. Create an import / export definition

The Import Definition is basically the mapping used by the Bulk API to match (y)our import with the existing fields in Eloqua.

First of all, we need to set up the required Http method. In this case it's a Http POST. Just use the dropdown menu and select “POST”.

Step two is to enter the Request URL which is a combination of the BaseURL “https://secure.p01.eloqua.com/BULK/2.0/”  and the endpoint “contacts/imports/”.

The third step is to add a second header: “Content-Type” with the value: “application/json”. Now we have two headers: Authorization and Content-Type.

In the last step we have to send our Import Definition within the body. Just click on the Body tab, check the ‚raw‘ checkbox and paste it into the text field.

{
 "name": "Contact Import",
 "fields": {
       "salutation":"{{Contact.Field(C_Salutation)}}",
         "firstName": "{{Contact.Field(C_FirstName)}}",
         "lastName": "{{Contact.Field(C_LastName)}}",
         "emailAddress": "{{Contact.Field(C_EmailAddress)}}"
 },
 "identifierFieldName": "emailAddress",
 "isSyncTriggeredOnImport" : "false"
 }

Although a 'name‘ is required for every import, it’s not needed or used again, so you shouldn’t worry about the name giving.

„fields“ is the most important parameter of the Import Definition because it defines which fields in Eloqua will be mapped to the data we want to import. The „identifierFieldName“ should be a field in Eloqua which is unique, to ensure that no wrong records get updated.

The option „isSyncTriggeredOnImport“ ensures that the import we POST into the staging area automatically gets synced into the Eloqua Database when the POST to the staging area is completed. So every one of our contacts will have a salutation, a first name, a last name and an email address.

Now, we hit the 'send‘-button and this is how it should look like:

{
 "name": "Contact Import",
 "fields": {
         "salutation": "{{Contact.Field(C_Salutation)}}",
         "firstName": "{{Contact.Field(C_FirstName)}}",
         "lastName": "{{Contact.Field(C_LastName)}}",
         "emailAddress": "{{Contact.Field(C_EmailAddress)}}"
 },
 "identifierFieldName": "emailAddress",
 "isSyncTriggeredOnImport": false,
 "dataRetentionDuration": "P7D",
 "isUpdatingMultipleMatchedRecords": false,
 "uri": "/contacts/imports/144",
 "createdBy": "Firstname.Lastname",
 "createdAt": "2016-03-15T13:03:57.6999123Z",
 "updatedBy": "Firstname.Lastname",
 "updatedAt": "2016-03-15T13:03:57.6999123Z"
 }

2. Moving the data to the staging area

Now, after successfully POSTing the Import Definition to Eloqua, we need to POST the data we want to import. We need the „uri“ from our response of the Import Definition which is „/contacts/imports/144“. Just append the “/144” to the RequestURL.

Headers: Authorization  Value: “Basic Q29tcGFueVx1c2VyLm5hbWU6cGFzc3dvcmQ=”
Content-Type Value: “application/json”

Finally, paste your data json into the „Body“.

[{
 "salutation": "Mr.",
 "firstName": "Carlos",
 "lastName": "Carlsen",
 "emailAddress": "carlos@example.com"
}, {
 "salutation": "Mrs.",
 "firstName": "Sophie",
 "lastName": "Smith",
 "emailAddress": "sophie@example.com"
}]

This time, the response should be a „Http 204 No Content“ status message. If we get this response, the data is in the staging area and ready to be synchronized into the Eloqua Database

3. Moving the data to the final destination

Now we are able to synchronize the data. (If we would have set the option „isSyncTriggeredOnImport“:“true“ instead of „false“ the synchronization would be done automatically.)

To sync the data, we a POST on the following RequestURL: “https://secure.p01.eloqua.com/BULK/2.0/syncs“

The Headers are still the same, only the body changes to :

{
 "syncedInstanceUri": "/contacts/imports/144"
 }

We should get a „201 Created“ Http status code and the response-body looks like this:

{
 "syncedInstanceUri": "/contacts/imports/144",
 "status": "pending",
 "createdAt": "2016-03-15T13:04:20.1675119Z",
 "createdBy": "Firstname.Lastname",
 "uri": "/syncs/37"
 }

To ensure that everything worked out perfectly, we need to do one last request, a ‘GET’ request to check the „status“.

HttpMethod: GET
RequestURL: https://secure.eloqua.com/api/bulk/2.0/syncs/37
Headers: Authorization & Content-type

If everything worked right you get a response like this:

{
 "syncedInstanceUri": "/contacts/imports/144",
 "syncStartedAt": "2016-03-15T13:04:20.0200000Z",
 "syncEndedAt": "2016-03-15T13:04:24.1970000Z",
 "status": "success",
 "createdAt": "2016-03-15T13:04:19.9500000Z",
 "createdBy": "Firstname.Lastname",
 "uri": "/syncs/37"
 }

Troubleshooting

If the status is “warning”, the reason could be a typo in your data json, for example when you send more data than you originally declared in the Import Definition. The most common errors are typos.

The correct data will be successfully imported while the not declared data will be ignored. Best practise should be to get a “success”-status instead of ignoring “warnings”.

You can check out the logs of the sync with another ‘GET’ request, just append “/logs/” to the RequestURL.

The response is a json which shows how the data went through every step.

In case of a “warning” or an “error”, just copy the “statusCode” beyond it and check it against Eloqua's Bulk Documentation, where all “Bulk Status Codes” are listed. (http://docs.oracle.com/cloud/latest/marketingcs_gs/OMCAC/APIRequests_EloquaStatusCodes.html)

Or maybe there was already an error with the request. For example, instead of a “200 OK” status code we get a “The requested resource was not found.” or “404 Endpoint not found” status. If the API fails to validate a request, it will respond with a validation error message describing the issue.

It’s basically just another typo, so always make sure that you enter everything correctly.

For all HTTP status codes you may have a look at :
http://docs.oracle.com/cloud/latest/marketingcs_gs/OMCAC/APIRequests_HTTPStatusCodes.html

There are also explanations for “Validation Errors”.

Another very useful advantage of Postman is the History tab, where you can search for older requests. We often use an older request from our history and just edit and adapt it. That way we don’t have to re-enter everything and Base64-encode our complete login-credentials again.

The Eloqua Bulk API is very awesome. With a bit of patience and by testing your use cases with Postman, you can save a lot of time searching for errors in your own code. Postman has a free basic version – in our opinion it’s perfectly adequate for testing API’s. There is also a “Cloud”-Version with some more features, which includes a monthly fee.

Sources:

https://www.getpostman.com/

https://docs.oracle.com/cloud/latest/marketingcs_gs/OMCAC/api-bulk-2.0-syncs.html