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>]
[HAVING <condition> [AND <condition> ...]]
[TIMESERIES <interval>]
[COMPARE TO PREVIOUS]
[ORDER BY <field> [DESC]]
[LIMIT <n>]
[WITH TOTALS]
[WITH CUMULATIVE_VALUES]
[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.

WITH CUMULATIVE_VALUES is a chart-level modifier: in the EcomSolo report UI it renders the visualized metric as running totals, while the response data (and any table or CSV built from it) stays per-bucket. It applies to additive metrics only — averages and rates cannot be summed over time. API responses are unaffected by the flag; it simply round-trips in the query text.

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

Numeric and date ranges can also be written with BETWEEN, which is inclusive at both ends: BETWEEN 2 AND 4 is exactly the same filter as >= 2 AND <= 4.

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

Filtering totals with HAVING

WHERE and HAVING filter at different moments, and on aggregated data the difference changes the answer.

WHERE runs before grouping, on individual records. HAVING runs after, on the grouped totals you get back:

FROM sales SHOW total_sales
GROUP BY product_title
HAVING total_sales > 50

That returns the products whose total sales exceed 50. Written as WHERE total_sales > 50 it would instead keep individual line items over 50 and group what survives — a different question, and usually not the one being asked.

This matters most on sales, where reversals (returns, cancellations, order edits) are stored as negative rows. A WHERE test discards those negative rows along with the small ones, so the filtered total can come out higher than the unfiltered one. HAVING leaves the reversals to net into each total as they normally do.

Conditions chain with AND, and may test different metrics:

FROM sales SHOW total_sales, net_quantity
GROUP BY product_title
HAVING total_sales >= 50 AND net_quantity > 2

HAVING accepts the comparison operators (=, !=, <, <=, >, >=). The list forms (IN, CONTAINS) and the null checks belong to WHERE. You can test a metric your SHOW clause does not list — it is computed for the comparison and left out of the response.

When a query has WITH TOTALS, the totals row is computed over the rows that survive HAVING, so the summary always describes the table beneath it.

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.
availability — present and "not_synced" only when the dataset holds no data for your stores at all: the empty result is a state, not a report of zero activity. Cross-check GET /v1/datasets — if it says not_permitted there, a Shopify permission is missing; otherwise the store is typically still syncing. Omitted on every ordinary result, including genuine zeros.

Result limits

SoloQL is built for analytics — it returns grouped totals, not raw records. When you want the records themselves, use Fetching records instead. Most integrations use both: a query for the summary, records for the detail behind it.

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_charges, 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_charges, 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_charges, 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_charges, 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_charges, 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.