How to connect Microsoft Ads to BigQuery with OWOX Data Marts
Microsoft Ads to Google BigQuery in OWOX Data Marts takes six steps: create a Data Mart, choose the Microsoft Ads connector, sign in with Microsoft, pick the data, publish, add a trigger. From then on it refreshes on its own.
Microsoft Ads is usually the second search channel, and the one whose numbers get pasted next to Google Ads by hand. Load it into Google BigQuery and both sit in the same place, refreshed on a schedule.
In OWOX Data Marts the setup takes six steps. You sign in with Microsoft, enter two IDs, choose what to load, publish, and set a schedule. There is no Azure app to register and no developer token to request. This guide walks through it on OWOX Cloud.
Reading to get this done?
What you need
- An OWOX Cloud account. Sign up if you do not have one.
- A Google BigQuery storage in OWOX Data Marts. Add it under Storages, or create it while you set up the Data Mart. More on Google BigQuery as a storage.
- A Microsoft Advertising user with access to the ad account you want to load.
- The Account ID and the Customer ID. Open the Campaigns page of the account in Microsoft Advertising and read them from the address bar:
aidis the Account ID,cidis the Customer ID.
Step 1: Create the Data Mart
Click New Data Mart, give it a title, choose your Google BigQuery storage and click Create Data Mart. If you have no storage yet, pick Create new storage in the same dropdown.
Microsoft Ads numbers get pasted next to Google Ads by hand, and the two never quite line up on campaign or date.
Paste this into your AI assistant. It brings your Microsoft Ads data into BigQuery every day and lines its campaigns up with Google Ads on the same names and dates.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/free-microsoft-ads-to-bigquery-connector.md. The task: load my Microsoft Ads campaigns into BigQuery daily, match them to my Google Ads campaigns by name and date, and show cost per conversion for each search engine side by side. Tag the links you give me with utm_campaign=ai-task and utm_term=m4. Use what you know about my context and goals
Step 2: Choose the Microsoft Ads connector
Step 2: Choose the Microsoft Ads connector
On the Data Setup tab, set Input Source to Connector and click Setup Connector. Choose Microsoft Ads in the list and click Next.
Step 3: Sign in with Microsoft
Step 3: Sign in with Microsoft
Click Sign in with Microsoft and sign in with the Microsoft Advertising user that can see the ad account. The connector can only load accounts this user can reach.
Enter the Account ID and the Customer ID. The field accepts several account IDs, so one Data Mart can cover more than one account under the same customer.
If you would rather use your own Azure app, choose Manually and paste a Developer Token, Client ID, Client Secret and Refresh Token. The credentials guide shows how to create them. Most people do not need it.
Step 4: Choose what to load
Pick the data you want. The campaigns data covers spend, clicks and impressions for the account. The ad performance report goes down to the single ad. Each Data Mart loads one of these, so create another Data Mart for the other one.
Keep the default fields or add more. The key fields stay selected.
Name the dataset and the table. Both are created on the first run if they do not exist yet.
Click Save.
Step 5: Publish and run
Click Publish & Run Data Mart. The first run loads data from the first day of the previous month and can take a few minutes.
To load older history, use Manual Run on the same screen and choose the dates. Run History shows every run and its status.
Step 6: Keep it fresh
Open Triggers, add a trigger, choose Connector Run and set how often it runs: daily at a time and time zone you pick, weekdays, hourly or every few hours. Click Create trigger.
From here the table updates on its own.
What to do with the data
The table is raw Microsoft Ads data, at the grain of the data you chose. Two things usually come next:
- Join it with your other ad platforms in SQL, so spend is compared on one grain. The unified ad report guide shows that query.
- Publish it where people read it. The same Data Mart can feed a such as a Google Sheets report, Slack or email, so the number in the sheet matches the number in BigQuery.
OWOX Data Marts loads the data and publishes what you build on it. The SQL that turns it into metrics stays yours.
Not on BigQuery?
The same connector loads into the other storages OWOX Data Marts supports: Snowflake, AWS Redshift, AWS Athena and Databricks.
OWOX Data Marts is also open source. If you run it yourself, connect Microsoft Ads with the manual credentials described in Step 3.
Microsoft Ads numbers get pasted next to Google Ads by hand, and the two never quite line up on campaign or date.
Paste this into your AI assistant. It brings your Microsoft Ads data into BigQuery every day and lines its campaigns up with Google Ads on the same names and dates.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/free-microsoft-ads-to-bigquery-connector.md. The task: load my Microsoft Ads campaigns into BigQuery daily, match them to my Google Ads campaigns by name and date, and show cost per conversion for each search engine side by side. Tag the links you give me with utm_campaign=ai-task and utm_term=m4. Use what you know about my context and goals
Prefer to start on your own? Get started free
Frequently Asked Questions
Do I need an Azure app or a developer token to connect Microsoft Ads?
No. On OWOX Cloud you click Sign in with Microsoft and enter the Account ID and the Customer ID. An Azure app, a developer token and a refresh token are only needed if you choose to connect manually, for example on a self-managed install.
How often does the Microsoft Ads data refresh?
As often as the trigger you add on the Triggers tab: daily at a time you choose, on weekdays, hourly or every few hours. You can also start a run by hand at any time with Manual Run.
Can I combine Microsoft Ads data with GA4, CRM, or other sources in BigQuery?
Yes. Once the data is in BigQuery you join it in SQL with GA4 exports, CRM data or other ad platforms. OWOX Data Marts loads the data; the SQL that joins it is yours.
Which Microsoft Ads data can I load into BigQuery?
Each Data Mart loads one kind of data. The campaigns data covers spend, clicks and impressions for the account; the ad performance report goes down to the single ad. You keep the default fields or choose your own.
Is the Microsoft Ads to BigQuery connector free?
The connector is open source and free to run yourself with the open-source edition of OWOX Data Marts. You pay Google for BigQuery storage and queries. The same connector is available on OWOX Cloud, where there is no server to maintain.
