Microsoft Advertising (v2) | Stitch Documentation

Microsoft Advertising is supported by Stitch
This integration is powered by Singer's Bing Ads tap and certified by Stitch. Check out and contribute to the repo on GitHub.

For support, contact Stitch support.

Microsoft Advertising integration summary

Stitch’s Microsoft Advertising integration replicates data using the Bing Ads v.13 API . Refer to the Schema section for a list of objects available for replication.

Microsoft Advertising feature snapshot

A high-level look at Stitch's Microsoft Advertising (v2) integration, including release status, useful links, and the features supported in Stitch.

STITCH
Release status	Released on October 4, 2018	Supported by	Stitch
Stitch plan	Standard	API availability	Available
Singer GitHub repository	singer-io/tap-bing-ads
REPLICATION SETTINGS
Anchor Scheduling	Supported	Advanced Scheduling	Unsupported
Table-level reset	Unsupported	Configurable Replication Methods	Unsupported
DATA SELECTION
Table selection	Supported	Column selection	Supported
Select all	Unsupported
TRANSPARENCY
Extraction Logs	Supported	Loading Reports	Supported

Connecting Microsoft Advertising

Microsoft Advertising setup requirements

To set up Microsoft Advertising in Stitch, you need:

To pause all ad-blocking software currently in use. Because Microsoft Advertising authentication uses pop ups, you may encounter issues if ad blockers aren’t disabled during the setup.
To have Viewer permissions to the profiles you want to connect. These read-only permissions will ensure Stitch can read data from the profiles you select for replication. Read more about Microsoft Advertising user permissions here.

Step 1: Add Microsoft Advertising as a Stitch data source

Sign into your Stitch account.
On the Stitch Dashboard page, click the Add Integration button.
Click the Microsoft Advertising icon.
Enter a name for the integration. This is the name that will display on the Stitch Dashboard for the integration; it’ll also be used to create the schema in your destination.

For example, the name “Stitch Microsoft Advertising” would create a schema called stitch_microsoft_advertising in the destination. Note: Schema names cannot be changed after you save the integration.

Step 2: Define the historical replication start date

Note: Retention windows and historical data

Microsoft Advertising retains reporting data for specified periods of time, which can impact the amount of historical data Stitch can replicate. Refer to the Report retention periods and historical data section below for more info.

The Sync Historical Data setting defines the starting date for your Microsoft Advertising integration. This means that data equal to or newer than this date will be replicated to your data warehouse.

Change this setting if you want to replicate data beyond Microsoft Advertising’s default setting of 1 year. For a detailed look at historical replication jobs, check out the Syncing Historical SaaS Data guide.

Step 3: Create a replication schedule

Replication schedules affect the time Extraction begins, not the time to data loaded. Refer to the Replication Scheduling documentation for more information.

In the Replication Frequency section, you’ll create the integration’s replication schedule. An integration’s replication schedule determines how often Stitch runs a replication job, and the time that job begins.

Microsoft Advertising integrations support the following replication scheduling methods:

To keep your row usage low, consider setting the integration to replicate less frequently. See the Understanding and Reducing Your Row Usage guide for tips on reducing your usage.

Important: API limits and Replication Frequency

Microsoft Advertising’s API enforces usage limits. As a result, a high Replication Frequency may exceed these limits and impact Stitch’s ability to replicate data. See the Replication section below for more details.

Step 4: Authorize Stitch and select Microsoft Advertising accounts

Selecting Bing Ads accounts.

Next, you’ll be prompted to log into your Microsoft account and to approve Stitch’s access to your Microsoft Advertising/Bing Ads data.
Click Authorize to continue.
After your credentials are validated, you’ll be prompted to select the Microsoft Advertising/Bing Ads account(s) you want to connect to Stitch.

If you don’t see the account(s) you want to connect, verify that you have completed the setup requirements.
When selecting accounts, keep the following in mind:
- Only one top-level manager account can be selected. If you need to connect multiple top-level accounts, we recommend creating additional Microsoft Advertising integrations.
- Selecting a subaccount will also select the parent, or top-level account. If you de-select the parent account, you will be unable to select any subaccounts.
- If multiple subaccounts are selected, data for all the selected subaccounts will map to the same table in your destination. For example: If two subaccounts are selected and the accounts table is tracked, account data for both accounts will be replicated into the accounts table. This is applicable to every table selected in the next step.
When finished selecting accounts, click Check and Save.

Step 5: Set objects to replicate

Is an object missing or not replicating? Verify that the object meets the requirements for selection and replication.

The last step is to select the tables and columns you want to replicate. Learn about the available tables for this integration.

Note: If a replication job is currently in progress, new selections won’t be used until the next job starts.

In the integration’s Tables to Replicate tab, locate a table you want to replicate.
To track a table, click the checkbox next to the table’s name. A blue checkmark means the table is set to replicate.
To track a column, click the checkbox next to the column’s name. A blue checkmark means the column is set to replicate.
Repeat this process for all the tables and columns you want to replicate.
When finished, click the Finalize Your Selections button at the bottom of the screen to save your selections.

Initial and historical replication jobs

After you finish setting up Microsoft Advertising, its Sync Status may show as Pending on either the Stitch Dashboard or in the Integration Details page.

For a new integration, a Pending status indicates that Stitch is in the process of scheduling the initial replication job for the integration. This may take some time to complete.

Initial replication jobs with Anchor Scheduling

If using Anchor Scheduling, an initial replication job may not kick off immediately. This depends on the selected Replication Frequency and Anchor Time. Refer to the Anchor Scheduling documentation for more information.

Free historical data loads

The first seven days of replication, beginning when data is first replicated, are free. Rows replicated from the new integration during this time won’t count towards your quota. Stitch offers this as a way of testing new integrations, measuring usage, and ensuring historical data volumes don’t quickly consume your quota.

Replication will continue after the seven days are over. If you’re no longer interested in this source, be sure to pause or delete the integration to prevent unwanted usage.

Microsoft Advertising replication

Report retention periods and historical data

Microsoft Advertising retains reporting data for specified periods of time. Depending on the type of report, the retention period can vary. For the majority of reports, however, Microsoft Advertising will retain data for 36 months from the current date.

This retention period can impact the historical data Stitch can replicate from Microsoft Advertising. If you select a Start Date further in the past than the retention period allows, a message similar to the following will surface in the integration’s Extraction Logs during the extraction phase:

