The goal of this project is to analyze data from a sales records database for a business selling scale model cars and extract information for decision-making.
The Tableau dashboard for this project can be found here.
The database contains eight tables:
- customers: customer data
- employees: all employee information
- offices: sales office information
- orders: customers' sales orders
- orderdetails: sales order line for each sales order
- payments: customers' payment records
- products: a list of scale model cars
- productlines: a list of product line categories
The database schema is shown as follows:
The aim is to optimize the supply and the user experience by preventing the best-selling products from going out-of-stock.
The low stock represents the quantity of each product sold divided by the quantity of product in stock. We can consider the ten lowest rates. These will be the top ten products that are (almost) out-of-stock.
The product performance represents the sum of sales per product.
Priority products for restocking are those with high product performance that are on the brink of being out of stock.
Classic cars are the priority for restocking. They sell frequently, and they are the highest-performance products.
This involves categorizing customers: finding the VIP (very important person) customers and those who are less engaged.
VIP customers bring in the most profit for the store Less-engaged customers bring in less profit.
For example, we could organize some events to drive loyalty for the VIPs and launch a campaign for the less engaged.
The number of clients has been decreasing since 2003, and in 2004, we had the lowest values. The year 2005, which is present in the database as well, isn't present in the table above, this means that the store has not had any new customers since September of 2004. This means it makes sense to spend money acquiring new customers.
To determine how much money we can spend acquiring new customers, we can compute the Customer Lifetime Value (LTV), which represents the average amount of money a customer generates. We can then determine how much we can spend on marketing.
The customer Lifetime Value (LTV) tells us how much profit an average customer generates during their lifetime with our store. We can use it to predict our future profit. So, if we get ten new customers next month, we'll earn 390,395 dollars, and we can decide based on this prediction how much we can spend on acquiring new customers.
- PostgreSQL in pgAdmin 4
- Tableau