Skip to main content
Sam Hager
from the dash team
Asked a question 8 months ago

What is the correct revenue field to report sales for MWS? Should I use Amount, ItemPrice, or another field? How should I calculate revenue using the MWS data? Does the MWS reporting tie to the Business Reports data for Ordered Product Sales (OPS)?

have a question?  ask it now and either we will answer it or one of you fellow community members.

You may be wondering, what revenue does the MWS data report? It doesn't seem to tie to the Business Reports OPS data. You're right, it doesn't. MWS data is derived from the Orders data seen in Seller Central under Reports > Fulfillment > All Orders. Orders data and MWS data report a few fields related to Revenue for Seller Central, in particular Amount (seen in the mws_orders_list table) and ItemPrice (seen in our mws_orders_metric table). We also added an 'Amount' field to our mws_orders_metric table in order to show how the list data relates to the metric data noting that the Amount value is variable in the EU region marketplaces due to how taxes and shipping are reconciled.

Generally speaking, we recommend reporting Revenue as ItemPrice - ItemPromotionDiscount from the mws_orders_metric table. This calculation provides the closest approximation of revenue and cashflow to the seller given that taxes, giftwrapping, and shipping are often netted out by Amazon before payments are disbursed to the seller.

It is our recommendation to treat the MWS Data (and ALL ORDERS report) as the ‘source of truth’ with your clients as you now have the granular status data at your fingertips. The MWS data will not tie directly to the Business Reports data due to the reasons described below in this post, namely the timing of the sales reporting and values used to construct OPS.

For posterity, here is how the values seen in mws_orders_list and mws_orders_metric relate:

Amount = ItemPrice - ItemPromotionDiscount + ItemTax + ShippingPrice + ShippingTax - ShipPromotionDiscount + GiftWrapPrice + GiftWrapTax.

And generally ItemPrice = OPS for the Detail Page Sales and Traffic Business Report

While ItemPrice + ItemTax = OPS for the Detail Page Sales and Traffic by Child Item Business Report

Now for the details (read on for how to reconcile Business Reports data to your MWS data):

Your MWS data will largely replicate the ‘ALL ORDERS’ report available within Seller Central (Reports > Fulfillment > All Orders):

  • If you have worked with this data before, you may be familiar with some of its peculiarities, most notably:
    • All MWS Date fields (e.g. ‘PurchaseDate’) are reported using a UTC timestamp (Coordinated Universal Time).
      • To convert the Date Fields to match what is reported in Seller Central (for the US Marketplace), you will need to subtract 8 hours to convert to Pacific Standard Time (PST), and subtract 7 hours to support Pacific Daylight Savings Time (PDT).
      • For other Marketplaces, similar date conversions will be required, but we have yet to map every marketplace as we do not have Seller Central Access to all marketplaces.  (We would love your feedback here!)
  • For mws_orders_metric, Amazon duplicates the ‘AmazonOrderID’ for instances where customers have ordered multiple ASINs within the same Order
    • Amazon will not duplicate the ‘AmazonOrderID’ for instances where the customers has ordered Multiple units of the same ASIN in a single Order
  • The MWS Data (and ‘ALL ORDERS’ report) returns data in its raw form, with Amazon performing translations on this data to deliver the ‘Business Reports’ found in Seller Central.
    • These translations are not always transparent and cannot be fully replicated with the available data, but in our testing the error rate has been marginal (<1%)
    • It is our recommendation to treat the MWS Data (and ALL ORDERS report) as the ‘source of truth’ with your clients as you now have the granular status data at your fingertips.
  • If you plan to replicate the Business Reports using the MWS data (noting there will be disconnects with what Seller Central reports), we recommend the following:
    • In all instances, be sure ‘SalesChannel’ is filtered to “Amazon.com19” (or the 'default' sales channel of the marketplace such as "Amazon.co.uk27" or "Amazon.de16" seen in the marketplace table of your db) to ensure you are evaluating the same data set that is found through Seller Central
      • When the ‘Sales Channel’ returns “Non-Amazon” we believe this is reserved for Orders placed through other channels that are leveraging “Check-out with Amazon” (i.e. Amazon Pay)
    • We recommend filtering ‘OrderStatus’ and ‘ItemStatus’ fields to “Shipped” to return the most relevant (and accurate) data set
    • Amazon has a definition disconnect with ‘Ordered Product Sales’ in their Business Reports
      • Ordered Product Sales in the ‘By Date’ (Detail Page Sales and Traffic) is calculated using ‘ItemPrice’ without netting out 'ItemPromotionDiscount'
      • Ordered Product Sales in the ‘By Child Item’ (Detail Page Sales and Traffic by Child Item) is calculated using ‘ItemPrice’ + ‘ItemTax’ without netting out 'ItemPromotionDiscount'
        • The ‘By Item’ (ASIN) reports will at times include Cancelled Orders in their Units Ordered field
          • We have not been able to determine a consistent signal for when these cancelled units are being included, but believe a subset of the ‘quantity’ is counted where OrderStatus=Cancelled and ItemStatus=UnShipped
  • Free Replacements:
    • For instances where ‘Free Replacements’ have been given to customers, the original Order will return ‘ItemQuantity’ and ‘ItemPrice’, whereas the Replacement Order will return ‘ItemQuantity’ but no ‘ItemPrice’

You will see we have added additional fields in your MWS data as compared to the ORDERS REPORT.

  • In addition to fields such as Seller ID and Marketplace ID to fields to support reporting across your merchants, we have also incorporated the ‘hashed’ ‘Buyer Email’ for both ‘Amazon Fulfilled’ and ‘Merchant Fulfilled’ Orders
    • The ‘hashed’ email, though anonymized, is unique to the customer and enables you to calculate Customer Lifetime Value among many other valuable metrics for your clients
      • This is not possible through Seller Central as ‘Buyer Email’ is available only for ‘Amazon Fulfilled Shipments’
      • If you find instances where the ‘Buyer Email’ field is blank, these can be attributed to “Gift Orders” as Amazon will not populate Emails for this Order Type