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.
2. Fetching Metadata
3. Retrieving Data
4. Creating Items
5. Updating Items
6. Deleting Items
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.
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.
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.
Authorization: Bearer 805913d0-fc75-48f9-ad9b-2f5781a0acc9:ab
Eloqua’s REST API supports two types of authentication, Basic Authentication and OAuth.
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.
Authorization: Basic UHJvZ3Jlc3NDb3JwL3Rlc3R1c2VyOnRlc3RwYXNzd29yZA==
In the response, you will find REST endpoints under urls object as shown below.
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.
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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
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
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 4th page.
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
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.
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
Consider the following example,
Service URI: http://services.odata.org/OData/OData.svc
When you send a GET request to the OData server in the above format, you will get all the items in that object.
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.
Logical Operators include =, !=, <, > , >=, <=, and, or, not operators. Following are couple of example that illustrate the usage
http://services.odata.org/OData/OData.svc/Suppliers?$filter=Address/City eq 'Redmond'
http://services.odata.org/OData/OData.svc/Products?$filter=Price gt 20
To learn more about remaining logical and arithmetic operators, you can visit the OData documentation page.
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.
http://services.odata.org/Northwind/Northwind.svc/Customers?$filter=substringof('Alfreds', CompanyName) eq true
http://services.odata.org/Northwind/Northwind.svc/Employees?$filter=hour(BirthDate) eq 0
http://services.odata.org/Northwind/Northwind.svc/Orders?$filter=round(Freight) eq 32d
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
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.
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
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.
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.
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.
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.
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.
To update items using Eloqua API, you need to send a PUT request in the following format.
If the request is successful, you will see the updated item in the response as shown below.
To learn more about all the Eloqua’s REST endpoints, visit this page.
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
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.
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
If the request is successful and the record is deleted, you will see the response from the server as below.
To delete items, you just need to send a DELETE request with id of the item as follows
If the request is successful, you will see the response from the server as shown below.
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
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.
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 with Progress DataDirect Hybrid Data Connectivity Solutions which can be deployed anywhere on your environment, cloud or on-premises.
Saikrishna is a DataDirect Developer Evangelist at Progress. Prior to working at Progress, he worked as Software Engineer for 3 years after getting his undergraduate degree, and recently graduated from NC State University with Masters in Computer Science. His interests are in the areas of Data Connectivity, SaaS and Mobile App Development.
Subscribe to get all the news, info and tutorials you need to build better business apps and sites
You have the right to request deletion of your Personal Information at any time.
You can also ask us not to pass your Personal Information to third parties here: Do Not Sell My Info
We see that you have already chosen to receive marketing materials from us. If you wish to change this at any time you may do so by clicking here.
Thank you for your continued interest in Progress. Based on either your previous activity on our websites or our ongoing relationship, we will keep you updated on our products, solutions, services, company news and events. If you decide that you want to be removed from our mailing lists at any time, you can change your contact preferences by clicking here.
Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.Learn More