LayersQL syntax reference

Clause ordering and optionality

Required: FROM · SHOW · (SINCE or DURING)
Optional: GROUP BY · WHERE · HAVING · UNTIL · TIMESERIES · SEGMENT BY · COMPARE TO · ORDER BY · WITH · VISUALIZE
Canonical order: FROM → SHOW → (GROUP BY / WHERE / SEGMENT BY) → [HAVING] → (SINCE [UNTIL] | DURING) → [TIMESERIES] → [SEGMENT BY] → [COMPARE TO] → [ORDER BY] → [WITH …] → [VISUALIZE]

The parser accepts flexible ordering of WHERE, GROUP BY, and SEGMENT BY clauses before the time bound. SEGMENT BY can also appear after the time bound or TIMESERIES for backwards compatibility. Use either SINCE (with an optional UNTIL) or DURING to bound a query, but not both. DURING is a single-token shortcut for common ranges; SINCE/UNTIL gives you full control over the start and end. Minimal example

FROM products
SHOW SUM(total_sales)
GROUP BY product
SINCE -7d

Full example

FROM products
SHOW SUM(total_sales) + SUM(quantity_purchased) * 10 AS weighted_score
GROUP BY product
WHERE geo_country = 'US' AND marketing_source IS NOT NULL
SINCE last_month
UNTIL this_month
TIMESERIES day
SEGMENT BY geo_city
COMPARE TO previous_year
ORDER BY weighted_score DESC
WITH TOTALS, PERCENT_CHANGE
VISUALIZE SUM(total_sales) TYPE line MAX 30

Clauses

FROM

Purpose: Selects the dataset.
Example:
FROM products

SHOW

Purpose: Lists one or more expressions to compute. Typically uses aggregation functions over metrics or fields. In the report editor, the visual query builder lets you select multiple metrics for the same query; each becomes a separate item in the SHOW list.

Examples:

SHOW SUM(total_sales)
SHOW COUNT(quantity_purchased), MAX(total_sales)
SHOW COUNT(*)

GROUP BY

Purpose: Defines the grouping keys for aggregation.
Keys: Can be identifiers, dimensions, product_attributes.* paths, or discovered custom.* context paths. See Custom context dimensions.
TOP N Modifier: Limit results to the top N values for a dimension.

Examples:

GROUP BY product
GROUP BY product_attributes.color, product_attributes.size
GROUP BY TOP 5 product_title
GROUP BY ONLY TOP 10 geo_country
GROUP BY TOP 5 OVERALL marketing_campaign

TOP N modifier

The TOP N modifier ranks the first grouping dimension by the primary metric (the first SHOW expression), highest first. Other grouping and segmentation dimensions define separate ranking groups. ORDER BY changes the final display order, not which values qualify for TOP N. Three variants are available:

TOP N: Shows the top N values plus an “Other” category for the remainder.
GROUP BY TOP 5 product_title
ONLY TOP N: Shows only the top N values, hiding the remainder entirely.
GROUP BY ONLY TOP 5 product_title
TOP N OVERALL: Ranks values across the full time range rather than per-period when used with a supported TIMESERIES query. It does not enable gap filling.
GROUP BY TOP 5 OVERALL product_title

The Other row sums additive metrics, such as sales totals and counts. Rates, averages, and distinct counts remain blank on that row rather than showing a misleading sum. The retained top rows keep their original metric values. The same rules apply to previous-period values when you use COMPARE TO. For example, this query returns the five products with the highest sales, plus Other, with previous-period values:

FROM products
SHOW SUM(total_sales), AVG(total_sales)
GROUP BY TOP 5 product
SINCE -7d
COMPARE TO previous_period

TOP N, ONLY TOP N, and TOP N OVERALL cannot be combined with WITH TOTALS, GROUP_TOTALS, PERCENT_CHANGE, or CUMULATIVE_VALUES. COMPARE TO alone is supported, without PERCENT_CHANGE.TOP N also rejects TIMESERIES gap filling for natural granularities: minute, hour, day, week, month, quarter, and year. Gap filling applies when the query has resolved start and end bounds. The report runner can supply those bounds even when you omit UNTIL, so omitting UNTIL does not guarantee a compatible query. Use TOP N without TIMESERIES for a ranked report, or remove TOP N to show a gap-filled time series. OVERALL does not bypass this restriction.

WHERE (optional)

Purpose: Filters rows before aggregation using boolean expressions over dimensions, fields, or attributes.
Comparison operators: =, !=, <, <=, >, >=
Logical operators: AND, OR, NOT
Null handling: IS NULL, IS NOT NULL
Precedence: Parentheses are supported for grouping conditions.

Examples:

