This page provides a guide to Establishing a Connection to Microsoft Ads in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Microsoft Ads

Establishing a Connection shows how to authenticate to Microsoft Ads and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Microsoft Ads through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to Microsoft Ads by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

Connecting to Microsoft Ads

You need to register a Microsoft Ads account, get a developer token, and authenticate using OAuth to connect to the Microsoft Ads APIs.

Register a Bing Ads Account

Complete the following steps to sign up for a Microsoft Ads account and obtain the CustomerId and (optional) AccountId:

1. Sign up for a Microsoft Ads account in the Bing Ads Web application or at the Sandbox Site for a sandbox account.
2. Select Settings > Accounts and Billing and select a single account. The AccountId and CustomerId (cid) are displayed on the account's page and in the page's URL.

Get a Developer Token

To use the Microsoft Ads APIs, you must have a DeveloperToken in addition to the CustomerId. You must have the Super Admin role in Microsoft Ads to create a developer token, though you do not need special permissions to use the token.

1. Log in at the Bing Ads Developer Portal as a Microsoft Account user with the Microsoft Ads Super Admin role.
2. Click Request Token and take note of your developer token. You will set it in the DeveloperToken connection property.

Authenticating to Microsoft Ads

OAuth

AuthScheme must be set to OAuth in all user account flows.

Customizing the SSL Configuration

By default, the Cloud attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Cloud uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The CData Cloud models entities in the Microsoft Ads API as database views and stored procedures. These are defined in schema files, which are simple, text-based configuration files.

The following sections provide information on executing SQL to the available API objects. The Cloud offloads as much of the SELECT statement processing as possible to Microsoft Ads and then processes the rest of the query in memory. The Cloud uses the Microsoft Ads API to process supported filters. The Cloud processes other filters client-side within the Cloud.

Views

The Cloud Microsoft Ads objects as Views, including reports.

You can query Reports based on an aggregation and time period. The default time period is this year; to change the time period, see the following Time Filter section.

When retrieving only some report columns, you should select all columns marked as 'Select Requirement' and at least one of the columns marked as 'Metric' to issue a valid report request. On the other hand, if you are retrieving all columns, then the driver defaults to including the minimally required columns in the request.

Specifying a Time Filter

You can specify date ranges in the WHERE clause using the "Date" field with these operators: =, <,>

These predefined date fields are supported:

• Today
• Yesterday
• LastSevenDays
• ThisWeek
• LastWeek
• LastFourWeeks
• ThisMonth
• LastMonth
• LastThreeMonths
• LastSixMonths
• ThisYear
• LastYear

To search on a predefined date range, set the DatePreset pseudo column in the WHERE clause to one of the predefined date fields.

SELECT Monthly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM [ AccountPerformanceReport ] WHERE DatePreset = 'LastYear'

SELECT Monthly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM [ AccountPerformanceReport ] WHERE StartDate = '2014-01-01' AND EndDate = '2017-09-19'

Aggregation is the process of reducing and summarizing data. You can aggregate the report data by selecting one of the following aggregation columns:

• Daily: Each row of the report identifies the month, day, and year when the transaction occurred. The report data is aggregated by each day. The report includes a column named Daily that contains the day formatted as yyyy-mm-dd.
  

  SELECT Daily, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM AccountPerformanceReport

• DayOfWeek: Each row of the report identifies the day of the week when the transaction occurred. The report data is aggregated by each of the seven days in a week. The report includes a column named DayOfWeek, and the possible values are 1 - 7 where 1 represents Sunday and 7 represents Saturday.

  If the report time spans multiple weeks, then the performance data across all weeks for a given day of the week is aggregated in one row. For example, if Campaign A has 5 impressions every Monday (day 2) throughout each of the 3 weeks included in the report time range, then the report includes one row with DayOfWeek set to 2 and impressions in that row totaling 15.
  

  SELECT DayOfWeek, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM AccountPerformanceReport

