Unlocking Your Shopify Reports: Finding the 'Highest Quantity Per Order' for Smarter Stock Planning

Hey fellow store owners! Let's talk about something that's probably given many of us a headache: figuring out exactly how much stock to order. We all know Shopify's standard reports are super useful, but sometimes, they just don't quite hit the mark for those nuanced situations. Recently, a really insightful discussion popped up in the Shopify Community that I just had to share, because it tackles a common inventory planning challenge head-on: finding the "highest quantity per order."

The original post, from a merchant managing a store with over 20,000 SKUs and 208 brands, highlighted a critical gap. While Shopify provides an "average quantity per order," that average can be misleading. Imagine you sell a product that usually goes out one or two at a time, but occasionally, a customer buys 10 or 20 for a special event or a wholesale deal. An average won't show you that peak, and if you plan your stock based on averages alone, you're going to get caught short when those big orders hit. This merchant, like many of us, needed a way to see the absolute highest quantity sold in a single order for each product to make replenishment planning truly effective.

Why the 'Highest Quantity Per Order' Matters (and Why Averages Lie)

As the original poster, dolia_goprofit, wisely put it, "averages can hide occasional large orders." And they're absolutely right! For stock planning, especially with a large and varied catalog, knowing the maximum single-order quantity is a game-changer. It helps you anticipate those infrequent but impactful spikes in demand, ensuring you don't run out of stock on a popular item just because you only planned for the average sale.

However, the community discussion quickly brought up a crucial point: don't blindly stock to the absolute maximum. As Jack_BuildsShopify and Nobolevsk pointed out, the "all-time max" can often be a true outlier – a one-off wholesale deal, a large gift-set order, or a seasonal anomaly that isn't likely to repeat next month. Sizing your safety stock to cover that single, highest peak might lead to carrying excessive inventory across your entire catalog, tying up capital unnecessarily.

A smarter approach, suggested by Nobolevsk, is to consider the 90th or 95th percentile of order quantities instead of the flat max. This covers most significant orders without over-inflating your stock levels for a super rare event. You can still keep the absolute max for context – it's useful to know the biggest hit you've ever taken – but perhaps not as the primary driver for your day-to-day reorder rules.

How to Find Your 'Highest Quantity Per Order' in Shopify

Since Shopify's standard reports don't offer a direct "max quantity per order" column, the community rallied with some fantastic workarounds. Here are the top methods, ranging from quick checks to scalable solutions for large stores:

Method 1: Using ShopifyQL for a Quick Look (Good for specific products/brands)

For those comfortable diving a little deeper into custom reports, ShopifyQL is a powerful tool. V.marychenka provided a brilliant ShopifyQL query that gets you exactly what you need:

1. Go to your Shopify Admin, then navigate to Analytics > Reports.
2. Click New exploration.
3. Paste the following code into the ShopifyQL editor and click Run:

FROM sales
  SHOW quantity_ordered
  WHERE product_vendor = 'Brand name'
  GROUP BY product_title, order_name, day
  SINCE -10y
  ORDER BY product_title ASC, quantity_ordered DESC

A few notes on this code:

• Replace 'Brand name' with the exact name of the brand you want to check (if your brands are in the Vendor field).
• This query groups rows by product title and order name, then orders them by quantity descending. This means the first row under each product title will show its highest single-order quantity, along with the order number and date.
• SINCE -10y ensures you're looking at your full sales history, which is crucial for finding an all-time max.
• It counts items before returns, so you see the true peak ordered.

While this is great for individual checks or smaller catalogs, AsMd's situation (20,000 SKUs) makes manually scanning these results impractical. This leads us to more scalable solutions.

Method 2: Exporting Data for Spreadsheet Analysis (Best for large catalogs)

This is where the real power for large stores comes in. Several community members, including accessify.web.app, ahsandoesntcare, Ecom_swift_LLC, and optima-tiktok-shop1, converged on a robust solution involving data export and spreadsheet manipulation. This method addresses the bottleneck of manual, per-brand exports.

Step-by-Step Export & Analysis:

1. Export Your Order Line Items:
   • Go to Orders > Export in your Shopify Admin.
   • Choose to export orders with line items. This is critical as it gives you one row per item in an order, along with its quantity, SKU, and order details.
   • For your initial setup, export your full order history. Moving forward, you can export only orders since your last export date to keep your master file updated.
2. Create a Master Spreadsheet:
   • Open your exported CSV in Google Sheets or Excel. Keep this as a master file.
   • When you export new orders, append those rows to this master sheet.
3. Calculate Max Quantity Per SKU:
   • Using a Pivot Table: This is generally the cleanest and most scalable approach.
     1. Select all your data.
     2. Insert a Pivot Table (or Pivot Report in Sheets).
     3. Drag your SKU or Product Title to the "Rows" field.
     4. Drag Lineitem quantity (or similar column name for quantity) to the "Values" field.
     5. Change the aggregation for Lineitem quantity from "Sum" or "Average" to "Max". This will give you one row per product, showing its highest single-order quantity.
     6. You can also add Order Date to the "Values" field and set its aggregation to "Max" to see when that peak order occurred.
   • Using the MAXIFS Function (Excel/Sheets): If you prefer formulas, you can add a helper column.

     =MAXIFS(quantity_column, sku_column, A2)
     

     Replace quantity_column with the range of your quantity data, sku_column with the range of your SKU data, and A2 with the cell containing the SKU for the current row. Drag this formula down.
4. Refine Your Data:
   • Filter by Order Status: As ahsandoesntcare noted, you might want to filter out refunded or canceled orders to only consider truly shipped items.
   • Add Vendor Information: If your vendor isn't in the line-item export, you can add it with a lookup against your product CSV using VLOOKUP or XLOOKUP. Then you can easily filter your pivot table by brand.
5. For Extreme Volumes: If your CSV gets too large for standard spreadsheet tools, optima-tiktok-shop1 suggested the Admin API’s bulk operation query to dump all order line items to JSONL, which can then be summarized by a script or database. This is for the truly massive stores!

What About a Direct MAX() Function in ShopifyQL?

VikashJ raised an excellent point: whether ShopifyQL supports a direct MAX() function within the SHOW clause. If it does, you could get that one-row-per-product highest quantity directly from the query itself, eliminating the need for pivoting. It's definitely worth testing with a small product subset to see if your Shopify plan supports this.

Requesting New Features from Shopify

AsMd's frustration with the manual work for such a seemingly simple metric is completely understandable. They asked about a feature request inbox, and VikashJ had the answer: while there isn't a single public voting portal, the most reliable way to submit feature feedback is directly through the Help panel (the "?" icon) in your Shopify admin. Shopify staff also monitor the community forums for patterns and feedback to pass along internally.

So, there you have it! While Shopify's built-in reports might not give you the "highest quantity per order" directly, the community has cooked up some incredibly effective ways to get that crucial data. Whether you're doing a quick check with ShopifyQL or building a robust spreadsheet system for thousands of SKUs, you now have the tools to make more informed stock planning decisions and avoid those frustrating out-of-stock moments. Keep those insights coming, fellow merchants!