Discussions
Eloqua 18B Sneak Peek: Opportunities are coming to the Bulk API
Original Blog Post Date: Apr 30, 2018 9:32:08 PM
With the Oracle Eloqua 18B release Opportunities are being added to the Bulk API. I am going to walk-through an end to end scenario with examples, but before I do that here are some of the highlights:
- Opportunity fields will be discoverable via the Bulk API
- There are two sets of endpoints, one set to import Opportunities, and the other set to link Opportunities to Contacts
- Opportunities can be linked directly to Contacts, or via Accounts
With the key takeaways highlighted, let's now take a look at this new functionality in action. I'm going to walk-through discovering Opportunity fields, using those fields to import Opportunities, and completing the scenario by linking the Opportunities to Contacts. Along the way we'll cover some requirements and notes as well.
Let's start by retrieving Opportunity fields:
Request
GET /api/Bulk/2.0/opportunities/fields
Response
{ "items": [ { "name": "Opportunity ID", "internalName": "RemoteOpportunityID", "dataType": "string", "hasReadOnlyConstraint": false, "hasNotNullConstraint": true, "hasUniquenessConstraint": false, "statement": "{{Opportunity.Id}}", "uri": "/opportunities/fields/38" }, { "name": "Opportunity Name", "internalName": "OpportunityName", "dataType": "string", "hasReadOnlyConstraint": false, "hasNotNullConstraint": true, "hasUniquenessConstraint": false, "statement": "{{Opportunity.Field(Name)}}", "uri": "/opportunities/fields/39" }, { "name": "Created Date", "internalName": "CreatedDate", "dataType": "date", "hasReadOnlyConstraint": false, "hasNotNullConstraint": true, "hasUniquenessConstraint": false, "statement": "{{Opportunity.CreatedAt}}", "uri": "/opportunities/fields/40" }, { "name": "Stage", "internalName": "OpportunityStageID", "dataType": "string", "hasReadOnlyConstraint": false, "hasNotNullConstraint": true, "hasUniquenessConstraint": false, "statement": "{{Opportunity.Field(Stage)}}", "uri": "/opportunities/fields/41" }, { "name": "Amount", "internalName": "OpportunityAmount", "dataType": "number", "hasReadOnlyConstraint": false, "hasNotNullConstraint": true, "hasUniquenessConstraint": false, "statement": "{{Opportunity.Field(Amount)}}", "uri": "/opportunities/fields/42" }, { "name": "Currency", "internalName": "OpportunityCurrencyID", "dataType": "string", "hasReadOnlyConstraint": false, "hasNotNullConstraint": false, "hasUniquenessConstraint": false, "statement": "{{Opportunity.Field(Currency)}}", "uri": "/opportunities/fields/43" }, { "name": "Close Date", "internalName": "CloseDate", "dataType": "date", "hasReadOnlyConstraint": false, "hasNotNullConstraint": false, "hasUniquenessConstraint": false, "statement": "{{Opportunity.Field(CloseDate)}}", "uri": "/opportunities/fields/44" }, { "name": "Forecast To Close Date", "internalName": "ForecastToCloseDate", "dataType": "date", "hasReadOnlyConstraint": false, "hasNotNullConstraint": false, "hasUniquenessConstraint": false, "statement": "{{Opportunity.Field(ForecastToCloseDate)}}", "uri": "/opportunities/fields/45" }, { "name": "Account Name", "internalName": "AccountName", "dataType": "string", "hasReadOnlyConstraint": false, "hasNotNullConstraint": false, "hasUniquenessConstraint": false, "statement": "{{Opportunity.Field(AccountName)}}", "uri": "/opportunities/fields/46" }, { "name": "Territory", "internalName": "Territory", "dataType": "string", "hasReadOnlyConstraint": false, "hasNotNullConstraint": false, "hasUniquenessConstraint": false, "statement": "{{Opportunity.Field(Territory)}}", "uri": "/opportunities/fields/47" } ], "totalResults": 10, "limit": 1000, "offset": 0, "count": 10, "hasMore": false}Now that we have the statements for the Opportunity fields, let's create an Opportunity import definition:
Before creating the definition, here are some requirements for the Opportunity import definition:
- The following fields are required:
{{Opportunity.Id}}
{{Opportunity.Field(Name)}}
{{Opportunity.Field(Stage)}}
{{Opportunity.Field(Amount)}}
- The identifierFieldName must be set to the {{Opportunity.Id}} field within fields
Request
POST /api/Bulk/2.0/opportunities/importsContent-Type: application/json
Request - Body
{ "name": "Opportunity Import", "fields": { "OpportunityID": "{{Opportunity.Id}}", "OpportunityName": "{{Opportunity.Field(Name)}}", "AccountName": "{{Opportunity.Field(AccountName)}}", "CreatedDate": "{{Opportunity.CreatedAt}}", "Amount": "{{Opportunity.Field(Amount)}}", "CloseDate": "{{Opportunity.Field(CloseDate)}}", "Currency": "{{Opportunity.Field(Currency)}}", "ForecastToCloseDate": "{{Opportunity.Field(ForecastToCloseDate)}}", "Stage": "{{Opportunity.Field(Stage)}}", "Territory": "{{Opportunity.Field(Territory)}}" }, "identifierFieldName": "OpportunityID", "isIdentifierFieldCaseSensitive": false, "isSyncTriggeredOnImport": true}Response
201 Created{ "isIdentifierFieldCaseSensitive": false, "name": "Opportunity Import", "fields": { "OpportunityID": "{{Opportunity.Id}}", "OpportunityName": "{{Opportunity.Field(Name)}}", "AccountName": "{{Opportunity.Field(AccountName)}}", "CreatedDate": "{{Opportunity.CreatedAt}}", "Amount": "{{Opportunity.Field(Amount)}}", "CloseDate": "{{Opportunity.Field(CloseDate)}}", "Currency": "{{Opportunity.Field(Currency)}}", "ForecastToCloseDate": "{{Opportunity.Field(ForecastToCloseDate)}}", "Stage": "{{Opportunity.Field(Stage)}}", "Territory": "{{Opportunity.Field(Territory)}}" }, "identifierFieldName": "OpportunityID", "isSyncTriggeredOnImport": true, "dataRetentionDuration": "P7D", "uri": "/opportunities/imports/18819", "createdBy": "API.User", "createdAt": "2018-04-30T18:05:26.9420890Z", "updatedBy": "API.User", "updatedAt": "2018-04-30T18:05:26.9420890Z"}Now that we have our definition created we can upload data to the definition:
Request
POST /api/Bulk/2.0/opportunities/imports/18819/dataContent-Type: application/json
Request - Body
[ { "OpportunityID": "1ABC", "OpportunityName": "ABC Company", "AccountName": "ABC", "CreatedDate": "2018-04-15 1:15", "Amount": "1000000", "CloseDate": "", "Currency": "USD", "ForecastToCloseDate": "", "Stage": "Prospecting", "Territory": "West" }, { "OpportunityID": "2XYZ", "OpportunityName": "XYZ Company", "AccountName": "XYZ", "CreatedDate": "2018-04-18 5:10", "Amount": "1000000", "CloseDate": "", "Currency": "USD", "ForecastToCloseDate": "", "Stage": "Prospecting", "Territory": "West" }]Response
201 Created{ "syncedInstanceUri": "/opportunities/imports/18819", "status": "pending", "createdAt": "2018-04-30T18:19:51.4928305Z", "createdBy": "EveryOne.Tests", "uri": "/syncs/27418"}As I knew I could upload all data in one request to the data endpoint, I set the isSyncTriggeredOnImport property to true, so the response to the data upload is the created sync. Now I'll retrieve the sync logs to confirm the Opportunities were created:
Request
GET /api/Bulk/2.0/syncs/27418/logs
Response
{ "items": [ { "syncUri": "/syncs/27418", "count": 2, "severity": "information", "statusCode": "ELQ-00130", "message": "Total records staged for import.", "createdAt": "2018-04-30T18:19:53.5400000Z" }, { "syncUri": "/syncs/27418", "count": 0, "severity": "information", "statusCode": "ELQ-00137", "message": "Ready for data import processing.", "createdAt": "2018-04-30T18:19:53.5530000Z" }, { "syncUri": "/syncs/27418", "count": 0, "severity": "information", "statusCode": "ELQ-00101", "message": "Sync processed for sync , resulting in Success status.", "createdAt": "2018-04-30T18:20:56.0730000Z" }, { "syncUri": "/syncs/27418", "count": 2, "severity": "information", "statusCode": "ELQ-00001", "message": "Total records processed.", "createdAt": "2018-04-30T18:19:53.0800000Z" }, { "syncUri": "/syncs/27418", "count": 2, "severity": "information", "statusCode": "ELQ-00041", "message": "Opportunities created.", "createdAt": "2018-04-30T18:19:57.0530000Z" }, { "syncUri": "/syncs/27418", "count": 0, "severity": "information", "statusCode": "ELQ-00042", "message": "Opportunities updated.", "createdAt": "2018-04-30T18:19:57.0530000Z" } ], "totalResults": 6, "limit": 1000, "offset": 0, "count": 6, "hasMore": false}With the count of 2 for the message "Opportunities created." I know the Opportunities in the data upload were successfully imported. If you'd like to see the Opportunities in Eloqua, follow these instructions. To be sure, I searched in Eloqua for the Opportunities I uploaded:
Now that we have Opportunities created in Eloqua, let's create an Opportunity Contact linkage import definition to link them to Contacts:
Before creating the definition, here are some requirements and notes for the Opportunity Contact linkage import definition:
- The {{Opportunity.Id}} field is required within fields
- At least one Contact or Account field is required within fields
- For linking by Account, the Account field set for Account Linkage must be used to match
- There is a maximum of two fields allowed
- identifierFieldName is a read only field set to the linkOpportunitiesMatchFieldName value, and should not specified in definition
Request
POST /api/bulk/2.0/opportunities/contacts/importsContent-Type: application/json
Request - Body
{ "name": "Opportunity Contact Linkage Import", "fields": { "EmailAddress": "{{Opportunity.Contact.Field(C_EmailAddress)}}", "OpportunityID": "{{Opportunity.Id}}" }, "linkOpportunitiesCaseSensitiveMatchField": false, "linkOpportunitiesCaseSensitiveSourceField": false, "linkOpportunitiesEntityType": "Contact", "linkOpportunitiesMatchFieldName": "OpportunityID", "linkOpportunitiesMultipleSourceMatches": true, "linkOpportunitiesSourceField": "EmailAddress", "isSyncTriggeredOnImport": true}Response
201 Created{ "linkOpportunitiesMatchFieldName": "OpportunityID", "linkOpportunitiesSourceField": "EmailAddress", "linkOpportunitiesEntityType": "Contact", "linkOpportunitiesCaseSensitiveSourceField": false, "linkOpportunitiesCaseSensitiveMatchField": false, "linkOpportunitiesMultipleSourceMatches": true, "name": "Opportunity Contact Linkage Import", "fields": { "EmailAddress": "{{Opportunity.Contact.Field(C_EmailAddress)}}", "OpportunityID": "{{Opportunity.Id}}" }, "identifierFieldName": "OpportunityID", "isSyncTriggeredOnImport": true, "dataRetentionDuration": "P7D", "uri": "/opportunities/contacts/imports/18820", "createdBy": "API.User", "createdAt": "2018-04-30T19:15:39.6474055Z", "updatedBy": "API.User", "updatedAt": "2018-04-30T19:15:39.6474055Z"}In our import definition, we are stating that we want to link Contacts by their email address to specific Opportunities, by OpportunityID. Once documentation is published, I will provide a link here. (Documentation now published: API documentation and tutorial) In the mean time, here are descriptions for all the new properties:
| Property | Type | Description |
|---|---|---|
| linkOpportunitiesCaseSensitiveMatchField | boolean | Whether or not to perform a case sensitive search on the match field. |
| linkOpportunitiesCaseSensitiveSourceField | boolean | Whether or not to perform a case sensitive search on the source field. |
| linkOpportunitiesEntityType | string | Specifies the entity of the contact linkage import. Allowed values are "Contact" or "Account". |
| linkOpportunitiesMatchFieldName | string | Specifies the field name for matching. |
| linkOpportunitiesMultipleSourceMatches | boolean | Whether or not imported data will be mapped to multiple matching records. |
| linkOpportunitiesSourceField | string | Specifies the source field name for matching. |
Now that we have our definition created we can upload data to the definition to link Opportunities to Contacts:
Note: All Contacts were confirmed created prior to upload, except "[email protected]", to demonstrate what happens if the Contact does not exist.
Request
POST /api/Bulk/2.0/opportunities/contacts/imports/18820/dataContent-Type: application/json
Request - Body
[ { "OpportunityID": "1ABC", "EmailAddress": "[email protected]" }, { "OpportunityID": "1ABC", "EmailAddress": "[email protected]" }, { "OpportunityID": "2XYZ", "EmailAddress": "[email protected]" }, { "OpportunityID": "2XYZ", "EmailAddress": "[email protected]" }]Response
{ "syncedInstanceUri": "/opportunities/contacts/imports/18820", "status": "pending", "createdAt": "2018-04-30T19:47:34.6272339Z", "createdBy": "API.User", "uri": "/syncs/27421"}As I knew I could upload all data in one request to the data endpoint, I set the isSyncTriggeredOnImport property to true, so the response to the data upload is the created sync. Now I'll retrieve the sync logs to confirm the Opportunities were linked:
Request
GET /api/Bulk/2.0/syncs/27421/logs
Response
{ "items": [ { "syncUri": "/syncs/27421", "count": 4, "severity": "information", "statusCode": "ELQ-00130", "message": "Total records staged for import.", "createdAt": "2018-04-30T19:48:03.0570000Z" }, { "syncUri": "/syncs/27421", "count": 0, "severity": "information", "statusCode": "ELQ-00137", "message": "Ready for data import processing.", "createdAt": "2018-04-30T19:48:03.0800000Z" }, { "syncUri": "/syncs/27421", "count": 0, "severity": "information", "statusCode": "ELQ-00101", "message": "Sync processed for sync , resulting in Warning status.", "createdAt": "2018-04-30T19:48:32.6700000Z" }, { "syncUri": "/syncs/27421", "count": 4, "severity": "information", "statusCode": "ELQ-00001", "message": "Total records processed.", "createdAt": "2018-04-30T19:48:02.3930000Z" }, { "syncUri": "/syncs/27421", "count": 1, "severity": "warning", "statusCode": "ELQ-00085", "message": "Contact does not exist.", "createdAt": "2018-04-30T19:48:07.9070000Z" }, { "syncUri": "/syncs/27421", "count": 3, "severity": "information", "statusCode": "ELQ-00058", "message": "Opportunities mapped to contacts.", "createdAt": "2018-04-30T19:48:07.9070000Z" } ], "totalResults": 6, "limit": 1000, "offset": 0, "count": 6, "hasMore": false}As expected we see a count of 3 with the message of "Opportunities mapped to contacts.", and a count of 1 with the message "Contact does not exist." Now to see the details on which record referenced a Contact that did not exist I make a request to the rejects endpoint:
Request
GET /api/Bulk/2.0/syncs/27421/rejects
Response
{ "items": [ { "fieldValues": { "EmailAddress": "[email protected]", "OpportunityID": "2XYZ" }, "message": "Contact does not exist.", "statusCode": "ELQ-00085", "recordIndex": 4, "invalidFields": [] } ], "totalResults": 1, "limit": 1000, "offset": 0, "count": 1, "hasMore": false}As expected we see [email protected] appear with the message "Contact does not exist." If an Opportunity doesn't exist the message is "Opportunity does not exist." with a statusCode of "ELQ-00084".
We've retrieved Opportunity fields, used those fields to import Opportunities, then completed the scenario by linking the Opportunities to Contacts, and with that we've completed the walk-through of the Opportunities endpoints coming to the Bulk API.
When will I receive the 18B release? Visit the Oracle Eloqua Release Center to view roll out dates.
How about documentation? On May 18, 2018, the applicable Oracle Eloqua Developer Help Center reference and tutorial pages will be published. (Documentation now published: API documentation and tutorial)
Interested in more related to the 18B release?
- For additional developer facing updates, information, and examples, refer to the Oracle Eloqua Developer Changelog on May 18, 2018
- For user-facing features, and product notices, visit the Oracle Eloqua Release Center