• Hourly: Each row of the report identifies the hour when the transaction occurred. The report data is aggregated by each hour of the day. The report includes a column named Hourly, and the possible values are 0 - 23.
  

  SELECT Hourly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM AccountPerformanceReport

• HourOfDay: Each row of the report identifies the hour of the day when the transaction occurred. The report data is aggregated by each of the 24 hours across all days. The report includes a column named HourOfDay, and the possible values are 0 - 23.

  If the report time spans multiple days, then the performance data across all days for a given hour is aggregated in one row. For example, if Campaign A has 5 impressions during hour 7 on each of the 3 days included in the report time range, then the report includes one row with impressions for HourOfDay totaling 15.
  

  SELECT HourOfDay, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM AccountPerformanceReport

• Monthly: Each row of the report identifies the month when the transaction occurred. The report data is aggregated by each month. The report includes a column named Monthly that contains the first day of the month formatted as yyyy-mm-dd.
  

  SELECT Monthly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM AccountPerformanceReport

• Weekly: Each row of the report identifies the week when the transaction occurred. The report data is aggregated by each week. The report includes a column named Weekly that contains the date of the Sunday for each week formatted as yyyy-mm-dd.
  

  SELECT Weekly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM AccountPerformanceReport

• Yearly: Each row of the report identifies the year when the transaction occurred. The report data is aggregated by each year. The report includes a column named Yearly that contains the year formatted as yyyy.
  

  SELECT Yearly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM AccountPerformanceReport

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - Microsoft Ads Views

Use this report to observe long-term account performance and trends.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query the available Accounts in Bing Ads.

Table Specific Information

Select

SELECT * FROM Accounts

Columns

Query the available Assets linked to Ads in Bing Ads.

Table Specific Information

Select

The following queries are processed server-side:

SELECT * FROM AdAssets
SELECT * FROM AdAssets WHERE AdGroupId = '2562405'
SELECT * FROM AdAssets WHERE AdGroupId IN ('1562405', '2562405', '3562405')
SELECT * FROM AdAssets WHERE AdType = 'ResponsiveAd'
SELECT * FROM AdAssets WHERE AdType IN ('ResponsiveAd', 'ResponsiveSearch')
SELECT * FROM AdAssets WHERE AccountId = '2562405'
SELECT * FROM AdAssets WHERE AccountId IN ('1562405', '2562405', '3562405')

Columns

Use this report to identify which dynamic text strings are performing well and which strings you should consider changing.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query the available Assets linked to Ad Extensions in Bing Ads.

Table Specific Information

Select

The following queries are processed server-side:

SELECT * FROM AdExtensionAssets
SELECT * FROM AdExtensionAssets WHERE AdExtensionId = '2562405'
SELECT * FROM AdExtensionAssets WHERE AdExtensionId IN ('1562405', '2562405', '3562405')
SELECT * FROM AdExtensionAssets WHERE AdExtensionType = 'ImageAdExtension'
SELECT * FROM AdExtensionAssets WHERE AccountId = '2562405'
SELECT * FROM AdExtensionAssets WHERE AccountId IN ('1562405', '2562405', '3562405')

Columns

Use this report to compare how well different versions of your ad extensions are performing with each ad.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Use this report to compare how well different versions of your ad extensions are performing for each keyword.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Use this report to discover the effectiveness of individual ad extension items, for example, each link of a sitelink extension.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query the available Ad Extensions in Bing Ads.

Table Specific Information

Select

The following queries are processed server-side:

SELECT * FROM AdExtensions
SELECT * FROM AdExtensions WHERE Id = '2562405'
SELECT * FROM AdExtensions WHERE Id IN ('1562405', '2562405', '3562405')
SELECT * FROM AdExtensions WHERE Type = 'ActionAdExtension'
SELECT * FROM AdExtensions WHERE Type IN ('ActionAdExtension', 'AppAdExtension')
SELECT * FROM AdExtensions WHERE AccountId = '2562405'
SELECT * FROM AdExtensions WHERE AccountId IN ('1562405', '2562405', '3562405')

Columns

