library(tidyverse)
library(lubridate)
library(DomoR)
load("domoCustomer")
load("domoAccessToken")
init(domoCustomer, domoAccessToken)
orders_products_split_kits <-
  fetch("6bd884cc-0abf-4d98-8927-3a6a1903eec6")
#str(orders_products_split_kits)
#table(orders_products_split_kits$delivery_state)

delivery_state <- unique(orders_products_split_kits$delivery_state)
delivery_state <- as.data.frame(delivery_state) %>%
  mutate(
    UT_State = delivery_state %in% c(
      'Arizona',
      'California',
      'Colorado',
      'Hawaii',
      'Idaho',
      'Montana',
      'Nevada',
      'North Dakota',
      'New Mexico',
      'Oregon',
      'South Dakota',
      'Utah',
      'Washington',
      'Wyoming'
    )
  ) %>%
  arrange(UT_State, delivery_state) %>%
  mutate(warehouses_id = ifelse(UT_State == TRUE, 2, 1)) %>%
  select(-UT_State)

orders_with_warehouses <-
  left_join(orders_products_split_kits, delivery_state) %>%
  mutate(products_volume = products_length * products_width * products_height) %>%
  select(-products_length,-products_width,-products_height)

missingDims <- orders_with_warehouses %>%
  filter(products_volume == 0) %>%
  group_by(products_id) %>%
  summarise(products_quantity = sum(products_quantity))

write.csv(missingDims, file = "skus missing dims.csv")

Inventory

Merge Inventory

Shipments

This is how they actually shipped

Add on actual shipping warehouse

Add logic

Put it in Domo

Postal Zones

The commented out code changes the US postal services matrix into a usable format and saves it as a csv.

# zones_raw <- readxl::read_xlsx('postal_zones.xlsx')
#
# # Postal zones for ATL
# zones_atl <- filter(zones_raw, From == '30093', replace == 0)
#
# i = 2
# dataList = list()
# for (i in seq(1, length(zones_atl$start_zip))) {
#   zips = seq(as.integer(zones_atl$start_zip[i]), as.integer(zones_atl$end_zip[i]))
#   zips = str_pad(zips, width = 5, side = 'left', pad = "0")
#   r = length(zips)
#   zips = as.data.frame(zips) %>%
#     mutate(zone = rep(zones_atl$Zone[i], r)) %>%
#     rename(postal_code = zips)
#   dataList[[i]] <- zips
# }
# zones_full_atl <- do.call(rbind, dataList)
#
# replaces_atl <- filter(zones_raw, From == '30093', replace == 1)
# dataList = list()
# for (i in seq(1, length(replaces_atl$start_zip))) {
#   zips = seq(as.integer(replaces_atl$start_zip[i]), as.integer(replaces_atl$end_zip[i]))
#   zips = str_pad(zips, width = 5, side = 'left', pad = "0")
#   r = length(zips)
#   zips = as.data.frame(zips) %>%
#     mutate(zone = rep(replaces_atl$Zone[i], r)) %>%
#     rename(postal_code = zips)
#   dataList[[i]] <- zips
# }
#
# replacements_atl <- do.call(rbind, dataList) %>% rename(new_zone = zone)
#
# postal_zones_atl <- merge(zones_full_atl, replacements_atl, all = T) %>%
#   mutate(zone = ifelse(is.na(new_zone), zone, new_zone)) %>%
#   select(-new_zone)
#
# # Postal zones for SLC
# zones_slc <- filter(zones_raw, From == '84003', replace == 0)
#
# i = 2
# dataList = list()
# for (i in seq(1, length(zones_slc$start_zip))) {
#   zips = seq(as.integer(zones_slc$start_zip[i]), as.integer(zones_slc$end_zip[i]))
#   zips = str_pad(zips, width = 5, side = 'left', pad = "0")
#   r = length(zips)
#   zips = as.data.frame(zips) %>%
#     mutate(zone = rep(zones_slc$Zone[i], r)) %>%
#     rename(postal_code = zips)
#   dataList[[i]] <- zips
# }
# zones_full_slc <- do.call(rbind, dataList)
#
# replaces_slc <- filter(zones_raw, From == '84003', replace == 1)
# dataList = list()
# for (i in seq(1, length(replaces_slc$start_zip))) {
#   zips = seq(as.integer(replaces_slc$start_zip[i]), as.integer(replaces_slc$end_zip[i]))
#   zips = str_pad(zips, width = 5, side = 'left', pad = "0")
#   r = length(zips)
#   zips = as.data.frame(zips) %>%
#     mutate(zone = rep(replaces_slc$Zone[i], r)) %>%
#     rename(postal_code = zips)
#   dataList[[i]] <- zips
# }
#
# replacements_slc <- do.call(rbind, dataList) %>% rename(new_zone = zone)
#
# postal_zones_slc <- merge(zones_full_slc, replacements_slc, all = T) %>%
#   mutate(zone = ifelse(is.na(new_zone), zone, new_zone)) %>%
#   select(-new_zone)
#
# rm(list = c('zones_atl', 'zones_slc', 'zones_raw', 'zones_full', 'zips', 'dataList', 'i', 'r',
#             'replacements_atl', 'replacements_slc', 'replaces_atl', 'replaces_slc', 'zones_full_atl',
#             'zones_full_slc'))

