Project Data Analysis for B2B Retail: Customer Analytics 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 Sales Growth and creating Customer Analytics
  3. Created the insights for the project

Project Background & Dataset

Project Background

Provide data and analysis regarding the condition of the company in the last month, including sales growth, customer growth,transaction growth, actual active customer, 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 : xyz company’s performance

In this project, xyz.com will review the company’s performance during the last quarter. A data analyst is asked to provide data and analysis regarding the current condition of the company.

The tables that will be used in this project are:

  1. orders_1 table: Contains data related to sales transactions for the 1st quarter (Jan — Mar 2004)
  2. orders_2 table: Contains data related to sales transactions for the 2nd quarter (Apr — Jun 2004)
  3. customer table: Contains profile data of consumers who have registered as xyz.com customers

1.Sales and Revenue Growth



the company’s performance decreased from 1st quarter to 2nd quarter where total sales decreased 22% and revenue decreased 24%.

2.Customer Analytics | Total Customer



the number of new consumers has decreased from 1st quarter to 2nd quarter.

3. Customer Analytics | Customer Transactions



the number of customers who have made transactions has decreased from 1st quarter to 2nd quarter. In 1st quarter, customers who have made transactions are around 58% of total customers registered in 1st quarter and in 2nd quarter, customers who have made transactions are about 54% of total registered customers in 2nd quarter. The total number of new customers who have ordered is only about 56%.

4. Retention Rate Konsumen


customers who have made transactions in 1st quarter and returned to transactions in 2nd quarter are 24%.

Conclusion

Based on the data that we have obtained through SQL queries, we can conclude that:

  1. xyz.com's performance decreased significantly in the 2nd quarter, as seen from the value of sales and revenue which dropped by 20% and 24%,
  2. the acquisition of new customers is also not very good, and slightly decreased compared to the previous quarter.
  3. The interest of new customers to shop at xyz.com is still lacking, only about 56% have made transactions. It is recommended that the Product team need to study customer behavior and make product improvements, so that the conversion rate (register to transaction) can increase.
  4. Product categories S18 and S24 contribute about 50% of total orders and 60% of total sales, so xyz.com should focus on developing categories S18 and S24.
  5. xyz.com's customer retention rate is also very low at only 24%, meaning that many customers who have transacted in the 1st quarter do not return to order in the 2nd quarter (no repeat orders).
  6. xyz.com experienced negative growth in the 2nd quarter and needs to make a lot of improvements both in terms of products and marketing business, if you want to achieve the target and have positive growth in the 3rd quarter. The low retention rate and conversion rate can be an early diagnosis that customers are not interested/unsatisfied/disappointed in shopping at xyz.com.