Analytics Engineering with dbt for Measured

Goal

• The key issues we needed to solve had to do with data engineering. The data needed to be transformed for correct analytics, which required setting up special tools and writing high-quality SQL code.
• The Client team didn’t have data engineers who could create an ELT pipeline using dbt. Because of a lack of relevant competencies, they needed to outsource.
• The Client chose Valiotti because of our extensive experience with data engineering tools, with dbt in particular. Our portfolio includes projects dedicated to setting up data workflows in various industries, and our expertise allowed us to code effectively in SQL to work with columnar databases, such as Redshift.

Results

Combining data from different sources

We combined data from three data sources (payment systems that Measured uses). For this, we used Fivetran to connect to various data sources and collect information from these sources in the Amazon Redshift cloud database.

Below, you can see how Fivetran connects to payment platforms and Facebook Ads.

Connectors in Fivetran

And here are the data schemas stored in the Amazon Redshift database after being integrated with Fivetran. As you can see, the data sources contain different fields after Fivetran connects to them and transfers the information to the database.

Braintree Entity-Relationship Diagram (ERD)

Stripe Entity-Relationship Diagram (ERD)

Transforming data

With the help of the dbt ETL tool, we were able to connect to the database and transform data in the warehouse.

Graph from dbt

This screenshot shows how a data mart is formed based on two payment systems (Braintree and Stripe, with measured_stripe as an old version of Stripe). This would later be used to build charts in Metabase. The data in the data marts are updated at an interval set by the user in dbt.

In the screenshot below, you can see how we created a data mart (shaped as a dbt model, bt_stripe_combined_subs on the graph).

Structure of the final data mart (assembled in stages)

We should also mention that dbt has a unique syntax – the screenshot below shows the processing of a subscription (the stg_mapping model in the graph).

Structure of the final data mart (assembled in stages)

Reporting

The main data marts were used as the basis for  Metabase reports, such as:

• % of retained customers by month (by subscription fields)
• New vs. repeat revenue by month (without refund transactions)
• Net revenue vs. refunds by original day
• Cumulative metrics and more

For example, we generated a Retention Rate report with the use of cohort analysis and based on the final data mart for subscriptions (bt_stripe_combined_subs in the dbt graph).

Retention rate split by cohort

Also, we built a number of graphs and tables based on other data marts to show the dynamics of business metrics, revenue, and returns. See the examples below.

Dashboards for net revenue vs. refunds and new vs. repeat revenue

Pivot table with cohort analysis

Interactive UI to filter the dashboard

Getting a 360-degree view of the business

The project stages described above allow the Client to see a complete picture of the company’s processes. The graphs show the dynamic changes in various metrics, which inform the user if something is going wrong and they need to implement changes.

For example, the Client can promptly see issues with user retention in the app and take measures to correct them.

Boosting productivity

The Client is now able to make decisions in a data-informed way. For example, they can create new marketing experiments and see the results separately for each cohort.