Amazon Ads API v1 Report Template

Equivalent to v3 Sponsored_Products_Campaign_report — 24 v3 columns mapped to v1 fields

Summary

This template replicates the v3 Sponsored Products Campaign report on the Amazon Ads API v1 cross-product reporting endpoint (CreateReport). Of the 24 v3 columns, 19 map cleanly to v1 fields. Three categories of columns require special handling: 'Retailer' has no v1 equivalent and is dropped; 'Targeting Type' (manual/auto) has no campaign-grain v1 dimension and must be derived from target-grain match types or fetched from the campaign management API; and the four 'Last Year *' YoY columns have no server-side equivalent in v1 — reproduce them by running a second report for the prior-year date range and joining client-side.

The main field list (23 fields) was validated against the packaged v1 catalog: zero unknown fields, zero missing required, zero incompatible pairs.

Two-report architecture (only if YoY is needed)

If you don't need the v3 'Last Year *' columns, skip Report 2 entirely — the main report alone reproduces 19 of 24 v3 columns. If you do need YoY:

• Report 1 (main): all current-period campaign-grain metrics and dimensions.

• Report 2 (prior year): same shape with timeRange shifted back exactly one year. LEFT JOIN onto Report 1 on campaign.id and (date.value - INTERVAL 1 YEAR). Map metric.impressions → 'Last Year Impressions', metric.clicks → 'Last Year Clicks', metric.totalCost → 'Last Year Spend', metric.cpc → 'Last Year CPC'. Campaigns that didn't exist last year produce NULL — handle in the warehouse layer.

Report 1 — field list (grouped by role)

Time dimension (exactly one required)

• date.value

Level-of-detail dimensions

• portfolio.portfolioId

• portfolio.name

• adProduct.value

• campaign.id

• campaign.name

• campaign.deliveryStatus

• campaign.country

• country.code

• country.name

• campaign.currencyCode

• campaign.budgetAmount

• campaign.budgetType

• campaign.bidStrategy

Required supporting field

• budgetCurrency.value

Delivery metrics

• metric.impressions

• metric.clicks

• metric.ctr

• metric.cpc

• metric.totalCost

Attributed conversion metrics

• metric.purchases

• metric.sales

• metric.roas

Filter (limits the report to SP)

• adProduct.value = SPONSORED_PRODUCTS

v3 → v1 field mapping

Yellow rows highlight columns that don't have a clean one-to-one v1 mapping (no v1 equivalent, derived client-side, or recovered via a separate report).

Critical differences from the v3 report

Operational notes

• Targeting Type recovery: if you need this column without descending to target grain, call the campaign-list operation and join on campaign.id. The campaign object's targetingType field carries 'AUTO' or 'MANUAL' directly. Don't try to derive it from bid strategy — bid strategy is independent of targeting type.

• Status historicity: if you need a true historical campaign status timeline (not just the current status), capture campaign.deliveryStatus daily at ingest time and persist as a slowly-changing dimension in your warehouse. v1 reports the live status only.

• ACOS derivation: in the warehouse, persist the raw inputs (totalCost, sales) and expose ACOS as a view: acos = totalCost / NULLIF(sales, 0).