logo
SPC provides consulting excellence in analytics, data management and reporting services to small and medium enterprises.
Feel Free To contact Us

+91 20 6524 2455

info@spconsultingllp.com

Mont Vert Marc, Pune, India

+91 20 6524 2455

Mont Vert Marc, Pune, India

Top

Implementation of Dashboard for online Retailer on Amazon

Implementation of Dashboard for online Retailer on Amazon
Online Retail

Designing and implementing a streamlined dashboard for our Canada based online retailer client was a unique experience for us.

The Problem: 

One of our client, a Canada based manufacturer and online retailer of kitchen and garden equipment products, has its sales across 6 countries namely Canada, US, UK, France, Germany and Japan, using Amazon as an online retail platform.

Amazon provides sales, inventory, competitor rankings, PPC (Price Per click) and other data on Amazon Seller Central. However, each country has a different data source and currency. Consolidating this data on a single platform is a major challenge. Moreover, analyzing this enormous aggregated data and generating day to day report out of it is a critical task.

The Solution:

SP consulting came up with a solution of designing a consolidated dashboard with the aid of which client can monitor his business at the click of a button.

We designed and developed a centralized database in SQL Azure that stores data from different countries in a single repository.

In the next stage, MS excel based tools were developed that pick up downloaded amazon seller data from different countries inserts that in a newly designed centralized database.

Our team members worked side-by side with teams from the client company and came with the smart analytic framework of the dashboard. The uniquely designed dashboard was an automated real-time dashboard.

Considering the advance features for graphics and computations and user friendly advantage of Power BI technology we decided to go with it to create dashboard. The dashboard contains following reports. All data can be filtered by country, team, product SKU, name, category, sub category and time.

  • CEO Dashboard: This dashboard offers an overview of overall business. In-depth knowledge of sales by country, sales by sales team, sales by product, category and sub-category, comparison of sales with previous week, previous month, previous year at same day can be extracted. End to end details of sessions, conversions, buy box and impressions is obtainable from this dashboard. The aggregated data can be seen in the local currency of the individual country or in USD or CAD
  • Sales Monitor: This monitor provides total sales segregated by different parameters, top 10 products, bottom 10 products by sales. With the help of this monitor comparison of sales parameters with different time windows is possible. Data can be visualized using interactive tables, pie charts, line charts and bar charts.
  • Competition Monitor: It provides comparison of competitor’s products with client’s products in terms of price and rankings.
  • PPC Monitor: The function of this monitor is to display performance of different ad campaigns based on their impressions, number of clicks and ACoS (Advertising Cost of Sales). This monitor plays a key role in Identifying which keywords are generating money and which ones are ineffective.
  • Inventory Monitor: This monitor dose the analysis of stock available, stock in transit, excess inventory, Days to inventory, near to out of stock products.

SP consulting delivered a remarkable solution of consolidated dashboard which offered deeper data pool and unique analytic framework that would unlock all the data possibilities to yield actionable conclusions. Benefits gained by our client are listed below:

  • The key advantage of consolidated dashboard is that client gain an overview of inventory management. For example: Dashboard clearly represents the availability of products and notifies the client. This helps client to increase sales by maintaining adequate inventory of the demanding products.
  • The 2nd most crucial factor in online retailing is pricing/rankings of products. With the aid of consolidated dashboard client can understand the trend in competitor’s pricing/ranking of products and can change the price of his products dynamically, to achieve elevated sales.
  • It can be easily accessed from desktop, tab or mobile phone (android and iPhone)
  • Daily data update process needs only 10 minutes.
  • The dashboard gets refreshed automatically when database gets updated.
  • The productivity and efficiency at client has increased.
  • Client can gain deeper insights of which products, categories, countries are driving the sales.
  • Efficient inventory management.
Client

A Canada based online Retailer on Amazon

Industry

Online Retail

Function

  • Sales
  • Order Management
  • Inventory Management

Challanges

  • Analyzing and Integrating fragmented data from Amazon central across 6 countries
  • Elaborating dashboards for CEO, Sales team, inventory and competition monitoring
  • Currency aggregation of 6 countries into single point

Solution

SP consulting implemented a database framework to consolidate Amazon central data across 6 countries (Canada, US, UK, France, Germany and Japan) and developed dashboard for Sales and Marketing departments. The sales data from different countries was aggregated into single currency.

Benefits

  • Business monitoring single click
  • Deeper insights of higher and sunken product sales
  • Elevated sales
  • Optimal inventory management
  • Competition monitoring
  • Accurate calculation of ACoS (Advertising cost of Sales)

Technologies Used

  • Database: SQL Azure
  • Others: MS Excel-VBA
  • Dashboard, Reporting: Power BI

Date