The goal of this project is to determine profitability by sku.
The picks table will correspond to the data table in Domo which is the product of an ETL the orders_products_picked table and a calculated weighted average cost of inventory table. I can make the ETL run by sending the inventoryFlow table up to Domo. It usually takes around 2min to load into Domo so the pause is for 5min to be safe.
I’ll load packages and source the inventoryFlow script.
## NULLNow that the weighted average cost of inventory for each product at the time of each warehouse pick is appended to the orders_products_picked table I’ll download all the tables for the project from Domo.
load('domoCustomer')
load('domoAccessToken')
init(domoCustomer, domoAccessToken)
#OPP_with_weightedAvgCost
picks <- fetch('a0e64422-9972-4c00-bbfa-65f8686993fa')
#bundles_append
kits <- fetch('e5b70b86-056b-44a3-aa73-ce813263c120')
#orders_products_retail_cal_lite
products <- fetch('07da13d1-7547-46be-9637-14362ab9084a')
#Product_Costs_Append (for drop-ships only)
costs <- fetch('0d8dc831-9a74-4785-880f-5cfdc35f640d')
#OTWC_Lite
totals <- fetch('770b8d4c-be83-40a3-b726-0070574b3019')
#shipments_lite
shipments <- fetch('a5844699-431d-485b-9fd1-e30926df4fee') Select the columns we want to use from the picks table. Group by orders_id and prodcuts_id. Summerise products_quantity as sum, products_weight by mean and weightedAvgCost by mean.
The weightedAvgCost here should probably be summerised by the weighted average instead of the mean but since the costs should be the same the mean should work.
# Make the picks table
picksSel <- picks %>%
select(orders_id,
products_id,
products_quantity,
products_weight,
weightedAvgCost) %>%
group_by(orders_id, products_id) %>%
summarise(
products_quantity = sum(products_quantity),
products_weight = mean(products_weight),
weightedAvgCost = mean(weightedAvgCost)
) %>%
mutate(products_ttl_weight = products_weight * products_quantity,
products_ttl_cost = products_quantity * weightedAvgCost) %>%
rename(qtyPicked = products_quantity)Next up is the orders_prodcuts table which will be called productsSel here. The first step will be to calculate the extended_products_price and extended_final_price for each sku.
After that we will replace the products_id of any product that has a products_parent_id with the products_parent_id.
If the sku has a products_parent_id and it has a case amount greater than 0 the products_quantity should be changed to equal the products_quantity * products_case_amount.
Once the new quantities are calculated the products_price and final_price should be changed so the price per unit is correct for the new number of units.
productsSel <- products %>%
mutate(
extended_price = products_price * products_quantity,
ext_fin_price = final_price * products_quantity
) %>%
mutate(
products_id = ifelse(products_parent_id > 0, products_parent_id, products_id),
products_quantity = ifelse(
products_case_amt > 0 & products_parent_id > 0,
products_quantity * products_case_amt,
products_quantity
),
) %>%
mutate(
products_price = ifelse(
products_case_amt > 0 &
products_price != 0 &
products_parent_id > 0,
extended_price / products_quantity,
products_price
),
final_price = ifelse(
products_case_amt > 0 & final_price != 0 & products_parent_id > 0,
ext_fin_price / products_quantity,
final_price
)
) %>%
select(-products_case_amt,
-products_is_case,
-extended_price,
-ext_fin_price) %>%
group_by(orders_id, products_id, kit, orders_status) %>%
summarise(
products_quantity = sum(products_quantity),
products_price = mean(products_price),
final_price = mean(final_price),
date_purchased = max(date_purchased),
mdy = max(mdy)
) %>%
ungroup()
# If everything is correct these two should match.
#sum(products$final_price * products$products_quantity)
#sum(productsSel$final_price * productsSel$products_quantity)Since kits are listed as their own sku in the products table but listed as the components of the kit in the picks table we need to transform the products table to take out the kit rows and replace them with the kit contents. In doing so we need to spread the price of the kit based on the costs of the items in the kit.
First we’ll make a list of all the orders that have a kit in them. Then create an empty data frame to hold the kits.
For each orders_id with a kit in it we’ll find the components of the kit in the bundles table. The bundles table can change over time so we’ll make sure we’re matching the date of the order with the date of the bundle unless the order is from a time before we started appending the bundles list by day.
I’ll flag the kit items as 2 and leave the kits flagged as 1 so we can filter out the kits skus later.
# List of all kits on orders
kitSales <- productsSel %>%
filter(kit == 1)
# Empty dataframe to append kits to
kitProductsbyOrder <- data.frame()
# Assemble the kits
for (i in seq(1:length(kitSales$orders_id))) {
kit = kits %>%
filter(bundle_id == kitSales$products_id[i],
#started appending 2/5/19 so this will be then or after
mdy == max(kitSales$mdy[i],
ymd("2019-02-05"),
na.rm = T)) %>%
rename(
kit = bundle_id,
products_id = subproduct_id,
products_quantity = subproduct_qty
) %>%
select(kit,
products_id,
products_quantity,
products_parent_id) %>%
mutate(
orders_id = kitSales$orders_id[i],
date_purchased = kitSales$date_purchased[i],
mdy = kitSales$mdy[i],
products_price = NA,
final_price = NA,
orders_status = kitSales$orders_status[i],
kit = ifelse(kit > 1, 2, kit)
)
kitProductsbyOrder = rbind(kitProductsbyOrder, kit)
}All the kits we made get tacked onto the end of the productsSel dataframe.
# Bind the kits onto the orders_products df
productsSel <- productsSel %>%
rbind(kitProductsbyOrder %>% select(-products_parent_id)) %>%
arrange(orders_id, products_id) %>%
group_by(orders_id,
products_id,
date_purchased,
mdy,
kit,
orders_status) %>%
summarise(products_quantity = sum(products_quantity),
products_price = mean(products_price),
final_price = mean(final_price)) %>%
ungroup()
# Check to make sure the extended price is still close
#sum(products$final_price * products$products_quantity)
#sum(productsSel$final_price * productsSel$products_quantity, na.rm = T)I’ll join the two tables now by orders_id and products_id.
We need to work on the orders where the order includes a kit. Here we will filter the joined list so we have a dataframe with only kits and one without kits. We’ll also get the list of all orders that include a kit to itterate over.
# Filter the merged list for orders that have kits on them. Kit items have been
# coded with 2 in the created rows. And the kits themselves
# will be coded 1 from the orders_products table.
justKitsPicksWithProducts <- picksWithProducts %>%
filter(kit > 0)
noKitsPicksWithProducts <- picksWithProducts %>%
filter(kit == 0)Here we’ll split the prices of the kits by the items in the kits proportionally to the costs of the items in the kits. We’ll append them onto an empty data frame created by filtering for NA orders_id which don’t exist.
# Vector of orders with kits in them
kitOrderIDs <- justKitsPicksWithProducts$orders_id %>%
unique()
# Empty df to hold the assigned prices from costs (this makes and empty frame)
kitsSpread <- justKitsPicksWithProducts %>%
filter(is.na(orders_id))
# Split the prices based on costs
for (i in kitOrderIDs) {
kitSpread = justKitsPicksWithProducts %>%
filter(orders_id == i) %>%
mutate(
products_price = ifelse(
!is.na(products_price),
products_price,
(
sum(products_price, na.rm = T) * products_ttl_cost / sum(products_ttl_cost, na.rm = T)
) / products_quantity
),
final_price = ifelse(
!is.na(final_price),
final_price,
(
sum(final_price, na.rm = T) * products_ttl_cost / sum(products_ttl_cost, na.rm = T)
) / products_quantity
)
) %>%
filter(kit != 1)
kitsSpread = rbind(kitsSpread, kitSpread)
}Drop-ships apparently have no qty picked because of that we have a price but no cost. This will join on the cost listed in the inventory table for the product for the matching date.
Since this wasn’t being tracked for this purpose before 2/5/19 I’ll use the costs on that date for drop ships which occured in the past.
I’ll bind those onto a data frame which includes only the skus where the qtyPicked != NA.
missingPicks1 <- priceAndCost %>%
filter(is.na(qtyPicked)) %>%
left_join(., costs, by = c("products_id", "mdy")) %>%
filter(products_weight.y != 0) %>%
mutate(products_quantity = ifelse(is.na(products_quantity), 0, products_quantity),
products_price = ifelse(is.na(products_price), 0, products_price),
qtyPicked = products_quantity,
products_weight = products_weight.y,
weightedAvgCost = products_cost,
products_ttl_weight = products_weight * products_quantity,
products_ttl_cost = weightedAvgCost * products_quantity) %>%
select(-products_weight.x, -products_weight.y, -products_cost) %>%
filter(products_quantity != 0 & qtyPicked != 0)
costsDate1 <- costs %>%
filter(mdy == min(mdy))
missingPicks2 <- priceAndCost %>%
filter(is.na(qtyPicked),
mdy < ymd("2019-02-05")) %>%
left_join(., costsDate1, by = c("products_id")) %>%
mutate(products_quantity = ifelse(is.na(products_quantity), 0, products_quantity),
products_price = ifelse(is.na(products_price), 0, products_price),
qtyPicked = products_quantity,
products_weight = products_weight.y,
weightedAvgCost = products_cost,
products_ttl_weight = products_weight * products_quantity,
products_ttl_cost = weightedAvgCost * products_quantity) %>%
select(-products_weight.x, -products_weight.y, -mdy.y, -products_cost) %>%
rename(mdy = mdy.x) %>%
filter(products_quantity != 0 & qtyPicked != 0)
orders_product_and_picks <- priceAndCost %>%
filter(!is.na(qtyPicked)) %>%
rbind(missingPicks1) %>%
rbind(missingPicks2) %>%
mutate(products_price = ifelse(is.na(products_price), 0, products_price),
final_price = ifelse(is.na(final_price), 0 , final_price)) %>%
mutate(extended_price = ifelse(final_price > 0,
final_price * products_quantity,
products_price * products_quantity)) %>%
arrange(orders_id, products_id)We’ll group together orders with multiple shipments to calculate the total box cost and total shipping cost. That will get bound onto the totals data set. I’ll also drop the fields from the totals data set that aren’t used or aren’t necessary.
# Group shipments by orders_id to get the total shipping cost and the box cost
shipmentCosts <- shipments %>%
select(orders_id, shipments_id, box_cost, total_shipment_cost) %>%
group_by(orders_id, shipments_id) %>%
summarise(box_cost = max(box_cost),
shipping_cost = max(total_shipment_cost)) %>%
ungroup() %>%
group_by(orders_id) %>%
summarise(box_cost = sum(box_cost),
shipping_cost = sum(shipping_cost)) %>%
ungroup()
# Join shipping and box costs to orders_total
totalsAndShipCosts <- totals %>%
left_join(shipmentCosts, by = 'orders_id') %>%
select(-date_purchased, -orders_total, -Subtotal, -Returned_Items,
-Handling, -Handling_Fee, -Referral, -Refund, -Custom, -Custom2,
-Custom3, -Custom4)# Functions to split by weight
ShippingChargeSplit <- function(df) {
x = numeric(length = length(df$products_ttl_weight))
for (i in 1:length(x)) {
x[i] = (df$products_ttl_weight[i]/sum(df$products_ttl_weight)) * df$Shipping[i]
}
return(x)
}
BoxCostSplit <- function(df) {
x = numeric(length = length(df$products_ttl_weight))
for (i in 1:length(x)) {
x[i] = (df$products_ttl_weight[i]/sum(df$products_ttl_weight)) * df$box_cost[i]
}
return(x)
}
ShippingCostSplit <- function(df) {
x = numeric(length = length(df$products_ttl_weight))
for (i in 1:length(x)) {
x[i] = (df$products_ttl_weight[i]/sum(df$products_ttl_weight)) * df$shipping_cost[i]
}
return(x)
}
# Functions to split by extended price
TaxSplit <- function(df) {
x = numeric(length = length(df$extended_price))
for (i in 1:length(x)) {
x[i] = (df$extended_price[i]/sum(df$extended_price)) * df$Tax[i]
}
return(x)
}
CouponSplit <- function(df) {
x = numeric(length = length(df$extended_price))
for (i in 1:length(x)) {
x[i] = (df$extended_price[i]/sum(df$extended_price)) * df$Coupon[i]
}
return(x)
}
DiscountSplit <- function(df) {
x = numeric(length = length(df$extended_price))
for (i in 1:length(x)) {
x[i] = (df$extended_price[i]/sum(df$extended_price)) * df$Discount[i]
}
return(x)
}
Misc_RefundSplit <- function(df) {
x = numeric(length = length(df$extended_price))
for (i in 1:length(x)) {
x[i] = (df$extended_price[i]/sum(df$extended_price)) * df$Misc_Refund[i]
}
return(x)
}
AddonSplit <- function(df) {
x = numeric(length = length(df$extended_price))
for (i in 1:length(x)) {
x[i] = (df$extended_price[i]/sum(df$extended_price)) * df$Addon_Charge[i]
}
return(x)
}
CharitySplit <- function(df) {
x = numeric(length = length(df$extended_price))
for (i in 1:length(x)) {
x[i] = (df$extended_price[i]/sum(df$extended_price)) * df$Charity[i]
}
return(x)
}# Split the totals by the products in each order. Shipping charges and costs
# should be split on weight, all other charges should be split by price.
costsAssigned <- mergedProductsAndTotals %>%
group_by(orders_id) %>%
nest() %>%
mutate(ShippingChargeSplit = map(data, ShippingChargeSplit),
BoxCostSplit = map(data, BoxCostSplit),
ShippingCostSplit = map(data, ShippingCostSplit),
TaxSplit = map(data, TaxSplit),
CouponSplit = map(data, CouponSplit),
DiscountSplit = map(data, DiscountSplit),
Misc_RefundSplit = map(data, Misc_RefundSplit),
AddonSplit = map(data, AddonSplit),
CharitySplit = map(data, CharitySplit)) %>%
unnest() %>%
select(-Shipping, -Tax, -Coupon, -Discount, -Misc_Refund, -Addon_Charge,
-Charity, -box_cost, -shipping_cost) %>%
mutate(ShippingChargeSplit = if_else(is.na(ShippingChargeSplit), 0, as.numeric(round(ShippingChargeSplit, 2))),
BoxCostSplit = if_else(is.na(BoxCostSplit), 0, as.numeric(round(BoxCostSplit, 2))),
ShippingCostSplit = if_else(is.na(ShippingCostSplit), 0, as.numeric(round(ShippingCostSplit, 2))),
TaxSplit = if_else(is.na(TaxSplit), 0, as.numeric(round(TaxSplit, 2))),
CouponSplit = if_else(is.na(CouponSplit), 0, as.numeric(round(CouponSplit, 2))),
DiscountSplit = if_else(is.na(DiscountSplit), 0, as.numeric(round(DiscountSplit, 2))),
Misc_RefundSplit = if_else(is.na(Misc_RefundSplit), 0, as.numeric(round(Misc_RefundSplit, 2))),
AddonSplit = if_else(is.na(AddonSplit), 0, as.numeric(round(AddonSplit, 2))),
CharitySplit = if_else(is.na(CharitySplit), 0, as.numeric(round(CharitySplit, 2))),
revenue = extended_price + ShippingChargeSplit - CouponSplit - DiscountSplit - Misc_RefundSplit + AddonSplit,
revenue = if_else(is.na(revenue), 0, as.numeric(round(revenue, 2))),
cost = products_ttl_cost + ShippingCostSplit + BoxCostSplit,
cost = if_else(is.na(cost), 0, as.numeric(round(cost, 2))))
#sum(costsAssigned$extended_price)
#sum(costsAssigned$final_price * costsAssigned$products_quantity)Here I’ll push the final table up to Domo and also save this r-markdown file as an r-script file.