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

  1. Sign into your Stitch account.

  2. On the Stitch Dashboard page, click the Add Integration button.

  3. Click the Microsoft Advertising icon.

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

The Sync Historical Data setting defines the starting date for your Microsoft Advertising integration. This means that:

  • For tables using Key-based Incremental Replication, data equal to or newer than this date will be replicated to your destination.
  • For tables using Full Table Replication, all data - including records that are older, equal to, or newer than this date - will be replicated to your destination.

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

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.

Step 4: Authorize Stitch and select Microsoft Advertising accounts

Selecting Bing Ads accounts.

  1. Next, you’ll be prompted to log into your Microsoft account and to approve Stitch’s access to your Microsoft Advertising/Bing Ads data.
  2. Click Authorize to continue.

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

  4. 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.
  5. When finished selecting accounts, click Check and Save.

Step 5: Set objects to replicate

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.

  1. In the integration’s Tables to Replicate tab, locate a table you want to replicate.

  2. To track a table, click the checkbox next to the table’s name. A blue checkmark means the table is set to replicate.

  3. To track a column, click the checkbox next to the column’s name. A blue checkmark means the column is set to replicate.

  4. Repeat this process for all the tables and columns you want to replicate.
  5. 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.

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.

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:

    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:

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

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

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

}

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:

  1. A subquery first uses a window function to create a ‘window’ of data for each TimePeriod,
  2. The values of the _sdc_report_datetime column are ranked within each window partition, and
  3. 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

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

accounts

Replication Method :

Key-based Incremental

 Replication Key :

LastModifiedTime
Primary Key :

Id : LastModifiedTime

 Official docs :

Official Docs

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

accounts table schema

Id
INTEGER

The account ID.

Reference:

LastModifiedTime
DATE-TIME

The time (in UTC) the account was last updated.

Note: The date and time value reflects the date and time at the Bing server, not the client.

AccountFinancialStatus
STRING

The financial status of the account.

AccountLifeCycleStatus
STRING

The status of the account.

AutoTagType
STRING

Indicates whether supported UTM tracking codes are appended or replaced.

BillToCustomerId
INTEGER

The ID of the customer that is billed for the charges that the account generates. This is either the reseller that manages the account on behalf of the owner or the ID of the customer that owns the account.

BusinessAddress
ARRAY

The location where your business is legally registered. If you’re an agency working as an agent for your customer, this is the location where your client is legally registered.

Click to expand BusinessAddress

Id
STRING

The ID of the address object.

BusinessName
STRING

The legal business name.

City
STRING

The city.

CountryCode
STRING

The country/region code. For a list of possible values, refer to Microsoft’s documentation.

Line1
STRING

The first line of the address.

Line2
STRING

The second line of the address.

Line3
STRING

The third line of the address.

Line4
STRING

The fourth line of the address.

PostalCode
STRING

The postal or zip code.

StateOrProvince
STRING

The state or province. For a list of possible values, refer to Microsoft’s documentation.

TimeStamp
DATE-TIME

The date and time that the address was last updated.
accounts (table), BusinessAddress (attribute)

CurrencyCode
STRING

The type of currency used to settle the account. Refer to Microsoft’s documentation for a list of possible values.

ForwardCompatibilityMap
ARRAY

Details about the forward compatibility settings for the account.

Click to expand ForwardCompatibilityMap

KeyValuePairOfStringString
ARRAY

Key and value pairs for the account’s forward compatibility settings.

Click to expand KeyValuePairOfStringString

key
STRING

The name of the setting.

value
STRING

The value of the setting.
accounts (table), KeyValuePairOfStringString (attribute)
accounts (table), ForwardCompatibilityMap (attribute)

Language
STRING

The language used to render the invoice for the account, if invoice is used as the payment method.

LastModifiedByUserId
INTEGER

The ID of the user who last updated the account’s information.

Name
STRING

The name of the account.

Number
STRING

The account number generated by Bing Ads used to identify the account in the Bing Ads web app. This value is alphanumeric.

ParentCustomerId
INTEGER

The ID of the customer that owns the account.

PauseReason
INTEGER

Indicates the reason the account is paused. Possible values include:

  • 1 - A user paused the account
  • 2 - The billing service paused the account
  • 4 - The user and billing service paused the account

PaymentMethodId
INTEGER

The ID of the payment instrument used to settle the account.

PaymentMethodType
STRING

The type of payment instrument used to settle the account.

PrimaryUserId
INTEGER

The ID of the account manager who is primarily responsible for managing the account. By default, this value is set to the reseller’s user ID.

SoldToPaymentInstrumentId
STRING

The ID of the payment instrument of your client (the sold-to customer) used to settle the account.

Timestamp
TIMESTAMP

The timestamp value used internally by Bing Ads to reconcile updates between account update and delete operations.

Timezone
STRING

The default timezone value to use for campaigns in this account.

If left unspecified when the account is created, this value will default to PacificTimeUSCanadaTijuana.

ads

Replication Method :

Full Table

 Primary Key :

Id
API endpoint :

getAdsByAdGroupId

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

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.

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

ads table schema

Id
INTEGER

The ad ID.

Reference:

AdFormatPreference
STRING

Indicates whether or not the ad copy is shown to users as a search or native ad. Search ads tend to be written as a call to action, whereas intent ads are written in a more informational style.

Possible values are:

  • Native - Ad will be eligible only for the native ad format
  • All - Ad will be eligible for both search and native ad formats

DevicePreference
INTEGER

Determines the device preference for showing the ad.

Domain
STRING

Applicable to Type: ExpandedText ads. The URL that will be displayed instead of the final URL. The final URL will still be used for the landing page URL.

EditorialStatus
STRING

The editorial review status of the ad, which indicates whether the ad is pending review, approved, or disapproved.

FinalAppUrls
STRING

The last or final URL where a user who clicks on an in-app ad is taken.

Click to expand FinalAppUrls

string
ARRAY

The app landing page URL.

Click to expand string

value
STRING

The app landing page URL.
ads (table), string (attribute)
ads (table), FinalAppUrls (attribute)

FinalMobileUrls
ARRAY

The last or final URL where a user who clicks on a mobile ad is taken.

Click to expand FinalMobileUrls

string
ARRAY

The mobile landing page URL. This is only supported for text ads.

Click to expand string

value
STRING

The mobile landing page URL. This is only supported for text ads.
ads (table), string (attribute)
ads (table), FinalMobileUrls (attribute)

FinalUrls
STRING

The last or final URL where a user is taken, whether or not the click to final URL path included any redirects.

Click to expand FinalUrls

string
ARRAY

The last or final URL where a user is taken, whether or not the click to final URL path included any redirects.

Click to expand string

value
STRING

The last or final URL where a user is taken, whether or not the click to final URL path included any redirects.
ads (table), string (attribute)
ads (table), FinalUrls (attribute)

ForwardCompatibilityMap
ARRAY

Details about the forward compatibility settings for the ad.

Click to expand ForwardCompatibilityMap

KeyValuePairOfStringString
ARRAY

Key and value pairs for the ad’s forward compatibility settings.

Click to expand KeyValuePairOfStringString

key
STRING

The name of the setting.

value
STRING

The value of the setting.
ads (table), KeyValuePairOfStringString (attribute)
ads (table), ForwardCompatibilityMap (attribute)

Status
STRING

The status of the ad. Possible values are Active and Paused.

TextPart2
STRING

Applicable to Type: ExpandedText ads. As per Microsoft, this field is reserved for future use.

TitlePart1
STRING

Applicable to Type: ExpandedText ads. The first part of the ad title.

TitlePart2
STRING

Applicable to Type: ExpandedText ads. The second part of the ad title.

TitlePart3
STRING

Applicable to Type: ExpandedText ads. As per Microsoft, this field is reserved for future use.

TrackingUrlTemplate
STRING

The tracking template to use as a default for all landing page URLs.

Type
STRING

The type of the ad.

UrlCustomParameters
STRING

The custom collection of key and value parameters for URL tracking.

ad_groups

Replication Method :

Full Table

 Primary Key :

id
API endpoint :

getAdGroupsbyCampaignId

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

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.

  • 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

ad_groups table schema

id
INTEGER

The ad group ID.

Reference:

AdRotation
STRING

