Analytics query cookbook | Paid

Copy-paste recipes for common questions. Each is the query value you send to POST /query. See the Schema reference for every column.

Reminder: monetary columns are cents (divide by 100; fact_cost.cost_amount is fractional cents), credit amounts are decimal counts of credits (never divided by 100), UInt64/Int64 results come back as JSON strings, and Decimal results come back as JSON numbers (wrap in toString() for exact digits beyond 2^53).

Prerequisites

A Paid API key with access to your organization’s Analytics API.
Signals, costs, delivered value, invoices, or credit activity already present for the tables you want to query.
Familiarity with the table names and joins in the Schema reference.

Usage and signals

Total signals in the last 30 days

SELECT count() AS signals
FROM fact_signal
WHERE created_at > now() - INTERVAL 30 DAY

Signal volume by type

SELECT signal_name, count() AS signals
FROM fact_signal
WHERE created_at > now() - INTERVAL 30 DAY
GROUP BY signal_name
ORDER BY signals DESC

Daily signal time-series

SELECT toDate(created_at) AS day, count() AS signals
FROM fact_signal
WHERE created_at > now() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day

Signals by customer (resolving names)

SELECT c.name AS customer, count() AS signals
FROM fact_signal s
JOIN dim_customer c ON s.customer_id = c.id
WHERE s.created_at > now() - INTERVAL 30 DAY
GROUP BY customer
ORDER BY signals DESC
LIMIT 20

Querying signal metadata

Signals carry a free-form JSON data payload. Access a field with data.<path> and cast it to a type. First discover available keys with GET /signals/metadata, then filter or group on them.

Casting a missing key with ::String yields an empty string (''), not NULL, so a '' bucket in a grouped result represents signals that don’t carry that key.

Group by a string metadata field

SELECT data.opportunity_stage::String AS stage, count() AS signals
FROM fact_signal
WHERE created_at > now() - INTERVAL 30 DAY
GROUP BY stage
ORDER BY signals DESC

Filter on a metadata field

SELECT count() AS completed_calls
FROM fact_signal
WHERE signal_name = 'call'
  AND data.call_status::String = 'completed'
  AND created_at > now() - INTERVAL 7 DAY

Aggregate a numeric metadata field

SELECT
  data.account_tier::String AS tier,
  avg(data.call_length_sec::Float64) AS avg_call_seconds
FROM fact_signal
WHERE signal_name = 'call'
  AND created_at > now() - INTERVAL 30 DAY
GROUP BY tier

AI cost

cost_amount is exact fractional cents (a typical AI call costs a fraction of a cent), so SUM(cost_amount) / 100 gives exact dollars and matches the Cost Explorer in the Paid UI.

Total spend (dollars) by vendor

SELECT vendor, count() AS calls, sum(cost_amount) / 100 AS dollars
FROM fact_cost
WHERE start_time > now() - INTERVAL 30 DAY
GROUP BY vendor
ORDER BY dollars DESC

Spend by model with token usage

SELECT
  model,
  count() AS calls,
  sum(input_tokens) AS input_tokens,
  sum(output_tokens) AS output_tokens,
  sum(cost_amount) / 100 AS dollars
FROM fact_cost
WHERE start_time > now() - INTERVAL 30 DAY
GROUP BY model
ORDER BY dollars DESC

Daily cost trend

SELECT toDate(start_time) AS day, sum(cost_amount) / 100 AS dollars
FROM fact_cost
WHERE start_time > now() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day

Cost by customer

SELECT c.name AS customer, sum(co.cost_amount) / 100 AS dollars
FROM fact_cost co
JOIN dim_customer c ON co.customer_id = c.id
WHERE co.start_time > now() - INTERVAL 30 DAY
GROUP BY customer
ORDER BY dollars DESC
LIMIT 20

Cost per signal (joining usage and cost)

Signals and costs share a trace_id, so you can attribute AI cost to the signal that incurred it.

SELECT
  s.signal_name,
  count(DISTINCT s.signal_id) AS signals,
  sum(co.cost_amount) / 100 AS dollars,
  sum(co.cost_amount) / 100 / count(DISTINCT s.signal_id) AS dollars_per_signal
FROM fact_signal s
JOIN fact_cost co ON s.trace_id = co.trace_id
WHERE s.created_at > now() - INTERVAL 30 DAY
GROUP BY s.signal_name
ORDER BY dollars DESC

Revenue

Monthly revenue (posted invoices)

SELECT
  toStartOfMonth(issue_date) AS month,
  sum(total_incl_tax) / 100 AS revenue
FROM fact_invoice
WHERE status = 'posted'
  AND issue_date > now() - INTERVAL 12 MONTH
GROUP BY month
ORDER BY month

Revenue by customer

