,

Ruby & Marketo API

Summary

Some years back, maybe around 2020, we encountered a seemingly trivial problem: there was a need to make Marketo data available in our data warehouse to be able to leverage it with our PowerBI reports. However, already the E of the ETL process (Extract, Transform, Load) proved to be challenging. None of the middleware or integration platforms available for us supported Marketo natively. A workaround was to configure Marketo-Salesforce sync (which Marketo provides out-of-the-box) and then ensure relevant Marketo-originated data is extracted into the warehouse via Salesforce.

With the initial business problem thus sufficiently solved, there was no immediate pressure to change the approach. However, at the time, I had started to tinker with Ruby in my free time and realized implementing a tool to sync Marketo data into an SQL database would be a perfect exercise project to familiarize myself with Ruby and its ecosystem of 3rd-party libraries.

Skills & Requirements

Planning the implementation, the following was the initial list of required skills and technologies:

  • Familiarity with Marketo REST API using Postman
  • Implementing a daemon constantly polling Marketo data and updating the data warehouse
  • Authenticate to Maketo API using OAuth 2.0 and refresh the required auth tokens regularly
  • Implement various REST API calls to Marketo using Ruby and proper 3rd-party libraries
  • Use multithreading to isolate e.g. Marketo polling, auth token refresh, and writing to data warehouse into their separate threads
  • Implement logging using a chosen logging library
  • Implement a way to configure the tool via JSON files
  • Implement logic to fetch data in incremental batches due to the restrictions of Marketo API
  • Implement parsing of CSV result data files provided by Marketo API
  • Implement SQL database schema to support uploading Marketo data
  • Access (Azure) SQL database using Ruby to upload the data fetched from Marketo
  • Implement exception handling
  • Ruby language syntax 🙂

A reasonably mature prototype running nicely was implementd, with all the critical functionalities in place. The fundamental limitation of the prototype was (and is) that it processes only a minimal subset of the Marketo contact record fields.

As sometimes happens, before finalizing the implementation, the original need went away; our marketing team decided to switch to Hubspot. However, for me personally, the project fulfilled the purpose: I can now confidently say that if there ever is a need to implement a completely custom integration using REST+OAuth and SQL, I now know what it takes, what are the pitfalls, and how to do it.

The source code is available for anyone interested in Github: https://github.com/EAngineer/mktosync


Leave a Reply

Your email address will not be published. Required fields are marked *