Choose a version:

inventory_by_location

The inventory_by_location schema captures how much of each item you hold at each location, as point-in-time snapshots. Use it for stock, value, and availability views by item and variant.

Anchor to Use casesUse cases

Stock on hand: Group ending_inventory_units_at_location by product_variant_id to break on-hand units down by variant.
Net change: Compare starting_inventory_units_at_location and inventory_units_net_change_at_location to trace how stock at a location shifted over the interval.
Stock duration: Show days_in_stock_at_location and days_out_of_stock_at_location for each inventory_item_id to see how long items sit in or run out of stock at a location.
Value by product: Report ending_inventory_value_at_location and ending_inventory_retail_value_at_location by product or variant to see where inventory capital is tied up.

Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor

FROM inventory_by_location

SHOW ending_inventory_units_at_location,

days_of_inventory_remaining_at_location, days_out_of_stock_at_location

GROUP BY inventory_location_name, product_variant_id

HAVING days_of_inventory_remaining_at_location < 14

AND ending_inventory_units_at_location > 0

SINCE -30d UNTIL today

ORDER BY days_of_inventory_remaining_at_location ASC

LIMIT 200

VISUALIZE days_of_inventory_remaining_at_location TYPE horizontal_grouped_bar

Examples

Description

Rank variants at each location by `days_of_inventory_remaining_at_location` ascending, keeping only those with fewer than 14 days of stock left. This example uses [`HAVING`](/docs/api/shopifyql/latest/syntax/having) `ending_inventory_units_at_location > 0` to drop variants that are already out of stock.

ShopifyQL

FROM inventory_by_location
  SHOW ending_inventory_units_at_location,
    days_of_inventory_remaining_at_location, days_out_of_stock_at_location
  GROUP BY inventory_location_name, product_variant_id
  HAVING days_of_inventory_remaining_at_location < 14
    AND ending_inventory_units_at_location > 0
  SINCE -30d UNTIL today
  ORDER BY days_of_inventory_remaining_at_location ASC
  LIMIT 200
VISUALIZE days_of_inventory_remaining_at_location TYPE horizontal_grouped_bar

Description

Rank the product variants that aren't selling by `ending_inventory_value_at_location`, the capital tied up at each location, highest first. This example uses [`HAVING`](/docs/api/shopifyql/latest/syntax/having) `inventory_units_net_change_at_location = 0` to keep only variants with no movement over the last 90 days, your clearance or relocation candidates.

ShopifyQL

FROM inventory_by_location
  SHOW days_in_inventory_at_location, ending_inventory_value_at_location,
    ending_inventory_units_at_location, inventory_units_net_change_at_location
  GROUP BY inventory_location_name, product_variant_id
  HAVING days_in_inventory_at_location > 90
    AND inventory_units_net_change_at_location = 0
  SINCE -90d UNTIL today
  ORDER BY ending_inventory_value_at_location DESC
  LIMIT 100
VISUALIZE ending_inventory_value_at_location TYPE horizontal_bar

Description