postal_zones_atl <- read.csv('postal_zones_atl.csv')[,-1] %>%
  mutate(postal_code = str_pad(
    as.character(postal_code),
    width = 5,
    side = 'left',
    pad = '0'
  )) %>%
  mutate(mail_from_atl_zone = as.integer(zone)) %>%
  select(-zone)

postal_zones_slc <- read.csv('postal_zones_slc.csv')[,-1] %>%
  mutate(postal_code = str_pad(
    as.character(postal_code),
    width = 5,
    side = 'left',
    pad = '0'
  )) %>%
  mutate(mail_from_slc_zone = as.integer(zone)) %>%
  select(-zone)

fedex_zones_atl <- readxl::read_xlsx('FEDEX Origin 30093.xlsx') %>%
  select(-S_Zip,-TnT_GND) %>%
  rename(postal_code = R_Zip) %>%
  mutate(fedex_from_atl_zone = as.integer(Zone_GND)) %>%
  select(-Zone_GND)

fedex_zones_slc <-
  readxl::read_xlsx('FEDEX of Origin 84003.xlsx') %>%
  select(-S_Zip,-TnT_GND) %>%
  rename(postal_code = R_Zip) %>%
  mutate(fedex_from_slc_zone = as.integer(Zone_GND)) %>%
  select(-Zone_GND)

zones <- merge(postal_zones_atl, postal_zones_slc, all.x = T) %>%
  merge(fedex_zones_atl, all.x = T) %>%
  merge(fedex_zones_slc, all.x = T) %>%
  unique() %>%
  mutate(
    mail_from_atl_zone = ifelse(mail_from_atl_zone == 1, 2, mail_from_atl_zone),
    mail_from_slc_zone = ifelse(mail_from_slc_zone == 1, 2, mail_from_slc_zone)
  )

#keep <- c('zones', 'fin')
#rm(list = setdiff(ls(), keep))

Munge data to be able to add shipping cost estimates

Read in shipping cost files

Group into shipments and add costs

ops_grouped <- ops %>%
  select(
    orders_id,
    products_id,
    mdy,
    alt_warehouse,
    primary_warehouse,
    website_id,
    ups_res_com,
    shpd_from_prim,
    in_stock_in_prim,
    in_stock_in_alt,
    ttl_weight,
    mail_from_atl_zone,
    mail_from_slc_zone,
    fedex_from_atl_zone,
    fedex_from_slc_zone,
    warehouses_id,
    service_type,
    products_volume
  ) %>%
  group_by(
    orders_id,
    mdy,
    alt_warehouse,
    primary_warehouse,
    website_id,
    ups_res_com,
    shpd_from_prim,
    in_stock_in_prim,
    mail_from_atl_zone,
    mail_from_slc_zone,
    fedex_from_atl_zone,
    fedex_from_slc_zone,
    warehouses_id,
    service_type
  ) %>%
  summarise(ttl_weight = sum(ttl_weight),
            ttl_volume = sum(products_volume)) %>%
  ungroup() %>%
  mutate(ttl_weight_oz = ttl_weight * 16,
         ttl_volume_cu_ft = ttl_volume / 1728) %>%
  mutate(
    rounded_weight_lbs = ceiling(ttl_weight),
    rounded_weight_oz = ceiling(ttl_weight_oz)
  ) %>%
  filter(ttl_weight != 0,!is.na(ttl_weight))

