Northwind Traders Analysis

Project Goal:

My project's primary objective was to leverage sales and order data from Northwind Traders, a fictitious gourmet supplier. This dataset included essential information about customers, products, orders, shipping companies, and employees. Analysis aimed to achieve the following key objectives:

Sales Trends Analysis: Tracking sales trends

Product Performance: Ranking best and worst products

Customer Profiling: Identifying key customers

Shipping Cost Assessment: Evaluating cost consistency

The dataset used for this analysis included comprehensive order details spanning from July 2013 to the beginning of May 2015.

Project Steps:

Data Preparation: After acquiring CSV files from Maven Data Playground, I established a Power BI connection. Next, I profiled the data for quality, structure, and distribution and performed data cleaning in Power Query Editor. No significant data cleaning was necessary, except for enhancing the custom Calendar dimension table.

Data Modeling: In this step, I created relationships between dimension tables (Calendar, Categories, Customers, Employees, Products, Shippers, and Orders) and the fact table (Order Details) in a snowflake schema.

DAX Creation: One of the most crucial steps involved creating DAX measures to generate new insights. For further efficiency, I organized all measures within a Measure table, categorizing them into folders based on their purpose in subsequent analyses.

Data Visualization: The final step was to visualize the imported data and the DAX measures I created, highlighting trends and insights aligned with the project's objectives.

Report Structure and Key Insights:

Analysis Overview: The report comprises four pages. Page one highlights trends defined by project goals, including NET Revenue, quantities, number of orders, and freight costs from both delivered and open orders. It also showcases trends over time in revenue and freight, along with the list of the top-performing customers and their three best-selling products. Additionally, it identifies the top and bottom-performing products, all measured by revenue. Additional pages are reserved for Freight Cost Analysis, Product and Shipping Performance Overview

Unfulfilled Orders Focus: Despite the dataset encompassing order details from July 2013 to the beginning of May 2015, it's important to note that there are orders from April and May 2015 that haven't been delivered according to the available dataset. I've placed special attention on these undelivered orders. Open orders without confirmed delivery dates have been extracted from totals such as NET revenue, orders, freight costs, and quantity units. These are displayed in separate visuals. While undelivered orders contain all data like the completed ones, I've excluded them from the completed orders as they may introduce potential losses due to cancellations or shipment delays.

Positive Revenue Trend: NET revenue exhibits a consistent upward trend over the years, culminating in its highest value in the last completed month, April 2015, at $142,901 USD.

During April 2015, the total freight costs amounted to $7,737 USD, with 4,880 units delivered and 74 orders successfully completed.

Interestingly, this is the only completed month in the dataset with open orders; all other months are completed but less impressive.

Notably, 15.7% of NET Revenue in this period is attributed to the best-selling product, "Cote de Blaye," as we will explore in more detail later in the analysis.

Top Customer Performance: The leading customer, QUICK-Stop, achieved a remarkable total NET Revenue of $110,000 USD. Save-a-lot Markets and Ernst Handel follow closely behind. Notably, QUICK-Stop reached its highest revenue in February 2015, with $17,926 USD, while in the top-performing month of April 2015, it generated $9,162 USD in NET Revenue.

April 2015 Standout: Both the second and third top customers on the revenue list also reached their peak performance in April 2015. Save-a-lot Markets played a significant role in this, contributing 13.5% of the revenue during that standout month.

Top 3 Customer Impact: The top three customers collectively accounted for 25% of the total revenue over the years. To emphasize their substantial impact on overall sales, I've incorporated visual representations of these percentages, allowing users to view exact revenue amounts in tooltips.

Top-Selling Product: Cote de Blaye takes the lead in revenue due to its high price, even though it falls in the middle of the best performers in terms of delivered units. Conversely, Chocolate, the lowest-selling product, owes its performance to being one of the cheapest and sold in small quantities. If we focus solely on revenue, the greatest opportunities lie in boosting the sales of Cote de Blaye and other high-unit-price products. Cote de Blaye achieved its peak revenue performance in February 2015, generating $33.6K USD, contributing to 29.2% of the total revenue. Over a three-year period, the top three best-selling products accounted for 23.6% of the revenue.

Dominant Product Category: The largest product category, Beverages, with $262.57K USD, owes its first-place position primarily to Cote de Blaye, which contributed a significant 53.9% share to that result, even though it's the only product from this category in the top 10 by revenue.

Freight Cost Analytics: Three shipping companies handle all deliveries. United Package excels in several key areas. It contributed to the deliveries that generated NET revenue of $517K USD and executed 315 orders, transporting 19.2 units. Notably, United Package also incurred the highest freight costs per order and, in total, had the highest freight costs among the shippers.

Freight Cost Impact: In comparison to the total revenue, freight costs account for 5.2%. Speedy Express has the smallest share of freight costs and the lowest average cost per order.

Growing Freight Costs: Over the years, freight costs have paralleled the growth trends in the business, reaching their peak at $7,737 USD in April 2015.

Shipping Efficiency Overview: In the shipping performance section, we observe that, on average, shippers took 8.49 days to deliver shipments over the entire 3-year period. Interestingly, 5.37% of revenue is attributed to 37 orders delivered with an average delay of 6.38 days. I have paid special attention to the average delay days in comparison to the average delivery days and the NET revenue generated from these orders.

Company Comparison: In terms of shipping performance, United Package, the largest shipping company, presents the poorest results in average delivery days (9.23 days) and the highest number of delayed orders (16 orders taking an average of 5.56 days to deliver). Conversely, Federal Shipping emerges as the top performer in this regard, with the fewest delayed orders and the shortest average delay period (9 orders with a 5.56-day delay) and the fastest delivery time of all shippers, averaging 7.47 days. This area offers significant room for improvement, particularly because the busiest shipping company currently lags behind in performance.