How to Analyze Amazon Seller Central Reports in Excel

Seller Central has dozens of reports, and most sellers download the same three or four over and over: traffic and sales by ASIN, the raw orders, and the settlement that explains why the payout is smaller than the sales. Each one answers a different question, uses a different file layout, and counts money in a different way. Mix them up and you get numbers that don't reconcile with anything. This guide covers which report to use for which question, the layout traps, and the analysis worth doing once the data is in a spreadsheet.

The three reports that cover most questions

Menu names shift between marketplaces and Seller Central redesigns, so if a path doesn't match exactly, look under the same top-level Reports menu. For returns, the FBA Customer Returns report (under the fulfillment reports) lists returned units with a reason code.

Getting the files into a spreadsheet

Business Reports download as CSV, which opens directly. Pick the date range before downloading; the file is a summary of that range, not a daily series. For a trend, download one file per month (or use the "by date" variants of the report) and keep them as separate sheets.

All Orders and settlement flat files are usually tab-delimited .txt files. Excel opens them if you use File → Open and choose the text file (the import wizard picks up the tabs), and Google Sheets imports them with the tab separator. Either way, save the result as .xlsx or CSV before doing anything else. That's also the format ChatExcel accepts: Excel (.xlsx, .xls) or CSV, not .txt.

Two things to check after opening. First, long order IDs and ASINs: make sure Excel didn't turn an order ID like 111-2345678-9012345 into something else or drop leading zeros from a SKU. Import those columns as text if needed. Second, dates: settlement and order files use ISO-style timestamps, sometimes with a time zone. Keep them as they are and group by date later.

Business Reports: the columns

The Detail Page Sales and Traffic by Child Item report includes (Parent) ASIN, (Child) ASIN, Title, Sessions, Session Percentage, Page Views, Featured Offer (Buy Box) Percentage, Units Ordered, Unit Session Percentage, Ordered Product Sales, and Total Order Items. Recent versions split sessions and page views into browser, mobile app, and total; use the total columns unless you're specifically comparing devices.

Unit Session Percentage is units ordered ÷ sessions. It's Amazon's conversion rate, but on units, so a customer who buys three counts three times. If you want order conversion, use Total Order Items ÷ Sessions instead.

Ordered Product Sales is the item price × units at the time of ordering. It's before fees, before refunds, and includes orders that were later cancelled. It won't match your settlement, and it isn't meant to.

All Orders: the traps

One row per item, not per order. An order with two different SKUs appears on two rows with the same amazon-order-id. Counting rows counts items; count distinct order IDs to count orders.

Status matters. order-status includes Pending, Shipped, and Cancelled. Cancelled rows are still in the file. Filter them out before summing item-price.

Price columns are per line, not per unit. item-price is the total for that line (quantity × unit price), and tax, shipping, and promotion discounts sit in separate columns. Decide up front whether "sales" means item price only or item price plus shipping minus promotions, and use the same definition everywhere.

Pending orders may show no price. Until an order is confirmed, some price fields can be blank. Treat blanks as unknown, not zero, or restrict the analysis to shipped orders.

Channel and marketplace. sales-channel (e.g. Amazon.com vs Amazon.ca) and fulfillment-channel (Amazon for FBA, Merchant for your own fulfillment) are separate columns. Group by both if you sell in several marketplaces or mix FBA with fulfilling yourself.

Settlement reports: fees split across rows

The settlement flat file is the one that reconciles with your bank, and it's the easiest to misread. Each order shows up as several rows: one for the principal (the item price), one for each fee (referral commission, FBA fulfillment fee), and more for shipping, tax, and promotions. The transaction-type column says what kind of event it is (Order, Refund, ServiceFee, Adjustment, and so on); amount-type and amount-description say what each line is; and a single amount column holds the money, positive for credits and negative for charges.

That layout means:

• Summing amount for one order gives the net you received for it. Summing it for the whole file gives the settlement total (the file's summary row aside, so leave that out).
• Fees are the negative lines whose amount-type is a fee type. Group by amount-description to see which fees cost the most.
• Refunds carry their own fee lines, including a partial return of the referral fee and, in some cases, a refund administration fee. Look at refunds as a group, not just the principal.
• Account-level lines (subscription fee, advertising, storage fees, reimbursements) have no order ID. They're real costs, so don't drop them when you filter by order.

Each settlement covers one period, usually about two weeks. For monthly numbers, stack several settlement files into one sheet and group by the posted date rather than the settlement period.

Currency and marketplace. Every settlement has a currency, and each marketplace settles separately. A US and a Canadian settlement in the same workbook need to be grouped by currency before you add anything up.

Questions worth asking

1. Units and sales by ASIN. Top ASINs by Units Ordered and by Ordered Product Sales, from the Business Report. Often the two rankings differ, and the gap tells you where price is doing the work.
2. Conversion by ASIN. Unit Session Percentage per ASIN next to Sessions. High traffic and low conversion is a listing problem (price, images, reviews); low traffic and high conversion is a visibility problem.
3. Featured Offer percentage. ASINs where you hold the Buy Box less often than usual will usually show a sales dip. Check this before blaming the listing.
4. Fee share of revenue. From the settlement: total fees ÷ total principal, overall and by fee type. Split by SKU to find products whose fees eat most of the margin.
5. Returns by ASIN and reason. From the Customer Returns report: returned units ÷ units ordered per ASIN, then the most common reason codes for the worst offenders.
6. Order mix by marketplace and fulfillment channel. From All Orders: distinct orders and sales by sales-channel and fulfillment-channel, cancellations excluded.
7. Monthly trend. Sales and units by month, stacking monthly Business Reports or grouping All Orders by purchase-date month.

Doing it without formulas

Put the reports in one workbook (one sheet each) and upload it to ChatExcel. Every sheet is read, and answers are computed from every row. Then ask in plain English:

• "Top 10 ASINs by units ordered, with sessions and unit session percentage, as a bar chart."
• "From the settlement sheet, total fees as a share of principal, broken down by fee description."
• "Distinct orders and item sales by sales channel, excluding cancelled orders."
• "Return rate by ASIN: returned units from the returns sheet divided by units ordered."

Give the ground rules once ("count distinct order IDs, skip cancelled rows, group by currency") so they apply throughout. Ask "what's the Excel formula for that?" to get the SUMIFS or COUNTIFS version for your own workbook, and see the Shopify orders guide if you sell on both platforms and want to compare them side by side.