Sales_Insights_Data_Analyst

This is Project about Sales Product in Various Cities sells of Products with Year and Months with Data and more.

Instructions for to setup mysql on your local computer.

SQL database dump is in db_dump.sql file above. Download db_dump.sql file to your local computer and import into your MySQL workbench. Download MYSQL WorkBench : https://dev.mysql.com/downloads/workbench/

Data Analysis Using SQL

1. Show all customer records

SELECT * FROM customers;

2. Show total number of customers

SELECT count(*) FROM customers;

3. Show transactions for Chennai market market code for chennai is Mark001

SELECT * FROM transactions where market_code='Mark001';

4. Show distrinct product codes that were sold in chennai

SELECT distinct product_code FROM transactions where market_code='Mark001;

5. Show transactions where currency is US dollars

SELECT * from transactions where currency="USD"

6. Show transactions in 2020 join by date table

SELECT transactions.*, date.* FROM transactions INNER JOIN date ON transactions.order_date=date.date where date.year=2020;

7. Show total revenue in year 2020,

SELECT SUM(transactions.sales_amount) FROM transactions INNER JOIN date ON transactions.order_date=date.date where date.year=2020 and transactions.currency="INR\r" or transactions.currency="USD\r";

8. Show total revenue in year 2020, January Month.

SELECT SUM(transactions.sales_amount) FROM transactions INNER JOIN date ON transactions.order_date=date.date where date.year=2020 and and date.month_name="January" and (transactions.currency="INR\r" or transactions.currency="USD\r");

9. Show total revenue in year 2020 in Chennai

SELECT SUM(transactions.sales_amount) FROM transactions INNER JOIN date ON transactions.order_date=date.date where date.year=2020 and transactions.market_code="Mark001";

10. You have to find of Inner join with date year = 2020;

SELECT sales.transactions.*, sales.date.* from sales.transactions INNER JOIN sales.date ON sales.transactions.order_date = sales.date.date where sales.date.year=2020;

11. Find the Revenue of 2020.

SELECT SUM(sales.transactions.sales_amount) from sales.transactions INNER JOIN sales.date ON sales.transactions.order_date = sales.date.date where sales.date.year=2020;

12. Show the with date of Sales revenue in chennai.

SELECT SUM(sales.transactions.sales_amount) from sales.transactions INNER JOIN sales.date ON sales.transactions.order_date = sales.date.date where sales.date.year=2020 and sales.transactions.market_code="Mark001"

13. Distinct query for sell Product in Chennai.

SELECT distinct product_code from sales.transactions where market_code="Mark001";

Star Schema :

• A star schema is a data model that organizes data in a database in a way that makes it easier to analyze and understand. It's a popular architecture for data warehousing and is characterized by a central fact table surrounded by dimension tables.

Data Analysis Using Power BI Formula to create norm_amount column

= Table.AddColumn(#"Filtered Rows", "norm_amount", each if [currency] = "USD" or [currency] ="USD#(cr)" then [sales_amount]*75 else [sales_amount], type any)

Sales market Table : Make a filter for blank zones removed .

= Table.SelectRows(sales_markets, each ([zone] <> ""))

Sales _transactions table : Where -1 value find out .

SELECT * FROM sales.transactions where sales_amount = -1;

SELECT * FROM sales.transactions where sales_amount <= 0;

Or using <=0 then find values less than 0 so it’s garbage value.

In PowerBi :

Using filter deselect 0 and -1 values you can get the filtered data from sales transactions.

→ Using conditional column , select currency and add value USD and Output value 1 so it will change the where USD = 1 and else Null. But you can change the formula and make it 0 instead of Null.

→ Using formula :

= Table.AddColumn(#"Filtered Rows", "norm_sales_amount", each if [currency] = "USD" then [sales_amount]*84 else [sales_amount])

Norms_sales_amount convert USD to INR and also sales_amount will as similar show.

→ In between found the duplicates records like INR\r and USD\r :

select count(*) from transactions where transactions.currency='INR\r';

select count(*) from transactions where transactions.currency='INR';

select * from transactions where transactions.currency='USD\r' or transactions.currency='USD';

Their is 4 records found and fix it.

Make a dashboard of Sales Insights :

• Revenue generated in Market
• Sales by Market
• Top 5 Customers in Market
• Top 5 Products in Market

Revenue and Date between generates market show on Dashboard.

Mobile View of PowerBI Dashboard:

---

In that Sales Insights Project I have generate the Piecharts , Stakedbar Chart , LineChart , Slicer Using make a Report of Revenue Generate , Sales of Product ,Top 5 Customers and Top 5 Product Sell in Market show in Report by Date,Month and Year.