WHERE geo_country = 'US'
WHERE geo_country = 'US' AND marketing_source = 'facebook'
WHERE (geo_country = 'US' OR geo_country = 'CA') AND marketing_source IS NOT NULL
WHERE NOT marketing_campaign = 'holiday_sale'
WHERE product_attributes.color = 'red'

HAVING (optional)

Purpose: Filters grouped results after aggregation using boolean expressions over metrics. Use it to hide low-volume rows. For example, only show products with at least 500 impressions when ranking by click-through rate.
Requires: A GROUP BY or TIMESERIES clause. HAVING is invalid on ungrouped queries.
References: Only known metrics from the dataset. Referencing an unknown metric is a validation error.
Comparison operators: =, !=, <, <=, >, >=
Logical operators: AND, OR, NOT (parentheses supported)

Examples:

FROM products
SHOW product_ctr
GROUP BY product
HAVING product_impressions >= 500
SINCE -30d

FROM products
SHOW hover_rate
GROUP BY product
HAVING product_impressions >= 1000 AND product_hovers >= 50
SINCE -30d

Metrics referenced only in HAVING do not appear in the result. They are computed as hidden companions so you can filter on volume without adding it as an output column.

SINCE

Purpose: Defines the start of the time window for the query.
Accepted forms:
- Relative: -7d, -14d, -30d, -60d, -90d, -180d, -365d, -24h, -48h, -12m, -1y
- Keywords: today, yesterday, this_week, last_week, this_month, last_month, this_quarter, last_quarter, this_year, last_year
- Rolling windows: past_7_days, past_14_days, past_30_days, past_60_days, past_90_days, past_180_days, past_365_days
- Custom rolling window: past_N_days where N is any positive integer (for example, past_45_days, past_120_days)
- Year-to-date: year_to_date
- Black Friday / Cyber Monday by year: bfcm2024, bfcm2025 (the four days from Black Friday through Cyber Monday)
- ISO: 2025-03-15 or 2025-03-15T10:30:00Z (timezone offsets supported, ISO 8601)

Examples:

SINCE -7d
SINCE this_month
SINCE past_45_days
SINCE year_to_date
SINCE bfcm2024
SINCE 2025-01-01
SINCE 2025-01-01T00:00:00Z

UNTIL (optional)

Purpose: Defines the end of the time window for the query. When omitted, the query runs up to the current time.
Accepted forms: Same as SINCE (relative offsets, keywords, rolling windows, ISO timestamps).

Examples:

SINCE -30d UNTIL -7d
SINCE last_month UNTIL this_month
SINCE 2024-01-01 UNTIL 2024-12-31

The UNTIL point is exclusive: the query counts activity up to, but not including, that moment. For example, SINCE 2024-01-01 UNTIL 2024-12-31 covers January 1 through December 30. To include all of December 31, use UNTIL 2025-01-01.

DURING

Purpose: A single-token shortcut for common time ranges. Use DURING instead of SINCE/UNTIL when one of the named ranges fits your query.
Accepted forms:
- Calendar keywords: today, yesterday, this_week, last_week, this_month, last_month, this_quarter, last_quarter, this_year, last_year
- Rolling windows: past_7_days, past_30_days, past_90_days, past_N_days (any positive integer N)
- Year-to-date: year_to_date
- Black Friday / Cyber Monday by year: bfcm2024, bfcm2025
Mutually exclusive: A query uses either SINCE (with optional UNTIL) or DURING, never both.

Examples:

FROM products
SHOW SUM(total_sales)
GROUP BY product
DURING last_month

FROM products
SHOW SUM(total_sales)
GROUP BY product
DURING bfcm2024

TIMESERIES (optional)

Purpose: Groups results by time intervals with automatic backfilling for missing periods.
Granularities:
- Standard: day, week, month, quarter, year, hour, minute
- Cyclical: day_of_week, hour_of_day, week_of_year, month_of_year

Examples:

TIMESERIES day
TIMESERIES week
TIMESERIES month
TIMESERIES hour_of_day

When using TIMESERIES, the query results include a time dimension that shows values for each period within the SINCE range. Missing periods are automatically backfilled with zero values, so TIMESERIES output works well for charting and trend analysis. Natural-granularity gap filling is incompatible with TOP N. Remove TOP N when you need a gap-filled time series.

SEGMENT BY (optional)

Purpose: Adds a segmentation dimension applied to each group.
Accepts: built-in dimensions and discovered custom context dimensions (for example, custom.storefront).

Examples:

SEGMENT BY geo_country
SEGMENT BY marketing_campaign, geo_country
SEGMENT BY custom.storefront

COMPARE TO (optional)

