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]
🚀 Shopify Dropshipping & Store Optimization Expert
