FOR YOUR ANALYST

The formulas

Weeks of cover by product

-- stock on hand and on order, over average weekly units sold (last 28 days)
WITH sold AS (
  SELECT ol.sku, SUM(ol.quantity) / 4.0 AS units_per_week
  FROM order_lines ol
  JOIN orders o ON o.id = ol.order_id
  WHERE o.created_at >= CURRENT_DATE - INTERVAL '28 days'
    AND o.cancelled_at IS NULL
  GROUP BY ol.sku
), stock AS (
  SELECT sku, SUM(on_hand) AS on_hand
  FROM inventory_levels
  WHERE snapshot_date = CURRENT_DATE
  GROUP BY sku
), open_po AS (
  SELECT sku, SUM(quantity_ordered - quantity_received) AS on_order
  FROM purchase_order_lines
  WHERE status = 'open'
  GROUP BY sku
)
SELECT s.sku,
       s.on_hand,
       COALESCE(p.on_order, 0)                                   AS on_order,
       ROUND(COALESCE(d.units_per_week, 0), 1)                   AS units_per_week,
       (s.on_hand + COALESCE(p.on_order, 0))
         / NULLIF(d.units_per_week, 0)                           AS weeks_of_cover
FROM stock s
LEFT JOIN open_po p ON p.sku = s.sku
LEFT JOIN sold d    ON d.sku = s.sku
ORDER BY weeks_of_cover DESC NULLS FIRST;

Null means no sales in four weeks: the slowest stock you have, listed first.

Float owed

-- what the business owes customers today
SELECT 'gift cards'   AS kind, SUM(balance) AS owed
FROM gift_cards WHERE disabled_at IS NULL
UNION ALL
SELECT 'store credit', SUM(balance)
FROM store_credit_accounts
UNION ALL
SELECT 'unshipped preorders', SUM(o.total_price - COALESCE(r.refunded, 0))
FROM orders o
LEFT JOIN (SELECT order_id, SUM(amount) AS refunded FROM refunds GROUP BY order_id) r
  ON r.order_id = o.id
WHERE o.tags LIKE '%preorder%'
  AND o.cancelled_at IS NULL
  AND NOT EXISTS (SELECT 1 FROM fulfillments f WHERE f.order_id = o.id);

Add prepaid plan balances from your subscription app.

Repeat share of cash

-- order cash from new and returning customers, last 13 weeks
WITH first_order AS (
  SELECT customer_id, MIN(created_at) AS first_at
  FROM orders WHERE cancelled_at IS NULL
  GROUP BY customer_id
)
SELECT DATE_TRUNC('week', o.created_at)                              AS week,
       SUM(o.total_price) FILTER (WHERE o.created_at = f.first_at)   AS new_cash,
       SUM(o.total_price) FILTER (WHERE o.created_at > f.first_at)   AS repeat_cash,
       SUM(o.total_price) FILTER (WHERE o.created_at > f.first_at)
         / NULLIF(SUM(o.total_price), 0)                             AS repeat_share
FROM orders o
JOIN first_order f ON f.customer_id = o.customer_id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '13 weeks'
  AND o.cancelled_at IS NULL
GROUP BY 1 ORDER BY 1;

Add each week’s acquisition and retention spend for the table in chapter 9. Postgres syntax.