---
title: ShopifyQL syntax reference
description: Learn how to use the ShopifyQL syntax for querying store data.
source_url:
  html: 'https://shopify.dev/docs/api/shopifyql'
  md: 'https://shopify.dev/docs/api/shopifyql.md'
---

# ShopifyQL syntax reference

[ShopifyQL](https://shopify.dev/docs/apps/build/shopifyql) is Shopify's query language built for commerce. This reference documents the ShopifyQL syntax that you can use to query merchant store data and build analytics and reporting tools.

***

## Overview

ShopifyQL's query syntax lets you create custom queries against store data. You can use ShopifyQL queries through the [GraphQL Admin API](https://shopify.dev/docs/apps/build/shopifyql/graphql-admin-api), with [Python scripts](https://shopify.dev/docs/apps/build/shopifyql/python-sdk-and-cli), directly in code editors, or through [Shopify's analytics tools](https://shopify.dev/docs/apps/build/shopifyql/shopify-admin).

***

## Glossary of terms

| Term | Definition |
| - | - |
| Dimension | An attribute that segments data so that it can be sorted and presented more clearly. |
| Keyword | Syntax that defines what query operation to perform. |
| Metric | A quantitative measurement of data, such as sales totals, order counts, or profit. |
| Parameter | Syntax that defines what data to return. |
| Operator | Syntax for performing logical or arithmetic operations on the query data. |

***

## Core syntax rules

Your query must include `FROM` and `SHOW` keywords with parameters. All other keywords are optional, but must follow a specific sequence:

1. `FROM`
2. `SHOW`
3. `WHERE`
4. `SINCE` and `UNTIL`, or `DURING`
5. `GROUP BY`
6. `TIMESERIES`
7. `COMPARE TO`
8. `HAVING`
9. `ORDER BY`
10. `LIMIT`
11. `WITH` (`TOTALS`, `GROUP_TOTALS`, `PERCENT_CHANGE`, `CUMULATIVE_VALUES`, `CURRENCY`, `TIMEZONE`)
12. `VISUALIZE`, `TYPE`, and `ANNOTATE`

### Formatting conventions

ShopifyQL uses two-level indentation where `FROM` and `VISUALIZE` are top-level keywords, and all other keywords are indented under `FROM`. The following example shows the formatting pattern:

```shopifyql
FROM sales
  SHOW total_sales
  WHERE billing_country = 'Canada'
  GROUP BY product_title
  SINCE last_month
  ORDER BY total_sales DESC
  LIMIT 10
VISUALIZE total_sales TYPE bar
```

***

## FROM and SHOW

The simplest query requires only these two keywords. `FROM` specifies dataset tables; `SHOW` selects columns to extract.

```shopifyql
FROM sales
  SHOW total_sales
```

***

## WHERE

Filters data by dimensions before aggregation. The `WHERE` keyword only supports dimensions, not metrics. Values must be wrapped in single quotes (`'`), not double quotes.

```shopifyql
FROM sales
  SHOW total_sales, product_title, product_type, product_vendor
  WHERE billing_country = 'Canada'
  GROUP BY product_title, product_type, product_vendor
```

### Comparison operators

* `=` (equal to)
* `!=` (not equal to)
* `<` (less than)
* `>` (greater than)
* `<=` (less than or equal to)
* `>=` (greater than or equal to)

### Logical operators

* `AND`
* `OR`
* `NOT`

### String matching

* `STARTS WITH`
* `ENDS WITH`
* `CONTAINS`

### MATCHES operator

Use the `MATCHES` to filter for collections of related entities. Use `MATCHES` and `NOT MATCHES` for semi-join expressions with named parameters:

WHERE \<expression> MATCHES (\<parameter\_list>)

WHERE \<expression> NOT MATCHES (\<parameter\_list>)

**Note:**

Each parameter can only be used once per `MATCHES` filter. For example, `WHERE orders_placed MATCHES (date > 2025-01-01, date < 2025-06-01)` isn't valid because `date` is used twice.

##### Filter by order activity

```shopifyql
FROM customers
SHOW customer_email, total_orders
WHERE orders_placed MATCHES (date > 2025-01-01)
```

##### Filter by email engagement

```shopifyql
FROM customers
SHOW customer_email, email_subscription_status
WHERE shopify_email.opened MATCHES (activity_id = 5240029206, date > 2025-01-01)
```

##### Exclude customers

```shopifyql
FROM customers
SHOW customer_email
WHERE products_purchased NOT MATCHES (date > 2025-01-01)
```

#### Available semi-join expressions

| Expression | Parameters | Description |
| - | - | - |
| `products_purchased` | `id`, `tag`, `category`, `date`, `sum_quantity`, `count` | Products purchased by customers. |
| `orders_placed` | `date`, `amount`, `location_id`, `app_id`, `count`, `sum_amount` | Orders placed by customers. |
| `shopify_email.opened` | `activity_id`, `date`, `count` | Email open events. |
| `shopify_email.clicked` | `activity_id`, `date`, `count` | Email click events. |
| `shopify_email.bounced` | `activity_id`, `date`, `count` | Email bounced events. |
| `shopify_email.marked_as_spam` | `activity_id`, `date`, `count` | Email marked as spam events. |
| `shopify_email.unsubscribed` | `activity_id`, `date`, `count` | Email unsubscribed events. |
| `storefront.product_viewed` | `id`, `date`, `count` | Customer events for products viewed. |
| `storefront.collection_viewed` | `id`, `date`, `count` | Customer events for collections viewed. |
| `store_credit_accounts` | `currency`, `balance`, `next_expiry_date`, `last_credit_date` | Customers who have a balance on a store. |
| `customer_within_distance` | `coordinates`, `distance_km` or `distance_mi` | Customer address within distance. |

***

## GROUP BY

Segments metrics by dimensions. If a dimension is used in the `SHOW` keyword, then it must also be included in the `GROUP BY` keyword.

```shopifyql
FROM sales
  SHOW billing_country, billing_region, total_sales
  GROUP BY billing_country, billing_region
```

### Time dimensions

Available grouping options: `second`, `minute`, `hour`, `day`, `week`, `month`, `quarter`, `year`, `hour_of_day`, `day_of_week`, `week_of_year`, `month_of_year`

***

## TIMESERIES

Distinguishes grouping by time dimensions and backfills dates in a query where data gaps exist.

```shopifyql
FROM sales
  SHOW total_sales
  TIMESERIES month
  SINCE last_year UNTIL today
```

### Valid options

* `day`
* `day_of_week` — zero-based day of week (0 = Monday, 1 = Tuesday, ..., 6 = Sunday)
* `hour`
* `hour_of_day` — zero-based hour (0–23)
* `minute`
* `month`
* `month_of_year` — month number (1–12)
* `quarter`
* `week`
* `week_of_year` — week number (1–53)
* `year`

***

## HAVING

Filters grouped results by metrics after aggregation. The `HAVING` keyword only supports metrics, not dimensions. Unlike `WHERE`, `HAVING` can reference aliases and aggregate functions. Requires `GROUP BY` or `TIMESERIES`.

```shopifyql
FROM sales
  SHOW total_sales
  GROUP BY product_title
  HAVING total_sales > 1000
    AND total_sales < 5000
```

***

## WITH

You can use `WITH` to change keyword behavior using these modifiers:

* `TOTALS`: Top-level metric summaries before dimensional breakdown.
* `GROUP_TOTALS`: Subtotals for grouped aggregations.
* `PERCENT_CHANGE`: Adds percentage change columns with `COMPARE TO`.
* `CUMULATIVE_VALUES`: Generates running total columns for additive metrics.
* `CURRENCY`: Displays data in specified currency codes (three-letter format).
* `TIMEZONE`: Displays data in specified timezone (IANA format, for example, `America/New_York`).

##### Totals modifier

```shopifyql
FROM sales
  SHOW total_sales
  GROUP BY billing_region WITH TOTALS
```

##### Cumulative values

```shopifyql
FROM sales
  SHOW net_sales
  TIMESERIES day WITH CUMULATIVE_VALUES
  DURING last_month
```

### CUMULATIVE\_​VALUES

When `WITH CUMULATIVE_VALUES` is specified, ShopifyQL automatically adds cumulative columns for each eligible additive metric using the naming pattern: `{metric_name}__cumulative`.

Cumulative values require `TIMESERIES` or `ORDER BY <time>` to establish time-based ordering:

* Eligible metrics can be ordered by time, such as `net_sales`, `gross_sales`, `orders`, `units_sold`, `customers`, `sessions`, `revenue`, `taxes`, `shipping`, `discounts`, or `sales_reversals`.
* Ineligible metrics are those that can't be ordered by time, such as `average_order_value`, `conversion_rate`, `cart_abandonment_rate`, or `growth_rate`.

In this example, the `net_sales` column tracks sales on each day while the `net_sales_cumulative` column tracks cumulative sales:

| day | net\_sales | net\_sales\_\_cumulative |
| - | - | - |
| 2024-12-01 | $1,200.00 | $1,200.00 |
| 2024-12-02 | $950.00 | $2,150.00 |
| 2024-12-03 | $1,400.00 | $3,550.00 |

***

## SINCE and UNTIL

Filter by time periods. If `SINCE` lacks `UNTIL`, defaults to today.

```shopifyql
FROM sales
  SHOW net_sales
  WHERE billing_country = 'Canada'
  GROUP BY month
  SINCE -12m UNTIL yesterday
```

### Offset operators

* `-{#}s` (seconds)
* `-{#}min` (minutes)
* `-{#}h` (hours)
* `-{#}d` (days)
* `-{#}w` (weeks)
* `-{#}m` (months)
* `-{#}q` (quarters)
* `-{#}y` (years)
* Specific dates: `yyyy-MM-dd`

### Date functions

* `startOfDay()`
* `startOfMonth()`
* `startOfWeek()`
* `startOfQuarter()`
* `startOfYear()`

***

## DURING

Simplifies date filtering using named ranges instead of `SINCE`/`UNTIL` combinations.

```shopifyql
FROM sales
  SHOW total_sales
  DURING last_month
```

### Named ranges

Available named ranges include `today`, `yesterday`, `this_week`, `last_week`, `this_weekend`, `last_weekend`, `this_month`, `last_month`, `this_quarter`, `last_quarter`, `this_year`, `last_year`, and `bfcmYYYY`.

***

## COMPARE TO

Compares data across multiple date ranges. Supports absolute dates, named dates, offset dates, and multiple comparisons.

##### Year-over-year comparison

```shopifyql
FROM sales
  SHOW net_sales, product_title
  GROUP BY product_title
  TIMESERIES day
  SINCE -1m UNTIL 0m
  COMPARE TO previous_year
```

##### Match day of week

```shopifyql
FROM sales
  SHOW total_sales
  TIMESERIES day
  SINCE -7d
  COMPARE TO previous_year_match_day_of_week
```

### Comparison options

* `previous_period`: The directly preceding period.
* `previous_year`: Same calendar dates one year ago.
* `previous_month`: Same calendar dates one month ago.
* `this_month`: The current month.
* `last_month`: The previous month.
* `previous_year_match_day_of_week`: Same days of the week, shifted back 52 weeks.

The `previous_year_match_day_of_week` option aligns days of the week when making year-over-year comparisons, which is important for retail reporting where weekday patterns are significant.

### Benchmarks

You can compare your store's data against benchmarks for specific metrics within reports using `COMPARE TO benchmarks`. For more information, see the [benchmarks documentation](https://help.shopify.com/en/manual/reports-and-analytics/shopify-reports/report-types/custom-reports/benchmarks).

```shopifyql
FROM sales
  SHOW total_sales
  TIMESERIES day
  SINCE startOfDay(-30d) UNTIL today
  COMPARE TO benchmarks
VISUALIZE total_sales TYPE line
```

***

## ORDER BY

Specifies sort direction using `ASC` (ascending) or `DESC` (descending).

```shopifyql
FROM sales
  SHOW net_sales
  GROUP BY product_title, product_type
  ORDER BY product_title, product_type DESC
```

***

## LIMIT

Restricts returned rows (defaults to 1000). Optional `OFFSET` parameter skips rows.

```shopifyql
FROM sales
  SHOW gross_sales AS total_gross_sales
  GROUP BY product_title
  ORDER BY total_gross_sales DESC
  LIMIT 10
```

***

## VISUALIZE and TYPE

Renders data graphically. If `TYPE` isn't included in your query, then ShopifyQL automatically selects the most suitable visualization. Use `MAX` to limit the number of data points displayed.

##### Line chart

```shopifyql
FROM sales
  SHOW gross_sales
  TIMESERIES month
VISUALIZE gross_sales TYPE line
```

##### Bar chart with limit

```shopifyql
FROM sales
  SHOW total_sales
  GROUP BY product_title
VISUALIZE total_sales TYPE bar MAX 5
```

### Supported visualization types

| Type | Description |
| - | - |
| `bar` | Vertical bar chart. |
| `horizontal_bar` | Horizontal bar chart. |
| `grouped_bar` | Grouped vertical bars. |
| `horizontal_grouped_bar` | Grouped horizontal bars. |
| `stacked_bar` | Stacked vertical bars. |
| `stacked_horizontal_bar` | Stacked horizontal bars. |
| `single_stacked_bar` | Single stacked bar. |
| `line` | Line chart. |
| `stacked_area` | Stacked area chart. |
| `histogram` | Histogram distribution. |
| `donut` | Circular chart with center hole. |
| `funnel` | Step by step view through a process. |
| `heatmap` | Two-dimensional grid. |
| `single_metric` | Single metric display. |
| `list` | List display. |
| `list_with_dimension_values` | List with dimension values. |
| `table` | Tabular data. |
| `rfm_grid` | RFM (Recency, Frequency, Monetary) segmentation grid for customer analysis. |
| `target_gauge` | Gauge showing progress towards an analytics target. |

### Visualization modifiers

* `MAX number`: Limits the number of data points in the visualization.
* `LIMIT number`: Deprecated alias for `MAX`.

### ANNOTATE

Overlays contextual event markers on a chart visualization. `ANNOTATE` is an optional sub-clause of `VISUALIZE` that highlights events such as product changes, app installations, or theme updates alongside your data.

Use `ANNOTATE ALL` to include all annotation categories, or specify individual categories:

##### Annotate with specific categories

```shopifyql
FROM sales
  SHOW total_sales
  TIMESERIES day
  SINCE -30d UNTIL today
VISUALIZE total_sales TYPE line
  ANNOTATE product_events, online_store_events
```

##### Annotate with all categories

```shopifyql
FROM sessions
  SHOW conversion_rate
  TIMESERIES month
VISUALIZE conversion_rate TYPE line ANNOTATE ALL
```

#### Annotation categories

| Category | Description |
| - | - |
| `product_events` | Product lifecycle events: publishing, unpublishing, and price changes. |
| `online_store_events` | Online store theme changes: theme publishing and code edits. |
| `app_events` | App lifecycle events: installations and uninstallations. |
| `system_events` | System-level events: definition changes and data availability. |

#### Event types

Each category contains specific event types. Use dot syntax to request individual event types instead of an entire category:

| Category | Event types |
| - | - |
| `product_events` | `published`, `unpublished`, `price_changed` |
| `online_store_events` | `theme_published`, `theme_code_edited` |
| `app_events` | `app_installed`, `app_uninstalled` |
| `system_events` | `definition_change`, `data_unavailable` |

##### Dot syntax for specific event types

```shopifyql
FROM sales
  SHOW total_sales
  TIMESERIES day
  SINCE -30d UNTIL today
VISUALIZE total_sales TYPE line
  ANNOTATE product_events.published, product_events.price_changed, online_store_events.theme_published
```

##### Mix categories and event types

```shopifyql
FROM sales
  SHOW total_sales
  TIMESERIES day
  SINCE -30d UNTIL today
VISUALIZE total_sales TYPE line
  ANNOTATE product_events.published, system_events
```

**Note:**

`ANNOTATE` isn't supported with `FROM ORGANIZATION` queries.

***

## AS (aliases)

Renames columns with aliases. Quote aliases containing spaces.

```shopifyql
FROM sales
  SHOW total_sales AS "My Total Sales"
```

***

## TOP N

Displays top items by category, grouping remainder as "Other."

```shopifyql
FROM sales
  SHOW gross_sales
  GROUP BY day, TOP 5 product_title
  TIMESERIES day
  SINCE startOfDay(-30d) UNTIL today
```

### Options

* `ONLY TOP N`: Hides remainder.
* `TOP N OVERALL`: Ranks across full range.

***

## Mathematical operators

Perform arithmetic on metrics: `+`, `-`, `×`, `÷`

```shopifyql
FROM sales
  SHOW (net_sales + sales_reversals) AS order_value, orders
  GROUP BY billing_region
```

***

## Implicit joins

ShopifyQL automatically joins multiple tables intelligently. Join field must have the same name in all joined schemas and must be in `GROUP BY`.

```shopifyql
FROM sales, sessions
  SHOW day, total_sales, sessions
  GROUP BY day
```

***

## Comments

Add comments to your queries for documentation.

##### Single-line comments

```shopifyql
-- This is a comment
  FROM sales
  SHOW total_sales
```

##### Multi-line comments

```shopifyql
/* This is a
 multi-line comment */FROM sales
  SHOW total_sales
```

***

## Multi-store reporting

Organizations with multiple stores can query across stores.

```shopifyql
FROM ORGANIZATION sales
  SHOW total_sales
  WHERE shop_id IN (12301, 12302, 12303)
  GROUP BY shop_name
```

***

## Rate limiting

When you use ShopifyQL with the GraphQL Admin API, [`shopifyqlQuery`](https://shopify.dev/docs/api/admin-graphql/latest/queries/shopifyqlQuery) uses separate rate limits based on query complexity. Query complexity depends on factors like the following:

* Number of keywords and clauses used
* Number of metrics in the `SHOW` clause
* Number of dimensions in the `GROUP BY` clause

If you reach the rate limit, then the API returns a 429 error. The rate limit uses a 60-second timer, so you'll need to wait until it resets before sending another query.

***

## Segment query language

The segment query language is a different implementation of ShopifyQL that uses a subset of ShopifyQL. The segment query language only uses the `WHERE` clause from ShopifyQL to filter customers by their attributes.

You can use the segment query language to create a collection of customers that are filtered out by specific criteria. Filtered customers in a collection are called "segment members", and the collections of filtered customers are called "segments". Merchants can create segments in the Shopify admin.

For a complete reference of the segment query language, refer to the [segment query language reference](https://shopify.dev/docs/api/shopifyql/segment-query-language-reference).

***

## Metafields

With metafields, you can analyze custom [metafields](https://shopify.dev/docs/apps/build/custom-data/metafields) data stored in your shop alongside standard Shopify metrics. You can use metafields in `WHERE`, `GROUP BY`, and `SHOW` clauses to filter, segment, and display custom attributes.

### Requirements

To use a metafield in ShopifyQL, it must have a [metafield definition](https://shopify.dev/docs/apps/build/custom-data/metafields/definitions) with `use_in_analytics` enabled.

### Supported owner types

* `customer`
* `order`
* `product`
* `product_variant`

### Supported metafield types

**Scalar types:** `single_line_text_field`, `multi_line_text_field`, `number_integer`, `number_decimal`, `date`, `date_time`, `url`, `boolean`, `color`, `id`, `product_reference`, `rating`

**List types:** `list.single_line_text_field`, `list.multi_line_text_field`, `list.number_integer`, `list.number_decimal`

### Syntax

Reference metafields using the following pattern:

\<owner\_type>.metafields.\<namespace>.\<key>

For example, a customer metafield with namespace `custom` and key `membership_level` would be referenced as:

customer.metafields.custom.membership\_level

### Examples

##### Filter by metafield

```shopifyql
FROM customers
  SHOW customer_name, total_amount_spent
  WHERE customer.metafields.custom.membership_level = 'gold'
  GROUP BY customer_name
```

##### Group by metafield

```shopifyql
FROM sales
  SHOW total_sales
  GROUP BY product.metafields.custom.category
```

##### Multiple metafields across owner types

```shopifyql
FROM sales
  SHOW total_sales
  WHERE customer.metafields.custom.vip = true
    AND product.metafields.reviews.rating > 4
  GROUP BY product_title
```

***