Track weekly `ending_inventory_retail_value_at_location` across last quarter, with `starting_inventory_units_at_location`. This example uses [`WITH TOTALS`](/docs/api/shopifyql/latest/syntax/with#total-columns) to add a total for last quarter beside the weekly rows.

ShopifyQL

FROM inventory_by_location
  SHOW ending_inventory_retail_value_at_location,
    starting_inventory_units_at_location, first_day_in_inventory_at_location
  TIMESERIES week WITH TOTALS
  DURING last_quarter
  ORDER BY week ASC
VISUALIZE ending_inventory_retail_value_at_location TYPE line

Description
Track weekly `days_in_stock_at_location` across this quarter, each week compared to the quarter before. This example uses [`COMPARE TO previous_period`](/docs/api/shopifyql/latest/syntax/compare-to) to benchmark your date range against the equal-length period right before it.
ShopifyQL
```
FROM inventory_by_location
  SHOW days_in_stock_at_location
  TIMESERIES week WITH PERCENT_CHANGE
  DURING this_quarter
  COMPARE TO previous_period
  ORDER BY week ASC
VISUALIZE days_in_stock_at_location TYPE bar
```

Anchor to MetricsMetrics

Counts and calculations that let you track key business indicators. Metrics show up as the columns when queried.

Metrics you can use when querying FROM inventory_by_location.

Anchor to days_in_inventory_at_location days_in_inventory_at_location: The number of days in your selected period when the inventory item had a stock record at the inventory location.
Anchor to days_in_stock_at_location days_in_stock_at_location: The number of days in your selected period that ended with more than zero units at the inventory location.
Anchor to days_of_inventory_remaining_at_location days_of_inventory_remaining_at_location: Forecasted number of days of inventory remaining at the specified location

Days of inventory remaining (at location) = ending inventory units (at location) / inventory units net change (at location) per day
Anchor to days_out_of_stock_at_location days_out_of_stock_at_location: The number of days in your selected period that ended with zero or fewer units at the inventory location.
Anchor to ending_inventory_retail_value_at_location ending_inventory_retail_value_at_location: The retail value is reported in your store's currency.

Ending inventory retail value (at location) = ending inventory units (at location) * product variant price
Anchor to ending_inventory_units_at_location ending_inventory_units_at_location: Units at the end of the given period at the specified location
Anchor to ending_inventory_value_at_location ending_inventory_value_at_location: The cost value is reported in your store's currency.

Ending inventory value (at location) = ending inventory units (at location) * inventory item cost
Anchor to first_day_in_inventory_at_location first_day_in_inventory_at_location: The first day in your selected period when the inventory item had a stock record at the inventory location. Use first_day_in_inventory_at_location with last_day_in_inventory_at_location to show the date range covered for each item and location.
Anchor to inventory_units_net_change_at_location inventory_units_net_change_at_location: The net movement in units at the inventory location during your selected period. Use inventory_units_net_change_at_location with starting_inventory_units_at_location and ending_inventory_units_at_location to show the opening and closing unit counts for the period.

Inventory units net change (at location) = starting inventory units (at location) - ending inventory units (at location)
Anchor to last_day_in_inventory_at_location last_day_in_inventory_at_location: The last day in your selected period when the inventory item had a stock record at the inventory location. Use last_day_in_inventory_at_location with first_day_in_inventory_at_location to show the date range covered for each item and location.
Anchor to starting_inventory_units_at_location starting_inventory_units_at_location: Units at the start of the given period at the specified location

DAY_DURATION

A duration measured in days.

MONEY

A monetary amount representing currency values such as prices, revenue, costs, and discounts.

INTEGER

A whole number without decimal places, used for counts, quantities, and other discrete numeric values.

DAY_TIMESTAMP

A date value truncated to day precision.

Anchor to DimensionsDimensions

Attributes of your data that let you look more granularly at aspects of the data. Group and filter by dimensions to shape the rows your query returns.

Dimensions you can use when querying FROM inventory_by_location.

Anchor to day day: The day an inventory snapshot applies to.
Anchor to inventory_group_id inventory_group_id: Shopify identifier for the inventory group
Anchor to inventory_item_id inventory_item_id: The unique Shopify identifier for the inventory item. Use inventory_item_id to break results down by inventory item or to filter to a specific inventory item. Use it with product_variant_sku or product_variant_title to show readable labels.
Anchor to month month: The month an inventory snapshot applies to.
Anchor to product_id product_id: The unique Shopify identifier for the product, which can include multiple variants. Use product_id to group stock for the same product or to filter to a specific product. Use product_id with product_title to show a readable label.
Anchor to product_variant_id product_variant_id: The unique Shopify identifier for the product variant. Use product_variant_id to break stock down to a specific variant or to filter to one variant. Use product_variant_id with product_variant_title or product_variant_sku to show readable labels.
Anchor to quarter quarter: The quarter an inventory snapshot applies to.
Anchor to shop_id shop_id: The unique Shopify identifier for your store. Use shop_id with shop_name to show readable store labels.
Anchor to week week: The week an inventory snapshot applies to.
Anchor to year year: The year an inventory snapshot applies to.
Anchor to inventory_location_id inventory_location_id: Shopify identifier for the inventory location
Anchor to inventory_location_name inventory_location_name: Name of the inventory location
Anchor to shop_name shop_name: Name of your store
Anchor to inventory_cost_is_recorded inventory_cost_is_recorded: Whether the inventory item has a cost saved in Shopify, with values true and false.
Anchor to inventory_is_tracked inventory_is_tracked: Whether Shopify tracks inventory adjustments for the inventory item, with values true and false.
Anchor to inventory_item_cost inventory_item_cost: The cost recorded for one unit of the inventory item, in your store's currency.
Anchor to product_status product_status: The product's current publishing state, with values Active, Archived, Draft, Suspended, and Unlisted.
Anchor to product_title product_title: Display name of the product that your customers see
Anchor to product_type product_type: Type (category) that you've assigned to the product
Anchor to product_vendor product_vendor: Vendor of the product
Anchor to product_collection product_collection: Collection associated with the product
Anchor to product_collections product_collections <>: Set of collections associated with the product
Anchor to app_product_creation app_product_creation: The app that created the product, shown as an app name.
Anchor to product_tag product_tag: Tag associated with the product
Anchor to product_tags product_tags <>: Set of tags associated with the product
Anchor to product_variant_abc_grade product_variant_abc_grade: The ABC grade assigned to the product variant based on its share of revenue. A-grade variants account for about 80% of revenue, B-grade for 15%, and C-grade for 5%.
Anchor to product_variant_sku product_variant_sku: SKU of the product variant
Anchor to product_variant_title product_variant_title: Display name of the product variant

IDENTITY

A unique identifier for a Shopify resource such as a customer, product, or order.

MONTH_TIMESTAMP

A date value representing the start of a month.

QUARTER_TIMESTAMP

A date value representing the start of a fiscal quarter.

WEEK_TIMESTAMP

A date value representing the start of a week.

YEAR_TIMESTAMP

A date value representing the start of a year.

STRING

A sequence of characters representing text data.

BOOLEAN

A true or false value representing binary states.

ARRAY

A list of values. The element type appears inside angle brackets, such as ARRAY<STRING>.

Anchor to Related schemasRelated schemas

inventory: Store-wide inventory totals without location breakdown.
inventory_transfers: Stock moves between locations that resolve stockouts.
inventory_shipments: Incoming shipments arriving at each location.
inventory_adjustment_history: Manual adjustments at each location.

Was this page helpful?