AKD Media — Centralized Marketing & Lead Performance Analytics Pipeline
A fully automated ETL pipeline that aggregates data from LeadProsper, Meta Ads, Google Ads, and CaseOff API into BigQuery — delivering real-time dashboards on spend, revenue, profit, and ad-level performance across all clients.
Meta, Google, LeadProsper, CaseOff
Extract, Transform, Load
Real-time data
Actionable insights
01 / Project Overview
The Challenge & What We Built
AKD Media runs performance marketing campaigns across multiple clients using Meta Ads, Google Ads, LeadProsper, and CaseOff. With data scattered across four separate platforms, getting a unified view of spend, revenue, and profit per client required hours of manual work every day.
We designed and implemented a fully automated ETL pipeline using Google Cloud Functions and Python to extract data from all four sources, transform and centralise it in BigQuery, and surface it through live Looker Studio dashboards — giving AKD Media instant visibility into what's working and what's not.
Core problem solved: Four data sources, zero unified reporting. This pipeline aggregates everything automatically — daily spend, revenue, profit, and ad-level performance — all in one place, updated every 6 hours, with zero manual work from the marketing team.
Multi-Source Extraction
Pulls data from 4 APIs automatically — Meta, Google, LeadProsper, CaseOff.
BigQuery Warehouse
Centralised raw and transformed data tables — queryable in real time.
Cloud Functions
Python serverless functions — triggered every 6 hours, zero maintenance.
Looker Dashboards
Interactive reports on spend, revenue, profit, and ad performance per client.
02 / Data Architecture
The ETL Pipeline — How Data Flows
Data flows from four source systems through Google Cloud Functions, into BigQuery raw tables, through SQL transformations, and finally into Looker Studio dashboards — fully automated, every 6 hours.
Source Systems
Pipeline Steps
Cloud Functions trigger every 6 hours — pulling fresh data from Meta Ads API, LeadProsper API, and CaseOff API using Python.
Python scripts inside Cloud Functions clean the data — date formatting, deduplication, field normalisation — before loading to BigQuery.
Cleaned data loaded into raw BigQuery tables — leadprosper_raw, meta_ads_raw, google_ads_raw, caseoff_raw.
BigQuery SQL views aggregate daily spend, revenue, and profit per client — creating the daily_spend_revenue_profit view.
Joined datasets from all four sources create complete client-level and ad-level performance tables for reporting.
Looker Studio connects directly to BigQuery — querying the transformed tables and views to power live dashboards.
Interactive Looker Studio dashboards show daily spend, revenue, profit, and ad-level performance — filterable by client and date range.
Marketing and finance teams get real-time visibility into ROI, campaign effectiveness, and lead conversion — zero manual reporting.
03 / Technical Implementation
Four Phases of Delivery
Phase 1 — Extraction
Phase 2 — Transformation
daily_spend_revenue_profit viewPhase 3 — Business Logic
Phase 4 — Dashboards
04 / Key Metrics & Insights
What the Dashboards Track
Daily Spend, Revenue & Profit
Track spend, revenue, and profit for each client on a daily basis. Monitor profitability and assess ad campaign ROI in real time.
Ad-Level Performance
CTR, conversion rate, spend, and impressions for every ad campaign — identify high-performing ads driving the most revenue.
Lead Performance
Number of leads per client, conversion rate from leads to paid subscriptions, and lead source attribution across all platforms.
Revenue Trends
Time series charts showing revenue and spend trends over time — enabling data-driven budget allocation decisions per client.
Cross-Platform Attribution
Unified view of Meta and Google Ads performance side by side — compare platform ROI and reallocate budgets accordingly.
Client Profitability
Per-client profit and loss calculated daily — gives account managers clear visibility into which clients are most profitable.
05 / Business Impact
What This Pipeline Delivers
Optimised ad spend — marketing teams can track ROI per campaign in real time and adjust budgets based on live performance data, not weekly reports.
Improved lead prioritisation — sales teams can identify high-value leads from LeadProsper and CaseOff data and focus conversion effort where it counts most.
Real-time insights — automated 6-hour refresh means decision-makers always have the latest spend, revenue, and profit data without waiting for manual reports.
Zero manual reporting — hours of daily data consolidation eliminated. The entire pipeline runs automatically, with dashboards always up to date.
Scalable architecture — adding a new data source or client requires only a new Cloud Function and table, with no changes to the reporting layer.
06 / Tech Stack
Technologies Used
07 / Skills & Deliverables
What Was Built & Applied
Want a centralised analytics pipeline for your marketing data?
From multi-source ETL to BigQuery warehousing and Looker dashboards — we build data pipelines that give you real-time ROI visibility across every client and campaign.