our blog chatter
.One of the best reports Amazon provides Sellers for understanding their business performance comes in the ‘Detail Page Sales and Traffic by Child Item’ report.
It is not without its limitations, namely that you cannot view your ASIN level data in time series format, but considering it includes fields such as Ordered Product Sales (OPS), Conversion Rates (Unit Session Percentage and Order Item Session Percentage), Buy Box Percentage and Sessions… there is no better report for diagnosing what is driving changes in your business performance.
In fact, this is the report we use in our ‘Bridge Analysis’ to calculate the ‘drivers of change’, which help us zero-in on growth opportunities for Brands and separate the ‘signal’ from the ‘noise’.
But one thing I could never reconcile was how Amazon was calculating OPS for this report. If you have ever tried to compare the total OPS for the ‘by ASIN’ Business Reports against the ‘by Date’ Business Reports, you may have seen the issue yourself. By Amazon’s definition, Ordered Product Sales is “calculated by multiplying the price of order items and the number of units sold for the selected time period.” But if you compare the ‘OPS per Unit’ against your Amazon listing price, more often than not you’ll see that those two don’t tie either (for example, OPS per Unit equals $25.55 but your Amazon listing price is $24.99, what gives?).
Having spent years working with Amazon data, their documentation, and the various APIs, I have come to expect a certain amount of squishiness and lack of precision from them as they attempt to accommodate for rapidly changing and evolving data points and definitions. When things don’t make sense, or the documentation seems out of date, my conciliatory refrain is “It’s Amazon.”
These last few months we have been integrating the Marketplace Web Services (MWS) API data for Seller Central accounts into our DATA STUDIO solution, and through the tedious process of data validation… I have finally gotten to the bottom of the question, “How do I reconcile Amazon Ordered Product Sales across the Business Reports”.
First let’s level set:
Where is the Detail Page Sales and Traffic Report?
You can find the ‘Detail Page Sales and Traffic by Child Item’ report by clicking “Reports” and then “Business Reports” from the main navigation bar at the top of the Seller Central dashboard. The report “Detail Page Sales and Traffic by Child Item” can then be found under the ‘By ASIN’ section of the left navigation menu of the Business Reports module.
Within this report, you can select from a suite of metrics including B2B specific (not shown here) and Amazon will deliver reporting metrics for your ASINs that had performance data over the time period selected.
Where Exactly is the Ordered Product Sale Discrepancy for Platform vs. Child ASIN Sales?
As OPS = SUM(Item Price * Units Ordered), if you take the ‘Ordered Product Sales’ for an ASIN and divide it by the ‘Units Ordered’, you’ll get OPS per Unit, which by definition should equate to ‘Item Price’. But checking ‘Item Price’ against your actual Amazon listing price, you’ll see that OPS per Unit (or ‘Item Price’) can be overstated, usually in the range of ~2-4 percent. Per the example mentioned above, your OPS per Unit will be something like $25.55 while your Amazon listing price is $24.99, a difference of $0.56 or a 2.2% difference.
So, either we have a data disconnect between Amazon’s reporting of ‘Units Ordered’ and ‘Ordered Product Sales’, or Amazon has incorrectly defined or labeled the field ‘Item Price’. Teaser, it’s the latter; Amazon has incorrectly defined ‘Item Price’ for this report.
How Do I Then Reconcile Child Item Sales to Total Platform Sales?
The best way to get a ‘true’ accounting of Ordered Product Sales, in total or by ASIN, is by leveraging MWS API data. It is also possible to perform a similar analysis by exporting the raw orders data out of Seller Central, but not one I’d recommend as you have to manually pull 30 days’ worth of data at a time...we’re all for streamlining that process, which is why we are working on providing MWS data via a database for our customers, no manual report pulling required.
For some time now we have been delivering streamlined access to Amazon Advertising Data via stand-alone databases. Leveraging the Advertising API data has brought many benefits including time saved pulling manual reports, easy connection to most reporting platforms, and scaling Amazon reporting for internal teams and external clients. Now that we have begun incorporating MWS data, we are seeing similar benefits… and new possibilities.
Among many other data elements, MWS delivers Amazon order information at an ASIN and day level. Each ASIN ordered is tied to an Amazon Order ID, with all of the customer fees broken out. Included in those fees are ‘Item Price’, which indeed matches the Amazon list price, but also included are: ‘Item Tax’, ‘Shipping Price’, ‘Shipping Tax’, ‘Gift Wrap Price’, ‘Gift Wrap Tax’, ‘Item Promotion Discount’, and ‘Ship Promotion Discount’.
As part of our data validation we mapped MWS data to the Business Reports to ensure we could replicate and automate these reports going forward. Through that process we discovered that ‘Item Price’ in the ‘Detail Page Sales and Traffic by Child Item’ report is actually calculated as ‘Item Price’ + ’Item Tax’, whereas the ‘Detail Page Sales and Traffic’ report calculates OPS using only ‘Item Price’. The ‘why’ on this I’ll explain as ‘It’s Amazon’… but it is probably a legacy definition from before they were collecting sales tax.
If you’ve ever been called upon to explain this discrepancy, you now have the most important disconnect. (I’ll save the other edge cases for another time.) But beyond this one use-case, there are other practical applications for tracking your MWS orders data in conjunction with your Business Reports. First and foremost, Promotional discounts are not being netted out of your OPS in the ‘Detail Page Sales and Traffic’ Business Reports. This can lead to surprises in your Amazon payments, as well as distort your conversion rates as price moves and price elasticity aren’t fully transparent in the business reports.
Why Should You Care About Capturing MWS Orders Data?
The Business Reports are still a valuable resource for your business and contain data currently unavailable from any other source; namely Sessions, Buy Box Percentage, and Conversion Rates. These reports do however have their limitations, and supplementing them with MWS data can help enrich your understanding of your business.
Beyond what has been noted above, with MWS data you can calculate the Lifetime Value of your customers across both Amazon fulfilled and Merchant fulfilled orders. This is not possible through the manual reports. You can also measure customer repurchase cycles and gauge how well you are selling across your line. You open the door to Geo-mapping your sales, which can inform your DSP targeting (Amazon’s programmatic advertising platform or Demand Side Platform) and help in evaluating other off-platform initiatives. And I’d be remiss if I didn’t mention, with MWS data you can efficiently calculate ‘Total ACoS’ or ‘TACoS’, and more importantly, through all the above, establish your ACoS threshold for customer acquisition initiatives.
All in, there is much to be gained by taking control of your MWS data, well beyond answering the irksome question of data consistency across reports.
The three questions we get asked by agencies, brands and/or retailers the most often are:
How can you easily get your Amazon data?
Getting your data out of Amazon and maintaining it is very difficult, and Amazon isn’t in a hurry to help you. For that reason, we have built DATA STUDIO. Firstly, it is a simple tool that helps you connect to your account(s). Once connected, your data is securely stored so you can review it using our views. Secondly, because we are maintaining and backing up your data in your own dedicated data warehouse, we provide easy access to it so you can link it to your own BI tool for custom reporting.
Is there an easier way for us to know how to improve our advertising performance?
Amazon doesn’t tell you where you could better allocate your advertising spend based on the success or failures of your current campaigns and keywords. Instead, you need to have some type of analytics tool to highlight where you should focus your attention so you can take immediately action. Our ADVERTISING STUDIO is setup for you to review, drill-down and action your amazon campaigns and keywords to improve your advertising performance.
How much can I spend on inventory?
Everyone listed on Amazon knows that not having inventory can impact your search placement. So, it’s important to manage your cash flow so you don’t run out. Our INVENTORY PLANNING software walks you through the process of managing your open-to-buy spend so you can better allocate your ordering budget based on inventory goals, sales targets, and planned receipts. After all, you don’t want a lack of inventory to negate all the work you’ve done to optimize your advertising performance.
Want to learn more...check out our video.