How to merge Amazon Ads and Seller Central reports by ASIN

ACoS versus TACoS

Amazon Ads reports ACoS (advertising cost of sales): ad spend divided by sales attributed to ads. It ignores organic sales entirely. TACoS (total advertising cost of sales) divides the same spend by all sales of the product, ads-driven or not. A falling TACoS while ad spend holds steady usually means organic sales are growing — the outcome most sellers want. You can only get it by joining the two reports.

The two reports to download

Column names differ slightly by marketplace and report version, and attribution windows differ by ad type (Sponsored Products commonly uses 7-day, Sponsored Brands and Display 14-day), so check the headers in your downloads. Use the same date range for both. The business report summarises the whole range per ASIN unless you change it, whereas the ads report can be daily — sum the ads report to the same range before joining.

Clean before you join

• Child vs parent ASIN: ads run on child ASINs (a size or colour), so join on the child ASIN column, not the parent.
• Currency text: Ordered Product Sales often arrives as “£1,234.56”. Strip the symbol and comma so it becomes a number.
• Multiple ad types: if you run Sponsored Brands or Display too, download those reports and stack them before summing spend per ASIN, or your TACoS will be understated.
• ASINs with no ads: keep them. A left join from the business report keeps every selling product, with spend as 0.

The join

WITH ads AS (
  SELECT "Advertised ASIN" AS asin,
         SUM("Spend") AS ad_spend,
         SUM("7 Day Total Sales") AS ad_sales
  FROM sp_advertised_product
  GROUP BY 1
)
SELECT b."(Child) ASIN" AS asin,
       b."Ordered Product Sales" AS total_sales,
       COALESCE(a.ad_spend, 0) AS ad_spend,
       a.ad_sales,
       a.ad_spend / NULLIF(a.ad_sales, 0) AS acos,
       COALESCE(a.ad_spend, 0) / NULLIF(b."Ordered Product Sales", 0) AS tacos
FROM business_report b
LEFT JOIN ads a ON a.asin = b."(Child) ASIN"
ORDER BY tacos DESC;

In Excel, the equivalent is a SUMIFS on the ads sheet for each ASIN in the business report: =SUMIFS(Ads[Spend], Ads[Advertised ASIN], [@ASIN]), then divide by Ordered Product Sales.

Worked example: four ASINs, September

B0C1AAA333 has the worst ACoS (£400 / £1,000 = 40%) but the lowest TACoS among advertised products (£400 / £8,000 = 5%): it sells mostly organically and the ads are a small top-up. B0C1AAA222 looks fine on ACoS (30%) but 60% of its sales (£3,000 of £5,000) are ad-attributed, so it's the product most exposed if you cut spend. Across the range, £2,500 / £27,500 = 9.1% TACoS.

Ad-attributed sales can include sales of other ASINs (halo sales) depending on the column you export. If ad sales for an ASIN exceed its total sales, you've picked a column that includes other products.

Next steps

Add a month column and stack several months to watch TACoS trend per ASIN. To get to profit rather than sales, bring in fees too — see matching Amazon FBA fees to the order report. Analistable can load both reports in the browser and answer “which ASINs have TACoS above 15%?” without you writing the join; it reads the files and doesn't write anything back to Amazon. More patterns are in the guide to answering questions across spreadsheets.

Track TACoS over time

A single month of TACoS is a snapshot. The useful signal is the trend per ASIN. Download both reports monthly, add a month column to each, and stack them before joining on month and ASIN:

SELECT b.month, b.asin,
             SUM(a.spend) / NULLIF(SUM(b.total_sales), 0) AS tacos
      FROM business_monthly b
      LEFT JOIN ads_monthly a ON a.asin = b.asin AND a.month = b.month
      GROUP BY 1, 2
      ORDER BY 2, 1;

Read the trend alongside ad spend. TACoS falling while spend is flat means organic sales grew; TACoS falling because you cut spend may just mean sales fell less than spend did. Total sales and units, plotted on the same chart, show which it is.

Keep refunds in mind too. Ordered Product Sales counts orders placed, not net of returns, so a product with a high return rate looks healthier on TACoS than it is on profit.

Frequently asked questions

Why is my TACoS higher than ACoS for some ASINs?

It shouldn't be for a single ASIN, because total sales include ad sales. If it happens, the ad sales column probably includes halo sales of other products, or the two reports cover different dates.

Do I need daily data?

Not for TACoS. Weekly or monthly totals smooth out the mismatch between click date and order date, and are easier to download from both consoles.

What is a good TACoS?

It depends on margin and stage. New launches often run high TACoS deliberately; mature products usually aim lower. Track the trend per ASIN rather than chasing one benchmark.

Why don't ad sales and Seller Central sales line up by day?

Ad sales are attributed to the day of the click within the attribution window, while business reports count the order date. Compare over weeks or months, not single days.

Can I do this for Vendor Central?

The method is the same — ad spend by ASIN joined to sales by ASIN — but the sales report and its column names come from Vendor Central's retail analytics instead.

Related guides

• How to match Amazon FBA fees to your orders
• How to merge email campaign results with GA4 conversions
• How to merge keyword rankings with Search Console data
• How to merge two tables and summarise them with a pivot
• How to merge UTM campaign data with CRM leads