Back to projects

End-to-End Data Analytics

Unified Paid Media Data Model

Twelve ad platforms unified into one data model — spend, sales, clicks, views and watch time comparable across every channel.

Client
Confidential · multi-channel advertiser
Type
Data model & dashboards
Role
Data Analyst / Analytics Engineer
Category
End-to-End Data Analytics ↗

12

Platforms

6

Channel recipes

1 unified grain

Model

Overview

An advertiser running search, social, video, programmatic and retail media had its performance spread across twelve platforms, each with its own metrics, naming and currency. I built API pipelines for every account, transformed the data through channel recipes (Paid Search, Paid Social, Video, Programmatic, Retail Media) and merged it into one unified model that powers every report. Visuals use illustrative data; the structure mirrors the production model.

Scope of work

01

API pipelines per ad account

02

Cleaning & naming taxonomy

03

dbt recipes by channel group

04

Unified star schema

05

Data tests & reconciliation

06

KPI dashboards

01 · The challenge

Every platform told a different story: different metrics, naming, attribution windows and currencies, exported by hand into spreadsheets.

  • No single view of spend, revenue or ROAS across channels and markets.
  • Twelve sources — Google Ads, Microsoft Ads, DV360, Campaign Manager 360, YouTube, Facebook, Instagram, LinkedIn, TikTok, Amazon and Walmart — plus sales.
  • Video, click and retail metrics that could not be compared side by side.

02 · The solution

API pipelines per account into BigQuery, a cleaning and naming-taxonomy layer in SQL, and dbt recipes by channel group that normalize metrics into one star schema (fct_paid_performance) at a single grain — date × platform × campaign × creative × market — tested and reconciled to each platform.

03 · Results

  • One source of truth for spend, revenue, ROAS, clicks, views and watch time across 12 platforms.
  • Cross-channel comparisons by channel group, platform, market and creative.
  • Automated refresh instead of manual exports.
  • A foundation reused for dashboards, analysis and predictive models.

Screens

Take a closer look

Have a project in mind?

Let's build something beautiful together.

Book an intro call

Next project

Predictive ROAS for Amazon Ads

Predictive models