Introduction

This report will be a walk-through of the method used to calculate product level profit. It will show how costs are split across items at the warehouse pick level and then consolidated back into the order level.

Throughout this report when I’m referring to the price we’ve paid for a product I’ll call it a cost and when referring to the price we’ve charged a customer I’ll call it price.

Data

All the data for this project is from our internal mySQL server. It has been pulled into Domo where various ETLs were performed. We use the DomoR plugin to pull it down into R for this report.

Methodology

The flow of the project is as follows:

  1. Define the cost of products in inventory
    * Figure out which promotions applied to which products and calculate the costs after the promotions
    * Build a table showing the cost of each product each day it was picked

  2. Match the costs of products to the prices of products
    * Get all products to the parent level of the product
    * Split kits into the items in the kits

  3. Calculate non-product costs and prices
    * Merge shipments to the order level to get shipping costs and box costs
    * Use the orders_total table for:
    - Tax
    - Coupon
    - Discounts
    - Charity
    - Shipping charges
    - Refunds
    * Estimate fees based on payment type

  4. Split the costs and charges by weight or price to all items in the order

  5. Calculate product parent level revenue and cost
    * revenue = extended_price + ShippingChargeSplit + AddonSplit
    - CouponSplit - DiscountSplit - Misc_RefundSplit - feesSplit
    * cost = products_ttl_cost + ShippingCostSplit + BoxCostSplit

  6. The table up to here is than pushed to Domo as “Costs Assigned”

  7. Bind these calculated fields onto the orders products table (after splitting the kits) and group to the orders products level to get the second table “costsAssigned_orders_products” which is also passed to Domo.

Defining the Cost of Products in Inventory

Costs change over time so the current cost from our supplier isn’t necessarily the cost we’ve paid for the inventory in the warehouse. In order to account for these fluctuations the first thing we need to calculate is the weighted average cost of the products in inventory.

Promotions

Suppliers and manufacturers often offer promotions that are “on the backend.” That is to say they aren’t reflected on the invoice from the supplier but rather paid at some later date based on some conditions.

It is also possible for them to give promotions that extend retroactivly. In the future they will be on the invoice but they were not on the invoice in previous times when they should have been.

If we want to nail down our the cost of a product at any point in time both of these types of promotions need to be taken into account.

library(DomoR)
library(tidyverse)
library(lubridate)

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") %>%
  rename(products_id = `Our SKU`,
         specific_quantity = `UNITS ON ORDER`)

# 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
}

#create(purchasesWithPromos, "Purchases With Promos", "All PO items since 10/12/2017 with promos added")
replace_ds("4fc7104b-9bb1-4cc1-8f0f-2e5dbb31aad7", purchasesWithPromos)