tap - Message = "Bing reported that the requested report date range ended outside of their data retention period. Skipping to next range..."

When this occurs, Stitch will move the Start Date up a month until extraction succeeds. For example: If 01/09/2015 is found to be outside of the retention period, Stitch will move the date range up to 01/10/2015, then 01/11/2015, and so on until extraction is successful. This will all occur in the same extraction job.

Refer to Microsoft’s documentation for more info about retention periods.

Table types and replication

There are two types of tables in Stitch’s Microsoft Advertising integration: Core Object and Report.

Core Object tables contain foundational data that’s useful for analysis. These are the following tables:
- account
- ad_groups
- ads
- campaigns
With the exception of the accounts table, these tables are replicated using Full Table Replication.
Report tables are the various Microsoft Advertising reports. The replication process for these tables is a bit unlike that of other tables:
- Extraction: Data is extracted using a Conversion Window. A Conversion Window is a period of time after a customer clicks an ad that a conversion (ex: a purchase) is recorded in Microsoft Advertising.
Stitch currently uses a Conversion Window of 30 days.

This means that data from the past 30 days will be replicated during every replication job.
- Loading: Data is loaded into your data warehouse using Append-Only Replication.

For Report tables, this means that:

Due to the Conversion Window, a high Replication Frequency may not be necessary. Because Stitch will replicate data from the past 30 days during every replication job, recent data will be re-replicated and count towards your row quota.

To reduce your row usage and replicating redundant data, consider setting the integration to replicate less frequently. For example: every 12 or 24 hours.
Querying for the latest data in Report tables will require a different strategy than you might usually use. Stitch will add a column named _sdc_report_datetime to Report tables to help you identify the most recent records in a table. See the Query for the Latest Data section for more info and a sample query.

Each part of the replication process for Report tables is explained below.

Report Tables: Data extraction and Conversion Windows

Every time Stitch runs a replication job for Microsoft Advertising, the last 30 days’ worth of data will be replicated for any Report tables currently being tracked.

Stitch replicates data in this way to account for updates made during the Conversion Window.

Historical and full re-replications
Ongoing replications

For historical and full re-replications of Microsoft Advertising data, Stitch will query for and extract data newer than or equal to the date defined in the Start Date field in the Integration Settings page.

The Start Date, in conjunction with the Conversion Window, defines the minimum date Stitch should query for when extracting historical data. This is calculated as:

Start Date - Conversion Window = Minimum Extraction Date

Example

During the initial set up, the Start Date field is set to July 3, 2017, or 2017-07-03 00:00:00.

To account for the Conversion Window, Stitch would calculate the Minimum Extraction Date value as: 2017-07-03 00:00:00 - 30 days = 2017-06-03 00:00:00

If you were to write a SQL query using this date for the ad_performance_report table, it might look like this:

  SELECT *
    FROM bing_ads.ad_performance_report
   WHERE TimePeriod >= '2017-06-03 00:00:00'   /* Min. Extraction Date */
ORDER BY TimePeriod

For ongoing replication jobs, Stitch will query for and extract data using the last saved maximum value in the table’s Replication Key column and the Conversion Window for the table.

Note: This applies to every replication job that takes place after the historical replication job.

Example

The last maximum saved Replication Key value for the ad_performance_report table is 2017-10-01 00:00:00.

To account for the Conversion Window of 30 days, we’d subtract this from the last maximum saved Replication Key value:

2017-10-01 00:00:00 - 30 days = 2017-09-01 00:00:00

In this case, Stitch would query for and extract data that is newer than or equal to 2017-09-01 00:00:00 and older than or equal to 2017-10-01 00:00:00.

If this were a SQL query, it might look like this:

  SELECT *
    FROM ad_performance_report
   WHERE TimePeriod >= '2017-09-01 00:00:00'
                              /* max Replication Key value - Conversion Window */
     AND TimePeriod <= '2017-10-01 00:00:00'
                              /* max Replication Key value from previous job */
ORDER BY TimePeriod

Report Tables: Data loading and Append-Only Replication

When Stitch loads the extracted data for Report tables into your destination, it will do so using Append-Only Replication. This is a type of Incremental Replication where existing rows aren’t updated, but appended to the end of the table.

Additionally, the number of rows loaded into the table during each replication job is dependent on the combination of unique values in the dimension columns you track. See the Column Selection and Statistic Aggregation section for more info and examples.

Example

Let’s say these columns are tracking in the ad_performance_report table:

campaignId (dimension) - This is the ID associated with a campaign. In this example, there are two campaigns: 929007494 and 929599581
deviceType (dimension) - The device type. There are two values for this example: Computer and Tablet
impressions (metric) - The total number of impressions.

Every time Stitch replicates and loads data, a row for each unique combination of the dimension columns will be appended to the end of the table:

_sdc_report_datetime	TimePeriod	AccountId	campaignId	deviceType	impressions
2017-10-01 17:48:26.791	2017-09-10 00:00:00	71086605	929007494	Computer	61
2017-10-01 17:48:26.791	2017-09-10 00:00:00	71086605	929007494	Tablet	15
2017-10-01 17:48:26.791	2017-09-10 00:00:00	71086605	929599581	Computer	37
2017-10-01 17:48:26.791	2017-09-10 00:00:00	71086605	929599581	Tablet	9

Report Tables: Query for the latest data

You may need to alter this query

The query below is meant to act as a foundation for writing your own. Depending on your SQL syntax and the dimensions contained in a given table, you may need to alter this query for your own usage.

When querying your Microsoft Advertising data, you’ll want to include the Dimensions you’re analyzing in the partition. For example: If you’re analyzing campaigns over time, you’d include columns like campaignId and date.

If you want only the most recently replicated data for any Microsoft Advertising Report table, you can use the sample query below to account for the Append-Only Replication Stitch uses.

This query uses two columns - which are automatically included for every Report table - to return the latest data:

TimePeriod - The day that the record pertains to.
_sdc_report_datetime - The starting time of the Stitch job that extracted the record.

SELECT *
  FROM (
        SELECT *,
        RANK() OVER (PARTITION BY TimePeriod, 
                     ORDER BY _sdc_report_datetime DESC)
        FROM ad_performance_report
        ORDER BY TimePeriod ASC
       ) AS latest
 WHERE latest.rank = 1

