Choose a version:

FROM and SHOW

Every ShopifyQL query needs FROM, which chooses the table or tables to query. You can use SHOW to choose which metrics and dimensions to return from the table.

The table you choose in FROM determines the metrics and dimensions available to SHOW, WHERE, GROUP BY, TIMESERIES, and other clauses. Browse the schema reference to find each table's metrics and dimensions.

Note

If a query doesn't include SHOW, then it must include VISUALIZE to select the metric it renders.

Anchor to FROMFROM

Use FROM to choose the table or tables that a query runs against. A complete query includes one FROM clause.

A table's schema can include dimensions from related records, so a single-table query can group and filter by those related attributes without combining tables. The selected table also determines which related records are available to MATCHES filters.

Browse the schema reference to find each table's metrics and dimensions.

Anchor to TablesTables

Tables such as sales or customers can appear in FROM. Each table has a versioned schema that defines its metrics and dimensions. Select one table, or combine several in a multi-fact query when their schemas support it.

Anchor to campaign_products campaign_products: Product quantities and average order quantities for orders attributed to marketing campaigns. Learn more
Anchor to campaign_sales campaign_sales: Campaign-attributed orders, sales, and average order value across first-click, last-click, last non-direct click, and linear attribution models. Learn more
Anchor to campaign_sessions campaign_sessions: Campaign-attributed online store visits, including page views, cart additions, checkout progress, and conversion rates. Learn more
Anchor to chargebacks chargebacks: Payment disputes, including chargeback counts, amounts, rates, reasons, reported fraud, and Rapid Dispute Resolution status. Learn more
Anchor to collective_product_engagements collective_product_engagements: Engagement data for products sourced through Shopify Collective, including impressions and clicks per product per day
Anchor to customers customers: Customer records, including first-purchase timing, new customer counts, lifetime spend, order count, average spend per order, and days since last order. Learn more
Anchor to discounts discounts: Discounted order counts and discount amounts across product, order, shipping, and app-applied discounts. Learn more
Anchor to fees fees: Shopify Payments processing, foreign exchange, and Managed Markets fee amounts and averages. Learn more
Anchor to fulfillments fulfillments: Fulfillment speed and volume from order placement through fulfillment, shipping handoff, and delivery. Learn more
Anchor to gift_cards gift_cards: Gift card issuance, sales, adjustments, disablement, and starting and ending balances over time. Learn more
Anchor to global_searches global_searches: Global catalog search requests by product, product variant, store, and time. Learn more
Anchor to inventory inventory: Inventory item history by stock levels, units sold, days in stock or out of stock, sell-through rate, days remaining, and inventory value over time. Learn more
Anchor to inventory_adjustment_history inventory_adjustment_history: Inventory quantity changes and adjustment counts by reason, state, app, staff member, and reference document. Learn more
Anchor to inventory_by_location inventory_by_location: Inventory stock levels, net unit changes, days in stock or out of stock, days remaining, and value by location over time. Learn more
Anchor to inventory_shipments inventory_shipments: Shipped, received, accepted, and rejected quantities by carrier, tracking, status, and origin and destination. Learn more
Anchor to inventory_transfers inventory_transfers: Inventory transfer quantities and counts by item, origin, destination, app, and staff member. Learn more
Anchor to low_engagement_product_recommendations low_engagement_product_recommendations: Low-engagement product recommendations and their click-rate gap from the store's 30-day average. Learn more
Anchor to marketing_engagements marketing_engagements: Engagement, spend, order, and sales metrics reported by marketing channels for marketing events. Learn more
Anchor to payment_attempts payment_attempts: Payment attempt volume, failures, and authorization rates by payment method, amount, card details, and retry pattern. Learn more
Anchor to payments payments: Payment transaction amounts, refunds, gift card redemptions, payment methods, gateways, and transaction status by order and customer. Learn more
Anchor to payouts payouts: Payout amounts from payment transactions by payout currency and date. Learn more
Anchor to product_recommendation_conversions product_recommendation_conversions: Click, cart-addition, and purchase conversion metrics for online store product recommendations. Learn more
Anchor to profitability profitability: Per-order profitability before returns, including revenue, cost of goods sold, shipping costs, duties, import taxes, sales taxes, and fulfillment costs. Learn more
Anchor to returns returns: Returned item quantities by return status, reason, refund, return app, staff member, and line item. Learn more
Anchor to sales sales: Core sales metrics across channels, including revenue, orders, discounts, taxes, shipping, sales reversals, and customer type. Learn more
Anchor to sales_taxes sales_taxes: Tax amounts applied to sales and returns by tax name, rate, location, sales channel, product, and order. Learn more
Anchor to search_conversions search_conversions: Click, cart-addition, and purchase conversion metrics for online store search sessions. Learn more
Anchor to search_queries search_queries: Online store search query volume and result-click counts by query and click status. Learn more
Anchor to searches searches: Online store search query volume by query, intent, content type, and whether results were returned. Learn more
Anchor to sessions sessions: Online store visits, page views, traffic sources, devices, visitor locations, cart additions, checkout progress, and conversion rates. Learn more
Anchor to shipping_labels shipping_labels: Shipping label counts and costs by carrier, service, origin, destination, package, and order. Learn more
Anchor to shop_campaign_insights shop_campaign_insights: Sales, customers, ad spend, customer acquisition cost, and return on ad spend from Shop Campaigns. Learn more
Anchor to shop_post_purchase_offers shop_post_purchase_offers: Counts of customers who receive Shop post-purchase offers by discount code and time. Learn more
Anchor to shop_product_impressions shop_product_impressions: Product impression counts in the Shop channel by product and time. Learn more
Anchor to shopify_forms shopify_forms: Views, submissions, and conversion rates for Shopify Forms by form, page type, and product. Learn more
Anchor to shopify_tax_ca_country shopify_tax_ca_country: Taxes on Canadian sales transactions broken down by tax type (GST/HST, PST, QST, RST, VPT) and destination province, to help merchants file federal and provincial returns with the right authorities
Anchor to shopify_tax_ca_transactions shopify_tax_ca_transactions: Individual Canadian sales tax transactions, including tax type (GST/HST, PST, QST, RST, VPT), to provide detailed records for tax preparation and audit defense
Anchor to store_credit_summaries store_credit_summaries: Store credit starting balance, ending balance, and net change by date and currency. Learn more
Anchor to store_credit_transactions store_credit_transactions: Credited, debited, and expired store credit by event, source, customer, company, order, currency, and expiration date. Learn more
Anchor to subscriptions subscriptions: Subscription counts for active, added, and canceled subscriptions over time. Learn more
Anchor to web_performance web_performance: Online store page load, responsiveness, and visual stability metrics by page, device, browser, and storefront. Learn more

Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor

FROM sales

SHOW total_sales, orders

GROUP BY sales_channel

SINCE startOfDay(-30d) UNTIL today

ORDER BY total_sales DESC

LIMIT 10

Examples

Description
Rank sales channels by [`total_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-totalsales) over the last 30 days and return `orders` for each channel, keeping only the top 10. This example uses `FROM` to draw every metric and dimension from a single table, `sales`.
ShopifyQL
```
FROM sales
  SHOW total_sales, orders
  GROUP BY sales_channel
  SINCE startOfDay(-30d) UNTIL today
  ORDER BY total_sales DESC
  LIMIT 10
```
Description
Break down last month's [`total_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-totalsales) by `product_title` and `new_or_returning_customer`. This example uses `FROM` to group on dimensions the `sales` table carries from related records, so one table covers both product and customer type without a multi-fact query.
ShopifyQL
```
FROM sales
  SHOW total_sales, orders
  GROUP BY product_title, new_or_returning_customer
  DURING last_month
  ORDER BY total_sales DESC
  LIMIT 10
```

Anchor to Multi-fact queriesMulti-fact queries

Query several tables in one request by listing them as comma-separated tables in FROM, such as FROM sales, sessions. ShopifyQL joins the tables on the dimensions they share, so a query can return metrics from each table in the same result rows. Use a multi-fact query only when the selected schemas support being combined.

Group by the dimensions that the tables share: every GROUP BY field must exist with the same name in each selected schema.

Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor

FROM sales, customers

SHOW total_sales, new_customer_records

TIMESERIES day

SINCE -30d UNTIL today

ORDER BY day ASC

Examples

Description
Pair daily [`total_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-totalsales) with `new_customer_records` over the last 30 days. This example uses a multi-fact query to read `sales` and `customers` at once, joining them on the shared `day` so each row pairs revenue with the customers acquired that day.
ShopifyQL
```
FROM sales, customers
  SHOW total_sales, new_customer_records
  TIMESERIES day
  SINCE -30d UNTIL today
  ORDER BY day ASC
```
Description
Track daily [`total_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-totalsales), `sessions`, and `conversion_rate` for last month. This example uses a multi-fact query to combine `sales` and `sessions`.
ShopifyQL
```
FROM sales, sessions
  SHOW total_sales, sessions, conversion_rate
  TIMESERIES day
  DURING last_month
  ORDER BY day ASC
```

Anchor to Organization queriesOrganization queries

Use FROM ORGANIZATION to query a table across the stores in an organization for multi-store reporting. Filtering on shop_id also requires FROM ORGANIZATION.

Note

FROM ORGANIZATION requires organization analytics access and a table that supports multi-store reporting.

Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor

FROM ORGANIZATION sales

SHOW total_sales, shop_id

GROUP BY shop_id

DURING last_month

ORDER BY total_sales DESC

Examples

Description
Rank every store in an organization by last month's [`total_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-totalsales), grouped by `shop_id`. This example uses `FROM ORGANIZATION` to query the `sales` table across all stores at once, rather than a single store.
ShopifyQL
```
FROM ORGANIZATION sales
  SHOW total_sales, shop_id
  GROUP BY shop_id
  DURING last_month
  ORDER BY total_sales DESC
```
Description
Compare last month's [`total_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-totalsales) and `orders` for two named stores in an organization. This example uses [`WHERE`](/docs/api/shopifyql/latest/syntax/where) `shop_id IN` to narrow the organization-wide result to just the `shop_id` values you list, instead of every store.
ShopifyQL
```
FROM ORGANIZATION sales
  SHOW total_sales, orders
  WHERE shop_id IN (123456789, 987654321)
  GROUP BY shop_id
  DURING last_month
  ORDER BY total_sales DESC
```

