Order Dynamics Unveiled: A Strategic Analysis

Order Dynamics Unveiled: A Strategic Analysis of Product Orders

Introduction:

In collaboration with Hongyu Chen, Daskh Kumar, Saumya Seth, Ellen Wang, and myself, we delved into the realm of order data from a business, aiming to unveil the intricate dynamics that govern its product orders. Armed with raw data and an absence of specific directives, our collective goal was to extract compelling insights from the sea of transactions that make up this company's order history. From the initial placement to the final shipment, each order became a narrative waiting to unfold. Our collaborative effort transformed this data into actionable insights, providing a roadmap for strategic decision-making centered around the order-driven heartbeat of this specific business.

Project Background:

Initiated with an extensive dataset capturing the details of product orders, our focus narrowed on understanding the underlying patterns and trends within the specific business's operations. The challenge was not just to analyze but to decode the stories behind each order, offering a comprehensive view of its order fulfillment ecosystem. The synergy among team members, each contributing unique analytical perspectives and skills, allowed us to navigate the complexities of order data effectively. This project showcases our collective ability to discern meaningful patterns within the realm of order dynamics, delivering valuable insights that illuminate the path forward for this specific business.

Data Warehouse

Explore Insights

Explore Dashboard

About Our Data Warehouse

The data warehouse is a key tool that our team prepared for company decision-makers to understand overall company operations, track performance metrics, identify trends and make informed business decisions. Combining detailed orders data with aggregate statistics provides a holistic view of a company's processes from purchasing to selling, enabling stakeholders to optimize operations, strengthen customer relationships, and streamline supply chain management. The data warehouse contains 7 sections, which are listed below:

Orders: Basic order details like order date, shipment date, and the number of days taken to ship. Comprehensive financial information such as unit prices, quantities, discounts, and order subtotals. Shipment details including the ship name, ship address, ship city, postal code, and country.
Products: Information about each product including product name, quantity per unit, units in stock, units on order, reorder levels, and discontinuation status. Association of each product to its category and supplier.
Customers: Personal and contact details of customers like company name, contact name, contact title, address, city, postal code, country, phone, and fax. Analytical data such as the number of orders made by each customer.
Categories: Details about product categories, including category names and descriptions.
Suppliers: Comprehensive supplier data like company name, contact name, contact title, address, city, postal code, country, phone, and fax. Analytical data such as the average total order amount associated with each supplier.
Shippers: Details about each shipper, including their company name and phone number. Analytical data such as the average number of days taken by each shipper to deliver orders.
Employees: Detailed employee profiles, capturing personal and professional details like full name, title of courtesy, birth date, job title, hire date, address, city, postal code, country, home phone, extension, notes, direct reports, and photo paths.

Insights Unveiled

We prepared 8 SQL Queries to extract meaningful insight from these data and they are presented below:

Products ordered the most during the busiest 5 months of the year.

Insight: The ultimate goal of this query is to see which months are your busiest months and also which products to promote and what periods to market them i.e. when they are making the most purchases. If we want to drive sales we may offer special deals and promotions for those products during popular periods. The results snapshot although it only shows the first 25 in the table, we can see that January is one of the busiest months with 5967 total orders, thus we should target advertising of these products in that month. Raclette Courdavault had the most January orders. The month with the most orders on the actual data was April, followed by March, then January, February then December so these months should be targeted as most orders for these products are happening at those times. Analyzing the top periods in each period can help you devise effect marketing campaigns at different times of the year.

Orders whose total order value surpasses the company's median order value.

Insight: The query helps in assessing the performance of individual orders in comparison to the company's historical order data. Orders categorized as "Above Median" may indicate strong sales or higher-value transactions, while those categorized as "Below Median" may require further analysis to understand why they are falling below the median. Upon analyzing the data there is about the same amount of orders below and above the median but more below. The business can focus on retaining both high-value and low-value customers. Customer retention strategies can be customized based on the value they bring to your business. High-value customers may require personalized attention, while low-value customers can benefit from loyalty programs to encourage repeat purchases. Overall, maintaining a balance between orders above and below the median is not necessarily a negative sign. It indicates diversity in your customer base and provides opportunities for strategic optimization and growth.

Orders containing products that have been discontinued

Insight: This data allows us to identify which orders were made with discontinued products. Knowing which products have been discontinued and are still being ordered can help in managing your product lifecycle. It may prompt you to reconsider discontinuing certain products that still have demand. Understanding which discontinued products are still being ordered and who is ordering them can help you manage inventory better. You may need to keep a small stock of these products available to fulfill orders. Customers who continue to order discontinued products may be considered loyal to your brand. You might want to reward their loyalty or offer them alternative products thus having their contact information and product information in one place will be helpful when engaging with them. Given this Data, we can see that 'Berglunds snabbk?' has about 7 orders that contain discontinued products and on two instances they bought the same discontinued product twice ('Guaran?Fant?tica' and 'R?sle Sauerkraut'), thus we as a company must investigate why they continue doing so. Continuing this analysis for all products will allow us to decide whether to recontinue some of those products due to their continued high demand.

Products that have not been ordered within the last 2 months of their last order

Insight: This will help us with inventory management to figure out which orders are losing popularity if they have been ordered within the last 2 months of their last order. This may imply customers may have issues with the products thus we may need to make some inquiries as to whether they are still satisfied with the products. If this product has a very large total revenue, we should prioritize it and resolve its issues so that we can sell more. Results show that there are only 3 products that we need to investigate as they have 3,4,4 months respectively since their last orders. On the other hand, we see that the rest of our inventory is moving as most of them are rebought within 0-1 months thus we are in good standing as a business.

Check if there exists a correlation between the greater order cycle time (i.e. the time between the order being placed and shipped) and the busiest months of the year

Insight: This query uses a binary column to understand if the months with the maximum number of orders have an order cycle (days between orders being placed and shipped) greater than average across the dataset. This can help the company to improve its efficiency as the order cycle impacts customer retention rate.
We observe that there is no pattern that we can establish from the relationship between the number of orders in a month and the average days to ship per month. This indicates the potential for introducing measures to optimize processes across the value chain to minimize the order cycle.

Identify if there exist any regional patterns in the Number of Orders by customers or the Product Preferences of customers

Insight: The above query helps us to understand the reach of the products in cities, across countries. The idea behind formulating this query was to use Segmentation, Targeting, and Positioning in an effective way by understanding the markets (specific regions) that are untapped. This knowledge could help companies customize their product lines and advertising tactics to meet the unique needs of clients in various geographic areas.
- Regional Ordering Patterns: Nations and cities have considerably varied total order numbers. As we can see from the above result, certain nations have significantly more orders than others, including Brazil, Germany, and the USA. Such information about varied demand can also help in better supply chain management, inventory planning decisions, etc.
- Product Preferences by Region: In a similar vein, there are differences in the total quantity of unique products that buyers order between various nations and localities. We can see from the above result that a wide range of product preferences can be found in nations such as France, Germany, and the USA, indicating a wide range of consumer needs and preferences.

Track orders with highest discount percentage and the products that were sold in that order

Insights: Businesses can determine how discounts affect their overall profitability by identifying the orders with the highest percentage of discounts. Insights into the competitive environment can also be gained by keeping an eye on discount patterns and figuring out which products are being sold at the biggest discounts. Businesses who want to stay ahead of the competition in the market might utilise this knowledge to modify their pricing strategy. In the above results we find that more than 35 orders have the exact same discount percentage ie. 0.25. This is despite the fact that most of these orders have different order compositions when we look at the product list. Hence further research into quantity-based discounts, seasonal discounts, etc is imperative to understand the reason behind these figures.

Orders placed but not shipped per year

Insight: The data on placed and unshipped orders provides a clear picture of the effectiveness of the company's order processing. It aids in locating any inefficiencies or obstructions in the order fulfilment or supply chain processes. We used the query to help find orders per year, however, companies can also take a micro view of this problem by analyzing order inefficiencies per month. We have added the product names column to help identify any particular product range inefficiencies that led to unshipped orders. We find that in 1998, there were 21 unshipped orders, though the reason behind them cannot be pinned down, without having access to data that describes the possibility of cancellation of orders from the customers’ end.