Introduction to Amazon return report data analysis
Amazon FBA returns data analysis examples
You can perform various types of analysis based on Amazon returns report data. We detail various examples and opportunities for performing data analysis of returns reports.
Our test dataset is relatively small, with just a few days of return data.
- The dataset contains 46,586 entries.
- There are 13 columns in the dataset: ‘return-date’, ‘order-id’, ‘sku’, ‘asin’, ‘fnsku’, ‘product-name’, ‘quantity’, ‘fulfillment-center-id’, ‘detailed-disposition’, ‘reason’, ‘status’, ‘license-plate-number’, and ‘customer-comments’.
- Most columns are of object (string) type, except ‘quantity’ which is an integer.
- The ‘customer-comments’ column has some missing values (NaN), with 13,013 non-null entries out of 46,586 total entries.
- All returned items have a quantity of 1, as indicated by the minimum, maximum, and average values of the ‘quantity’ column.
- The ‘sku’, ‘asin’, ‘fnsku’, and ‘product-name’ columns have several unique entries, indicating a wide variety of products in the dataset.
- The ‘return-date’ column has 28 unique entries, suggesting that the dataset spans a period of 28 days.
- The ‘fulfillment-center-id’ column has 170 unique entries, indicating that the dataset includes returns processed by 170 fulfillment centers.
- The ‘detailed-disposition’ column, which describes the condition of the returned items, has 5 unique entries.
- The ‘reason’ column, which contains the reason for return as indicated by the customer, has 26 unique entries.
- The ‘status’ column, which describes the status of the customer return, has 2 unique entries.
- The ‘license-plate-number’ column, which uniquely identifies specific items, has 46,538 unique entries, almost one for each entry in the dataset.
Below are some example SQL queries that could be used to explore the dataset.
Count of returned products by reason:
This can help you understand the primary reasons customers are returning products.
SELECT reason, COUNT(*) AS count FROM Returns GROUP BY reason ORDER BY count DESC;
Count of returned products by disposition (condition):
This can help you understand the condition of products when they are returned.
SELECT detailed_disposition AS disposition, COUNT(*) AS count FROM Returns GROUP BY detailed_disposition ORDER BY count DESC;
The total quantity of each product returned:
This can help you understand which products are returned the most frequently.
SELECT product_name, SUM(quantity) AS total_quantity FROM Returns GROUP BY product_name ORDER BY total_quantity DESC;
Number of returns processed by each fulfillment center:
This can help you understand if there are issues with specific fulfillment centers.
SELECT fulfillment_center_id AS fc, COUNT(*) AS count FROM Returns GROUP BY fulfillment_center_id ORDER BY count DESC;
Percentage of returned items that are sellable:
This can help you understand the financial impact of returns.
SELECT (COUNT(*) FILTER (WHERE detailed_disposition = 'SELLABLE') * 100.0 / COUNT(*)) AS sellable_percentage FROM Returns;
Average number of returned items per order:
This can help you understand if there are orders with multiple returned items.
SELECT AVG(quantity) AS average_returns FROM Returns;
Visualization Examples
Let’s start with the first one: Count returned products by reason. We’ll compute the counts and create a bar plot for visualization.
The bar plot above shows the count of returned products grouped by the reason for return. “APPAREL_STYLE” is the most common reason for returns, followed by “APPAREL_TOO_LARGE.”
Next, let’s examine the count of returned products by their disposition (condition)
The bar plot above shows the count of returned products by their condition (disposition). Most returned items appear in the “SELLABLE” condition, followed by “CUSTOMER_DAMAGED.”
Next, calculate the number of returns processed by each fulfillment center. Due to the potentially large number of unique centers, we will visualize the top 10 centers with the highest return counts.
The bar plot above shows the number of returns processed by each fulfillment center, focusing on the top 10 centers with the highest return counts.
Here is a word cloud based on customer comments;
Additional Analysis Opportunities
Here are additional examples of how you might visualize the data;
- Count of returned products by reason: A bar chart would work well here, with the reasons on the x-axis and the counts on the y-axis.
- Count of returned products by disposition (condition): Similarly, a bar chart would work well for this data.
- The total quantity of each product returned: If there are many different products, a horizontal bar chart or a pie chart is more effective in visualizing the products with the highest return quantities.
- Number of returns processed by each fulfillment center: A bar chart or pie chart could be used to visualize this data.
- Percentage of returned items that are sellable: This could be visualized using a pie chart showing the proportion of sellable vs. unsellable returns.
- Average number of returned items per order: A histogram could be useful here to see the distribution of the number of returned items per order.
Seamless, Code-free Rapid Retail API Automation Unleashed
Openbridge provides automated data feeds, without the need for code, to quickly deliver data directly to Amazon Redshift, Amazon Redshift Spectrum, Google BigQuery, Snowflake, Azure Data Lake, and Amazon Athena.
This fast and unified data access enabled teams to utilize their preferred analytical tools, such as Google Data Studio, Tableau, Microsoft Power BI, Looker, or Amazon Quicksight, for various purposes, including machine learning, business intelligence, data modeling, and online analytical processing.
Get Started Automating Amazon Returns Data for — For Free.
Ditch the messy, manual report downloads for code-free automation access to the new Amazon’s Return data. Openbridge integration is a code-free, fully-automated API integration. By providing Sellers with access to high-velocity data, they can accelerate the speed at which the team can gain valuable insights that deliver data-driven growth and profit.
