> This page is for version v2 (default).
> For other versions, use one of these documentation indexes:
> - v2 (default): https://docs.paid.ai/v-2/llms.txt
> - v1: https://docs.paid.ai/v-1/llms.txt

> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://docs.paid.ai/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://docs.paid.ai/_mcp/server.

# Analytics query cookbook

> Ready-to-use SQL recipes for the Analytics API

Copy-paste recipes for common questions. Each is the `query` value you send to
`POST /query`. See the [Schema reference](./schema-reference) for every column.

> **Note**
>
> 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](https://app.paid.ai/settings/api-keys) 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](./schema-reference).

## Usage and signals

**Total signals in the last 30 days**

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

**Signal volume by type**

```sql
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**

```sql
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)**

```sql
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.

> **Note**
>
> 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**

```sql
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**

```sql
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**

```sql
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**

```sql
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**

```sql
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**

```sql
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**

```sql
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.

```sql
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)**

```sql
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**

```sql
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)

```sql
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.

```sql
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**

```sql
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

```sql
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.

```sql
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)

```sql
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**

```sql
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**

```sql
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](/documentation/getting-started/first-signals#user-level-tracking-within-a-customer)),
you can break down credit spend per user:

```sql
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**

```sql
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:

```sql
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**

```sql
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`):

```sql
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:

```sql
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:

```sql
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**

```sql
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**

```sql
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**

```sql
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`:

```sql
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
```

## Related

* [Analytics API overview](./overview) for endpoints, limits, and errors.
* [Schema reference](./schema-reference) for every table and column you can query.