The Project

For this project, I want to play around and explore the data using SQL and then visualize data using Power BI.

The Objective

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.

The Data

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:

1. Customers

2. Employees

3. Offices

4. Order details

5. Orders

6. Payments

7. Products

8. Product lines

Techniques (What I did)

Step 1: Define the problem

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.

Step 2: Data Cleaning (SQL)

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.

Step 3: Data Analysis

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.

Step 4: Data Visualization

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.

Overview

• Sales peak in October and November.
• USA generates the largest sales volume
• Classic Cars generates 40% of sales volume

Product Orders

• 'Euro+ Shopping Channel 'and 'Mini Gifts' are the top customers.
• The best-seller product is '1992 Ferrari 360 Spider red'
• Euro+ Shopping Channel likes to order the '1992 Ferrari 360 Spider red '

Account Details

• Sales Representative Gerard Hernandez is assigned to Euro+ Shopping Channel, one of the highest paying clients and the largest remaining balance. He has to follow up on the remaining balance.
• Singapore has the longest average processing days for shipping orders.

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.

Extra

I played around with 'stored procedures' code to see what happens: