Amazon All Orders Report: A Comprehensive Guide
Mon, 02 Oct 2023 13:06:02 GMT
Amazon All Orders Reports Introduction to the Amazon Seller Central All Orders Report Amazon has long been at the forefront of e-commerce innovation, providing sellers with tools to enhance their sales strategy. Among the most valuable of these tools is the Amazon All Orders Report data. In this guide, we’ll delve into its intricacies, offering insights and practical applications for both novices and seasoned sellers.
Overview and Purpose The Amazon All Orders Report is a crucial tool for sellers, offering a wealth of information related to their sales activities. This report combines data on order status, sales channels, and item details, ensuring sellers have a holistic understanding of their business operations.
What can you do with this data?
Growth Pattern Analysis:
Study the report to identify consistent growth patterns. Are there specific times of the year where certain products outperform others? By understanding these patterns, you can adjust marketing efforts and promotions to capitalize on predictable sales surges. Inventory Optimization:
Analyze the report to understand which products are selling fast and which ones are lagging. Based on these insights, adjust your inventory levels. Holding the right stock ensures you can meet demand during high sales periods, while also reducing costs associated with overstocking slow-moving items. Leverage Advanced Analytics:
Integrate the All Orders Report with advanced analytical tools to derive deeper insights. Visual representations like trend lines, bar graphs, and pie charts can highlight sales channels’ performance, product preferences, and even regional sales trends. This can guide targeted marketing and sales strategies. Data Ownership and Backups:
Regularly export and back up your All Orders Report data. Owning your data ensures you can access historical information, even beyond Amazon’s retention period. This long-term data is invaluable for year-over-year growth comparisons and long-term strategic planning. Engage in Predictive Analysis:
Use the report’s data in conjunction with forecasting tools to predict future sales and inventory needs. By understanding what’s likely to come, you can pre-emptively adjust inventory levels, plan promotions, or even explore potential new product additions. Accessing the Report: Manual or Automated Two primary methods for accessing this report; Automated or manual downloads. Downloading occurs in your account interface. Automation taps into the Amazon Selling Partner API (Amazon SP-API ) to automate report processing and storage in a cloud warehouse or data lake.
Manual Access via Seller Central: Login: First, sellers need to log into their Amazon Seller Central account. Navigate to Reports: Usually found in the main navigation bar, this section contains various sales, inventory, and performance reports. Locate the ‘All Orders Report’: Within the Reports section, sellers can find and select the specific “All Orders Report”. Select Date Range: Amazon allows sellers to pull reports based on specific date ranges. Choose the desired range. Download: Once the report has been generated, there will typically be an option to download it. The report is often available in different formats like .csv or .txt. Import to BI Tool: If you’re using tools like Power BI, Tableau, or Looker, you can then manually upload this downloaded file to visualize and analyze the data. Note: Manual downloads are more time-consuming and may not be ideal for frequent and up-to-date data analytics.
Go Faster With Selling Partner API Report Automation The Amazon Selling Partner API (SP-API) allows for direct, automated access to Amazon seller data, making it easier for businesses to integrate this data into their systems.
Openbridge allows Amazon sellers to streamline the data management process by automating All Orders API integrations and storing data in a unified data warehouse. This service saves sellers time manually downloading reports and reduces errors in merging and tracking downloaded reports.
Automation eliminates manual download wrangling, ensuring up-to-date and accurate data straight from Amazon APIs. With data stored in a unified data warehouse like Amazon Redshift , Amazon Redshift Spectrum , Google BigQuery , Snowflake , Azure Data Lake , and Amazon Athena , you can combine different Amazon reports is easier to gain a more holistic view of the business.
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.
Types Of Reports The report’s data can be tailored based on two primary criteria:
By order date: This captures the data of orders placed within a designated timeframe. By last update: This includes data on orders that have undergone recent changes, such as updates or edits, within a specified period. Limitations: What’s Not Offered Before diving into the data, it’s worth noting that an online view of the All Orders Report isn’t available. This can be a drawback for those used to real-time analytics but ensures data privacy and accuracy.
Deciphering Field Definitions The table provides a detailed set of field definitions for Amazon’s “All Orders” data.
Identification Fields: This includes unique order IDs from both Amazon and the seller, signifying each transaction. Date Fields: There are fields indicating when the order was placed and its most recent update. Status and Channels: Information regarding the current status of orders, fulfillment methods, and channels through which orders were placed is provided. Product and Pricing: Product-related fields capture the name, unique identifiers (SKU and ASIN), and pricing details. This includes the base price, tax, shipping costs, promotions, and any associated gift wrapping charges. Shipping Details: Information on both the default and actual shipping addresses are provided in a detailed manner, capturing name, address lines, city, state, country, and postal codes. Taxation and Licensing: There’s data on the tax collection model and the Import One Stop Shop (IOSS) number which is crucial for sellers shipping to the EU. Promotions: The table also offers fields defining promotional discounts on items and shipping. Licensing: Some fields point towards professional licenses, suggesting that certain items might require buyer credentials (like medical equipment). This table is designed to capture a comprehensive view of any order placed on Amazon, from basic transactional details to shipping and promotions, ensuring sellers have a complete picture of their sales.
| Field Name | Description | Example |
| — — — — — — — — — — — — — — — — — — — -| — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -| — — — — — — — — — — — — — — — — — — — — — — — |
| amazon-order-id | Amazon’s unique, displayable identifier for an order | 058–3718414–0463502 or S01–3718414–0463502 |
| merchant order-id | A unique identifier optionally supplied for the order by the seller | my-order |
| purchase-date | The date the order was placed | 2003–07–14T18:53:56+ 00 :00 |
| last-updated-date | The date of the most recent order update | 2003–07–14T18:53:56+ 00 :00 |
| order-status | Current status of the customer order | Complete |
| fulfillment-channel | Indicates how the order was fulfilled, via Amazon (AFN) or Merchant (MFN) | AFN or MFN |
| sales-channel | Channel through which the order was purchased | Amazon.com |
| order-channel | The sub channel through which a sale was made for CBA/WBA orders | Phone |
| ship-service-level | An enumerated value that determines the type of fulfillment service that the buyer expects the seller to use | Standard |
| product-name | The short title for the product | Chocolate Truffles |
| sku | A seller-defined unique identifier for a product | HarryPotter |
| asin | Amazon inventory ID | B000WON1Z0 |
| item-status | Current status of this item within the order | Shipped |
| quantity | The number of this item that were purchased | 1 |
| currency | The currency used for the purchase | USD |
| item-price | The amount the buyer paid for the item | 15 |
| item-tax | The amount the buyer paid for item tax | 0 |
| shipping-price | The amount the buyer paid for shipping | 4.99 |
| shipping-tax | The amount the buyer paid for shipping tax | 0 |
| gift-wrap-price | The amount the buyer paid for gift wrap | 4.99 |
| gift-wrap-tax | The amount the buyer paid for gift wrap tax | 0 |
| item-promotion-discount | Total of all promotion discounts applied to the order item | -6.5 |
| ship-promotion-discount | Promotion discount applied to shipping | -4.25 |
| ship-city | The city of a standard address | Los Angeles |
| ship-state | The state or region of a standard address | California |
| ship-postal-code | The postal (zip) code of a standard address | 90039 |
| ship-country | ISO 3166 standard two-letter country code | US |
| promotion-ids | List of all item promotions applied to this order item | — |
| Licensee name | Name of the individual on the professional license | Jane Doe |
| License number | Professional license number provided by the customer | 12345 |
| License state | State where professional license number is valid | California |
| License expiration date | Professional license expiration date provided by the customer | 2017–08–20 |
| default-ship-from-address-name | The warehouse or business name used in the Seller Central account shipping settings or template | John Doe |
| default-ship-from-address field-1 | First line of “Default Ship From” address | 4270 Cedar Ave |
| default-ship-from-address-field-2 | Second line of “Default Ship From” address | Optional |
| default-ship-from-address-field-3 | Third line of “Default Ship From” address | Optional |
| default-ship-from-address-city | City of “Default Ship From” address | SUMNER PARK |
| default-ship-from-address-state | State of “Default Ship From” address | FL |
| default-ship-from-address-country | Country of “Default Ship From” address | US |
| default-ship-from-address-postal-code | ZIP code or postal code of “Default Ship From” address | 32091 |
| actual-ship-from-address-name | The warehouse or business name that is chosen while confirming the order | John Doe |
| actual-ship-from-address-1 | First line of “Actual Ship From” address | 4270 Cedar Ave |
| actual-ship-from-address-field-2 | Second line of “Actual Ship From” address | Optional |
| actual-ship-from-address-field-3 | Third line of “Actual Ship From” address | Optional |
| actual-ship-from-address-city | City of “Actual Ship From” address | SUMNER PARK |
| actual-ship-from-address-state | State of “Actual Ship From” address | FL |
| actual-ship-from-address-country | Country of “Actual Ship From” address | US |
| actual-ship-from-address-postal-code | ZIP code or postal code of “Actual Ship From” address | 32091 |
| tax-collection-model | Tax Collection Model or Tax Collection Responsible Party | MarketplaceFacilitator |
| IOSS Number | Import One Stop Shop (IOSS) number for the store | IMXXXYYYYYYZ | Critical Fields Explained The report boasts a comprehensive set of fields, each offering a unique piece of the puzzle. Fields like the amazon-order-id offer a unique identifier for an order, whereas the product-name provides insights into the specific products that are selling, for instance, “Chocolate Truffles”.
Practical Examples For a clearer understanding, consider a seller aiming to determine the popularity of “Chocolate Truffles.” By filtering data using the product-name field, they can quickly ascertain the product’s sales trajectory.
FBA vs. Seller-Fulfilled Orders Not all Amazon orders are made equal. The platform differentiates between orders fulfilled directly by Amazon (FBA) and those managed by the sellers themselves. This report captures data from both these streams, allowing sellers to track and measure demand variations and performance across both channels.
Utilizing the Report With fields like order-status and item-status, sellers can effectively monitor and manage order progress, ensuring timely deliveries and satisfied customers. For businesses operating on multiple channels, understanding where orders are coming from is essential. The fulfillment-channel and sales-channel fields provide this clarity, helping businesses refine their multi-channel strategies.
Here are a collection SQL queries based on the provided field definitions for the All Orders data:
Find total sales per product:
SELECT product-name, SUM(item-price) as total_sales
FROM orders
GROUP BY product-name
ORDER BY total_sales DESC; Determine the total number of orders shipped via each fulfillment channel:
SELECT fulfillment-channel, COUNT(amazon-order-id) as total_orders
FROM orders
GROUP BY fulfillment-channel; Identify the top 10 selling products based on quantity:
SELECT product-name, SUM(quantity) as total_sold
FROM orders
GROUP BY product-name
ORDER BY total_sold DESC
LIMIT 10; Calculate the average item price per sales channel:
SELECT sales-channel, AVG(item-price) as average_price
FROM orders
GROUP BY sales-channel; Find orders that have not been shipped (assuming statuses other than ‘Shipped’ imply not shipped):**
SELECT amazon-order-id, order-status
FROM orders
WHERE item-status <> ‘Shipped’;
Determine total shipping cost per city:
SELECT ship-city, SUM(shipping-price) as total_shipping_cost
FROM orders
GROUP BY ship-city
ORDER BY total_shipping_cost DESC; Find the top 5 states based on total sales
SELECT ship-state, SUM(item-price) as total_sales
FROM orders
GROUP BY ship-state
ORDER BY total_sales DESC
LIMIT 5; These queries can provide valuable insights into sales performance, product popularity, shipping costs, and more. They’re a great starting point for sellers looking to optimize their operations based on historical data.
What about Daily queries? Daily time series queries are particularly valuable for identifying short-term trends or patterns. Here are some daily time series SQL queries based on the “All Orders” data:
Total Sales per Day:
SELECT
DATE(purchase-date) as sales_date,
SUM(item-price) as total_sales
FROM orders
GROUP BY sales_date
ORDER BY sales_date; Daily Quantity Sold of a Specific Product (e.g., “Chocolate Truffles”):
SELECT
DATE(purchase-date) as sales_date,
SUM(quantity) as total_quantity_sold
FROM orders
WHERE product-name = ‘Chocolate Truffles’
GROUP BY sales_date
ORDER BY sales_date; Average Daily Shipping Costs:
SELECT
DATE(purchase-date) as sales_date,
AVG(shipping-price) as average_shipping_cost
FROM orders
GROUP BY sales_date
ORDER BY sales_date; Number of Orders per Day by Fulfillment Channel:
SELECT
DATE(purchase-date) as sales_date,
fulfillment-channel,
COUNT(amazon-order-id) as total_orders
FROM orders
GROUP BY sales_date, fulfillment-channel
ORDER BY sales_date, fulfillment-channel; Total Daily Sales for Each Sales Channel:
SELECT
DATE(purchase-date) as sales_date,
sales-channel,
SUM(item-price) as total_sales
FROM orders
GROUP BY sales_date, sales-channel
ORDER BY sales_date, sales-channel; Daily Tax Collection from Item Tax and Shipping Tax:
SELECT
DATE(purchase-date) as sales_date,
SUM(item-tax) + SUM(shipping-tax) as total_tax_collected
FROM orders
GROUP BY sales_date
ORDER BY sales_date; By analyzing the results of these daily time series queries, sellers can identify day-to-day fluctuations, determine the cause of any spikes or drops in sales or other metrics, and strategize to optimize operations and marketing activities.
Ensuring Customer Privacy Customer trust is paramount. That’s why the All Orders Report deliberately excludes any customer-specific details, ensuring privacy and compliance with data protection regulations.
Additional Resources Dive Deeper into Amazon’s Reporting Tools The All Orders Report is just the tip of the iceberg. Sellers eager to delve deeper can explore a plethora of tools and resources on Amazon Seller Central, amplifying their e-commerce success.
Understanding Amazon Order and Sales Reports | Openbridge Help Center Amazon Sales Connector | Openbridge Help Center Amazon Sales and Traffic Business Report | Openbridge Help Center Get Started Automating Amazon All Orders Data for — For Free. Ditch the messy, manual report downloads for code-free automation access to the new Amazon’s All Orders 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.
Sign up for a 30-day free trial of our Amazon API code-free automation . Frequently Asked Questions (FAQs) What is the primary purpose of the All Orders Report? It offers sellers a detailed insight into their sales activities, encompassing both FBA and self-fulfilled orders.
Is there an online view available for this report? No, currently Amazon does not offer an online view for the All Orders Report.
Does the report contain any customer-specific information? To ensure utmost data privacy, the report does not include any customer-identifying information.
How can the report aid in inventory management? By analyzing sales trends and order statuses, sellers can optimize their inventory, reducing stockouts and overstock scenarios.
Are there any future enhancements expected for this report? Given Amazon’s commitment to seller success, we can expect regular updates and enhancements to the report in the future.
Amazon All Orders Report: A Comprehensive Guide was originally published in Openbridge on Medium, where people are continuing the conversation by highlighting and responding to this story.
