LayersQL syntax reference

• 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]

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

FROM products
SHOW SUM(total_sales) + SUM(quantity_purchased) * 10 AS weighted_score
GROUP BY TOP 10 product_id
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 expressions to compute. Typically uses aggregation functions over metrics or fields.
• 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_id
  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

• 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. Useful with TIMESERIES to maintain consistent rankings.
  GROUP BY TOP 5 OVERALL product_title
  

​

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
  

​

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
  

​

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_id
  DURING last_month
  
  FROM products
  SHOW SUM(total_sales)
  GROUP BY product_id
  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
  

​

SEGMENT BY (optional)

• Purpose: Adds an additional 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_id
  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 — 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
  

​

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_id
  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
  

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

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

​

Arithmetic expressions

​

Supported operators

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

​

Operator precedence

​

Examples

FROM products
SHOW SUM(total_sales) + SUM(quantity_purchased) AS combined_metric
GROUP BY product_id
SINCE -7d

FROM products
SHOW SUM(total_sales) * 100 AS sales_cents
GROUP BY product_id
SINCE -7d

FROM products
SHOW (SUM(total_sales) + SUM(quantity_purchased)) * 2 AS doubled_total
GROUP BY product_id
SINCE -7d

FROM products
SHOW (SUM(total_sales) - SUM(quantity_purchased)) / SUM(view_sessions) AS profit_per_view
GROUP BY product_id
SINCE -7d

FROM products
SHOW SUM(total_sales)
GROUP BY product_id
WHERE product_id * 2 > 100
SINCE -7d

​

Aliasing arithmetic expressions

FROM products
SHOW SUM(total_sales) + SUM(quantity_purchased) AS total_revenue,
     SUM(total_sales) - SUM(quantity_purchased) AS profit
GROUP BY product_id
SINCE -7d

​

Examples

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

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

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