Creating Amazon Seller Sales Dashboard In Google Data Studio [ Free Template ]

Introduction

If you are selling your products on amazon then this article is going to be very helpful to you because in this article i am going to create an amazon sales dashboard with the help of google data studio. This dashboard will help you to calculate your gross sale, net sale, and returns etc. . You can also figure out your top performing products, region wise sales, day to day graphs of your orders.

After this tutorial I will share the template of this dashboard , So you can easily create your own dashboard.

Amazon Seller Sales Dashboard

Prerequisites

  • Amazon Seller Central's Amazon MTR Report
  • Basic knowledge of Excel like opening files in it.
  • A Google account.
  • Basic knowledge of Google Sheets like opening & importing files, creating worksheets, applying formulas etc.
  • Basic Knowledge of Google Data Studio

How to Create an Amazon Sales Dashboard ?

So the question is how do we create a sales dashboard ?

To create this dashboard we are going to use amazon MTR report csv file , google sheets and google data studio.

Steps to Create Amazon Interactive Sales Dashboard

Step 1 : Downloading and Understanding Report

Go to https://sellercentral.amazon.in/ and download the report by going Reports > Tax Document Library > Merchant Tax Report .

There are two types of reports: B2C Report and B2B Report . We download both reports.

Here is a list of columns available in B2C and B2B Report.

Downloading Amazon MTR Report
Column Name Description
Seller GstinThis is the GST registration of merchant for the State from where item is being shipped.
Invoice NumberUnique ID assigned to invoice
Invoice DateDate of invoice generation
Transaction TypeShipment, Cancel and Refund
Order IdAmazon Order ID
Shipment IdAmazon Shipment ID
Shipment DateDate of shipment confirmation
Order DateDate of the order
Shipment Item Id
QuantityOrdered Quantity
Item Description
AsinAmazon provided ASIN
Hsn/sacHSN code provided by the seller
SkuStock Keeping Unit. merchant-defined unique identifier for a product
Product Tax Code
Bill From City
Bill From State
Bill From Country
Bill From Postal Code
Ship From CityOrder shipped from city
Ship From StateOrder shipped from state
Ship From CountryOrder shipped from country
Ship From Postal CodeOrder shipped from portal code
Ship To CityOrder shipped to city
Ship To StateOrder shipped to state
Ship To CountryOrder shipped to country
Ship To Postal CodeOrder shipped to postal code
Invoice AmountTotal tax inclusive amount collected from customer including all charges.
Tax Exclusive GrossTotal tax exclusive amount collected from customer including all charges.
Total Tax Amount
Cgst Rate
Sgst Rate
Utgst Rate
Igst Rate
Compensatory Cess Rate
Principal Amount
Principal Amount Basis
Cgst Tax
Sgst Tax
Igst Tax
Utgst Tax
Compensatory Cess Tax
Shipping Amount
Shipping Amount Basis
Shipping Cgst Tax
Shipping Sgst Tax
Shipping Utgst Tax
Shipping Igst Tax
Shipping Cess Tax Amount
Gift Wrap Amount
Gift Wrap Amount Basis
Gift Wrap Cgst Tax
Gift Wrap Sgst Tax
Gift Wrap Utgst Tax
Gift Wrap Igst Tax
Gift Wrap Compensatory Cess Tax
Item Promo Discount
Item Promo Discount Basis
Item Promo Tax
Shipping Promo Discount
Shipping Promo Discount Basis
Shipping Promo Tax
Gift Wrap Promo Discount
Gift Wrap Promo Discount Basis
Gift Wrap Promo Tax
Tcs Cgst Rate
Tcs Cgst Amount
Tcs Sgst Rate
Tcs Sgst Amount
Tcs Utgst Rate
Tcs Utgst Amount
Tcs Igst Rate
Tcs Igst Amount
Warehouse IdUnique fulfilment centre ID from where the order was shipped.
Fulfillment Channel
Payment Method Code
Credit Note No
Credit Note Date
Customer Ship to GSTIN
Bill To City
Bill To State
Bill To Country
Bill To Postal Code
Customer Bill to GSTIN

We are going to calculate our sales according to the Transaction Type column available in the report. So there are three types of transaction types - Shipment, Cancel and Refund .
Shipment is for order ,
Refund is for Return and
Cancel is for cancelled orders .

We will not going to calculate Cancel transaction because there are no amount transaction of cancelled orders.

Step 2 : Importing Report In Google Sheets

Now create a new spreadsheet in google sheets and rename it to Amazon Merchant Tax Report. Now import the csv report in the sheet by going to File > Import > Upload.

Select your csv report file to upload. After uploading the csv file, select replace current sheet and import data to Sheet1. Rename Sheet1 to Amazon MTR Report.

Now we have successfully imported the report to google sheets.

Step 3 : Implementing Formulas for Gross Sale, Returns & Net Sale Units

Now we add three columns with name Gross Sale, Returns and Net Sale.

Amazon Gross Sale, Return, Net Sale
Column Name Transaction Type Column Formula In second row
Gross Sale Shipment =ArrayFormula(IF(D2:D="Shipment",J2*1,0))
Returns Refund =ArrayFormula(IF(D2:D="Refund",J2*1,0))
Net Sale Shipment - Refund =ArrayFormula(IF(D2:D="Shipment",J2*1,IF(D2:D="Refund",J2*-1,0)))

Step 4 : Creating State and Region Map

Now we create a State and Region Map Sheet of which state falls in which region. There are also lots of differences between Amazon provided state names and Google Data Studio provided state names . There are many mistakes in amazon provided state names like spelling error, capital - small letters, short code of state name So we correct these names. So let’s take a look -

Region Map for Amazon Seller Central Sales
  1. Create a new sheet named Region Map .
  2. In the first column we put unique state names from Ship To State column with header Amazon State.
  3. In the second column we put correct state names manually, with header GDS State.
  4. In the third column we put region names of country , here I am using a report of India so I use indian regions. If you are creating a report from another country you have to do it manually with your country regions.
  5. Now add two columns with header GDS State and REGION in Amazon MTR Report sheet with VLOOKUP of Ship to State Column.
State and Region Map Column

If you want to create your own spreadsheet you can do it, otherwise you can make a copy of this sheet by going to this link - https://docs.google.com/spreadsheets/d/1PPlMNSA8khCqdQ0Z5ZWSfOubS3iwohDup5Q-mKsGSag/copy and replace the report with your report without deleting Gross Sale, Returns and Net Sale, GDS State and REGION columns.

Step 5 : Connecting Spreadsheet To Amazon Sales Dashboard

In this step we are going to add our spreadsheet to template of amazon sales dashboard .

Connecting Spreadsheet To Google Data Studio Step I
Connecting Spreadsheet To Google Data Studio Step II
  1. First of all go to this link - https://datastudio.google.com/u/0/reporting/5a6d5258-52cf-4810-9ce8-c83036346756/page/VW6NC and see how our dashboard is going to look like?
  2. Click on Use Template from top right of dashboard.
  3. Now a pop up will open , change the New Data Source from the dropdown and click on CREATE NEW DATA SOURCE .Now click Google Sheets and select spreadsheet and worksheet of your data .
  4. After connecting the data source there is a list of all columns available in your sheet , check for data type of columns if there is any error ,correct the data type. You have to change the data type of GDS State to GEO. After this Click on Add to Report.
  5. Now Dashboard data is replaced with your data .There would be an error in the Returns % box.
  6. Click on error box and view the metric in left pan and click on AUT and replace the formula with this- SUM(Returns)/SUM(Gross Sale) and select Aggregation type PERCENT. CLick on APPLY.
  7. Now your dashboard is ready.

Uses Of This Sales Dashboard

This dashboard is fully interactive when you click on a slice of donut chart of any region or state, all data filtered according to the selected field. So here is a list of this you can do it by using this dashboard.

  • Filter your reports by date range.
  • You can check your Gross Sale, Returns and Net Sales with their tax exclusive amount .
  • Day wise day chart of your orders and returns.
  • Percentage of goods shipped from which warehouse.
  • Top performing SKU or Products with their sales, returns and net sales units.
  • State or Region wise sales with percentage.

You can also download your dashboard calculated feild data as excel or csv file by clicking on three dots on the top of chart. If you are facing any error then comment your error , I will help you.

Note : Google sheets not able to handle large data. If your report has large data or if you want to analyze a report of years , then you have to upload it to Data Servers like SQL server, Microsoft Azure, Google BigQuery or any other database.

There are a lot of advantages to making your dashboard. In the dashboard we can simply see which product is performing better, we can find out what goods should be produced. In the dashboard we can simply view our state and region wise sales which will help us to send goods to FBA according to region wise sales.

This is a simple dashboard. There are a lot of possibilities in a data studio to visualize your data. If you want to add any metric in this dashboard , you can comment below, I will help you.

If you want to make a dashboard of any other report or any other platform ,you can mail me or comment it down.

5 comments:

  1. Hi Ankit,

    This is really a wonderful sheet for the amazon sellers. It gives a lot of insight about the overview of the business.

    We can dig deeper a bit and create the sheet for actionable so that the sellers can take action based on the report. I am writing down a few pain points of the sellers which you can think of creating.

    1. Return Reconciliation -> Nearly 30% of the returning shipments are lost in the transit. And this is a bigger problem for amazon sellers. Now if we can create the sheet where the sellers can see the returning items, and map it with the actual returned items, they will be able to see what is missing.

    2. Profit and Loss dashboard.. With every SKU the merchant can enter the input cost as 2p(Product cost, Packing cost) and rest of the data can be fetched through Amazon.. this sheet will be helpful in finding out the actual cost and profit.

    I've many more ideas as amazon seller.. Feel free to connect with me if you need more insight (vs at abanahomes.com )

    Cheers, Neha.

    ReplyDelete
  2. Good website! I truly love how it is easy on my eyes it is. I am wondering how I might be notified whenever a new post has been made. I have subscribed to your RSS which may do the trick? Have a great day! Buy Amazon New World Gold

    ReplyDelete
  3. Excellent .. Amazing .. I’ll bookmark your blog and take the feeds also…I’m happy to find so many useful info here in the post, we need work out more techniques in this regard, thanks for sharing. Buy Amazon New World Coins

    ReplyDelete
  4. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing. Book on Hair Care

    ReplyDelete
  5. You can utilize Fulfillment by Amazon whether you simply need to sell a couple of things on Amazon as a sideline or need to sell great many items. You can utilize it assuming you are beginning another business or have a current one that you need to change over to Fulfillment by Amazon.https://ww.soap2day.top/

    ReplyDelete

Pages