under1lbs <- filter(ops_grouped, ttl_weight < 1) %>%
  merge(
    usps_costs,
    by.x = c('mail_from_atl_zone', 'rounded_weight_oz'),
    by.y = c('zone', 'weight_oz'),
    all.x = T
  ) %>%
  rename(usps_from_atl_cost_per_lbs = cost_per_lbs) %>%
  select(-weight_lbs) %>%
  merge(
    usps_costs,
    by.x = c('mail_from_slc_zone', 'rounded_weight_oz'),
    by.y = c('zone', 'weight_oz'),
    all.x = T
  ) %>%
  rename(usps_from_slc_cost_per_lbs = cost_per_lbs) %>%
  select(-weight_lbs) %>%
  merge(
    fedex_costs,
    by.x = c('fedex_from_atl_zone', 'rounded_weight_lbs'),
    by.y = c('zone', 'weight'),
    all.x = T
  ) %>%
  rename(fedex_from_atl_cost_per_lbs = cost_per_lbs) %>%
  merge(
    fedex_costs,
    by.x = c('fedex_from_slc_zone', 'rounded_weight_lbs'),
    by.y = c('zone', 'weight'),
    all.x = T
  ) %>%
  rename(fedex_from_slc_cost_per_lbs = cost_per_lbs) %>%
  mutate(num_pkgs = 1)

over1lbs <-
  filter(ops_grouped, ttl_weight < 70, ttl_weight >= 1) %>%
  merge(
    usps_costs,
    by.x = c('mail_from_atl_zone', 'rounded_weight_lbs'),
    by.y = c('zone', 'weight_lbs'),
    all.x = T
  ) %>%
  rename(usps_from_atl_cost_per_lbs = cost_per_lbs) %>%
  select(-weight_oz) %>%
  merge(
    usps_costs,
    by.x = c('mail_from_slc_zone', 'rounded_weight_lbs'),
    by.y = c('zone', 'weight_lbs'),
    all.x = T
  ) %>%
  rename(usps_from_slc_cost_per_lbs = cost_per_lbs) %>%
  select(-weight_oz) %>%
  merge(
    fedex_costs,
    by.x = c('fedex_from_atl_zone', 'rounded_weight_lbs'),
    by.y = c('zone', 'weight'),
    all.x = T
  ) %>%
  rename(fedex_from_atl_cost_per_lbs = cost_per_lbs) %>%
  merge(
    fedex_costs,
    by.x = c('fedex_from_slc_zone', 'rounded_weight_lbs'),
    by.y = c('zone', 'weight'),
    all.x = T
  ) %>%
  rename(fedex_from_slc_cost_per_lbs = cost_per_lbs) %>%
  mutate(num_pkgs = 1)

usps_costs <-
  usps_costs %>% mutate(weight_lbs = ceiling(weight_lbs))

over70lbs <- filter(ops_grouped, ttl_weight >= 70) %>%
  mutate(
    num_pkgs = ceiling(ttl_weight / 70),
    rnd_weight_new = ceiling(rounded_weight_lbs / num_pkgs)
  ) %>%
  merge(
    usps_costs,
    by.x = c('mail_from_atl_zone', 'rnd_weight_new'),
    by.y = c('zone', 'weight_lbs'),
    all.x = TRUE
  ) %>%
  rename(usps_from_atl_cost_per_lbs = cost_per_lbs) %>%
  mutate(usps_from_atl_cost_per_lbs = usps_from_atl_cost_per_lbs * num_pkgs) %>%
  select(-weight_oz) %>%
  merge(
    usps_costs,
    by.x = c('mail_from_slc_zone', 'rnd_weight_new'),
    by.y = c('zone', 'weight_lbs'),
    all.x = TRUE
  ) %>%
  rename(usps_from_slc_cost_per_lbs = cost_per_lbs) %>%
  mutate(usps_from_slc_cost_per_lbs = usps_from_slc_cost_per_lbs * num_pkgs) %>%
  select(-weight_oz) %>%
  merge(
    fedex_costs,
    by.x = c('fedex_from_atl_zone', 'rnd_weight_new'),
    by.y = c('zone', 'weight'),
    all.x = TRUE
  ) %>%
  rename(fedex_from_atl_cost_per_lbs = cost_per_lbs) %>%
  mutate(fedex_from_atl_cost_per_lbs = fedex_from_atl_cost_per_lbs * num_pkgs) %>%
  merge(
    fedex_costs,
    by.x = c('fedex_from_slc_zone', 'rnd_weight_new'),
    by.y = c('zone', 'weight'),
    all.x = TRUE
  ) %>%
  rename(fedex_from_slc_cost_per_lbs = cost_per_lbs) %>%
  mutate(fedex_from_slc_cost_per_lbs = fedex_from_slc_cost_per_lbs * num_pkgs) %>%
  select(-rnd_weight_new)

addCubes <- rbind(under1lbs, over1lbs) %>%
  rbind(over70lbs) %>%
  mutate(usps_cubes_rounded = ceiling(ttl_volume_cu_ft * 10) / 10) %>%
  merge(
    cubic_costs,
    by.x = c("mail_from_atl_zone", "usps_cubes_rounded"),
    by.y = c("zone", "Cubic"),
    all.x = T
  ) %>%
  rename(cubic_cost_from_atl = cost) %>%
  mutate(cubic_cost_from_atl = ifelse(is.na(cubic_cost_from_atl), 99999, cubic_cost_from_atl)) %>%
  merge(
    cubic_costs,
    by.x = c("mail_from_slc_zone", "usps_cubes_rounded"),
    by.y = c("zone", "Cubic"),
    all.x = T
  ) %>%
  rename(cubic_cost_from_slc = cost) %>%
  mutate(cubic_cost_from_slc = ifelse(is.na(cubic_cost_from_slc), 99999, cubic_cost_from_slc))

withCosts <- addCubes %>%
  mutate(ups_res_com = ifelse(is.na(ups_res_com), 'Y', ups_res_com)) %>%
  mutate(residential_surcharge = ifelse(ups_res_com != 'N', residential_surcharge, 0)) %>%
  mutate(
    fedex_from_atl_cost_per_lbs = fedex_from_atl_cost_per_lbs + residential_surcharge,
    fedex_from_slc_cost_per_lbs = fedex_from_slc_cost_per_lbs + residential_surcharge
  ) %>%
  mutate(
    usps_from_atl_cost_per_lbs = ifelse(
      is.na(usps_from_atl_cost_per_lbs),
      99999,
      usps_from_atl_cost_per_lbs
    ),
    usps_from_slc_cost_per_lbs = ifelse(
      is.na(usps_from_slc_cost_per_lbs),
      99999,
      usps_from_slc_cost_per_lbs
    ),
    fedex_from_atl_cost_per_lbs = ifelse(
      is.na(fedex_from_atl_cost_per_lbs),
      99999,
      fedex_from_atl_cost_per_lbs
    ),
    fedex_from_slc_cost_per_lbs = ifelse(
      is.na(fedex_from_slc_cost_per_lbs),
      99999,
      fedex_from_slc_cost_per_lbs
    )
  ) %>%
  mutate(
    usps_atl_cost = usps_from_atl_cost_per_lbs,
    usps_slc_cost = usps_from_slc_cost_per_lbs,
    fedex_atl_cost = fedex_from_atl_cost_per_lbs,
    fedex_slc_cost = fedex_from_slc_cost_per_lbs
  ) %>%
  mutate(recommended_shipping_method_prim = ifelse(
    primary_warehouse == 1,
    ifelse(
      usps_atl_cost > fedex_atl_cost,
      ifelse(cubic_cost_from_atl > fedex_atl_cost,
             'fedex', 'cubic'),
      ifelse(cubic_cost_from_atl > usps_atl_cost,
             'usps', 'cubic')
    ),
    ifelse(
      usps_slc_cost > fedex_slc_cost,
      ifelse(cubic_cost_from_slc > fedex_slc_cost,
             'fedex', 'cubic'),
      ifelse(cubic_cost_from_slc > usps_slc_cost,
             'usps', 'cubic')
    )
  )) %>%
  mutate(recommended_shipping_method_alt = ifelse(
    alt_warehouse == 1,
    ifelse(
      usps_atl_cost > fedex_atl_cost,
      ifelse(cubic_cost_from_atl > fedex_atl_cost,
             'fedex', 'cubic'),
      ifelse(cubic_cost_from_atl > usps_atl_cost,
             'usps', 'cubic')
    ),
    ifelse(
      usps_slc_cost > fedex_slc_cost,
      ifelse(cubic_cost_from_slc > fedex_slc_cost,
             'fedex', 'cubic'),
      ifelse(cubic_cost_from_slc > usps_slc_cost,
             'usps', 'cubic')
    )
  )) %>%
  mutate(cost_from_prim = ifelse(
    primary_warehouse == 1,
    ifelse(
      usps_atl_cost < fedex_atl_cost,
      ifelse(
        cubic_cost_from_atl < usps_atl_cost,
        cubic_cost_from_atl,
        usps_atl_cost
      ),
      ifelse(
        cubic_cost_from_atl < fedex_atl_cost,
        cubic_cost_from_atl,
        fedex_atl_cost
      )
    ),
    ifelse(
      usps_slc_cost < fedex_slc_cost,
      ifelse(
        cubic_cost_from_slc < usps_slc_cost,
        cubic_cost_from_slc,
        usps_slc_cost
      ),
      ifelse(
        cubic_cost_from_slc < fedex_slc_cost,
        cubic_cost_from_slc,
        fedex_slc_cost
      )
    )
  )) %>%
  mutate(cost_from_alt = ifelse(
    alt_warehouse == 1,
    ifelse(
      usps_atl_cost < fedex_atl_cost,
      ifelse(
        cubic_cost_from_atl < usps_atl_cost,
        cubic_cost_from_atl,
        usps_atl_cost
      ),
      ifelse(
        cubic_cost_from_atl < fedex_atl_cost,
        cubic_cost_from_atl,
        fedex_atl_cost
      )
    ),
    ifelse(
      usps_slc_cost < fedex_slc_cost,
      ifelse(
        cubic_cost_from_slc < usps_slc_cost,
        cubic_cost_from_slc,
        usps_slc_cost
      ),
      ifelse(
        cubic_cost_from_slc < fedex_slc_cost,
        cubic_cost_from_slc,
        fedex_slc_cost
      )
    )
  )) %>%
  mutate(cost_from_actual = ifelse(
    warehouses_id == 1,
    ifelse(
      usps_atl_cost < fedex_atl_cost,
      ifelse(
        cubic_cost_from_atl < usps_atl_cost,
        cubic_cost_from_atl,
        usps_atl_cost
      ),
      ifelse(
        cubic_cost_from_atl < fedex_atl_cost,
        cubic_cost_from_atl,
        fedex_atl_cost
      )
    ),
    ifelse(
      usps_slc_cost < fedex_slc_cost,
      ifelse(
        cubic_cost_from_slc < usps_slc_cost,
        cubic_cost_from_slc,
        usps_slc_cost
      ),
      ifelse(
        cubic_cost_from_slc < fedex_slc_cost,
        cubic_cost_from_slc,
        fedex_slc_cost
      )
    )
  )) %>%
  filter(
    !is.na(fedex_from_atl_zone),
    service_type == 'Fedex Ground' |
      service_type == 'Priority Mail' |
      service_type == 'FedEx Home Delivery' |
      service_type == 'First Class'
  )

Put it in Domo

Summary

## [1] -10287.15
## [1] 573
## [1] -7801.17
## [1] -512.8
## [1] 585
## [1] -1821.13