Getting started with REST APIs across Marketo, Eloqua and OData

In this tutorial, learn how to access data from Marketo and Eloqua using their REST API. We'll also compare the usage of this API against the OData API for any SaaS or Relational or Big Data sources.

Developers are spending an increasing amount of time working with a growing number of APIs. With each new API you need to consume, it's a great opportunity to revisit the power of OData, an ISO/IEC approved, OASIS standard that defines a set of best practices for building and consuming RESTful APIs.

Let's start with a specific example in accessing data from marketing systems' REST APIs. Many devs are rebuilding Marketo integrations from SOAP to REST, and it's a perfect opportunity to experience the benefits of OData if you need to support multiple integrations. The problem with consuming multiple proprietary APIs is that you have to create separate code bases, test cases, maintenance schedules in case there are any changes to these APIs.

To illustrate, we will go through how you can access the data from Eloqua and Marketo using their REST API and eventually discuss how developing against the OData API is a better choice for many applications. OData is not limited to these marketing apps, and can be generated for any SaaS, Relational or Big Data sources using Progress DataDirect Hybrid Connectivity solutions, which is, as I call it, the Midas touch for proprietary REST/SOAP APIs.

Note that the endpoints in these examples were generated on an instance with the URL service.datadirectcloud.com. Your URL will be different.

Sections:
1. Authentication
1.1.Marketo Authentication
1.2.Eloqua Authentication
1.3.OData Authentication
2. Fetching Metadata
2.1.Marketo Metadata
2.2.Eloqua Metadata
2.3.OData Metadata
3. Retrieving Data
3.1.Marketo Query
3.2.Eloqua Query
3.3.OData Query
4. Creating Items
4.1.Marketo Create
4.2.Eloqua Create
4.3.OData Create
5. Updating Items
5.1.Marketo Update
5.2.Eloqua Update
5.3.OData Update
6. Deleting Items
6.1.Marketo Delete
6.2.Eloqua Delete
6.3.OData Delete

1. Authentication

Let’s begin this with the most important thing when dealing with APIs, Authentication, as we don’t want any unauthorized person to access the data. Let’s see how different these APIs handle and how Progress Hybrid Data pipeline handles authentication for the OData API it generates.

1.1.Marketo Authentication

To get authenticated with Marketo API, it involves two steps.

Step 1: Get Access Token

To get the Access Token that you need to access/modify the Marketo data, you need to get API credentials (Client Id and Client Secret tokens) from your Marketo admin. For more details on how you can create those credentials if you are the admin, instructions can be found here. Once you have the API credentials you need to request an access token using those credentials which will allow you to access the Lead and Asset database endpoints that will allow you to interact with objects such as Opportunities, Leads etc.,

To get the access token, you need to send a GET request to Marketo servers in the following format.

 

If you are authenticated successfully, you will be returned an access token with expiry time in seconds as shown below.

 

You can access the sample codes for authentication in C#, Java and Ruby

Step 2: Using the Access Token

The access token that you have received from the Step 1 should be included in every REST call that you make to successfully access/modify the data. You can either include it in the HTTP header or use it as query string parameter as shown below.

HTTP header
Authorization: Bearer 805913d0-fc75-48f9-ad9b-2f5781a0acc9:ab

Query Parameter
https://Your-endpoint/rest/v1/leads/describe.json?access_token=access-token

 

1.2.Eloqua Authentication

Eloqua’s REST API supports two types of authentication, Basic Authentication and OAuth.

Basic Authentication

To authenticate using the Basic authentication against Eloqua REST API you need to calculate a base-64 encoded string of a string that contains your company name, user name and password in the below format.

 

 

For example if the company name is ‘ProgressCorp’ and username is ‘testuser’ and password is ‘testpassword’, then you would have

 

Calculate Base64 for that string, which in this case would be UHJvZ3Jlc3NDb3JwL3Rlc3R1c2VyOnRlc3RwYXNzd29yZA==

You can now use this encoded string to authenticate against Eloqua API. For example, let’s say you want to know about your organizations base URL for accessing Eloqua API, you can send a GET request as below.

 

GET

https://login.eloqua.com/id

HTTP Header:

Authorization: Basic UHJvZ3Jlc3NDb3JwL3Rlc3R1c2VyOnRlc3RwYXNzd29yZA==

 

RESPONSE

In the response, you will find REST endpoints under urls object as shown below.

 

OAuth

Eloqua uses OAuth 2.0 for accessing the resources of a user from a third-party application while securing the credentials of the user who authorizes the access. To know more about on how to authenticate using OAuth for Eloqua, you can visit this page for detailed instructions.

 

1.3.OData Authentication

You can implement Basic Auth, OAuth or any custom based authentication for OData, if you write your own OData service for your data source. When you use Progress Hybrid Data pipeline for generating OData service for your data source, you will be using Basic Authentication. Following is an example of the Authentication.

GET

https://service.datadirectcloud.com/api/odata/{data-source-name}/$metadata

Authentication: Basic base_64_encode (username + “:” + password)

When you use Progress Hybrid Data pipeline, to generate the OData service for your data sources like Eloqua, Marketo, Salesforce etc., all you need to know is to do a basic authentication to access your data from your data sources through OData. You would find yourself no more in need to research and learn to use all the crazy authentication schemes that are implemented by proprietary APIs. Once you have the single piece of code written for OData generated by Progress Hybrid Data pipeline, you wouldn’t have to write for every data source as you would write for the proprietary API saving you time in development and maintenance.

2. Fetching Metadata

Before accessing the data, you might want to know what kind of data that you are dealing with. In this section, we will look at how you can access the metadata from the Marketo, Eloqua API, and compare it with how OData handles this.

 

2.1.Using Marketo API

To access metadata of the objects from Marketo API, you need to use the Describe method to get the details for the object. Following is an example of how you can access the metadata for Leads object.

 

GET

https://<your_endpoint>/rest/v1/leads/describe.json?access_token=<access_token>

 

RESPONSE

 

As you can see the metadata from Marketo lets you know the field names for both REST and SOAP APIs, their data type, length if applicable and if the field is read only or not. To learn more about the Describe method in Marketo API, you can visit this page in documentation.

 

2.2.Using Eloqua API

In terms of metadata, the Eloqua API has a very limited support. It doesn’t have any endpoint that lets you access the metadata under REST, but it does have an endpoint in its Bulk API which will let you access metadata for selected few objects. Following us an example of accessing the metadata from Bulk API endpoint for the object Contact, which is one of the object that is supported under Bulk API

 

GET

https://<eloqua-endpoint>/API/BULK/2.0/contacts/fields

 

RESPONSE

 

 

The metadata information that you receive from the Eloqua API has information about the field’s name, data type, if its read only, if it’s not a null field and if it is unique for that object. To learn more about this API, you can visit the Eloqua documentation.

 

2.3.Using OData API

 

OData has a standard way of exposing the metadata whatever might be the data source that it has been built up on. Unlike the proprietary APIs which may or may not expose metadata, and even if they do you might not be getting all the properties for the fields in the object. This is pretty evident from the above two examples of Eloqua and Marketo, where none of the API gives you the full metadata for an object, they only expose partial metadata.

So, the question now is how does OData API, that can be generated with Progress Hybrid Data Pipeline solve this and improve your productivity? When you configure your SaaS data sources like Marketo, Eloqua or Salesforce or even on-premise databases like Oracle or SQL Server in Progress Hybrid Data Pipeline and enable OData on that data source, you will be able to access full metadata for that object. It includes the name, data type (16 data types supported by OData), precision, nullability, MaxLength, Primary key and any foreign key relationships that might exist. And the whole metdata document is returned in CSDL format which is represented using standard XML.  Following is an example of metadata returned from the OData service for an object product.

 

GET

http://<Odata-service-URI>/$metadata

 

RESPONSE

 

To access the full metadata document of a sample service, you can visit this URL. When you use Progress Hybrid Data pipeline and configure your SaaS sources, you will have individual OData base URL’s for these sources and all you must do is simply send a GET request in the following format to access metadata and the best when you get the response, the metadata document is in a standard CSDL format represented using XML.

 

GET

http://<Odata-service-URI>/$metadata

 

As the response from Progress Hybrid Data Pipeline is based on standards based OData API, you can write the code once for accessing metadata and re-use it for data sources like Eloqua, Marketo, Google Analytics, Salesforce etc., which will improve your productivity as you need not spend a lot of time researching, developing and maintaining code for accessing metadata from the proprietary APIs separately.

 

3. Retrieving Data

3.1.Using Marketo API

There are two ways you can retrieve data from the objects using Marketo API. One is using the ID and another is using a filter. Note that filters are only possible on non-Boolean field and if you are dealing with custom fields you can only filter on the fields with data type string, integer and email. As an example, let’s consider the lead object, we can fetch leads using two methods Get Lead by Id and Get Leads by Filter Type.

3.1.1 Get Leads By ID

To get items by Id, you need to send a GET request in the following format. The following is an example of how you can fetch a lead item with ID.

 

GET

https://<Your endpoint>/rest/v1/lead/{lead-id}.json?access_token=11e2d738-ff80-4720-9f0e-d9b4ff7efa69:ab

 

RESPONSE

 

You can access the code samples for getting the leads by Id in C#, Java and Ruby. For more information on this you can visit the Marketo Developers guide.

3.1.2 Get leads by filter

Another way of fetching item is by using filters on the fields and fetching the items that match the criteria. Following is an example to get leads based on filter, you need to send a GET request as below.

 

GET

 

 

RESPONSE

 

You can access the code samples for getting the leads by Id in C#, Java and Ruby. For more information on this you can visit the Marketo Developers guide.

 

3.2.Using Eloqua API

 

You can fetch data from Eloqua in two ways. One is you fetch the item using ID field and other is fetching list of all the items. If you want to fetch items based on a filter criteria, you can do that in the later way with the help of URL parameters. Let’s see how you can do these with some examples.

3.2.1 Single item retrieval

To fetch a single item from an Eloqua’s REST method, you need to send the request with the ID of the item that you want to fetch. Following is an example of how you can retrieve an item from accounts method.

 

GET

 

RESPONSE

 

3.2.2 Retrieving List of items

 

3.2.2a. All items – No Filter

 

You can either fetch list of items without applying any filter conditions and access all the data by paging through the results. To do that just send a GET request to the server as following. For demonstration purposes, let’s see how we can fetch all accounts from Eloqua API

GET

 

RESPONSE

 

 

3.2.2b. Fetching items per criteria

Not every time you need access to all the data at once and is not a recommended way of accessing data when you need a subset of the data that is based on a filter or criteria. You can customize the data that you fetch using URL parameters, so that you can fetch the data that you need. Following are couple of URL parameters supported with examples.

Count: This is used to specify the maximum number of items that you would want to return for that request. Say you wanted to get 15 accounts you can just send a GET request as

 https://<eloqua-REST-endpoint>/API/REST/1.0/data/accounts?count=15

which will return only 15 accounts.

Page: Say you made a GET request which will return 10000 records, the server sends the response in pages with a default page size of 1000. To access the records 1001 to 2000 you should specify the page as 2 to access them. Following is an example of using this to get 100 items on the 4^th page.

https://<eloqua-REST-endpoint>/API/REST/1.0/data/accounts?page=4&count=100

Search: If you would like to fetch data with a filter you need to use this parameter. Say you want to fetch all the accounts with name as ‘Alex’, you can send GET request as following

Syntax: search=’{field}{operator}{value}’

https://<eloqua-REST-endpoint>/API/REST/1.0/data/accounts?search=’name=Alex’

Along with the above parameters, you can also specify sort and dir parameters to sort your results or orderBy to achieve similar results.

To learn more about URL parameters of Eloqua, you can visit this page.

 

3.3.Using OData API

If you know basics of SQL, getting data using OData API is a piece of cake.  You can get the data from OData service in either JSON or XML. If you choose it get it in XML you will get the response in ATOM format. To start off, let’s see how we can access the data using OData. Following is an example of fetching all the records from an object, you need to send a GET request as shown

 

GET

http://{ODataServiceURI}/{ObjectName}?$format=json

