15 January 2021

Presentation Structure

  • Project Goals
  • Data Import, Cleaning and Transformation I, II & III
  • Dashboards Rules
  • Dashboards Presentation
  • Insights

Project Goals

  • Identification of main revenue patterns for the management team
  • Analysis of geographical activity

    • One data frame per each dashboard
      • DDS data used for geographical analysis
      • All data used for revenue analysis

Data Import, Cleaning and Transformation I

  • DDS system for four products
    (Innight, Groupage, Pallet and Road Freight)
    • Same structure
    • Same import method (see code)
      • first get list with filenames
      • use lappy, function: read_excel for each filename
    • Results in df with 3.23 million rows and 25 cols
  • Then merging with master file
    (leftjoin, UID= Customer ID)
  • Then merging with ZIP file
    (ZIP’s of DK, SE & NO)

# read  in data
#road
# setwd(".../POSTNORD/DDS/Road_Freight")
# file.list1 <- list.files(pattern='*.xls')
# df.road <- lapply(file.list1, read_excel)
#pallet
# ...same process
#innight
# ...same process
#groupage
# ...same process

#create df
# dfroad <-  do.call(rbind.data.frame, df.road)
# same for dfpallet dfinnight & dfgroupage

#rbind all df
# data <- rbind(dfroad, dfpallet, 
#               dfinnight, dfgroupage)

Data Import, Cleaning and Transformation II

  • Changed data types where necessary (num, int, chr, POSIXct)
  • Handled NA’s
    • Parcel Pallet Type: if NA -> “PLL”
    • SAPNumber: if NA -> “NotSpecified”
    • numeric/integer: if NA -> median
  • Creation of variable Route
    • String: Consignor_City & Consignee City
  • Merging with LON/LAT file for consignor and consignee
    (UID: ZIP code & country code)

Data Import, Cleaning and Transformation III

  • Geographical Dashboard:
    • Focus on DDS data
    • Only analyse non-NA -> complete cases
      • Data loss due to missing LON/LAT data
      • Still huge included data
        (2.23mm rows, 37 cols)

  • Management Dashboard:
    • Import ADS data
      • Clean ADS data (assign country abbreviation, based on ZIP-file)
      • Merge with Master-file
      • Check data types
    • Import old Road data:
      • Check colnames
      • Add R as product type
      • Check data types
    • Rbind ADS, DDS, oldR
      (3.60mm rows, 37 cols)

Dashboards Rules

  • White spaces between graphs and a lot of white in general
  • Easily understandable graphs with a button for additional information
    • No axis legends (make it clear from the title)
  • Connect charts (interactivity)
  • Coherent color selection
    • Focusing on customers blue
      • User-friendly with regards to colorblindness
      • Cooling effect of blue
    • Using lightness steps from low to higher values
    • Possibility of printing dashboards in black-white-schemes
  • …

Geographical Dashboard

Management Dashboard

Insights

  • Geographical Dashboard
    • Analysis level:
      • Yearly-based
      • Location-based
      • Product-based
    • Which industries/subindustries are of particular importance for which route?
    • Which customers are of particular importance for which route?

  • Management Dashboard
    • Analysis level:
      • Yearly-based
    • Which customers are of particular importance for the business?
    • Which products are of particular importance for the business?
    • Which industries are of particular importance for the business?
    • Which trends with regards to turnover and customers, products, industries exist?

Thank you for your attention!