Querying with SoloQL

Q: Can one query return data from all my stores at once?

Yes — that is the point. Every dataset has a store dimension. Omit it for a consolidated total, add GROUP BY store for a per-store breakout, or use WHERE store IN (...) to filter to specific shops by name.

Q: How do I set the date range?

Use SINCE with a relative value like -30d, -12w, -6M, or -1y, or absolute quoted dates like SINCE '2026-01-01' UNTIL '2026-02-01', or a named period with DURING. The response window echoes the exact UTC range that was measured.

Q: Which time zone are the days measured in?

The account's zone by default. Pass a timezone in the request body (an IANA name such as Europe/Athens) to override it; the response timezone confirms which zone was used.

Q: How many rows can a query return?

SoloQL returns grouped totals, not raw records. A GROUP BY is capped to the top 1000 groups by volume (raise it up to 5000 with LIMIT). If a query hits the cap, the response sets truncated and group_limit so you always know. For every group with no cap, run an

Q: Can I get CSV instead of JSON?

Yes. Send Accept: text/csv and the same query returns CSV, with the column titles as the header row.

Q: What happens if my query has a mistake?

You get a 400 with an errors array of positioned diagnostics — each with line, column, message, and severity — so you can point the developer straight at the problem.

SoloQL is a compact query language built for commerce reporting. You post a SoloQL string to POST /v1/query and get back a clean, currency-normalised table across every connected store.

Clause order

A query is a FROM and a SHOW clause, plus optional clauses in this order:

FROM <dataset>
SHOW <field>[, <field> ...]
[WHERE <condition>]
[SINCE <date> [UNTIL <date>]] | [DURING <period>]
[GROUP BY <dimension>]
[TIMESERIES <interval>]
[COMPARE TO PREVIOUS]
[ORDER BY <field> [DESC]]
[LIMIT <n>]
[WITH TOTALS]
[WITH CURRENCY 'USD']

Only FROM and SHOW are required. Results are grouped totals, not raw records — see Result limits for how many groups a query returns.

Multi-store consolidation

This is the reason the API exists. Every dataset has a store dimension, so a single query spans all the stores your key can access:

One consolidated total — omit store and metrics are summed across every store.
Per-store breakout — add GROUP BY store to get one row per shop.
Filter to specific shops — use WHERE store IN ('My Shop Name') to narrow by shop name.

Money metrics are automatically converted to the account's currency — or to the currency you name with WITH CURRENCY 'USD' — so totals across stores that sell in different currencies are apples-to-apples.

FROM sales SHOW total_sales, orders GROUP BY store SINCE -30d

Dates

Restrict a query to a date window with SINCE (and optionally UNTIL), or name a period with DURING.

Relative — SINCE -30d (days), -12w (weeks), -6M (months), -1y (years).
Absolute — SINCE '2026-01-01' UNTIL '2026-02-01' (quote the dates).
Named periods — DURING accepts named periods such as a month or quarter.

Every response echoes back the resolved window as window with gte and lt in UTC ISO form, so you always know exactly what range was measured. Pass a time_zone in the request body (an IANA name such as Europe/Athens) to control how day boundaries are drawn; it defaults to the account's zone.

Filtering with WHERE

WHERE filters on dimensions using =, IN (...), and comparisons:

FROM orders SHOW order_count, total_price
WHERE financial_status = 'paid'
SINCE -30d

You can combine a store filter with any other condition, for example WHERE store IN ('EU Shop', 'US Shop') AND financial_status = 'paid'.

The request body

POST /v1/query takes a JSON body:

{
  "query": "FROM sales SHOW total_sales, orders GROUP BY store SINCE -30d",
  "store_ids": ["str_a1b2", "str_c3d4"],
  "time_zone": "Europe/Athens"
}

query (required) — the SoloQL string.
store_ids (optional) — narrows the query to these store IDs. It can only narrow within the key's scope; it can never widen beyond it.
time_zone (optional) — an IANA zone; defaults to the account's zone.

The response

A successful query returns:

{
  "columns": [
    { "name": "store", "title": "Store", "type": "string", "role": "dimension" },
    { "name": "total_sales", "title": "Total sales", "type": "money", "role": "metric" },
    { "name": "orders", "title": "Orders", "type": "number", "role": "metric" }
  ],
  "rows": [
    { "store": "EU Shop", "total_sales": 48210.55, "orders": 612 },
    { "store": "US Shop", "total_sales": 71934.10, "orders": 845 }
  ],
  "currency": "EUR",
  "time_zone": "Europe/Athens",
  "window": { "gte": "2026-06-24T00:00:00Z", "lt": "2026-07-24T00:00:00Z" }
}

columns — one entry per column, each with name, title, type, and role. Rows are keyed by column name.
rows — the result rows.
totals — present only when you add WITH TOTALS.
currency — the ISO currency the money columns were converted to.
time_zone — the IANA zone the window was resolved in.
window — the resolved UTC date range (gte, lt).
truncated — present and true only when a high-cardinality GROUP BY was capped to the top groups (see Result limits); omitted otherwise.
group_limit — the effective group cap that was applied, present only when truncated.

Result limits

The API is built for analytics — it returns grouped totals, not raw records. When you GROUP BY a high-cardinality dimension (say, every product across a year of days), the result is capped to the top groups by volume:

1000 groups by default.
Raise the cap up to 5000 with a LIMIT clause (for example LIMIT 5000).

When a query hits that cap the response tells you — it sets truncated to true and group_limit to the cap that was applied:

{
  "columns": [ "..." ],
  "rows": [ "..." ],
  "truncated": true,
  "group_limit": 1000
}

The dropped groups are always the lowest-volume ones, so a top-N view stays intact. To capture more, narrow the query (add WHERE filters or a shorter date range) or raise LIMIT (up to 5000).

For the complete grouped dataset, run an export instead of a live query. An export of a query that exceeds the cap is built in full in the background — every group, no limit — and delivered when it's ready, because enumerating a very large grouping is too slow to return in a single request. A query never silently drops data: if truncated is absent, the result is complete.

Worked examples

Consolidated sales and shipping per store

Sales, shipping, and order counts for every store over the last 30 days:

cURL
JavaScript
Python

curl https://api.ecomsolo.com/v1/query \
  -H "Authorization: Bearer esk_live_..." \
  -H "Content-Type: application/json" \
  -d '{"query": "FROM sales SHOW total_sales, shipping, orders GROUP BY store SINCE -30d"}'

const res = await fetch("https://api.ecomsolo.com/v1/query", {
  method: "POST",
  headers: {
    Authorization: "Bearer esk_live_...",
    "Content-Type": "application/json",
  },
  body: JSON.stringify({
    query: "FROM sales SHOW total_sales, shipping, orders GROUP BY store SINCE -30d",
  }),
});
console.log(await res.json());

import requests

res = requests.post(
    "https://api.ecomsolo.com/v1/query",
    headers={"Authorization": "Bearer esk_live_..."},
    json={"query": "FROM sales SHOW total_sales, shipping, orders GROUP BY store SINCE -30d"},
)
print(res.json())

Top 10 products by net sales

FROM sales SHOW net_sales, net_quantity GROUP BY product_title
ORDER BY net_sales DESC LIMIT 10 SINCE -90d

Paid orders summary

FROM orders SHOW order_count, total_price, average_order_value
WHERE financial_status = 'paid' SINCE -30d

Monthly revenue trend

FROM sales SHOW total_sales TIMESERIES month SINCE -12M

Consolidated total in a chosen currency

Sum sales across every store, converted to US dollars, with a grand total:

FROM sales SHOW total_sales, orders SINCE -30d WITH CURRENCY 'USD' WITH TOTALS

Sales for specific stores, this quarter

FROM sales SHOW total_sales, net_sales GROUP BY store
WHERE store IN ('EU Shop', 'US Shop') DURING this_quarter

Getting CSV instead of JSON

Send an Accept: text/csv header and the same query returns the tabular result as CSV — the header row is the column titles. This is handy for spreadsheets and warehouse loads.

cURL
Python

curl https://api.ecomsolo.com/v1/query \
  -H "Authorization: Bearer esk_live_..." \
  -H "Content-Type: application/json" \
  -H "Accept: text/csv" \
  -d '{"query": "FROM sales SHOW total_sales, shipping, orders GROUP BY store SINCE -30d"}'

import requests

res = requests.post(
    "https://api.ecomsolo.com/v1/query",
    headers={
        "Authorization": "Bearer esk_live_...",
        "Accept": "text/csv",
    },
    json={"query": "FROM sales SHOW total_sales, shipping, orders GROUP BY store SINCE -30d"},
)
print(res.text)

See Recipes for full CSV-to-spreadsheet and warehouse examples.

FAQ

Can one query return data from all my stores at once? Yes — that is the point. Every dataset has a store dimension. Omit it for a consolidated total, add GROUP BY store for a per-store breakout, or use WHERE store IN (...) to filter to specific shops by name.

How are different store currencies handled? Money metrics are automatically converted to your account's currency so cross-store totals are comparable. Override the target currency with WITH CURRENCY 'USD'. The currency actually used is returned in the response currency field.

How do I set the date range? Use SINCE with a relative value like -30d, -12w, -6M, or -1y, or absolute quoted dates like SINCE '2026-01-01' UNTIL '2026-02-01', or a named period with DURING. The response window echoes the exact UTC range that was measured.

Which time zone are the days measured in? The account's zone by default. Pass a time_zone in the request body (an IANA name such as Europe/Athens) to override it; the response time_zone confirms which zone was used.

How many rows can a query return? SoloQL returns grouped totals, not raw records. A GROUP BY is capped to the top 1000 groups by volume (raise it up to 5000 with LIMIT). If a query hits the cap, the response sets truncated and group_limit so you always know. For every group with no cap, run an export — see Result limits.

Can I get CSV instead of JSON? Yes. Send Accept: text/csv and the same query returns CSV, with the column titles as the header row.

What happens if my query has a mistake? You get a 400 with an errors array of positioned diagnostics — each with line, column, message, and severity — so you can point the developer straight at the problem.