Skip to main content

OpenAI Ads

The openai_ads connector reads ad accounts, campaigns, ad groups, ads and performance reports from the OpenAI Advertising API. From a job, it can also send rows to any endpoint. It is a REST connector: you declare the requests in YAML, the same way as the REST API connector.

Install​

sw connectors -i openai_ads

Connect​

OpenAI Ads signs in with an API key sent as a bearer token. Put these keys in a source's auth: block (with provider: openai_ads) or in a job's connection.

KeyWhat it isRequired / default
typebearerRequired
tokenThe API key (secret: a {{ secrets.NAME }} reference, redacted in every log line)Required

The http block describes the API. The values OpenAI Ads needs:

KeyValue
base_urlhttps://api.ads.openai.com
paginatorcursor with token_path: last_id, param: after, has_more_path: has_more
recordspath: data
source.yaml
kind: source
name: openai_ads_demo

spec:
config:
account_ids: { type: list, items: string, description: "Ad account IDs" }
secrets:
openai_ads_key: { type: string }

auth:
provider: openai_ads
type: bearer
token: "{{ secrets.openai_ads_key }}"

http:
base_url: https://api.ads.openai.com
paginator: { type: cursor, token_path: last_id, param: after, has_more_path: has_more }
records: { path: data }
policy:
retry: { codes: [429, 500, 502, 503, 504], max_attempts: 5, backoff: exponential, max_delay: 2m }
  • Paging is by cursor: the response's last_id becomes the next request's after parameter while has_more is true.
  • Records come from the response's data list.
  • The source's policy retries rate limits (429) and server errors with exponential backoff, up to 5 attempts.

Read data​

In a source​

streams/campaigns.yaml
partitions:
- { name: account_id, values: "{{ config.account_ids }}" }
policy:
retry: { max_attempts: 3, max_delay: 2m }

requests:
- name: raw_campaigns
operation:
path: /v1/campaigns
method: GET
params:
ad_account_id: "{{ partition.account_id }}"
limit: 500

transform:
mode: page
steps:
- name: campaigns
select: |
SELECT partition->>'account_id' AS account_id,
record->>'id' AS campaign_id,
record->>'name' AS campaign_name,
record->>'status' AS status,
(record->>'budget_micros')::BIGINT AS budget_micros,
record->>'currency' AS currency
FROM raw_campaigns

export:
campaigns:
step: campaigns
primary_key: [account_id, campaign_id]
  • The stream runs once per account in account_ids.
  • The stream's own policy.retry replaces the source's for this stream.

In a job​

The connection holds the sign-in and the http defaults. A step's operation is the same request as in a stream, and its sql reads the table records (call steps).

connections:
openai_ads_api:
provider: openai_ads
type: bearer
token: "{{ secrets.openai_ads_key }}"
http:
base_url: https://api.ads.openai.com

steps:
- name: campaigns
connection: openai_ads_api
operation:
path: /v1/campaigns
method: GET
params: {ad_account_id: "{{ config.account_id }}", limit: 100}
records: {path: data}
paginator: {type: cursor, token_path: last_id, param: after, has_more_path: has_more}
sql: |
SELECT record->>'id' AS campaign_id,
record->>'name' AS campaign_name,
record->>'status' AS status,
(record->>'daily_budget')::DOUBLE AS daily_budget
FROM records

Starter streams​

The starter template (examples/sources/ads/openai_ads) has these streams. The performance streams read daily windows from a start_date config input. Copy them and change the columns, SQL or endpoints to fit your needs.

StreamWhat it readsEndpoint
ad_accountsAccessible ad accounts and their currency/v1/ad_accounts
campaignsCampaign settings and budgets/v1/campaigns
ad_groupsAd groups and bidding strategies/v1/ad_groups
adsAds and creative definitions/v1/ads
ad_group_treeCampaigns joined with their ad groups/v1/campaigns, /v1/ad_groups
ad_treeCampaigns joined with their ads/v1/campaigns, /v1/ads
campaign_performanceDaily impressions, clicks and spend per campaign (incremental)/v1/campaigns/<id>/insights
ad_group_performanceDaily metrics per ad group (incremental)/v1/ad_groups/<id>/insights
ad_performanceDaily metrics per ad (incremental)/v1/ads/<id>/insights

Complete jobs that read ad groups and their metrics are in examples/openai_ads.

Write data back​

A job's apply output (reverse ETL) sends rows to any endpoint of this API as HTTP requests:

outputs:
- name: send
connection: openai_ads_api
step: rows # each row becomes one request's JSON body
operation:
path: /endpoint # the API endpoint that takes the rows
method: POST
apply:
on_row_error: continue
  • Without json, each row is sent as one request, with the row as its JSON body. When json holds {{ rows }}, the whole chunk goes in one request.
  • {{ row.<column> }} puts a row's value in the path or body (/campaigns/{{ row.id }}).
  • A row the API rejects fails alone, with the HTTP status as its code.
  • Check OpenAI's API reference for the endpoint and the fields it expects.

Good to know​

  • Engines: sources (streamwright run), and DuckDB and Spark jobs through call steps. Writes run only in job apply outputs.
  • Vendor arguments: any HTTP request field goes to OpenAI unchanged, for example params: { limit: 100 }.

See also​