Purpose: Repeats the query over a shifted time window and returns both result sets so you can compare two periods (for example, this month vs. last month). The shifted window has the same length as the primary window.
Syntax: COMPARE TO <target>
Targets:
- previous_period — shifts back by the exact length of the primary window (a 7-day window compares against the prior 7 days).
- previous_month — shifts back by one calendar month.
- previous_year — shifts back by one calendar year.
- previous_year_match_day_of_week — shifts back 364 days so the same days of the week align (useful for retail where day-of-week effects matter).
Combine with WITH PERCENT_CHANGE to surface the percentage delta between the two periods.

Examples:

FROM products
SHOW SUM(total_sales)
GROUP BY product
SINCE this_month
COMPARE TO previous_month

FROM products
SHOW SUM(total_sales)
DURING bfcm2024
COMPARE TO previous_year_match_day_of_week
WITH PERCENT_CHANGE

ORDER BY (optional)

Purpose: Sorts the result rows. Without it, results are sorted by the first SHOW expression in descending order.
Syntax: ORDER BY <target> [ASC | DESC] [, <target> [ASC | DESC]]*
Direction: DESC is the default when omitted. Rows with a NULL value in the sort column always sort last.
Targets: A column visible in the result: an AS alias, a metric or expression from the SHOW list, or a dimension used in GROUP BY or SEGMENT BY. Derived columns added by other clauses are also valid: *_previous when COMPARE TO is present, *_pct_change when WITH PERCENT_CHANGE is present, *_cumulative when WITH CUMULATIVE_VALUES and TIMESERIES are present, and *_previous_cumulative when all three are present.
Restrictions: Unlike HAVING, ORDER BY cannot reference metrics that are not in the SHOW list, because ordering by a hidden column has no meaning. Handle dimensions (dimensions that resolve a handle to an internal ID, such as sort_order) cannot be sort targets. Unknown or ambiguous targets are validation errors that list the available columns.

Examples:

FROM search_text
SHOW cvr, COUNT_DISTINCT(requests) AS searches
GROUP BY term
HAVING requests >= 20
SINCE -30d
ORDER BY cvr DESC

FROM products
SHOW SUM(total_sales) AS revenue
GROUP BY geo_country
SINCE -30d
ORDER BY revenue DESC, geo_country ASC

When ordering by a rate (for example, ctr or cvr), add a HAVING volume floor on the underlying count so small denominators don’t dominate the top of the table.

WITH (optional)

Purpose: Adds calculated rollups and derived series to the result without changing the base aggregation. Combine modifiers with commas in any order; each modifier may appear at most once.
Syntax: WITH <modifier> [, <modifier>]*
Modifiers:
- TOTALS — adds a grand total row across every group.
- GROUP_TOTALS — adds subtotal rows when a query has both GROUP BY and SEGMENT BY (or TIMESERIES). One subtotal is emitted per primary group.
- PERCENT_CHANGE — adds a percent-change column. Requires COMPARE TO.
- CUMULATIVE_VALUES — adds a running total series. Only valid for additive expressions (SUM, COUNT, and add/subtract combinations of them). Non-additive expressions such as AVG, MIN, MAX, COUNT_DISTINCT, and ratios are rejected.

Examples:

FROM products
SHOW SUM(total_sales)
GROUP BY product
SINCE -30d
WITH TOTALS

FROM products
SHOW SUM(total_sales)
GROUP BY geo_country
SEGMENT BY marketing_source
SINCE -30d
WITH TOTALS, GROUP_TOTALS

FROM products
SHOW SUM(total_sales)
SINCE this_month
TIMESERIES day
WITH CUMULATIVE_VALUES

VISUALIZE (optional)

Purpose: Specifies how to render the query results graphically.
Syntax: VISUALIZE <target> [TYPE <type>] [BY <field>] [SERIES <field>] [BUCKETS <n>, <n>, ...] [LIMIT <n>] [MAX <number>]
Target: The metric or expression to visualize (e.g., SUM(total_sales) or a field name).
Options: Each option may appear at most once and they can be written in any order.
- TYPE <type> — chart type. One of table, bar, line, donut, horizontal_bar, stacked_bar, area, card, geo_bar. Use geo_bar for ranked geographic distributions; country codes in geo_country are rendered as full country names, and bars are sorted in descending order of the metric.
- BY <field> — primary axis or grouping field for the chart (for example, BY position).
- SERIES <field> — splits the data into separate series by the given field (for example, SERIES geo_country).
- BUCKETS <n>, <n>, ... — numeric bucket boundaries for histogram-style charts.
- LIMIT <n> — caps the number of categories or series rendered.
- MAX <number> — caps the number of data points displayed.

Examples:

