Building a Multi-Platform Ads Data Platform with Medallion Architecture

February 15, 2024

Last year, I built a comprehensive multi-platform data platform for a performance marketing agency that needed to centralize all their Meta and TikTok marketing data and generate automated insights. This project showcases how to build production-grade data pipelines that handle multiple advertising platforms with modern cloud technologies.

The Challenge

The agency was managing multiple Meta and TikTok ad accounts with hundreds of campaigns across both platforms, but they lacked:

Centralized data storage for cross-platform analysis
Automated performance monitoring across platforms
Real-time alerts for underperforming ads on any platform
Consistent benchmarking across platforms and accounts
Automated reporting to stakeholders with platform comparisons
Unified view of marketing performance across all channels

Architecture Overview

I designed a medallion architecture (Bronze → Silver → Gold) that provides:

Bronze (RAW): Landing zone for API data with minimal transformation
Silver: Normalized dimensions and facts with business logic
Gold: Reporting marts, benchmarks, and automated signals

The key innovation was creating a unified data model that could handle both Meta and TikTok data seamlessly while maintaining platform-specific insights.

Data Ingestion Strategy

Incremental Loading Approach

Instead of full refreshes, I implemented incremental loading using watermarks. This approach ensures that only changed data is processed, significantly reducing API calls and BigQuery costs. Each platform maintains its own watermark tracking the last successful ingestion time.

Multi-Platform Orchestration

I used GitHub Actions to orchestrate data ingestion from both platforms simultaneously. The workflow runs every 6 hours and processes Meta and TikTok data in parallel, ensuring consistent data freshness across platforms.

TikTok Integration

Platform-Specific Challenges

Integrating TikTok presented unique challenges compared to Meta:

Different API structure: TikTok's Marketing API has a different authentication flow and data format
Rate limiting: TikTok has stricter rate limits requiring careful request management
Data granularity: Some metrics are calculated differently between platforms

Unified Data Model Design

To handle both platforms seamlessly, I created a unified data model that normalizes platform-specific fields. Each table includes a platform field to distinguish between Meta and TikTok data, while maintaining consistent column structures.

The unified approach enables powerful cross-platform queries without complex joins or platform-specific logic in the application layer.

Cross-Platform Insights & Reporting

Platform Performance Comparison

The unified data model enables powerful cross-platform analysis. Marketing teams can now:

Compare CTR, CPC, and CPA performance across platforms
Identify which platform performs better for different objectives
Optimize budget allocation based on platform performance
Track creative performance across platforms

Cross-Platform Alerting

The system monitors performance across platforms and provides unified alerts. When an ad underperforms on either platform, the system automatically flags it and sends notifications to the appropriate team members.

Data Transformation Strategy

Bronze to Silver Transformation

The Bronze layer stores raw API responses with minimal transformation. The Silver layer applies business logic, data cleaning, and deduplication. This separation allows for easy debugging and data lineage tracking.

Silver to Gold Transformation

The Gold layer focuses on business intelligence and reporting. It includes:

Performance benchmarks per platform and funnel stage
Cross-platform performance signals
Automated alerting thresholds
Weekly reporting aggregates

Automated Monitoring & Alerting

Performance Signals

The system automatically flags:

Underperforming ads: KPI < benchmark × 0.8
Outperforming ads: KPI > benchmark × 1.2
CTR fatigue: CTR down > X% vs 4-week median
CPA over target: CPA > benchmark
High frequency: frequency > threshold

Cross-Platform Monitoring

I implemented comprehensive monitoring that tracks:

Data freshness across both platforms
Data volume consistency
Cross-platform performance anomalies
API rate limit usage

Weekly Reporting

Every Friday at 9:00 AM (Asia/Karachi), the system generates comprehensive cross-platform reports that include:

Platform-specific performance summaries
Cross-platform performance comparisons
Top-performing and watchlist ads by platform
Budget allocation recommendations

Cost Optimization

BigQuery Best Practices

Partitioning: All tables partitioned by date
Clustering: Clustered by frequently queried columns
Incremental processing: Only process changed data
Materialized views: Hot aggregates for common queries

Storage Strategy

GCS staging: Cheap raw storage (ndjson format)
Column pruning: Select only required fields in Silver/Gold
Data lifecycle: Archive old partitions to cheaper storage

Data Quality & Monitoring

Automated Checks

The system runs comprehensive data quality checks including:

Freshness monitoring: Ensures data is updated within SLA
Volume monitoring: Detects unusual data volume changes
Schema validation: Alerts on unexpected data structure changes
Cross-platform consistency: Ensures data quality across platforms

Results & Impact

Performance Metrics

Data freshness: ≤2 hours from API change to availability across both platforms
Alert speed: Underperforming ads caught 24-48h faster
Cost efficiency: 40% reduction in BigQuery costs vs full refresh
Reliability: 99.9% uptime with automated error handling

Business Value

Faster decision making: Automated alerts reduce manual monitoring across platforms
Cross-platform insights: Unified view of performance across Meta and TikTok
Consistent benchmarking: 7d/60d comparisons across platforms and accounts
Platform optimization: Identify which platform performs better for different objectives
Scalability: Platform handles 10x growth without rework
Self-service: Marketing teams can analyze data independently across platforms

Lessons Learned

What Worked Well

Incremental processing: Essential for cost control and performance
Medallion architecture: Clear separation of concerns
Automated monitoring: Catches issues before they become problems
GitHub Actions: Simple, reliable orchestration
Unified data model: Enables powerful cross-platform insights

Challenges & Solutions

API rate limits: Implemented exponential backoff and retry logic
Schema evolution: Used flexible JSON schemas in Bronze layer
Data quality: Built comprehensive monitoring and alerting
Cost management: Implemented partitioning, clustering, and lifecycle policies
Platform differences: Created abstraction layer for platform-specific logic

Future Enhancements

Planned Features

Creative wear-out model: Rolling CTR decay analysis across platforms
Additional platform support: Google Ads, LinkedIn, and Snapchat adapters
Cross-platform attribution: Unified customer journey tracking
Looker Studio integration: Non-SQL user dashboards with platform comparisons
Backfill automation: Historical data processing for new platforms
Platform-specific insights: Creative performance analysis per platform

Technical Improvements

Streaming ingestion: Real-time data processing
ML-powered insights: Predictive performance modeling
Advanced alerting: Smart threshold adjustment
Performance optimization: Query optimization and caching

Conclusion

Building this multi-platform ads data platform taught me the importance of:

Architecture first: Good design saves months of rework, especially when scaling to multiple platforms
Unified data models: Normalizing platform-specific data enables powerful cross-platform insights
Automation everywhere: Manual processes don't scale across multiple platforms
Monitoring by default: You can't fix what you can't see across your entire marketing ecosystem
Cost consciousness: Cloud costs can spiral without planning, especially with multiple data sources

The platform now serves as the foundation for all cross-platform marketing analytics at the agency, enabling data-driven decision making, automated performance optimization, and strategic platform allocation decisions.

The key insight from this project is that multi-platform data platforms aren't just about collecting more data—they're about creating a unified view that enables strategic decisions across your entire marketing ecosystem. By normalizing platform differences and creating consistent data models, you can unlock insights that would be impossible to see when looking at platforms in isolation.

This project demonstrates my expertise in building production data platforms with modern cloud technologies. If you're interested in building something similar for your business, let's discuss your requirements.