Northwind Traders

Northwind Traders is a fictious global import and export company that specializes in supplying high-quality gourmet food products to restaurants, cafes, and specialty food retailers around the world.

As a Data Analyst for Northwind Traders, I built this KPI dashboard for the executive team.

This dashboard quickly shows the company's performance through Sales Trends over the years, Top Selling Products, Top Customers, Freight Costs as well as the shipment delays with each shipping company.

The Sales and Order data provided includes information on customers, products, orders, shippers, and employees.

Key Findings

1: Top Customers: Customer with id SAVEA (Save-a-lot-markets) from USA has the maximum number of orders placed at 31, with ERNSH (Ernst Handel) from Austria at 30 and QUICK (Quick-Stop) from Germany at 28. Total of 89 customers have placed orders with Northwind Traders as of the provided dates.

2: Top Employees: Margaret Peacock sold the maximum number of orders at 156, followed by Janet Leverling at 127.

3: Countries with Most Customers: USA has the greatest number of customers at 13 followed by Germany at 11.

SQL Analysis

I used SQL queries to analyze this data with SQL JOINS, Groups, and Aggregate functions, before analyzing it in Tableau and creating the dashboard.

Business Task 1: Find the Company Names with maximum number of orders places with Northwind Traders.

Solution: Customer with id SAVEA (Save-a-lot-markets) from USA has the maximum number of orders placed at 31, with ERNSH (Ernst Handel) from Austria at 30 and QUICK (Quick-Stop) from Germany at 28. Total of 89 customers have placed orders with Northwind Traders as of the provided dates.

SELECT 
    orders.customerID,
    customers.companyName,
    customers.city,
    customers.country,
    COUNT(DISTINCT orders.orderID) AS Number_of_orders
FROM
    orders
        LEFT JOIN
    customers ON customers.customerID = orders.customerID
GROUP BY customers.customerID
ORDER BY Number_of_orders DESC

customerID, companyName, city, country, Number_of_orders

SAVEA, Save-a-lot Markets, Boise, USA, 31

ERNSH, Ernst Handel, Graz, Austria, 30

QUICK, QUICK-Stop, Cunewalde, Germany, 28

Business Task 2: Calculate total price of a product shipment and find the most expensive products based on per shipment cost, in descending order.

Solution: Product ID '39', named as 'Chartreuse verte', priced at '750 cc per bottle', with unit price '18', and a total cost of '13500', is the most expensive shipment item in the list of products.

SELECT 

    order_details.productID,

    products.productName,

    products.quantityPerUnit,

    products.unitPrice,

    (products.quantityPerUnit * products.unitPrice) AS TotalPrice

FROM

    order_details

        LEFT JOIN

    products ON products.productID = order_details.productID

ORDER BY TotalPrice DESC

Business Task 3: Calculate shipping trends based on on-time and late shipments.

Solution: There were total 37 late shipments. 23 days was the longest time difference between the required shipment date and the actual shipment date.

SELECT 
        DateDiff(requiredDate,shippedDate) as Find_late_Shipments

        FROM orders

HAVING
     Find_Late_Shipments < 0

     ORDER BY Find_Late_shipments

Business Task 4: Find the employee with maximum number of orders secured

Solution: Margaret Peacock sold the maximum number of orders at 156, followed by Janet Leverling at 127.

SELECT 

    orders.employeeID,

    employees.employeeName,

    COUNT(orders.orderID) AS Number_of_orders_per_employee

FROM

    orders

        LEFT JOIN

    employees ON orders.employeeID = employees.employeeID

GROUP BY employeeID

ORDER BY Number_of_orders_per_employee DESC

employeeID, employeeName, Number_of_orders_per_employee 4, Margaret Peacock, 156 3, Janet Leverling, 127

Business Task 5: Find the number of customers in each country.

Solution: USA has the greatest number of customers at 13 followed by Germany at 11.

SELECT country, companyName,

COUNT(customerID)as number_of_customers_per_country

from customers

GROUP BY country

ORDER BY number_of_customers_per_country DESC

country, companyName, number_of_customers_per_country

USA, Great Lakes Food Market, 13

Germany, Alfreds Futterkiste, 11

Business Task 6: Find the company with heaviest freight transported.

Solution: United package transported the heaviest freight in total

SELECT 

    shippers.shipperID,

    shippers.companyName,

    SUM(ROUND(orders.freight)) AS total_freight

FROM

    orders

        LEFT JOIN

    shippers ON shippers.shipperID = orders.shipperID

GROUP BY shippers.shipperID

ORDER BY total_freight

shipperID, companyName, total_freight

1, Speedy Express, 16181

3, Federal Shipping, 20511

2, United Package, 28247