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.
| Key | What it is | Required / default |
|---|---|---|
developer_token | The developer token (secret) | Required |
client_id | The application (client) id (secret) | Required |
refresh_token | The OAuth refresh token (secret) | Required |
client_secret | For web apps; leave it out for desktop and mobile apps (secret) | Optional |
tenant | The Microsoft Entra tenant | common |
customer_id | The customer the calls are made for | Optional |
account_id | The account the calls are made for | Optional |
environment | production or sandbox | production |
Secret keys must be secret references ({{ secrets.NAME }}). They are redacted in every log line.
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
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]
partitionsruns the request once per account inaccount_ids.- The SOAP request's fields go under
arguments.request, with the names the API uses (AccountId). records.pathpicks the list of records out of the response.- A call can also take
account_idandcustomer_idarguments. 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:
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, ...) andColumns.- 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_jobgoes in a call step instead ofoperation. Call steps have no windows, so use a fixed range such asTime: { 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.
| Stream | What it reads | Service |
|---|---|---|
campaigns | Campaign settings, budgets and statuses | CampaignManagementService |
ad_groups | Ad groups within campaigns | CampaignManagementService |
ad_group_tree | Campaigns joined with their ad groups (several requests) | CampaignManagementService |
campaign_performance | Daily impressions, clicks and spend | ReportingService (campaign performance report) |
keywords | Keywords, match types and bids | CampaignManagementService |
audience_targets | Remarketing lists and audience associations | CampaignManagementService |
location_targets | Geographic target criteria | CampaignManagementService |
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.
| Operation | op | Rows per call |
|---|---|---|
*Service.Add*, such as CampaignManagementService.AddCampaigns | create | 100 |
*Service.Update* | update | 100 |
*Service.Delete* (needs allow_remove: true) | remove | 100 |
CampaignManagementService.Apply*Conversion* (offline conversions, adjustments) | upload | 1000 |
- Columns are the item's fields as the API spells them (
Name,DailyBudget). A dotted column fills a nested field (BiddingScheme.Type);@typenames a concrete type (OfflineConversionGoal). For a list of plain values, such as theCampaignIdsofDeleteCampaigns, a row is one column. - Arguments:
requestholds the request's other fields (AccountId, orCampaignIdforAddAdGroups).account_iddefaults to the request's own, else the connection's;customer_iddefaults 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). AnAdd*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.
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*andFind*methods of any*Service, plusReportingService.SubmitGenerateReportandPollGenerateReport, are reads. TheAdd*,Update*andDelete*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
GetAdGroupsByCampaignIdpartitioned 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. TheTimePeriodcolumn is not allowed withAggregation: Summary. - Customer vs account:
customer_idis the account's parent customer. Swapping the two fails with119 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:00ZforConversionTime(in UTC). - Vendor arguments: any SOAP request field passes through under
arguments.request, for examplerequest: { ReturnAdditionalFields: AdGroupId }. - Retries: by default the connector retries SOAP error codes
0,117and207(waiting 60 seconds after117and207) and the OAuth errorstemporarily_unavailableandserver_error. Settingretry: { 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.