What is this?
Retailers and eCommerce companies need to maintain an adequate supply of inventory to minimize the risk of out-of-stock items and a poor customer experience. An industry-standard measure is “stock-to-sales” ratio, effectively measuring how many days of sales coverage you have in stock.
For advanced use cases and seasonal industries, it may be helpful to use advanced forecasting methodologies to forecast upcoming sales. However, in many cases, a simple 28-day rolling average average is sufficient.
The Result
This block allows you to to identify your stock coverage ratio for any SKU, department, or product category:
- 1.0 indicates you have 28 days worth of sales in stock
- Values significantly over 1.0 indicate you may be overstocked
- Values significantly under 1.0 indicte you may be understocked
With this ratio you can build these reports:
Distribution of Product Categories by Coverage Ratio
Coverage Ratio Heatmap
This is also a great way to leverage alerts, and automatically notify your purchasing team when stock levels drop below a defined threshold.
How to Get There
Make sure your Explore starts with your full inventory table[1], or uses a full outer join from order_items to inventory. (You need to include sold and unsold inventory items!)
explore: inventory_items{
join: order_items {
type: left_outer
relationship: many_to_one
sql_on: ${inventory_items.id} = ${order_items.inventory_item_id} ;;
}
Then, in your inventory view, define your count on-hand by identifying inventory items with a corresponding order_id
view: inventory_items {
sql_table_name: inventory_items ;;
dimension: id {
primary_key: yes
type: number
sql: ${TABLE}.id ;;
}
dimension_group: sold {
type: time
timeframes: [time, date, week, month, raw]
sql: ${TABLE}.sold_at ;;
}
dimension: is_sold {
type: yesno
sql: ${sold_raw} is not null ;;
}
# If you don't track this in the inventory table, you could also
# define this by `${order_items.id} is not null`
measure: number_on_hand {
type: count
filters: {
field: is_sold
value: "No"
}
}
```
In your order_items view, define a count of orders sold within the past 28 days. You'll want to do this with a filtered measure, as opposed to filtering from the Explore UI:
```
measure: count_last_28d {
type: count
hidden: yes
filters: {
field: created_date
value: "28 days"
}
}
```
Finally, back in your inventory view, you can define a stock coverage ratio:
```
measure: stock_coverage_ratio {
type: number
description: "Stock on Hand vs Trailing 28d Sales Ratio"
sql: 1.0 * ${number_on_hand} / nullif(${order_items.count_last_28d},0) ;;
value_format_name: decimal_2
# Optional HTML styling to color-code:
html: <p style="color: black; background-color: rgba({{ value | times: -100.0 | round | plus: 250 }},{{value | times: 100.0 | round | plus: 100}},100,80); font-size:100%; text-align:center">{{ rendered_value }}</p> ;;
}
}
```
Happy stocking!
-----
_[1] Note that for massive datasets where querying your entire inventory table is not feasible, you may use a Persistent Derived Table to calculate the number of items on hand, grouped by SKU, and use that instead._