Querying store data

Querying store data

query-language

use before composing a query that groups, compares, spans more than one measure family, or reports on variants or stock

The customer-facing MCP has its own copy of this guidance, for a person's live session rather than
an unattended run. Both are paired in `docs/superpowers/specs/2026-09-10-query-language-design.md`
section 10.4; if you change a fact here, change it there.

## What a query is

One JSON body naming measures and dimensions. The system decides which of nine Elasticsearch indices
answer, queries them in parallel, and merges the results into one table. You never name an index and
never write a join.

```jsonc
{
  "select": ["revenue", "orders", "aov"],
  "by": ["country"],
  "over": "day",
  "where": [{ "field": "device", "op": "eq", "value": "mobile" }],
  "since": "-30d", "until": "today",
  "orderBy": { "key": "revenue", "dir": "desc" },
  "limit": 20
}
```

`describe_query` tells you what THIS store can answer. The tool schema already lists every name that
exists, so you do not need it to write a valid query. You need it for two things the schema cannot
say: which measures this store's connected apps actually support, and what a measure means. Glood's own datasets need no app and are always listed.

## Six things worth knowing before you report a number

### 1. There are two order counts, and picking the wrong one is the easiest mistake

**`orders`, `revenue`, `aov`** come from `checkout_completed` on the storefront pixel. That is what
makes them groupable by everything else a shopper did: device, country, search term, the
recommendation they clicked. Nothing else can answer those questions.

**`recordedOrders`, `recordedRevenue`** are Shopify's own order rows. They are the only thing
breakable down by a field that lives on the order rather than on the event: `province`,
`financialStatus`, `discountCode`, `gateway`. `unitsSold`, `lineRevenue`, `itemsSold`, `discounts`,
`shipping` and `tax` are the same rows in more detail.

They do not agree and are not meant to. The records include online channels the pixel never ran on;
the pixel is the only thing that knows the device. **Say which one a figure came from.** "Revenue was
4.2L" is not an answer; "revenue was 4.2L (pixel-observed, 1 to 30 Sep)" is.

`note.onlineStoreOnly` marks the pixel figures, which makes them a floor rather than the whole. Pass
it on.

If you ask for a pixel measure grouped by an order-record field, the tool refuses and lists what IS
legal for it. Switch to `recordedOrders` rather than trying variations.

### 2. Read the notes and pass them on

Every result carries `notes`. They change what the numbers mean, and the merchant reading your answer
has no footer to check.

- `note.capped` means there is a tail you did not see. Never present a capped list as "the" list.
- `note.onlineStoreOnly` means the figure is pixel-observed, so Glood's reach is understated in a
  known direction.
- `note.catalogueAsOfSync` means stock and price are as of the last sync, not as of your window.
- `note.currencyConverted` means money was converted at today's rate to compare stores.

### 3. `covered: false` is not "nothing happened"

If `covered` is false, the data does not reach that window at all. There are no rows because nothing
is held, not because nothing occurred. Never file a finding on it.

If `clamped` is present, the window was shortened. Report the window in `window`, never the one you
asked for.

### 4. Variant CTR does not exist

You can ask for units, revenue, views, add-to-carts and stock at the variant grain. You cannot ask
for impressions, clicks or CTR there, and the tool will refuse.

The reason is not a missing field. A recommendation card is a product, so there is no variant
impression to divide by: one app writes the product's FIRST variant into the impression and the other
writes null. Grouping by it would credit every impression to variant one and produce a table that
looks entirely correct.

If you need variant-level engagement, say it is not measurable rather than reaching for the nearest
number.

### 5. Some combinations have no answer

A measure can only be grouped by a dimension its own data carries. `unitsSold` comes from the order
index and `searchTerm` exists only in the events index, so no document holds both and the pair is
refused. The refusal lists the legal dimensions for that measure. Take one of them rather than
trying variations.

This holds WITHIN the events index too, which is the case that surprises people. A Glood surface
event carries the track envelope and nothing else, so `impressions`, `clicks`, `ctr` and
`gloodAddToCarts` have no product title on the document. They group by `productId`; ask for
`productTitle` and you are refused.

Select the title and group by the id - the name is resolved for you:

```jsonc
{ "select": ["productTitle", "impressions", "clicks", "ctr"], "by": ["productId"],
  "since": "-30d", "until": "today", "orderBy": { "key": "clicks", "dir": "desc" }, "limit": 10 }
```

The refusal matters here: before it existed, that query returned real product names with every
count zero, because the buckets came from product views and none of them was a click.

### 6. Two measures refuse, and point at `run_report`

`influencedOrders` and `influencedRevenue` cannot be composed. "Was this shopper influenced by
Glood" spans two documents, an engagement and a checkout, and no aggregation can express that. Use
`run_report`. `describe_query` marks them `unavailable`.

Everything else composes, including the order count: Shopify redelivers `checkout_completed`, and
counting DISTINCT checkout tokens is what makes that harmless.

## Glood's own data

Four datasets are Glood's own, not a connected app's: `storefront` (Glood's pixel on the storefront), `orderHistory` (Glood's own order records, measures prefixed `history`), `probe` (Glood's browser visits, speed percentiles, uptime, page errors, checkout journey, certificate) and `adminEvents` (Shopify's admin activity feed, `adminChanges`). Percentile measures (`probe*P75`) are the 75th percentile, not an average.

