Amazon MWS API to Google BigQuery – Made Easy

Posted By: administrator
Posted On: 17 May, 2020
Last Updated On: 18 May, 2020

Before we jump into what Amazon MWS is and how Amazon MWS APIs can streamline reporting and analysis for Amazon sellers, let us look at why Amazon is such an important channel for sellers and what challenges sellers can overcome if they have access to their Amazon Seller data.

E-commerce is a competitive space and coronavirus or COVID-19 is going to make E-commerce even more competitive as retailers that hitherto have not taken to e-commerce are now going to embark on a new journey to start selling online. There are many avenues for sellers to sell their products online. An easy way to get started with ECommerce and validating an idea is by selling on an established marketplace.

Marketplaces like Amazon simplify the selling process by providing the entire infrastructure needed for sellers. Once sellers gain traction and sellers can prove that their products have demand, they can then expand their investments into selling their products more aggressively within Amazon as well on other channels as well. This model of selling in a marketplace works for many businesses where creating a brand is not necessary, but the attributes of the product are sufficient to generate revenue.

For many sellers, marketplaces have become a necessity because an increasing number of people are buying from marketplaces is more than they buy from individual e-commerce websites. Amazon Seller Central is the seller portal to manage and sell on the global Amazon marketplace.

There are many popular marketplaces, but none are as comprehensive and as ubiquitous as Amazon.com. The growing popularity of Amazon.com is driven primarily by their prime membership program, arguably the most popular membership program in the history of membership programs has ensured that over 300 million people shop on Amazon.com and its subsidiaries annually.

Amazon.com is a self-contained ecosystem that provides sellers with all the tools that they need to market, sell, and fulfil their orders without using any other third-party tools. However, it is rare for any serious seller to rely only on Amazon.com or its subsidiaries as their sales channel. There are many reasons for not limiting oneself from selling on Amazon. None are more important than the fact that the risks of selling products in a single marketplace are so high that a change in rules of the marketplace or the company operating the marketplace deciding to sell a similar product might turn out to be a death knell for the seller’s business.

Market places also make it very different for brands to differentiate themselves by creating unique user experiences that make shopping a fun experience for customers. There are many combinations of channels that sellers use to promote and sell their products. They may choose to sell their products on their 

Complexity increases with the addition of every sales channel. Not only is this complexity limited to operations, technology, people, but one of the biggest challenges is to identify effective ways to market products in each sales channel. For instance, if we consider marketing channels available to support online business, you will find a choice of:

Choice, while being a great virtue, leads to complexity and this complexity when not managed properly, can, in turn, impact the efficiency of running an eCommerce business. Most eCommerce businesses grapple with this complexity; some well and many not so well. 

In a competitive digital landscape that we live in, it has become imperative that eCommerce businesses of all sizes that aspire to grow and stay profitable have to look into their data deeply and leverage this for growth. 

With the increase in competition, eCommerce Companies should strive to be more data-driven for various reasons. Some of these reasons include 

  • understanding the balance between demand and supply, 
  • finding opportunities to reduce wasteful spend,
  • Promote the right products
  • optimizing digital assets to maximize revenue for the same marketing spend, 
  • improving ROIs on Ad campaigns and 
  • offering an engaging and seamless experience for customers in every channel that the customer engages with the brand. 

Businesses these days need to be efficient in terms of their data analysis. They are struggling to make sense of the data generated from various applications and tools used to manage different processes efficiently. 

Due to the reasons highlighted above, any eCommerce business typically operates at least 10-15 different software/platforms to deliver on their customer expectations. As a result, data silos are created, which makes it more difficult to consolidate data and use the data for reporting, operations, analysis, and taking informed forward-looking decisions. 

For many sellers, Amazon.com happens to be the lifeblood of the business. In contrast, for many sellers, Amazon.com happens to be a channel to drive awareness about their products and to eventually leverage this awareness into traffic and revenue on their own branded site running on one of the platforms listed above. Regardless of which channel is seller decides to sell their products it is vital for them to track their business KPI’s daily, hourly if possible, to ensure that the business is profitably and operations are smooth. 

 Amazon Marketplace Overview  

Amazon Marketplace is the fastest growing online marketplace with an active customer base of 310 million. This user base and the sellers who are interested in selling to this user base are both growing in double digits every year. Amazon is also on an international expansion spree. Amazon’s global marketplace provides sellers with an excellent opportunity to sell to a worldwide market with relative ease.

However, fierce competition means that there is constant pressure on margins for sellers. Sellers need to do whatever is in their power to optimize outcomes from selling on Amazon.com and its subsidiaries. The best performing sellers on Amazon are the ones who take advantage of the data that is available on Amazon.com and use the data and insights for growth. Amazon provides numerous reports to manage the day-to-day operations of selling on Amazon marketplace. However, these reports fall woefully short when it comes to performing an in-depth analysis of the performance of the channel.

Google BigQuery Overview

BigQuery is a widely used cloud data warehouse used by both start-ups and Fortune 500 companies. A cloud data warehouse acts as the consolidated data store for data generated in the business. BigQuery is a fully managed cloud service that enables analysts to forget data infrastructure management and focus their effort on analysis and business value generation. BigQuery is a Petabyte scale data warehouse that is affordable for businesses large and small. You can read more about Google BigQuery here <give link>.

Why Do Businesses Need to Replicate Amazon Seller Central data to BigQuery?

Sellers on Amazon typically rely on analysts who perform daily downloads of reports from Amazon.com. A channel manager or analyst is generally in charge of implementing this activity. They usually leverage reports available on the platform portals to report on the performance of the channel to their executive management. However, as channels grow, the problems grow along with them. What happens when 

  • Products get sold in multiple Amazon marketplaces? 
  • Products get promoted in each of these marketplaces? 
  • There are other channels where sales are happening?
  • There is marketing spend on social media, podcasts, athletes, email marketing, others?
  • Sellers don’t use FBA, and you decide to ship products on your own?

Relying on manual reporting is a sure-shot way to ensure an under performing channel. We all have a limited amount of time every day to spend on managing, tracking, and improving business performance. The more time people spend on enhancing business performance, the more chance a business owner has to improve his company’s performance. If people have to log into multiple systems every day to

  1.  run reports, 
  2. wait for the reports to complete
  3. download those reports, 
  4. copy the data over to a spreadsheet, 
  5. run the Excel macros and then 
  6. discover what is happening in their business.

And repeat this activity for every channel and every country, then, it is fair to say that there is just way too much time spent on unproductive endeavours. Manual reporting leaves less time for critical resources to think and strategize on improving business performance. Freeing up an analyst time from manual reporting can leave time for many kinds of analysis that may not have been possible or may have been possible but less frequently to address questions like the ones below:

  • How do you determine profitability?
  • How do you determine the LTV of your customers?
  • How do you determine a plan for future based on data from previous years?
  • How do you determine the impact of promotions?
  • How do you determine trends in customer support requests?
  • How do you identify which products to promote in your Amazon marketing efforts? 
  • How do you determine which keywords to use in Amazon Ads?
  • How do you determine which products give you the best margin?
  • How do you determine profitability across products, countries, categories, and other dimensions?
  • How are shipping fees impacting profits?
  • How are commissions eating in to profitability?
  • How do you improve conversion rates?

Amazon recognizes this and has provided another way for businesses to get access to the data. It is called Amazon Marketplace WebServices (MWS). Amazon MWS offers a set of APIs that allow developers to extract data from Amazon.com and periodically and to submit reports on behalf of the seller by leveraging code. 

 What data is available using amazon marketplace web services? 

Amazon MWS APIs are available for data extraction in a few broad categories. They are 

  • Amazon Product APIs
  • Amazon Shipment APIs
  • Amazon Order APIs
  • Amazon Fulfillment APIs
  • Amazon Sales API

Replicating from Amazon MWS to BigQuery

There are a couple of options to gain control of your Amazon MWS data for in-depth analysis and to build comprehensive channel reporting. For both these options Amazon MWS APIs are required. A thorough understanding of business requirements and the functioning of the Amazon Marketplace APIs is essential before developing the code needed to pull data from Amazon. 

