# Query commerce analytics Use `analyticsQuery` in the Admin GraphQL API to answer questions such as how sales changed, which products generated revenue, which stores took orders, or how often buyers used a payment method or discount. You send a reporting-language string and receive structured values that your application can render as a table or chart. The language has SQL-like keywords, but it is a bounded reporting language, not general SQL. ## Before you begin Use your [project-specific Admin endpoint and credentials](/getting-started/connect) from trusted code with permission to view orders. Analytics reads the latest available reporting data, so a new or changed order may not appear immediately. Check the result type and availability errors before treating an empty report as zero activity. There are two separate interfaces: | Interface | Use it for | | --- | --- | | `analyticsQuery` and `analyticsQueryMetrics` | The query language described here: current order values, formulas, grouping, time series, and comparisons. | | `analyticsReport` and `analyticsMetrics` | The separate daily financial-report contract, including commercial reversals, in one original transaction currency. | Do not mix the metric catalogs or assume identically named metrics have identical accounting behavior. For example, `sales_reversals` belongs to the financial-report catalog and is not accepted by `analyticsQuery`. The query-language `net_sales` does not subtract separate refund events. ## Run your first query This request returns the last 30 complete days in Copenhagen time, converted to EUR, with an average order total and totals for the entire period. Keep the query text in GraphQL variables rather than interpolating it into the GraphQL document. Admin API ```graphql query Analytics($query: String!) { analyticsQuery(query: $query) { __typename ... on AnalyticsQuerySuccess { currency grain period { from until } comparisonPeriod { from until } totals { ...MetricValue } buckets { start comparisonStart values { ...MetricValue } } groups { key label values { ...MetricValue } buckets { start comparisonStart values { ...MetricValue } } } visualization { metric type } } ... on AnalyticsQueryFailure { code message } } } fragment MetricValue on AnalyticsValue { name unit value isPartial comparison { value absoluteChange percentChange status isPartial } } ``` Variables ```json { "query": "FROM sales SELECT orders, total_sales, total_sales / orders AS average_order_total TIMESERIES day INCLUDE TOTALS CURRENCY 'EUR' TIMEZONE 'Europe/Copenhagen' SINCE start_of_day(-30d) UNTIL today" } ``` On success, render `buckets` for the daily series and `totals` for the period summary. Read `unit`, `currency`, and `isPartial` alongside each value. On `AnalyticsQueryFailure`, use `code` and `message`; HTTP success alone does not mean the report succeeded. Handle top-level GraphQL authentication and execution errors too. ## Supported syntax Clauses must appear in this order. Square brackets below mean optional syntax; do not include the brackets in a request. ```text FROM sales SELECT metric_or_formula [AS alias], ... [WHERE field = 'value' [AND field != 'value'] ...] [GROUP BY dimension [, dimension ...]] [TIMESERIES hour|day|week|month|quarter | COHORT month] [INCLUDE TOTALS, PERCENT_CHANGE] [CURRENCY 'EUR'] [TIMEZONE 'Europe/Copenhagen'] SINCE boundary UNTIL boundary [COMPARE previous_period] [ORDER BY grain_or_selected_metric ASC|DESC] [LIMIT 1_to_1000] [VISUALIZE selected_name AS line|area|bar|donut|table] ``` An ungrouped report requires `TIMESERIES`, unless it uses `COHORT month`. A category report uses `GROUP BY` without `TIMESERIES`; its `ORDER BY` takes a selected metric or alias, and its visualization supports `bar`, `donut`, or `table`. Add `TIMESERIES` after `GROUP BY` to return a trend inside each group. Category-only reports do not support totals or previous-period comparison; query each period separately. Keywords and identifiers are case-insensitive, and returned metric names and aliases are lowercase. Use single quotes around currency codes, time zones, and filter values; escape an apostrophe inside a value as `''`. Do not add SQL semicolons, comments, joins, `SELECT *`, arbitrary functions, or extra datasets. ### Available metrics These are the metrics accepted by `analyticsQuery` today. Sales and order metrics use order creation dates and exclude currently cancelled orders. Refunds use their own processing dates and can include refunds on cancelled orders. | Metric | Meaning | | --- | --- | | `units` | Ordered units. Physical returns are not deducted. | | `customers` | Distinct identified customers with an order. Guest orders without a customer ID are excluded. | | `new_customers` | Distinct customers placing their first known non-cancelled order in the period. | | `returning_customers` | Distinct customers ordering after their first known non-cancelled order. A customer can be new and returning in the same period. | | `retention_rate` | The percentage of an acquisition cohort purchasing in each activity month. This metric requires `COHORT month`. | | `discounted_orders` | Distinct orders with a positive recorded merchandise discount. | | `orders` | Order count. Product groups count distinct orders containing the product; payment and discount groups count distinct qualifying orders per group. | | `total_sales` | Current order totals including shipping and tax. In product groups, the product's line totals exclude shipping. | | `taxes` | Sum of known tax amounts. Missing tax can make the result partial or unavailable. | | `net_order_value` | Current order totals before tax, including shipping. Product groups use their line amounts before tax. | | `gross_sales` | Original merchandise value before discounts and tax. | | `discounts` | Merchandise price reductions and allocated discounts as a positive deduction, excluding shipping discounts and tax. | | `net_sales` | Current merchandise totals after discounts, excluding shipping and tax. Separate refund events are not subtracted. | | `shipping` | Current shipping charges after discounts, excluding tax. | | `refunds` | Recorded payment refund transactions as positive amounts, grouped by processing date. Time series and totals only. | | `net_refunds` | Recorded payment refunds before tax, calculated with each order's net-to-gross ratio and grouped by processing date. Time series and totals only. | | `discount_amount` | Attributable discount amounts for discount groups only. Product-discount groups use recorded product-price reductions; cart-discount and code groups use recorded cart-discount applications. | `discount_amount` retains the recorded application's tax basis and excludes unlabelled price reductions and shipping-line-only discounts. It is not interchangeable with the merchandise `discounts` metric. Some dimensions and metrics depend on reporting data that may not yet be available for a project; handle `DATA_UNAVAILABLE` rather than substituting zero. Discover the deployed query-language catalog with this query: Admin API ```graphql query AnalyticsMetricCatalog { analyticsQueryMetrics { id description isMoney } } ``` ### Filters Use up to 16 `WHERE` conditions joined by `AND`. The supported operators are `=` and `!=`; conditions are exact matches and are not full-text search. | Filter | Expected value | | --- | --- | | `store`, `product`, `variant`, `customer` | Stable resource ID. Resolve a human-readable name to an ID before building the query. | | `sku` | Exact stored SKU. | | `country`, `city` | Stored address value. Country values are normally uppercase codes. | | `customer_type` | `new`, `returning`, or `unknown`. | | `shipment_state`, `order_state` | Exact stored state. | | `currency` | Original order currency, such as `EUR`. | | `cart_discount`, `product_discount` | Stable discount key returned by the corresponding group. | | `discount_code` | Exact applied code. | | `payment_method`, `payment_gateway` | Normalized method or stable gateway value returned by the corresponding group. | Dates belong in `SINCE` and `UNTIL`, not `WHERE`. There is no `payment_state` filter. A filter on `product`, `variant`, or `sku` selects matching lines when the report is grouped by a product dimension; in an order-level report it selects whole orders containing a match. ### Grouping capabilities | Grouping | Supported metrics | Interpretation | | --- | --- | --- | | `TIMESERIES hour`, `day`, `week`, `month`, or `quarter` | All except `discount_amount` and `retention_rate` | Calendar buckets in the requested reporting timezone, optionally with full-period totals and comparison. Weeks begin Monday. | | `GROUP BY store`, `country`, `city`, `customer`, `customer_type`, or `shipment_state` | Order, customer, unit, and order-level monetary metrics | Order-level groups. Orders without a value use an empty key or an `Unknown` label. | | `GROUP BY product`, `variant`, or `sku` | `orders`, `units`, `total_sales`, `taxes`, `net_order_value`, and supported customer metrics | Product and variant amounts exclude shipping and other unallocated order charges. | | `GROUP BY payment_method` or `payment_gateway` | `orders` only | Distinct orders with authorized, paid, or later-refunded payments per method or gateway. No currency filter or arithmetic formulas. | | `GROUP BY discount` or `cart_discount` | `orders`, `discount_amount`, and formulas using them | Recorded cart-discount applications. `cart_discount` is an alias for `discount`. | | `GROUP BY product_discount` | `orders`, `discount_amount`, and formulas using them | Recorded product-price reductions attributed to product discounts. | | `GROUP BY discount_code` | `orders`, `discount_amount`, and formulas using them | Recorded cart-discount applications grouped by exact discount code. | Use up to three different grouping dimensions in one request. Discount and payment dimensions cannot be combined with another dimension. An order can belong to multiple product, payment, or discount groups, so summing those group counts does not give a unique order count. Use `key` as the group identity and `label` for display. For multiple dimensions, `key` is a JSON array in `GROUP BY` order and `label` joins readable values with `/`. For a category-only query, render `groups[].values`. For `GROUP BY ... TIMESERIES ...`, render `groups[].buckets`; the top-level `buckets` remains empty. Grouped time series are limited to 100 groups and 50,000 returned group buckets. ### Customer cohorts `COHORT month` groups identified customers by their acquisition month and returns monthly activity buckets for each group. The supported metrics are `customers`, `retention_rate`, `orders`, and `total_sales`. Cohorts do not support filters, original-currency filtering, or comparison, and `SINCE` must begin on the first day of a month in the reporting timezone. Use a table visualization and treat missing future months as unobserved, not zero. Analytics query ```text FROM sales SELECT customers, retention_rate COHORT month CURRENCY 'EUR' TIMEZONE 'Europe/Copenhagen' SINCE 2026-01-01 UNTIL 2026-09-01 VISUALIZE retention_rate AS table ``` ### Dates, ordering, and limits `SINCE` is inclusive and `UNTIL` is exclusive. Use optional `TIMEZONE 'Area/Location'` with an IANA time zone name to control calendar dates, `today`, day offsets, buckets, cohorts, and comparison periods. If you omit `TIMEZONE`, Thor uses UTC; it does not infer a tenant time zone. Use `SINCE 2026-08-01 UNTIL 2026-09-01` for all of August in the selected reporting timezone. | Boundary | Meaning | | --- | --- | | `2026-09-01` | Midnight on the specified date in the reporting timezone. | | `today` | Start of the current day in the reporting timezone. | | `now` | Current instant, so the last bucket may be incomplete. | | `start_of_day(-30d)` | Start of the current reporting-timezone day, shifted back 30 calendar days. | | `start_of_hour(-24h)` | Start of the current reporting-timezone hour, shifted back 24 elapsed hours. | The two `start_of_...` functions accept a signed integer offset ending in `d` or `h`, including `0d` or `0h`. Raw timestamps and bare relative offsets such as `SINCE -30d` are not accepted. Response periods and bucket starts are serialized as UTC instants. Convert them to the query's reporting timezone for labels. A daylight-saving transition can produce 23 or 25 hourly buckets in a local day; repeated local hours have different UTC instants. Time series default to ascending date order. Their `ORDER BY` must name the selected grain. Groups default to ascending order of the first selected metric; use an explicit descending order for top-N reports. Group sorting can use an alias selected by the same query. `LIMIT` defaults to 1,000 and accepts 1 through 1,000. It limits returned buckets or groups, not the source orders, and does not reduce full-period totals. There is no offset or cursor pagination; use smaller time ranges if you need more buckets. Hourly and daily ranges can span at most 366 days; weekly, monthly, quarterly, cohort, and category-only ranges can span at most 3,660 days. Queries are limited to 4,096 characters, 256 tokens, and 20 selected metrics. ### Formulas and units Use `+`, `-`, `*`, `/`, parentheses, and numeric constants. Multiplication and division bind before addition and subtraction. Calculated selections require `AS` with a unique name, such as `total_sales / orders AS average_order_total`. Aliases label results; another formula cannot reference an alias as if it were a built-in metric. Addition and subtraction require matching units. For example, `orders + total_sales` is invalid. Supported result units include `orders`, `units`, `customers`, `minor_units`, `ratio`, `percentage`, and supported per-order, per-unit, and per-customer rates. Dividing by zero returns a null value, not an infinite result or zero. A period's average is calculated from its aggregate amounts and counts, not by averaging its daily averages. Money values are decimal minor units of the response's `currency`. Use the currency's decimal exponent when formatting; do not divide every currency by 100 or round values before calculating ratios. See [Money and rounding](/concepts/pricing/money). ### Original currency versus reporting currency `WHERE currency = 'DKK'` filters by original transaction currency. `CURRENCY 'EUR'` selects the output currency and converts the selected monetary amounts. These clauses solve different problems and can be used together. Without `WHERE`, orders in different currencies can contribute to the report. Without `CURRENCY`, Thor uses the configured reporting currency. Conversion uses current exchange rates, not historical rates from each order date. For an original-currency view, use the same currency in both clauses. If a required conversion rate is unavailable, the query fails rather than returning a partially converted monetary report. ## Common reports ### Compare sales with the previous period This compares 30 complete Copenhagen calendar days with the immediately preceding local calendar period. `PERCENT_CHANGE` requires `COMPARE previous_period`. The starting boundary must align to an hour for hourly comparisons, or midnight for other grains, in the reporting timezone. Analytics query ```text FROM sales SELECT orders, total_sales, total_sales / orders AS average_order_total TIMESERIES day INCLUDE TOTALS, PERCENT_CHANGE CURRENCY 'EUR' TIMEZONE 'Europe/Copenhagen' SINCE start_of_day(-30d) UNTIL today COMPARE previous_period ORDER BY day ASC VISUALIZE total_sales AS line ``` Use `comparisonPeriod` and each bucket's `comparisonStart` to label the comparison. The previous period has equal local calendar duration; its elapsed duration can differ across a daylight-saving transition. It is not necessarily the previous calendar month or the same dates last year. `comparison.value` is the previous value; `absoluteChange` is current minus previous. `percentChange` uses the absolute previous value as the denominator and is returned only when requested and defined. ### Rank products by sales Analytics query ```text FROM sales SELECT orders, units, total_sales, total_sales / orders AS average_product_order_value WHERE currency = 'EUR' GROUP BY product CURRENCY 'EUR' TIMEZONE 'Europe/Copenhagen' SINCE start_of_day(-30d) UNTIL today ORDER BY total_sales DESC LIMIT 10 VISUALIZE total_sales AS bar ``` This average uses the product's amounts and distinct order count, not full order totals or unit quantities. Product groups do not support the `gross_sales`, `discounts`, `net_sales`, or `shipping` breakdown metrics. ### Track store sales over time This returns one weekly series per store. Read each series from `groups[].buckets`, not the top-level `buckets`. Analytics query ```text FROM sales SELECT total_sales, orders GROUP BY store TIMESERIES week CURRENCY 'EUR' TIMEZONE 'Europe/Copenhagen' SINCE 2026-06-01 UNTIL 2026-09-01 ORDER BY total_sales DESC LIMIT 10 VISUALIZE total_sales AS line ``` ### Compare stores Analytics query ```text FROM sales SELECT orders, gross_sales, discounts, net_sales, shipping, taxes, total_sales GROUP BY store CURRENCY 'EUR' SINCE 2026-08-01 UNTIL 2026-09-01 ORDER BY total_sales DESC LIMIT 20 VISUALIZE total_sales AS table ``` ### Count payment-method usage Analytics query ```text FROM sales SELECT orders GROUP BY payment_method SINCE start_of_day(-30d) UNTIL today ORDER BY orders DESC LIMIT 20 VISUALIZE orders AS donut ``` Use `payment_gateway` instead to group by gateway. This is an order-usage report, not a count of payment attempts or a report of captured amounts. Do not add a currency filter; payment groups count orders across currencies. ### Measure discount-code usage Analytics query ```text FROM sales SELECT orders, discount_amount, discount_amount / orders AS average_discount GROUP BY discount_code CURRENCY 'EUR' SINCE start_of_day(-30d) UNTIL today ORDER BY discount_amount DESC LIMIT 20 VISUALIZE discount_amount AS bar ``` Replace `discount_code` with `cart_discount` or `product_discount` for the respective application category. This reports recorded usage, not the current list of configured campaigns or their eligibility rules. ### Track refunds by processing date Analytics query ```text FROM sales SELECT refunds, net_refunds TIMESERIES day INCLUDE TOTALS CURRENCY 'EUR' TIMEZONE 'Europe/Copenhagen' SINCE start_of_day(-30d) UNTIL today ORDER BY day ASC VISUALIZE refunds AS area ``` A refund processed in this period can relate to an order created before it. `refunds` is the recorded gross refund; `net_refunds` estimates its before-tax amount with the order's net-to-gross ratio. Do not assume subtracting period refunds from period order sales produces a commercial-reversal report. For financial reporting, inspect the separate `analyticsReport` contract and its metric definitions. ## Use Analytics in the dashboard and Odin The Thor dashboard can save query results as shared charts and can open authenticated reports produced by Odin. An Odin report is a saved result snapshot: reopening a conversation does not rerun the query, and opening it in Analytics does not save a dashboard change until a user reviews and saves the dashboard. The Admin API exposes the shared definition through `analyticsDashboard` and replaces it atomically with `analyticsDashboardSave`. Use the returned `revision` as `expectedRevision` on the next save; a stale revision returns `REVISION_CONFLICT` without changing the dashboard. A definition contains 1 to 30 charts, and the submitted list is the complete replacement, so omitting a chart removes it. Saved chart types are `METRIC`, `GOAL`, `LINE`, `AREA`, `BAR`, `DONUT`, `TABLE`, and `BREAKDOWN`. Charts can follow dashboard controls by using `{since}`, `{until}`, optional `{grain}`, and `{currency}` tokens in their query. Enable comparison in the chart filters instead of placing `COMPARE` in a saved query. A `GOAL` chart requires a positive monetary target in major currency units and an inclusive `goalPeriod` with `startDate` and `targetDate`. Its query uses `{goalStart}` and `{goalUntil}`; the dashboard converts the inclusive target date to the query's exclusive end. Goal charts use their own period instead of the dashboard date range. ## Render results accurately Keep these distinctions in the UI and in agent-generated summaries: - `value: null` means unavailable or undefined; it is not zero. - `isPartial: true` means known amounts were returned but required source amounts are missing. - `comparison.status: AVAILABLE` means the baseline is usable; `ZERO_BASELINE` makes percentage change undefined; `MISSING_VALUE` means at least one value is unknown. - Check `comparison.isPartial` independently of the current value's flag. - `totals` covers the full requested range when requested, even when `LIMIT` returns only some buckets. - `visualization` is presentation metadata. Thor returns values and chart intent, not a rendered chart. Use `line`, `area`, `bar`, or `table` with time series; use `bar`, `donut`, or `table` with category groups. For donut charts, ensure the selected values form meaningful positive segments. ## Troubleshoot a query | Failure code | What to do | | --- | --- | | `INVALID_QUERY` | Check clause order, metric names, aliases, date boundaries, range limits, and grouping restrictions. Read the returned message before changing the query. | | `UNSUPPORTED_METRIC` | Choose metrics supported by the requested grouping, especially for product and payment reports. | | `INVALID_CURRENCY` | Use a supported three-letter currency code in single quotes. | | `FX_RATE_MISSING` or `FX_UNAVAILABLE` | Conversion could not be completed. Retry later or use a deliberately chosen original-currency report. | | `DATA_UNAVAILABLE` | The required analytics data is not available yet. Ask the project operator or support to refresh reporting data; do not report zero sales. | | `SCHEMA_NOT_INITIALIZED` or `UNAVAILABLE` | Reporting is not ready or cannot currently be reached. Retry later, and contact the project operator if the condition persists. | | `TIMEOUT` | Reduce the time range or request fewer metrics before retrying. | | `NUMERIC_OVERFLOW` | Simplify the formula or reduce its numeric scale. | | `QUERY_FAILED` | Preserve a sanitized query and failure message for support; retry with bounded backoff. | Do not treat a parser error as a transient failure, and never include Admin credentials in a support report. Use `analyticsQueryMetrics` to discover the deployed metric catalog and `analyticsQuery` to execute the examples above. See the [Admin API reference](/api/admin-graphql) for general GraphQL conventions.