Data Driven:
An ETL Story

This was a collaboration between Setfive and Data Driven. The end customer was a client of Data Driven's that we'll refer to as WF. WF is an affiliate marketing company which owns and operates about a dozen different website properties. WF works with advertisers to match users with offers and discounts as users browse WF sites. WF does this by combining behavioral, intent, and contextual data about their users and sites. For historical reasons, WF selected Elasticsearch as their primary datastore to collect and record their user data. Unfortunately, as WF started servicing more advertisers and users, they began struggling to use Elasticsearch for their reporting needs.

  • Elasticsearch was unable to efficiently respond to reporting queries while receiving a heavy workload of reads and writes from the client facing sites.
  • Due to Elasticsearch's custom query syntax the WF business team required engineering time whenever they wanted to add or modify a report.
  • Because of this, the WF team was dependent upon third party advertisers to provide metrics around revenues and conversions.

WF met with Data Driven and Setfive to discuss the difficulties of using Elasticsearch and the business risks around relying on a third party for revenue reports. After this discussion, Data Driven and Setfive came up with a twofold solution. First, we would create an Extract Transform Load (ETL) pipeline to regularly move the data from Elasticsearch and into Amazon Redshift. The second step was to make the data in Redshift easy to query by members of the WF business team. To accomplish this, we setup and configured Looker to connect and query the WF Redshift cluster.

  • The Elasticsearch to Redshift ETL pipeline allowed WF to use a familiar SQL query syntax to run analytics against their data.
  • In addition, having their data in an OLAP data warehouse allowed the WF team to use off the shelf business intelligence tools and avoid having to develop custom reporting components.
  • Using Looker, the WF business team can now create new reports and run ad-hoc queries without requiring any engineering time.

This collaboration was a success and WF continues to use Looker today.