Orders Products

The ship before 5 metirc calculates the percentage of orders recieved before 5pm that ship the same day. The metric shoud not include orders for which an item is out of stock in the warehouse.

This sounds easy enough but the bit about checking if all the items on the PO are in stock is tricky due to our table setup. Since skus on the order can be kits we need to spread the kits out ot the items in the kits to check if we have inventory on hand for each of the items. I aleady had to do this to calculate profit so I’ve sent that table up into Domo. It will be refreshed with the same frequency as the profit table.

library(tidyverse)
library(data.table)
library(DomoR)
library(lubridate)
load("domoCustomer")
load("domoAccessToken")
init(domoCustomer, domoAccessToken)

orders_products_split_kits <- fetch("6bd884cc-0abf-4d98-8927-3a6a1903eec6") %>%
    filter(orders_status < 2000,
           date_purchased >= "2019-03-01",
           products_dropship == 0)

picks <- fetch('a0e64422-9972-4c00-bbfa-65f8686993fa') %>%
    filter(shipments_status < 2000,
           date_purchased >= "2019-03-01") %>%
    select(orders_id, products_id, warehouses_id, products_quantity, pack_time, is_drop_ship) %>%
    rename(products_picked = products_quantity) %>%
    group_by(orders_id, products_id, warehouses_id, is_drop_ship) %>%
    summarise(products_picked = sum(products_picked), 
              pack_time = max(pack_time))

opp <-
  merge(
    orders_products_split_kits,
    picks,
    by = c("orders_id", "products_id"),
    all.x = T
  ) %>%
  select(
    -website_id,
    -delivery_postcode,
    -ups_res_com,
    -products_weight,
    -products_weight_new,
    -shipping_method,
    -shipping_module,
    -products_length,
    -products_width,
    -products_height,
    -products_dropship,
    -is_drop_ship
  )

delivery_state <- unique(opp$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(primary_warehouse = ifelse(UT_State == TRUE, 2, 1)) %>%
        select(-UT_State)

opp <- merge(opp, delivery_state, all.x = T, by = "delivery_state") %>%
  mutate(warehouses_id = ifelse(is.na(warehouses_id), primary_warehouse, warehouses_id)) %>%
  select(-primary_warehouse)

Inventory

We record inventory daily at 5:00 PM. We’re only going to include orders where every item on the order was in stock at that time.

Shipments

We can’t join the inventory onto the orders_products table until we know which warehouse the order was intended to ship from.

Since orders can go out in more than one shipment and since we’re tracking to make sure the whole order went out I’ll group and summarise by max(date_shipped).

Merge shipments onto orders_products

Merge Inventory

Add logic