SELECT c.name AS customer, sum(i.total_incl_tax) / 100 AS revenue
FROM fact_invoice i
JOIN dim_customer c ON i.customer_id = c.id
WHERE i.status = 'posted'
GROUP BY customer
ORDER BY revenue DESC
LIMIT 20

Revenue by product (invoice lines → order lines → products)

SELECT p.name AS product, sum(il.total_after_tax) / 100 AS revenue
FROM fact_invoice_line il
JOIN fact_order_line ol ON il.order_line_id = ol.id
JOIN dim_product p ON ol.product_id = p.id
GROUP BY product
ORDER BY revenue DESC

Fixed-fee vs usage revenue split (posted invoices)

charge_type is usage for metered lines and oneTime/recurring/seatBased for fixed charges. It is empty on manual lines (no linked pricing) and on lines predating charge-type attribution. Bucket those as unknown rather than folding them into fixed, or they will inflate the fixed-fee share.

SELECT
  multiIf(
    il.charge_type = 'usage', 'usage',
    il.charge_type IN ('oneTime', 'recurring', 'seatBased'), 'fixed',
    'unknown'
  ) AS billing_basis,
  count() AS lines,
  sum(il.total_after_tax) / 100 AS revenue
FROM fact_invoice_line il
JOIN fact_invoice i ON il.invoice_id = i.id
WHERE i.status = 'posted'
GROUP BY billing_basis
ORDER BY revenue DESC

A non-trivial unknown total means manual or unattributed lines carry real revenue. Inspect them before treating the fixed-vs-usage split as complete. For the full breakdown, group by il.charge_type directly (oneTime / recurring / seatBased / usage, plus empty for unattributed).

Outstanding / overdue invoices

SELECT c.name AS customer, sum(i.amount_due) / 100 AS outstanding
FROM fact_invoice i
JOIN dim_customer c ON i.customer_id = c.id
WHERE i.payment_status IN ('pending', 'partiallyPaid', 'overdue')
GROUP BY customer
ORDER BY outstanding DESC

Payments and cash flow

SELECT toDate(payment_date) AS day, sum(amount) / 100 AS collected
FROM fact_payment
WHERE payment_status = 'posted'
  AND payment_date > now() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day

Billed usage

Which signals drive billed revenue

Counts every window billed so far, including those closed since September 17, 2026, which this table showed as collecting until October 2026. Collecting windows have no subtotal yet.

SELECT signal_name, sum(subtotal) / 100 AS billed
FROM fact_usage_summary
WHERE status = 'billed'
GROUP BY signal_name
ORDER BY billed DESC

Overage (usage above the included allowance)

SELECT
  signal_name,
  sum(signals_quantity) AS used,
  sum(included_quantity) AS included,
  sum(greatest(signals_quantity - included_quantity, 0)) AS overage
FROM fact_usage_summary
WHERE included_quantity IS NOT NULL
GROUP BY signal_name

Credits

Credit amounts and balances are Decimal(30, 6), not integers: a single spend can be a fraction of a credit, and they are counts of credits rather than cents, so never divide them by 100. As with fractional cents, round after aggregating rather than per row.

Net credit flow by currency

SELECT cc.name AS currency, sum(t.amount) AS net_credits
FROM fact_credit_transaction t
JOIN dim_credits_currency cc ON t.credits_currency_id = cc.id
GROUP BY currency

Top credit-spending signals

SELECT signal_name, -sum(amount) AS credits_spent
FROM fact_credit_transaction
WHERE type = 'SPEND'
  AND created_at > now() - INTERVAL 30 DAY
GROUP BY signal_name
ORDER BY credits_spent DESC

Credit consumption by user within a customer

If your signals include external_user_id in the data payload (see First signals), you can break down credit spend per user:

SELECT
  s.data.external_user_id::String AS external_user_id,
  cc.name AS currency,
  -sum(t.amount) AS credits_spent
FROM fact_credit_transaction t
JOIN fact_signal s ON t.signal_mongo_id = s.signal_id
JOIN dim_credits_currency cc ON t.credits_currency_id = cc.id
WHERE t.type = 'SPEND'
  AND t.created_at > now() - INTERVAL 30 DAY
GROUP BY external_user_id, currency
ORDER BY credits_spent DESC

Customer units

Customer units are the teams, departments, or users inside a customer. Join dim_customer_unit to label usage and credit spend by unit, and dim_customer_unit_ancestor to total a unit with everything below it. Always use LEFT JOIN and keep the (unassigned) row: it is real usage that was not attributed to a unit, and dropping it makes unit totals disagree with the customer total.

Credit consumption by unit for one customer

SELECT
  coalesce(nullIf(u.name, ''), t.external_customer_unit_id, '(unassigned)') AS unit,
  t.credits_currency_id AS credits_currency_id,
  cc.name AS currency,
  greatest(-sum(t.amount), 0) AS credits_consumed
