Microsoft Ads keeps data for a specified period of time. This time period varies based on the report type. For most reports, Microsoft Ads retains the data for 36 months. In order to have access to your historical data for analysis, you need to export your Ads data to an external tool. BigQuery is a perfect tool for this as it can handle large datasets. You can also analyze your Microsoft Ads data by running SQL queries, combining data with other sources, etc.
There are different methods to connect Microsoft Ads to BigQuery. Let’s explore these options together.
Different options to connect Microsoft Ads to BigQuery
You can count on three ways to load Microsoft Ads data to BigQuery. Each way has its pros and cons.
- Connect Microsoft Ads to BigQuery with Coupler.io: If you need to recurrently export Microsoft Ads data to BigQuery, consider using Coupler.io. It’s a reporting automation solution to turn raw data into simple reports. It lets you connect Microsoft Ads to BigQuery without any coding. Coupler.io is an excellent choice for marketers and business users, as no tech knowledge is needed. Along with Microsoft Ads, you can also connect other ad applications (TikTok Ads, Facebook Ads, etc.) to BigQuery and analyze them together.
One of the standout features of Coupler.io is the ability to set a schedule for automatic data refresh. This makes Bing Ads analytics very convenient since your reports are updated on their own.
- Manual export: This method involves downloading your Microsoft Ads data in CSV format, followed by uploading it into BigQuery. It’s a simple and effective method, particularly apt for one-off data transfers from Microsoft Ads to BigQuery. However, this approach may not be as practical for repeated exports due to its manual nature. Your ad data in BigQuery won’t update automatically, and you will need to export-import it again every time you need to work with fresh data. If you need regular updates, an automated connection is a more suitable choice.
- Custom ETL scripts: This is a code-based option that is suitable for tech-savvy marketers. It involves using the Bing Ads API in your preferred programming language to export data from Microsoft Ads directly into BigQuery. For business users, we don’t recommend this method as it requires a strong technical background. We won’t be diving deep into this method in this article. If you are interested in it, we recommend checking Bing’s Ad API documentation, as it explains everything you need to know.
Moving further, let’s check out the other two methods in more detail, starting with the most straightforward and convenient approach – using Coupler.io.
Connect Microsoft Ads to BigQuery with Coupler.io
1. Collect your Microsoft Ads data
To start transferring your data from Microsoft Ads to BigQuery, click the Proceed button in the widget below.
Connect to your Microsoft Ads account and specify the type of report you need to export. You’ll also need to select the dimensions and metrics you want to include in your report. Also, feel free to modify the default Bing Ads reporting period according to your requirements (the default option covers the past 60 days).
2. Organize and transform your data
Before exporting your data, take a moment to review it and adjust it as needed. Here you can:
- Reorganize and rename columns as required.
- Conceal existing columns or introduce new ones.
- Implement filters and organize data in a specific order.
- Use formulas to perform calculations.
- Merge data from multiple accounts or various applications.
Alternatively, you can simply preview the data to ensure everything is accurate and in order.
3. Load data to BigQuery and schedule updates
After this, connect to your BigQuery account. For this, you’ll need a Google Cloud JSON key. Once connected, enter the names of the dataset and table where you want to import your Microsoft Ads data. You also have the option to create a new dataset and table by entering new names.
Toggle on the Automatic data refresh feature and choose the intervals at which you want your data to be updated.
Click Run Importer. Once the importer has run successfully, click on View Results to examine the newly imported data from Microsoft Ads to BigQuery.
Below is our sample Microsoft Ads Campaigns data exported to BigQuery with Coupler.io.
With Coupler.io, you can quickly connect Microsoft Ads to BigQuery. It also allows you to collect data from 60+ apps, to BigQuery, and other destinations. You won’t need to manually back up your data, as Coupler.io provides automated updates of your Microsoft Ads data in BigQuery, aligning with the schedule you set.
Use case example: use your Microsoft Ads data in BigQuery for closed-loop analytics
With your Microsoft Ads data in BigQuery, you can combine it with marketing and sales data from other different channels. For example, social media platforms, email campaigns, website analytics, CRM tools, and more. Then, you can analyze all this data together and obtain valuable insights. This concept is commonly known as closed-loop analytics. By bringing together data from multiple sources, you can cross-analyze, examine the impact of each channel on your campaign strategy, and build predictions.
With Coupler.io, you can easily import data from other sources into BigQuery. We have already briefly touched on this topic in the section above. All you need to do is set up separate importers for the different data sources you want to import. In our example below, we have set up separate importers for connecting Microsoft Ads, Google Analytics, and HubSpot to BigQuery.
This creates separate datasets in the BigQuery warehouse. Below is the list of the datasets from the different data sources that we imported using Coupler.io. Now they are available for analysis in BigQuery.
With your datasets in BigQuery, you can combine the different types of data gained from each of the platforms together, for example:
- From your Microsoft ad apps – clicks, ad spend, impressions, click-through rate (CTR), keyword performance, geographical data, device information
- Metrics from GA4 – conversions, website session length, page views, user demographics, traffic sources, event tracking
- From Hubspot – clients and customers, sales revenue, lead sources, email campaign data, deal pipeline, customer lifetime value (CLV)
Now, you can analyze all this data together and see, for example:
- How your ads spending is leading to actual sales and profits.
- Learn what people do on your website after clicking an ad. Do they stay long? What pages do they visit?
- Understand the full path a customer takes from seeing your ad (Microsoft Ads), visiting your website (GA4), to making a purchase or becoming a lead (HubSpot).
- Find out which ads are not just getting clicks but are actually leading to sales or sign-ups.
- Identify which ads appeal to certain age groups, locations, or interests, and see how these groups behave on your website and in the sales process.
With all of these insights, you will be able to make data-driven decisions to help push your Microsoft Ads campaign further. Also, the data processed in BigQuery can be visualized in interactive dashboards built using data visualization tools like Looker Studio. Here is an example of a Bing Ads dashboard template by Coupler.io.
The dashboard provides an overview of your campaign performance. At the bottom, there is a table with a breakdown of your Bing Ads campaigns. On a separate page of this Looker Studio report, you’ll get the monthly dynamics of key metrics including impressions, clicks, amount spent, conversions, etc.
To start using the dashboard, you only need to connect to your Bing Ads account. Then the data will be turned into insights about your campaign performance.
How to export Microsoft Ads data to BigQuery manually
In certain cases, manually transferring data from Microsoft Ads to BigQuery is a viable option. For instance, at the end of each year, you might want to manually export your yearly ad campaign data to BigQuery for a detailed annual review. In these situations, a once-a-year manual export is sufficient, as regular updates aren’t usually needed.
To manually export your data, you need to start by saving your information in a CSV file and then manually upload it to BigQuery. Here are the steps to follow;
- Sign in to your Microsoft Ads account.
- Navigate to the Campaigns. Select the campaigns you want to export.
- Click on Download to save the selected data in the CSV format.
Once you have downloaded the data in the CSV format, proceed with importing it into Google BigQuery
- Head over to your Google BigQuery console. Click +Add.
- Choose Local file to begin uploading the CSV file from your computer.
- Click on Browse in the select file section to locate and upload your CSV file
- Select the appropriate dataset where you want the new report table to be stored. Enter a name for the table where your report will be located. In the Schema section, enable Auto detect.
To finalize, click Create Table – this will import your data into BigQuery.
And there you have it: your Bing Ads CSV data is now successfully uploaded to BigQuery.
The manual way of connecting Microsoft Ads to BigQuery is simple, but keeping data fresh with this method can be very exhausting. It’s only recommended when working with small datasets. But if you need to constantly update data or work with large datasets, it’s best to build an automated connection.
What is the best way to load Microsoft Ads data to BigQuery?
As mentioned above, there are three methods to connect Microsoft Ads to BigQuery. Each method to export Microsoft Ads to BigQuery — whether it’s code-free automation, manual data transfer, or coding — has its own unique strengths and is suited to different situations.
- The code-free automated approach using Coupler.io is fantastic for regular, ongoing data transfers, ensuring consistency and saving time. You can also integrate other data sources within your importer. Though it might come at a cost, the trade-off is its convenience.
- The manual approach is for one-time data analysis This method is free and suitable for budget-conscious users.
- Lastly, the coding approach is a bit more complex as it involves knowledge of Python, APIs, SQL, and so on. It is great for those who require a customized solution to handle their data effectively.
However, when it comes to selecting the best way to move data from Microsoft Ads to BigQuery, our recommendation leans toward the option that simplifies and streamlines the process while ensuring efficiency, which is Coupler.io.
We know that you’ll choose what works best for you. Good luck with your data!
Automate data export from Bing Ads with Coupler.io
Get started for free