How are you storing and organizing Amazon MWS data in my target data destination?

This doc details the source tables and views that are created. We always suggest using the views for analytics as they are the parents and the source tables are children.

Note: If you need to, you can export a schema for any views or tables in your data destination.

I'm not seeing any data for Orders? Why?

Amazon limits how frequently certain data can be requested. If you have any other process using this report, it will prevent us from collecting it. Here is what Amazon says:

  • "You can only schedule one _GET_FLAT_FILE_ORDERS_DATA_ or _GET_CONVERGED_FLAT_FILE_ORDER_REPORT_DATA_ report at a time. If you have one of these reports scheduled and you schedule a new report, the existing report will be canceled."

Given this limitation, this often explains why mws_orders_master might not data present because there is another process requesting the report. As a result, this will prevent us from collecting this report since you can execute this process once a day and there is another system consuming that one request period.

How long does it take for initial data sync to occur after I activate an MWS data pipeline?

After you activate a data pipeline subscription, the initial sync can take 24-48 hours.

What are the differences between the different order, returns, FBA...feeds?

In terms of what collection of data each feed reflects, we suggest looking at the Amazon docs. Amazon provides the most current description of these data feeds:

For example, here is what Amazon says different order feeds:

  • orders by last update is "all orders updated in the specified period"

  • orders is "all orders that were placed in the specified period"

orders by last update may have an older order that had some update applied to it.

This means an order from last month may show up on recent orders by last update feeds because it had an update.

orders are just orders specific to the date an order was placed.

When are Settlement Reports available? Can I get them daily?

Settlement Reports are scheduled directly by Amazon. Openbridge has no control over report availability.

Amazon will typically be available approximately every 15 days, though there are cases the settlement period closes approximately every 30 days. Once Amazon publishes the report our system will pick it up.

For your seller account to be settled, it must have had activity during the settlement period and either a positive or zero account balance. Referral fees and refunds can affect your balance. When your account is settled, Amazon will generate a report for the settlement period is posted on the "All Statements".

You may want to confirm with Amazon the schedule dates for your reports. If you log into Seller Central account REPORTS -> PAYMENTS -> ALL STATEMENTS it will give you a sense of the timing of your account. The most recent will likely say it is still OPEN which means no reporting data is available.

Do you have published schemas for the Amazon MWS data?

Detailed table schemas are defined by Amazon. If we detect a change we version the source tables and views that we store in your data destination. Asa result, we suggest checking the Amazon documentation for the latest information on the data feeds.

Amazon "generally" keeps its schemas up to date:

What data is included in MWS Real-time data feeds? How is real-time order data different from reporting order data?

The MWS Real-time data connector syncs transactional data from Amazon to a target destination. Per Amazon;

The Orders API is optimized for data synchronization, retrieving order updates in real-time, and order research. For bulk order data reporting, you should create order reports with the Reports API section. For more information about the Reports API section, see the Reports API section documentation.

As a result, there is no historical data. For comprehensive reporting data, see our MWS Advanced or MWS Basic Reporting Connectors.

The following are typical uses cases for real-time order data:

  • Synchronize Amazon order data with order data in your local ordering system

  • Get order status data on your Amazon sales so you can fulfill your orders using your own fulfillment system

  • Migrate your order data to a private data destination

  • Get order details for researching issues and answering customer questions

  • Find SKUs with high or low sales volumes

  • Monitor sales trends by SKU

  • Support for sales channel and fulfillment channel decisions:

  • Find SKU sales trends by sales channel and by fulfillment channel

  • Find SKUs that have been in a given state for too long

  • Find unexpected changes in sales trends by SKU

Once activated, two primary data feeds will be synced:

mwsrt_order_items

An item and a quantity. For example, if a customer buys three copies of a particular book, the order contains one order item with a quantity of three. If one of the copies is to be gift wrapped, the order now contains the following two order items:

  • Gift wrapped book, quantity of one

  • Non-gift wrapped book, quantity of two

mwsrt_orders

The following are both considered orders:

  • A collection of order items sold on Amazon with identical values for the following attributes: Shipping addressShipping speedSeller

  • A Multi-Channel Fulfillment order (an order not sold on Amazon's retail website but fulfilled using your Fulfillment by Amazon inventory).

How do I JOIN data across tables and views?

You have a number of options for JOIN though each will depend on your query objective. In some cases, it might be ASIN or SKU if you are trying to understand product-level transactions or trends. It might be a DATE if you are trying to explore behaviors within a specific time period.

Ultimately, your query would be a function of the types of questions you are asking of the data. If you are looking for help with modeling the data to optimize for analytic efforts, reach out to our professional services team.

Do you sync all the account history?

No, currently we do not sync all history. Largely, this is due to limits within the Amazon API on the number of times their service can be called. We sync a limited subset of data feeds for thirteen months of historical data.

If you want to sync more than the standard window of historical data for a given Seller account, this process requires customized API requests and backfill data flows. Given the complexity of configuration, monitoring, and testing long-haul backfill data flows additional charges apply for this process. Contact Openbriodge support for more details.

Note: Historical syncs are not available during a trial period.

How long does it take to generate historical data for MWS?

For MWS API data feeds, there are seven feeds that sync approximately thirteen months of historical data.

  1. mws_orders_by_order_date

  2. mws_fba_returns

  3. mws_fba_reimbursements

  4. mws_all_listings

  5. mws_fba_customer_shipment

  6. mws_returns

Note: Amazon recently made a change to the frequency the Orders By Last Update report can be requested. Unfortunately, as a result of this change historical data can no longer be collected. See the "In-progress" error Amazon responds with for this report here.

  1. mws_orders_by_last_update

Amazon places significant limits on the MWS API which is 60 API calls an hour. Completing a typical historical run (1 year) can require over 10,000 API requests. Given the limits, we can generate one day of historical data per hour for all seven feeds. As a result, this equals about 390 hours of processing.

Also, we have to pull daily, go-forward data feeds in addition to historical backfill. There can be up to 30+ daily data feeds, depending on the marketplace, that leverage the same Amazon API limits.

As a result, collecting the historical data listed above can take 16-24 business days depending on API availability, successful responses to all our requests, retries for failures, and so forth.

Can you make the MWS historical backfill processing go faster?

The cadence and timing are set by Amazon API limits, not Openbridge. The Openbridge system is optimized to work within those limits, maximizing available Amazon API capacity. For example, to regenerate close to over 2,700 reports (7 reports, by day, for 390 days), we have to carefully sequence requests to the MWS API to avoid Amazon system throttling, which would slow any requests and responses by Amazon.

How frequently are you getting data from Amazon MWS Reporting?

After activating MWS Advanced or Basic reporting, it can take up to 24-48 hrs for the first sync to occur. Afterward, we are pulling MWS reporting data once a day for a "settled" day. A settled day means a full 24 hour period according to Amazon system guidelines (see UTC note below). This is largely a function of when data is available and the limits Amazon has on how frequently calls to their API can occur. Given we are pulling a broad cross-section of reporting data, making those calls more frequently would cause Amazon to throttle our connection to your account.

On any given day, collecting reporting can take 2-6 hours, sometimes longer, depending on Amazon systems.

NOTE: The MWS API uses UTC while Amazon internally reconciles data in PST. This can create temporary shifts in data availability. Normally this self-corrects itself within 24-48 hours. For example, there may be a shift due to time zones on Jan 3. However, on Jan 4 or Jan 5, the data for Jan 3 would be "settled". This means any gaps due to timezone variations are reconciled. See https://docs.openbridge.com/en/articles/5053618-understanding-timezones-within-amazon-seller-central-and-amazon-advertising

How frequently does Amazon update data within its MWS system?

Based on our testing, Amazon does not consistently have data available when we call the MWS API. For example, we may call the API for export "X". Amazon will respond to our request saying there is no data available for "X". We will attempt a series of retries over a number of hours, within API limits, to retrieve "X". As a result, there may be some inconsistency in how Amazon is making data available which limits our ability to collect it on your behalf.

This is why our schedule calls to the reporting API for the prior day's data. For example, on January 2, we will ask Amazon for the January 1 data. If we attempted to call the reporting API on January 2 for January 2 data, the response from Amazon is often empty, partial, or incomplete. As a best practice, calling for the prior day delivers the most consistent and accurate data.

If you need more current data, we do offer an MWS real-time orders data feed. This process occurs hourly and syncs orders as they occur in Amazon to your target data destination.

There is no data in various tables on certain days. Why?

In some cases, Amazon will not return any data because there is no data. For example, if there are no customer returns, Amazon will not return any data for the customer returns data feed. Likewise, if all your listings are inactive, your active listings feed would not contain any data.

Another cause may be some system outages, API errors, or updates at Amazon which can cause delays. In the event of errors like this, we queue our requests for data until we know Amazon can process them.

How do you download the report which contains Amazon Sales Rank? Is there a report that shows sales rank?

Yes, within our current Amazon MWS Advanced Reporting data feeds, there is something called mws_fba_inventory_health. Within this data feed, there is a column called sales_rank. The data feed will contain the sales rank for a product as provided by Amazon.

Which Amazon MWS and Advertising API integrations do you support?

The Openbridge Amazon MWS application offers ready-to-go, automated, and code-free connectors to your data.

  • Amazon MWS Advanced Reporting — Our MWS API supports delivering a complete view of your seller operations. Over 50 data feeds for listings, inventory, fees, orders, FBA, returns, and many others

  • Amazon MWS Basic Reporting — Take control of your data by building a Seller reporting and analytics foundation. The MWS Basic Reporting API connector delivers a core set 7 data feeds

  • Amazon MWS Real-time Orders — Hourly feeds of MWS order data for use in your local ordering system, researching issues, answering customer questions, and using your fulfillment system (FBM)

  • Amazon MWS Settlements — Every two weeks, per Amazon schedules, settlement reports provide a detailed breakdown of account activity for a given settlement period

  • Amazon MWS Warehouse — Hourly Inbound fulfillment shipment data for your inventory sent to Amazon's fulfillment network

Learn more here: https://www.openbridge.com/amazon-mws

Did this answer your question?