VISUALIZE SUM(total_sales)
VISUALIZE SUM(total_sales) TYPE bar
VISUALIZE SUM(total_sales) TYPE line MAX 30
VISUALIZE COUNT_DISTINCT(view_sessions) TYPE donut MAX 10
VISUALIZE SUM(total_sales) TYPE bar BY product_title SERIES geo_country LIMIT 20
VISUALIZE AVG(price) TYPE bar BUCKETS 10, 25, 50, 100
VISUALIZE searches TYPE geo_bar BY geo_country LIMIT 12

Combine VISUALIZE with TIMESERIES to create time-series charts, or with GROUP BY to create bar and donut charts that show distribution across dimensions. Identifiers and paths

Identifiers: letters, digits, underscores; start with a letter or underscore.
Paths: dot notation (for example, product_attributes.color).

Literals

Strings: ‘value’ or “value”
Numbers: 10, 10.5
Booleans: TRUE, FALSE

Arithmetic expressions

LayersQL supports arithmetic operations in both SHOW and WHERE clauses, so you can create calculated metrics and multi-part filtering conditions.

Supported operators

Addition: +
Subtraction: -
Multiplication: *
Division: /
Modulo: %
Unary minus: - (negation)

Operator precedence

Arithmetic expressions follow standard mathematical precedence: multiplication, division, and modulo are evaluated before addition and subtraction. Use parentheses to override the default precedence.

Examples

Blend sales and units into a weighted score for a sort order:

FROM products
SHOW SUM(total_sales) * 0.7 + SUM(quantity_purchased) * 0.3 AS weighted_score
GROUP BY product
SINCE -7d

Divide one aggregation by another to get the average selling price per unit:

FROM products
SHOW SUM(total_sales) / SUM(quantity_purchased) AS avg_unit_price
GROUP BY product
SINCE -30d

Use parentheses and a number to turn a ratio into a percentage:

FROM products
SHOW (COUNT_DISTINCT(purchase_sessions) / COUNT_DISTINCT(view_sessions)) * 100 AS view_to_purchase_pct
GROUP BY product
SINCE -30d

When a divisor is zero (for example, a product with views but no units sold), the result is empty rather than an error.

Aliasing arithmetic expressions

Use the AS keyword to give your calculated expressions meaningful names. You can compute several named expressions in one query:

FROM products
SHOW SUM(total_sales) / SUM(quantity_purchased) AS avg_unit_price,
     SUM(total_sales) / COUNT_DISTINCT(view_sessions) AS sales_per_view
GROUP BY product
SINCE -7d

Examples

Group by attributes with a rolling window

FROM products
SHOW SUM(total_sales)
GROUP BY product, product_attributes.metafields.custom.style_code
SINCE past_30_days

Filter by geography and marketing channel

FROM products
SHOW SUM(total_sales)
GROUP BY product
WHERE geo_country = 'US' AND marketing_source = 'facebook'
SINCE -7d

Calculate weighted metrics with segmentation

FROM products
SHOW SUM(total_sales) * 0.7 + COUNT_DISTINCT(view_sessions) * 0.3 AS weighted_score
GROUP BY product
SINCE -30d
SEGMENT BY geo_country

Overview

Core Concepts

Merchandising

Search & Discovery

Catalog & Data

LayersQL

Shopify Integration

Legal

LayersQL syntax reference

Clauses

FROM

SHOW

GROUP BY

TOP N modifier

WHERE (optional)

HAVING (optional)

SINCE

UNTIL (optional)

DURING

TIMESERIES (optional)

SEGMENT BY (optional)

COMPARE TO (optional)

ORDER BY (optional)

WITH (optional)

VISUALIZE (optional)

Arithmetic expressions

Supported operators

Operator precedence

Examples

Aliasing arithmetic expressions

Examples

​Clauses

​FROM

​SHOW

​GROUP BY

​TOP N modifier

​WHERE (optional)

​HAVING (optional)

​SINCE

​UNTIL (optional)

​DURING

​TIMESERIES (optional)

​SEGMENT BY (optional)

​COMPARE TO (optional)

​ORDER BY (optional)

​WITH (optional)

​VISUALIZE (optional)

​Arithmetic expressions

​Supported operators

​Operator precedence

​Examples

​Aliasing arithmetic expressions

​Examples

Clauses

FROM

SHOW

GROUP BY

TOP N modifier

WHERE (optional)

HAVING (optional)

SINCE

UNTIL (optional)

DURING

TIMESERIES (optional)

SEGMENT BY (optional)

COMPARE TO (optional)

ORDER BY (optional)

WITH (optional)

VISUALIZE (optional)

Arithmetic expressions

Supported operators

Operator precedence

Examples

Aliasing arithmetic expressions

Examples