Build your custom data pipeline

To build support for extracting data using Amazon APIs, the developer or analyst will have to follow the steps.

  1. Register with the Amazon Developer Portal
  2. Create API tokens also known as MWS Auth Token to pull data from Amazon using Amazon MWS APIs
  3. Read and understand Amazon MWS API documentation thoroughly using the links above
  4. Handle Amazon Marketplace API rate limits to avoid getting blocked by Amazon or getting data slowly. Listed at the end of the article is a full list of all the rate limits of Amazon MWS APIs.
  5. Handle different data types used in the KPI’s for efficient data storage
  6. Schedule the Amazon MWS API calls frequently to pull new data generated in the platform. 
  7. Handle errors, changes and upgrades to the Amazon MWS APIs, which happens quite frequently
  8. Handle notifications so that you are alerted when a script has failed.
  9. Handle incremental data extraction and avoid full data extraction with every replication task

Below are the links to Amazon MWS APIs and reports that you may need to pull Amazon Seller Central reports and other Amazon MWS APIs:

List of API End PointsAPI Documentation PageLink to Rate Limits
ListFinancialEventGroupsAmazon MWS Developer GuideLink
ListFinancialEventsAmazon MWS Developer GuideLink
ListMarketplaceParticipationsAmazon MWS Developer GuideLink
ListOrdersAmazon MWS Developer GuideLink
ListRecommendationsAmazon MWS Developer GuideLink
GetFeedSubmissionListAmazon MWS Developer GuideLink
ListInventorySupplyAmazon MWS Developer GuideLink
ListAllFulfillmentOrdersAmazon MWS Developer GuideLink
ListRegisteredDestinationsAmazon MWS Developer GuideLink
ListSubscriptionsAmazon MWS Developer GuideLink
ListOrderItemsAmazon MWS Developer GuideLink
GetMatchingProductAmazon MWS Developer GuideLink
GetCompetitivePricingForSKUAmazon MWS Developer GuideLink
InventoryReportAmazon MWS ReportsLink 1 Link 2
AllListingsReportAmazon MWS ReportsLink 1 Link 2 Link 3
OpenListingsReportAmazon MWS ReportsLink 1 Link 2 Link 3
CanceledListingsReportAmazon MWS ReportsLink 1 Link 2 Link 3
SoldListingsReportAmazon MWS ReportsLink 1 Link 2 Link 3
ListingQualityandSuppressedListingReportAmazon MWS ReportsLink 1 Link 2 Link 3
GlobalExpansionOpportunitiesReportAmazon MWS ReportsLink 1 Link 2 Link 3
UnshippedOrdersReportAmazon MWS ReportsLink 1 Link 2 Link 3
OrderReportAmazon MWS ReportsLink 1 Link 2 Link 3
FlatFileOrdersByLastUpdateReportAmazon MWS ReportsLink 1 Link 2 Link 3
FlatFileReturnsReportbyReturnDateAmazon MWS ReportsLink 1 Link 2 Link 3
ReferralFeePreviewReportAmazon MWS ReportsLink 1 Link 2 Link 3
ActiveListingsReportAmazon MWS ReportsLink 1 Link 2 Link 3
InactiveListingsReportAmazon MWS ReportsLink 1 Link 2 Link 3
FlatFileFeedbackReportAmazon MWS ReportsLink 1 Link 2 Link 3
EasyShipPickedUpReportAmazon MWS ReportsLink 1 Link 2 Link 3
EasyShipWaitingforPickUpReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBAAmazonFulfilledShipmentsReportAmazon MWS ReportsLink 1 Link 2 Link 3
FlatFileAllOrdersReportbyLastUpdateAmazon MWS ReportsLink 1 Link 2 Link 3
FBACustomerShipmentSalesReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBAPromotionsReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBAAmazonFulfilledInventoryReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBAReservedInventoryReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBAInventoryHealthReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBAManageInventoryAmazon MWS ReportsLink 1 Link 2 Link 3
FBAManageInventoryArchivedAmazon MWS ReportsLink 1 Link 2 Link 3
RestockInventoryReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBAStrandedInventoryReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBABulkFixStrandedInventoryReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBAReturnsReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBARemovalOrderDetailReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBADailyInventoryHistoryReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBAReceivedInventoryReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBAInventoryEventDetailReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBAInventoryAdjustmentsReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBAInventoryAgeReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBAReplacementsReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBARecommendedRemovalReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBAMonthlyInventoryHistoryReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBAInboundPerformanceReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBAStorageFeesReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBAReimbursementsReportAmazon MWS ReportsLink 1 Link 2 Link 3
FBARemovalShipmentDetailReportAmazon MWS ReportsLink 1 Link 2 Link 3
FeePreviewReportAmazon MWS ReportsLink 1 Link 2 Link 3