FROM fact_credit_transaction t
LEFT JOIN dim_customer_unit u
  ON u.customer_id = t.customer_id AND u.external_id = t.external_customer_unit_id
JOIN dim_credits_currency cc ON t.credits_currency_id = cc.id
WHERE t.type = 'SPEND'
  AND t.customer_id = '<customer-uuid>'
  AND coalesce(t.effective_at, t.created_at) >= now() - INTERVAL 30 DAY
GROUP BY t.external_customer_unit_id, unit, credits_currency_id, currency
ORDER BY credits_consumed DESC

Group by the unit’s key and the currency’s id as well as their names: two units, or two credit currencies, can share a name.

Signals by unit

A signal names its unit by customer_unit_id, or only by external_customer_unit_id if the unit did not exist yet when the signal arrived. Join twice and prefer the first match:

SELECT
  coalesce(
    nullIf(if(s.customer_unit_id IS NOT NULL, by_id.name, by_key.name), ''),
    s.external_customer_unit_id,
    '(unassigned)'
  ) AS unit,
  count() AS signals
FROM fact_signal s
LEFT JOIN dim_customer_unit by_id
  ON by_id.customer_id = s.customer_id AND by_id.id = s.customer_unit_id
LEFT JOIN dim_customer_unit by_key
  ON by_key.customer_id = s.customer_id AND by_key.external_id = s.external_customer_unit_id
WHERE s.customer_id = '<customer-uuid>'
  AND s.created_at > now() - INTERVAL 30 DAY
GROUP BY if(s.customer_unit_id IS NOT NULL, by_id.id, by_key.id), unit
ORDER BY signals DESC

Credit consumption per top-level unit, including the units below it

SELECT
  branch.name AS unit,
  t.credits_currency_id AS credits_currency_id,
  cc.name AS currency,
  greatest(-sum(t.amount), 0) AS credits_consumed
FROM fact_credit_transaction t
JOIN dim_customer_unit u
  ON u.customer_id = t.customer_id AND u.external_id = t.external_customer_unit_id
JOIN dim_customer_unit_ancestor a
  ON a.customer_id = u.customer_id AND a.unit_id = u.id
JOIN dim_customer_unit branch
  ON branch.customer_id = a.customer_id AND branch.id = a.ancestor_id
JOIN dim_credits_currency cc ON t.credits_currency_id = cc.id
WHERE t.type = 'SPEND'
  AND t.customer_id = '<customer-uuid>'
  AND coalesce(t.effective_at, t.created_at) >= now() - INTERVAL 30 DAY
  AND (
    branch.parent_id IN (SELECT id FROM dim_customer_unit WHERE parent_id IS NULL)
    OR (a.depth = 0 AND branch.parent_id IS NULL)
  )
GROUP BY branch.id, unit, credits_currency_id, currency
ORDER BY credits_consumed DESC

Each row counts once for every unit above it, so pick one level, as the parent_id filter does here, rather than summing nested units together. The a.depth = 0 line adds one row for usage booked on the root itself; without it that usage is left out.

Spend caps

A spend cap limits how many credits of one credits currency a customer unit and every unit below it can spend per period. dim_customer_unit_cap_current holds the cap in force now on each unit, with its current period (period_start, period_end). The usage counted against a cap is the net SPEND of its credits currency by the unit’s whole subtree, from period_start up to the earlier of period_end and effective_until.

Caps are spend policy, so the cap tables need billing access, as the cap endpoints do. Through the Analytics API they return rows only for an API key with read:billing (or a dashboard user); for any other key they return no rows. In a custom view they return rows only when the view has capsEnabled: true.

Cap usage so far this period

This gives the same used and remaining as the customer unit cap endpoints (currentPeriod.used):

WITH caps AS (
  SELECT *, least(period_end, coalesce(effective_until, period_end)) AS counted_until
  FROM dim_customer_unit_cap_current
  WHERE customer_id = '<customer-uuid>'
),
spend AS (
  SELECT caps.id AS cap_id, greatest(-sum(t.amount), 0) AS used
  FROM fact_credit_transaction t
  JOIN dim_customer_unit u
    ON u.customer_id = t.customer_id AND u.external_id = t.external_customer_unit_id
  JOIN dim_customer_unit_ancestor a
    ON a.customer_id = u.customer_id AND a.unit_id = u.id
  JOIN caps
    ON caps.customer_id = a.customer_id AND caps.customer_unit_id = a.ancestor_id
    AND caps.credits_currency_id = t.credits_currency_id
  WHERE t.type = 'SPEND'
    AND coalesce(t.effective_at, t.created_at) >= caps.period_start
    AND coalesce(t.effective_at, t.created_at) < caps.counted_until
  GROUP BY caps.id
)
SELECT
  cap_unit.name AS unit,
  cc.name AS currency,
  caps.amount AS cap,
  ifNull(spend.used, 0) AS used_so_far,
  caps.amount - used_so_far AS remaining,
  caps.period_start AS period_start,
  caps.period_end AS period_end
