Computer Hardware Manufacturer
Performance Analysis
Aims Grid
Purpose
To unlock sales insights that are not visible before the sales team for decision support and automate them to reduced manual time spent in data gathering.
Stakeholders
- Sales Director
- Marketing Team
- Customer Service Team
- Data & Analytics Team
- IT
End Result
An automated dashboard providing quick & latest sales insights in order to support data driven decision making
Success Criteria
- Dashboard(s) uncovering sales order insights with latest data available
- Sales team ablel to take better decisions & prove 10% cost savings of total spend
- Sales Analysts stop data gathering manually to order to save 20 % of their business time and re-invest it value added activity
coding
Data Cleansing & Transformation (SQL)
To create the necessary data model for doing analysis and fulfilling the business needs defined in the user stories the following tables were extracted using SQL.
The data source (sales) were provided in .sql format by the IT team, and were connected in the data model in a later step of the process.
Below are the SQL statements for cleansing and transforming necessary data.
sales schema
Use sales;
SELECT *
FROM [sales].[customers]
SELECT *
FROM [sales].[products]
SELECT distinct product_type
FROM [sales].[products]
--Show total number of customers
SELECT count(*) FROM sales.customers;
--Validating currency tags
SELECT distinct currency FROM sales.transactions;
SELECT count(*) FROM sales.transactions where currency='GPB\r';
--Show transactions for Chennai market (market code for Birmingham is Mark001)
SELECT * FROM sales.transactions where market_code='Mark001';
--Show distrinct product codes that were sold in Birmingham
SELECT distinct product_code FROM sales.transactions where market_code='Mark001';
--Show transactions where currency is US dollars
SELECT * from sales.transactions where currency like '%USD%';
--Show transactions in 2020 join by date table
SELECT transactions.*, date.* FROM sales.transactions INNER JOIN sales.date
ON transactions.order_date=date.date where date.year=2020;
--Show total revenue in year 2020
SELECT SUM(transactions.sales_amount) FROM sales.transactions INNER JOIN sales.date
ON transactions.order_date=date.date where date.year=2020
and transactions.currency='INR' or transactions.currency='USD';
--Show total revenue in year 2020, January Month,
SELECT SUM(transactions.sales_amount) FROM sales.transactions INNER JOIN sales.date
ON transactions.order_date=date.date where date.year=2020 and date.month_name='January'
and (transactions.currency='INR' or transactions.currency='USD');
--Show total revenue in year 2020 in Chennai
SELECT SUM(transactions.sales_amount) FROM sales.transactions INNER JOIN sales.date
ON transactions.order_date=date.date where date.year=2020 and transactions.market_code='Mark001';
Data Cleansing & Transformation (Power Query/DAX)
The data (sales) from the SQL server was exported to Power BI for further cleaning and transformation.
Below are the Power Query for cleansing and transforming necessary data.
Market Table:
-- Removing blank data rows in market zone column
= Table.SelectRows(sales_markets, each ([zone] <> ""))
Transactions Table:
-- Removing null data rows of -1 and 0 in transactions column
= Table.SelectRows(sales_transactions, each ([sales_amount] <> -1 and [sales_amount] <> 0))
-- Adding new column to convert sales amount in USD to INR
= Table.AddColumn(#"Filtered Rows", "n_sales_amount", each if [currency] = "USD" or [currency] = "USD#(cr)" then [sales_amount]*75 else [sales_amount])
-- Creating "Total Profit Margin" Feature
Total Profit Margin = SUM('Sales transactions'[Profit_Margin])
-- Creating "Revenue" Feature
Revenue = SUM('Sales transactions'[norm_sales_amount])
-- Creating "Profit Margin" Feature
Profit Margin % = DIVIDE([Total Profit Margin],[Revenue],0)
-- Creating "Profit Margin Contribution %" Feature
Profit Margin Contribution % = DIVIDE([Total Profit Margin],CALCULATE([Total Profit Margin],ALL('sales products'),ALL('sales customers'),ALL('sales markets')))
-- Creating "Profit Target" Feature for performance analysis
Profit Target = GENERATESERIES(-0.05, 0.15, 0.01)
-- Creating "Profit Target Value" Feature for performance analysis
Profit Target Value = SELECTEDVALUE('Profit Target'[Profit Target])
-- Creating "Profit Difference" Feature to highlight negative performance
Target Diff = [Profit Margin %]-'Profit Target'[Profit Target Value]
-- Creating "Revenue Contribution %" Feature
Revenue Contribution % = DIVIDE([Revenue],CALCULATE([Revenue],ALL('sales products'),ALL('sales customers'),ALL('sales markets')))
-- Creating "Revenue" Feature for the last year for comparison
Revenue LY = CALCULATE([Revenue],SAMEPERIODLASTYEAR('sales date'[date]))
Data Model
Below is a screenshot of the data model after cleansed and prepared tables were read into Power BI.
This data model also shows star schema of the dimension data from the customer, calendar, products and transactions connected to the facts table.
Star Schema
Sales Management Dashboard
The finished sales management dashboard with one page with works as a dashboard and overview, with two other pages focused on combining tables for necessary details and visualizations to show sales over time, per customers and per products.
