Analyze Amazon Sales Data in Excel: Import and Pivot Tables

Which SKU brought in the most sales last quarter? And what share of it goes to referral fees and FBA fulfillment? The answers are in your payment data, row by row. An Excel file and a pivot table are enough. You don’t need an expensive tool.

You get the data in two ways: from Seller Central via the transaction report or the settlement report, or from AMALYTIX via the “Financial events” report. Amazon’s reports are available to sellers only, not to vendors.

In Excel you then build three analyses: sales per SKU and month, costs as a share of sales, and sales per unit sold and country as a calculated field. For first analyses and smaller amounts of data, you can also use free Amazon tools before you invest in professional solutions.

Download the transaction or settlement report from Seller Central

A period of your choice or exactly one settlement: the period decides which of the two payment reports you need.

The transaction report for a date range. You find it in the Reports Repository under Finance › financial reports. As the report type, choose “Transaction”. In the classic menu, the Reports Repository sits under Payments. The report comes as a CSV file. You choose a month or a custom range, up to 365 days per report and back to 2012. Generating it can take up to three hours. Next to the transaction report there is a summary report as a PDF, which is no use for Excel.

The settlement report. It covers exactly one settlement period. You download it from the “All Statements” page. Amazon offers three formats: XML, Flat File and Flat File V2. Choose Flat File V2. The file is tab-delimited and opens well in Excel. The SP-API (Selling Partner API) also only provides the settlement report V2; it replaces the old Flat File and XML formats. Amazon creates these reports on a fixed schedule; you cannot request them.

For a pivot table, these Flat File V2 columns matter most:

Column	Content
transaction-type	type of transaction
order-id	order number
amount-type	general type of the amount
amount-description	exact type of the amount, e.g. Principal, Commission, FBAPerUnitFulfillmentFee, ShippingChargeback
amount	amount
sku	your SKU
quantity-purchased	quantity

This table is for the settlement report. The transaction report for a date range has other columns: there, the quantity is in “Quantity” and the referral fee in “Selling fees”. Amazon lists all columns on the help page Payment Date Range Transaction and Summary reports. They can differ by country.

The column names come from Amazon’s documentation; single labels in Seller Central can differ. Principal is the sales price, Commission the referral fee. Our article on Amazon seller fees explains what is behind each fee. FBAPerUnitFulfillmentFee is the FBA fulfillment fee per unit; you find the rates in our article on Amazon FBA fees.

Watch the number format: in the EU marketplaces, Amazon writes amounts in the local format, with a decimal comma (“95,00”). This matters when you import the file.

New in 2026: held payments

For some sellers, Amazon holds payouts until shortly after delivery (a reserve based on the delivery date, seven days after delivery). For these sellers, transaction reports for dates from January 1, 2025 also contain deferred and released transactions. Two new columns come with them: “Transaction Status” and “Transaction Release Date”. Amazon rolled out the change between March 23 and the end of May 2026.

For your analysis this means: filter by status if you only want to see amounts that have already been released.

Export payments from AMALYTIX

In AMALYTIX you can export almost all the data the tool collects for you as a CSV file. This allows analyses the tool does not offer itself, for example for your own controlling questions.

You find the reports under “Reports”. With “Create a new report” you choose the “Financial events” report. It contains payments from sales, refunds, fees and PPC. With “Schedule report” you have a report created regularly at set times. You set the period the report covers with “Offset from” and “Offset to”. As soon as the report is ready, you download it as a CSV file.

Each sales or cost item is on its own row. Three columns describe the type of item:

• Type 1 is the type of event, e.g. an order (“Shipment”) or a refund (“Refund”). Service fees (“ServiceFees”) and PPC costs (“PPCCosts”) come on top.
• Type 2 tells you whether the item is a credit (“charge”), a fee (“fee”) or a promotion (“promotion”).
• Type 3 names the exact type. Possible values include:
  • Commission (referral fee)
  • FBAPerUnitFulfillmentFee (FBA fulfillment fee per unit)
  • GiftWrap (gift wrap)
  • GiftwrapChargeback (gift wrap deduction)
  • GiftWrapTax (tax on gift wrap)
  • Goodwill (goodwill credit)
  • ppcCharges (PPC costs)
  • Principal (order sales)
  • RefundCommission (refunded referral fee)
  • ReturnShipping (return shipping costs)
  • ShippingCharge (shipping credit)
  • ShippingChargeback (shipping credit deduction, a pass-through item)
  • ShippingHB (shipping holdback)
  • ShippingTax (tax on shipping)
  • Tax (tax on order sales)

The values in “Type 3” are values of the AMALYTIX export. Many have the same name as the amount-description in the settlement report, but the lists are not identical. ppcCharges, for example, exists only in the AMALYTIX export.

The data is arranged on purpose so that you can analyze it in pivot tables later.

Import the data into Excel

Don’t open the file with a double-click. Excel then guesses the delimiter and the number format and often gets them wrong. Use the import for text and CSV files on the “Data” tab instead. Before Excel loads the data, check three settings:

1. Delimiter: tab for the settlement report (Flat File V2). For the AMALYTIX CSV file, the character set in your profile under “CSV delimiter” (default: comma).
2. File origin: UTF-8. Otherwise special characters show up wrong.
3. Decimal separator: comma for amounts in a format like “95,00”. For AMALYTIX files, you set it in your profile under “CSV decimal point”. If this setting is wrong, Excel treats the amounts as text and the pivot table cannot calculate.

A quick test: select a few cells in the amount column. If Excel shows a sum in the status bar at the bottom, the amounts arrived as numbers.

Analyze the data with pivot tables

For data in this shape, pivot tables are ideal. They let you group the rows in almost any way and build your own views in a few clicks. The following examples use the AMALYTIX export; the screenshots show the pivot table settings.

For an empty pivot table, click anywhere in the table, go to Insert and click PivotTable. Insert the pivot table as a new sheet.

Example 1: Sales per SKU per month

This shows the sales per SKU and month for the whole period. The settings:

• Filter “Type 1”: only “Shipment” and “Refund”. Sales are therefore reduced by refunds.
• Filter “Type 3”: only “Principal”, so that only order sales count.
• Subtotals are turned off, so that no subtotals per year appear.
• The number format for “Amount” is set to Currency (see below).
• The rows are sorted by total sales, largest to smallest. The SKU with the most sales is at the top.

To sort, right-click a value in the “Grand Total” column and choose “Sort” › “Sort Largest to Smallest”:

You change the number format of a value in the field settings:

With the settlement report it works the same way: instead of “Type 3”, filter amount-description = Principal, and put sku in the rows.

Example 2: Costs as a share of sales

In this example we compare the sales of two years. Next to them are the costs for referral fees (“Commission”), FBA fulfillment and PPC, each as a share of sales.

The settings:

• Filter “Type 3” on order sales (“Principal”), PPC costs (“ppcCharges”), referral fees (“Commission”) and FBA fulfillment fees (“FBAPerUnitFulfillmentFee”).
• Under “Values”, the field “Amount” appears twice: once as a sum and once as a percentage of sales. For the percentage, choose the option ”% of” under “Show Values As” (Excel for Mac: “Show data as”), with “Type 3” as the base field and “Principal” as the base item:

In the settlement report, use amount-description as the base field.

Example 3: Work with calculated fields

In a pivot table you can also calculate with columns. You do this with calculated fields. In the example we calculate the average sales per unit sold and country.

The settings:

• Filter “Type 1”: only “Shipment”, i.e. orders
• Filter “Type 2”: only “charge”, i.e. credits
• Filter “Type 3”: only “Principal”, i.e. sales

You insert the calculated field from this menu:

There you create a field “Average product sales” that divides the sales by the number of units sold:

Then drag this field into the values of the pivot table. In the settlement report, divide amount by quantity-purchased and filter amount-description on Principal.

Without the export: group Amazon numbers in Amazon BI

New month, same clicks: export the report, import the file, build the pivot table. Even for recurring questions such as “sales by brand and marketplace”, the manual work starts all over again.

Amazon BI takes the import off your hands. Every evening, AMALYTIX copies the data into the BI database, up to and including yesterday. There you group metrics such as sales, units or sessions by brand, category, marketplace or your own tags, without opening a file. If you do need the numbers in Excel, you export them as a CSV or Excel file.

The limits: our team enables BI access on request. Amazon BI is currently available only for accounts in the EU region of AMALYTIX, so it covers EU marketplaces only. You still decide which question to ask of the numbers. And for fees per single order, the settlement report remains the right source.

Conclusion

With the transaction or settlement report from Seller Central, or the “Financial events” report from AMALYTIX, and a pivot table, you answer the key questions about sales and costs yourself. The three examples show only a part of what is possible.

More analyses become possible when you combine the payment data with other AMALYTIX reports, e.g. the “Seller products (long)” report. It contains listing data including attributes. Via the SKU you assign this listing data to each payment and then aggregate the numbers.

If you want to automate even more, for example in PPC, read our article on Amazon bulk operations with Python and Pandas.

Frequently asked questions about analyzing Amazon data in Excel

What is the difference between the transaction report and the settlement report?

You create the transaction report for a date range of your choice, up to 365 days per report, as a CSV file in the Reports Repository. The settlement report covers exactly one settlement period and therefore matches one payout. For Excel, use the Flat File V2 format of the settlement report.

Why doesn’t the total match my payout?

A transaction report for a date range rarely matches a settlement period. Payments at the edge of the range end up in one payout or the other. If Amazon holds payments until after delivery, the transaction report also shows deferred amounts; the columns “Transaction Status” and “Transaction Release Date” show what has already been released. To reconcile a payout, use the settlement report.

Can vendors use these reports?

No. Transaction and settlement reports are Seller Central reports. Vendors find their sales data in Amazon Retail Analytics in Vendor Central.

Excel shows amounts as text or wrong. What can I do?

Import the file from the “Data” tab instead of opening it with a double-click. Check the delimiter there (tab for the settlement report, your “CSV delimiter” for AMALYTIX), the file origin UTF-8 and the decimal separator. Amounts in the format “95,00” need the comma as decimal separator.