Digital Knowledge, a company specializing in online education, needed a way to streamline and automate its campaign reporting and data analysis processes, which were previously done manually using Google Sheets.
The company uses platforms like Google Ads, Facebook, and Ontraport for lead generation and course sales, relying on multiple data sources to track the performance of each campaign stage, from lead capture to course enrollment. The goal was to automate the data extraction, transformation, and reporting process, centralizing all information in a data warehouse (BigQuery) and creating dynamic, real-time dashboards to monitor metrics across different campaign phases.
By integrating data from multiple sources, such as Facebook Ads, Google Ads, Ontraport, Hotmart, Samcart, and Stripe, the project sought to reduce errors, improve data accessibility, and save valuable time previously spent on manual analysis.
Before Implementation
Manual data extraction and reporting were time-consuming and prone to errors, creating inefficiencies in the process.
Data from multiple sources was fragmented leading to delays and inconsistencies.
Lack of a centralized system to track campaign metrics in real-time, which hindered decision-making.
Key Improvements
Centralized data in BigQuery to streamline data integration from multiple sources, automating daily data loads.
Automated extraction processes from various platforms like Google Ads, Ontraport, Hotmart.
Implementation of the Insights Portal, providing real-time insights on campaign performance and improving decision-making.
Project Overview
1.
Data Warehouse Setup
A centralized data warehouse was established using BigQuery to consolidate all data from different sources. This setup was essential for normalizing and structuring the data for easy access.
2.
Data Extraction
Automating the data extraction from platforms like Google Ads, Facebook Ads, Ontraport, and others. Using tools such as Google Cloud Functions, Fivetran, and Python scripts.
3.
Data Modeling
Raw data was cleaned and transformed into a standardized format, allowing for better reporting and analysis. This included modeling the data to generate key metrics for the various stages of the campaign (Lead Capture, Consumption, Sales).
4.
Insights Portal
An Insights-branded portal was implemented, featuring dashboards that provided real-time KPIs across various campaign phases, including lead conversion rates, video engagement, email open and click-through rates, and sales revenue.
Python
BigQuery
Google Analytics
SQL
Technologies used
Johannes Waldow
"Since implementing the automated data management and reporting solution, we've seen a significant improvement in efficiency and accuracy. The new system not only saves us time but also provides us with actionable insights that help optimize our campaigns in real-time. The dashboards have transformed the way we analyze and respond to campaign performance, empowering us to make informed decisions quickly."
