Skip to main content

Amazon Ads

The amazon_ads connector reads advertising profiles and Sponsored Products (SP) campaigns, ad groups, product ads and keywords from the Amazon 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 amazon_ads

Connect​

Amazon Ads signs in with Login with Amazon (LWA) OAuth 2, or with a bearer token. Put these keys in a source's auth: block (with provider: amazon_ads) or in a job's connection.

KeyWhat it isRequired / default
typeoauth2_refresh_token or bearerRequired
token_urlThe LWA token endpoint, https://api.amazon.com/auth/o2/tokenFor oauth2_refresh_token
client_idYour LWA client id (secret)For oauth2_refresh_token
client_secretYour LWA client secret (secret)For oauth2_refresh_token
refresh_tokenYour LWA refresh token (secret)For oauth2_refresh_token
scopesThe scopes to requestOptional
tokenA bearer token (secret)For bearer
  • Secret keys must be secret references ({{ secrets.NAME }}). They are redacted in every log line.
  • With oauth2_refresh_token, StreamWright exchanges the refresh token for a short-lived access token and refreshes it for you.
  • Amazon also needs two headers in the http block: Amazon-Advertising-API-ClientId (your client id) and Amazon-Advertising-API-Scope (the profile id, so requests apply to that profile).
source.yaml
kind: source
name: amazon_ads_demo

spec:
config:
api_url:
type: string
default: "https://advertising-api.amazon.com"
description: "Regional endpoint: https://advertising-api.amazon.com (NA), https://advertising-api-eu.amazon.com (EU), https://advertising-api-fe.amazon.com (FE)"
profile_id:
type: string
required: false
description: "Amazon Advertising Profile ID (sent as Amazon-Advertising-API-Scope)"
start_date:
type: date
default: "-30d"
description: "The first day the report streams read (later runs resume from their bookmarks)"
secrets:
amazon_client_id: { type: string }
amazon_client_secret: { type: string }
amazon_refresh_token: { type: string }

auth:
provider: amazon_ads
type: oauth2_refresh_token
token_url: https://api.amazon.com/auth/o2/token
client_id: "{{ secrets.amazon_client_id }}"
client_secret: "{{ secrets.amazon_client_secret }}"
refresh_token: "{{ secrets.amazon_refresh_token }}"

http:
base_url: "{{ config.api_url }}"
headers:
Amazon-Advertising-API-ClientId: "{{ secrets.amazon_client_id }}"
Amazon-Advertising-API-Scope: "{{ config.profile_id }}"
paginator:
type: offset
offset_param: startIndex
limit_param: count
page_size: 100
  • api_url picks the regional endpoint: North America (default), Europe or Far East.
  • start_date is the first day report streams read.
  • Paging uses startIndex and count.

Read data​

In a source​

streams/sp_campaigns.yaml
description: Amazon Sponsored Products campaigns metadata and budgets.

requests:
- name: raw_sp_campaigns
operation:
path: /v2/sp/campaigns
method: GET

transform:
mode: page
steps:
- name: sp_campaigns
select: |
SELECT record->>'campaignId' AS campaign_id,
record->>'name' AS campaign_name,
record->>'campaignType' AS campaign_type,
record->>'targetingType' AS targeting_type,
record->>'state' AS state,
(record->>'dailyBudget')::DOUBLE AS daily_budget,
record->>'startDate' AS start_date,
record->>'endDate' AS end_date,
(record->>'premiumBidAdjustment')::BOOLEAN AS premium_bid_adjustment
FROM raw_sp_campaigns

export:
sp_campaigns:
step: sp_campaigns
primary_key: [campaign_id]

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:
amazon_ads_api:
provider: amazon_ads
type: oauth2_refresh_token
token_url: https://api.amazon.com/auth/o2/token
client_id: "{{ secrets.amazon_client_id }}"
client_secret: "{{ secrets.amazon_client_secret }}"
refresh_token: "{{ secrets.amazon_refresh_token }}"
http:
base_url: https://advertising-api.amazon.com
headers:
Amazon-Advertising-API-ClientId: "{{ config.client_id }}" # headers take no secrets: the public id
Amazon-Advertising-API-Scope: "{{ config.profile_id }}"

