__STYLES__
Sourcing the Dataset and understand the business problem
The dataset was given as a SQL dump file in github page of codebasics.io and from that we can understand that the Sales Director of AtliQ Hardware is concerned about declining sales and revenue. So the management outsources a data analyst who can analyse and report the findings to increase the sales and revenue.
Analysing the Dataset using MySQL
The data had been imported to MySQL workbench and basic analysis is carried out. The main table transactions tells the overview of sales,products,cutomers,order date.
To understand the sales and revenue decline over the years we will analyse using below MySQL query by joining transactions and date tables.
Sales declined consecutively for 2 years which raised concern
In this result we can clearly see from 2018 -2020 the sales declined significantly which raises concern in top management.
When querying the transactions table we could find some discrepancies like sales amount is -1 (As this can be eliminated since its insignificant).So checked the sales amount for any more insignificant value .1611 such records existed either with 0 price or -1. Maybe 0 price is manual error or could be that product given to customer for free as a goodwill. So these records can be insignificant when deciding the sales and revenue and it can be omitted in visualisation.This data cleaning will be done in Tableau desktop later.
Currency is in USD for 2 records so checked the table for any other currencies as sometimes customer can pay in other currencies.
Could see that the Indian currency has 2 values as ‘INR’ and ‘INR/r’. To avoid discrepancies in total sales amount USD should be converted to INR . and also ‘INR/r’ to INR.These two data cleaning will be done in Tableau desktop later.
Let’s take a look into AtliQ Hardwares primary markets and it’s sales. Note: AtliQ hardware is currently operating inside India only. Could see that 2 markets found outside India which is wrong.
So let’s see that if there is any sales transaction for those two markets which is none so it can be omitted during data cleaning in Tableau.
We couldn’t find any data discrepancies other than Foreign Markets,Insignificant Sales amount, Multiple currencies. As all this can be cleaned/omitted ,we can proceed with loading into Tableau desktop.
We are now connected to Tableau and loaded all the related files and established relationships between tables.
Data cleaning for negative and 0 sales amount has been done in Tableau.
Data cleaning for markets outside India has been done.
USD to INR conversion data cleaning also done by creating Calculated field.
After data cleaning, the Tableau dashboard has been created as below.
Conclusions:
From this dashboard, the sales director can able to understand how well the business doing in certain aspects and can take data driven decision to improve the business of AtliQ Hardware.
This dashboard can be found in Tableau public website