​

Configuring Amazon Vendor as a Source

​

1. Add account access

• Region: Select the region where your vendor account is registered:
  • North America (US, Canada, Mexico, Brazil)
  • Europe (UK, France, Germany, Italy, Spain, Netherlands, etc.)
  • Far East (Japan, Australia, Singapore)
• Marketplace ID: The Amazon marketplace your vendor account belongs to (e.g., ATVPDKIKX0DER for Amazon US, A2Q3Y263D00KWC for Amazon Brazil). A vendor account belongs to a single marketplace.

• Distributor view: Which side of the business the sales and inventory numbers describe.
  • Manufacturing: your view as the manufacturer, including what Amazon ordered from you. This is the usual choice.
  • Sourcing: Amazon’s view of what it sourced from you. Ordered revenue and ordered units are not reported in this view.
• Selling program: The Amazon program your vendor account participates in: Retail, Business, or Fresh. Amazon publishes the inventory and forecasting reports for Retail and Fresh only, so those two streams fall back to Retail when Business is selected. The run log records it when that happens.
• Start Date: The earliest date from which records will be synced. Amazon keeps roughly 4 years of daily vendor data, so earlier dates are automatically adjusted to the oldest date Amazon still serves.

​

2. Select streams

Tip: The stream can be found more easily by typing its name.

​

3. Configure data streams

• Layer: choose between the existing layers on your catalog. This is where you will find your new extracted tables as the extraction runs successfully.
• Folder: a folder can be created inside the selected layer to group all tables being created from this new data source.
• Table name: we suggest a name, but feel free to customize it. You have the option to add a prefix to all tables at once and make this process faster!
• Sync Type: you can choose between INCREMENTAL and FULL_TABLE.
  • Incremental: every time the extraction happens, we’ll get only the new data - which is good if, for example, you want to keep every record ever fetched.
  • Full table: every time the extraction happens, we’ll get the current state of the data - which is good if, for example, you don’t want to have deleted data in your catalog.

​

4. Configure data source

• Configure Delta Log Retention and determine for how long we should store old states of this table as it gets updated. Read more about this resource here.
• Determine when to execute an Additional Full Sync. This will complement the incremental data extractions, ensuring that your data is completely synchronized with your source every once in a while.

​

5. Check your new source

​

Streams and Fields

​

Use Cases for Data Analysis

​

1. Shipped Revenue and Margin by ASIN

• Identify the products driving revenue
• Spot ASINs where Amazon’s margin is thin, which is what buyers cite in cost negotiations
• Track customer returns against shipped units

SELECT
   s.asin,
   SUM(s.shippedUnits) AS shipped_units,
   ROUND(SUM(s.shippedRevenueAmount), 2) AS shipped_revenue,
   ROUND(SUM(s.shippedCogsAmount), 2) AS shipped_cogs,
   SUM(s.customerReturns) AS customer_returns,
   ROUND(AVG(m.netPureProductMargin), 4) AS avg_net_pure_product_margin
FROM
   nekt_raw.amazon_vendor_sales_report s
   LEFT JOIN nekt_raw.amazon_vendor_net_pure_product_margin_report m
      ON m.asin = s.asin AND m.startDate = s.startDate
WHERE
   s.startDate >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY
   s.asin
ORDER BY
   shipped_revenue DESC

SELECT
   s.asin,
   SUM(s.shippedUnits) AS shipped_units,
   ROUND(SUM(s.shippedRevenueAmount), 2) AS shipped_revenue,
   ROUND(SUM(s.shippedCogsAmount), 2) AS shipped_cogs,
   SUM(s.customerReturns) AS customer_returns,
   ROUND(AVG(m.netPureProductMargin), 4) AS avg_net_pure_product_margin
FROM
   `nekt_raw.amazon_vendor_sales_report` s
   LEFT JOIN `nekt_raw.amazon_vendor_net_pure_product_margin_report` m
      ON m.asin = s.asin AND m.startDate = s.startDate
WHERE
   s.startDate >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY
   s.asin
ORDER BY
   shipped_revenue DESC

​

2. Detail Page Conversion by ASIN

• Find high-traffic, low-conversion pages where content or pricing is the bottleneck
• Prioritize A+ content and imagery work by potential impact
• Measure the effect of a listing change on conversion

