Northwind Traders Analysis
The purpose of this project is to test my knowledge of data modeling and report design. Therefore, this project accentuates the aforementioned data analysis skills.
Introduction
The Northwind Traders database is originally a fictional sample database used by Microsoft. However, I downloaded this modified version from Maven Analytics data playground. It models a fictitious gourmet food supplier which sells various products such as beverages, confections, meat, seafood, etc to customers.
Data Understanding
The database was already normalized and split into 6 easy-to-understand tables, which were;
Categories — 3 columns and 8 rows
Customers — 6 columns and 91 rows
Employees — 6 columns and 9 rows
Order details — 7 columns and 2,115 rows
Orders — 8 columns and 830 rows
Products — 6 columns and 77 rows
Shippers — 2 columns and 3 rows
To note, the database's earliest and latest dates were July 4, 2013, and June 11, 2015, respectively. This signified the company had a fiscal calendar that starts in July and ends in June. Therefore, the Date table I created was adjusted to start in July for this analysis. Throughout this period, the company has had 2 fiscal years; FY13-FY14 and FY14-FY15.
Data Cleaning
The database had minimum dirty data in all tables. Nulls only appeared in the ShippedDate column in the Orders table, accounting for unshipped orders.
Data Modelling
When I loaded the database onto Power BI, a snowflake model was generated. However, I wanted to create a Date table and establish the correct relationships between the tables.
I created a date table in the Power Query editor, which included columns like Date, Day of Week, Day of Week Order, Fiscal Month, and Fiscal Month Order. In Power BI, I designated this table as the main Date table for the model and removed the pre-existing date hierarchies in all the date columns. To connect the tables, I used the primary key of each table and established the appropriate relationships, including inactive relationships. This was necessary because the Orders table had three date columns that needed to be linked to the single date column in the Date table.
Data Analysis
To analyze the database, I created some measures; net sales, freight paid, shipped orders, on-time delivery rate, MoM growth%, and YoY growth%. You can find detailed documentation on how I created these measures in this GitHub repository.
Since the database contains multiple tables, I had to ask several questions in order to analyze each of them. To make things easier, I organized my questions into five sub-headings.
Overview;
1. What is the net sales?
2. How many orders have been processed?
3. How many products have been sold?
4. What is the total amount of discounts?
5. What is the sales trend over the months?
Products;
1. How many products are in the catalog?
2. What are the top 5 selling products?
3. Which category has the most purchases?
4. How many products have been discontinued?
5. Which category has the most discounted products?
Customers;
1. How many customers have been attended to?
2. In which countries are these customers located?
3. Who are the top 5 customers by net sales?
4. Who are the top 5 customers by products bought and orders?
5. Which country has the most sales?
Shipping;
1. Which shipping company is used the most?
2. How many orders have been shipped and unshipped?
3. How much freight was paid to the shippers?
4. What is the on-time delivery rate?
5. How many products were delivered on time, late, and not shipped?
Employees;
1. How many employees work in the offices?
2. Who is the most active employee in terms of sales made?
3. Which employee has handled the most orders?
4. What are the offices’ order and sales performance?
Get Grace Idiare’s stories in your inbox
Join Medium for free to get updates from this writer.
Data Visualization and Insights
Based on the overview, the total sales after factoring in discounts of approximately $88.67K, amount to $1.27M.
The total number of orders received was 830, with 2155 products sold.
The most sold category(404 products) is Beverages with sales of $267k. Categories like Dairy products and Confections also follow suit showing that most customers ordered mostly beverages and edibles.
The sales in the FY13-FY14 show a mild increase and decrease throughout the months, only peaking in January with net sales of $61k. August records the lowest sales in this fiscal year.
In FY14-FY15, there was a sharp increase in net sales in April of over $124k, almost 3x more than the lowest sales($46k) month which was in November.
The similarity between both fiscal periods is the low sales at the beginning of the fiscal year, that is, between July and September.
With 77 different products in the catalog, 2155 products were sold. The top 5 selling products include Cote de Blaye($141k), Thuringer Rostbratwurst($80k), Raclette Courdavault($71k), Tarte au sucre($47k) and Camembert Pierrot($47k).
There were 8 discontinued products and while Beverages is the most purchased category, it also has the most discontinued products.
So far, there are 91 customers from 21 different countries. The 5 top paying customers are Horst Kloss($110k), Roland Mandel($105k), Jose Pavarotti($104k), Paula Wilson($51k), Patricia McKenna($50k). These customers and Maria Larsson have also ordered the most products.
A large proportion of the company’s sales is from the USA, another large proportion is from a cluster of countries that includes Germany, France, Austria, the UK, Belgium, Italy, and others.
Northwind traders rely on three different shipping companies to get their products to their customers: United Package, Federal Shipping, and Speedy Express. Out of these three, United Package has delivered the most orders and is the most expensive with a freight cost of $64.94k. Although they have a good record of delivering shipments on time (772 orders), there were 37 instances of delayed orders and 21 orders that were not shipped at all. Overall, the on-time delivery rate for all the shipping companies was 92%.
There are currently 9 employees between two offices, with 4 in the UK and 5 in the USA. The UK office has received 224 orders and generated $334k in sales, while the USA office has received 606 orders and generated over $921k in sales.
The employee with the highest sales is Janet Leverling with $80k, while Nancy Davolio has handled the most orders with 21.
I have included all of the visuals in the report I created. The report contains five pages; Overview, Products, Customers, Shipping, and Employees. To interact with it, please click here.’
If you’ve done an analysis on this database before, let me know your thought process in the comments.
You can connect with me on LinkedIn and Twitter.
Thank you for reading!
Grace✨
