> Note: > The ShopifyQL API is being [sunset as of version 2024-07](https://shopify.dev/changelog/shopifyql-admin-graphql-api-sunset). > Please consider using alternative sources of data in the [Admin GraphQL API](https://shopify.dev/docs/api/admin-graphql) for your use-cases, such as the [Orders API](https://shopify.dev/docs/api/admin-graphql/latest/queries/orders), and the [Web Pixels API](https://shopify.dev/docs/api/web-pixels-api). > You can continue using ShopifyQL API on GraphQL API version `2024-04`. This page describes ShopifyQL syntax, keywords, and keyword parameters. The examples on this page use the orders and products datasets in Shopify stores. For information about available metrics, dimensions, and aggregates, refer to the [orders dataset](/docs/api/shopifyql/datasets/orders-dataset), [products dataset](/docs/api/shopifyql/datasets/products-dataset), or [payment attempts dataset](/docs/api/shopifyql/datasets/payment-attempts-dataset). ## Syntax ShopifyQL follows the syntax below. You can place the entire query on one line or on separate lines.

## Keywords Keywords need to follow the [syntax order](#syntax), otherwise, errors occur. This section describes the required keywords in ShopifyQL queries. ### Required keywords A ShopifyQL query must contain at least the `FROM` and `SHOW` keywords.

### Keyword order Keywords need to be in the following order: - `FROM` - `SHOW` | `VISUALIZE` - `GROUP BY` - `WHERE` - (`SINCE` & `UNTIL`) | `DURING` - `COMPARE TO` - `ORDER BY` - `LIMIT` ### FROM - `FROM { table_name }` - `FROM` accepts one parameter, `table_name`, where `table_name` is a table. ### SHOW - `SHOW { column1 AS { alias } , column2 AS { alias } , ... }` - `SHOW` accepts any number of parameters, where each parameter is a column in a table or an expression. - Each parameter optionally accepts an `alias` using the [`AS`](#as) keyword.

### AS - `AS { alias }` - `AS` accepts one parameter, which is an alias for a column name in a table, or an alias for the return value of an [aggregate function](#aggregate-functions). If an alias has a space in the name, then surround the alias with double quotes. - `AS` can used with both the [`SHOW`](#show) and [`VISUALIZE`](#visualize) keywords.

> Note: > The double quotes are necessary when the alias contains a space. ### VISUALIZE - `VISUALIZE { column1 AS { alias } , column2 AS { alias } , ... }` - `VISUALIZE` accepts any number of parameters, where each parameter is a column in a table or an expression. - Each parameter optionally accepts an `alias` using the [`AS`](#as) keyword. - `VISUALIZE` returns axis labels and data formatting information for creating data visualizations. - Optionally accepts a `visualization_type` using the [TYPE](#type) keyword.

The sales are depicted as a single line, with the x-axis labeled as `month`, and the y-axis as `sum_net_sales`. #### TYPE - `TYPE { visualization_type }` - `TYPE` accepts one parameter, `visualization_type`, where `visualization_type` is `line` or `bar`. - `line` returns a line graph. - `bar` returns a bar graph. - `TYPE` is an optional keyword. If `TYPE` isn't used, then ShopifyQL returns a visualization that's appropriate for the submitted query.

### GROUP BY - `GROUP BY { dimension | date }` - `GROUP BY` accepts any number of parameters, where each is a `dimension` or [time dimension](#time-dimensions). A `dimension` is a field in a table. - Each parameter optionally accepts an `alias_name` using the [`AS`](#as) keyword. - If there isn't a return value for the specified `dimension`, `expression`, or `date`, then the `dimension`, `expression`, or `date` isn't returned. If you want to return the `dimension`, `expression`, or `date` when there's no data present, then you can use the [`ALL`](#all) modifier. > Note: > `GROUP BY` is similar to the [SQL `GROUP BY`](https://www.w3schools.com/sql/sql_groupby.asp) statement.

#### ALL - `GROUP BY { dimension | date } ALL` - `ALL` is an optional [`GROUP BY`](#group-by) modifier which can only be used with a `date`. - The `ALL` modifier fills in zeros for any `date` where data isn't present. - The `ALL` modifier enables you to retrieve continuous date periods. This allows you to get continuous date periods without having to perform joins to date lookup tables. - When using the `ALL` modifier [`SINCE`](#since-and-until) or [`DURING`](#during) must also be specified.

### WHERE - `WHERE { condition }` - `WHERE` accepts one parameter, `condition`, where `condition` is an expression that consists of one or more [comparison operators](#comparison-operators) and [logical operators](#logical-operators). - `WHERE` filters the results of an entire query based on the specified `condition`.

### SINCE and UNTIL - `SINCE { date_offset }` - `SINCE` accepts one parameter, `date_offset`, where `date_offset` is a [date range operator](#date-range-operators). The date range operator that's used for `start_date_offset` sets a starting date in a date range. This date is included in the range. - `UNTIL { date_offset }` - `UNTIL` accepts one parameter, `date_offset`, where `date_offset` is a [date range operator](#date-range-operators). The date range operator that's used for `end_date_offset` sets an ending date in a date range. This date is included in the range. - If `UNTIL` isn't used in a query, then `today` is used as the ending date.

### DURING - `DURING { named_date_range }` - `DURING` accepts one parameter, `named_date_range`, where `named_date_range` is a [named date range operator](#named-date-range-operators). - `DURING` is an optional keyword that replaces [`SINCE` and `UNTIL`](#since-and-until) statements. - This keyword helps to filter the query results for known time periods such as a calendar year or a specific month, or to filter the query results for date ranges that have different dates every year, such as Black Friday Cyber Monday.

### COMPARE TO - `COMPARE TO { named_date_range | relative_date_range }` - `COMPARE TO` is an optional keyword which is paired with [`SINCE` and `UNTIL`](#since-and-until) or [`DURING`](#during). - When paired with [`SINCE` and `UNTIL`](#since-and-until) `COMPARE TO` accepts a `relative_date_range` (see [relative date range operators](#relative-date-range-operators)). - While using the `relative_date_range` `previous_year` the [`SINCE` and `UNTIL`](#since-and-until) specified length of time must be a year or less. - When paired with [`DURING`](#during), `COMPARE TO` accepts either a `named_date_range` (see [named date range operators](#named-date-range-operators)) or a `relative_date_range` (see [relative date range operators](#relative-date-range-operators)). - While using a `named_date_range` it must match the length of the parameter in [`DURING`](#during). - This keyword allows you to compare data across the `date_offset` in [`SINCE` and `UNTIL`](#since-and-until) or [`DURING`](#during) to the `COMPARE TO` `named_date_range` or `relative_date_range`.

### ORDER BY - `ORDER BY { column } ASC | DESC` - `ORDER BY` can accept a list of one or more parameters. Each parameter consists of one or two elements, `column`, and an optional `ASC` or `DESC`. `column` is a column in a table, and `ASC` or `DESC` change the behavior of the returned results. - The `ASC` parameter is used after the `column` parameter, and indicates that the returned query results are sorted in an ascending order. - The `DESC` parameter is used after the `column` parameter, and indicates that the returned query results are sorted in a descending order. - If the `ASC` or `DESC` parameters aren't used, then the default sorting order is ascending. - If `ORDER BY` has multiple columns, then the results are first sorted by the first `column`, and then sorted by the second `column`, and so on. - The columns used in the `ORDER BY` parameters must be present in either the `SHOW`, `BY` or `OVER` parameter list.

### LIMIT - `LIMIT { number }` - `LIMIT` accepts one parameter, `number`, where `number` is a number that represents how many rows that you want the query to return. - `LIMIT` enables you to understand the data in each column without returning all of the data in the table. This is useful for larger tables where queries can take longer to return values. You can also use `LIMIT` with [`ORDER BY`](#order-by) to create lists of the top or bottom-most X. For example, the top five products.

## Time dimensions The time functions are abstracted so you don’t have to keep track of which date field corresponds to the grain of the available datasets. The following date fields are available as time dimensions in ShopifyQL: | Date field | Description | |----------------|-----------------------------------------| | `hour` | Groups by hour of calendar day | | `day` | Groups by calendar day | | `week` | Groups by calendar week | | `month` | Groups by calendar month | | `quarter` | Groups by calendar quarter | | `year` | Groups by calendar year | | `hour_of_day` | Groups by 24 hours (1, 2, ..., 24) | | `day_of_week` | Groups by day of week (M, T, W, ..., S) | | `week_of_year` | Groups by week of year (1, 2, ..., 52) |

## Date range operators You can use the following date range operators as well as a [named date range operator](#named-date-range-operators) in [`SINCE` and `UNTIL`](#since-and-until) statements. | Date range operator | Description | |---------------------|-------------------------------------------------------------------------| | `{-} {#} d` | The number of calendar days ago from the day that the query was run | | `{-} {#} w` | The number of calendar weeks ago from the day that the query was run | | `{-} {#} m` | The number of calendar months ago from the day that the query was run | | `{-} {#} q` | The number of calendar quarters ago from the day that the query was run | | `{-} {#} y` | The number of calendar years ago from the day that the query was run | | `yyyy-mm-dd` | A specific date | > Note: > Date range operators truncate to the start of the time grain (day, week, month, quarter, and year) when used with `SINCE` and to the end of the time grain when used with `UNTIL`.

### Named date range operators [`SINCE` and `UNTIL`](#since-and-until), [`DURING`](#during), and [`COMPARE TO`](#compare-to) accept any of the following named date range date operators below: | Date Range Operator | Description | |---------------------|------------------------------------------------------------------| | `today` | The day that the query was run | | `yesterday` | The previous 24-hour period from the time that the query was run | | `this_week` | The current calendar week | | `this_month` | The current calendar month | | `this_quarter` | The current calendar quarter | | `this_year` | The current calendar year | | `last_week` | The previous calendar week | | `last_month` | The previous calendar month | | `last_quarter` | The previous calendar quarter | | `last_year` | The previous calendar year | | `bfcm2022` | November 25 to November 28 2022 | | `bfcm2021` | November 26 to November 29 2021 | | `bfcm2020` | November 27 to November 30 2020 | | `bfcm2019` | November 29 to December 2 2019 | | `bfcm2018` | November 23 to November 26 2018 | | `bfcm2017` | November 24 to November 27 2017 | | `bfcm2016` | November 25 to November 28 2016 | ### Relative date range operators Relative operators return the same length of time as the base date range, shifted back by the specified period. [`COMPARE TO`](#compare-to) accepts the following relative date range operators: | Date Range Operator | Description | |---------------------|------------------------------------------------------------------| | `previous_period` | One period before the base date range | | `previous_year` | One year before the base date range | The following table demonstrates how `previous_period` and `previous_year` work with base periods specified by [`DURING`](#during): | `DURING` | `COMPARE TO previous_period` | `COMPARE TO previous_year` | |---------------|-------------------------------|--------------------------------------------------------------------| | `today` | `yesterday` | same date, in the previous year (Feb 29 = Mar 1, Feb 28 = Feb 28) | | `yesterday` | day before `yesterday` | yesterday, in the previous year (Feb 29 = Mar 1, Feb 28 = Feb 28) | | `this_week` | `last_week` | same week of year as `this_week` but the in previous year | | `last_week` | week before `last_week` | same week of year as `last_week` but the in previous year | | `this_month` | `last_month` | same month as `this_month` but in the previous year | | `last_month` | month before `last_month` | same month as `last_month` but in the previous year | | `this_quarter`| `last_quarter` | same quarter as `this_quarter` but in the previous year | | `last_quarter`| quarter before `last_quarter` | same quarter as `last_quarter` but in the previous year | | `this_year` | `last_year` | `last_year` | | `last_year` | year before `last_year` | year before `last_year` | | `bfcm2022` | `bfcm2021` | `bfcm2021` | | `bfcm2021` | `bfcm2020` | `bfcm2020` | The following table demonstrates how `previous_period` and `previous_year` work with base periods specified by [`SINCE` and `UNTIL`](#since-and-until): | `SINCE` | `UNTIL` | `COMPARE TO previous_period` | `COMPARE TO previous_year` | |---------------|---------------|-----------------------------------------------------------------------|-------------------------------------------------------------------------------| | `today` | `today` | `yesterday` | same date, in the previous year (Feb 29 = Mar 1, Feb 28 = Feb 28) | | `yesterday` | `yesterday` | day before `yesterday` | yesterday, in the previous year (Feb 29 = Mar 1, Feb 28 = Feb 28) | | `this_week` | `this_week` | `last_week` | same week of year as `this_week` but the in previous year | | `bfcm2022` | `bfcm2022` | `bfcm2021` | `bfcm2021` | | `yesterday` | `today` | the same length of time as the base period but before the base period | the same two days but in the previous year | | `-2m` | `1m` | the same length of time as the base period but before the base period | the start of the previous two months to the end of last month but in the previous year | | `-1m` | `this_week` | the same length of time as the base period but before the base period | the start of last month to the end of this week but in the previous year | | `2021-11-03` | `2022-11-15` | the same length of time as the base period but before the base period | invalid, the period of 377 days is greater than a single year | > Note: > When using `SINCE` and `UNTIL` with `COMPARE TO previous_period` the previous period will be precisely the same length of time as defined in the `SINCE` and `UNTIL` base period. This is true even in special cases such as leap years, daylight savings, and months with varying number of days.

## Comparison operators You can use the following comparison operators in [`WHERE`](#where) statements. | Comparison operator | Description | |---------------------|--------------------------| | `=` | Equal to | | `!=` | Not equal to | | `<` | Less than | | `>` | Greater than | | `<=` | Less than or equal to | | `>=` | Greater than or equal to | ## Logical operators You can use one or more of the following logical operators in [`WHERE`](#where) statements. | Logical operator | Description | |------------------|--------------------------------------------------------------------------------------------| | `AND` | Return all rows where the conditions that are separated by an `AND` are satisfied | | `OR` | Return all rows where either of the conditions that are separated by an `OR` are satisfied | | `NOT` | Return all rows where the conditions aren't satisfied |

## Mathematical operators You can use the following mathematical operators on numerical data. | Mathematical operator | Description | |-----------------------|----------------------| | `+` | Add two numbers | | `-` | Subtract two numbers | | `*` | Multiple two numbers | | `/` | Divide two numbers |

## Aggregate functions You can use the following functions to aggregate columns, and group columns by dimensions. | Aggregate function | Description | |---------------------|------------------------------------------------| | `count()` | Return the number of instances in a result set | | `sum()` | Return the sum of values in a result set | | `min()` | Return the lowest value in a result set | | `max()` | Return the highest value in a result set | | `avg()` | Return the average value in a result set | The `sum()`, `min()`, `max()`, and `avg()` functions can only be used with numerical values, while `count()` can be used to count different instances of dimensional attributes. You can't use aggregated fields as arguments in the functions.

The following query returns the aggregated sum of net sales and ordered quantity as a result of the `sum()` function, and the count of cities as a result of the `count()` function. These metrics are broken down by region for all regions in the United States in 2021.

## Comments Single line comments start with `--` and end at the end of the line. Multi-line comments start with `/*` and end with `*/`. You can use comments to explain sections of ShopifyQL statements, or to prevent the execution of a ShopifyQL statement. Any text within a comment will be ignored during execution time.