How to Load Data from Microsoft Ads (Bing Ads) to BigQuery

Rising consumer demands drive the market towards more personalized and responsive offerings. To keep up with varied consumer needs, you must utilize advanced analytics and optimize your marketing strategies. This can enhance customer satisfaction, foster loyalty, and give you a competitive edge in the market.

The advertising data from Microsoft Ads (Bing Ads) is a good source for gaining valuable insights. Combining this data with data from other platforms in BigQuery can make analysis, reporting, and performance audits simpler. Some benefits include controlling ad spending, monitoring campaign performance, launching new strategies, and reporting results across platforms.

If you’d like to optimize your campaigns and produce a comprehensive dataset of your marketing activities, consider loading data from Bing Ads to BigQuery. Let’s look into the details of these platforms and the methods to connect them.

An Overview of Microsoft Advertising (Formerly Bing Ads)

Microsoft Advertising, formerly known as Bing Ads until 2019, is an online pay-per-click advertising platform that allows you to create and run high-performing ad campaigns. With Microsoft Ads, your business can display brief ads, product listings, service offers, and videos to reach your target audience.

You can use Microsoft Ads to advertise on Microsoft properties, including Bing Ads, Yahoo, MSN, and AOL. This lets you maximize your online presence, connecting with potential customers across digital channels.

Microsoft Ads offers several features and tools to help you create effective campaigns. Let’s look into some of these features:

• Keyword Planner: With this tool, you can conduct keyword research and identify relevant search terms that will likely attract your target audience. By selecting the right keywords, you can increase the visibility of your ads when users search for related queries.
  
• Automated Bidding: In Microsoft Ads, you can use automated bidding to set the maximum amount you’re willing to pay for each click or impression on your ads. This feature automatically adjusts bids based on factors such as campaign performance, ad position, and keyword competition.
  
• Performance Monitoring: With this feature, you can monitor your campaign performance, providing key metrics such as ROI, conversions, click-through rates, clicks, and impressions. Such data can help optimize strategies and enable you to make informed decisions to improve your campaign effectiveness.

An Overview of BigQuery

Google BigQuery, a part of the Google Cloud Platform (GCP), is a fully managed, serverless data warehouse. BigQuery facilitates the storage of petabyte-scale data and offers powerful data analytics capabilities.

One of the primary reasons for BigQuery’s high scalability and performance is its architecture, which separates storage and computation. Such a decoupled architecture allows flexibility and increased control over costs.

Here are some other impressive features of BigQuery that add to its efficiency and make it a great data warehouse choice:

• Real-Time Analytics: BigQuery supports real-time analytics by integrating with Google Cloud’s data streaming tools like Pub/Sub and DataFlow. This allows your business to respond promptly to the latest events and trends.
  
• Machine Learning: With BigQuery ML, you can create and execute ML models directly within BigQuery using standard SQL queries. This decreases development speed, simplifying the process of applying machine learning to your data without requiring specialized expertise except SQL knowledge.
  
• BI Engine: The BigQuery BI Engine is an in-memory analysis service that provides subsecond query response times with high concurrency. This eliminates the need for complex transformation pipelines. BI Engine also integrates with Google BI tools and other popular tools to help visualize query results.

2 Methods to Connect Microsoft Ads (Bing Ads) to BigQuery

You can use one of the following two methods to connect Microsoft Ads to BigQuery. The choice depends on your time and budget constraints.

• The Automated Way: Connecting Microsoft Ads to BigQuery using Estuary Flow
• The Manual Way: Connecting Microsoft Ads to BigQuery Using CSV Export/Import

Method 1: Connecting Bing Ads to BigQuery Using Estuary Flow

A fast and cost-efficient way to connect Bing Ads to BigQuery is by using Estuary Flow. This data integration platform simplifies the process with real-time, low-maintenance pipelines.

Estuary Flow provides an intuitive interface, ready-to-use connectors, and multiple deployment options for your data integration needs. Let’s look at some of the unique offerings of Estuary:

• Change Data Capture (CDC): With our flagship feature—CDC—you can track and synchronize data changes in the source system and replicate them to the target system in real time. CDC allows you to connect to a system and immediately start reading a stream while also capturing its (24-hour) history. The combined stream is sent to the destination in real time with sub-100ms latency.
  
• Extensive Connectors: Estuary Flow offers 200+ streaming and batch connectors for data warehouses, data lakes, social media, and SaaS applications. These connectors take only a few minutes to configure and start moving your data.
  
• Scalability: Estuary Flow is built to be horizontally scalable, allowing it to accommodate high throughput demands and handle large volumes of data. This makes it well-suited for small as well as large enterprises.
  
• Supports ETL and ELT: With Estuary Flow, you can transform and merge data from multiple sources before you load into the data warehouse (ETL), after (ELT), or both (ETLT). There is support for streaming or batch transforms using SQL or TypeScript (ETL) and dbt (ELT).
  