Use this report to more broadly compare delivery performance statistics by ad group, campaign, or account attributes rather than at the keyword level.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query the available AdGroups in Bing Ads.

Table Specific Information

Select

The following queries are processed server-side:

SELECT * FROM AdGroups
SELECT * FROM AdGroups WHERE CampaignId = '234505536'

Columns

Use this report to help you determine which ads lead to clicks and conversions, and which are not performing. Having underperforming ads in your account can pull down the quality of your campaigns.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query the available Ads in Bing Ads.

Table Specific Information

Select

The following queries are processed server-side:

SELECT * FROM Ads
SELECT * FROM Ads WHERE AdGroupId = '5179946006'
SELECT * FROM Ads WHERE Type = 'Text'
SELECT * FROM Ads WHERE Type IN ('ExpandedText','Text')
SELECT * FROM Ads WHERE AdGroupId = '5179946006' AND Type = 'Text'
SELECT * FROM Ads WHERE AdGroupId IN ('5179946006',''5179946007'') AND Type = 'Text'
SELECT * FROM Ads WHERE AdGroupId IN ('5179946006',''5179946007'') AND Type IN ('ExpandedText','Text')
SELECT * FROM Ads WHERE AdGroupId = '1297424037031698' AND Type = 'DynamicSearch' AND AccountId = '135093022'
SELECT * FROM Ads WHERE AccountId IN ('2562405', '141163457', '180394748')

Columns

Use this report to discover how your campaigns and ad groups are resonating with audiences of diverse age and gender.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query the available Assets linked to Asset Groups in Bing Ads.

Table Specific Information

Select

The following queries are processed server-side:

SELECT * FROM AssetGroupAssets
SELECT * FROM AssetGroupAssets WHERE CampaignId = '2562405'
SELECT * FROM AssetGroupAssets WHERE CampaignId IN ('1562405', '2562405', '3562405')
SELECT * FROM AssetGroupAssets WHERE AccountId = '2562405'
SELECT * FROM AssetGroupAssets WHERE AccountId IN ('1562405', '2562405', '3562405')

Columns

Query the available Asset Groups in Bing Ads.

Table Specific Information

Select

The following queries are processed server-side:

SELECT * FROM AssetGroups
SELECT * FROM AssetGroups WHERE CampaignId = '2562405'
SELECT * FROM AssetGroups WHERE CampaignId IN ('1562405', '2562405', '3562405')
SELECT * FROM AssetGroups WHERE AccountId = '2562405'
SELECT * FROM AssetGroups WHERE AccountId IN ('1562405', '2562405', '3562405')

Columns

Use this report to compare delivery performance statistics by audience, ad group, campaign, or account attributes.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query the available Audiences in Bing Ads.

Table Specific Information

Select

The following queries are processed server-side:

SELECT * FROM Audiences
SELECT * FROM Audiences WHERE Type = 'RemarketingList'
SELECT * FROM Audiences WHERE AccountId = '65098547' AND Type = 'RemarketingList'

Columns

Query the available Bid Opportunities in Bing Ads.

Table Specific Information

Select

The following queries are processed server-side:

SELECT * FROM BidOpportunities
SELECT * FROM BidOpportunities WHERE OpportunityType = 'FirstPage'

Columns

Query the available Budget Opportunities in Bing Ads.

Table Specific Information

Select

The following queries are processed server-side:

SELECT * FROM BudgetOpportunities
SELECT * FROM BudgetOpportunities WHERE CampaignId = '234505536'

Columns

Query the available Budgets in Bing Ads.

Table Specific Information

Select

The following queries are processed server-side:

SELECT * FROM Budgets
SELECT * FROM Budgets WHERE CampaignId = '431046229' AND AccountId = '65098547'

Columns

Use this report to discover how your campaign's budget is performing for the month. For example, the report shows your monthly budget, how much you have spent to date, and whether you are on target to spend your monthly budget.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Use this report to view high-level performance statistics and quality attributes for each campaign or account. This is also a quick way to flag any major campaign or account problems.

Columns