This script will check to make sure each item recieved in on a PO has the correct promo discounts applied to it. It will then use the calculated costs for each sku and the starting inventory from a fixed point in time to calculate what the weighted average cost of each sku was at the time it was picked for an order.
Using nested tables to calculate the Weighted Average Cost of Inventory each time inventory is changed.
I’ll split the promos into Munfacturer promos and Supplier promos. I’ll then expand them out so each day the promo is active it will have its own row. I’ll then split them by types and make sure there are no duplicate rows.
Those will then get left joined with the purchases table (all the PO items) so I can check if the promo was applied correctly in the case of off invoice promos. I’ll also check how much backend money is associated with the product in the case of backend rebates.
load("domoAccessToken")
load("domoCustomer")
DomoR::init(domoCustomer, domoAccessToken)
#Product Promos - Magic ETL
promos <-
fetch("fdab5361-3b57-45f8-9480-0a4e30d89051")
manuPromos <- promos %>%
filter(source_type_name == 'Manufacturer',
date_end > ymd_hms("2018-10-16 18:59:44")) %>%
spread(key = promo_type_name, value = amount)
manuPromosSpreadDates <- manuPromos %>%
filter(is.null(product_promos_id)) %>%
select(-date_start) %>%
rename(date = date_end)
i = 1
for (i in 1:nrow(manuPromos)) {
promoDates = seq(from = manuPromos$date_start[i],
to = manuPromos$date_end[i],
by = 1)
df = tibble(
products_id = manuPromos$products_id[i],
date = promoDates,
mp_name = manuPromos$name[i],
mp_backend = manuPromos$`Backend Rebate`[i],
mp_pos = manuPromos$`POS Promotion`[i],
mp_x_value = manuPromos$x_value[i],
mp_y_value = manuPromos$y_value[i],
mp_tier_type_name = manuPromos$tier_type_name[i]
)
manuPromosSpreadDates <- rbind(manuPromosSpreadDates, df)
i = i + 1
}
manuFixedOffPerUnit <- manuPromosSpreadDates %>%
filter(mp_tier_type_name == '$ Off Per Unit') %>%
select(-mp_tier_type_name,-mp_x_value,-mp_y_value)
# Table of skus with more than one manufactuer POS promo
doubleTrouble <- manuFixedOffPerUnit %>%
select(-mp_backend) %>%
filter(mp_pos != 0) %>%
group_by(products_id, date) %>%
summarise(count = n()) %>%
ungroup() %>%
select(-date) %>%
filter(count > 1) %>%
unique()
# Table of skus with more than one manufacturer backend promo
doubleTrouble2 <- manuFixedOffPerUnit %>%
select(-mp_pos) %>%
filter(mp_backend != 0) %>%
group_by(products_id, date) %>%
summarise(count = n()) %>%
ungroup() %>%
select(-date) %>%
filter(count > 1) %>%
unique()
# Group multiple fixed price off manufacturer promos
manuFixedOffPerUnitGrouped <- manuFixedOffPerUnit %>%
group_by(products_id, date) %>%
summarise(mp_pos = sum(mp_pos),
mp_backend = sum(mp_backend))
manuPctOffPerUnit <- manuPromosSpreadDates %>%
filter(mp_tier_type_name == '% Off Per Unit') %>%
select(-mp_tier_type_name,-mp_x_value,-mp_y_value,-mp_name) %>%
rename(mp_pctOffInvoice = mp_pos,
mp_pctOffBackend = mp_backend)
# Table of skus with more than one manufacturer % off promo
doubleTrouble3 <- manuPctOffPerUnit %>%
group_by(products_id, date) %>%
summarise(count = n()) %>%
ungroup() %>%
select(-date) %>%
filter(count > 1) %>%
unique()
manuBuyXGetY <- manuPromosSpreadDates %>%
filter(mp_tier_type_name == 'Buy X Get Y') %>%
select(-mp_backend, -mp_pos, -mp_tier_type_name, -mp_name)
# Table of skus with more than one manufacturer buy x get y
doubleTrouble4 <- manuBuyXGetY %>%
group_by(products_id, date) %>%
summarise(count = n()) %>%
ungroup() %>%
select(-date) %>%
filter(count > 1) %>%
unique()
### Supplier Promos
supPromos <- promos %>%
filter(source_type_name == 'Supplier',
date_end > ymd_hms("2018-10-16 18:59:44")) %>%
spread(key = promo_type_name, value = amount)
# The specific_quantites by sku for BWI-Expo_2018
bwi18 <- read.csv("BWI_Expo_2018_quantities.csv",
col.names = c("products_id", "specific_quantity"),
stringsAsFactors = FALSE,
strip.white = TRUE)
# Filter for just BWI 2018 and add on the specific_quantities
justBWI18 <- supPromos %>%
filter(grepl("BWI EXPO 2018", name)) %>%
select(-specific_quantity) %>%
merge(bwi18, by = "products_id")
# Filter for not BWI 2018
notBWI18 <- supPromos %>%
filter(!grepl("BWI EXPO 2018", name))
# Row bind them back together
supPromos <- rbind(justBWI18, notBWI18)
supPromosSpreadDates <- supPromos %>%
filter(is.null(product_promos_id)) %>%
select(-date_start) %>%
mutate(date = date_end)
i = 1
for (i in 1:nrow(supPromos)) {
promoDates = seq(from = supPromos$date_start[i],
to = supPromos$date_end[i],
by = 1)
df = tibble(
products_id = supPromos$products_id[i],
date = promoDates,
sp_name = supPromos$name[i],
suppliers_id = supPromos$source_id[i],
sp_pos = supPromos$`POS Promotion`[i],
sp_x_value = supPromos$x_value[i],
sp_y_value = supPromos$y_value[i],
sp_tier_type_name = supPromos$tier_type_name[i],
sp_specific_quantity = supPromos$specific_quantity[i],
sp_date_end = supPromos$date_end[i]
)
supPromosSpreadDates <- rbind(supPromosSpreadDates, df)
i = i + 1
}
supFixedOffPerUnit <- supPromosSpreadDates %>%
filter(sp_tier_type_name == '$ Off Per Unit') %>%
select(
-sp_tier_type_name,
-sp_x_value,
-sp_y_value,
-sp_name,
-sp_specific_quantity,
-sp_date_end
)
# Table of skus with more than one supplier POS promo
doubleTrouble5 <- supFixedOffPerUnit %>%
filter(sp_pos != 0) %>%
group_by(products_id, date) %>%
summarise(count = n()) %>%
ungroup() %>%
select(-date) %>%
filter(count > 1) %>%
unique()
supFixedPrice <- supPromosSpreadDates %>%
filter(sp_tier_type_name == 'Fixed Price') %>%
select(products_id,
date,
suppliers_id,
sp_pos,
sp_specific_quantity,
sp_name,
sp_date_end) %>%
rename(sp_fixed_price = sp_pos)
skusToGetEndDates <- unique(supFixedPrice$products_id)
suppliersCodes <- unique(supFixedPrice$suppliers_id)
#PO Items Lite
purchases <-
DomoR::fetch("b8d4c659-5735-41b7-adb7-b6a42be1aed7")
purchasesOfTrackedSkus <- purchases %>%
filter(products_id %in% skusToGetEndDates,
suppliers_id %in% suppliersCodes) %>%
mutate(date = date(date_received),
products_quantity = qty_received * lot_qty) %>%
group_by(products_id, suppliers_id, date) %>%
summarise(products_quantity = sum(products_quantity))
supFixedPrice <- merge(
supFixedPrice,
purchasesOfTrackedSkus,
by = c('products_id', 'date', 'suppliers_id'),
all.x = TRUE
)
runningTtl <- function(df) {
cumsum(df$products_quantity)
}
supFPbyName <- supFixedPrice %>%
select(sp_name,
products_id,
date,
sp_specific_quantity,
products_quantity,
sp_date_end) %>%
mutate(products_quantity = ifelse(is.na(products_quantity),
0,
products_quantity)) %>%
group_by(products_id, date, sp_name, sp_specific_quantity, sp_date_end) %>%
summarise(products_quantity = sum(products_quantity)) %>%
ungroup() %>%
group_by(sp_name) %>%
nest() %>%
mutate(cusum = map(data, runningTtl)) %>%
unnest() %>%
mutate(active = cusum < sp_specific_quantity)
endDates <- supFPbyName %>%
filter(active == TRUE) %>%
group_by(sp_name, products_id) %>%
summarise(endDate = max(date))
supFixedPriceActive <- supFixedPrice %>%
merge(supFPbyName %>%
select(-sp_specific_quantity,
-products_quantity,
-sp_date_end),
by = c('sp_name', 'date', 'products_id')) %>%
filter(active == TRUE)
# Table of skus with more than one supplier fixed price promo (overlap of 2018 and 2019 BWI)
doubleTrouble6 <- supFixedPriceActive %>%
group_by(products_id, date, suppliers_id) %>%
summarise(count = n()) %>%
ungroup() %>%
select(-date) %>%
filter(count > 1) %>%
select(products_id, count) %>%
unique()
# Newer promos for the same sku for same supplier overwrite old ones of same if old one is still active
skusToOverride <- supFixedPriceActive %>%
group_by(products_id, date, suppliers_id) %>%
summarise(number = n(),
sp_date_end = min(sp_date_end)) %>%
ungroup()
supFixedPriceNumber2AreDups <- supFixedPriceActive %>%
merge(
skusToOverride,
by = c('products_id', 'date', 'suppliers_id', 'sp_date_end'),
all.x = TRUE
)
supFixedPriceClean <- supFixedPriceNumber2AreDups %>%
filter(number != 2) %>%
select(products_id, date, suppliers_id, sp_fixed_price)
# Put them all together
purchasesWithPromos <- purchases %>%
mutate(
date = date(date_received),
products_quantity = qty_received * lot_qty,
po_unit_cost = round(unit_cost / lot_qty, 2),
base_unit_cost = base_unit_cost / lot_qty,
promo_discount = promo_discount / lot_qty,
calc_unit_cost = round(base_unit_cost - promo_discount, 2),
diff_costs = po_unit_cost != calc_unit_cost
) %>%
merge(
manuFixedOffPerUnitGrouped,
by = c('products_id', 'date'),
all.x = TRUE
) %>%
merge(manuPctOffPerUnit,
by = c('products_id', 'date'),
all.x = TRUE) %>%
merge(manuBuyXGetY,
by = c('products_id', 'date'),
all.x = TRUE) %>%
merge(
supFixedOffPerUnit,
by = c('products_id', 'date', 'suppliers_id'),
all.x = TRUE
) %>%
merge(
supFixedPriceClean,
by = c('products_id', 'date', 'suppliers_id'),
all.x = TRUE
) %>%
select(
-supplier_sku,
-admin_id,
-date_expected,
-reason_description,
-invoices_id,
-lot_types_id,
-order_by,
-exclude_from_fill_rate,
-received_location,
-original_qty_ordered
) %>%
mutate(
notes = ifelse(is.na(notes), '', notes),
mp_pos = ifelse(is.na(mp_pos), 0, mp_pos),
mp_backend = ifelse(is.na(mp_backend), 0, mp_backend),
mp_pctOffBackend = ifelse(is.na(mp_pctOffBackend), 0, mp_pctOffBackend),
mp_pctOffInvoice = ifelse(is.na(mp_pctOffInvoice), 0, mp_pctOffInvoice),
mp_x_value = ifelse(is.na(mp_x_value), 0, mp_x_value),
mp_y_value = ifelse(is.na(mp_y_value), 0, mp_y_value),
sp_pos = ifelse(is.na(sp_pos), 0, sp_pos),
sp_fixed_price = ifelse(is.na(sp_fixed_price), 0, sp_fixed_price)
)
if (nrow(purchasesWithPromos) != nrow(purchases)) {
print("You've got double trouble")
break
}
# Take out the trash
tablesInMem <- ls()
tablesInMem <- tablesInMem[!tablesInMem %in% c("purchasesWithPromos",
"domoCustomer",
"domoAccessToken")]
rm(list = tablesInMem)
#create(purchasesWithPromos, "Purchases With Promos", "All PO items since 10/12/2017 with promos added")
replace_ds("4fc7104b-9bb1-4cc1-8f0f-2e5dbb31aad7", purchasesWithPromos)purchasesSel <- purchasesWithPromos %>%
rename(date_time = date_received,) %>%
mutate(
backend_per_unit = (mp_pctOffBackend / 100) * po_unit_cost + mp_backend,
po_unit_cost = po_unit_cost - backend_per_unit
) %>%
select(
po_items_id,
products_id,
warehouses_id,
date_time,
products_quantity,
po_unit_cost
) %>%
mutate(
weightedAvgCost = NA,
pick_products_id = rep_len(0, length(date_time)),
products_history_id = rep_len(0, length(date_time)),
starting_inventory_id = rep_len(0, length(date_time))
)#Orders Products Pick Lite
picks <-
DomoR::fetch("7540a427-9f55-4af4-8ca0-f5af807ec466")
picksSel <- picks %>%
filter(products_picked == 1,
products_picked_time > ymd_hms("2018-10-16 18:59:44")) %>%
select(
pick_products_id,
products_id,
products_quantity,
warehouses_id,
products_picked_time
) %>%
rename(date_time = products_picked_time) %>%
mutate(
products_quantity = products_quantity * -1,
po_unit_cost = rep_len(0, length(date_time)),
weightedAvgCost = NA,
po_items_id = rep_len(0, length(date_time)),
products_history_id = rep_len(0, length(date_time)),
starting_inventory_id = rep_len(0, length(date_time))
)#Products History Lite
adjustments <-
DomoR::fetch("7b561bef-e909-4c7b-8ec2-a6fca2bae723")
adjustmentsSel <- adjustments %>%
filter(date_added > ymd_hms("2018-10-16 18:59:44")) %>%
select(
products_history_id,
products_id,
products_history_quantity,
warehouses_id,
date_added
) %>%
rename(date_time = date_added,
products_quantity = products_history_quantity) %>%
mutate(
po_unit_cost = rep_len(0, length(date_time)),
weightedAvgCost = NA,
pick_products_id = rep_len(0, length(date_time)),
po_items_id = rep_len(0, length(date_time)),
starting_inventory_id = rep_len(0, length(date_time))
)#First Day of Tracked Inventory
startingInventory <-
DomoR::fetch("86502dfd-0d7a-47e7-98ee-b9a11bffbdc3")
inventorySel <- startingInventory %>%
filter(products_quantity > 0) %>%
select(products_id,
products_quantity,
warehouses_id,
inventory_date_time,
products_cost) %>%
rename(date_time = inventory_date_time,
weightedAvgCost = products_cost) %>%
mutate(
po_unit_cost = rep_len(0, length(date_time)),
pick_products_id = rep_len(0, length(date_time)),
po_items_id = rep_len(0, length(date_time)),
products_history_id = rep_len(0, length(date_time)),
starting_inventory_id = seq(1, length(date_time))
)runningTtl <- function(df) {
cumsum(df$products_quantity)
}
wgtAvg <- function(df) {
x = numeric(length = length(df$weightedAvgCost))
x[1] = df$weightedAvgCost[1]
if (is.na(df$weightedAvgCost[1])) {
if (!is.na(df$po_unit_cost[1])) {
x[1] = df$po_unit_cost[1]
}
}
if (length(df$weightedAvgCost) > 1) {
for (i in 2:length(df$weightedAvgCost)) {
if (!is.na(df$weightedAvgCost[i])) {
x[i] = df$weightedAvgCost[i]
}
if (is.na(df$weightedAvgCost[i]) &
df$po_items_id[i] == 0) {
x[i] = x[i - 1]
}
if (is.na(df$weightedAvgCost[i]) &
df$po_items_id[i] != 0) {
x[i] = (
ifelse(is.na(
df$n[i - 1] * x[i - 1]
), 0, df$n[i - 1] * x[i - 1]) + (
df$products_quantity[i] * df$po_unit_cost[i]
)
) / sum(df$n[i - 1],
df$products_quantity[i],
na.rm = TRUE)
}
}
}
return(x)
}inventoryCost <- flow %>%
group_by(products_id, warehouses_id) %>%
nest() %>%
mutate(n = map(data, runningTtl)) %>%
unnest() %>%
ungroup() %>%
mutate(n = ifelse(n > 0, n, 0)) %>%
group_by(products_id, warehouses_id) %>%
nest() %>%
mutate(newWeightedAvgCost = map(data, wgtAvg)) %>%
unnest() %>%
mutate(weightedAvgCost = newWeightedAvgCost,
inventoryValue = weightedAvgCost * n) %>%
select(-newWeightedAvgCost) %>%
rename(inventory = n) %>%
arrange(products_id, warehouses_id, date_time) %>%
mutate(products_id = as.integer(products_id),
warehouses_id = as.integer(warehouses_id),
inventory = as.integer(inventory),
products_quantity = as.integer(products_quantity),
pick_products_id = as.integer(pick_products_id),
po_items_id = as.integer(po_items_id),
products_history_id = as.integer(products_history_id),
starting_inventory_id = as.integer(starting_inventory_id))