Dead stock and slow-moving inventory analysis

A dead stock analysis highlights product variants that are active and in stock but have not been sold within an adjustable period. The goal is to identify underperforming items so that action can be taken to improve stock turnover and optimise inventory management.

This report is most useful during monthly or quarterly inventory reviews, or whenever stock performance and warehouse capacity are being evaluated.

This article covers analysis at the product variant level — for analysis at the product or SKU level, see Variations and extensions below.

💡 Typically, dead stock or aging inventory analyses use the most recent date an item was received into inventory. As Shopify does not expose this data to external apps, the date of last sale is used as a proxy.


In this article


How the report works

The report is built on the Product variants root table, where each row represents a variant. This is the correct table for any analysis that needs to report on non-selling variants, inventory, and variant-level data such as current price and cost.

Two custom fields are required:

  • Last sold date: The Product variants table does not contain a built-in field that displays the last sale date for a variant. This custom dimension pulls the most recent sale date from the Agreement lines table associated with the variant id. If a product variant has never been sold, the field returns a null value.
  • Days since last sold: This custom dimension compares the date returned in the Last sold date dimension to the current date and returns the number of days that have passed. If a product variant has never been sold, the field returns a null value.

The report is filtered for active, in-stock variants only where Days since last sold ≥ 180          or Days since last sold is in [Unknown]         . The number of days since last sold can be adjusted to your own reporting needs.

The report builds on a copy of the built-in Inventory by variant report, which already includes the key variant-level dimensions and measures: Product title, Variant title, and Total quantity.

💡 Prefer to skip the setup? If you'd like this report built for you, contact our team through the Help widget in the bottom-right corner. We typically respond within one business day.


Steps to build the report

Step 1 — Start from the Inventory by variant report

Rather than starting from a blank report, this report is built from a copy of the built-in Inventory by variant report, which already includes the key dimensions and measures needed. To open it:

  1. Open the Reports tab
  2. Open the Inventory by variant report in the Product variants reports section

Step 2 — Create the Last sold date custom dimension

This custom dimension returns the most recent maximum date for a sale line on the Agreement lines table using a joined field reference via the variant id. The maximum date reflects the most recent date a sale occurred.

  1. Select Edit, then + Create new, then Create Dimension
  2. Set the Root table to Variant
  3. Set the Display name to Last sold date         
  4. Set Description to Displays the latest sale date associated with the variant
  5. Set the Data type to Date & Time
  6. Enter the following expression:
(SELECT MAX([i.Date])
FROM [$.AgreementLines i]
WHERE [i.Kind] = 'Sale')
  1. Select OK

Step 3 — Create the Days since last sold custom dimension

This custom dimension displays the number of days between the Last sold date and the current date. This field is dynamic and always uses the most up-to-date sale data and current date each time the report is run.

  1. Select Edit, then + Create new, then Create Dimension
  2. Set the Root table to Variant
  3. Set the Display name to Days since last sold         
  4. Set the Description to Calculates the number of days since the variant last sold
  5. Set the Data type to Number and Numeric type to Number
  6. Enter the following expression:
DATEDIFF(DAY, [$.LastSoldDate], TODAY())
  1. Select OK
[$.LastSoldDate]      is the auto-populated BRQL name for the Last sold date field created in Step 2. If you edited the BRQL name in Step 2, replace LastSoldDate     with the name you used.

Step 4 — Add fields to the report

Add the following fields to the report.

Field Type
Last sold date Dimension
Days since last sold Dimension

Step 5 — Add filters

The report requires both AND and OR filter logic. To learn more about adding filters, see Filter a report.

To restrict the report to active and in-stock product variants only:

  1. Select Add filter, select the Status field from the Products related table
  2. Select active             from the value list (the filter defaults to is in)
  3. Select Add filter, select the Inventory quantity field from the root table
  4. Set the operator to > greater than, and enter 0            into Type in value

To restrict the report to variants with no sales in more than 180 days or variants with no sales:

  1. Select Add filter, select the Days since last sold field
  2. Set the operator to ≥ greater than or equal, enter 180      into Type in value
  3. Hover over the applied Days since last sold filter and select + OR
  4. Select the Days since last sold field from the root table
  5. Select [Unknown]       from the value list (the filter defaults to is in)

The OR condition is needed because variants that have never been sold have no value in Days since last sold and would not be captured by the ≥ 180       filter alone.

Step 6 — Sort the report

The report is sorted by product title and variant title in ascending order by default. Adjust the sort order to suit your needs — for example, sorting by Days since last sold in descending order displays the product variant that has not been sold for the longest period. Sorting by Days since last sold in ascending order displays variants that have never been sold first, followed by those most recently sold.

Step 7 — Save the report

Select Save as and save a copy of the report with a name such as Product variants with no sales in the last 180 days.