Determines how often ads in the ad group show in relation to each other. If there are multiple ads within an ad group, the ads will rotate because only one ad from your account can show at a time.

Possible values are:

  • OptimizeForClicks - In this rotation, Bing Ads will predominantly show ads that have the highest click-through rate, or CTR.
  • RotateAdsEvenly - In this rotation, Bing ads will rotate between ads on an equal basis.

AudienceAdsBidAdjustment
INTEGER

The percent amount by which to adjust your bid for intent ads above or below the base ad group or keyword bid.

Possible values are negative one hundred (-100) through positive nine hundred (900).

BiddingScheme
OBJECT

The bid strategy type for how bids are managed.

Click to expand BiddingScheme

Type
STRING

The type of bidding scheme set for the campaign, ad group, or keyword. Refer to Microsoft’s documentation for info about bidding scheme types.

InheritedBidStrategyType
STRING

The type of bidding scheme that is inherited from the parent campaign or ad group. Possible values are:

  • EnhancedCpc
  • ManualCpc
  • MaxClicks
  • MaxConversions
  • TargetCpa
ad_groups (table), BiddingScheme (attribute)

CpcBid
STRING

The default bid to use when the user’s query and the ad group’s keywords match by either using a broad, exact, or phrase match comparison.

EndDate
DATE-TIME

The date that the ads in the ad group will expire.

ForwardCompatibilityMap
ARRAY

Details about the forward compatibility settings for the ad group.

Click to expand ForwardCompatibilityMap

KeyValuePairOfStringString
ARRAY

Key and value pairs for the ad group’s forward compatibility settings.

Click to expand KeyValuePairOfStringString

key
STRING

The name of the setting.

value
STRING

The value of the setting.
ad_groups (table), KeyValuePairOfStringString (attribute)
ad_groups (table), ForwardCompatibilityMap (attribute)

Language
STRING

The language of the ads and keywords in the ad group.

Name
STRING

The name of the ad group.

Network
STRING

The search networks where the ads will display. Possible values are:

  • OwnedAndOperatedAndSyndicatedSearch
  • OwnedAndOperatedOnly
  • SyndicatedSearchOnly

Settings
ARRAY

Details about the settings applied to the ad group.

Only certain attributes are applicable to different ad group settings. This means that only certain columns will contain values based on the Type field.

Click to expand Settings

Type
STRING

The type of setting. Possible values are:

  • CoOpSetting
  • DynamicSearchAdsSetting
  • ShoppingSetting

BidBoostValue
NUMBER

Applicable to Cooperative Bidding campaigns, or Type: CoOpSetting. The percentage that allows your cooperative bid to flex.

BidMaxValue
NUMBER

Applicable to Cooperative Bidding campaigns, or Type: CoOpSetting. The flat amount of your cooperative bid.

BidOption
STRING

Applicable to Cooperative Bidding campaigns, or Type: CoOpSetting. Determines whether or not to amplify your partner’s bid.

DomainName
STRING

Applicable to Dynamic Search Ads campaigns, or Type: DynamicSearchAdsSetting. The domain name of the website that you want to target for dynamic search ads.

Language
STRING

Applicable to Dynamic Search Ads campaigns, or Type: DynamicSearchAdsSetting. The language of the website pages that you want to target for dynamic search ads.

PageFeedIds
ARRAY

Applicable to Dynamic Search Ads campaigns, or Type: DynamicSearchAdsSetting. Reserved by Microsoft.

Click to expand PageFeedIds

value
INTEGER

Applicable to Dynamic Search Ads campaigns, or Type: DynamicSearchAdsSetting. Reserved by Microsoft.
ad_groups (table), PageFeedIds (attribute)

Source
STRING

Applicable to Dynamic Search Ads campaigns, or Type: DynamicSearchAdsSetting. Reserved by Microsoft.

LocalInventoryAdsEnabled
BOOLEAN

Applicable to feed-based audience or shopping campaigns, or Type: ShoppingSetting. Determines whether local inventory ads are enabled for the Bing Merchant Center store.

Priority
INTEGER

Applicable to feed-based audience or shopping campaigns, or Type: ShoppingSetting. Determines which Bing Shopping campaign serves ads, in the event that two or more campaigns use the product catalog feed from the same Bing Merchant Center store.

