Sales Performance Report 📠

Another SQL project. The data get from DQLab project contains transactions from 2009 to 2012 with a total of 5500 raw data. In this blog, not only answer the question from the original DQLab project but I also build queries for my questions. Let's go!

Data Preparation

The table name that will be used in this project is sales_store (only one table). There are 10 columns that contain all information about customers' orders. This is the ERD from the LucidChart application.

For sales_store tables it contains 10 columns that record the transaction with order_id as key column.

Overall Performance

Performance by year

First of all, let's see the sales by revenue and number of quantity in each year.

SELECT EXTRACT(year from order_date) as years, sum(sales) as sales, count(distinct order_id) as number_of_order 
FROM `sql-project-dqlab.project3.sales_store` 
WHERE order_status='Order Finished'
GROUP BY 1
ORDER BY 1; 

The highest sales occur in 2009. It shows that the more quantity are not equal with sales, in 2012 when the higher quantity order not represented the higher sales.

Growth sales by year

Sales growth rate measures company’s ability to generate revenue through sales over by year. This rate is not only used to look at internal successes and problems, it’s also analyzed by investors to see if that the company on the rise or a company starting to stagnate. Formula:

growth = (current sales - previous sales) x 100 / previous sales

WITH growth AS(
    SELECT EXTRACT(year from order_date) as years, sum(sales) as sales, 
    FROM `sql-project-dqlab.project3.sales_store` 
    WHERE order_status='Order Finished'
    GROUP BY 1
    ORDER BY 1
    )
SELECT years, sales, 
concat(round((sales-LAG(sales) OVER (ORDER BY years ASC))*100/LAG (sales) OVER (ORDER BY years ASC),2),"%") as growth_sales 
FROM growth
ORDER BY 1 ASC; 

Whatever happened in 2018 was catastrophic to the average annual sales growth rate. It saw a drop for the company that is reflected in the four-year growth.

Performance by product sub category

Find out the most sales by product sub category, it's group by year and have condition after 2010 or only two years in 2011 and 2012.

SELECT EXTRACT(year FROM order_date) as years, product_sub_category, sum(sales) as sales
FROM `sql-project-dqlab.project3.sales_store`
WHERE order_status='Order Finished' AND EXTRACT(year FROM order_date) > 2010
GROUP BY 1,2
ORDER BY 1,3 DESC;

From the result we know that:

Category 'Chairs & Chairmats' and 'Office Machines' are the highest sales in 2011 and 2012.

'Rubber Bands' is the lowest sales in both year, it can be evaluation for that product to gather the promotion.

Top 3 performance by product sub category

Get insight to know the best sellers product in each product category from their top 3 total sales.

So, I decide to write query using row_number() with partition by product_category and gather into subquery and define condition that row number less than equal to 3 (get the top 3)

SELECT product_category, product_sub_category, sales
FROM (
    SELECT product_category, product_sub_category, sum(sales) as sales,
    ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY sum(sales) DESC) as rn
    FROM`sql-project-dqlab.project3.sales_store`
    WHERE order_status='Order Finished' 
    GROUP BY 1,2
    ORDER BY 1
    ) as top_3
WHERE rn <= 3;

Office machines product have the largest sales in overall sales and in technology product category.

Promotion Effectiveness and Efficiency

Promotion by year

Find out the burn rate for analysis of the effectiveness and efficiency of the promotions that have been carried out so far in each year. Calculated of burn rate by comparing the total value of the promotions issued to the total sales obtained. Formula for burn rate :

Burn rate = (total discount / total sales) x 100

SELECT EXTRACT(year FROM order_date) as years, sum(sales) as sales, sum(discount_value) as promotion_value, round(sum(discount_value)*100/sum(sales),2) as burn_rate_percentage 
FROM `sql-project-dqlab.project3.sales_store`
WHERE order_status='Order Finished'
GROUP BY 1;

It shows that in four years have up to 4.5% burn rate and in 2011 have the largest burn rate.

Promotion by product sub category

This analysis same as the previous analysis is burn rate but it contains the product category and sub category.

SELECT EXTRACT(year FROM order_date) as years, product_category, product_sub_category, sum(sales) as sales, sum(discount_value) as promotion_value, round(sum(discount_value)*100/sum(sales),2) as burn_rate_percentage 
FROM `sql-project-dqlab.project3.sales_store`
WHERE order_status='Order Finished'
GROUP BY 1,2,3
ORDER BY 4 DESC;

From that table, it know that the largest sales have the most burn rate in 5.75% means it have higher promotion value.

Customers Analytics

Customers Transactions per Year

Find out number of customers in each year.

SELECT EXTRACT(year FROM order_date) as years, count(distinct customer) as number_of_customer 
FROM `sql-project-dqlab.project3.sales_store`
WHERE order_status='Order Finished'
GROUP BY 1;

It has pattern that in each year was fluctuation. In 2010, number of customer increased from 2009 but decreased in 2011 and up again in 2012. The company was investigate the customers engagement