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_lite
inventory <- fetch("8f590587-6427-4498-8923-7c7e6481e46c")
inventorySel <- inventory %>%
select(products_id, warehouses_id, inventory_date, products_quantity) %>%
rename(inventory = products_quantity) %>%
group_by(products_id, warehouses_id, inventory_date) %>%
summarise(inventory = mean(inventory))op_inv_at_tgt_warehouse <- merge(
orders_with_warehouses,
inventorySel,
by.x = c("products_id", "mdy", "warehouses_id"),
by.y = c("products_id", "inventory_date", "warehouses_id"),
all.x = TRUE
)
op <- op_inv_at_tgt_warehouse %>%
rename(primary_warehouse = warehouses_id,
primary_warehouse_inv = inventory) %>%
mutate(alt_warehouse = 1) %>%
merge(
inventorySel,
by.x = c("products_id", "mdy", "alt_warehouse"),
by.y = c("products_id", "inventory_date", "warehouses_id"),
all.x = TRUE
) %>%
rename(alt_warehouse_inv = inventory)This is how they actually shipped
#shipments_lite
shipments <- fetch('a5844699-431d-485b-9fd1-e30926df4fee')
shipmentsSel <- shipments %>%
select(orders_id, warehouses_id, date_shipped, service_type) %>%
filter(warehouses_id != 0) %>%
group_by(orders_id, warehouses_id, service_type) %>%
summarise(date_shipped = max(date_shipped)) %>%
ungroup()
multipleWarehouses <- shipmentsSel %>%
select(-service_type) %>%
group_by(orders_id) %>%
summarise(warehouses_shipped_from = mean(warehouses_id)) %>%
mutate(is_mult_whs = warehouses_shipped_from != 1.0 &
warehouses_shipped_from != 2.0) %>%
select(-warehouses_shipped_from)
shp <-
merge(shipmentsSel,
multipleWarehouses,
by = 'orders_id',
all.x = T) %>%
mutate(warehouses_id = ifelse(is_mult_whs == T, 1, warehouses_id)) %>%
select(-is_mult_whs) %>%
group_by(orders_id, warehouses_id, service_type) %>%
summarise(date_shipped = max(date_shipped)) %>%
ungroup()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))ops <- fin %>%
mutate(postal_code = str_sub(delivery_postcode, start = 1, end = 5)) %>%
select(-delivery_postcode) %>%
mutate(products_weight = ifelse(products_weight_new > 0, products_weight_new, products_weight)) %>%
select(-products_weight_new) %>%
mutate(ttl_weight = products_weight * products_quantity) %>%
merge(zones, all.x = T)
#rm(fin)
#rm(zones)fedex_costs <- readxl::read_xlsx('fedex_costs.xlsx')
residential_surcharge <- 2.09
fedex_costs <- fedex_costs %>%
gather(
key = zone,
value = cost_per_lbs,
zone_2,
zone_3,
zone_4,
zone_5,
zone_6,
zone_7,
zone_8
) %>%
mutate(zone = as.integer(str_replace(zone, 'zone_', '')))
usps_costs <- readxl::read_xlsx('usps_costs.xlsx') %>%
gather(
key = zone,
value = cost_per_lbs,
zone_2,
zone_3,
zone_4,
zone_5,
zone_6,
zone_7,
zone_8
) %>%
mutate(zone = as.integer(str_replace(zone, 'zone_', '')))
cubic_costs <- readxl::read_xlsx('cubic_costs.xlsx') %>%
gather(
key = zone,
value = cost,
zone_2,
zone_3,
zone_4,
zone_5,
zone_6,
zone_7,
zone_8
) %>%
mutate(zone = as.integer(str_replace(zone, 'zone_', '')))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'
)shpdFromAltWStockInPrim <- withCosts %>%
filter(shpd_from_prim == F,
in_stock_in_prim == T) %>%
select(orders_id)
thoseOrders <- withCosts %>%
filter(orders_id %in% shpdFromAltWStockInPrim$orders_id) %>%
select(orders_id, cost_from_prim, cost_from_actual, num_pkgs) %>%
group_by(orders_id) %>%
summarise(
new_shp_cost = sum(cost_from_prim),
calc_actual = sum(cost_from_actual),
num_pkgs_new = sum(num_pkgs)
) %>%
ungroup()
act_shp_costs <- shipments %>%
group_by(orders_id) %>%
summarise(act_shp_cost = mean(total_shipment_cost),
num_pkgs_actual = n())
new_and_old_costs <- thoseOrders %>%
merge(act_shp_costs, all.x = T) %>%
mutate(
cost_diff = new_shp_cost - act_shp_cost,
diff_from_calc_actual = new_shp_cost - calc_actual,
num_pkgs_diff = num_pkgs_new - num_pkgs_actual
)
sum(new_and_old_costs$cost_diff)## [1] -10287.15## [1] 573## [1] -7801.17write.csv(new_and_old_costs, file = 'shp_cost_ests.csv')
single_box <- new_and_old_costs %>%
filter(num_pkgs_actual == 1 & num_pkgs_new > 1)
sum(single_box$cost_diff)## [1] -512.8## [1] 585## [1] -1821.13