Possible value are 0, 1, or 2. The higher numbers are given priority.

SalesCountryCode
STRING

Applicable to feed-based audience or shopping campaigns, or Type: ShoppingSetting. The country code for the Bing Merchant Center store. Refer to Microsoft’s documentation for a list of possible values.

StoreId
INTEGER

Applicable to feed-based audience or shopping campaigns, or Type: ShoppingSetting. The ID for the Bing Merchant Center store that contains a product catalog feed that you want to use for the campaign.
ad_groups (table), Settings (attribute)

StartDate
DATE-TIME

The date that the ads in the ad group can begin serving.

Status
STRING

The status of the ad group. Possible values are:

  • Active
  • Expired
  • Paused

TrackingUrlTemplate
STRING

The tracking template to use as a default for all URLs in your ad group.

UrlCustomParameters
ARRAY

The custom collection of key and value parameters for URL tracking.

ad_group_performance_report

Replication Method :

Key-based Incremental

 Replication Key :

TimePeriod
Primary Key :

AccountId : _sdc_report_datetime : TimePeriod

 Official docs :

Official Docs
Loading Behavior:

Append-Only

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

ad_group_performance_report table schema

AccountId
INTEGER

The Bing Ads-assigned ID of the account.

Reference:

_sdc_report_datetime
DATE-TIME

The start time of the Stitch replication job that replicated this record.

TimePeriod
DATE

The day the record pertains to.

adGroupId
INTEGER

The ID of the ad group..

Reference:

campaignId
INTEGER

The ID of the campaign the ad group is a part of.

Reference:

Custom Fields

Columns selected by you. For descriptions of available columns, refer to Microsoft’s documentation.

ad_performance_report

Replication Method :

Key-based Incremental

 Replication Key :

TimePeriod
Primary Key :

AccountId : _sdc_report_datetime : TimePeriod

 Official docs :

Official Docs
Loading Behavior:

Append-Only

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

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.

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

age_gender_audience_report

Replication Method :

Key-based Incremental

 Replication Key :

TimePeriod
Primary Key :

AccountId : _sdc_report_datetime : TimePeriod

 Official docs :

Official Docs
Loading Behavior:

Append-Only

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.

age_gender_audience_report table schema

AccountId
INTEGER

The Bing Ads-assigned ID of the account.

Reference:

_sdc_report_datetime
DATE-TIME

The start time of the Stitch replication job that replicated this record.

TimePeriod
DATE

The day the record pertains to.

AdGroupId
INTEGER

The ID of the ad group.

Reference:

CampaignId
INTEGER

The ID of the campaign.

Reference:

AccountName
STRING

The account name.

AdGroupName
STRING

The ad group name.

AgeGroup
STRING

The age group of the audience who might have viewed the ad.

Gender
STRING

The gender of the audience who might have viewed the ad.

Custom Fields

Columns selected by you. For descriptions of available columns, refer to Microsoft’s documentation.

audience_performance_report

Replication Method :

Key-based Incremental

 Replication Key :

TimePeriod
Primary Key :

AccountId : AudienceId : _sdc_report_datetime : TimePeriod

 Official docs :

Official Docs
Loading Behavior:

Append-Only

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.

audience_performance_report table schema

AccountId
INTEGER

The Bing Ads-assigned ID of the account.

Reference:

AudienceId
INTEGER

The ID of the audience.

_sdc_report_datetime
DATE-TIME

The start time of the Stitch replication job that replicated this record.

TimePeriod
DATE

The day the record pertains to.

AdGroupId
INTEGER

The ID of the ad group.

Reference:

CampaignId
INTEGER

The ID of the campaign.

Reference:

Custom Fields

Columns selected by you. For descriptions of available columns, refer to Microsoft’s documentation.

campaigns

Replication Method :

Full Table

 Primary Key :

Id
API endpoint :

getCampaignsByAccountId

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

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.

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

campaigns table schema

Id
INTEGER

The campaign ID.

Reference:

AudienceAdsBidAdjustment
INTEGER

The percent amount by which to adjust your bid for intent ads above or below the base ad group or keyword bid.

