Microsoft Advertising Connection Details¶

Introduction¶

Connector Version

This documentation is based on version 23.0.8803 of the connector.

Get Started¶

Microsoft Ads Version Support

The connector models reports and other entities in the Microsoft Ads API as relational views.

Establish a Connection¶

Connect 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.

Authenticate to Microsoft Ads¶

OAuth¶

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

Desktop Applications¶

provides an embedded OAuth application that simplifies OAuth desktop Authentication. Alternatively, you can create a custom application. See Creating a Custom OAuth App for information on creating custom applications and reasons for doing so.

For authentication, the only difference between the two methods is that you must set two additional connection properties when using custom OAuth applications.

After setting the following connection properties, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthClientId: (custom applications only) Set to the Client ID in your application settings.
• OAuthClientSecret: (custom applications only) Set to the Client Secret in your application settings.

When you connect, the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process as follows:

• Extracts the access token from the callback URL.
• Obtains a new access token when the old one expires.
• Saves OAuth values in OAuthSettingsLocation so that they persist across connections.

Create a Custom OAuth App¶

When to Create a Custom OAuth App¶

embeds OAuth Application Credentials with branding that can be used when connecting via either a Desktop Application or from a Headless Machine. Creating a custom OAuth application is, however, required when using a web application. You may choose to create your own OAuth Application Credentials when you want to

• control branding of the Authentication Dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

Follow the steps below to create a custom OAuth app and obtain the connection properties in a specific OAuth authentication flow.

Create a Custom OAuth App¶

You can create a custom OAuth application for desktop applications, web applications, and headless machines. The steps are identical except for how the Redirect URI is defined.

Desktop Applications¶

Follow these steps:

1. If you have not done so already, sign up for a Bing Ads account in the Bing Ads Web application or at the Sandbox Site for a sandbox account.
2. Go to the Azure portal (Azure test portal for sandbox accounts), and login with your Microsoft Account credentials when prompted.
3. Under Azure Active Directory > App registrations, click New registration.
4. Provide the application name and select an appropriate Supported account type. If you do not choose to allow personal Microsoft accounts, users with personal accounts are prevented from authenticating.
5. How Re In the Redirect URI section, you must set an appropriate URL. For desktop applications a value such as http://localhost:33333 or another port of your choice may be set.
6. Register your application and take note of your Application (client) Id. You will use it as the OAuthClientId value in the OAuth grant flow.
7. In the Certificates & secrets section, click New client secret to generate a client secret and take note of the resulting value. You will use it as the OAuthClientSecret value.

Important Notes¶

Configuration Files and Their Paths¶

• All references to adding configuration files and their paths refer to files and locations on the Harmony Agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features¶

This section details a selection of advanced features of the Microsoft Ads connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Microsoft Ads and then processes the rest of the query in memory (client-side).

User Defined Views¶

The Jitterbit Connector for Microsoft Ads allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File¶

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM AdGroups WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements¶

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View¶

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View¶

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View¶

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views¶

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views¶

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration¶

Customize the SSL Configuration¶

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model¶

The Jitterbit Connector for Microsoft Ads 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 connector offloads as much of the SELECT statement processing as possible to Microsoft Ads and then processes the rest of the query in memory; API limitations and requirements are documented in this section.

Views

The connector 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¶

Date Ranges

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

The default DatePreset value is "ThisYear".

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'

To specify a custom date range, specify the StartDate and EndDate pseudo columns in the WHERE clause.

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

Aggregation

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¶

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.

Jitterbit Connector for Microsoft Ads Views¶

AccountPerformanceReport¶

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.

Accounts¶

Query the available Accounts in Bing Ads.

Table Specific Information¶

Select¶

Searches on this table are processed client-side by the connector; the following query is processed server-side.

SELECT * FROM [ Accounts ]

The client-side execution of the query can be turned off by setting SupportEnhancedSQL to false in which case any query other than SELECT * will return an error.

Columns¶

AdDynamicTextPerformanceReport¶

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.

AdExtensionByAdReport¶

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.

AdExtensionByKeywordReport¶

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.

AdExtensionDetailReport¶

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.

AdGroupPerformanceReport¶

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.

AdGroups¶

Query the available AdGroups in Bing Ads.

Table Specific Information¶

Select¶

You must specify the CampaignId column in the WHERE clause to query this table. The connector will use the Microsoft Ads API to filter the results by CampaignId while the rest of the filter is executed client side within the connector.

For example, the following query is processed server side:

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

The client-side execution of the query can be turned off by setting SupportEnhancedSQL to false in which case any search criteria with columns in the WHERE clause other than CampaignId will cause an error.

Columns¶

AdPerformanceReport¶

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.

Ads¶

Query the available Ads in Bing Ads.

Table Specific Information¶

Select¶

You must specify the AdGroupId and Type columns in the WHERE clause to query this table. The connector uses the Microsoft Ads API to filter the results by AdGroup and Type, while the rest of the filter is executed client side within the connector.

For example, the following query is processed server side:

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

Columns¶

AgeGenderAudienceReport¶

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.

AudiencePerformanceReport¶

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.

Audiences¶

Query the available Audiences in Bing Ads.

Table Specific Information¶

Select¶

Searches on this table are processed client-side by the connector; the following query is executed server-side.

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

Columns¶

BidOpportunities¶

Query the available Bid Opportunities in Bing Ads.

Table Specific Information¶

Select¶

You must specify the OpportunityType column in the WHERE clause to query this table. You can specify the following values for OpportunityType: FirstPage, MainLine, and MainLine1.

The connector will use the Microsoft Ads API to filter the results by OpportunityType while the rest of the filter is executed client side within the connector. For example, the following query is processed server side.

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

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria that refers to columns other than OpportunityType will cause an error.

Columns¶

BudgetOpportunities¶

Query the available Budget Opportunities in Bing Ads.

Table Specific Information¶

Select¶

You must specify the CampaignId column in the WHERE clause to query this table. The connector will use the Microsoft Ads API to filter the results by CampaignId while the rest of the filter is executed client side within the connector.

For example, the following query is processed server side:

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

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria in the WHERE clause that refers to columns other than CampaignId will cause an error.

Columns¶

Budgets¶

Query the available Budgets in Bing Ads.

Table Specific Information¶

Select¶

Searches on this table are processed client-side by the connector; the following query is processed server-side:

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

Columns¶

BudgetSummaryReport¶

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.

CampaignPerformanceReport¶

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¶

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.

Campaigns¶

Query the available Campaigns in Bing Ads.

Table Specific Information¶

Select¶

Searches on this table are processed client-side by the connector; the following query is processed server-side:

SELECT * FROM [ Campaigns ]

Columns¶

ConversionPerformanceReport¶

Use this report to understand which campaigns and keywords are leading an audience to complete a purchase or other conversion action.

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.

DestinationUrlPerformanceReport¶

Use this report to identify landing pages that met audience expectations and potentially resulted in high click-through or conversion rates.

Columns¶