Query language
The PQL query format, its text form, time ranges, filters and results.
A PQL query is a JSON object. Only dataset and measures are required.
{
"dataset": "orders", // orders | products | customers | carts | web | blend
"measures": ["revenue", "order_count"], // 1–12 measures
"dimensions": ["time.day", "province"], // 0–4 dimensions (default: none)
"filters": [ // 0–20 filters, all must match (AND)
{ "field": "status", "op": "in", "value": ["SHIPPED", "DELIVERED"] }
],
"time": {
"range": "last_30_days", // preset or { "from": "2026-09-01", "to": "2026-09-30" }
"compare": "previous_period" // optional: previous_period | previous_year
},
"sort": [{ "field": "revenue", "dir": "desc" }],
"limit": 100 // 1–1000, default 100
}Names are lower_snake_case. Time dimensions use a time. prefix, and blend measures can reference another dataset as <dataset>.<measure>.
Text form
Every query can also be written on one line. The clauses must appear in this order, and keywords are case-insensitive:
<dataset>: <measures> [by <dimensions>] [where <filter> and <filter> …]
[during <range>] [compare <mode>] [sort <field> [asc|desc], …] [limit <n>]orders: revenue, aov by time.day where status in (SHIPPED, DELIVERED) during last_30_days compare previous_period
web: visitors by referrer where path starts_with /products during 2026-09-01..2026-09-30 sort visitors desc limit 10Quote values that contain spaces or punctuation (province = "Western Cape"). Bare words, numbers, true/false and paths starting with / don't need quotes. The text form is converted to the JSON form, so both behave the same.
Time ranges
All ranges and time dimensions use the store's timezone (Africa/Johannesburg). A range includes both its start and end.
| Preset | Meaning |
|---|---|
today, yesterday | That calendar day |
last_7_days, last_30_days, last_90_days, last_365_days | Today plus the previous N−1 days |
this_week, last_week | ISO weeks (Monday–Sunday) |
this_month, last_month | Calendar months |
this_year / year_to_date, last_year | Calendar years |
all_time | Since 2015 (web data is capped at two years, with a warning) |
Custom ranges use { "from": "YYYY-MM-DD", "to": "YYYY-MM-DD" } (whole days in store time) or full ISO timestamps. In the text form, write during 2026-01-01..2026-03-31.
Comparison. previous_period compares against the same-length period immediately before. previous_year shifts the range back one year. The result then includes comparison.totals and, when there are dimensions, comparison.rows.
Time dimensions
Every dataset (except blend, which lists its own) has these:
| Dimension | Example value |
|---|---|
time.hour | 2026-09-30T14:00 |
time.day | 2026-09-30 |
time.week | 2026-09-28 (the Monday) |
time.month | 2026-09 |
time.year | 2026 |
time.day_of_week | 1 (Monday) … 7 (Sunday) |
time.hour_of_day | 0 … 23 |
Filters
Filters apply to dimensions and are combined with AND.
op | Text form | Value |
|---|---|---|
eq, neq | =, != | single value (neq keeps nulls) |
gt, gte, lt, lte | >, >=, <, <= | single value |
in, not_in | in (a, b), not in (a, b) | array (not_in keeps nulls) |
contains, starts_with | contains x, starts_with x | text, case-insensitive |
is_null, not_null | is null, is not null | none |
Dimensions with a fixed set of values, such as status, reject unknown values and match case-insensitively (delivered → DELIVERED).
Sorting and limits
sort can reference any selected measure or dimension. Without sort, rows are ordered by the first time dimension (ascending) or else by the first measure (descending). If more rows match than limit, the result is cut off and meta.truncated is true.
Results
{
"query": { /* the normalized query, with defaults filled in */ },
"columns": [
{ "name": "time.day", "kind": "dimension", "type": "date" },
{ "name": "revenue", "kind": "measure", "type": "money", "unit": "cents" }
],
"rows": [["2026-09-01", 1234500], ["2026-09-02", 98000]],
"totals": { "revenue": 1332500 }, // whole range, ignoring dimensions
"range": { "from": "…", "to": "…", "timezone": "Africa/Johannesburg" },
"comparison": { "mode": "previous_period", "range": { … }, "totals": { … }, "rows": [ … ] },
"meta": { "sources": ["puddle"], "durationMs": 42, "cached": false, "truncated": false, "warnings": [] }
}Column types tell the UI and LLMs how to format each value:
| Type | Format |
|---|---|
money | Integer cents in the store currency (ZAR). |
percent | Already multiplied by 100, rounded to 2 decimals. |
duration | Seconds. |
integer, number | Plain numbers. |
date, string, boolean | Dimension values. |
Errors
Errors are written so a person or an LLM can fix the query:
- Invalid query: unknown names (with a "did you mean" suggestion and the list of valid names), wrong value types, unknown enum values, or bad syntax with its position.
- Unavailable: the data source isn't configured or linked for the store, for example website data when website analytics aren't connected.
- Timeout: the query ran longer than 10 seconds. Shorten the range or add filters.
MCP tools return these as tool errors.