ShopifyQL Notebooks: A Practical Guide (With Real Examples)

ShopifyQL is Shopify's proprietary query language for commerce data. It's exclusive to Shopify Plus and runs inside "Notebooks" - an in-admin query environment where you write queries, run them, and visualize results without leaving your Shopify dashboard. If you've used SQL before, ShopifyQL will feel familiar at first glance. But here's the catch: it's not standard SQL, and many teams discover this the hard way.

ShopifyQL is designed specifically for Shopify data. It's optimized for the questions ecommerce teams ask most: "What are my top products?" "How much revenue came from this discount code?" "Which customer segments are growing?" It's straightforward for these queries, but when you need to join data from different tables or build complex transformations, you'll hit walls quickly.

The upside: if you're not a data analyst, ShopifyQL is easier to get started with than SQL. The syntax is closer to natural language, and you can run your first meaningful query in minutes. The downside: you'll outgrow it if your business needs scale.

Enough theory. Let's get into practical queries. These are copy-paste ready - run them in your Shopify Plus Notebooks admin and you'll get real answers about your business. Each one answers a specific business question your team is probably asking right now.

Find your bestsellers by revenue. This tells you which products are actually making money - critical for inventory planning and merchandising decisions.

Change -90d to -30d for last 30 days, or -12m for last 12 months. The LIMIT 20 can be adjusted too - increase it if you want to see more products.

Understand how each discount code affects your bottom line. Use this to see which promotions are working and which are just giving away margin.

Tip: If you see codes with huge discount amounts but low revenue, that's a pricing leak. Consider adjusting your discount strategy.

Track AOV trends over time. This reveals seasonality, the impact of product changes, and whether your bundles or upsells are working.

The sum(net_sales) / count() divides total revenue by order count to get average per order. ShopifyQL will chart this and show you which months had the highest AOV.

How much revenue comes from repeat customers vs first-time buyers? This is essential for understanding your cohort health and customer lifetime value.

If your returning customer percentage is low, your retention efforts need work. If it's high, focus on maintaining that loyalty.

Which traffic sources drive the most sessions? This helps you allocate marketing budget to your strongest channels.

Note: This query uses sessions not orders. You're analyzing traffic patterns, not revenue. Combine this with conversion rate data to find your most efficient channels.

Do customers shop more on certain days? This informs when to launch promotions, send emails, or run ads.

Most DTC brands see a Monday - Wednesday spike and a weekend dip (or vice versa, depending on your audience). Use this to optimize ad spend timing.

Which products have the most returns? High refund rates signal quality issues, mismatched expectations, or product-market fit problems.

Run this monthly. If a product's refund amount spikes, investigate. It could be a bad batch, shipping damage, or a sizing issue.

If you ship internationally, understand which countries drive revenue. This guides inventory allocation and localization priorities.

Combine this with shipping cost data to see which markets are actually profitable. You might be surprised.

Who are your whales? Identify your top 50 customers by lifetime spend. Focus retention efforts here - losing one high-value customer is expensive.

See your revenue day-by-day. Spot drops instantly and correlate them with campaigns, external events, or traffic patterns.

Run this every Monday morning. If you see an unexpected dip, you'll catch it early and can investigate the root cause.

ShopifyQL is powerful for simple queries, but it has real limitations. Understanding them now saves frustration later.

The most frustrating limitation for growing brands: no JOINs. You can't write a query like "show me revenue by product AND customer segment in one query." You have to choose one dimension. For a single brand this is annoying. For a multi-brand business it's a deal-breaker.

The second limitation: single chart per notebook. You can't build a dashboard with 5 charts that update together. You'd need 5 separate notebooks. If you want a proper dashboard, you'll need to export to Looker Studio or another visualization tool.

Third: no profitability data. ShopifyQL gives you revenue and orders but not cost of goods sold. You can't calculate true margin per product. This is a backend issue - Shopify doesn't store COGS by default. You have to add it manually or integrate a tool.

ShopifyQL is fantastic for ad-hoc analysis of Shopify data. But it hits a ceiling when you need more. Here's when to reach for alternatives:

• Cross-platform data: Combine Shopify + Meta + Google + Amazon → Use Niblin or a data warehouse. ShopifyQL only sees Shopify data.
• Profitability and margins: Calculate true profit per product → Use BeProfit or manual COGS tracking in Shopify metafields.
• Plain English questions: "Why did revenue drop yesterday?" instead of writing queries → Use Niblin's AI analytics agent.
• Automated anomaly detection and alerts: Get notified when something breaks → Use Niblin or custom Shopify Flow automation.
• Multi-chart dashboards: Build real dashboards with 5+ visualizations → Use Looker Studio, Tableau, or Polar Analytics.
• Custom forecasting: Predict next month's revenue or inventory needs → Use a forecasting tool or hire a data analyst.

Here's how to think about it: Use ShopifyQL for quick, tactical questions. "What are my top products?" "Which discount codes worked?" These take 2 minutes to answer and inform immediate decisions.

Use a BI tool or analytics platform for strategic questions. "How is my marketing efficiency trending?" "Which customer cohort is at risk?" "What's my true product profitability?" These require combining data from multiple sources, building dashboards your whole team can access, and ongoing monitoring.

As your business scales, ShopifyQL becomes a bottleneck. You'll reach for it less and less. Instead, you'll want a platform that gives you:

1. All data in one place (Shopify + marketing + customer data)
2. Pre-built dashboards for your team (marketing, finance, operations)
3. Anomaly detection that alerts you to problems before they spiral
4. The ability to ask questions in plain English without writing code
5. Export and sharing that doesn't require notebooks

Niblin does this - it's an AI analytics platform built for DTC brands. It connects to Shopify and your marketing platforms, automatically detects anomalies, and briefs your team on what's happening with your business. No queries to write, no notebooks to manage. But you might also use Looker Studio (free, good for dashboards), Tableau (enterprise, powerful), or Polar Analytics (built for ecommerce, growth-stage focused).

Start with single-table queries on recent data. Don't try to query 3 years of data in your first notebook. Once you understand the syntax, add complexity. A query that times out teaches you nothing.

Call them "Top Products - Last 30 Days" not "Query 1." Your future self (and your team) will thank you when they need to find a query later.

ShopifyQL notebooks aren't collaborative out of the box. Export to CSV and share in Slack or Google Sheets. This is why most teams move to a dashboard tool eventually.

If you're querying large date ranges or complex data, run during nights/weekends. You'll avoid rate limiting and get faster results.

If you're just testing a query, add LIMIT 100 first. Once you confirm the results are what you expect, run the full query without LIMIT. This saves time and avoids hitting rate limits.