Facebook Ads to BigQuery

Spend, reach and clicks from Meta Ads, by ad account, campaign, ad set or ad, in your own BigQuery dataset. Add Day to a report and history builds up.

One table per report, named after it: fs_spend_by_campaign, for example.
Refreshes every day on Plus, every 6 hours on Pro and every hour on Max.
Up to 10,000 rows a report.

Start free trial

Or install the Google Sheets add-on

30 days of Plus, free from sign-up. No card needed.

Meta Ads

BigQuery

How to connect Facebook Ads to BigQuery

To send Facebook Ads to BigQuery you need a Google Cloud project with a dataset in it, and a Facebook account with access to an ad account.

On the Sources page, choose Connect Meta and sign in with the Facebook account you use in Meta Ads Manager.
In Fresh Sheets, go to Destinations and choose Add a dataset. Enter the project ID and dataset ID.
In Google Cloud, give the account Fresh Sheets shows you the BigQuery Data Editor role on your dataset, and add the label it shows.
Back in Fresh Sheets, choose Check and add.
Choose New report, pick Meta Ads and the account, then the level, fields and date range.
Under Destination, pick your dataset and name the table. Under Schedule, pick how often. Then Save and run.

Columns Meta Ads writes to BigQuery

Each report is one table: the columns you pick, plus _fs_written_at, the time the row landed. Money is in the account's own currency.

Column	Type	Fresh Sheets field	Available at
`date`	DATE	Day	Every level
`account_name`	STRING	Ad account	Every level
`account_id`	STRING	Ad account ID	Every level
`campaign_name`	STRING	Campaign	Campaign, Ad set, Ad
`campaign_id`	STRING	Campaign ID	Campaign, Ad set, Ad
`ad_set_name`	STRING	Ad set	Ad set, Ad
`ad_set_id`	STRING	Ad set ID	Ad set, Ad
`ad_name`	STRING	Ad	Ad
`ad_id`	STRING	Ad ID	Ad
`impressions`	INT64	Impressions	Every level
`reach`	INT64	Reach	Every level
`frequency`	FLOAT64	Frequency	Every level
`clicks`	INT64	Clicks (all)	Every level
`spend`	NUMERIC	Amount spent	Every level
`cpc`	NUMERIC	CPC (all)	Every level
`cpm`	NUMERIC	CPM (cost per 1,000 impressions)	Every level

Days are in your ad account's time zone.

An example

A query for the report saved as Spend by campaign:

SELECT
  date,
  campaign_name,
  SUM(spend) AS spend,
  SUM(clicks) AS clicks,
  SAFE_DIVIDE(SUM(spend), SUM(clicks)) AS cpc
FROM `your-project.your_dataset.fs_spend_by_campaign`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY date, campaign_name
ORDER BY date DESC, spend DESC;

Scheduling and refresh

Include Day and each run replaces only its own dates, so history builds up. Leave it out and each run replaces the whole table.

Try it with your own Meta Ads data in BigQuery.

Start free trial

Or install the Google Sheets add-on

Other ways to do it

Google's BigQuery Data Transfer Service has a Facebook Ads connector. It takes up to 30 days of data each time it runs, and needs a Facebook developer app of your own and an access token you create for it.

Source: Google Cloud: Introduction to Facebook Ads data transfers; Google Cloud: Load Facebook Ads data into BigQuery

Fresh Sheets needs no developer app of your own. You sign in with Facebook, and the figures land in a table in your dataset.

What this doesn't cover

Lead form submissions and organic posts aren't included. This reads Meta Ads reporting only.

Questions

How do I connect Facebook Ads to BigQuery?

Choose Connect Meta on the Sources page, add your dataset under Destinations, then build a report that sends to it on a schedule. No code needed.

How is this different from Google's Facebook Ads BigQuery transfer?

Google's transfer loads every 24 hours, and you create a Facebook developer app and an access token for it yourself. Fresh Sheets needs no app or token of your own: you sign in with Facebook. Meta's access has no fixed end date. It lasts until you remove it on the Sources page, or until Meta ends it, for example if you change your Facebook password or for security reasons. If that happens, your Meta reports stop at their next run and, if any are scheduled, we email you. Choose Reconnect Meta on the Sources page with the same Facebook account and they start again.

Source: Google Cloud: Introduction to Facebook Ads data transfers; Google Cloud: Load Facebook Ads data into BigQuery; Meta for Developers: Access tokens, debugging and error handling