• Private Deployments: You can deploy Estuary in your private network, ensuring that data never leaves your control. The other deployment modes include BYOC (Bring Your Own Cloud) and public deployment.

Let’s look at how you can use Estuary Flow to connect Microsoft Ads to BigQuery.

Pre-requisites:

• User credentials with access to the Bing Ads account.
  
• The user must have an associated developer token.
  
• A new GCS bucket in the same region as your BigQuery destination dataset.
  
• A GCS service account with a generated key file and dataEditor, jobUser, and objectAdmin roles.

Step 1: Configure Microsoft Ads as the Source

• Sign in to your Estuary account. If you don’t have one, you can register for a new account with your Google, GitHub, or Azure credentials. It’ll only take two minutes of your time to get this done.

Alternatively, you can contact us to enable single sign-on.

• After signing in, you will be redirected to the dashboard, where you can click on the Sources option on the left-side pane.
  
• Then, click the + NEW CAPTURE button.

• On the Create Capture page that follows, you will see a Search connectors box. Search for the Bing Ads connector using this box.
  
• The connector will be displayed in the search results; click its Capture button.
  
• Next, you must specify all the necessary fields on the connector configuration page. This includes:
  • Name: A unique name for your capture.
  • Tenant ID: Your Microsoft developer application’s Tenant ID. Unless you need a different value, set this field to “common.”
  • Developer Token: The developer token associated with the user.
  • Reports replication start date: The desired earliest date of replication for report data.
    
• Click on AUTHENTICATE YOUR MICROSOFT ACCOUNT. In the pop-up, log in to your Microsoft account with access to Bing Ads. The connector uses OAuth2 to authenticate with Microsoft.
  
• Finally, click on NEXT > SAVE AND PUBLISH.

This completes the configuration of the connector, allowing it to capture your Bing Ads data.

Step 2: Configure BigQuery as the Destination

• To proceed with setting up the destination connector, click the MATERIALIZE COLLECTIONS button on the pop-up that follows a successful capture.

Alternatively, you can select the Destinations option on the left-side pane of the dashboard. Then, on the Destinations page, click the + NEW MATERIALIZATION button.

• You will be redirected to the Create Materialization page. Type BigQuery in the Search connectors box to find the connector.

• Click the Materialization button of the Google Bigquery connector.

• This will redirect you to the connector’s configuration page, where you must specify the following fields:
  • Name: A unique name for your materialization.
  • Project ID: The Google Cloud Project ID that owns the BigQuery dataset.
  • Service Account JSON: The service account JSON credentials for authorization.
  • Region: The location of the BigQuery dataset and bucket; both must be in the same region.
  • Dataset: A BigQuery dataset for bound collection tables and associated materialization metadata tables.
  • Bucket: The GCS bucket you intend to use for staging specifications and temporary data before loading into BigQuery
    
• In the Source Collections section, check if your captured Bing Ads data collection has been added to the materialization. If not, click the SOURCE FROM CAPTURE button to select the appropriate capture to link to your materialization.
  
• Then, click NEXT > SAVE AND PUBLISH.

After completing these steps, the connector will materialize your Bing Ads collection into BigQuery tables. This will complete the loading of data from Microsoft Ads to BigQuery.

Method 2: Connecting Bing Ads to BigQuery Using CSV Export/Import

This manual method involves exporting your Microsoft Ads data in CSV format, followed by uploading it into BigQuery. Let’s look into the details of how you can execute this.

Step 1: Export Microsoft Ads Data in CSV Format

There are two ways to export your Bing Ads data in CSV format. One involves using the Bing Ads Editor, and the other involves using the Bing Ads Bulk API.

Using Bing Ads Editor to Export Data in CSV Format

To get started, you must have Bing Ads Editor installed. Then,

• In the Bing Ads Editor Browser pane, right-click the ad group or campaign you want to export. Click Export.
• When you see the Export dialog box, click Export again.
• In the Save As dialog box, select the folder where you want to save the exported file.
• Provide a name for your file export in the File name box.
• Click Save.

Using Bing Ads Bulk API to Export Data in CSV Format

Bing Ads has a very rich API that you can use to create and run campaigns programmatically and to interact with the platform. The Bing Ads API is implemented using the SOAP protocol.

To download your campaign data, you can call the DownloadCampaignsByAccountIds operation. If you want to download specific campaign data, call the DownloadCampaignsByCampaignIds operation instead.

Whether you opt for all the campaign’s data or only the data that has changed since the last download, here are the steps to follow:

• Set the request’s DataScope element to include quality score data or bid suggestions along with entity data.
  
• Set the request’s DownloadFileType element to Csv for the format of the download file.
  
• For the request’s Entities element, you can include your campaign, ad group, ad, and keyword entities. Optionally, you may add negative keywords and targets as additional entities in the download.
  
• The request’s LastSyncTimeInUTC must be set to the timestamp of the previous download. This ensures that the request is only for the entities that have been deleted or updated since the last download.

However, if this is the first time you’re requesting a download, you can set this element to NULL to download all available entities.

