Project Data Analysis for Retail: Sales Performance Report



Project Summary

Improving the Understanding of SQL function to help access, read, manipulate, and analyze the data stored in the database and generate useful insights to drive an informed decision-making process, and this 3 main things that I did in this project :

  1. Created SQL query based using aggregate functions, date format, joins, CTE, and window function.
  2. Creating EDA, Analyze Store Promotion Effectiveness,Efficiency and creating Customer Analytics
  3. Created the insights for the project

Project Background & Dataset

Project Background

This project purpose is to Analyze the performance of the DQLab Store, Explore and get the hands on Structured Query Language (SQL). As a data analyst, we are expected to use SQL to formating and cleaning the data, joining the data, using CTE and Window function also generating useful insights from the data.

Dataset 1 | The table name used in this project is "dqlab_sales_store".

The dataset used contains transactions from 2009 to 2012 with a total of raw data is 5500, including order status which is divided into order finished, order returned and order cancelled, The dataset that will be used in this project contains the following data:

  1. OrderID
  2. Order Status
  3. Customer
  4. Order Date
  5. Order Quantity
  6. Sales
  7. Discount %
  8. Discount
  9. Product Category
  10. Product Sub-Category

Dataset 1

1.Overall Performance by Year



Total sales each year tends to increase, but in 2012 total sales were not greater than total sales in 2009 and number of orders finished each year tended to increase but not significantly.

2.Overall Performance by Product Subcategory



There is an increase and decrease in total sales for each product subcategory in one year. The highest total sales in 2011 and 2012 were still dominated by product subcategories that tended to be the same, it is chairs and chairmats, office machines, and tables, while the lowest total sales are labels, scissors, rulers and trimmers, and rubber bands.

3.DQLab Store Promotion Effectiveness and Efficiency | By Year

In this section we will analyze the effectiveness and efficiency of the promotions that have been carried out so far. The effectiveness and efficiency of the promotions will be analyzed based on the burn rate by comparing the total value of the promotions to the total sales earned. DQLab hopes that the burn rate remains at the maximum 4.5% (Formula untuk burn rate : (total discount / total sales) * 100)



Promotion Effectiveness and Efficiency by Years. every year the percentage of burn rate tends to increase, which means that promotion costs continue to increase every year. The impact of increased promotion costs is to increase in total sales, but the promotion that has increased sales cannot be said to be effective and efficient because the percentage of burn rate exceeds the maximum expected rate at 4.5%.

4. DQLab Store Promotion Effectiveness and Efficiency | by Product Subcategory


in 2012 there were only a few product subcategories with a burn rate percentage lower than 4.5%. Meanwhile, several product subcategories with a burn rate greater than 4.5% had low sales rates.

5. Customer Analyst | Customers Per Year



Every year the number of customers who make transactions tends to increase but not significantly.

Conclusion

DQLab store during 2009 to 2012 had performance that increased every year but not too significantly. Overall, promotion activities are quite good but still not effective and efficient based on product subcategories. In addition, there are more existing customers who make transactions than new customers, so store performance tends to be stagnant.