In this query:

A subquery first uses a window function to create a ‘window’ of data for each TimePeriod,
The values of the _sdc_report_datetime column are ranked within each window partition, and
Then, in the outer query, only the rows with _sdc_report_datetime values ranked as 1 - which is equal to the maximum timestamp - are returned.

Microsoft Advertising table reference

Schemas and versioning

Schemas and naming conventions can change from version to version, so we recommend verifying your integration’s version before continuing.

The schema and info displayed below is for version 2 of this integration.

This is the latest version of the Microsoft Advertising integration.

Report Tables: Column selection and statistic aggregation

The dimension columns selected for replication in Report tables can impact how performance statistics are aggregated. Additionally, this can also affect the number of rows replicated and loaded into your destination.

For example: if gregorianDate, accountId, campaignId, deviceType, and impressions were selected, the impressions column would contain the total number of impressions for the device type for that date:

gregorianDate	accountId	campaignId	deviceType	impressions
2018-01-05 00:00:00	71086605	240531207	Computer	4
2018-01-05 00:00:00	71086605	240531207	Smartphone	3
2018-01-05 00:00:00	71086605	240531207	Tablet	3
2018-01-05 00:00:00	71086605	240531207	Unknown	1

If the network column were also selected, a row for every unique combination of deviceType and network would be created and impressions would be aggregated accordingly:

gregorianDate	accountId	campaignId	deviceType	network	impressions
2018-01-05 00:00:00	71086605	240531207	Computer	AOL Search	3
2018-01-05 00:00:00	71086605	240531207	Smartphone	AOL Search	1
2018-01-05 00:00:00	71086605	240531207	Tablet	AOL Search	0
2018-01-05 00:00:00	71086605	240531207	Unknown	AOL Search	0
2018-01-05 00:00:00	71086605	240531207	Computer	Bing and Yahoo! search	1
2018-01-05 00:00:00	71086605	240531207	Smartphone	Bing and Yahoo! search	2
2018-01-05 00:00:00	71086605	240531207	Tablet	Bing and Yahoo! search	3
2018-01-05 00:00:00	71086605	240531207	Unknown	Bing and Yahoo! search	1

Table and column names in your destination

Depending on your destination, table and column names may not appear as they are outlined below.

For example: Object names are lowercased in Redshift (CusTomERs > customers), while case is maintained in PostgreSQL destinations (CusTomERs > CusTomERs). Refer to the Loading Guide for your destination for more info.

accounts

The accounts table contains high-level information about each of the ad accounts you selected during setup. Each row in this table corresponds to a single account.

This is a Core Object table.

Schema changes from Bing Ads v1

In this version of Stitch’s Microsoft Advertising integration, the schema of this table has changed. This was done to ensure consistency between the fields in our integration and the changes made by Microsoft to the Bing Ads API.

CurrencyType is now CurrencyCode
AccountType has been removed
CountryCode has been removed
AutoTagType has been added
BusinessAddress has been added
SoldToPaymentInstrumentId has been added

Replication Method	Key-based Incremental
Primary Keys	Id LastModifiedTime
Replication Key	LastModifiedTime
Useful links	Microsoft Advertising documentation

accounts table foreign keys

Join accounts with	on
ad_performance_report	accounts.Id = ad_performance_report.AccountId
ad_group_performance_report	accounts.Id = ad_group_performance_report.AccountId
age_gender_audience_report	accounts.Id = age_gender_audience_report.AccountId
audience_performance_report	accounts.Id = audience_performance_report.AccountId
campaign_performance_report	accounts.Id = campaign_performance_report.AccountId
geographic_performance_report	accounts.Id = geographic_performance_report.AccountId
goals_and_funnels_report	accounts.Id = goals_and_funnels_report.AccountId
keyword_performance_report	accounts.Id = keyword_performance_report.AccountId
search_query_performance_report	accounts.Id = search_query_performance_report.AccountId

accounts table schema

AccountFinancialStatus

STRING

AccountLifeCycleStatus

STRING

AutoTagType

STRING

BillToCustomerId

INTEGER

BusinessAddress

ARRAY

Click to expand BusinessAddress

BusinessName

STRING

City

STRING

CountryCode

STRING

Line1

STRING

Line2

STRING

Line3

STRING

Line4

STRING

PostalCode

STRING

StateOrProvince

STRING

TimeStamp

DATE-TIME

CurrencyCode

STRING

ForwardCompatibilityMap

ARRAY

Click to expand ForwardCompatibilityMap

KeyValuePairOfStringString

ARRAY

Click to expand KeyValuePairOfStringString

key

STRING

value

STRING

INTEGER

Language

STRING

LastModifiedByUserId

INTEGER

LastModifiedTime

DATE-TIME

Name

STRING

Number

STRING

ParentCustomerId

INTEGER

PauseReason

INTEGER

PaymentMethodId

INTEGER

PaymentMethodType

STRING

PrimaryUserId

INTEGER

SoldToPaymentInstrumentId

STRING

Timestamp

TIMESTAMP

Timezone

STRING

The ads table contains info about the following ad types:

AppInstall
DynamicSearch
ExpandedText
Product
Text
Image

This is a Core Object table.

Schema changes from Bing Ads v1

Domain has been added
TextPart2 has been added
TitlePart3 has been added

Replication Method

Full Table

Primary Key

Useful links

Microsoft Advertising documentation

Microsoft Advertising API method

ads table foreign keys

Join ads with	on
ad_performance_report	ads.Id = ad_performance_report.AdId

ads table schema

AdFormatPreference

STRING

DevicePreference

INTEGER

Domain

STRING

EditorialStatus

STRING

FinalAppUrls

STRING

Click to expand FinalAppUrls

string

ARRAY

Click to expand string

value

STRING

FinalMobileUrls

ARRAY

Click to expand FinalMobileUrls

string

ARRAY

Click to expand string

value

STRING

FinalUrls

STRING

Click to expand FinalUrls

string

ARRAY

Click to expand string

value

STRING

ForwardCompatibilityMap

ARRAY

Click to expand ForwardCompatibilityMap

KeyValuePairOfStringString

ARRAY

Click to expand KeyValuePairOfStringString

key

STRING

value

STRING

INTEGER

Status

STRING

TextPart2

STRING

TitlePart1

STRING

TitlePart2

STRING

TitlePart3

STRING

TrackingUrlTemplate

STRING

Type

STRING

UrlCustomParameters

STRING

ad_groups

The ad_groups table contains info about the ad groups associated with the campaigns in your Bing Ads account.

This is a Core Object table.

Schema changes from Bing Ads v1

AdDistribution has been removed
ContentMatchBid has been removed
NativeBidAdjustment has been removed
PricingModel has been removed
RemarketingTargetingSetting has been removed
SearchBid has been removed
AudienceAdsBidAdjustment has been added
CpcBid has been added
Additional fields have been added to BiddingScheme and Settings

Replication Method

Full Table

Primary Key

Useful links

Microsoft Advertising documentation

Microsoft Advertising API method

ad_groups table foreign keys

Join ad_groups with	on
ad_performance_report	ad_groups.id = ad_performance_report.AdGroupId
ad_group_performance_report	ad_groups.id = ad_group_performance_report.adGroupId
age_gender_audience_report	ad_groups.id = age_gender_audience_report.AdGroupId
audience_performance_report	ad_groups.id = audience_performance_report.AdGroupId
geographic_performance_report	ad_groups.id = geographic_performance_report.AdGroupId
goals_and_funnels_report	ad_groups.id = goals_and_funnels_report.AdGroupId
keyword_performance_report	ad_groups.id = keyword_performance_report.AdGroupId
search_query_performance_report	ad_groups.id = search_query_performance_report.AdGroupId

ad_groups table schema

AdRotation

STRING

AudienceAdsBidAdjustment

INTEGER

BiddingScheme

OBJECT

Click to expand BiddingScheme

InheritedBidStrategyType

STRING

Type

STRING

CpcBid

STRING

EndDate

DATE-TIME

ForwardCompatibilityMap

ARRAY

Click to expand ForwardCompatibilityMap

KeyValuePairOfStringString

ARRAY

Click to expand KeyValuePairOfStringString

key

STRING

value

STRING

Language

STRING

Name

STRING

Network

STRING

Settings

ARRAY

Click to expand Settings

BidBoostValue

NUMBER

BidMaxValue

NUMBER

BidOption

STRING

DomainName

STRING

Language

STRING

LocalInventoryAdsEnabled

BOOLEAN

PageFeedIds

ARRAY

Click to expand PageFeedIds

value

INTEGER

Priority

INTEGER

SalesCountryCode

STRING

Source

STRING

StoreId

INTEGER

Type

STRING

StartDate

DATE-TIME

Status

STRING

TrackingUrlTemplate

STRING

UrlCustomParameters

ARRAY

INTEGER

ad_group_performance_report

The ad_group_performance_report table contains performance data for ad groups, aggregated by day.

This is a Report table. See the Replication section for information on how data is replicated and loaded for this table.

Column compatibility

As per Microsoft’s attribute selection rules, some columns may be incompatible. This means that you won’t be able to select certain combinations of columns in Stitch. Refer to Microsoft’s documentation for more info, and the specific column combinations for this table.

Columns renamed from Bing Ads v1

In this version of Stitch’s Microsoft Advertising integration, some columns have been re-named. This was done to ensure consistency between the fields in our integration and the changes made by Microsoft to the Bing Ads API.

HistoricQualityScore is now HistoricalQualityScore
HistoricExpectedCtr is now HistoricalExpectedCtr
HistoricAdRelevance is now HistoricalAdRelevance
HistoricLandingPageExperience is now HistoricalLandingPageExperience
Status is now CampaignStatus

Replication Method	Key-based Incremental
Loading Behavior	Append-Only
Primary Keys	AccountId _sdc_report_datetime TimePeriod
Replication Key	TimePeriod
Useful links	Microsoft Advertising documentation

ad_group_performance_report table foreign keys

Join ad_group_performance_report with	on
accounts	ad_group_performance_report.AccountId = accounts.Id
ad_performance_report	ad_group_performance_report.AccountId = ad_performance_report.AccountId ad_group_performance_report.adGroupId = ad_performance_report.AdGroupId ad_group_performance_report.campaignId = ad_performance_report.CampaignId
age_gender_audience_report	ad_group_performance_report.AccountId = age_gender_audience_report.AccountId ad_group_performance_report.adGroupId = age_gender_audience_report.AdGroupId ad_group_performance_report.campaignId = age_gender_audience_report.CampaignId
audience_performance_report	ad_group_performance_report.AccountId = audience_performance_report.AccountId ad_group_performance_report.adGroupId = audience_performance_report.AdGroupId ad_group_performance_report.campaignId = audience_performance_report.CampaignId
campaign_performance_report	ad_group_performance_report.AccountId = campaign_performance_report.AccountId ad_group_performance_report.campaignId = campaign_performance_report.CampaignId
geographic_performance_report	ad_group_performance_report.AccountId = geographic_performance_report.AccountId ad_group_performance_report.adGroupId = geographic_performance_report.AdGroupId ad_group_performance_report.campaignId = geographic_performance_report.CampaignId
goals_and_funnels_report	ad_group_performance_report.AccountId = goals_and_funnels_report.AccountId ad_group_performance_report.adGroupId = goals_and_funnels_report.AdGroupId ad_group_performance_report.campaignId = goals_and_funnels_report.CampaignId
keyword_performance_report	ad_group_performance_report.AccountId = keyword_performance_report.AccountId ad_group_performance_report.adGroupId = keyword_performance_report.AdGroupId ad_group_performance_report.campaignId = keyword_performance_report.CampaignId
search_query_performance_report	ad_group_performance_report.AccountId = search_query_performance_report.AccountId ad_group_performance_report.adGroupId = search_query_performance_report.AdGroupId ad_group_performance_report.campaignId = search_query_performance_report.CampaignId
ad_groups	ad_group_performance_report.adGroupId = ad_groups.id
campaigns	ad_group_performance_report.campaignId = campaigns.Id

ad_group_performance_report table schema

AccountId

INTEGER

TimePeriod

DATE

adGroupId

INTEGER

campaignId

INTEGER

_sdc_report_datetime

DATE-TIME

ad_performance_report

The ad_performance_report table contains performance info about ads, including clicks and conversions. This data can be used to identify and improve under-performing ads.

This is a Report table. See the Replication section for information on how data is replicated and loaded for this table.

Columns renamed from Bing Ads v1

FinalAppURL is now FinalAppUrl
FinalURL is now FinalUrl
FinalMobileURL is now FinalMobileUrl

Replication Method	Key-based Incremental
Loading Behavior	Append-Only
Primary Keys	AccountId _sdc_report_datetime TimePeriod
Replication Key	TimePeriod
Useful links	Microsoft Advertising documentation

ad_performance_report table foreign keys

Join ad_performance_report with	on
accounts	ad_performance_report.AccountId = accounts.Id
ad_group_performance_report	ad_performance_report.AccountId = ad_group_performance_report.AccountId ad_performance_report.AdGroupId = ad_group_performance_report.adGroupId ad_performance_report.CampaignId = ad_group_performance_report.campaignId
age_gender_audience_report	ad_performance_report.AccountId = age_gender_audience_report.AccountId ad_performance_report.AdGroupId = age_gender_audience_report.AdGroupId ad_performance_report.CampaignId = age_gender_audience_report.CampaignId
audience_performance_report	ad_performance_report.AccountId = audience_performance_report.AccountId ad_performance_report.AdGroupId = audience_performance_report.AdGroupId ad_performance_report.CampaignId = audience_performance_report.CampaignId
campaign_performance_report	ad_performance_report.AccountId = campaign_performance_report.AccountId ad_performance_report.CampaignId = campaign_performance_report.CampaignId
geographic_performance_report	ad_performance_report.AccountId = geographic_performance_report.AccountId ad_performance_report.AdGroupId = geographic_performance_report.AdGroupId ad_performance_report.CampaignId = geographic_performance_report.CampaignId
goals_and_funnels_report	ad_performance_report.AccountId = goals_and_funnels_report.AccountId ad_performance_report.AdGroupId = goals_and_funnels_report.AdGroupId ad_performance_report.CampaignId = goals_and_funnels_report.CampaignId
keyword_performance_report	ad_performance_report.AccountId = keyword_performance_report.AccountId ad_performance_report.AdGroupId = keyword_performance_report.AdGroupId ad_performance_report.CampaignId = keyword_performance_report.CampaignId
search_query_performance_report	ad_performance_report.AccountId = search_query_performance_report.AccountId ad_performance_report.AdGroupId = search_query_performance_report.AdGroupId ad_performance_report.CampaignId = search_query_performance_report.CampaignId
ads	ad_performance_report.AdId = ads.Id
ad_groups	ad_performance_report.AdGroupId = ad_groups.id
campaigns	ad_performance_report.CampaignId = campaigns.Id

ad_performance_report table schema

AccountId

INTEGER

AdGroupId

INTEGER

AdId

INTEGER

CampaignId

INTEGER

TimePeriod

DATE

_sdc_report_datetime

DATE-TIME

age_gender_audience_report

The age_gender_audience_report table contains info about the age and gender demographics of people interacting with your campaigns and ad groups.

This is a Report table. See the Replication section for information on how data is replicated and loaded for this table.

Replication Method	Key-based Incremental
Loading Behavior	Append-Only
Primary Keys	AccountId _sdc_report_datetime TimePeriod
Replication Key	TimePeriod
Useful links	Microsoft Advertising documentation

age_gender_audience_report table foreign keys

Join age_gender_audience_report with	on
accounts	age_gender_audience_report.AccountId = accounts.Id
ad_performance_report	age_gender_audience_report.AccountId = ad_performance_report.AccountId age_gender_audience_report.AdGroupId = ad_performance_report.AdGroupId age_gender_audience_report.CampaignId = ad_performance_report.CampaignId
ad_group_performance_report	age_gender_audience_report.AccountId = ad_group_performance_report.AccountId age_gender_audience_report.AdGroupId = ad_group_performance_report.adGroupId age_gender_audience_report.CampaignId = ad_group_performance_report.campaignId
audience_performance_report	age_gender_audience_report.AccountId = audience_performance_report.AccountId age_gender_audience_report.AdGroupId = audience_performance_report.AdGroupId age_gender_audience_report.CampaignId = audience_performance_report.CampaignId
campaign_performance_report	age_gender_audience_report.AccountId = campaign_performance_report.AccountId age_gender_audience_report.CampaignId = campaign_performance_report.CampaignId
geographic_performance_report	age_gender_audience_report.AccountId = geographic_performance_report.AccountId age_gender_audience_report.AdGroupId = geographic_performance_report.AdGroupId age_gender_audience_report.CampaignId = geographic_performance_report.CampaignId
goals_and_funnels_report	age_gender_audience_report.AccountId = goals_and_funnels_report.AccountId age_gender_audience_report.AdGroupId = goals_and_funnels_report.AdGroupId age_gender_audience_report.CampaignId = goals_and_funnels_report.CampaignId
keyword_performance_report	age_gender_audience_report.AccountId = keyword_performance_report.AccountId age_gender_audience_report.AdGroupId = keyword_performance_report.AdGroupId age_gender_audience_report.CampaignId = keyword_performance_report.CampaignId
search_query_performance_report	age_gender_audience_report.AccountId = search_query_performance_report.AccountId age_gender_audience_report.AdGroupId = search_query_performance_report.AdGroupId age_gender_audience_report.CampaignId = search_query_performance_report.CampaignId
ad_groups	age_gender_audience_report.AdGroupId = ad_groups.id
campaigns	age_gender_audience_report.CampaignId = campaigns.Id

age_gender_audience_report table schema

AccountId

INTEGER

AccountName

STRING

AdGroupId

INTEGER

AdGroupName

STRING

AgeGroup

STRING

CampaignId

INTEGER

Gender

STRING

TimePeriod

DATE

_sdc_report_datetime

DATE-TIME

audience_performance_report

The audience_performance_report table contains info about performance statistics for various audiences.

This is a Report table. See the Replication section for information on how data is replicated and loaded for this table.

Replication Method	Key-based Incremental
Loading Behavior	Append-Only
Primary Keys	AccountId AudienceId _sdc_report_datetime TimePeriod
Replication Key	TimePeriod
Useful links	Microsoft Advertising documentation

audience_performance_report table foreign keys

Join audience_performance_report with	on
accounts	audience_performance_report.AccountId = accounts.Id
ad_performance_report	audience_performance_report.AccountId = ad_performance_report.AccountId audience_performance_report.AdGroupId = ad_performance_report.AdGroupId audience_performance_report.CampaignId = ad_performance_report.CampaignId
ad_group_performance_report	audience_performance_report.AccountId = ad_group_performance_report.AccountId audience_performance_report.AdGroupId = ad_group_performance_report.adGroupId audience_performance_report.CampaignId = ad_group_performance_report.campaignId
age_gender_audience_report	audience_performance_report.AccountId = age_gender_audience_report.AccountId audience_performance_report.AdGroupId = age_gender_audience_report.AdGroupId audience_performance_report.CampaignId = age_gender_audience_report.CampaignId
campaign_performance_report	audience_performance_report.AccountId = campaign_performance_report.AccountId audience_performance_report.CampaignId = campaign_performance_report.CampaignId
geographic_performance_report	audience_performance_report.AccountId = geographic_performance_report.AccountId audience_performance_report.AdGroupId = geographic_performance_report.AdGroupId audience_performance_report.CampaignId = geographic_performance_report.CampaignId
goals_and_funnels_report	audience_performance_report.AccountId = goals_and_funnels_report.AccountId audience_performance_report.AdGroupId = goals_and_funnels_report.AdGroupId audience_performance_report.CampaignId = goals_and_funnels_report.CampaignId
keyword_performance_report	audience_performance_report.AccountId = keyword_performance_report.AccountId audience_performance_report.AdGroupId = keyword_performance_report.AdGroupId audience_performance_report.CampaignId = keyword_performance_report.CampaignId
search_query_performance_report	audience_performance_report.AccountId = search_query_performance_report.AccountId audience_performance_report.AdGroupId = search_query_performance_report.AdGroupId audience_performance_report.CampaignId = search_query_performance_report.CampaignId
ad_groups	audience_performance_report.AdGroupId = ad_groups.id
campaigns	audience_performance_report.CampaignId = campaigns.Id

audience_performance_report table schema

AccountId

INTEGER

AdGroupId

INTEGER

AudienceId

INTEGER

CampaignId

INTEGER

TimePeriod

DATE

_sdc_report_datetime

DATE-TIME

campaigns

The campaigns table contains info about the campaigns in your Bing Ads account.

This is a Core Object table.

Schema changes from Bing Ads v1

NativeBidAdjustment has been removed
AudienceAdsBidAdjustment has been added
Additional fields have been added to BiddingScheme and Settings

Replication Method

Full Table

Primary Key

Useful links

Microsoft Advertising documentation

Microsoft Advertising API method

campaigns table foreign keys

Join campaigns with	on
ad_performance_report	campaigns.Id = ad_performance_report.CampaignId
ad_group_performance_report	campaigns.Id = ad_group_performance_report.campaignId
age_gender_audience_report	campaigns.Id = age_gender_audience_report.CampaignId
audience_performance_report	campaigns.Id = audience_performance_report.CampaignId
campaign_performance_report	campaigns.Id = campaign_performance_report.CampaignId
geographic_performance_report	campaigns.Id = geographic_performance_report.CampaignId
goals_and_funnels_report	campaigns.Id = goals_and_funnels_report.CampaignId
keyword_performance_report	campaigns.Id = keyword_performance_report.CampaignId
search_query_performance_report	campaigns.Id = search_query_performance_report.CampaignId

campaigns table schema

AudienceAdsBidAdjustment

INTEGER

BiddingScheme

OBJECT

Click to expand BiddingScheme

InheritedBidStrategyType

STRING

Type

STRING

BudgetId

INTEGER

BudgetType

STRING

CampaignType

STRING

DailyBudget

NUMBER

Description

STRING

ForwardCompatibilityMap

ARRAY

Click to expand ForwardCompatibilityMap

keyValuePairOfStringString

ARRAY

Click to expand keyValuePairOfStringString

key

STRING

value

STRING

INTEGER

Languages

STRING

Name

STRING

Settings

ARRAY

Click to expand Settings

BidBoostValue

NUMBER

BidMaxValue

NUMBER

BidOption

STRING

DomainName

STRING

Language

STRING

LocalInventoryAdsEnabled

BOOLEAN

PageFeedIds

ARRAY

Click to expand PageFeedIds

value

INTEGER

Priority

INTEGER

SalesCountryCode

STRING

Source

STRING

StoreId

INTEGER

Type

STRING

Status

STRING

TimeZone

STRING

TrackingUrlTemplate

STRING

UrlCustomParameters

STRING

campaign_performance_report

The campaign_performance_report table contains performance data for campaigns, aggregated by day..

This is a Report table. See the Replication section for information on how data is replicated and loaded for this table.

Column compatibility

Columns renamed from Bing Ads v1

HistoricQualityScore is now HistoricalQualityScore
HistoricExpectedCtr is now HistoricalExpectedCtr
HistoricAdRelevance is now HistoricalAdRelevance
HistoricLandingPageExperience is now HistoricalLandingPageExperience
Status is now CampaignStatus

Replication Method	Key-based Incremental
Loading Behavior	Append-Only
Primary Keys	AccountId _sdc_report_datetime TimePeriod
Replication Key	TimePeriod
Useful links	Microsoft Advertising documentation

campaign_performance_report table foreign keys

Join campaign_performance_report with	on
accounts	campaign_performance_report.AccountId = accounts.Id
ad_performance_report	campaign_performance_report.AccountId = ad_performance_report.AccountId campaign_performance_report.CampaignId = ad_performance_report.CampaignId
ad_group_performance_report	campaign_performance_report.AccountId = ad_group_performance_report.AccountId campaign_performance_report.CampaignId = ad_group_performance_report.campaignId
age_gender_audience_report	campaign_performance_report.AccountId = age_gender_audience_report.AccountId campaign_performance_report.CampaignId = age_gender_audience_report.CampaignId
audience_performance_report	campaign_performance_report.AccountId = audience_performance_report.AccountId campaign_performance_report.CampaignId = audience_performance_report.CampaignId
geographic_performance_report	campaign_performance_report.AccountId = geographic_performance_report.AccountId campaign_performance_report.CampaignId = geographic_performance_report.CampaignId
goals_and_funnels_report	campaign_performance_report.AccountId = goals_and_funnels_report.AccountId campaign_performance_report.CampaignId = goals_and_funnels_report.CampaignId
keyword_performance_report	campaign_performance_report.AccountId = keyword_performance_report.AccountId campaign_performance_report.CampaignId = keyword_performance_report.CampaignId
search_query_performance_report	campaign_performance_report.AccountId = search_query_performance_report.AccountId campaign_performance_report.CampaignId = search_query_performance_report.CampaignId
campaigns	campaign_performance_report.CampaignId = campaigns.Id

campaign_performance_report table schema

AccountId

INTEGER

CampaignId

INTEGER

TimePeriod

DATE

_sdc_report_datetime

DATE-TIME

geographic_performance_report

The geographic_performance_report table contains info about the physical locations of people searching for an ad or the locations people are searching for. This data can be used to validate or improve location targeting strategies.

This is a Report table. See the Replication section for information on how data is replicated and loaded for this table.

Replication Method	Key-based Incremental
Loading Behavior	Append-Only
Primary Keys	AccountId _sdc_report_datetime TimePeriod
Replication Key	TimePeriod
Useful links	Microsoft Advertising documentation

geographic_performance_report table foreign keys

Join geographic_performance_report with	on
accounts	geographic_performance_report.AccountId = accounts.Id
ad_performance_report	geographic_performance_report.AccountId = ad_performance_report.AccountId geographic_performance_report.AdGroupId = ad_performance_report.AdGroupId geographic_performance_report.CampaignId = ad_performance_report.CampaignId
ad_group_performance_report	geographic_performance_report.AccountId = ad_group_performance_report.AccountId geographic_performance_report.AdGroupId = ad_group_performance_report.adGroupId geographic_performance_report.CampaignId = ad_group_performance_report.campaignId
age_gender_audience_report	geographic_performance_report.AccountId = age_gender_audience_report.AccountId geographic_performance_report.AdGroupId = age_gender_audience_report.AdGroupId geographic_performance_report.CampaignId = age_gender_audience_report.CampaignId
audience_performance_report	geographic_performance_report.AccountId = audience_performance_report.AccountId geographic_performance_report.AdGroupId = audience_performance_report.AdGroupId geographic_performance_report.CampaignId = audience_performance_report.CampaignId
campaign_performance_report	geographic_performance_report.AccountId = campaign_performance_report.AccountId geographic_performance_report.CampaignId = campaign_performance_report.CampaignId
goals_and_funnels_report	geographic_performance_report.AccountId = goals_and_funnels_report.AccountId geographic_performance_report.AdGroupId = goals_and_funnels_report.AdGroupId geographic_performance_report.CampaignId = goals_and_funnels_report.CampaignId
keyword_performance_report	geographic_performance_report.AccountId = keyword_performance_report.AccountId geographic_performance_report.AdGroupId = keyword_performance_report.AdGroupId geographic_performance_report.CampaignId = keyword_performance_report.CampaignId
search_query_performance_report	geographic_performance_report.AccountId = search_query_performance_report.AccountId geographic_performance_report.AdGroupId = search_query_performance_report.AdGroupId geographic_performance_report.CampaignId = search_query_performance_report.CampaignId
ad_groups	geographic_performance_report.AdGroupId = ad_groups.id
campaigns	geographic_performance_report.CampaignId = campaigns.Id

geographic_performance_report table schema

AccountId

INTEGER

AdGroupId

INTEGER

CampaignId

INTEGER

TimePeriod

DATE

_sdc_report_datetime

DATE-TIME

goals_and_funnels_report

The goals_and_funnels_report table contains information about your audience’s progression through your conversion funnel. Use this report to determine the point at which users leave the funnel, thereby allowing you to improve and increase conversion.

This is a Report table. See the Replication section for information on how data is replicated and loaded for this table.

Replication Method	Key-based Incremental
Loading Behavior	Append-Only
Primary Keys	AccountId _sdc_report_datetime TimePeriod
Replication Key	TimePeriod
Useful links	Microsoft Advertising documentation

goals_and_funnels_report table foreign keys

Join goals_and_funnels_report with	on
accounts	goals_and_funnels_report.AccountId = accounts.Id
ad_performance_report	goals_and_funnels_report.AccountId = ad_performance_report.AccountId goals_and_funnels_report.AdGroupId = ad_performance_report.AdGroupId goals_and_funnels_report.CampaignId = ad_performance_report.CampaignId
ad_group_performance_report	goals_and_funnels_report.AccountId = ad_group_performance_report.AccountId goals_and_funnels_report.AdGroupId = ad_group_performance_report.adGroupId goals_and_funnels_report.CampaignId = ad_group_performance_report.campaignId
age_gender_audience_report	goals_and_funnels_report.AccountId = age_gender_audience_report.AccountId goals_and_funnels_report.AdGroupId = age_gender_audience_report.AdGroupId goals_and_funnels_report.CampaignId = age_gender_audience_report.CampaignId
audience_performance_report	goals_and_funnels_report.AccountId = audience_performance_report.AccountId goals_and_funnels_report.AdGroupId = audience_performance_report.AdGroupId goals_and_funnels_report.CampaignId = audience_performance_report.CampaignId
campaign_performance_report	goals_and_funnels_report.AccountId = campaign_performance_report.AccountId goals_and_funnels_report.CampaignId = campaign_performance_report.CampaignId
geographic_performance_report	goals_and_funnels_report.AccountId = geographic_performance_report.AccountId goals_and_funnels_report.AdGroupId = geographic_performance_report.AdGroupId goals_and_funnels_report.CampaignId = geographic_performance_report.CampaignId
keyword_performance_report	goals_and_funnels_report.AccountId = keyword_performance_report.AccountId goals_and_funnels_report.AdGroupId = keyword_performance_report.AdGroupId goals_and_funnels_report.CampaignId = keyword_performance_report.CampaignId
search_query_performance_report	goals_and_funnels_report.AccountId = search_query_performance_report.AccountId goals_and_funnels_report.AdGroupId = search_query_performance_report.AdGroupId goals_and_funnels_report.CampaignId = search_query_performance_report.CampaignId
ad_groups	goals_and_funnels_report.AdGroupId = ad_groups.id
campaigns	goals_and_funnels_report.CampaignId = campaigns.Id

goals_and_funnels_report table schema

AccountId

INTEGER

AdGroupId

INTEGER

CampaignId

INTEGER

Goal

STRING

TimePeriod

DATE

_sdc_report_datetime

DATE-TIME

keyword_performance_report

The keyword_performance_report table contains performance data about keywords.

This is a Report table. See the Replication section for information on how data is replicated and loaded for this table.

