Transforming Fragmented Ad Data into an Automated Paid Media BI

I led the design and implementation of an end-to-end paid media business intelligence reporting layer that consolidated performance data from Meta Ads, Google Ads, Microsoft Advertising, Google Analytics 4, and internal budget sources.

I translated business reporting requirements into a scalable cloud architecture, built four automated Google Cloud Functions, modeled millions of advertising and website-activity records in Big Query, and developed an interactive Power BI dashboard with daily refreshes, validation controls, and backup workflows.

Project Overview

Role: Technical Project Lead and Systems Engineer

Focus: Cloud data engineering, systems integration, data modeling, and business intelligence

Platforms: Meta Ads, Google Ads, Microsoft Advertising, and GA4

Core technologies: Google Cloud Platform, Big Query, Cloud Functions, Cloud Storage, Secret Manager, Fivetran, SQL, Python/JavaScript, Power BI, Power Query, and DAX

Interactive Meta Ads reporting interface with dynamic date-grain and dimension controls. Dashboard shown with anonymized sample data.

Interactive Meta Ads reporting interface with reusable date-grain and dimension controls. Dashboard shown with anonymized sample data.

The Business Challenge

Paid media performance was distributed across several advertising and analytics platforms. Each system used different schemas, attribution models, campaign structures, and definitions for conversions.

This fragmentation made it difficult to:

• Compare performance across advertising channels

• Monitor actual spend against budget

• Maintain consistent KPI definitions

• Analyze performance beyond each platform’s native reporting interface

• Refresh historical and current data without manual exports

• Give leadership a reliable, unified view of marketing performance

The business needed more than another dashboard. It needed a dependable data foundation capable of collecting, validating, and standardizing information from multiple external systems consistently and accurately.

My Solution: Enhancing Paid Media Reporting

I designed a centralized reporting architecture that moved data from each advertising platform into Big Query before presenting it in Power BI.

The reporting flow followed this structure:

Advertising and analytics platforms → Automated cloud pipelines → Big Query staging tables → Validated reporting tables → Power BI

I built four Google Cloud Functions to manage automated ingestion, transformation, validation, checkpointing, and recurring refreshes.

Incoming records were first loaded into staging tables.

Controlled Big Query MERGE operations then inserted new records and updated existing ones, allowing the system to process overlapping refresh periods without duplicating campaign performance.

Cloud Storage checkpoints tracked the progress of historical backfills and recurring loads. This allowed interrupted jobs to resume from the correct date instead of restarting an entire extraction.

Key Technical Contributions

Multi-Platform Data Integration

I integrated campaign-performance data from Meta Ads, Google Ads, and GA4 through automated cloud pipelines. After evaluating the complexity and maintenance requirements of a custom Microsoft Advertising API integration, I implemented Fivetran as the connector for MS Ads/ Bing and limited the imported schema to the performance and budget tables required for reporting.

This decision reduced unnecessary data ingestion while preserving the reporting dimensions needed by the business.

Scalable Data Processing

The pipelines supported both historical backfills and incremental refreshes. Large date ranges were divided into smaller processing windows to manage API limits, reduce failed requests, and reliably process millions of records.

I also implemented pagination controls, retry logic, checkpoint recovery, staging tables, and deduplication rules to improve pipeline reliability.

Standardized Data Modeling

I created a consistent reporting model across platforms while preserving important dimensions such as:

• Date

• Advertising platform

• Account

• Campaign and ad group

• Campaign type and objective

• Device

• Publisher

• Placement

This model enabled users to compare channels without losing the platform-specific details needed for deeper analysis.

GA4 reporting view connecting website engagement with add-to-cart activity, purchases, conversion rates, and revenue. Dashboard shown with anonymized sample data.

Business Intelligence Development

I developed Power BI reporting pages for Google Ads, Meta Ads, and GA4, along with a combined view of advertising spend and budget.

The dashboard included measures for:

• Spend

• Impressions

• Clicks

• Purchases

• Purchase revenue

• Add-to-cart activity

• CTR, CPC, and CPM

• CPA and ROAS

• Add-to-cart and purchase conversion rates

I created reusable date-grain controls that allowed users to move between daily, weekly, monthly, quarterly, and yearly views without rebuilding individual visuals.

GA4 reporting view connecting website engagement with add-to-cart activity, purchases, conversion rates, and revenue. Dashboard shown with anonymized sample data.

Solving Complex Data-Quality Problems

A major part of the project involved validating that technically successful data loads also produced trustworthy business results.

Meta’s API returned multiple versions of certain conversion actions across action types and attribution windows. Without additional processing, the dashboard could count the same add-to-cart activity more than once. I developed prioritization and deduplication logic that selected the appropriate action type and prevented overlapping attribution records from inflating totals.

I also resolved BigQuery MERGE failures caused by multiple source records matching the same target row. I refined the synthetic record keys and deduplicated staged data before each upsert, protecting the integrity of the final reporting tables.

Within Power BI, I validated calculated metrics using ratio-of-sums logic so KPIs such as ROAS, CPA, CTR, and conversion rate remained mathematically accurate when users filtered or aggregated the data.

For GA4 attribution, I normalized campaign names before joining analytics and advertising data. I also documented the risk of mismatches created by renamed campaigns so stakeholders understood the limitations of cross-platform attribution.

Business Impact

The completed system transformed disconnected advertising data into a centralized, repeatable reporting process.

It provided the organization with:

• Automated daily reporting instead of fragmented platform exports

• A unified view of paid media performance and budget

• Consistent KPI definitions across advertising channels

• More reliable data through automated validation and deduplication

• Faster analysis across campaigns, devices, placements, and time periods

• A scalable reporting foundation that could support additional platforms and metrics

This project demonstrates my ability to lead a technically complex initiative from requirements gathering and architecture design through development, debugging, validation, and final business delivery.

It also reflects how I approach business systems work: understanding the decision the organization needs to make, designing the supporting system, and ensuring the resulting data is both technically reliable and useful.

View the sanitized architecture and sample code on GitHub.