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.
Key Technologies & Skills
- Ruby
- Syntax
- REST API libraries
- SQL libraries
- Multithreading
- Scheduling
- JSON & CSV
- Logging
- Marketo API
- Postman
- OAuth 2.0
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