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

An embedded OAuth application is provided 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 Jitterbit 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