How to Transfer Microsoft Ads Data into Google BigQuery
Although digital advertisers often focus the majority of their budgets and time toward running campaigns on Google and Facebook, there are plenty of additional conversions/sales to be driven on other ad platforms. These platforms offer access to additional market share and innovative ways to target these alternative platforms’ audiences. One such ad platform is Microsoft Ads.
The platform is doing what it can to challenge Google’s dominating presence in the search ads market and trying to make it as easy as possible to advertise on its Bing search engine. As such, many digital advertisers run similar campaigns on both Google and Bing. Unfortunately, launching new strategies, monitoring campaign performance, pacing ad spend, and reporting on results across both platforms can become tedious fast.
Through the power of BigQuery, it’s possible to access and analyze Microsoft Ads data easily and quickly and even push optimizations back into the platform. Microsoft data can be analyzed alone or together with data from other ad platforms, making things such as reporting and performance audits simpler for agencies.
Similar to the procress to transfer Facebook Data to Google BigQuery, Google doesn’t make transfering Microsoft Ads data as simple as their Google Ads Transfer process. But, it’s feasible for any digital advertiser to get Microsoft Ads data into Google BigQuery. To spin up a Microsoft Ads data warehouse (without additional complex engineering and API integrations) we suggest advertisers use one of the three approaches below:
- 1. Manual Microsoft Ads Data Upload
- 2. Data Pipelines and Connectors
- 3. Shape’s Advertising Data Infrastructure
Manual Microsoft Ads Data Upload into BigQuery
While very manual, it is possible for digital advertisers to build their own Microsoft Ads data warehouses without engineering resources or expansive software budgets. Google BigQuery allows you to manually import data from a CSV file directly. This means that any digital advertiser that can download a Microsoft Ads report can upload it to their very own data warehouse.
The cost of building this solution manually is affordable. Uploading Microsoft Ads data is free although costs accumulate if a digital advertiser suprasses Google’s data querying and storage thresholds. However, manually updating Microsoft Ads data regularly across multiple different report types can get cumbersome. Likewise, when manually uploading Microsoft Ads data, digital advertisers want to be sure that their reports contain the same data schema (metrics, headings, and data formats) to prevent incomplete or missing data from quickly destroying the usefulness of their data warehouse.
Directions:
1. Generate a Google Cloud Project (free trial available).
2. You’ll be taken to a “Basic Checklist.” (You’ve already completed steps 1 and 2 listed.)
3. Navigate to Google BigQuery and find your new project. Note: a generic project name is created for you. You can edit the project name in the Settings tab.
4. Login to Microsoft Ads. Navigate to the data you wish to analyze in Google BigQuery. If you wish to have daily data, ensure you have segmented your reports by day.
5. Download the data by selecting “Reports.” Select the report type, accounts, columns, and dates you wish to include. Make sure your Format is set to .zip(.csv).
6. After your .zip file downloads, unzip the folder. Save the .csv file to your computer.
7. Navigate back to Google BigQuery. Ensure your project is selected at the top of the screen and then click your project ID in the left-hand navigation.
8. Click “+Create Dataset”
9. Name your dataset. This should be representative of the entire dataset you plan to include (ex: “all_client_accounts_microsoft_ads”).
10. Ensure an encryption method is set then click “Create dataset.”
11. Click the name of your new dataset in the left-hand navigation and click “+Create Table”.
12. Navigate to the Source section.
- a. Create table from: Upload
- b. Select file: Find your Microsoft Ads report you saved using the browse button
- c. File format: CSV
13. Navigate to the Destination section.
- a. Select “Search for a project.”
- b. Find your project name from the dropdown. Then select your dataset name (created in step 9).
- c. Enter a name for your table. This should be representative of the table of data you are uploading (ex: campaign_data_segmented_by_day)
14. Navigate to the Schema section.
- a. Click the checkbox to allow BigQuery to auto-detect schema OR
- b. Click “Edit as text” to manually name schema, set type (string, boolean, date, etc.), and mode (nullable, required or repeated).
15. Navigate to the Partition and cluster settings section.
- a. Choose “No partitioning” or “Partition by ingestion time” based on your needs. Partitioning divides your table into smaller segments that allow smaller sections of data to be more quickly and affordably queried.
16. Navigate to Advanced Options:
- a. Set Field delimiter: Comma
- b. If your data includes a header row, enter 1 in the “Header Rows to skip” field.
17. Click “Create table.” Your data warehouse will now begin to populate with Microsoft Ads data. Check your Job History for the status of your data load.
18. Navigate to Google BigQuery and click your Dataset ID.
19. You can now start writing SQL queries against your Microsoft Ads data in Google BigQuery, or export your data to Google Data Studio and other third-party tools for further analysis.
20. Repeat this process for all additional Microsoft Ads data sets you wish to upload.
21. Repeat this process on a regular basis to ensure fresh data is available when necessary.
Google offers fantastic guides on manually uploading data to Google BigQuery or using JSON, as well as extensive support documentation.
Using Data Pipelines/Connectors to Get Microsoft Ads Data into BigQuery
Another alternative to populate a Google BigQuery data warehouse with Microsoft Ads data is to use a third-party data pipeline or connector. Several solutions exist such as Supermetrics, Funnel, and Improvado. With each of these solutions, the advertiser is responsible for launching, managing, and paying for the data warehouse. The connectors/pipelines transfer Microsoft Ads data to the data warehouse on a preset basis. Depending on the solution, pricing may fluctuate based on the number of Microsoft Ads accounts and/or data sources linked, the frequency or size of data transfers, or the level of support needed.
Although we can’t cover spin up directions for each connector individually, we’ve selected SuperMetrics for BigQuery given that Supermetrics is used by many PPC advertisers.
Directions:
1. Sign up for a free trial of Supermetrics for BigQuery (note: there is a free trial but access may not be granted immediately).
2. Add your Microsoft Ads data sources to Supermetrics.
3. Generate a Google Cloud Project (free trial available).
4. You’ll be taken to a “Basic Checklist.” You’ve already completed steps 1 and 2. Click step 3: find “Product + API.” This takes you to the Google Cloud Platform Marketplace.
5. Search/select “Bing Ads by Supermetrics.” Click Enroll.
6. Select the name of the project you generated in steps 3 & 4. Note: a generic project name is created for you. You can edit the project name in the Settings tab.
7. Navigate to BigQuery and click your project name on the left-hand navigation.
8. Click “Create Dataset.” Fill out a dataset ID and location as needed. Save.
9. Using the left-hand navigation again, click Transfers. Select +Create Transfer.
10. You’ll be asked to configure the transfer form.
11. Set the Source to “Microsoft Ads by Supermetrics”
12. Set Transfer Config Name = Name this something that indicates the job being run. (Ex: Shape Microsoft Ads Transfer)
13. Set Schedule Options.
- a. Select what time you’d like data refreshed (Start Now or Start at set time)
- b. Select if you want data refreshed on a schedule or on-demand
- c. Select the start date and run time when data will be refreshed
14. Enter a Dataset ID = Choose (or create) a name for your dataset displayed in BigQuery.
- a. If creating a new Dataset ID, follow the steps on the “Create Dataset” form to add an ID name, data location, table expiration time, and encryption key. Give a clear indication of what’s included in the dataset (ex: Client Name Microsoft Ads).
15. Under the Third-Party Connection section, click “Connect Source”. Follow steps to authorize Supermetrics as a third-party connector.
16. Using the Supermetrics pop up modal, sign in to Supermetrics for BigQuery. Authorize the Microsoft Ads login as needed.
17. Select the ad accounts you wish to transfer data from and submit.
18. Your data warehouse will now begin to populate with data. Once the data has successfully been run, you can navigate back to BigQuery to access your data.
19. IMPORTANT: To backfill historical data, you’ll need to follow additional steps outlined in Supermetrics support documentation.
Using Shape’s Advertising Data Infrastructure to get Microsoft Ads Data into BigQuery
We designed the Shape ADI to be the fastest and easiest way for digital advertisers to generate and populate a new BigQuery data warehouse with Microsoft Ads data. Shape manages the Google Cloud Platform and dataset spinups, the data transfer, and even backfills data for 30 days (more can be requested). Everyone who spins up an ADI gets access to five campaign-level data views for free, and Shape handles the cost of data storage and queries. If advertisers want to enable premium views, pricing scales based on the number of views enabled (instead of on the number of data sources or ad accounts needed which can get expensive fast for agencies).
Directions:
2. Add your Microsoft Ads data sources during the onboarding process.
- a. Optional: Create Clients and Budgets in Shape. You can combine campaigns into unique groupings to track spend and metrics in whichever combinations you choose.
3. On the main dashboard, click ADI. On the ADI page, click “Enable Warehouse.”
4. Shape will automatically generate a BigQuery data warehouse for you and enable five always-free campaign-level data views.
5. Once your data warehouse has been generated and 30-days worth of data backfilled, you’ll be taken to the Data Warehouse that lists all available data views.
6. Select a View Name to open that view directly in BigQuery OR click the “Open in BigQuery” to access your entire BigQuery data warehouse.
7. Your data warehouse is already populated and backfilled with data. You can query your data in BigQuery or export it to tools such as Google Data Studio and other third-party tools.
8. Optional: If you want to enable premium views, click the checkbox next to a premium view. Click the pencil that appears at the top of the table.
- a. On the “Edit View Status” screen, change the Status to “Enabled.”
- b. Click into the tables tab and use the pencil icon to edit the view. You can change the Updaters to how frequently Shape refreshes data and the length of data the view includes in the refresh.
- c. Click “Save Settings.”
For additional detail on Shape’s ADI including data warehouse support, check out Shape’s Help Center.
Getting Started with Shape’s Managed PPC Data Warehouse
We believe that every digital advertiser should have a powerful data PPC warehouse at her or his fingertips. To help make it easier to access and analyze your data, we have made campaign-level warehousing and API access free.
Looking for more data views or interested to learn more about what you can build with a data warehouse? Don’t hesitate to schedule a free consultation with our engineering team today before diving in.