Cracking the Code: How to Get Shopify Stock on Hand & Units Sold in One Report (Even Per Location!)

Hey everyone! I've been diving into the Shopify community forums again, and a really common pain point keeps popping up, especially for store owners trying to get a tight grip on their inventory and sales data. It's about getting that elusive report that shows 'stock on hand' right alongside 'units sold' for the same products. Sounds basic, right? Well, as many of you know, Shopify doesn't quite serve this up on a silver platter.

I saw a great thread started by a user named mixx, who was specifically looking for this combined view, even wanting it 'per location' – a crucial detail for many multi-warehouse or multi-store setups. And here's the kicker: they also highlighted how some solutions, especially third-party apps, might show you sales with SOH (stock on hand) but they often won't show you SOH if there weren't any sales for that period. That's a huge blind spot if you're trying to manage your full inventory!

So, what's the workaround that the community, including metric_nerd, has found to be the most reliable? It's a classic spreadsheet tango, my friends. It might not be fully automated, but it gives you total control and ensures you capture everything.

The Manual Merge: Your Reliable Spreadsheet Solution

This method involves pulling two separate reports from Shopify and combining them. It's a bit of legwork, but it's robust and addresses the 'stock on hand even with zero sales' challenge if done correctly.

Step-by-Step: Combining Inventory and Sales Data

1. Export Your Inventory Snapshot: Start by heading into your Shopify Admin. Go to Analytics > Reports. Under the Inventory category, find and export the 'Inventory snapshot' report.
   • Pro-Tip: Make sure you select the date range that encompasses your reporting period. This report is your master list of all products that have inventory, regardless of sales.
2. Export Your Sales by Product Report: Next, still in Analytics > Reports, navigate to the Sales category. Export the 'Sales by product' report.
   • Pro-Tip: Crucially, use the exact same date range for this report as you did for your inventory snapshot. This ensures your data aligns perfectly.
   • For Multi-Location: If you need data per location, ensure your 'Inventory snapshot' report includes location details. For sales, you might need to adjust filters or export options if your sales reports can break down by fulfillment location.
3. Open Both in a Spreadsheet Program: Whether you're using Google Sheets, Excel, or another program, open both of these exported CSV files.
4. Clean Up and Prepare:
   • Ensure your column headers are clear.
   • Identify your 'join key' – this is usually the SKU (Stock Keeping Unit). SKUs are unique identifiers for each product variant, making them perfect for matching data between reports. If you don't use SKUs consistently, this step will be much harder, so consider that a strong recommendation!
5. Perform a 'Left Join' Operation: This is the critical step, especially for ensuring you see SOH even for products with no sales.
   • Start with your 'Inventory snapshot' data as your primary sheet (Sheet 1). This sheet contains all your products with inventory.
   • In a new column on your Inventory sheet, you'll use a lookup function (like VLOOKUP in Excel or INDEX/MATCH, or XLOOKUP, or VLOOKUP in Google Sheets) to pull in the 'Units Sold' data from your 'Sales by product' sheet (Sheet 2).
   • Your lookup value will be the SKU from your Inventory sheet.
   • Your table array will be the Sales sheet, looking up the SKU column and returning the 'Units Sold' column.
   • Important: When using the lookup function, if a product from your Inventory sheet doesn't have corresponding sales data in the Sales sheet, the function will likely return an error (like #N/A). You'll want to replace these errors with '0' (zero) to accurately reflect '0 units sold'.
6. Review and Analyze: Now you have a single sheet with your product, its stock on hand, and its units sold for the specified period, including those items that didn't sell a single unit! You can then filter, sort, and calculate further metrics like inventory turnover or days of supply.

Exploring Third-Party Reporting Apps

Now, I know what you're thinking: 'Can't an app do this for me?' Absolutely, many powerful reporting apps in the Shopify App Store can combine data from different sources. solutionsKruti rightly pointed this out as an option.

However, as mixx astutely observed, there's a common trap: 'If you create a custom report in a 3rd party app you end up with a sales report with soh but it wont show you soh if there werent any sales for the perod.' This is a really important distinction!

Many reporting apps are built with a 'sales-first' mentality. They start with your sales data and then try to pull in inventory. If there's no sale, that product often doesn't even appear in the report, which defeats the purpose of managing your full inventory accurately. You need to know what's sitting on shelves, not just what's flying off them.

What to Look For in a Reporting App:

• Inventory-Centric Reporting: Prioritize apps that emphasize inventory management and reporting, not just sales. They are more likely to build reports starting from your inventory list.
• Custom Report Builder Flexibility: Look for apps with truly robust custom report builders that allow you to define your primary data source (e.g., 'all products in inventory') and then 'left join' sales data to it.
• Multi-Location Support: If 'per location' is key for you, ensure the app explicitly supports multi-location inventory and sales reporting. Don't assume – ask their support!
• Data Granularity: Can it pull data down to the variant and location level for both sales and stock?

Some popular names often come up in these discussions, like Stock & Inventory Reports, Advanced Reporting Kit, or Better Reports. But always, always test them with your specific use case, especially that 'zero sales, still show SOH' scenario, before committing.

Ultimately, whether you go the manual spreadsheet route or find that perfect app, understanding your inventory in relation to your sales is absolutely critical for smart reordering, preventing stockouts, and optimizing your cash flow. It's a foundational piece of data for any growing Shopify store. It's a shame it's not a native one-click report, but thankfully, with a little know-how (and some great community insights!), you can definitely get the complete picture you need. Keep those questions coming in the forums; it's how we all get smarter!