__STYLES__
For this project, I want to play around and explore the data using SQL and then visualize data using Power BI.
Understand data trends, clean data and create a dashboard that shows key insights about:
Sales volume
Best-seller product
Status of shipment
But, as I explore further I hope to reveal more invaluable insights I haven't considered yet.
Classic Models Inc. is a retailer that sells scale models or collectible model cars to distributors across the globe.
Source of SQL sample database: https://www.mysqltutorial.org/mysql-sample-database.aspx
The database contains 8 tables:
Customers
Employees
Offices
Order details
Orders
Payments
Products
Product lines
With a database on hand, I start with a problem or question. Then lay out general questions to solve a business problem such as: "What is the best-seller product?". By framing this kind of question I managed to ask in-depth questions related to general questions such as: "What country generates the highest sales of the best-seller product?" or "What product line does the best-seller product belong to?"
This helps identify which tables to join before turning it into useful information.
This framework is only a guide to jumpstart the data analysis.
Removed unnecessary columns, checked duplicates, corrected data types, and concatenated and capitalized names. There are a few minor mistakes and errors found throughout the process so cleaning is done during the entire data analysis process.
How I used SQL to perform the analysis?
Step 3.1: Aggregate Data
Using SQL, the first thing I did was aggregate data for spot-checking purposes (total payment, total sales, total orders, etc) so that when joining multiple tables, for example: the customer table. I could verify the correctness of applied statistical functions.
Step 3.2: Report Sales Representative and Customers
Know which customers are handled by a certain sales representative.
Step 3.3: Tracking Customer Transactions
Explore and track which customers have the highest sales, and unpaid transactions.
Total Payments
Step 3.4: Report overall Sales (including unpaid amount)
Step 3.5: Report Sales Volume by Product Line (product category)
Step 3.6: Report Sales by Customer
Step 3.7: Employee and Office location
Finally, Step 3.8: Appending data
Creating a new dataset by joining multiple tables.
Example: By joining tables of customers, orders, orderdetails, and products it resulted in this whole new dataset of order and shipping status of customers and products.
After creating new datasets from SQL, it is exported as a CSV File. Only selected tables are used in Power BI.
The first thing to do is create a data model(star schema) by indicating the table as 'fact table' and/or 'dimension table'.
Then with Power Query, clean and verify again the data type of the imported table before appending data(Example: adding calculated column 'Processing Days')
Analyze the data tables and attempt to answer the listed questions from the 'post-it' framework. The efficient way to do this is to create DAX aggregate functions so creating charts and graphs becomes easier.
Product Orders
Account Details
Basket of Goods Analysis
The Basket of Goods Analysis, allows us to learn which products are often purchased together. For example; 27 out of 53 transaction orders of the product 1992 Ferrari 360 Spider red are ordered together with the 1964 Mercedes Tour Bus.
With this insight, Classic Models Inc. can launch marketing campaigns like special promotion bundles of products bought together.
I played around with 'stored procedures' code to see what happens: