How to Combine Google and Facebook Ads Data for stronger attribution

• August 3, 2024

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:

Average Daily Budget Comparison: Compare the overall spend on each platform.
Campaign Count by Status: Examine the number of active, removed, and paused campaigns on each platform.
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]+$';

platform	average_daily_budget
Google Ads	12000
Facebook Marketing	10000

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;

status	google_ads	facebook_ads
ACTIVE	1	1
DELETED	1	1
PAUSED	1	1

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';

Platform	Account ID	Campaign ID	Name	Status	Start Time	Stop Time	Campaign Budget	Primary Status
Google Ads	1234567890	101	Summer Sale Campaign	ENABLED	2024-06-01 00:00:00	2024-12-31 00:00:00	12000	ACTIVE
Facebook Marketing	act_1234567890	101	Summer Sale Campaign	ACTIVE	2024-06-01 00:00:00	2024-12-31 23:59:59	10000	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.

Written by Ron Shemesh

Ron is a Senior Software Engineer at CloudQuery and has a background in data science and now works as a senior software engineer at CloudQuery. He has experience working in Python, SQL, React, Java and R. At CloudQuery, Ron has worked on our range of integrations and several projects foundational to platform performance. He loves taking on a challenge and using it to improve his skills.

On this page