Sponsored Brands Keyword mapping

Equivalent to v3 Sponsored_Brands_Keyword_report — 46 v3 columns mapped to v1 fields across two reports

Summary

This template replicates the v3 Sponsored Brands Keyword report on the Amazon Ads API v1 cross-product reporting endpoint (CreateReport). Of the 46 v3 columns, 41 map cleanly to v1 fields, 4 are derived client-side (ACOS, click-only ACOS, % of units new-to-brand, and the VTR definition choice), and 1 (Top-of-search Impression Share) requires a separate campaign-level report that joins back to the main report on (date.value, campaign.id).

Both report payloads were validated against the packaged v1 catalog: 47 fields in the main report and 8 in the supplemental TOS report — all returned valid: true with zero unknown fields, zero missing required, and zero incompatible pairs.

Most important difference vs the SP reports in this series: the filter is adProduct.value = SPONSORED_BRANDS (not SPONSORED_PRODUCTS). The v1 endpoint is shared across all ad products and the filter is the only thing that scopes the result set.

Two-report architecture

The validator caught that metric.topOfSearchImpressionShare is incompatible with adGroup.id, adGroup.name (and portfolio.*, country.*) and requires adProduct.value + advertiserAccount.id. This means the metric only lives at the campaign grain in v1. Build two reports:

• Report 1 (main): all 41 mappable v3 columns at the date × portfolio × campaign × ad group × keyword × match-type grain, plus the four derivation inputs.

• Report 2 (supplemental): metric.topOfSearchImpressionShare at the date × campaign grain. LEFT JOIN onto Report 1 on (date.value, campaign.id). The TOS impression share value will fan out across all (keyword, match type, ad group) rows for the same campaign-day — that's correct, not a bug.

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

• campaign.costType

• adGroup.id

• adGroup.name

• target.value

• target.matchType

Required supporting field

• budgetCurrency.value

Delivery metrics

• metric.impressions

• metric.viewableImpressions

• metric.clicks

• metric.ctr

• metric.vctr

• metric.cpc

• metric.vcpm

• metric.totalCost

Video metrics

• metric.firstQuartileVideoAd

• metric.midpointVideoAd

• metric.thirdQuartileVideoAd

• metric.completeViewsVideoAd

• metric.unmutesVideoAd

• metric.5secondViewsVideoAd

• metric.5secondViewRateVideoAd

• metric.completionRateVideoAd

All-attribution conversion (14-day)

• metric.sales

• metric.purchases

• metric.unitsSold

• metric.purchaseRateOverClicks

• metric.purchaseRate

• metric.roas

Brand engagement

• metric.brandedSearches

• metric.detailPageViews

New-to-brand

• metric.newToBrandPurchases

• metric.newToBrandSales

• metric.newToBrandUnitsSold

• metric.percentOfPurchasesNewToBrand

• metric.percentOfSalesNewToBrand

• metric.newToBrandPurchaseRate

Click-only attribution variants

• metric.salesFromClicks

• metric.purchasesFromClicks

• metric.unitsSoldFromClicks

• metric.roasFromClicks

• metric.detailPageViewsFromClicksHalo

Filter (limits the report to SB)

• adProduct.value = SPONSORED_BRANDS

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, definition-dependent, or split across reports).

Critical differences from the v3 report

Operational notes

• VTR definition: confirm with consumers whether they expect VTR to mean completion rate (full play) or 5-second view rate. The template carries both metric.completionRateVideoAd AND metric.5secondViewRateVideoAd so the warehouse can pick. If you only want one, drop the other from the field list to slim the payload.

• Conversion rate: same definition pattern as the SP Targeting report. SB UIs typically use orders/clicks → metric.purchaseRateOverClicks. Drop metric.purchaseRate if you don't need the impressions-denominated alternative.

• Derivations to materialize in the warehouse: acos = totalCost / NULLIF(sales, 0); acosClickOnly = totalCost / NULLIF(salesFromClicks, 0); percentOfUnitsNewToBrand = newToBrandUnitsSold / NULLIF(unitsSold, 0). Persist the raw inputs alongside the views.

• Reusing this template for SB Campaign or SB Ad Group: drop target.value, target.matchType (and adGroup.* for campaign grain), keep everything else — the metric set is identical at coarser grains.