Possible values are negative one hundred (-100) through positive nine hundred (900).

BiddingScheme
OBJECT

The bid strategy type for how bids are managed.

Click to expand BiddingScheme

Type
STRING

The type of bidding scheme set for the campaign, ad group, or keyword. Refer to Microsoft’s documentation for info about bidding scheme types.

InheritedBidStrategyType
STRING

The type of bidding scheme that is inherited from the parent campaign or ad group. Possible values are:

  • EnhancedCpc
  • ManualCpc
  • MaxClicks
  • MaxConversions
  • TargetCpa
campaigns (table), BiddingScheme (attribute)

BudgetId
INTEGER

The ID of the budget that the campaign shares with other campaigns in the account. Refer to Microsoft’s documentation for more info on budgets.

BudgetType
STRING

The budget type that determines how the budget is spent.

CampaignType
STRING

The type of campaign.

DailyBudget
NUMBER

The amount to spend daily on the campaign.

Description
STRING

The description of the campaign.

ForwardCompatibilityMap
ARRAY

Details about the forward compatibility settings for the campaign.

Click to expand ForwardCompatibilityMap

keyValuePairOfStringString
ARRAY

Key and value pairs for the campaign’s forward compatibility settings.

Click to expand keyValuePairOfStringString

key
STRING

The name of the setting.

value
STRING

The value of the setting.
campaigns (table), keyValuePairOfStringString (attribute)
campaigns (table), ForwardCompatibilityMap (attribute)

Languages
STRING

The languages of the ads and keywords in the campaign.

Name
STRING

The name of the campaign.

Settings
ARRAY

Details about the settings applied to the campaign.

Only certain attributes are applicable to different campaign settings. This means that only certain columns will contain values based on the Type field.

Click to expand Settings

Type
STRING

The type of setting. Possible values are:

  • CoOpSetting
  • DynamicSearchAdsSetting
  • ShoppingSetting

BidBoostValue
NUMBER

Applicable to Cooperative Bidding campaigns, or Type: CoOpSetting. The percentage that allows your cooperative bid to flex.

BidMaxValue
NUMBER

Applicable to Cooperative Bidding campaigns, or Type: CoOpSetting. The flat amount of your cooperative bid.

BidOption
STRING

Applicable to Cooperative Bidding campaigns, or Type: CoOpSetting. Determines whether or not to amplify your partner’s bid.

DomainName
STRING

Applicable to Dynamic Search Ads campaigns, or Type: DynamicSearchAdsSetting. The domain name of the website that you want to target for dynamic search ads.

Language
STRING

Applicable to Dynamic Search Ads campaigns, or Type: DynamicSearchAdsSetting. The language of the website pages that you want to target for dynamic search ads.

PageFeedIds
ARRAY

Applicable to Dynamic Search Ads campaigns, or Type: DynamicSearchAdsSetting. Reserved by Microsoft.

Click to expand PageFeedIds

value
INTEGER

Applicable to Dynamic Search Ads campaigns, or Type: DynamicSearchAdsSetting. Reserved by Microsoft.
campaigns (table), PageFeedIds (attribute)

Source
STRING

Applicable to Dynamic Search Ads campaigns, or Type: DynamicSearchAdsSetting. Reserved by Microsoft.

LocalInventoryAdsEnabled
BOOLEAN

Applicable to feed-based audience or shopping campaigns, or Type: ShoppingSetting. Determines whether local inventory ads are enabled for the Bing Merchant Center store.

Priority
INTEGER

Applicable to feed-based audience or shopping campaigns, or Type: ShoppingSetting. Determines which Bing Shopping campaign serves ads, in the event that two or more campaigns use the product catalog feed from the same Bing Merchant Center store.

Possible value are 0, 1, or 2. The higher numbers are given priority.

SalesCountryCode
STRING

Applicable to feed-based audience or shopping campaigns, or Type: ShoppingSetting. The country code for the Bing Merchant Center store. Refer to Microsoft’s documentation for a list of possible values.

StoreId
INTEGER

Applicable to feed-based audience or shopping campaigns, or Type: ShoppingSetting. The ID for the Bing Merchant Center store that contains a product catalog feed that you want to use for the campaign.
campaigns (table), Settings (attribute)