SELECT
   t.asin,
   SUM(t.glanceViews) AS glance_views,
   SUM(s.orderedUnits) AS ordered_units,
   CASE
      WHEN SUM(t.glanceViews) > 0
         THEN ROUND(SUM(s.orderedUnits) * 100.0 / SUM(t.glanceViews), 2)
      ELSE 0
   END AS conversion_rate_pct
FROM
   nekt_raw.amazon_vendor_traffic_report t
   LEFT JOIN nekt_raw.amazon_vendor_sales_report s
      ON s.asin = t.asin AND s.startDate = t.startDate
WHERE
   t.startDate >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY
   t.asin
HAVING
   SUM(t.glanceViews) > 100
ORDER BY
   glance_views DESC

SELECT
   t.asin,
   SUM(t.glanceViews) AS glance_views,
   SUM(s.orderedUnits) AS ordered_units,
   ROUND(SAFE_DIVIDE(SUM(s.orderedUnits) * 100.0, SUM(t.glanceViews)), 2) AS conversion_rate_pct
FROM
   `nekt_raw.amazon_vendor_traffic_report` t
   LEFT JOIN `nekt_raw.amazon_vendor_sales_report` s
      ON s.asin = t.asin AND s.startDate = t.startDate
WHERE
   t.startDate >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY
   t.asin
HAVING
   SUM(t.glanceViews) > 100
ORDER BY
   glance_views DESC

​

3. Stock Risk Against Amazon’s Forecast

• Flag ASINs whose on-hand cover is below Amazon’s own demand forecast
• Catch aging and unhealthy inventory before it turns into chargebacks
• Bring an objective number to purchase order negotiations

WITH latest_inventory AS (
   SELECT
      asin,
      sellableOnHandInventoryUnits,
      openPurchaseOrderUnits,
      unhealthyInventoryUnits,
      aged90PlusDaysSellableInventoryUnits,
      ROW_NUMBER() OVER (PARTITION BY asin ORDER BY startDate DESC) AS rn
   FROM
      nekt_raw.amazon_vendor_inventory_report
),
next_4_weeks AS (
   SELECT
      asin,
      SUM(meanForecastUnits) AS forecast_units
   FROM
      nekt_raw.amazon_vendor_forecasting_report
   WHERE
      forecastGenerationDate = (SELECT MAX(forecastGenerationDate) FROM nekt_raw.amazon_vendor_forecasting_report)
      AND startDate <= CURRENT_DATE + INTERVAL '28' DAY
   GROUP BY
      asin
)
SELECT
   i.asin,
   i.sellableOnHandInventoryUnits,
   i.openPurchaseOrderUnits,
   f.forecast_units,
   i.sellableOnHandInventoryUnits + i.openPurchaseOrderUnits - f.forecast_units AS projected_gap,
   i.aged90PlusDaysSellableInventoryUnits,
   i.unhealthyInventoryUnits
FROM
   latest_inventory i
   JOIN next_4_weeks f ON f.asin = i.asin
WHERE
   i.rn = 1
ORDER BY
   projected_gap ASC

WITH latest_inventory AS (
   SELECT
      asin,
      sellableOnHandInventoryUnits,
      openPurchaseOrderUnits,
      unhealthyInventoryUnits,
      aged90PlusDaysSellableInventoryUnits,
      ROW_NUMBER() OVER (PARTITION BY asin ORDER BY startDate DESC) AS rn
   FROM
      `nekt_raw.amazon_vendor_inventory_report`
),
next_4_weeks AS (
   SELECT
      asin,
      SUM(meanForecastUnits) AS forecast_units
   FROM
      `nekt_raw.amazon_vendor_forecasting_report`
   WHERE
      forecastGenerationDate = (SELECT MAX(forecastGenerationDate) FROM `nekt_raw.amazon_vendor_forecasting_report`)
      AND startDate <= DATE_ADD(CURRENT_DATE(), INTERVAL 28 DAY)
   GROUP BY
      asin
)
SELECT
   i.asin,
   i.sellableOnHandInventoryUnits,
   i.openPurchaseOrderUnits,
   f.forecast_units,
   i.sellableOnHandInventoryUnits + i.openPurchaseOrderUnits - f.forecast_units AS projected_gap,
   i.aged90PlusDaysSellableInventoryUnits,
   i.unhealthyInventoryUnits
FROM
   latest_inventory i
   JOIN next_4_weeks f ON f.asin = i.asin
WHERE
   i.rn = 1
ORDER BY
   projected_gap ASC

​

Implementation Notes

​

Skills for agents