Microsoft Ads to BigQuery

Spend, clicks and impressions from Microsoft Advertising, by account, campaign, ad group or keyword, 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 100,000 rows a report.

Start free

Prefer to work inside Google Sheets? Install the add-on

Your first scheduled report starts 30 days of Plus, free. No card needed.

Your Microsoft Advertising account can be personal or work.

Microsoft Advertising

BigQuery

How to connect Microsoft Ads to BigQuery

To send Microsoft Advertising to BigQuery you need a Google Cloud project with a dataset in it, and access to the advertising account.

01On the Sources page, choose Connect Microsoft Advertising and sign in with the Microsoft account that has access to your advertising account.
02In Fresh Sheets, go to Destinations and choose Add a dataset. Enter the project ID and dataset ID.
03In Google Cloud, give the account Fresh Sheets shows you the BigQuery Data Editor role on your dataset, and add the label it shows.
04Back in Fresh Sheets, choose Check and add.
05Choose New report, pick Microsoft Advertising and the account, then the level, fields and date range.
06Under Destination, pick your dataset and name the table. Under Schedule, pick how often. Then Save and run.

Columns Microsoft Advertising 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	Account	Every level
`account_number`	STRING	Account number	Every level
`account_id`	STRING	Account ID	Every level
`campaign_name`	STRING	Campaign	Campaign, Ad group, Keyword
`campaign_id`	STRING	Campaign ID	Campaign, Ad group, Keyword
`campaign_status`	STRING	Campaign status	Campaign, Ad group, Keyword
`campaign_type`	STRING	Campaign type	Campaign, Ad group
`ad_group_name`	STRING	Ad group	Ad group, Keyword
`ad_group_id`	STRING	Ad group ID	Ad group, Keyword
`ad_group_status`	STRING	Ad group status	Ad group, Keyword
`keyword`	STRING	Keyword	Keyword
`keyword_id`	STRING	Keyword ID	Keyword
`keyword_status`	STRING	Keyword status	Keyword
`bid_match_type`	STRING	Match type	Keyword
`delivered_match_type`	STRING	Delivered match type	Keyword
`impressions`	INT64	Impressions	Every level
`clicks`	INT64	Clicks	Every level
`spend`	NUMERIC	Spend	Every level
`average_cpc`	NUMERIC	Average CPC	Every level
`average_cpm`	NUMERIC	Average CPM	Every level
`quality_score`	INT64	Quality score	Keyword

Fresh Sheets requests days in your advertising 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 average_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

How often depends on your plan: every day on Plus, every 6 hours on Pro and every hour on Max.

Each run that succeeds counts once towards your plan, wherever it sends.

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

Other ways to do it

Microsoft Advertising can download a report as a file, and email one to you on a schedule.

Source: Microsoft Advertising Help: Create and schedule reports

Fresh Sheets writes the figures into a table in your dataset instead.

FAQ

Questions

How do I connect Microsoft Ads to BigQuery?

Choose Connect Microsoft Advertising on the Sources page, add your dataset under Destinations, then build a report that sends to it on a schedule. It's the same route for Bing Ads to BigQuery, since Bing Ads is Microsoft Advertising's old name.