Columns renamed from Bing Ads v1

FinalAppURL is now FinalAppUrl
FinalURL is now FinalUrl
FinalMobileURL is now FinalMobileUrl
HistoricQualityScore is now HistoricalQualityScore
HistoricExpectedCtr is now HistoricalExpectedCtr
HistoricAdRelevance is now HistoricalAdRelevance
HistoricLandingPageExperience is now HistoricalLandingPageExperience
Status is now CampaignStatus
SidebarBid is now FirstPageBid

Replication Method	Key-based Incremental
Loading Behavior	Append-Only
Primary Keys	AccountId _sdc_report_datetime TimePeriod
Replication Key	TimePeriod
Useful links	Microsoft Advertising documentation

keyword_performance_report table foreign keys

Join keyword_performance_report with	on
accounts	keyword_performance_report.AccountId = accounts.Id
ad_performance_report	keyword_performance_report.AccountId = ad_performance_report.AccountId keyword_performance_report.AdGroupId = ad_performance_report.AdGroupId keyword_performance_report.CampaignId = ad_performance_report.CampaignId
ad_group_performance_report	keyword_performance_report.AccountId = ad_group_performance_report.AccountId keyword_performance_report.AdGroupId = ad_group_performance_report.adGroupId keyword_performance_report.CampaignId = ad_group_performance_report.campaignId
age_gender_audience_report	keyword_performance_report.AccountId = age_gender_audience_report.AccountId keyword_performance_report.AdGroupId = age_gender_audience_report.AdGroupId keyword_performance_report.CampaignId = age_gender_audience_report.CampaignId
audience_performance_report	keyword_performance_report.AccountId = audience_performance_report.AccountId keyword_performance_report.AdGroupId = audience_performance_report.AdGroupId keyword_performance_report.CampaignId = audience_performance_report.CampaignId
campaign_performance_report	keyword_performance_report.AccountId = campaign_performance_report.AccountId keyword_performance_report.CampaignId = campaign_performance_report.CampaignId
geographic_performance_report	keyword_performance_report.AccountId = geographic_performance_report.AccountId keyword_performance_report.AdGroupId = geographic_performance_report.AdGroupId keyword_performance_report.CampaignId = geographic_performance_report.CampaignId
goals_and_funnels_report	keyword_performance_report.AccountId = goals_and_funnels_report.AccountId keyword_performance_report.AdGroupId = goals_and_funnels_report.AdGroupId keyword_performance_report.CampaignId = goals_and_funnels_report.CampaignId
search_query_performance_report	keyword_performance_report.AccountId = search_query_performance_report.AccountId keyword_performance_report.AdGroupId = search_query_performance_report.AdGroupId keyword_performance_report.CampaignId = search_query_performance_report.CampaignId
ad_groups	keyword_performance_report.AdGroupId = ad_groups.id
campaigns	keyword_performance_report.CampaignId = campaigns.Id

keyword_performance_report table schema

AccountId

INTEGER

AdGroupId

INTEGER

CampaignId

INTEGER

TimePeriod

DATE

_sdc_report_datetime

DATE-TIME

search_query_performance_report

The search_query_performance_report table contains performance data for search terms that resulted in a significant number of clicks in the last 30 days. As this data may change over time, use the keyword_performance_report table to analyze the overall performance of keywords.

Note: This data in this table is not applicable to Bing Shopping campaigns.

This is a Report table. See the Replication section for information on how data is replicated and loaded for this table.

Replication Method	Key-based Incremental
Loading Behavior	Append-Only
Primary Keys	AccountId _sdc_report_datetime TimePeriod
Replication Key	TimePeriod
Useful links	Microsoft Advertising documentation

search_query_performance_report table foreign keys

Join search_query_performance_report with	on
accounts	search_query_performance_report.AccountId = accounts.Id
ad_performance_report	search_query_performance_report.AccountId = ad_performance_report.AccountId search_query_performance_report.AdGroupId = ad_performance_report.AdGroupId search_query_performance_report.CampaignId = ad_performance_report.CampaignId
ad_group_performance_report	search_query_performance_report.AccountId = ad_group_performance_report.AccountId search_query_performance_report.AdGroupId = ad_group_performance_report.adGroupId search_query_performance_report.CampaignId = ad_group_performance_report.campaignId
age_gender_audience_report	search_query_performance_report.AccountId = age_gender_audience_report.AccountId search_query_performance_report.AdGroupId = age_gender_audience_report.AdGroupId search_query_performance_report.CampaignId = age_gender_audience_report.CampaignId
audience_performance_report	search_query_performance_report.AccountId = audience_performance_report.AccountId search_query_performance_report.AdGroupId = audience_performance_report.AdGroupId search_query_performance_report.CampaignId = audience_performance_report.CampaignId
campaign_performance_report	search_query_performance_report.AccountId = campaign_performance_report.AccountId search_query_performance_report.CampaignId = campaign_performance_report.CampaignId
geographic_performance_report	search_query_performance_report.AccountId = geographic_performance_report.AccountId search_query_performance_report.AdGroupId = geographic_performance_report.AdGroupId search_query_performance_report.CampaignId = geographic_performance_report.CampaignId
goals_and_funnels_report	search_query_performance_report.AccountId = goals_and_funnels_report.AccountId search_query_performance_report.AdGroupId = goals_and_funnels_report.AdGroupId search_query_performance_report.CampaignId = goals_and_funnels_report.CampaignId
keyword_performance_report	search_query_performance_report.AccountId = keyword_performance_report.AccountId search_query_performance_report.AdGroupId = keyword_performance_report.AdGroupId search_query_performance_report.CampaignId = keyword_performance_report.CampaignId
ad_groups	search_query_performance_report.AdGroupId = ad_groups.id
campaigns	search_query_performance_report.CampaignId = campaigns.Id

search_query_performance_report table schema

AccountId

INTEGER

AdGroupId

INTEGER

CampaignId

INTEGER

TimePeriod

DATE

_sdc_report_datetime

DATE-TIME

Questions? Feedback?

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.

Related	Troubleshooting
Destination & Integration Compatibility Replication Scheduling Syncing Historical SaaS Data Resetting Replication Keys Nested Data Structures & Row Count Impact	Third-Party Downtime Understanding & Reducing Your Usage Re-Authorizing Integrations Replication Issues