Amazon Ads API v1 Report Template

Equivalent to v3 Sponsored_Products_Targeting_report — 25 v3 columns mapped to v1 fields across two reports

Summary

This template replicates the v3 Sponsored Products Targeting report on the Amazon Ads API v1 cross-product reporting endpoint (CreateReport). Twenty-three v3 columns map cleanly to v1 field_ids on a single targeting-grain report. Two columns require special handling: 'Retailer' has no v1 equivalent and is dropped, and 'Top-of-search Impression Share' is incompatible with portfolio/adGroup/country dimensions in v1 and must be requested as a separate campaign-level report and joined back to the main report on (date.value, campaign.id).

Both report payloads were validated against the packaged v1 catalog: zero unknown fields, zero missing required, zero incompatible pairs.

Two-report architecture

The validator caught that metric.topOfSearchImpressionShare is INCOMPATIBLE with portfolio.portfolioId, portfolio.name, adGroup.id, adGroup.name, country.code, country.name — and REQUIRES adProduct.value and advertiserAccount.id. This means the metric only lives at the campaign grain in v1. Build two reports and union them in the warehouse:

• Report 1 (main): all 23 mappable v3 columns at the date × portfolio × campaign × ad group × country × target × match-type grain.

• Report 2 (supplemental): metric.topOfSearchImpressionShare at the date × campaign grain. Join to Report 1 on (date.value, campaign.id) — TOS impression share will repeat across all targets within the same campaign-day.

Report 1 — field list (grouped by role)

Time dimension (exactly one required)

• date.value

Level-of-detail dimensions

• portfolio.portfolioId

• portfolio.name

• campaign.id

• campaign.name

• campaign.currencyCode

• adGroup.id

• adGroup.name

• country.code

• country.name

• target.value

• target.matchType

Required supporting field

• budgetCurrency.value

Delivery metrics

• metric.impressions

• metric.clicks

• metric.ctr

• metric.cpc

• metric.totalCost

Attributed conversion metrics

• metric.sales

• metric.purchases

• metric.unitsSold

• metric.purchaseRate

• metric.purchaseRateOverClicks

• metric.salesPromoted

• metric.salesHalo

• metric.unitsSoldPromoted

• metric.unitsSoldHalo

• 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 split across reports).

Critical differences from the v3 report

Operational notes

• Conversion-rate semantics: pick metric.purchaseRateOverClicks if your downstream consumers expect orders/clicks (legacy SP UI default). Pick metric.purchaseRate if they expect purchases/impressions. Including both lets the warehouse decide.

• ACOS derivation in the warehouse: acos = totalCost / NULLIF(sales, 0). Persist the raw inputs alongside the computed view.

• Joining the two reports: use a LEFT JOIN from Report 1 onto Report 2 on (date.value, campaign.id). Report 2 produces one row per campaign-day; the value will fan out across all (target, match type, ad group) rows in Report 1 — TOS impression share is a campaign-level metric, so this fan-out is correct, not a bug.