Skip to content
0

Supply Chain Analytics in Tableau or Power BI

📖 Background

Test your BI skills on a real-world dataset focusing on supply chain analytics. As the main data analyst for Just In Time, you will help solve key shipment and inventory management challenges, analyze supply chain inefficiencies, and create insightful dashboards to inform business stakeholders about potential problems and propose structural business improvements.

Be creative and make use of your full skillset! Use this Workspace to prepare your data, import the tables into your local Tableau or Power BI instance, and share your insights below.

The end goal will be a (set of) interactive dashboards that demonstrate clear insights for Just In Time.

💾 The data

GroupColumn nameDatasetDefinition
CustomerCustomer IDorders_and_shipments.csvUnique customer identification
CustomerCustomer Marketorders_and_shipments.csvGeographic grouping of customer countries, with values such as Europe, LATAM, Pacific Asia, etc.
CustomerCustomer Regionorders_and_shipments.csvGeographic grouping of customer countries, with values such as Northern Europe, Western Europe, etc.
CustomerCustomer Countryorders_and_shipments.csvCustomer's country
Order infoOrder IDorders_and_shipments.csvUnique Order identification. Order groups one or multiple Order Items
Order infoOrder Item IDorders_and_shipments.csvUnique Order Item identification. Order Item always belong to just one Order
Order infoOrder Yearorders_and_shipments.csvYear of the order
Order informationOrder Monthorders_and_shipments.csvMonth of the order
Order informationOrder Dayorders_and_shipments.csvDay of the order
Order informationOrder Timeorders_and_shipments.csvTimestamp of the order in UTC
Order informationOrder Quantityorders_and_shipments.csvThe amount of items that were ordered within a given Order Item (1 record of the data)
ProductProduct Departmentorders_and_shipments.csvProduct grouping into categories such as Fitness, Golf, Pet Shop, etc.
ProductProduct Categoryorders_and_shipments.csvProduct grouping into categories such as Sporting Goods, Women's Apparel, etc.
ProductProduct Nameorders_and_shipments.csvThe name of the purchased product
SalesGross Salesorders_and_shipments.csvRevenue before discounts generated by the sales of the Order Item (1 record of the data)
SalesDiscount %orders_and_shipments.csvDiscount % applied on the catalog price
SalesProfitorders_and_shipments.csvProfit generated by the sales of the Order Item (1 record of data)
Shipment informationShipment Yearorders_and_shipments.csvYear of the shipment
Shipment informationShipment Monthorders_and_shipments.csvMonth of the shipment
Shipment informationShipment Dayorders_and_shipments.csvDay of the shipment
Shipment informationShipment Modeorders_and_shipments.csvInformation on how the shipment has been dispatched, with values as First Class, Same Day, Second Class, etc.
Shipment informationShipment Days - Scheduledorders_and_shipments.csvInformation on typical amount of days needed to dispatch the goods from the moment the order has been placed
WarehouseWarehouse Countryorders_and_shipments.csvCountry of the warehouse that has fulfilled this order, the only two values being Puerto Rico and USA
Inventory & FulfillmentWarehouse Inventoryinventory.csvThe monthly level of inventory of a product, e.g. 930 units
Inventory & FulfillmentInventory cost per unitinventory.csvThe monthly storage cost per unit of inventory, e.g. $2.07
Inventory & FulfillmentWarehouse Order fulfillment (days)fulfillment.csvThe average amount of days it takes to refill stock if inventory drops below zero

The data can be downloaded from the sidebar on the left (under Files).

💪 Challenge

Using either Tableau or Power BI, create an interactive dashboard to summarize your research. Things to consider:

  1. Use this Workspace to prepare your data (optional).
  2. Some ideas to get you started: visualize how shipments are delayed, by country, product, and over time. Analyze products by their supply versus demand ratio. Rank products by over or understock. Don't feel limited by these, you're encouraged to use your skills to consolidate as much information as possible.
  3. Create a screenshot of your (main) Tableau or Power BI dashboard, and paste that into the designated field.
  4. Summarize your findings in an executive summary.
from scipy import stats
from matplotlib import pyplot
import matplotlib.pyplot as plt
#import schedule
import time
from datetime import datetime, timedelta
import pandas as pd
import numpy as np
import seaborn as sns
import random
import warnings
fullfillment_df = pd.read_csv('data/fulfillment.csv')
print(fullfillment_df.head())

inventory_df = pd.read_csv('data/inventory.csv')
print(inventory_df.head())
print(inventory_df.info())

orders_ship_df = pd.read_csv('data/orders_and_shipments.csv')
print(orders_ship_df.head())
print(inventory_df['Product Name'].unique())
print(len(inventory_df['Product Name'].unique()))

print(inventory_df[' Year Month '].unique())
print(len(inventory_df[' Year Month '].unique()))
inventory_df[' Year Month '] = pd.to_datetime(inventory_df[' Year Month '], format='%Y%m')
inventory_df = inventory_df.sort_values(by=' Year Month ')
# df['dates'] = pd.to_datetime(df['dates'])

Invertory deki productlar nasıl degisiyor zamana göre önce görsel olarak bakmak istedim Outputu hideladım 118 tane product icinde sonuc basıyor cünkü görmek isterseniz cell in sol kösesindeki 3 noktalı işaretten show output diyip bakabilirsiniz

for i in inventory_df['Product Name'].unique():
    deneme = inventory_df[inventory_df['Product Name'] == i]
    g = sns.relplot(data=deneme, kind='line', x=' Year Month ', y=' Warehouse Inventory ', height=2.5, aspect=2, linewidth=4)
    #g.set(yticks=[1, 3, 5, 7, 10, 13, 15])
    g.set_xticklabels(rotation=90)
    #plt.axhline(y=b, color='red')
    g.fig.suptitle(i)
    plt.legend()
    plt.show()
Hidden output

ürünlerin mean ve medianlarına bakmak istedim teker teker

def centralTendency(df):
    print('Mean: ', df.mean())
    print('Median: ', df.median())

for i in inventory_df['Product Name'].unique():
    deneme = inventory_df[inventory_df['Product Name'] == i]
    print(i)
    centralTendency(deneme[' Warehouse Inventory '])

grafiklerden anlasıldıgı üzere, bazı ürünler sık kullanılmıyor gibi, aylardaki ürün sayılarını compare ederek hangi ürünler sık kullanılıyor onları bulmaya çalıştım

change_count = []
product_name = [] 
#Production oldugu günlerdeki count sayısı yani bize toplam arac sayısını verir mi 

for k in inventory_df['Product Name'].unique():
    count = 0
    deneme = inventory_df[inventory_df['Product Name'] == k]
    for i in range(len(deneme)-1):
        #print(i)
        z = i + 1
        if (deneme[' Warehouse Inventory '].iloc[i]) != (deneme[' Warehouse Inventory '].iloc[z]):
            count = count + 1
        else:
            count = count
    change_count.append(count)
    product_name.append(k)       
change_product = pd.DataFrame()

change_product = pd.DataFrame(list(zip(product_name, change_count)), columns = ['product', 'number_of_days_count'])    
change_product = change_product.sort_values(by='number_of_days_count', ascending=False)
change_product = change_product.reset_index()

print(change_product.head(10))
print(' ')
print(change_product.tail(10))

Değişim oranlarının dağılım grafiğine bakarak hangi ürünleri seçebileceğimize karar verebiliriz

‌
‌
‌