Analytics

Everything Spondo Billing records about your customers (signals, AI costs, delivered value, orders, invoices and payments) can be queried with SQL. Use it to build your own reports, feed a warehouse, or answer a question the dashboard doesn't.

Operation	What it does
`GET /analytics/schema`	Lists the analytics views you can query, with every column, its type and what it means.
`POST /analytics/query`	Runs one read-only `SELECT` over those views and returns the rows.
`GET /analytics/signals/metadata`	Lists the fields your signals carry in `data`, with their types, so you know what to query.

See what you can query

curl https://billing.getspondo.com/api/v1/analytics/schema \
  --header "Authorization: Bearer $SPONDO_API_KEY"

The response lists each analytics view with its columns. The main ones:

View	One row per	Joins on
`fact_signal`	Signal. `data` holds its JSON; `effective_timestamp` is when it happened. `customer_id` is null until the customer it names exists.	`customer_id`, `product_id`, `trace_id`
`fact_usage_summary`	Customer, product, unit and event name per UTC day, with `event_count` and `billed_quantity`.	`customer_id`, `product_id`
`fact_cost`	Traced AI call with a cost. `cost_amount` is in US cents, with fractions.	`customer_id`, `product_id`, `trace_id`
`fact_delivered_value`	Signal and value metric it was valued on, with the `model_version` used. `delivered_value_cents` is in the model's currency.	`signal_id`, `customer_id`
`fact_credit_transaction`	Credits ledger entry: a `grant`, `pendingGrant` or `spend` (expiries are spends).	`customer_id`, `order_id`
`fact_order`	Order.	`customer_id`, `plan_id`
`fact_order_line`	Charge on an order line, with its pricing, quantity, unit price and what it has billed. An order line with several charges has several rows.	`order_id`, `order_line_attribute_id`, `product_id`
`fact_invoice`, `fact_invoice_line`	Invoice, and its lines. Amounts in cents.	`customer_id`, `order_id`, `order_line_id`
`fact_payment`	Payment. Amounts in cents.	`customer_id`, `invoice_id`
`fact_customer_unit_event`	A customer unit created or deleted, or its cap set or ended.	`customer_id`
`dim_customer`, `dim_customer_unit`, `dim_product`, `dim_plan`, `dim_credits_currency`, `dim_organization`	Customer, unit, product, plan, credit currency, and your account.	`id`
`dim_customer_unit_ancestor`	A unit and each unit above it, with `depth`. `WHERE ancestor_id = …` gives a unit's whole subtree.	`customer_unit_id`, `ancestor_id`
`dim_customer_unit_cap`, `dim_customer_unit_cap_current`	Every version of each unit's cap, and the version in force now.	`customer_unit_id`

Run a query

Send one SELECT (or WITH … SELECT) as query:

const result = await spondo.request("POST", "/analytics/query", {
  body: {
    query: `
      SELECT c.external_id AS customer,
             strftime('%Y-%m-%d', s.effective_timestamp) AS day,
             count(*) AS documents,
             SUM(json_extract(s.data, '$.pages')) AS pages
      FROM fact_signal s JOIN dim_customer c ON c.id = s.customer_id
      WHERE s.signal_name = 'document_uploaded'
        AND s.effective_timestamp >= '2026-11-01' AND s.effective_timestamp < '2026-11-03'
      GROUP BY customer, day ORDER BY day, customer`,
  },
});

{
  "columns": [
    { "name": "customer", "type": "String" },
    { "name": "day", "type": "String" },
    { "name": "documents", "type": "Int64" },
    { "name": "pages", "type": "Int64" }
  ],
  "rows": [
    ["acme-legal", "2026-11-01", "38", "455"],
    ["northwind", "2026-11-01", "12", "140"]
  ],
  "meta": { "rowCount": 2, "elapsedMs": 3.41, "truncated": false }
}

Things to know about results:

Integers come back as strings, such as "38", so large counts stay exact. Decimals are JSON numbers.
At most 10,000 rows. When there are more, meta.truncated is true; page through with your own ORDER BY … LIMIT … OFFSET.
Timestamps are ISO 8601 strings in UTC. Compare them with date strings (>= '2026-10-01') and bucket them with date(ts) for days or strftime('%Y-%m', ts) for months.
Money is in cents. Divide by 100 for dollars. Division always gives decimals, so SUM(amount) / 100 keeps the cents.

The SQL supports joins, WITH (including WITH RECURSIVE), subqueries, GROUP BY and HAVING, window functions, CASE, the usual aggregates, round, coalesce, nullif, date, datetime, strftime, julianday, and json_extract, json_each and json_tree for signals' data. These familiar analytics functions work too: now, today, toDate, toDateTime, toStartOfMonth, if, multiIf, greatest, least, countIf and sumIf. A query is at most 20,480 characters and may run for 30 seconds.

Errors