Anchor to SHOWSHOW

Use SHOW to choose which metrics, dimensions, and expressions a query returns from the table or tables chosen in FROM.

ShopifyQL returns result columns in the same order that you list them. When a metric, dimension, or expression uses AS, the returned column uses that alias in the same position.

Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor

FROM sales

SHOW total_sales

TIMESERIES day

DURING last_week

ORDER BY day ASC

Examples

Description
Chart [`total_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-totalsales) as a daily series for last week. This example uses `SHOW` to name just the one metric, letting `TIMESERIES day` supply the date column that turns it into a trend.
ShopifyQL
```
FROM sales
  SHOW total_sales
  TIMESERIES day
  DURING last_week
  ORDER BY day ASC
```
Description
Show six [`sessions`](/docs/api/shopifyql/latest/schemas/sessions_and_behavior/sessions#sessionsmetric-propertydetail-sessions) metrics for last week: overall traffic (`sessions`, `conversion_rate`), then the funnel from `online_store_visitors` to `sessions_that_completed_checkout`. This example uses `SHOW` to return columns in the order you list them, so the row reads left to right in that exact order.
ShopifyQL
```
FROM sessions
  SHOW sessions, conversion_rate, online_store_visitors,
    sessions_with_cart_additions, sessions_that_reached_checkout,
    sessions_that_completed_checkout
  TIMESERIES day
  DURING last_week
  ORDER BY day ASC
```

Anchor to ExpressionsExpressions

An expression calculates a returned value instead of returning a metric or dimension directly. Use mathematical operators to combine numeric fields or literals from the selected table or tables. Add AS to give an expression a readable returned column name.

Anchor to addition addition +: Adds two numeric values.
Anchor to subtraction subtraction -: Subtracts one numeric value from another.
Anchor to multiplication multiplication *: Multiplies two numeric values.
Anchor to division division /: Divides one numeric value by another.

Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor

FROM sales

SHOW net_sales, shipping_charges,

net_sales + shipping_charges AS net_sales_with_shipping

DURING last_month

ORDER BY net_sales DESC

Examples

Description
Add [`net_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-netsales) and `shipping_charges` into a new `net_sales_with_shipping` column for last month. This example uses a `SHOW` expression to compute the sum inline, keeping both inputs beside the combined total.
ShopifyQL
```
FROM sales
  SHOW net_sales, shipping_charges,
    net_sales + shipping_charges AS net_sales_with_shipping
  DURING last_month
  ORDER BY net_sales DESC
```
Description
Subtract `discounts` from [`gross_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-grosssales) to report sales after discounts for last month. This example uses a `SHOW` expression to derive a metric the table doesn't expose directly, without leaving the query.
ShopifyQL
```
FROM sales
  SHOW gross_sales, discounts,
    gross_sales - discounts AS sales_after_discounts
  DURING last_month
  ORDER BY gross_sales DESC
```

Anchor to ASAS

Create an alias for a returned metric, dimension, or expression. Use AS with an unquoted identifier for simple names, or double quotes when the alias contains spaces. You can reference aliases in clauses that operate on returned values, such as HAVING and ORDER BY.

Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor

FROM customers

SHOW total_amount_spent AS customer_spend

GROUP BY month

SINCE startOfYear(0y) UNTIL today

ORDER BY month ASC

Examples

Description
Rename [`total_amount_spent`](/docs/api/shopifyql/latest/schemas/customers/customers#customersmetric-propertydetail-totalamountspent) to `customer_spend` by month, year to date. This example uses `AS` with an unquoted alias, which works when the new name is a single identifier with no spaces.
ShopifyQL
```
FROM customers
  SHOW total_amount_spent AS customer_spend
  GROUP BY month
  SINCE startOfYear(0y) UNTIL today
  ORDER BY month ASC
```
Description
Alias [`net_payments`](/docs/api/shopifyql/latest/schemas/finance_and_payments/payments#paymentsmetric-propertydetail-netpayments) to `"Net payments"` by payment method for last month. This example uses `AS` with a quoted alias so the name can contain spaces, reused with its quotes intact in `ORDER BY "Net payments"`.
ShopifyQL
```
FROM payments
  SHOW net_payments AS "Net payments"
  GROUP BY payment_method
  DURING last_month
  ORDER BY "Net payments" DESC
```
Description
Derive `sales_after_discounts` from [`gross_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-grosssales) minus `discounts` for last month. This example uses `AS` to give the inline `SHOW` expression a readable column name that other clauses can reference in place of the calculation.
ShopifyQL
```
FROM sales
  SHOW gross_sales, discounts,
    gross_sales - discounts AS sales_after_discounts
  DURING last_month
  ORDER BY gross_sales DESC
```

Was this page helpful?