Category: Programming

  • Ruby & Marketo API

    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 implemented, 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