Status	Code	Why
`400`	`VALIDATION_ERROR`	No `query`, more than one statement, or a statement that isn't a `SELECT`.
`400`	`INVALID_QUERY`	The SQL doesn't compile or fails while running. `details` has the message, such as `no such column: x`.
`403`	`QUERY_FORBIDDEN`	The query reads something other than the analytics views.
`408`	`QUERY_TIMEOUT`	The query ran for more than 30 seconds. Narrow its time range, or aggregate a summary view such as `fact_usage_summary`.

Examples

Revenue, AI cost and margin by product for October:

WITH revenue AS (
  SELECT ol.product_id, SUM(il.total_before_tax) / 100 AS revenue_usd
  FROM fact_invoice_line il
  JOIN fact_order_line ol ON ol.order_line_attribute_id = il.order_line_attribute_id
  WHERE il.start_date >= '2026-10-01' AND il.start_date < '2026-11-01'
  GROUP BY ol.product_id
), cost AS (
  SELECT product_id, SUM(cost_amount) / 100 AS cost_usd FROM fact_cost
  WHERE start_time >= '2026-10-01' AND start_time < '2026-11-01'
  GROUP BY product_id
)
SELECT p.name AS product,
       ROUND(COALESCE(r.revenue_usd, 0), 2) AS revenue_usd,
       ROUND(COALESCE(c.cost_usd, 0), 2) AS cost_usd,
       ROUND(COALESCE(r.revenue_usd, 0) - COALESCE(c.cost_usd, 0), 2) AS margin_usd
FROM dim_product p
LEFT JOIN revenue r ON r.product_id = p.id
LEFT JOIN cost c ON c.product_id = p.id
ORDER BY revenue_usd DESC

AI cost per contract reviewed, by model, joining signals to the costs traced under them:

SELECT f.model,
       count(DISTINCT s.signal_id) AS contracts,
       ROUND(SUM(f.cost_amount) / 100 / count(DISTINCT s.signal_id), 4) AS cost_per_contract_usd
FROM fact_signal s JOIN fact_cost f ON f.trace_id = s.trace_id
WHERE s.signal_name = 'contract_reviewed'
  AND s.effective_timestamp >= '2026-10-01' AND s.effective_timestamp < '2026-11-01'
GROUP BY f.model ORDER BY cost_per_contract_usd DESC

Delivered value against AI cost per customer:

WITH value AS (
  SELECT customer_id, SUM(delivered_value_cents) / 100 AS value_usd FROM fact_delivered_value
  WHERE effective_timestamp >= '2026-10-01' AND effective_timestamp < '2026-11-01'
  GROUP BY customer_id
), cost AS (
  SELECT customer_id, SUM(cost_amount) / 100 AS cost_usd FROM fact_cost
  WHERE start_time >= '2026-10-01' AND start_time < '2026-11-01'
  GROUP BY customer_id
)
SELECT c.name,
       ROUND(COALESCE(v.value_usd, 0), 2) AS value_usd,
       ROUND(COALESCE(k.cost_usd, 0), 2) AS cost_usd,
       ROUND(v.value_usd / NULLIF(k.cost_usd, 0), 1) AS value_per_dollar_of_cost
FROM dim_customer c
LEFT JOIN value v ON v.customer_id = c.id
LEFT JOIN cost k ON k.customer_id = c.id
ORDER BY value_usd DESC LIMIT 10

Invoiced, collected and outstanding by month:

SELECT strftime('%Y-%m', issue_date) AS month,
       SUM(total_incl_tax) / 100 AS invoiced_usd,
       SUM(amount_paid) / 100 AS collected_usd,
       SUM(amount_due) / 100 AS outstanding_usd
FROM fact_invoice WHERE status = 'posted'
GROUP BY month ORDER BY month

Find the fields in your signals

Signals' data is free-form, so GET /analytics/signals/metadata tells you what's in it: for each event name in a window (the last 30 days by default), every field path, the types seen and how many signals carry it.

curl 'https://billing.getspondo.com/api/v1/analytics/signals/metadata?signalName=document_uploaded' \
  --header "Authorization: Bearer $SPONDO_API_KEY"

{
  "signals": [
    {
      "signalName": "document_uploaded",
      "paths": [
        { "path": "pages", "types": ["Int64"], "declared": false, "signals": 1840 },
        { "path": "review.minutes", "types": ["Float64", "Int64"], "declared": false, "signals": 212 }
      ]
    }
  ],
  "meta": { "fromDate": "2026-10-10T14:30:00.000Z", "toDate": "2026-11-09T14:30:00.000Z" }
}

Read a path in SQL with json_extract(data, '$.review.minutes').

Views (experimental)

Views package a few saved queries with your own HTML into a small dashboard, for your team or for each customer. A customer-scoped view runs its queries for one customer at a time, with every analytics view narrowed to that customer, so a query can never show one customer another's data. You can read a view's data for a customer with GET /experimental/views/{displayId}/data. To show a published view inside your product, get an embed token for the signed-in customer from your server with GET /experimental/views/{displayId}/embed-token: its url opens the view for that customer, in a page or an iframe, until the token expires (at most an hour).

Views are experimental: their operations live under /experimental and may change. The API reference documents them in full.

Prove value

Introduction