Using Data for Product and Order Management
by Bharti Dalal - 6 years ago
Amazon MWS API empowers end users to track seller account activity. The data can be used for order management, inventory management, and report management. The reports data can be utilized to prepare real-time reports including Browse Tree Reports, FBA Reports, Order tracking, performance and settlement reports etc.
Alteryx users face several challenges while preparing reports for Amazon Marketplace data and find it hard to connect the Amazon data into Alteryx. The good news is that the data is now available in Alteryx via Grazitti’s Amazon connector.
Any Amazon seller can login with simple account API credentials in Alteryx to get the desired data. The connector is available for free trial for all Alteryx users at https://alteryxconnectors.grazitti.com/.
Order and Sale data from Amazon
The order and product data from Amazon is very important to track orders for Amazon seller users. The data allows Amazon sellers to track the order, streamline the shipping data, cost, and revenue.
The order amount, purchase data, discount, shipping cost, expected delivery data, and order tax are important metrics for every seller. These metrics help Amazon users understand buyer behaviors, sales over time, and the impact of discounts on the sales over a time period.
The example here is of fetching product sales data with the help of the Amazon Seller connector, and then combining it with order data to make it usable for preparing analytics reports.
The Amazon MWS Connector by Grazitti helps users connect Alteryx to get data objects such as, Order, Product, Reports, and Sellers. The first step is to get all the orders data from the connector with the help of the Order object. The connector also gives a time filter to allow the user to choose the timeframe for which they wish get the data for.
Exhibit 1.11 shows the Order object data for List order downloaded for a timeframe of 30th Oct 2017 to 31st Oct 2017. This data object is being fetched via API and it includes all the important data fields related to order including: Order ID, Buyer details, Order amount, delivery date, No. of Items in the ordered/shipped, Order Status and Payment details etc.
Amazon Connector Order Object, TimeStamp
Amazon Connector Order Object, ListOrder
Some of these are shown in Exhibit 1.12, the fields retained the correct data type and are ready to use for any calculations in Alteryx. The order price, discount, shipping cost, and tax amount data fields are also available that can help analysts do detailed analytics on the amount they are getting in a timeframe. The data can be used to prepare various logical calculation e.g. impact of discounts on sales or the number of products sold to identify the best time for offers/discounts. Analysts can also keep an eye on sale variations.
The data has most of the needed data fields from this object apart from ASIN, which is one of the most important data fields to join Order data with data from other objects like product, sellers, and reports. To get ASIN for each order ID, a macro is created that pushes the AmazonOrderID(from ListOrders) into the Connector (Orders\ListOrderItems) to ASIN. The macro gets the Orders for any given AmazonOrderId. The final data includes ASIN as well as the AmazonOrderID, appended with the help of a text input tool. See details in Exhibit 1.13
The data is useful because it can be prepared with any product, seller, and shipment data with the help of ASIN to get the desired metrics. The data can also be filtered for ASIN and then see the order/sale trend for specific product category or product based on ASIN.
Amazon Connector Order Object, MWS Operations
Product data from Amazon
Another challenge was to pull data for the product based on ASIN. The connector gives product data, based on a search query (input) by users. It also gets data specific to the product based on ASIN and requires the user to provide ASIN as input.
To get the data for a list of keywords (ASIN), a macro is prepared with an input of a list of ASIN. The data collected from the Orders with ASIN is pushed to this macro to fetch all product details. The ASIN is appended to every row for respective product to make sure data is available for joining, with data from other objects. The macro as shown in exhibit 1.14 shows that ASIN input is going into the connector to output the product data and the same macro to get the product category data.
Amazon Connector Product Object, ASIN Summary
Amazon Connector Order Object, Get Matching Products
To get the Sales/Order data at the product level, the data is joined based on ASIN. The data is joined from both the Product macros:
- Product Object; GetMatchingProduct
- Product Object; GetProductCategoriesForASIN
The data is summarized to ensure no duplicates in the data, and then it is joined to get all product categories data based on the AmazonOrderID. Exhibit 1.15 shows the workflow of data preparation.
Product object has most of the useful metrics including Sales Ranking, Product Type, Brand, Department, Manufacturer, MaterialType, Publisher, and Release Data. The data we get from Order summary should match with data we get from Join tool after joining it with data from Product summary. In the final output file, we can have the same ASIN value tagged to multiple Amazon Order Ids. In the Amazon seller environment, ASIN represents the unique SKUs for products. The final data can be utilized for order as well as inventory management. Further, that data can be utilized to prepare predictive and sentiment analytics based on historical customer behaviors, seasons, and discounts.
Please contact us at connectorssupport@grazitti.com if you would like to get the workflow with the trial version of the connector.
Didn't find the Use Case you want?
Share your use case with us and our experts will get back to you.