- **No app needed, store scoped for you.** The prefix marks them: `storefront`, `history`, `probe`, `admin`. A partner measure in the same query is answered as unavailable with its reason; the Glood-owned ones still answer.
- **Hour windows.** `since` and `until` also take `-Nh` (the last N complete hours) and `YYYY-MM-DDTHH:00` (shop-local wall clock, complete hours only), and `over: hour` buckets by the hour. Hour windows work on Glood-owned datasets only and are refused on partner datasets.
- **Bots.** `storefrontBotEvents` and `storefrontBotShare` need `includeBots: true`; every other `storefront*` measure leaves bots out.
- **Each history measure has its own date.** `historyFulfilledOrders` and `historyFulfilmentHours` are dated by fulfilment, `historyDeliveryHours` by delivery, `historyReturns` by return, `historyRefunds` and `historyRefundAmount` by refund. Everything else in `orderHistory` is dated by order creation. A month with no refunds shows a dash (null), not 0. Mixing dates in one query is fine: it runs one search per date and merges them.
- **Cancelled orders.** `historyOrders` leaves cancelled orders out (`historyCancelledOrders` counts them); the fulfilled, returns and refunds measures do not.
- **Ratios are fractions.** `0.5` means 50%. Say "50%" to the reader.
- **A null key row is a real row.** Groupings on Glood-owned datasets include one row for documents without the field. In `adminChanges` by `adminAuthor`, no author means a system event, not an error.
- **`*Total` measures are components.** `probe*Total` and `historyDiscountDepthTotal` exist so the averages and ratios can be computed; quote the average or the ratio, never the total.
- **Probe error groupings are newer than the counts.** `exceptionMessage`, `consoleError` and `failedRequestUrl` only cover visits recorded since the probe started storing them (probe version 2). Counts, percentiles and uptime cover every visit, so the groups can sum to less than `probeExceptions`.
- **Not available.** `fulfillmentLocation` (the app lacks the Shopify scope to read locations). The text of a theme diff stays in `get_changelog`; full error stacks are not queryable, message, page and count are.

Who changed this product, and when:

```jsonc
{ "select": ["adminChanges"], "by": ["adminAuthor", "adminAction"],
  "where": [{ "field": "adminSubjectId", "op": "eq", "value": "<product id>" }],
  "over": "hour", "since": "-48h", "until": "today" }
```

Which error, on which page:

```jsonc
{ "select": ["probeVisits"], "by": ["exceptionMessage"], "since": "-7d", "until": "today",
  "orderBy": { "key": "probeVisits", "dir": "desc" }, "limit": 5 }
```

then the same query with `"by": ["pageType"]` and a `where` of `exceptionMessage eq <the message>`.

## Comparing windows

Ask for the same query twice, over two windows, and compare the results. Do not subtract two figures
in your head and report the difference as a trend: that is where arithmetic errors come from, and a
computed delta is checkable while a remembered one is not.

## Granular reads

Select only dimensions and no measure, and you get documents instead of an aggregation. This is how
you answer "what actually happened to this shopper".

```jsonc
{
  "select": ["eventName", "path", "productTitle"],
  "where": [{ "field": "sessionId", "op": "eq", "value": "..." }],
  "since": "-7d", "until": "today", "limit": 100
}
```

Identity fields are filterable and never returned. You can narrow to a session, a client or a
customer when you already hold the id; a query cannot list them.

## What you cannot express, by design

- **Another store.** Your deployment is one store. There is no field that names one.
- **A join.** The system joins for you, on whatever grain you grouped by.
- **A shopper's identity as a column.** Filter by it, never select it.

## Worked examples

Which variants are selling while stock runs low:

```jsonc
{ "select": ["unitsSold", "stockOnHand"], "by": ["variantId"],
  "since": "-30d", "until": "today",
  "orderBy": { "key": "unitsSold", "dir": "desc" }, "limit": 20 }
```

Where mobile revenue is concentrated, weekly:

```jsonc
{ "select": ["revenue", "orders"], "by": ["country"], "over": "week",
  "where": [{ "field": "device", "op": "eq", "value": "mobile" }],
  "since": "-90d", "until": "today" }
```

Where the money went, from the order records:

```jsonc
{ "select": ["recordedOrders", "recordedRevenue", "discounts", "shipping", "tax"],
  "by": ["province"], "since": "-30d", "until": "today" }
```

Lifetime value of the customers who bought in a window:

```jsonc
{ "select": ["customers", "lifetimeOrders", "lifetimeSpend"], "by": ["province"],
  "since": "-90d", "until": "today" }
```

Note `note.lifetimeFigure` on that one: those are running totals on the active population, so they
do not reconcile with the window's own order figures.

Conversion by device, which is one population divided by itself:

```jsonc
{ "select": ["sessions", "orders", "conversionRate"], "by": ["device"],
  "since": "-30d", "until": "today" }
```

Which recommendation types earn their placement:

```jsonc
{ "select": ["impressions", "clicks", "ctr"], "by": ["recommendationType"],
  "since": "-14d", "until": "today",
  "having": [{ "field": "impressions", "op": "gt", "value": 100 }] }
```

Note the `having`: a CTR over eleven impressions is noise, and filtering it out is cheaper than
explaining it.