Cover Image
Trutz Fries

Amazon Excel Data Analysis: Master Transaction Reports

08/24/2020 • Reading time: ca. 6 min • by Trutz Fries
  1. How to Export Amazon Transaction Data from AMALYTIX
  2. Importing Amazon Data into Excel for Analysis
  3. Creating Powerful Excel Analytics for Amazon Data
    1. Example 1: Analyzing Amazon Sales History by SKU and Month
    2. Example 2: Amazon Cost Analysis - Fees vs. Revenue Comparison
    3. Example 3: Advanced Amazon Analytics with Calculated Fields
  4. Conclusion

Amazon Excel Data Analysis empowers vendors and sellers to unlock powerful insights from their transaction data. By exporting Amazon's comprehensive financial reports and leveraging Excel's pivot table capabilities, you can analyze sales performance, track costs, and identify optimization opportunities that aren't visible in standard Amazon reports. This guide walks you through the complete process of extracting, importing, and analyzing your Amazon data.

We will proceed with this in several steps:

  1. Export the data from AMALYTIX
  2. Import the data into Excel
  3. Evaluation of the data in Excel

How to Export Amazon Transaction Data from AMALYTIX

In AMALYTIX, you can export almost all data that the tool collects for you as a CSV file. This allows individual analysis that are not shown in the tool. These can be customer-specific questions, for example.

The reports in AMALYTIX can be found under "Reports" (see mark 1 on figure 1).

AMALYTIX reports dashboard for Amazon Excel data analysis and export
Figure 1: Amalytix Reports

Select the report and then click on "Create".

Creating Amazon financial transaction reports in AMALYTIX for Excel export
Figure 2: Create a report in Amalytix

The report is now being generated. You can check the progress of the report generation by clicking the "Refresh list" button.

Once the report has been created, you can download it by clicking on "Download".

Each individual sales or cost item is listed in this file. The columns indicate the following:

  • "Type 1" indicates whether the item is a purchase order ("Shipment"), a return ("Refund") or an item from PPC sales
  • "Type 2" indicates whether it is a charge or a fee.
  • "Type 3" indicates the exact type. Some of the possible values are as follows:
    • Commission
    • FBAPerUnitFulfillmentFee
    • GiftWrap
    • GiftwrapChargeback
    • GiftWrapTax
    • Goodwill
    • ppcCharges
    • Principal
    • RefundCommission
    • ReturnShipping
    • ShippingCharge
    • ShippingChargeback
    • ShippingHB
    • ShippingTax
    • Tax

The remaining columns are self-explanatory.

The data is intentionally arranged in such a way that it can later be ideally evaluated in pivot tables.

Importing Amazon Data into Excel for Analysis

To import the data into Excel, we recommend the import dialog "Data - From text files". To do this, go to the "Data" tab and then click on "From text".

Excel data import dialog for Amazon transaction data analysis
Figure 3: Import data into Excel

In the following menu, you must select the comma as the separator. Then you can click on "Load", as no further settings are necessary.

Excel import settings with comma separator for Amazon CSV data files
Figure 4: Select comma as a separator to load data in Excel

If everything went well, it should now look similar to this in Excel:

Amazon transaction data imported into Excel showing financial events and categories
Figure 5: Example Excel Table

Now you can get started with the evaluation.

Creating Powerful Excel Analytics for Amazon Data

To analyze such structured data in Excel, you can use Pivot tables. Pivot tables allow you to group the data in almost any way you like and thus create specific views.

In the following, I would like to introduce you to some of the possibilities. To understand how the pivot table was built, I will illustrate the settings of the pivot tables.

To create an empty pivot table, use the mouse to click somewhere in the table and then go to Insert and there click on "Pivot Table".

Creating pivot tables in Excel for Amazon data analysis and reporting
Figure 6: Insert Pivot Table in Excel

Insert the pivot table as a blank sheet.

Example 1: Analyzing Amazon Sales History by SKU and Month

Amazon SKU sales analysis pivot table showing monthly revenue trends
Figure 7: Sales history for each SKU per month

Here the turnover per SKU and month is displayed for the entire period.

The following settings have also been made:

  • Filter "Type 1": Only "Revenues" and "Refund" were selected. The revenues are thus reduced by the returns.
  • Filter "Type 3": Only "Principal" has been selected to only consider order sales.
  • The "Partial results" were deactivated to not receive any intermediate results per year.
  • The number format for "Amount" has been set to "Currency" (see below).
  • The rows have been sorted by total sales in descending order so that the SKU with the most sales is at the top.

To sort, right-click on an element in the " Overall Result" column and go to "Sort / Sort by Size (Descending)":

Excel pivot table sorting Amazon SKUs by total sales in descending order
Figure 8: Sort SKU descending

You can change the number format for a value in the "Field settings":

Excel field settings for formatting Amazon revenue data as currency
Figure 9: Format numbers in Excel Table

Example 2: Amazon Cost Analysis - Fees vs. Revenue Comparison

In this example, we show the revenue for 2018 vs. 2019 and compare the costs for vendor fees ("Commission"), for FBA shipping and for PPC costs in relation to the revenue. This is also done with a few clicks without any further action.

Amazon cost analysis showing FBA fees, commissions, and PPC costs as percentage of revenue
Figure 10: Displaying costs in relation to sales

The following parameters are set here:

  • Filter "Type 3" on order turnover ("Principal"), PPC costs ("ppcCharges"), vendor fees ("Commission") and FBA shipping fees ("FBAperUnitFulfillmentFee").
  • Under Values, the field "Amount" was created twice. Once normally as a total and once as a percentage of sales (Type 3 / Principal). The latter was created as follows:
Excel pivot table value field settings for Amazon cost percentage calculations
Figure 11: Pivot Table field with the setting Type 3 & Principal

Example 3: Advanced Amazon Analytics with Calculated Fields

Within the pivot table, you can also relate columns to each other in any way you like. You can do this using so-called "calculated fields" as the following simple example shows.

In the following we would like to calculate the average sales per product per country.

Amazon average sales per product analysis using Excel calculated fields by marketplace
Figure 12: Average sales per product per country with calculated fields

The following settings were defined here:

  • Filter "Type 1": Only "Shipments" i.e. orders
  • Filter "Type 2": Only "charges" i.e. credit notes
  • Filter "Type 3": Only "Principal" i.e. sales

We have inserted the "calculated field" via this menu:

Excel pivot table menu for creating calculated fields for Amazon data analysis
Figure 13: Create calculated fields in Excel

We then defined a field called "Average product sales", where the sales are divided by the number of units ordered.

Excel calculated field formula for Amazon average product sales analysis
Figure 14: Calculate fields

We then added this field to the pivot table.

Conclusion

With the help of the "Payments" report, many more evaluations can be carried out. The examples given are only intended to give you a feeling for what is possible.

Further evaluations become possible if you enrich the "Payments" report with additional reports from AMALYTIX, e.g. with data from the "Product Catalog" report. Here you can then also assign the brand, parent ASIN, category and much more to each SKU and add aggregations on this data.

You have created an interesting report? Send us a (anonymized) screenshot of your analysis. We will be happy to add further practical examples to the article.

In AMALYTIX, we also map standard evaluations such as period comparisons for sellers in our Performance Module. You can compare any periods and key figures.

AMALYTIX Performance dashboard showing Amazon period comparisons and key metrics
Figure 15: Amalytix Dashboard

Using the item Aggregation, the values at the level of

  • Child ASIN
  • Parent ASIN
  • Category
  • Brand

can be compared.

Free trial

Just register for a 14-day free trial and we will show you how our Amazon Seller Tool and Amazon Vendor Tool can help you monitor your marketing activities on a daily basis. Start your free trial now

Would you like to have a better overview on Amazon?
Monitor your listings 14 days for free!
Do you have any questions? Don't hesitate to call us or send us an email!
Tel. +49 221-29 19 12 32 | info@amalytix.com