Skip to main content

Microsoft Ads

The microsoft_ads connector reads entities and reports from the Microsoft Advertising SOAP API: campaigns, ad groups, keywords, targeting and performance reports. From a job, it can also write: create, update and delete items, and upload offline conversions.

Install​

sw connectors -i microsoft_ads

Connect​

Put these keys in a source's auth: block (with provider: microsoft_ads) or in a job's connection.

KeyWhat it isRequired / default
developer_tokenThe developer token (secret)Required
client_idThe application (client) id (secret)Required
refresh_tokenThe OAuth refresh token (secret)Required
client_secretFor web apps; leave it out for desktop and mobile apps (secret)Optional
tenantThe Microsoft Entra tenantcommon
customer_idThe customer the calls are made forOptional
account_idThe account the calls are made forOptional
environmentproduction or sandboxproduction

Secret keys must be secret references ({{ secrets.NAME }}). They are redacted in every log line.

source.yaml
kind: source
name: microsoft_ads_demo

spec:
config:
account_ids: { type: list, items: string }
customer_id: { type: string }
start_date: { type: date, default: "-30d" }
secrets:
developer_token: { type: string }
client_id: { type: string }
refresh_token: { type: string }

auth:
provider: microsoft_ads
developer_token: "{{ secrets.developer_token }}"
client_id: "{{ secrets.client_id }}"
refresh_token: "{{ secrets.refresh_token }}"
customer_id: "{{ config.customer_id }}"

Read data​

In a source​

streams/campaigns.yaml
partitions:
- { name: account_id, values: "{{ config.account_ids }}" }

requests:
- name: raw_campaigns
operation:
service: CampaignManagementService
method: GetCampaignsByAccountId
arguments:
request:
AccountId: "{{ partition.account_id }}"
CampaignType: [Search, Shopping]
records: { path: Campaign }

transform:
mode: page
steps:
- name: campaigns
select: |
SELECT
record->>'Id' AS campaign_id,
record->>'Name' AS campaign_name,
record->>'Status' AS status
FROM raw_campaigns

export:
campaigns:
step: campaigns
primary_key: [campaign_id]
  • partitions runs the request once per account in account_ids.
  • The SOAP request's fields go under arguments.request, with the names the API uses (AccountId).
  • records.path picks the list of records out of the response.
  • A call can also take account_id and customer_id arguments. They override the connection's for that call.

In a job, the same operation (service, method, arguments) goes in a call step.

Reports​

Reports are asynchronous. The stream submits a report request, polls it until it is ready, then downloads the file:

streams/campaign_performance.yaml
partitions:
- { name: account_id, values: "{{ config.account_ids }}" }
incremental: { cursor_field: date, start: "{{ config.start_date }}", window: 30d }

requests:
- name: raw_report
async_job:
submit: # 1. ask for the report
operation:
service: ReportingService
method: SubmitGenerateReport
arguments:
request:
ReportRequest:
"@type": CampaignPerformanceReportRequest
Aggregation: Daily
Format: Csv
ExcludeReportHeader: true
ExcludeReportFooter: true
Columns: [TimePeriod, AccountId, CampaignId, Impressions, Clicks, Spend]
Scope: { AccountIds: ["{{ partition.account_id }}"] }
Time: { CustomDateRangeStart: "{{ window.start }}", CustomDateRangeEnd: "{{ window.end }}" }
poll: # 2. wait until it is ready
operation:
method: PollGenerateReport
arguments: { request: { ReportRequestId: "{{ submit.result }}" } }
every: 15s
timeout: 30m
done_when: { path: Status, equals: Success }
fail_when: { path: Status, equals: Error }
download: { url: "{{ poll.ReportDownloadUrl }}", format: csv, compression: zip } # 3. read the file

transform:
mode: page
steps:
- name: campaign_performance
select: |
SELECT record->>'CampaignId' AS campaign_id,
(record->>'TimePeriod')::DATE AS date,
coalesce((record->>'Impressions')::BIGINT, 0) AS impressions,
coalesce((record->>'Clicks')::BIGINT, 0) AS clicks,
coalesce((record->>'Spend')::DOUBLE, 0) AS spend
FROM raw_report

