How to Normalize Google Ads and Meta Ads Data for Cross-Channel Reporting
A practical guide to turning two source-specific advertising schemas into one analytics-ready data model in BigQuery.
Create a stable reporting contract for campaign-level spend, impressions, and clicks while preserving the original source data for validation and troubleshooting.
The problem: similar metrics, different schemas
Marketing teams often run campaigns across several advertising platforms, but comparing performance is harder than placing two exports side by side.
Google Ads and Meta Ads can both report campaign, spend, impressions, clicks, and conversion-related metrics. The challenge is that the platforms expose those values through different field names, data types, account settings, and attribution rules.
Before a cross-channel dashboard can be trusted, the source data needs a consistent reporting model.
For example, the final table might look like this:
| date | source | campaign | spend | impressions | clicks |
|---|---|---|---|---|---|
| 2026-09-01 | google_ads | Brand Search | 425.30 | 84,210 | 3,415 |
| 2026-09-01 | meta_ads | Autumn Campaign | 612.75 | 143,820 | 2,907 |
The sample campaign names and values in this article are illustrative.
1. Define the canonical model first
Do not start by copying every field from every API into one large table. Start with the reporting questions the dataset needs to answer and define the smallest useful shared schema.
date
source
campaign_id
campaign
spend
currency
impressions
clicksThis becomes the contract between the ingestion layer and downstream reporting. New source systems can be added later as long as they can map into the same contract.
source and campaign_id?
Campaign names are not unique. Two platforms can both contain a campaign called Summer Sale. The source and platform ID preserve identity after the data is combined.
2. Map Google Ads fields
Google Ads Query Language (GAQL) can retrieve campaign attributes, segments, and performance metrics in one query. A campaign-level query can request fields such as:
SELECT
campaign.id,
campaign.name,
customer.currency_code,
metrics.clicks,
metrics.impressions,
metrics.cost_micros,
segments.date
FROM campaign
WHERE segments.date DURING LAST_30_DAYSThe main transformation in this minimal model is cost. Google Ads exposes metrics.cost_micros as an integer value in micros, so one currency unit is represented by 1,000,000 micros.
425300000 micros / 1,000,000 = 425.30| Google Ads field | Canonical field | Transformation |
|---|---|---|
segments.date | date | Cast to DATE if required |
campaign.id | campaign_id | Cast to STRING for a common ID type |
campaign.name | campaign | Rename |
customer.currency_code | currency | Keep ISO currency code |
metrics.cost_micros | spend | Divide by 1,000,000 |
metrics.impressions | impressions | Rename |
metrics.clicks | clicks | Rename |
3. Map Meta Ads fields
Meta campaign performance is available through the Marketing API Insights endpoint. A simplified campaign-level request can ask for the same reporting concepts using Meta's field names:
GET /act_{ad_account_id}/insights
?level=campaign
&time_increment=1
&fields=date_start,campaign_id,campaign_name,spend,impressions,clicks,account_currency| Meta Ads field | Canonical field | Transformation |
|---|---|---|
date_start | date | Cast to DATE if required |
campaign_id | campaign_id | Keep as STRING |
campaign_name | campaign | Rename |
spend | spend | Cast to NUMERIC |
account_currency | currency | Keep ISO currency code |
impressions | impressions | Cast to INT64 if required |
clicks | clicks | Cast to INT64 if required |
4. Preserve raw data and normalize in a separate layer
Normalization should not destroy the original response. A safer design keeps source-specific staging tables and creates a reporting layer on top.
This separation gives analysts a simple model without removing the evidence needed to investigate a discrepancy or change a transformation later.
5. Build the normalized table in BigQuery
The exact staging schema depends on the ingestion method. The following example assumes source-specific tables already exist in BigQuery.
WITH google_ads AS (
SELECT
CAST(date AS DATE) AS date,
'google_ads' AS source,
CAST(campaign_id AS STRING) AS campaign_id,
campaign_name AS campaign,
SAFE_DIVIDE(cost_micros, 1000000.0) AS spend,
currency_code AS currency,
CAST(impressions AS INT64) AS impressions,
CAST(clicks AS INT64) AS clicks
FROM `marketing_raw.google_ads_campaigns`
),
meta_ads AS (
SELECT
CAST(date_start AS DATE) AS date,
'meta_ads' AS source,
CAST(campaign_id AS STRING) AS campaign_id,
campaign_name AS campaign,
CAST(spend AS NUMERIC) AS spend,
account_currency AS currency,
CAST(impressions AS INT64) AS impressions,
CAST(clicks AS INT64) AS clicks
FROM `marketing_raw.meta_ads_campaigns`
)
SELECT * FROM google_ads
UNION ALL
SELECT * FROM meta_ads;The dashboard no longer needs to know that Google uses cost_micros while Meta uses spend. That source-specific complexity is handled once in the transformation layer.
6. Do not normalize meaning blindly
A schema can be technically consistent while still being analytically wrong. Renaming two fields to the same canonical name does not guarantee they measure the same thing.
Conversions and attribution
Conversion reporting is the clearest example. Platforms can use different event definitions, attribution windows, reporting settings, and conversion logic. A single canonical conversions column is useful only after the team defines which source events and attribution settings are considered comparable.
Keep source-specific conversion fields in the raw layer and document the exact rule used to populate any normalized conversion metric.
Clicks
Even a familiar metric such as clicks should be checked before comparison. Platform-level clicks can follow different product definitions. If the business question specifically requires link or outbound clicks, map the closest equivalent fields instead of assuming every click metric is interchangeable.
Currency
Never add spend from different currencies without an explicit conversion rule. Preserve the source currency and, when needed, add a separate normalized amount such as spend_eur together with the exchange-rate source and conversion date.
Timezone
Daily totals can move between dates when source accounts use different timezones. Decide whether reporting should preserve each account's local date or convert events into a shared reporting timezone, then document that decision.
7. Validate the pipeline before publishing the dashboard
A successful SQL query is not evidence that the reporting is correct. Validation should compare the transformed data with the source platform for a controlled date range.
- Choose a fixed period. Use the same account, date range, timezone, filters, and attribution settings in the platform UI and the warehouse query.
- Compare totals. Reconcile spend, impressions, and clicks by source and campaign.
- Check row shape. Look for missing dates, duplicate source/campaign/date keys, unexpected nulls, and data-type failures.
- Test edge cases. Include zero-spend campaigns, renamed campaigns, inactive campaigns, and days with no activity.
- Set an acceptance rule. Exact-match metrics should reconcile exactly unless a documented source behavior explains a difference.
If Google Ads shows €4,821.74 spend and 31,204 clicks for the selected account and period, the normalized Google Ads rows should return the same totals under the same settings. A mismatch should be investigated before the dashboard is released.
8. Query the normalized dataset
Once the source differences have been handled, cross-channel analysis becomes much simpler:
SELECT
source,
SUM(spend) AS spend,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr
FROM `marketing_reporting.campaign_performance`
WHERE date BETWEEN '2026-09-01' AND '2026-09-30'
GROUP BY source
ORDER BY spend DESC;The reporting query does not need to know how either source API represented cost, dates, or campaign identifiers. It consumes a stable model.
What this architecture enables
- Consistent dashboards: BI users work with one predictable set of fields instead of learning every source schema.
- Faster source onboarding: a new ad platform only needs a mapping into the canonical contract.
- Lower maintenance: transformation logic is centralized instead of duplicated across dashboards.
- Auditability: raw source data remains available when a KPI needs to be traced or reprocessed.
- Clear metric governance: attribution, currency, timezone, and KPI definitions become documented technical decisions.
Final principle
The goal of normalization is not to make different APIs look identical at any cost. It is to give analysts a stable interface while preserving enough source context to keep the reporting accurate.
Normalize the interface. Preserve the source. Validate the meaning.