classicModels: A Data Analytics Project
Introduction
In today’s world of continuous data generation; the need to derive insights from datasets and harness their value to drive business operations, manage risk, forecast trends, and inform decision-making, the role of a data analyst is highly regarded. As such one of the many prized assets of a data analyst is the ability to work on relational databases. The use of SQL as a tool to execute one’s tasks on structured data highlights a data analyst’s ability in data manipulation, data extraction, and data analysis. By harnessing the power of SQL, a data analyst reveals the value of a dataset addressing key objectives and problem-solving.
This project marks the beginning of my journey as a data analyst, and I look forward to revealing insights from datasets.
Business Overview
The database is for a business named classicModels, and it contains typical business data, including information about customers, employees, products, sales orders, and payments. Classicmodels is a retailer of diecast miniatures, replicas, and scale models of vintage and classic models of vehicles — cars (classic or vintage), motorcycles, planes, ships, trains, trucks and buses; all coming in different scales. This is quite a niche business operation with its market ranging from collectors to vehicle enthusiasts, however, these objects serve well for decorative purposes in homes, schools, and office spaces. Classicmodels transacts business with numerous customers worldwide but operates in offices in Australia, France, Japan, the UK, and the USA. The business team comprises; the President, VP of Sales, VP of Marketing, territorial Sales Managers (NA/EMEA/APAC), and Sales Reps. There is a total of 110 distinct products across all 7 parent productlines:
- 38 products under Classic Cars,
- 24 products under Vintage Cars,
- 13 products under Motorcycles,
- 12 products under Planes,
- 9 products under Ships,
- 3 products under Trains, and
- 11 products under Trucks and Buses.
Classicmodels holds a clientele list of 122 customers spanning 27 countries.
Data Analysis Overview
The dataset can be found at classicModels. The queries for my database analysis can be found at classicModels Queries. Microsoft Excel was helpful for further study and preliminary visualizations. Power BI was used for the project dashboard. The nature of my analysis was both descriptive and diagnostic.
My analysis encompassed the use of various SQL functions and operations for;
data transformation | data retrieval | filtering and sorting | joining tables | aggregation, data grouping & window | creating virtual tables (views) | common table expressions (CTEs)
A key aspect of this project was understanding the structure of the database, for which the Entity Relationship Diagram (ERD) was invaluable. The ERD provided a conceptual framework for the records within the database and how tables and attributes relate and associate with others. This understanding was essential in formulating and executing the SQL queries accurately and efficiently.
The database schema consists of the following entities:
- customers: stores customer’s data.
- products: stores a list of scale model cars.
- productlines: stores a list of product lines.
- orders: stores sales orders placed by customers.
- orderdetails: stores sales order line items for every sales order.
- payments: stores payments made by customers based on their accounts.
- employees: stores employee information and the organization structure such as who reports to whom.
- offices: stores sales office data.
Initial queries were performed for the exploratory analysis phase, to understand and familiarize myself with the dataset. After a period, of further querying the dataset, I formulated a problem statement with key objectives and well-defined questions. From this step, I focused my assignment on the objectives to reveal key business insights.
Problem Statement
ClassicModels, like any typical business venture strives to remain profitable. To achieve this, there is a need for a healthy clientele list, an understanding of market needs, the delivery of good quality products and services, and more importantly product sales. Investigations into the dataset will reveal customer transactions and key customers who account for a large share of sales. Factors influencing these key stakeholders will inform us of strategic business plans and interventions to increase sales.
This project focuses on records dated between January 6th, 2003 to June 31st, 2005.
Project Objective
The goal of this project is to uncover insights in the dataset, utilizing them to inform business decisions that could improve customer relations, increase sales, and ultimately, drive profitability for classicModels. By harnessing the power of SQL and data analysis, I aim to reveal the value of the dataset and utilize it for decision-making.
Analysis Question
The scope of my problem statement helped focus my analysis after the initial analysis conducted in the exploratory phase. Then, further analysis was narrowed down to provide answers and insights to the following questions:
1. What is the performance of sales by share of quantities ordered and revenue generated across productlines?
2. What is the distribution of customers across countries?
3. Sales Revenue performance by transacting customers?
4. How do sales trends vary over time?
Analysis Results and Insights
Key results and insights in the form of charts and notes are provided below addressing each of the analysis questions raised.
Q1: Performance of Sales by Share of Quantities Ordered and Revenue Generated Across ProductLines
With a total of 105,516 in quantities ordered and $ 9,604,190.61 in sales revenue generated, Classic Cars emerged as the best-performing productLine with shares of 33.72% and 40.13% in quantities and revenue respectively. Trains held the last spot with shares of 2.67% and 1.96% in quantities and revenue respectively. Other productLines performed well in both the quantities and revenue metrics, with a possible strategy to boost sales.
Q2: Distribution of Customers Across Countries
Customers with a creditLimit of zero are unable to order products and make transactions. Israel, Netherlands, Poland, Portugal, Russia, and South Africa are countries with absolutely no transactions (Non-Transacting Customers) made by the customers registered to these locations. The more customers are actively transacting the more quantities ordered and sales revenue generated.
Q3: Sales Revenue Performance by Transacting Customers?
Top Transacting Customers across countries with active transactions made, usually ordered a lot more of products thus recording the most in sales revenue. Exceptions such as in the case of customers in Canada and Finland, their respective top Transacting Customers were willing to pay more for quantities of products ordered. These Top Transacting Customers do not have a standout preference for a particular productLine but order across all available productLines.
Q4: Sales trend over Time
Customers conduct all of their transactions within a month each year. However, in October and November, customers usually return to make additional transactions for the year. This is evident from an increase in average customer shopping activities of 6.3 visits for Other Months, compared to 24 visits in October and November. This notable surge in sales for October and November could be attributed to the upcoming festive season, which boosts demand.
Recommendation
Based on my analysis, here are some recommendations:
- Business operations should proactively engage with its vendors to maintain a well-stocked inventory.
- Business operations should proactively engage with its vendors to maintain a well-stocked inventory.
- For countries with no record of transactions (i.e. Israel, Netherlands, Poland, Portugal, Russia, and South Africa) efforts should be made to reach out to customers to inquire about their financial health and interests in products available. If interests are high, we could see how best an arrangement can be made to suit both parties. However, priority should always be given to Transacting Customers in the event of competition and high demand.
- Conduct market research for all productLines but Classic Cars to explore the business opportunity of increasing the number of products within each category.
- Devise a Christmas-themed marketing and sales strategy for the last quarter of the year.
- Explore a pricing strategy or bulk purchasing incentive for key contributors (customers who fell short of the top spot in their various countries).
Conclusion
In conclusion, the analysis of the classicModels database has provided valuable insights into customer transactions, key customers, and factors influencing sales. The use of SQL and data analysis has proven to be effective in revealing the value of the dataset and informing strategic business decisions.
Thank you for taking the time to view this data project. I hope you enjoyed it! Please feel free to comment, share your suggestions and feedback. I am more than happy to learn and improve.
I look forward to working and sharing more projects along my data journey. Feel free to connect with me here or on LinkedIn.
Link to Project: GitHub
The contents of external submissions are not necessarily reflective of the opinions or work of Maven Analytics or any of its team members.
We believe in fostering lifelong learning and our intent is to provide a platform for the data community to share their work and seek feedback from the Maven Analytics data fam.
Submit your own writing here if you’d like to become a contributor.
Happy learning!
-Team Maven