Interpreting the results

Each row represents an active product variant that is currently in stock and has either not been sold in the last 180 days or has never been sold. When interpreting results, be aware that we cannot show whether the items in the report were active and in stock for the whole period, or when restock occurred.

Useful patterns to look for:

  • Days since last sold is unknown — the variant has never sold. Review whether the item is newly launched or carrying inventory that has never generated demand. Consider adding the Created at date to the report.
  • Very high days since last sold values — Consider discounting, bundling, transferring stock, or removing the variant from active sale if demand is unlikely to return.
  • Variants with substantial inventory quantity — these represent stock that is tying up the most capital and warehouse space. Prioritise these variants when planning inventory reduction or clearance activity.
  • Multiple variants of the same product appearing in the report — this can indicate that specific sizes, colours, or configurations are underperforming while other variants continue to sell. Review whether the product assortment should be simplified.
  • Recently crossed the 180-day threshold — variants with days since last sold values just above 180 may warrant monitoring rather than immediate action, particularly if they are seasonal or have irregular purchasing patterns.

💡 The report uses a default threshold of 180 days. Adjust the Days since last sold filter to match your sales cycle, product lifecycle, or inventory review process.


Variations and extensions

Show the worst product variants per product type or vendor

Add Product Type or Vendor as a dimension and adjust the sort hierarchy to group by that field first, followed by the Days since last sold field in descending order. This displays the worst product variants per group with the oldest last sale dates. This is useful for a quick overview of the most dormant items per segment.

Product variants that have never been sold will appear last in each group under this sort order, since they have no value in Days since last sold. To show these items first, adjust the Days since last sold sort direction to ascending.

Analyse by product instead of product variant

The standard report identifies product variants that have not sold recently. Individual variants for a product are included or excluded depending on the last date they were sold. If you want to analyse products where no variants have sold in the last 180 days, you can create a product-level report.

To do this, build a new report using Product variants as the root table. Repeat Step 1 except start from the built-in Inventory by product report. Repeat Steps 2 and 3, but set the Root table to Product for both custom dimensions, so that the joined field reference to the Agreement lines table uses the product id rather than the variant id.

To filter for products that are still in stock, a new custom dimension is needed on the Product table that sums inventory across all variants belonging to the product.

  1. Select Edit, then + Create new, then Create Dimension
  2. Set the Root table to Product
  3. Set the Display name to Inventory quantity         
  4. Set the Description to Aggregates inventory to the Product id level
  5. Set the Data type to Number and Numeric type to Number
  6. Enter the following expression:
(
SELECT SUMZ([v.InventoryQuantity])
FROM [$.Variants v]
)
  1. Select OK

Repeat Steps 4 to 6 using the equivalent fields from the Product related table.

Analyse by product SKU instead of product variant

You may want to report by product SKU — rather than product variant — to group results by SKU instead of variant.

To analyse using this field, build a new report using Product variants as the root table. Repeat Step 1 except start from the built-in Inventory by SKU report.

Alternative versions of the Last sold date and Days since last sold custom fields created in Steps 2 and 3 are required to use the most recent sale date across all variants under a product SKU.

New custom field display name Approach Example expression
Product SKU last sold date          A direct table query to the Agreement lines using the product title and SKU as the joining fields.

(SELECT MAX([i.Date])       

FROM [$AgreementLines i]        

WHERE [i.Kind] = 'Sale'       

AND [i.ProductTitle] = [$.Product.Title]        

AND [i.SKU] = [$.SKU])     
Product SKU days since last sold          Refer to the BRQL name of the Product SKU last sold date field DATEDIFF(DAY, [$.ProductSKULastSoldDate], TODAY())              

A new custom dimension that uses a window function to sum inventory of all the variants belonging to the product SKU is needed to filter for in-stock items at the SKU level.

  1. Select Edit, then + Create new, then Create Dimension
  2. Set the Root table to Variant
  3. Set the Display name to Product SKU inventory quantity         
  4. Set the Description to Aggregates inventory to the Product SKU level
  5. Set the Data type to Number and Numeric type to Number
  6. Enter the following expression:
SUMZ([v.InventoryQuantity]) 
OVER (PARTITION BY [$.Product.Title],[$.SKU])
  1. Select OK
Part What it does
SUMZ([v.InventoryQuantity])     Totals inventory quantity across all variants in the partition (product title and SKU)
OVER (PARTITION BY [$.Product.Title],[$.SKU])     Groups the total by product title and SKU, so the result reflects all variants sharing those values

Repeat Steps 4 to 6 using the equivalent product SKU fields.

A similar approach could be used to report at any alternative level like variant option, such as size or colour.


Need more support?

If you get stuck or have additional questions, you can contact our team directly through the Help widget in the bottom-right corner — we typically respond within one business day.

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Related Articles