Ad Spend

A record of Performance Marketing outcomes

Overview

Ads data represents the marketing campaigns and performance metrics from various advertising platforms that drive traffic and conversions to your business. This data is essential for understanding campaign effectiveness, optimizing marketing spend, and attributing conversions back to specific marketing touchpoints.

This document outlines the various options available for integrating ads data from different platforms into Roadway.

Supported Advertising Platforms

The package currently supports the following advertising platforms:

• Google Ads - Search, display, video, and shopping campaigns

• Meta Ads (Facebook/Instagram) - Social media advertising campaigns

• LinkedIn Ads - Professional network advertising campaigns

• Reddit Ads - Community-based advertising campaigns

• TikTok Ads - Social media advertising campaigns

Data Connector Options

When providing ad-platform data to Roadway, you may use any of the supported connector-platform combinations found below:

Connector	Google Ads	Meta Ads	LinkedIn Ads	Reddit Ads	TikTok Ads	Bing Ads	X (Twitter) Ads	Snapchat Ads	Pinterest Ads
Airbyte	✅	✅	✅	❌	✅	❌	❌	❌	❌
Stitch	✅	❌	❌	❌	❌	❌	❌	❌	❌
Polytomic	❌	❌	❌	✅	❌	❌	❌	❌	❌
Fivetran	✅	✅	✅	❌	✅	❌	❌	❌	❌
Custom	✅	✅	✅	✅	✅	✅	✅	✅	✅

Airbyte

Airbyte is an open-source data integration platform that supports multiple advertising platforms through their connector library.

Google Ads via Airbyte

• Documentation: Airbyte Google Ads Connector

• Tables Used:

  • google_ads_campaigns - Campaign metadata and daily performance metrics

• Key Fields: campaign_id, campaign_name, campaign_status, segments_date, metrics_cost_micros

Meta Ads via Airbyte

• Documentation: Airbyte Facebook Marketing Connector

• Tables Used:

  • meta_ads_campaigns - Campaign metadata

  • meta_ads_ads_insights - Performance metrics and insights

• Key Fields: campaign_id, campaign_name, account_id, started_at, stopped_at, campaign_status

LinkedIn Ads via Airbyte

• Documentation: Airbyte LinkedIn Ads Connector

• Tables Used:

  • linkedin_ads_campaigns - Campaign metadata

  • linkedin_ads_ad_campaign_analytics - Performance analytics

• Key Fields: campaign_id, campaign_name

Stitch

Stitch is a cloud-based ETL service that provides managed data pipelines.

Google Ads via Stitch

• Documentation: Stitch Google Ads Integration

• Tables Used:

  • google_ads_campaigns - Campaign metadata and daily performance metrics

• Key Fields: campaign_id, campaign_name, campaign_status, segments_date, metrics_cost_micros

Polytomic

Polytomic specializes in reverse ETL and API integrations, particularly useful for platforms not covered by other connectors.

Reddit Ads via Polytomic

• Documentation: Polytomic Reddit Ads Integration

• Tables Used:

  • campaign - Reddit campaign metadata

  • report_campaign_id - Campaign performance reports

• Key Fields: id, name, campaign_id, spend, impressions, clicks, date

Fivetran

Fivetran provides enterprise-grade data connectors with automatic schema handling.

Google Ads via Fivetran

• Documentation: Fivetran Google Ads Connector

• Tables Used: campaign_stats, campaign_history

Meta Ads via Fivetran

• Documentation: Fivetran Facebook Ads Connector

• Tables Used: campaign, ads_insights

LinkedIn Ads via Fivetran

• Documentation: Fivetran LinkedIn Ads Connector

• Tables Used: campaigns, ad_analytics_by_campaign

Custom Data Source

If you have already aggregated ad spend and wish to expose custom, internal models, use this method.

Required Output Schema

NOTE: This schema is "downstream" of the tables that would be exported by 3rd-party ETL providers.

Your custom connector must provide data that conforms to the final reporting table schemas:

Campaign Dimension Schema (dim__marketing_campaigns):

create table dim__marketing_campaigns (
    campaign_id varchar not null,        -- unique identifier from source platform
    campaign_name varchar not null,      -- campaign name from source platform  
    platform_name varchar not null,      -- name of advertising platform, e.g. 'Google Ads', 'LinkedIn Ads'
    unique (campaign_id, platform_name)  -- composite unique constraint
);

Campaign Performance Schema (fact__marketing_campaign_performance):

create table fact__marketing_campaign_performance (
    date_day date not null,              -- performance date (YYYY-MM-DD)
    campaign_id varchar not null,        -- matches campaign dimension
    spend decimal(18,2) not null check (spend >= 0), -- cost in currency units (not micros)
    platform_name varchar not null,      -- name of advertising platform
    unique (date_day, campaign_id, platform_name)    -- composite unique constraint
);

Data Validation

• Non-null campaign_id: Every campaign must have a unique identifier

• Non-null campaign_name: Every campaign must have a name

• Non-null platform_name: Every record must specify the advertising platform

• Non-null date_day: Every performance record must have a date

• Non-null spend: Every performance record must have spend data

• Valid date format: date_day should be in YYYY-MM-DD format

• Positive spend values: spend must be >= 0 and in actual currency units (not micros)

• Unique campaigns: The combination of campaign_id + platform_name should be unique

• Unique performance records: The combination of date_day + campaign_id + platform_name should be unique

Data Integrity Expectations

• campaign_id: Must be unique within platform and consistent across time

• platform_name: Should match the platform_name configured in variables

• Deduplication: Handle any duplicate records from your source system

• Freshness: Implement appropriate freshness checks for your data source

Joining Campaigns to UTMs

Internally, Roadway attempts to join campaigns in ads performance reports to UTMs from visit data with the following rules:

1. On campaign_name = utm_campaign, falling back to

2. On campaign_id = utm_campaign