How to Combine Google and Facebook Ads Data for stronger attribution

Did you know that it's possible to sync your ad data from multiple providers into any database? It's true! By summarizing and combining data from both your Google Ads and Facebook Marketing, you can deepen your understanding of your digital marketing campaign performance on multiple platforms. By comparing budget allocations, you can get a top-level view of your investments and make informed decisions. This article focuses on three key questions:

1. Average Daily Budget Comparison: Compare the overall spend on each platform.
2. Campaign Count by Status: Examine the number of active, removed, and paused campaigns on each platform.
3. Combined View of Active Campaigns: View general details of all active campaigns across both platforms.

These questions are just a starting point, and we hope they inspire you to explore further.

To achieve this, we'll use CloudQuery with the Google Ads Integration and the Facebook Marketing Integration. We'll using the tables googleads_campaigns and facebookmarketing_campaigns, ensure they are both in your database.

You can export Google Ads data to PostgreSQL and export Facebook Marketing data to PostgreSQL (or other listed data destinations) using the requisite CloudQuery integrations for comprehensive analysis.

These queries provide a foundation for analyzing your advertising campaigns on Google Ads and Facebook Marketing by combining your data into one central location and allowing you to augment it with information from other sources such as your CRM (our HubSpot source integration will help you to do that!). You can adjust and expand upon them to suit your specific needs and gain deeper insights into your campaign performance and spending.

1. Average Daily Budget Comparison

Calculate the average daily budget for both Google Ads and Facebook Marketing campaigns.

SELECT
    'Google Ads' AS platform,
    AVG(CAST(NULLIF(campaign_budget, '') AS FLOAT)) AS average_daily_budget
FROM
    googleads_campaigns
WHERE
    status = 'ENABLED'
    AND campaign_budget ~ '^[0-9]+$'
UNION ALL
SELECT
    'Facebook Marketing' AS platform,
    AVG(CAST(NULLIF(daily_budget, '') AS FLOAT)) AS average_daily_budget
FROM
    facebookmarketing_campaigns
WHERE
    status = 'ACTIVE'
    AND daily_budget ~ '^[0-9]+$';

2. Campaign Count by Status

Count the number of campaigns by their status for both platforms.

WITH google_ads AS (
    SELECT
        CASE
            WHEN status = 'ENABLED' THEN 'ACTIVE'
            WHEN status = 'PAUSED' THEN 'PAUSED'
            WHEN status = 'REMOVED' THEN 'DELETED'
        END AS status,
        COUNT(*) AS campaign_count
    FROM
        googleads_campaigns
    WHERE status IN ('ENABLED', 'PAUSED', 'REMOVED')
    GROUP BY
        CASE
            WHEN status = 'ENABLED' THEN 'ACTIVE'
            WHEN status = 'PAUSED' THEN 'PAUSED'
            WHEN status = 'REMOVED' THEN 'DELETED'
        END
),

meta_ads AS (
    SELECT
        status,
        COUNT(*) AS campaign_count
    FROM
        facebookmarketing_campaigns
    WHERE status IN ('ACTIVE', 'PAUSED', 'DELETED')
    GROUP BY
  status
)

-- Combining Results into Pivot Table
SELECT
    COALESCE(google.status, meta.status) AS status,
    COALESCE(google.campaign_count, 0) AS google_ads,
    COALESCE(meta.campaign_count, 0) AS facebook_ads
FROM
    google_ads google
FULL OUTER JOIN
    meta_ads meta
ON google.status = meta.status
ORDER BY status;

3. Combined View of Active Campaigns

Create a unified view of all active campaigns with key metrics.

SELECT
    'Google Ads' AS platform,
    CAST(customer_id AS TEXT) AS account_id,
    CAST(id AS TEXT) AS campaign_id,
    name,
    status,
    CAST(start_date AS TIMESTAMP) AS start_time,
    CAST(end_date AS TIMESTAMP) AS stop_time,
    campaign_budget,
    primary_status
FROM
    googleads_campaigns
WHERE
    status = 'ENABLED'

UNION ALL

SELECT
    'Facebook Marketing' AS platform,
    account_id,
    id AS campaign_id,
    name,
    status,
    start_time,
    stop_time,
    daily_budget,
    effective_status AS primary_status
FROM
    facebookmarketing_campaigns
WHERE
    status = 'ACTIVE';

Summary

You can perform detailed campaign analysis by using CloudQuery to combine Google Ads and Facebook Marketing data. This allows you to compare budgets, evaluate campaign statuses, and understand overall ad performance. With practical queries, you can easily integrate and analyze data from both platforms, providing a solid foundation for optimizing your advertising efforts. If you want to gain an immediate understanding and optimize your advertising campaigns, download CloudQuery to get started (you may find the CloudQuery Quick Start Guide to be helpful) or connect with our engineering team for further assistance.

Want help getting started? Join the CloudQuery community to connect with other users and experts, or message our team directly here if you have any questions.