- Problem statement:
A case study is based on a computer hardware company located in Canada, which is facing challenges in a dynamically changing market: The sales director decides to invest in a Data Analysis project to handle these problems the company is facing, and he/she would like to build PowerBI dashboard that can give him/her a real time sales insights; a publication for executives seeking wise perspectives on selling, and sales management.
- Project planning, using Aims Grid:
After the meeting is conducted with the IT head office, the sales director is planning with the team on how to approach the Data project, they’ll brainstorm on project planning using the Aim’s grid method; it’s a project management tool that has 4 components to it. The 1st is “Purpose”, that ask the question, what do we want to do exactly? In other words, is to unlock sales insights that are not visible before for sales team for decision support and automate them to reduced manual time spent in data gathering. The 2nd is “Stakeholders” who are mainly involved, in our case we have the Sales Director, Marketing Team, Customer Service Team, Data Analytics Team, IT. The 3rd component is the “End Result”, An automated dashboard providing quick and latest sales insights in order to support data driven decision making. The next step is we have to ask ourselves, what do we want to achieve, well it can’t be any vague statement, the end product will be a dashboard for stakeholders to look at real sales numbers, so the 4th component will be the success criteria, it includes Dashboards uncovering sales order insights with latest data available, also the sales team are able to take better decisions and prove 10% cost savings of total spend, sales analysis stop data gathering manually in order to save 20% of their business time and reinvest it value added activity.
- Performing Data Analysis using SQL:
The Data handled to me consists of 5 tables; Products, transactions, customers, date, and markets. An estimate of 160,000 rows and 17 columns: -Performed left and right inner joins queries to gather data. -Answered Stakeholders multiple questions with SQL queries, then exported an Excel file to him/her for revision. Performing joins queries to find the patterns in our data: -After quering the tables together, we have the sum of sales in given 1st year for around 33,645,2114$. -Performed SQL queries to find out the sum and distinct product of sales in one location, it came to 2,463,024$ and the product001 the most popular.
- Data Cleaning and ETL with Power BI:
This process is also known as data munging or data wrangling. We will do currency normalization, handling invalid values etc.
- Data Visualization with PoweR bI: The Dashboard will be attached in the repository for review and sales estimates.
Note: all invoice sales records are directly uploaded to MySql in the company's Sales Management system (OLAT), around 150230 Sales Transctions.