Data Analysis Project using SQL

Project Data Analysis for Retail : Sales Performance Report is provided by DQLab Course. Through the data has given, the manager of DQLab wants to analyze:

Fig 1. Indicator Analysis (Created by Author)

I make an assumption that the data given is clean and I don’t have to do Data Cleaning. So my workflows are:

Fig 2. Workflow (Created by Author)

1. Problem Statement

Before jumping into the tasks, we have to understand about the business model first. So I ask, “what’s products DQLab Store sell to make money?”. After querying, I make assumptions that DQLab Store is an office supply company. DQLab Store helps businesses thrive by setting them up for success by providing stuff like Bookcases, Tables, Paper, Computer Peripherals, Office Machines, etc. DQLab has to make sure that their company is a trusted source of supplies for the businesses in the area.

Fig 3. Products in DQLab Store

What’s happening in this company? Manager said that DQLab Store don’t growth. Manager is having a lot of struggle and Manager want to know the reasons behind it and also want some insights to make DQLab Store performance better. So manager ask Data Analyst to help him.

Noted : All the explanations in the problem statement are just the author’s assumptions, the author is only given assignments and datasets

2. Data understanding

Relational Database Management System (RDBMS) DQLab Store using MySQL. The table name is dqlab_sales_store, dataset contains the transactions report from 2009 until 2012 consist 5500 records and 10 fields. The order status field has the value ‘Order Finished’, ‘Order Returned’ and ‘Order Cancelled’.

Fig 4. Dataset Brief (Created by Author)

Fig 5. Output total records, schema table, and type of order status

3. Exploratory Data Analysis

After we understand about problems and data, then let’s query for the task!

• Order numbers and total sales from 2009 until 2012

Fig 6. Total sales and orders

The highest total sales were in 2009 and it doesn’t get higher after that and the highest total order were in 2012. Although the change isn’t too significant over the years.

• Total sales for each sub-category of product in 2011 and 2012

Fig 7. Growth for each sub-category of product in 2011 and 2012

Most the growth sales are lead the increases, shown by a positive value. If we back to Fig 3, we can say that product from category Office Supplies and Technology contribute the most sales. Product from Furniture category don’t have significant growth also got a decline in sales the most. Labels, Copiers & Fax and Tables are the categories that got a decline in sales the most.

• The effectiveness and efficiency of promotions carried out so far, by calculating the burn rate of the overall promotions by year

Fig 8. Burn rate percentage

Fig 9. Burn rate formula

Burn rate is the ratio of the total cost for the discount compare to the total sales earned. In this project, burn rate analysis is used to knowing the effectiveness and efficiency of promotions. DQLab hopes that the burn rate doesn’t exceed 4.5% but we can see that percentage of burn rate more than 4.5% every year. It means promotion not increase sale

• The effectiveness and efficiency of promotions carried out so far, by calculating the burn rate of the overall promotions by sub-category of product on 2012

Fig 10. Burn rate percentage by sub-category of product

There are only five sub-category of product that have the burn rate bellow 4.5 % and most promotions failed for every product.

• The number of customers transactions for each year

Fig 11. Number of customers transactions for each year

The number of customers isn’t changing significantly overall, it means number of customer tend to remains. But fortunately, we didn’t get a significant decline in customers.

• The number of new customers for each year

Fig 12. Number of new customers for each year

The growth of new customers for each year is decreasing. If we back to Fig 11, total number of customers tends to be in the values ​​around 580–590 each year. This informs us that many previous customers still back to DQLab Store to do the transaction besides the new customers are decreasing.

4. Insights

According to exploratory data analysis that we have done, we get some insights:

1. The total sales and order number of DQLab Store are fluctuated as overall, where the highest total sales happened in 2009.
2. Total sales based on the sub-category of products mostly got decreasing between 2011 and 2012 except Chairs & Chairmats
3. Effectiveness and efficiency of promotions haven’t been reaching as DQLab Store hopes because burn rate over the year still above 4.5%.
4. The number of customers over the years tends to remain, it is around 580–590 and number of new customers decreasing over the years

5. Conclusions

Conclusions come with recommendations:

1. The manager knows that so far the promotions are not optimal in increasing sales, so the data analyst recommends doing target segmentation. Through segmentation, manager and marketing team can identify niches with specific needs, mature markets to find new customers, deliver more focused and effective marketing messages.
2. Managers know that there are some products not perform well (negative sales growth), so the data analyst suggests doing further research on consumer needs. DQLab Store must ensure that all products sold are in accordance with consumer needs.

Finally, we did it! I hope you enjoyed it as much as I did. Please let me know if you have any questions! You can either leave a comment here or on Linkedin (Let’s connect!) :)

Additional

Certificate success finish this project provided by DQLab: