Building and maintaining a data connector is a highly-involved process that we have described in detail before. Klaviyo is an email and social media marketing automation platform now used by several dozen of our customers. Businesses use Klaivyo to target, personalize and optimize email and social media advertising campaigns. Here, we describe the path we took to build our Klaviyo connector.

The story of how Fivetran built the Klaviyo connector illustrates the potential challenges and time commitment of building a data connector for a SaaS app. This article describes the tasks, timeline, people, and expertise required to build a well-functioning data connector with a clearly organized schema.

One Fivetran software engineer was dedicated to building the connector under the supervision of an engineering manager and product lead, and another software engineer reviewed the code. The project commenced in the middle of January 2018, and about six weeks later, at the beginning of March, the connector was released as public beta. By mid-April 2018 the connector was out of beta after some minor revisions.

The finished product includes the connector, an ERD, and documentation.

Klaviyo connector request: December 2017

The Klaviyo connector was initially requested by some online apparel customers. One of our customers gave our product team access to a test account, consisting of a private API key and a public API key.

A PM at Klaviyo provided the product team with the following high-level representation of the Klaviyo data model:

‍

Building a connector to the first endpoints: Mid-to-late January 2018

In mid-January, we assigned our software engineer to begin work in earnest on an initial iteration of an ERD and to integrate the first of several Klaviyo API endpoints. The initial work encompassed three endpoints containing Campaigns, Lists, and Templates.

The process consisted of the following steps:

1. Creating a team-wide test account for the API
2. Designing, testing, and validating the endpoint ingestion software
3. Sketching a normalized data model (i.e. ERD) out of the data ingested through the endpoints
4. Integrating the endpoint ingestion software into the broader Fivetran engineering framework

By reconciling the data from the Campaigns, Lists, and Templates endpoints with the API documentation and the high-level representation of the data we received, we produced the following ERD:

Hidden tables and endpoints: February 2018

Per the public Klaviyo API documentation, our engineer continued to add additional endpoints to the Klaviyo data connector. We were able to integrate the Metrics and Events endpoints with little issue.

However, a complication soon emerged. The high-level data model from Klaviyo clearly indicated the existence of a Flow table, which there was no mention of in the public documentation.

Our engineer and product lead corresponded with their product counterpart at Klaviyo to shed light on the matter, indicating points of confusion using the following image:

The ensuing conversation led to the discovery that a Flow API endpoint existed but was not publicly documented.

The product team at Klaviyo sent our product lead the necessary instructions to access the Flow endpoint by late February, allowing our engineer to extract Flow data from the endpoint and complete the ERD. We were able to convince Klaviyo to insert a key that allowed us to determine whether metrics and events were related to Flows or Campaigns. This below version from late February 2018 contains the final set of tables and interrelations. The current ERD is only slightly different and reflects minor revisions we made throughout the rest of 2018.

Some of the discrepancies between the high-level data model provided by Klaviyo remained unresolved. For instance, there was, and remains, no way to access Flow Messages or Flow Recipients. To this day, there is still no publicly accessible documentation for the Flow endpoint.

Finishing the connector

By early March, the data connector was considered complete and released as a public beta. It was considered a stable release by April. Since then, there have been periodic updates to the documentation, minor alterations to ERD and data model, various data integrity and bug fixes, and engineering fixes to keep the connector up-to-date with changes in the overall Fivetran infrastructure.

Lessons learned

Sometimes, building a data connector is straightforward. At Fivetran, we consider connectors for event tracking (like Segment, Snowplow, and WebHooks), for instance, relatively simple. Building the Klaviyo connector, however, was not a simple matter of reading API documentation and writing a script.

The single biggest obstacle to building the Klaviyo connector was the inconsistency between the publicly available API documentation, the data model provided by Klaviyo, and the data that could be readily accessed through the API endpoints. Some elements of the data model provided by Klaviyo remain inaccessible to this day.

These discrepancies have a tendency to become more serious and common as software grows in complexity. Software like Klaviyo performs a relatively simple and discrete set of tasks. Enterprise resource planning platforms like NetSuite and customer relationship management software like Salesforce are radically more complicated.

This project involved the full-time commitment of one engineer for at least six weeks, with the periodic involvement of an engineering manager, peer code reviewer, and product lead.

Building your own connector runs the classic triple constraint of complex engineering projects: choosing two of three between cost, speed, and scope. Purchasing a connector allows you to sidestep these issues entirely.

To experience for yourself how Fivetran can improve your data engineering workflow, sign up for a free trial or get in touch with a product specialist.