My Store Database scheme / analytics

Store Design

Topic summary

A user is attempting to create a report showing inventory levels for their top 50 selling products using ShopifyQL, but encounters multiple issues:

Core Problems:

  • Documentation examples from Shopify’s Products dataset produce errors when executed
  • The desired “Sell-through rate by product” analytics report cannot be customized to show only top 50 items, and its underlying ShopifyQL query is not visible
  • No inventory table appears in their ShopifyQL schema (confirmed via SHOW command)
  • Product_variants table is also missing from their available datasets
  • Despite these limitations, inventory data displays normally in the Shopify dashboard under Products/Inventory sections

Suggested Solutions:

  • Community members recommend third-party apps like Better Reports, Report Pundit, or Mipler for customizable inventory and sales reporting
  • Alternative approaches include exporting data to Google Sheets or BI tools, or using Shopify’s built-in Inventory Reports under Analytics
  • Sample ShopifyQL queries were provided for querying sales and product_variant tables, though these don’t resolve the user’s schema access issues

Current Status:
The discussion remains unresolved, as the fundamental issue—missing database tables in ShopifyQL despite data being visible in the dashboard—has not been explained or fixed. The user may need to explore app-based solutions or contact Shopify support regarding their specific plan’s ShopifyQL capabilities.

Summarized with AI on October 31. AI used: claude-sonnet-4-5-20250929.
1

HI,
am trying to produce a report of the product levels of the top 50 selling items. The examples in the shopify documentation do not appear to work. Products dataset queries give various error when pasted into my shopifyql.
The analytics report “Sell-through rate by product” is what would like but limited to top 50 items, however the shopifyql is not shown.
Spent time with customer care but no joy.
Looking for inventory level but not see an inventory table in my store.

How do I find my database schema as documentation does not appear to match my data.

2

Hi ,

I understand your challenge—ShopifyQL can be tricky when the documentation examples don’t match real-world store data. Based on your request, here’s a structured approach to generating a report for the top 50 selling items along with their inventory levels:

Step 1: Understanding Your Data Source

Shopify Analytics provides built-in reports, but these often lack customization options like filtering to a top 50 list. The “Sell-through rate by product” report is useful, but as you noted, ShopifyQL for that report isn’t visible.

ShopifyQL queries typically pull data from the Products, Orders, and Inventory tables (when available). However, depending on your plan and app setup, you might not see an explicit “Inventory” dataset. Instead, inventory data is usually linked through the Product Variants dataset.

Step 2: Retrieving the Top 50 Best-Selling Products

If you’re on Shopify Plus or using ShopifyQL through an advanced analytics tool (like a data warehouse or third-party app), you can try querying:

FROM sales  
SELECT product_title, variant_title, SUM(quantity) AS total_sold  
GROUP BY product_title, variant_title  
ORDER BY total_sold DESC  
LIMIT 50

This query fetches the top 50 products based on total sales.

Step 3: Getting Inventory Levels

Shopify does not directly expose an Inventory Table, but inventory data can be retrieved through the Product Variants dataset:

FROM product_variant  
SELECT product_title, inventory_quantity  
WHERE product_title IN (SELECT product_title FROM sales ORDER BY SUM(quantity) DESC LIMIT 50)

This links inventory data to your top-selling products.

Alternative Solutions

Since Shopify’s built-in reporting has limitations, you might consider:

  • Use a Shopify App like Report Pundit or Better Reports for deeper data analysis.
  • Exporting data to Google Sheets or a BI tool via an app or API for custom analysis.

If you’re looking for a fully automated, customized solution to generate this report dynamically, I offer Shopify store optimization services, including data reporting and analytics customization. Let me know if you’d like assistance setting up a tailored reporting solution that fits your business needs.

Hope this helps! Let me know if you have any questions.

Best,
[XERIONIZ]
:rocket: Shopify Dropshipping & Store Optimization Expert

3

Hi @hughespc ,

You can use apps like Better Reports, Report Pundit, or Mipler from the Shopify App Store to generate reports on the top 50 selling products with inventory levels. These apps provide customizable reports, advanced analytics, and easy data export options to help track sales and stock efficiently.

4

HI

I do not have a product_variant, cannot see any exposed inventory data, but it happily shows under products or inventory in the dashboard for the site.

Thanks

5

If your inventory data is visible in the Shopify dashboard but not through product variants, try using Shopify’s Inventory Reports under Analytics or export the inventory data from Products > Inventory. Apps like Better Reports, Report Pundit, or Mipler can also pull inventory details directly from the “Products” or “Inventory” sections instead of variants.

6

Sorry I do not have product_variants or inventory when looking in shopifyql ie SHOW neither is listed