Status
STRING

The status of the campaign. Possible values are Active and Paused.

TimeZone
STRING

The timezone where the campaign operates.

TrackingUrlTemplate
STRING

The tracking template to use as a default for all URLs in the campaign.

UrlCustomParameters
STRING

The custom collection of key and value parameters for URL tracking.

campaign_performance_report

Replication Method :

Key-based Incremental

 Replication Key :

TimePeriod
Primary Key :

AccountId : _sdc_report_datetime : TimePeriod

 Official docs :

Official Docs
Loading Behavior:

Append-Only

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

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

campaign_performance_report table schema

AccountId
INTEGER

The Bing Ads-assigned ID of the account.

_sdc_report_datetime
DATE-TIME

The start time of the Stitch replication job that replicated this record.

TimePeriod
DATE

The day the record pertains to.

CampaignId
INTEGER

The ID of the campaign.

Reference:

Custom Fields

Columns selected by you. For descriptions of available columns, refer to Microsoft’s documentation.

geographic_performance_report

Replication Method :

Key-based Incremental

 Replication Key :

TimePeriod
Primary Key :

AccountId : _sdc_report_datetime : TimePeriod

 Official docs :

Official Docs
Loading Behavior:

Append-Only

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.

geographic_performance_report table schema

AccountId
INTEGER

The Bing Ads-assigned ID of the account.

Reference:

_sdc_report_datetime
DATE-TIME

The start time of the Stitch replication job that replicated this record.

TimePeriod
DATE

The day the record pertains to.

AdGroupId
INTEGER

The ID of the ad group.

Reference:

CampaignId
INTEGER

The ID of the campaign.

Reference:

Custom Fields

Columns selected by you. For descriptions of available columns, refer to Microsoft’s documentation.

goals_and_funnels_report

Replication Method :

Key-based Incremental

 Replication Key :

TimePeriod
Primary Key :

AccountId : _sdc_report_datetime : TimePeriod

 Official docs :

Official Docs
Loading Behavior:

Append-Only

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.

goals_and_funnels_report table schema

AccountId
INTEGER

The Bing Ads-assigned ID of the account.

Reference:

_sdc_report_datetime
DATE-TIME

The start time of the Stitch replication job that replicated this record.

TimePeriod
DATE

The day the record pertains to.

AdGroupId
INTEGER

The ID of the ad group.

Reference:

CampaignId
INTEGER

The ID of the campaign.

Reference:

Goal
STRING

The name of the goal you set for the conversions you want.

Custom Fields

Columns selected by you. For descriptions of available columns, refer to Microsoft’s documentation.

keyword_performance_report

Replication Method :

Key-based Incremental

 Replication Key :

TimePeriod
Primary Key :

AccountId : _sdc_report_datetime : TimePeriod

 Official docs :

Official Docs
Loading Behavior:

Append-Only

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

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.

  • 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

keyword_performance_report table schema

AccountId
INTEGER

The Bing Ads-assigned ID of the account.

Reference:

_sdc_report_datetime
DATE-TIME

The start time of the Stitch replication job that replicated this record.

TimePeriod
DATE

The day the record pertains to.

AdGroupId
INTEGER

The ID of the ad group.

Reference:

CampaignId
INTEGER

The ID of the campaign.

Reference:

Custom Fields

Columns selected by you. For descriptions of available columns, refer to Microsoft’s documentation.

search_query_performance_report

Replication Method :

Key-based Incremental

 Replication Key :

TimePeriod
Primary Key :

AccountId : _sdc_report_datetime : TimePeriod

 Official docs :

Official Docs
Loading Behavior:

Append-Only

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.

search_query_performance_report table schema

AccountId
INTEGER

The Bing Ads-assigned ID of the account.

Reference:

_sdc_report_datetime
DATE-TIME

The start time of the Stitch replication job that replicated this record.

TimePeriod
DATE

The day the record pertains to.

AdGroupId
INTEGER

The ID of the ad group.

Reference:

CampaignId
INTEGER

The ID of the campaign.

Reference:

Custom Fields

Columns selected by you. For descriptions of available columns, refer to Microsoft’s documentation.
Related Troubleshooting
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.