export:
campaign_performance:
step: campaign_performance
primary_key: [campaign_id, date]
  • "@type" names the SOAP report type; the connector builds it. For other reports, change it (AdGroupPerformanceReportRequest, KeywordPerformanceReportRequest, ...) and Columns.
  • The poll uses the submit's service. The submit returns the bare request id, read as {{ submit.result }}.
  • The report's values arrive as text, so cast them in SQL. A range without data gives 0 rows.
  • In a job, the same async_job goes in a call step instead of operation. Call steps have no windows, so use a fixed range such as Time: { PredefinedTime: Yesterday }.

See Async reports for every key.

Starter streams​

The starter template (examples/sources/ads/microsoft_ads) has these streams. Copy them and change the columns, entities or SQL to fit your needs.

StreamWhat it readsService
campaignsCampaign settings, budgets and statusesCampaignManagementService
ad_groupsAd groups within campaignsCampaignManagementService
ad_group_treeCampaigns joined with their ad groups (several requests)CampaignManagementService
campaign_performanceDaily impressions, clicks and spendReportingService (campaign performance report)
keywordsKeywords, match types and bidsCampaignManagementService
audience_targetsRemarketing lists and audience associationsCampaignManagementService
location_targetsGeographic target criteriaCampaignManagementService

Write data back​

A job's apply output (reverse ETL) writes to Microsoft Advertising. Each chunk of rows is one SOAP call, one item per row.

OperationopRows per call
*Service.Add*, such as CampaignManagementService.AddCampaignscreate100
*Service.Update*update100
*Service.Delete* (needs allow_remove: true)remove100
CampaignManagementService.Apply*Conversion* (offline conversions, adjustments)upload1000
  • Columns are the item's fields as the API spells them (Name, DailyBudget). A dotted column fills a nested field (BiddingScheme.Type); @type names a concrete type (OfflineConversionGoal). For a list of plain values, such as the CampaignIds of DeleteCampaigns, a row is one column.
  • Arguments: request holds the request's other fields (AccountId, or CampaignId for AddAdGroups). account_id defaults to the request's own, else the connection's; customer_id defaults to the connection's.
  • Results: a rejected item fails alone, with Microsoft's error code (such as OfflineConversionMicrosoftClickIdInvalid). A created item keeps its new id ({"Id": 123}).
  • Cut-short calls: Update*, Delete* and conversion uploads are sent again (Microsoft rejects duplicate conversions). An Add* call is held for you to decide (unknown outcomes).
outputs:
- name: create
connection: ads
step: campaigns # columns: Name, DailyBudget, Status, ...
operation:
service: CampaignManagementService
method: AddCampaigns
arguments: {request: {AccountId: "{{ config.account_id }}"}}
apply: {on_row_error: continue}

Complete jobs (create campaigns and offline goals, upload conversions) are in examples/microsoft_ads.

Writes have no dry run

Microsoft records every item it accepts. Try new jobs in the sandbox environment first.

Good to know​

  • Engines: sources (streamwright run), and DuckDB and Spark jobs through call steps. Writes run only in job apply outputs.
  • Operations: the Get*, Search* and Find* methods of any *Service, plus ReportingService.SubmitGenerateReport and PollGenerateReport, are reads. The Add*, Update* and Delete* methods and the conversion uploads are writes. The installed SDK decides which methods exist; a misspelled one fails with the closest names.
  • Reports over fan-out: reading ad groups with GetAdGroupsByCampaignId partitioned by campaign makes one call per campaign, which is slow on large accounts. For metrics, use one report per account instead. A campaign without ad groups reads as 0 rows.
  • Case-sensitive values: enum values are PascalCase, as the API spells them: Aggregation: Daily, Format: Csv, PredefinedTime: Last30Days. The TimePeriod column is not allowed with Aggregation: Summary.
  • Customer vs account: customer_id is the account's parent customer. Swapping the two fails with 119 InvalidAccountId.
  • New offline goals take conversions 2 hours after they are created.
  • Empty values: a NULL field is left out of the item, so Microsoft keeps its current value.
  • Dates and times: send them as text, such as 2026-01-31T14:07:00Z for ConversionTime (in UTC).
  • Vendor arguments: any SOAP request field passes through under arguments.request, for example request: { ReturnAdditionalFields: AdGroupId }.
  • Retries: by default the connector retries SOAP error codes 0, 117 and 207 (waiting 60 seconds after 117 and 207) and the OAuth errors temporarily_unavailable and server_error. Setting retry: { codes: [117] } on a stream replaces that list.

See also​

  • Apply outputs: how writes are delivered, logged and retried.
  • Call steps: reading from a connector inside a job.
  • Shared vocabulary: run one pipeline across Microsoft, Google, Meta and other ad networks.