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.

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.

Column Name	Description
Seller Gstin	This is the GST registration of merchant for the State from where item is being shipped.
Invoice Number	Unique ID assigned to invoice
Invoice Date	Date of invoice generation
Transaction Type	Shipment, Cancel and Refund
Order Id	Amazon Order ID
Shipment Id	Amazon Shipment ID
Shipment Date	Date of shipment confirmation
Order Date	Date of the order
Shipment Item Id
Quantity	Ordered Quantity
Item Description
Asin	Amazon provided ASIN
Hsn/sac	HSN code provided by the seller
Sku	Stock 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 City	Order shipped from city
Ship From State	Order shipped from state
Ship From Country	Order shipped from country
Ship From Postal Code	Order shipped from portal code
Ship To City	Order shipped to city
Ship To State	Order shipped to state
Ship To Country	Order shipped to country
Ship To Postal Code	Order shipped to postal code
Invoice Amount	Total tax inclusive amount collected from customer including all charges.
Tax Exclusive Gross	Total 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 Id	Unique 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.

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 -

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.

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 .

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.