• Finally, submit your download request with either the DownloadCampaignsByAccountIds or DownloadCampaignsByCampaignIds operation.
  
• The download request will return an identifier that you can pass to the GetBulkDownloadStatus operation.
  
• When the GetBulkDownloadStatus operation is complete, it will return the URL of the download file. You can use this URL to copy the download file locally.

The file that is downloaded to your system will be compressed in zip format. You can unzip the file to access the data.

Step 2: Import Bing Ads CSV Files into BigQuery

You can load your Bing Ads CSV data from a local file into a new BigQuery table or append to or overwrite an existing table. However, ensure you have a BigQuery dataset to store your data before starting the process.

Here are the steps to import your CSV data into BigQuery using the BigQuery web UI:

• Sign in to your Google Cloud account and open the BigQuery web UI.
  
• In the Explorer pane, expand your project and select the dataset to which you want to upload the new data.
  
• Click on CREATE TABLE in the Data set info section on the right side of the UI.

• In the Create table panel, specify the following fields:
  • Create table from: Choose Upload (or Google Cloud Storage or Drive) to upload the data from your local system.
  • Select file: You can browse the location of the file on your system to upload it.
  • File format: Select CSV since you’re uploading a CSV file.
  • Table: For the specified project and data set, mention a table name for the new table.
  • In the Schema section, you can enable Auto-detect or manually enter the schema information.

• After providing the necessary information, click the CREATE TABLE button. This will result in a new table created with your Bing Ads data from the CSV file.

Drawbacks of Using CSV Export/Import for Connecting Bing Ads to BigQuery

• Lacks Real-Time Integration Capabilities: Manually exporting data from Bing Ads as CSV and importing it into BigQuery is time-consuming. This prevents real-time data analysis, impacting timely decision-making and reducing the effectiveness of marketing strategies.
  
• Requires Intensive Manual Efforts: If you want to work with fresh data, you’ll have to repeat the CSV export/import steps every time your Bing Ads data updates. This is labor-intensive and diverts efforts from more critical analysis or strategic tasks.

Use Cases for Bing Ads to BigQuery Integration

• Optimizing Ad Performance: By integrating your Bing Ads data into BigQuery, you can analyze the performance of your ads at a granular level. With this, you can identify any scope for improvement and tailor your strategies accordingly. The result is better-targeted campaigns, optimized ad placements, and higher CTRs and conversions.
  
• Campaign ROI Analysis: With your Bing Ads data in BigQuery, you can measure the effectiveness of each campaign by merging sales or conversion and cost data. By calculating the ROI, you can adjust your budgets or strategies for maximizing returns.
  
• Customer Segmentation: You can enhance customer segmentation by integrating your Bing Ads data into BigQuery. When you combine user behavior and demographic data with advertising data, you can identify unique patterns and trends in customer interactions. This helps you tailor your advertising techniques to target particular segments effectively. You can benefit from improved customer satisfaction, retention, and engagement rates.
  
• Centralize Data Management: By consolidating multiple Bing Ads campaigns within BigQuery, you can easily manage and analyze your marketing data. This allows you to extract vital insights leading to better performance. It also helps improve collaboration between different marketing teams and streamline your reporting processes.

Conclusion

A Bing Ads to BigQuery integration can be valuable for marketing teams as well as data teams, helping promote overall business. The associated benefits include real-time performance tracking, improved customer segmentation, and enhanced marketing decisions, among others.

To load your Bing Ads data into BigQuery, you can opt for the CSV export/import technique. However, this manual method is time-consuming, effort-intensive, and lacks real-time integration capabilities. An automated integration tool like Estuary Flow can help you overcome these drawbacks.

The range of connectors, real-time and CDC capabilities, and varied deployment options make Estuary Flow an excellent choice for your data integration needs.

Estuary provides you with a cost-effective and simple solution to work with real-time data and improve your overall productivity. It’ll take only minutes to set up your integration pipelines and have them running and only days to deliver new projects instead of months. Get started with your first data integration pipeline using Estuary!

FAQs

Is Bing Ads Better Than Google Ads?

Bing Ads is better than Google Ads in certain aspects. These include lower costs per click and more granular targeting at the ad group level. Bing Ads also offers targeting based on device type and OS. However, Google Ads generally provides a broad reach due to its large user base. Businesses may decide to use both so they can reach customers on either platform.

To compare, also see How to Move Google Ads Data to BigQuery.

Is Google BigQuery a SQL Database?

Google BigQuery is not a traditional SQL database; it’s a fully managed, serverless data warehouse that supports SQL for querying large-scale data. BigQuery uses GoogleSQL, which is compliant with the SQL 2011 standard and also supports a legacy SQL dialect. Features such as DDL and DML statements are only supported by GoogleSQL.

What is the Conversion Rate for Bing Ads?

Bing Ads has an average conversion rate (CVR) of 2.94% across all industries. While the rate variation can be pretty significant depending on the industry, this CVR is slightly better than that of Google Ads.