Consider the following example,

Service URI: http://services.odata.org/OData/OData.svc

ObjectName: Categories

When you send a GET request to the OData server in the above format, you will get all the items in that object.

GET

 http://services.odata.org/OData/OData.svc/Categories?$format=json

RESPONSE

 

Following are the some of the other parameters that are supported by OData, that helps you in fetching data $orderby, $top, $skip and $filter, $expand, $select and $format. Although most of them are quite a bit common in other APIs, the place where OData shines is $filter and $expand queries.

 

3.3.1. Filter Query Options

$filter query option of OData has the most extensive support which includes logical operators, Arithmetic operations, grouping operators, String functions, Date functions, Math functions and Type functions.

 

Operators

Logical Operators include =, !=, <, > , >=, <=, and, or, not operators. Following are couple of example that illustrate the usage

Equal operator

http://services.odata.org/OData/OData.svc/Suppliers?$filter=Address/City eq 'Redmond'

Greater Than

http://services.odata.org/OData/OData.svc/Products?$filter=Price gt 20

Not

http://services.odata.org/OData/OData.svc/Products?$filter=not endswith(Description,'milk')

To learn more about remaining logical and arithmetic operators, you can visit the OData documentation page.

Functions

Just like SQL, OData supports functions for String manipulation, Dates, Math and Type. Following are couple of examples that illustrates the usage of these functions.

 

Substringof

http://services.odata.org/Northwind/Northwind.svc/Customers?$filter=substringof('Alfreds', CompanyName) eq true

 

Hour

http://services.odata.org/Northwind/Northwind.svc/Employees?$filter=hour(BirthDate) eq 0

 

Round

http://services.odata.org/Northwind/Northwind.svc/Orders?$filter=round(Freight) eq 32d

 

isOf

http://services.odata.org/Northwind/Northwind.svc/Orders?$filter=isof(ShipCountry, 'Edm.String')

 

To learn more about remaining logical and arithmetic operators, you can visit the OData documentation page.

3.3.2. Expand Query options

 

The $expand system query basically lets you access the related resources in line with the retrieved resources. Say for example there are two objects Category and Product, where the relationship between them is defined as each product belongs to a category. If you want to get the information about certain category and products that it is associated with, you may need to execute two requests one for fetching the category details(/Categories(2)) and another for (/Categories(2)/Products). With $expand you can get this information with a single request. Following is an example illustrating that

 

GET

http://services.odata.org/OData/OData.svc/Categories(1)?$expand=Products&$format=json

RESPONSE

 

In the above request, I requested for a Category with Id 2, and asked to bring the related products with that category id using expand query, all within one request. To learn more about $expand query, you can visit the OData documentation

When you use Progress Hybrid Data pipeline to generate OData from your SaaS, Relational sources you will be able to leverage these features and the features that I may not have discussed but are in OData spec. This means you can use OData’s powerful query capabilities even though the native SaaS API doesn’t support them, enabling interoperability even though you work with different data sources.

 

4. Creating Items

4.1.Using Marketo API

 

To create items/records using Marketo API you need to send a POST request to the server in the following format. Following is an example of how you can create accounts using the Marketo API

 

POST

https://<Your-endpoint>/rest/v1/leads.json? access_token=<access_token>

 

BODY

 

There are two important fields that you can observe in the above body, action and lookupField. The field action specifies the type of operation which can be createOrUpdate, createOnly, updateOnly, or createDuplicate and the lookupField specifies the key to use for this operation.

RESPONSE

 

You can access the code samples for creating items in C#, Java and Ruby. For more information on this you can visit the Marketo Developers guide.

 

4.2.Using Eloqua API

 

To create items using Eloqua API, you need to send a POST request with a JSON body as shown below. Following is an example of creating a new account in Eloqua.

 

POST

https://<eloqua-REST-endpoint>/API/REST/1.0/data/account

Content-Type: application/json

BODY

 

RESPONSE

 

4.3.Using OData API

To create item using OData API, you need to send a POST request to the server with the body content as the new entity that’s in same format as the definition. The response if successful returns the created entity and any autogenerated fields. Following is a simple example of creating a Region.

 