FROM caps
JOIN dim_customer_unit cap_unit
  ON cap_unit.customer_id = caps.customer_id AND cap_unit.id = caps.customer_unit_id
JOIN dim_credits_currency cc ON cc.id = caps.credits_currency_id
LEFT JOIN spend ON spend.cap_id = caps.id
ORDER BY used_so_far / caps.amount DESC

The LEFT JOIN keeps caps with no spend yet. remaining goes negative once a unit is over its cap.

Caps near their limit across customers

Drop the customer filter from caps in the query above and replace its final SELECT with:

SELECT
  c.name AS customer,
  cap_unit.name AS unit,
  caps.amount AS cap,
  ifNull(spend.used, 0) AS used_so_far,
  round(100 * used_so_far / caps.amount, 1) AS percent_used,
  caps.period_end AS resets_at
FROM caps
JOIN dim_customer c ON c.id = caps.customer_id
JOIN dim_customer_unit cap_unit
  ON cap_unit.customer_id = caps.customer_id AND cap_unit.id = caps.customer_unit_id
LEFT JOIN spend ON spend.cap_id = caps.id
WHERE used_so_far >= 0.8 * caps.amount
ORDER BY percent_used DESC

The cap in force at another time

dim_customer_unit_cap keeps every version. The one in force at an instant is the highest version whose window covers it:

SELECT customer_unit_id, credits_currency_id, amount, schedule_type, anchor_day, version
FROM dim_customer_unit_cap
WHERE effective_from <= '2026-09-15 00:00:00'
  AND (effective_until IS NULL OR effective_until > '2026-09-15 00:00:00')
ORDER BY version DESC
LIMIT 1 BY customer_unit_id, credits_currency_id

Unit history

fact_customer_unit_event has one row per change to a unit: created, renamed, made billable or not, moved, or deleted. changed_fields names what changed. Who made the change and unit metadata values are not included.

What changed in a unit’s tree last week

SELECT
  e.created_at,
  e.event_type,
  e.external_id,
  e.name_after AS unit,
  e.changed_fields,
  e.name_before,
  e.from_parent_external_id,
  e.to_parent_external_id
FROM fact_customer_unit_event e
JOIN dim_customer_unit_ancestor a
  ON a.customer_id = e.customer_id AND a.unit_id = e.customer_unit_id
JOIN dim_customer_unit top
  ON top.customer_id = a.customer_id AND top.id = a.ancestor_id
WHERE top.customer_id = '<customer-uuid>'
  AND top.external_id = '<unit-key>'
  AND e.created_at >= now() - INTERVAL 7 DAY
ORDER BY e.version

The tree is today’s tree: a unit moved out of it during the week is not listed, and one moved into it is listed with its earlier history. Order by version rather than created_at to replay changes in the order they happened.

Delivered value

delivered_value_cents is denominated in each row’s delivered_value_currency, which is not always USD: organizations using value models can deliver value in EUR, GBP, and other currencies. Always group by (or filter on) delivered_value_currency; summing cents across currencies produces meaningless totals. Cents are exact and may carry fractions, so round after aggregating.

Value delivered by type

SELECT
  value_type_name,
  delivered_value_currency AS currency,
  round(sum(delivered_value_cents) / 100, 2) AS value_amount,
  sum(unit_value) AS total_units
FROM fact_delivered_value
WHERE created_at > now() - INTERVAL 30 DAY
GROUP BY value_type_name, delivered_value_currency
ORDER BY value_amount DESC

Value vs. cost (ROI) by customer

SELECT
  c.name AS customer,
  dv.delivered_value_currency AS currency,
  round(sum(dv.delivered_value_cents) / 100, 2) AS value_amount
FROM fact_delivered_value dv
JOIN dim_customer c ON dv.customer_id = c.id
WHERE dv.created_at > now() - INTERVAL 30 DAY
GROUP BY customer, currency
ORDER BY value_amount DESC
LIMIT 20

Paginating large result sets

Results are capped at 10,000 rows. For larger extracts, page with a WHERE filter on an ordered column (keyset pagination) rather than a large OFFSET:

SELECT signal_id, signal_name, created_at
FROM fact_signal
WHERE created_at < '2026-06-01 00:00:00'  -- last value from the previous page
ORDER BY created_at DESC
LIMIT 1000

Analytics API overview for endpoints, limits, and errors.
Schema reference for every table and column you can query.