Although you may not need all the reports, there are definitely a few important reports that when automated can provide a great deal of opportunity for brands to optimize their Amazon channel. All these reports have their own quirks and operate under rate limits that Amazon enforces. Developers who intend to pull data from Amazon marketplace using Amazon marketplace APIs (Amazon MWS APIs) are required to understand the above mentioned reports, their data structures, and rate limits. 

Once you have automated the extract of data using Amazon Marketplace APIs and you manage to save the data as a CSV or a JSON file, you can use the file to load Amazon MWS API data into Google BigQuery. You can leverage Google BigQuery loading routines to accomplish the task of loading data.

  1. Loading CSV data
  2. Loading JSON data

Use a cloud data pipeline

Building support for APIs is not only tedious but it is also extremely time-consuming, difficult, and expensive. Engaging analysts or developers in writing support for these APIs takes away their time from more revenue generating endeavors. Leveraging a cloud data pipeline like Daton significantly simplifies and accelerates the time it takes to build automated reporting. Daton supports automated extraction and loading of Amazon Ads data into cloud data warehouses like Google BigQuery, Snowflake, Amazon Redshift, and Oracle. 

Configuring data replication on Daton on only takes a minute and a few clicks. Analysts do not have to write any code or manage any infrastructure but yet can still get access to their Amazon ads data in a few hours. Any new data is generated is automatically replicated to the data warehouse without any manual intervention.

Daton – The Data Replication Superhero

Daton is a fully-managed, cloud data pipeline that seamlessly extracts relevant data from many data sources for consolidation into a data warehouse of your choice for more effective analysis. The best part analysts and developers can put Daton into action without the need to write any code.

Here are more reasons to explore Daton

  • Support for multiple data sources – Daton supports replication of data from 100+ data sources.
  • Low Effort & Low Maintenance – Daton automatically takes care of all the data replication processes and infrastructure once you sign up for a Daton account and configure the data sources. There is no infrastructure to manage or no code to write. 
  • Support for multiple data sources – In addition to Amazon Ads, Daton can extract data from a varied range of sources such as Sales and Marketing applications, Databases, Analytics platforms, Payment platforms and much more. Daton will ensure that you have a way to bring any data to BigQuery and generate relevant insights. 

Empower your analysts to do more with our data replication platform, Daton. If an analyst does not have access to a robust data warehousing infrastructure, they lose their valuable time on manual work that they could otherwise use to generate insights and work with the leadership to grow the business.

If you don’t have analysts on staff, our analysts and engineers can build a data warehouse for you and make data available at your fingertips when you need it at a fraction of the cost that you thought possible. 

Talk to us today about your reporting and analysis challenges. Our solution architects would love a good chat!

Build your Amazon Data Warehouse in just a few days!

Join the Conversation

  1. Avatar

1 Comment

  1. Hi,
    I want to start selling on Amazon/ other platforms online.
    I do not really computer literate.
    I’m just wondering if I, as a layman, can download and use your data effortlessly if I subscribe to you?
    Thanks.

Leave a comment

Your email address will not be published. Required fields are marked *

Sign up for a free trial of Daton today.

Take your analytics game to the next level

×
-