GET

https://service.datadirectcloud.com/api/odata/OracleXE/REGIONSES

 

Body

 

RESPONSE

 

 

When you use Progress Hybrid Data pipeline and since it exposes OData, this will be process of creating items regardless of the data source being SaaS or Relational or Big data. To learn more about creating entries/items/records in an object, you can visit the OData documentation.

 

5. Updating Items

5.1.Using Marketo API

To update an existing item using Marketo API, you need to send a POST request like what you did when you create an item. The only difference is the action is now updateOnly. Following is an example of how you can update the items.

 

POST

https://<Your_endpoint>/rest/v1/leads.json?access_token=ea4c232d-d57b-4404-9035-08c8921e726b:ab

 

BODY

 

RESPONSE

 

 

5.2.Using Eloqua API

 

To update items using Eloqua API, you need to send a PUT request in the following format.

PUT

https://<eloqua-REST-endpoint>/API/REST/1.0/data/account/{account-id}

Content-Type: application/json

BODY

 

If the request is successful, you will see the updated item in the response as shown below.

 

RESPONSE

 

To learn more about all the Eloqua’s REST endpoints, visit this page.

 

5.3.Using OData API

To update an existing entry using OData, you need to send the POST request to OData server with the body containing updated data. Note that you cannot update the Primary key for the entity. Following is an example of updating an entry in entity Region

 

POST

https://service.datadirectcloud.com/api/odata/OracleXE/REGIONSES(5.0)

Headers:

X-HTTP-Method: MERGE

Content-Type: application/json

BODY

 

RESPONSE

204 OK

If the entry is successfully updated, you will receive HTTP status 204. When you use Progress Hybrid Data pipeline and since it exposes OData, this will be process of updating items regardless of the data source being SaaS or Relational or Big Data.To learn more about updating entries using OData API you can visit the OData documentation.

 

6. Deleting Items

6.1.Using Marketo API

To delete a record, you need to send a DELETE request to Marketo server as following with body containing the id values of the items. Following is an example using the API to delete leads

 

DELETE

https://<Your-endpoint>/rest/v1/leads.json? access_token=<access_token>

 

BODY

 

If the request is successful and the record is deleted, you will see the response from the server as below.

 

RESPONSE

 

 

You can access the code samples for deleting the leads by Id in C# and Java. For more information on this you can visit the Marketo Developers guide.

 

6.2.Using Eloqua API

 

To delete items, you just need to send a DELETE request with id of the item as follows

DELETE

https://<eloqua-REST-endpoint>/API/REST/1.0/data/account/{account-id}

 

If the request is successful, you will see the response from the server as shown below.

RESPONSE

200 OK

 

6.3.Using OData API

To delete an existing entry using OData, you need to send a DELETE request to OData server. Following is an example of deleting entry in entity Region

DELETE

https://service.datadirectcloud.com/api/odata/OracleXE/REGIONSES(5.0)

Headers:

X-HTTP-Method: DELETE

RESPONSE

204 OK

If the request is successful, you will get a 204 HTTP status code. When you use Progress Hybrid Data pipeline and since it exposes OData, this will be process of deleting items regardless of the data source being SaaS or Relational or Big Data. To learn more about deleting entries using OData API you can visit the OData documentation.

 

Conclusion

Throughout the article, you may have noticed how different each proprietary API is, in terms of authentication, accessing data, modifying data etc., which generally means more development, testing & maintenance efforts when you deal with multiple of these proprietary APIs individually. With Progress DataDirect Hybrid Data Connectivity and OData, you don’t have to worry about dealing with different APIs anymore, as you can write common piece of code for all the data sources and still get access to the data that you need, improving your productivity and lets you focus on things that are important to you. And don’t worry, when the proprietary API changes/deprecates, you won’t have to deal with it as Progress DataDirect will do the heavy lifting for you to make sure you get the needed data through OData with minimal effect, and all you must do is make necessary minor changes on your end.

Get Started Today

Get started with Progress DataDirect Hybrid Data Connectivity Solutions which can be deployed anywhere on your environment, cloud or on-premises.