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

ExpandOn this page

* [Overview](https://shopify.dev/docs/api/shopifyql.md#overview)
* [Glossary of terms](https://shopify.dev/docs/api/shopifyql.md#glossary-of-terms)
* [Core syntax rules](https://shopify.dev/docs/api/shopifyql.md#core-syntax-rules)
* [FROM and SHOW](https://shopify.dev/docs/api/shopifyql.md#from-and-show)
* [WHERE](https://shopify.dev/docs/api/shopifyql.md#where)
* [GROUP BY](https://shopify.dev/docs/api/shopifyql.md#group-by)
* [TIMESERIES](https://shopify.dev/docs/api/shopifyql.md#timeseries)
* [HAVING](https://shopify.dev/docs/api/shopifyql.md#having)
* [WITH](https://shopify.dev/docs/api/shopifyql.md#with)
* [SINCE and UNTIL](https://shopify.dev/docs/api/shopifyql.md#since-and-until)
* [DURING](https://shopify.dev/docs/api/shopifyql.md#during)
* [COMPARE TO](https://shopify.dev/docs/api/shopifyql.md#compare-to)
* [ORDER BY](https://shopify.dev/docs/api/shopifyql.md#order-by)
* [LIMIT](https://shopify.dev/docs/api/shopifyql.md#limit)
* [VISUALIZE and TYPE](https://shopify.dev/docs/api/shopifyql.md#visualize-and-type)
* [AS (aliases)](https://shopify.dev/docs/api/shopifyql.md#as-aliases)
* [TOP N](https://shopify.dev/docs/api/shopifyql.md#top-n)
* [Mathematical operators](https://shopify.dev/docs/api/shopifyql.md#mathematical-operators)
* [Implicit joins](https://shopify.dev/docs/api/shopifyql.md#implicit-joins)
* [Comments](https://shopify.dev/docs/api/shopifyql.md#comments)
* [Multi-store reporting](https://shopify.dev/docs/api/shopifyql.md#multi-store-reporting)
* [Segment query language](https://shopify.dev/docs/api/shopifyql.md#segment-query-language)

# 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` and `TYPE`

### 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'
  SINCE last_month
  GROUP BY product_title
  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

Info

The `MATCHES` operator is currently only available in [customer segmentation](https://shopify.dev/docs/api/shopifyql/segment-query-language-reference) and isn't supported in Shopify Analytics.

Use the `MATCHES` to filter for collections of related entities, consistent with customer segmentation syntax. 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 > -365d, date < today)` 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 > -365d)
```

##### Filter by email engagement

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

##### Exclude customers

```shopifyql
FROM customers
  SHOW customer_email
  WHERE products_purchased NOT MATCHES (date > -365d)
```

#### 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
  SINCE last_year
  UNTIL today
  TIMESERIES month
```

### Valid options

* `day`
* `day_of_week`
* `hour`
* `hour_of_day`
* `minute`
* `month`
* `month_of_year`
* `quarter`
* `week`
* `week_of_year`
* `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
  DURING last_month
  TIMESERIES day
  WITH CUMULATIVE_VALUES
```

### 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 `returns`.
* 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'
  SINCE -12m
  UNTIL yesterday
  GROUP BY month
```

### 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
  SINCE -1m
  UNTIL -0m
  GROUP BY product_title
  TIMESERIES day
  COMPARE TO previous_year
```

##### Match day of week

```shopifyql
FROM sales
  SHOW total_sales
  SINCE -7d
  TIMESERIES day
  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. |

### Visualization modifiers

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

***

## 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
  SINCE startOfDay(-30d)
  UNTIL today
  GROUP BY day, TOP 5 product_title
  TIMESERIES day
```

### 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 + returns) 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
```

***

## 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).

***

* [Overview](https://shopify.dev/docs/api/shopifyql.md#overview)
* [Glossary of terms](https://shopify.dev/docs/api/shopifyql.md#glossary-of-terms)
* [Core syntax rules](https://shopify.dev/docs/api/shopifyql.md#core-syntax-rules)
* [FROM and SHOW](https://shopify.dev/docs/api/shopifyql.md#from-and-show)
* [WHERE](https://shopify.dev/docs/api/shopifyql.md#where)
* [GROUP BY](https://shopify.dev/docs/api/shopifyql.md#group-by)
* [TIMESERIES](https://shopify.dev/docs/api/shopifyql.md#timeseries)
* [HAVING](https://shopify.dev/docs/api/shopifyql.md#having)
* [WITH](https://shopify.dev/docs/api/shopifyql.md#with)
* [SINCE and UNTIL](https://shopify.dev/docs/api/shopifyql.md#since-and-until)
* [DURING](https://shopify.dev/docs/api/shopifyql.md#during)
* [COMPARE TO](https://shopify.dev/docs/api/shopifyql.md#compare-to)
* [ORDER BY](https://shopify.dev/docs/api/shopifyql.md#order-by)
* [LIMIT](https://shopify.dev/docs/api/shopifyql.md#limit)
* [VISUALIZE and TYPE](https://shopify.dev/docs/api/shopifyql.md#visualize-and-type)
* [AS (aliases)](https://shopify.dev/docs/api/shopifyql.md#as-aliases)
* [TOP N](https://shopify.dev/docs/api/shopifyql.md#top-n)
* [Mathematical operators](https://shopify.dev/docs/api/shopifyql.md#mathematical-operators)
* [Implicit joins](https://shopify.dev/docs/api/shopifyql.md#implicit-joins)
* [Comments](https://shopify.dev/docs/api/shopifyql.md#comments)
* [Multi-store reporting](https://shopify.dev/docs/api/shopifyql.md#multi-store-reporting)
* [Segment query language](https://shopify.dev/docs/api/shopifyql.md#segment-query-language)