steps:
- name: sp_campaigns
connection: amazon_ads_api
operation:
path: /v2/sp/campaigns
method: GET
paginator: {type: offset, offset_param: startIndex, limit_param: count, page_size: 100}
sql: |
SELECT record->>'campaignId' AS campaign_id,
record->>'name' AS campaign_name,
record->>'state' AS state,
(record->>'dailyBudget')::DOUBLE AS daily_budget
FROM records
note

In a job, a connection's headers cannot use {{ secrets.* }}: only its credential keys can. Pass the client id for the header as a config value, as above.

Reports​

Performance metrics come from v3 reports, which are async: the stream asks for the report, checks it until it is COMPLETED, then downloads its gzip JSON file. Each part is an HTTP request.

streams/sp_campaign_report.yaml
incremental:
cursor_field: date
start: "{{ config.start_date }}"
window: 30d # daily spCampaigns reports span at most 31 days
lookback: 14d # attributed sales change for up to 14 days

requests:
- name: raw_report
async_job:
submit: # 1. ask for the report
operation:
path: /reporting/reports
method: POST
headers: { Content-Type: application/vnd.createasyncreportrequest.v3+json }
json:
name: "sp_campaigns {{ window.start | date('%Y-%m-%d') }}"
startDate: "{{ window.start | date('%Y-%m-%d') }}"
endDate: "{{ window.end | date('%Y-%m-%d') }}"
configuration:
adProduct: SPONSORED_PRODUCTS
reportTypeId: spCampaigns
groupBy: [campaign]
columns: [date, campaignId, impressions, clicks, cost]
timeUnit: DAILY
format: GZIP_JSON
poll: # 2. wait until it is ready
operation:
path: "/reporting/reports/{{ submit.reportId }}"
every: 30s
timeout: 3h
done_when: { path: status, equals: COMPLETED }
fail_when: { path: status, equals: FAILED }
download: { url: "{{ poll.url }}", format: json, compression: gzip } # 3. read the file

transform:
mode: page
steps:
- name: sp_campaign_report
select: |
SELECT (record->>'date')::DATE AS date,
record->>'campaignId' AS campaign_id,
coalesce((record->>'impressions')::BIGINT, 0) AS impressions,
coalesce((record->>'clicks')::BIGINT, 0) AS clicks,
coalesce((record->>'cost')::DOUBLE, 0) AS cost
FROM raw_report

export:
sp_campaign_report:
step: sp_campaign_report
primary_key: [campaign_id, date]
  • The file link is presigned, so it is fetched without the API's headers and token. An empty file (a window without traffic) gives 0 rows.
  • For other reports, change reportTypeId, groupBy and columns.
  • In a job, the same async_job goes in a call step, with fixed dates from config (call steps have no windows).

Starter streams​

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

StreamWhat it readsEndpoint
profilesAdvertising profiles, marketplaces and time zones/v2/profiles
sp_campaignsSP campaigns, states and daily budgets/v2/sp/campaigns
sp_ad_groupsSP ad groups and default bids/v2/sp/adGroups
sp_adsSP product ads (SKU and ASIN)/v2/sp/productAds
sp_keywordsSP keywords and match types/v2/sp/keywords
sp_campaign_reportDaily SP campaign performance, from a v3 report/reporting/reports

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: amazon_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 Amazon'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.
  • Paging and retries: every list endpoint pages automatically. A request is retried on HTTP 429, 500, 502, 503 and 504 by default (REST API).
  • One request per list: the list endpoints return the whole profile, each item with its campaignId. Read each once and join in SQL, instead of one request per campaign. One report covers every campaign.
  • Case-sensitive values: Amazon enums are upper case, such as SPONSORED_PRODUCTS, DAILY and GZIP_JSON.
  • Vendor arguments: any HTTP request field goes to Amazon unchanged, for example params: { count: 100 } or a profileId.

See also​