Setting up Data Infrastructure to Optimize Marketing Efforts: Simple App Case

Goal

The Simple team invested in marketing initiatives in the USA geo heavily but couldn’t efficiently assess the ROI and effectiveness of each marketing channel. To overcome the challenge, they decided to become data mature and analyze marketing data. Since the company lacked an in-house data analytics team, they turned to a reliable partner, Valiotti Analytics, who set up data infrastructure and relevant reports to monitor marketing metrics.

Results

1. Optimized marketing strategy for lead generation through extensive reports on marketing attribution and channel efficiency in Redash

Thanks to the set-up data infrastructure, the Client managed to evaluate marketing attribution and the efficiency of different channels, which allowed them to better allocate their marketing budget and, therefore, optimize costs.

The reports are created in Redash and regularly updated, allowing the marketing team to access recent insights into marketing performance. To achieve this, the Valiotti Analytics team built the analytical infrastructure from scratch:

To provide a comprehensive overview of marketing efforts, there were several data sources:

1. Facebook API data was automatically sourced from Facebook with a Python script every 30 minutes. It was transferred to the Clickhouse database.
2. Product Event and Billing data was generated and sent by the app through SDK and a third-party service, RevenueCat, accordingly. Some RevenueCat events were stored in an S3 bucket and then sent to ClickHouse with a bash script.

Apache Kafka is an intermediary between a data source and a Clickhouse database. The solution allows numerous streams of data to be collected and stored for further extraction, in case they are lost or unavailable in Clickhouse. The collected data is stored according to various partitions to ensure easy access and navigation. The Kafka cluster is located on AWS Cloud, where a set of VPS (virtual private servers) is formed, which forms a cluster from 3 remote nodes. If one node or two nodes fail, the collection still remains, which ensures the stability of streamlined data collection. In addition, ClickHouse is connected to the Kafka cluster.

Clickhouse, as a data management system, regularly takes the collected data from Kafka and stores it. It also forms the data into tables, which are constantly monitored by a specific engine that removes duplicates. Clickhouse facilitates the completion of requests, allowing for seamless report building in Redash, as it sorts data properly, assigns it to the relevant partitions, and stores it on the hard drive.

Having the infrastructure set up, we thought through the visualization to find correlations between the allocated marketing budget across channels and received revenue. The up-to-date reports are delivered in Redash.