Metrics Query API Documentation

Overview

The metrics query endpoint returns one or more time series over a shared date range and interval. It is modelled on CloudWatch-style metric queries: a single request asks for a list of metrics, each identified by a caller-chosen id, a metric type, and a set of filters. The response returns one dense, contiguous series per requested metric, aligned to a common set of interval buckets.

This is a read endpoint, but it is called with POST so that per-metric filters can be expressed as a structured body.

Access: this endpoint requires an authenticated user with reporting access. See the authentication documentation.

Request

The request body is a JSON object with the following fields.

Name Type Required? Description
interval string No The bucket size for every series. One of the intervals. Defaults to daily. An unrecognised value is rejected.
start_date string (ISO-8601 date) No The first day to include, in your organization's time zone. Defaults to 31 days before end_date.
end_date string (ISO-8601 date) No The last day to include, in your organization's time zone, inclusive. Defaults to today.
metrics list of metric objects Yes The metrics to compute (1–100). See below.

Each entry in metrics is an object:

Name Type Required? Description
id string Yes A non-empty identifier you choose. Must be unique within the request; it is echoed back as the series id.
type string Yes The metric type: revenue or check_in_count.
filters object No Filters specific to the metric type.

Metrics

Product Attribute Filters

Every metric that supports selecting products by id also supports selecting them by an intrinsic attribute — all prepaid memberships, all single-entry passes, all rolling events — instead of enumerating each one. Each attribute is its own optional filter key holding a non-empty list of values. Values within a key are OR'd; different keys are AND'd; an empty list is rejected (omit the key to include everything).

Filter Type Applies to Values
membership_purchase_types list of strings membership recurring, prepaid, benefit
membership_billing_intervals list of strings membership weekly, biweekly, four_weeks, monthly, quarterly, half_yearly, yearly. Only recurring memberships have a billing interval, so this implicitly selects recurring memberships.
entry_pass_kinds list of strings entry_pass single (exactly one entry) or multi (more than one)
event_schedule_types list of strings event recurring, multi_part (multi-part / one-off), rolling
event_category_ids list of integers event Event types assigned to any of these event type categories. A removed category still matches the event types assigned to it.
product_statuses list of strings membership, entry_pass, event, voucher_pack active, retired, draft

Supported by revenue, product_sold_count and product_conversion (attributes must match the metric's product_type); by check_in_count and first_check_in_conversion (attributes must match the product type the entry_method implies — guest-pass entry is granted by a membership, so it takes membership attributes); and by membership_count, member_churn and membership_referral_count, which are membership-scoped and therefore take the membership attributes and product_statuses. An attribute filter used against a product type it does not apply to — or against a metric with no product dimension at all, such as a revenue metric with "product_type": "gift_card" or a check-in metric with no entry_method — returns a 400.

Attribute filters combine with an explicit product_ids (or membership_ids) list: the result is the intersection of every criterion. An intersection matching nothing is not an error — the metric simply returns an empty (zero) series.

The criteria are resolved when the query runs, not when it was written. A product created after a chart was saved appears in it automatically as soon as it matches, and a retired product keeps its historical values but stops accruing (unless product_statuses selects retired products explicitly). Attributes describe a product's current state for every interval in the series, so a membership later switched from prepaid to recurring is counted under recurring even for periods when it was sold as prepaid.

Revenue

"type": "revenue" — sums recorded revenue over a product, location, and revenue-source filter. Revenue series are additive.

Filter Type Description
product_type string Required. Which revenue to include: membership, entry_pass, event, voucher_pack, gift_card, other, or all.
product_ids list of integers Restrict to specific products of the product_type. Only valid for membership, entry_pass, event, and voucher_pack, and must be non-empty. Omit to include all products of the type.
location_ids list of integers Restrict to specific locations. Omit for all locations.
revenue_source string all (default), capitan, pos, or offline.
revenue_calculation string How revenue is calculated: net (default) reports sales net of refunds; gross reports sales only, ignoring refunds; net_of_fees reports sales net of refunds and payment processor fees. Processor fees are only tracked for payments collected by Capitan, so net_of_fees is only valid with a revenue_source of all or capitan — any other source returns a 400.
product attribute filters lists Select products by an intrinsic attribute instead of by id — see Product Attribute Filters. Which attributes are valid follows from the product_type.

Refunds are attributed to the day the refund was issued, not to the day of the original sale. A net series can therefore dip — or go negative — in a period holding large refunds of earlier periods' sales, while a gross series always reflects sale-day amounts.

Check-In Count

"type": "check_in_count" — counts check-ins over a location, entry-method, and time-of-day filter.

Filter Type Description
check_in_type string all (default) counts every check-in; unique counts distinct customers per interval (not additive — see the response); first_time counts first-ever check-ins (i.e. new customers).
entry_method string Restrict to a single entry method code (e.g. MEM). Omit for all methods.
product_ids list of integers Restrict to specific products used for entry (memberships, entry passes, event types, or guest-pass memberships, depending on the entry_method). Requires entry_method.
location_ids list of integers Restrict to specific locations. Omit for all locations.
time_of_day_start, time_of_day_end string (HH:MM or HH:MM:SS) Restrict each bucket to a daily time-of-day window, in your organization's local time. Only supported with the daily interval; rejected for any other interval.
day_of_week list of strings Restrict to check-ins that happened on these weekdays, in your organization's local time — sunday, monday, tuesday, wednesday, thursday, friday, saturday. Unlike the time-of-day window, this is supported at every interval: with the daily interval the non-matching days are still returned, as zero-valued buckets, and with a wider interval each bucket counts only its matching days (so a monthly series filtered to sunday is Sunday traffic per month). Omit, or send an empty list, for every day.
product attribute filters lists Select products by an intrinsic attribute instead of by id — see Product Attribute Filters. Which attributes are valid follows from the product type the entry_method implies, so an entry_method that records a product is required.

New Customer Count

"type": "new_customer_count" — counts customers by the interval in which their first check-in falls (their first_check_in_timestamp), attributed to their home location. Additive. Because it keys off the customer's first-check-in timestamp rather than a per-check-in count, it correctly includes customers whose check-in history was imported into Capitan.

Filter Type Description
location_ids list of integers Restrict to customers whose home location is one of these. Omit for all locations.

Product Sold Count

"type": "product_sold_count" — counts products sold (memberships, entry passes, event bookings, voucher packs, or gift cards) by the interval in which they were sold, attributed to the purchase location. Every record counts by default, regardless of price or how it was acquired; the optional exclude_* filters below narrow that. Additive.

Filter Type Description
product_type string Required. Which sales to count: membership, entry_pass, event (event bookings), voucher_pack, or gift_card.
product_ids list of integers Restrict to specific products of the product_type (membership / entry-pass / event-type / voucher-pack ids); must be non-empty. Not supported for gift_card (no per-product breakdown). Omit to count all products of the type.
location_ids list of integers Restrict to sales at these purchase locations. Omit for all locations.
exclude_gift_redemptions boolean When true, drop products acquired by redeeming a gift. Defaults to false. Applies to membership / entry_pass / voucher_pack; no effect on other types.
exclude_code_redemptions boolean When true, drop products acquired by redeeming a redemption code. Defaults to false. Applies to entry_pass / voucher_pack; no effect on other types.
exclude_zero_paid boolean When true, drop products with no money paid (amount paid = 0: summed across a membership's invoices, or the single invoice for other types; a null invoice counts as $0). Defaults to false. Applies to all types.
exclude_manually_created boolean When true, drop products created manually by staff rather than purchased. Defaults to false. Applies to entry_pass; no effect on other types.
product attribute filters lists Select products by an intrinsic attribute instead of by id — see Product Attribute Filters. Which attributes are valid follows from the product_type.

Membership Count

"type": "membership_count" — how many memberships, individual members, or membership add-ons the organization has over time, by their status at each interval. This is a point-in-time stock count (how many exist), not a flow, so it is not additive across intervals (see the response). Read from a pre-aggregated daily rollup.

Filter Type Description
count_by string What to count: memberships (default; membership records), members (a headcount of distinct member customers — a group membership counts each of its members, but a customer with more than one membership is counted once; unlinked members are excluded), or add_ons (membership add-ons).
status string Which memberships to count by their status at each interval: active (default), frozen, or active_and_frozen (both).
membership_ids list of integers Restrict to specific membership types; must be non-empty. Omit to count all membership types.
billing_location_ids list of integers Restrict to memberships billing to one of these locations; must be non-empty. Omit to count every location. This is the location a membership bills to, not a location anything happened at — the same dimension member_churn and membership_referral_count segment by. It is point-in-time: each day counts a membership under the location it billed to on that day, so moving a membership between locations does not rewrite earlier intervals. A membership whose snapshot carries no billing location is counted in the unfiltered total and under no location, so selecting every location can return less than omitting the filter.
add_on_names list of strings For count_by=add_ons, restrict to specific add-on names. Omit to count all add-ons.
discount_association_id integer Restrict memberships / members counts to those receiving a discount through this association. Omit to count all. Ignored for count_by=add_ons (there is no per-association add-on breakdown).
value_in_period string How to reduce the daily count to one value per interval: start (default; the count at the start of the period), end, min, or max. The default matches the Membership Counts by Status report, which always reads the start of the period.
product attribute filters lists Narrow the membership types by an intrinsic attribute instead of by id — see Product Attribute Filters. This metric is membership-scoped, so the membership attributes and product_statuses apply.

Discovering add-on names. To chart add-ons with count_by=add_ons you must name them, but the set of add-ons an organization has used — including discontinued ones no longer in any membership's current configuration — is only known from the rollup. The companion endpoint GET /api/metrics/membership-add-on-names/ returns that set: a sorted JSON list of the distinct add-on names present in the daily membership-count rollup. Pass start_date / end_date (and optionally interval) to scope discovery to the same day range a report over that period reads — interval-aligned, including the interval's lead-in day — so a name present only in the lead-in is still returned as a column; or omit both dates to list every add-on name in the org's whole rollup history (the chart-builder picker's use). Request the names for your period, then issue one membership_count metric per name. Bad input returns a bare-string 400, as with this query endpoint.

Customer Retention

"type": "customer_retention" — how well the customers who first checked in during each interval were retained. Customers are counted by the interval their first check-in falls in (their first_check_in_timestamp), attributed to their home location, and split into retained and lost.

A retention series carries one of three values, chosen with the component filter, so a single metric type serves all of retained, lost, and the rate:

  • retained / lost — whole-number counts (count unit), additive across intervals.
  • rate (the default) — the retention rate retained / (retained + lost) as a fraction in the range 0–1 (percent unit; multiply by 100 to display). It is not additive (a rate cannot be summed or averaged across intervals), and is null for a settled interval whose new customers were all excluded (see below) — a genuine "no one to retain", never a fake 0%.

Whether a customer was retained is only known once roughly 90 days have passed since their first check-in. So a bucket whose interval has not fully aged past that window still contains customers whose outcome is undetermined: reporting it yet would understate it. Every component of such a too-recent bucket is therefore returned as null (not 0) for that interval — the retention of a recent period simply isn't known yet. A bucket is settled, and starts returning values, only once its whole interval is more than about 100 days in the past.

Filter Type Description
component string Which value the series carries: rate (default), retained, or lost.
location_ids list of integers Restrict to customers whose home location is one of these. Omit for all locations.

Product Conversion

"type": "product_conversion" — of the customers who used a product for the first time in each interval while not already a member, how many became members within a configurable conversion window. Records are bucketed by when the product was used (their created_at) and split into converted and not converted against the window.

Like Customer Retention, a conversion series carries one of three values, chosen with the component filter:

  • converted / not_converted — whole-number counts (count unit), additive across intervals.
  • rate (the default) — the conversion rate converted / (converted + not_converted) as a fraction in the range 0–1 (percent unit; multiply by 100 to display). It is not additive, and is null for a settled interval with no records — a genuine "no one to convert", never a fake 0%.

Whether a record converted is only known once its conversion window has fully elapsed. So a bucket whose interval has not yet aged past that window still contains records whose outcome is undetermined; every component of such a too-recent bucket is returned as null (not 0) for that interval. Because the window is a per-metric filter, this cutoff moves with the chosen conversion_window — a wider window keeps more recent buckets pending.

Filter Type Description
component string Which value the series carries: rate (default), converted, or not_converted.
conversion_window string Required. How long after the product use to check for membership: 30_days, 60_days, 90_days, 120_days, 180_days, or 1_year.
product_type string Required. The product category: membership, entry_pass, or event.
product_ids list of integers Restrict to specific products of that type (membership / entry pass / event-type ids). Omit for all products of the type. Note that "first-time use" is per specific product, so an all-products series counts first-time product uses (a customer who first-used several products of the type is counted once per product), whereas a single-product series counts each customer at most once.
product attribute filters lists Select products by an intrinsic attribute instead of by id — see Product Attribute Filters. Which attributes are valid follows from the product_type.

First Check-In Conversion

"type": "first_check_in_conversion" — of the customers whose first check-in falls in each interval, how many became members within a configurable conversion window. Records are bucketed by the check-in's timestamp, attributed to the check-in location, and split into converted and not converted against the window. The component, conversion_window, and settled-bucket semantics are identical to Product Conversion above.

Filter Type Description
component string Which value the series carries: rate (default), converted, or not_converted.
conversion_window string Required. One of 30_days, 60_days, 90_days, 120_days, 180_days, or 1_year.
entry_method string Restrict to first check-ins made with this entry method (a check-in entry-method code). Omit for all methods.
product_ids list of integers Restrict to first check-ins made with these specific products used for entry (scoped to entry_method, which is then required). Rejected for methods with no product (free entry, migrated).
location_ids list of integers Restrict to first check-ins at one of these locations. Omit for all locations.
product attribute filters lists Select products by an intrinsic attribute instead of by id — see Product Attribute Filters. Which attributes are valid follows from the product type the entry_method implies, so an entry_method that records a product is required.

Member Churn

"type": "member_churn" — of the members at each interval boundary, how many had churned (were no longer members) by the end of a configurable churn window. A member is counted as a member if their membership is active, frozen, or in a failed-payment state; a member is not counted as churned if they are in any of those states at the window's end (so a temporary freeze or failed payment is not mistaken for churn). Unlinked members are excluded.

The churn check is against the whole organization's member set, so a member who merely switches to a different membership type — even one outside a membership_ids filter — is retained, not churned.

A churn series carries one of four values, chosen with the component filter:

  • members / retained / lost — whole-number point-in-time counts (count unit).
  • rate (the default) — the churn rate lost / members as a fraction in the range 0–1 (percent unit; multiply by 100 to display). It is null for a bucket with no members — a genuine "no one to lose", never a fake 0%. Note this divides by the member count, unlike the retention / conversion rates.

Every component is a point-in-time set size, so none are additive across intervals (see the response). A bucket whose churn window has not fully elapsed (its window end is on or after today, in your organization's time zone) is undetermined and returned as null for every component. Because a churn window can't be measured against a single day's snapshot, a daily interval is rejected with a 400.

Filter Type Description
component string Which value the series carries: rate (default), members, retained, or lost.
membership_ids list of integers Restrict the interval member set to these membership types; must be non-empty. Omit for all types. The window-end churn check stays organization-wide regardless.
billing_location_ids list of integers Restrict the interval member set to memberships billing to one of these locations; must be non-empty. This is the billing location, distinct from the check-in / home location_ids used by other metrics. Omit for all.
churn_window_days integer How long (in days) after each period a member must remain to count as retained: 1–366, default 30.
value_in_period string Which interval boundary to measure the member set at: start (default) or end.
product attribute filters lists Narrow the membership types by an intrinsic attribute instead of by id — see Product Attribute Filters. This metric is membership-scoped, so the membership attributes and product_statuses apply.

Member Ancillary Revenue

"type": "member_ancillary_revenue" — the total money members spent on ancillary purchases (revenue attributed to a customer who was a member at the time of purchase, outside their membership), summed by the interval in which the purchase completed and attributed to the purchase location. Reported in your organization's currency. Additive.

Filter Type Description
location_ids list of integers Restrict to purchases at these locations. Omit for all locations.

Member Ancillary Spend Count

"type": "member_ancillary_spend_count" — the number of distinct members who made an ancillary purchase in each interval, over the same purchases as Member Ancillary Revenue. Because the same member can appear in more than one interval, this is a distinct count and is not additive across intervals (see the response).

Filter Type Description
location_ids list of integers Restrict to purchases at these locations. Omit for all locations.

Membership Referral Count

"type": "membership_referral_count" — the number of membership referrals: memberships purchased that credit a referring customer, counted by the interval in which the purchase completed and segmented by the membership's billing location. Each referred membership is one referral, so this is additive.

Filter Type Description
billing_location_ids list of integers Restrict to memberships billing to one of these locations; must be non-empty. This is the billing location, distinct from the check-in / home location_ids used by other metrics. Omit for all locations.
membership_ids list of integers Restrict to these membership types; must be non-empty. Omit for all types.
product attribute filters lists Narrow the membership types by an intrinsic attribute instead of by id — see Product Attribute Filters. This metric is membership-scoped, so the membership attributes and product_statuses apply.

Composite Metrics

A composite metric computes each interval's value from other metrics in the same request, so a derived series (a ratio, a combined total, a margin) is plotted without exporting the data and doing the arithmetic elsewhere. It is a different kind of entry in the metrics list: instead of a type from the table above plus filters, it carries "type": "composite", an operation, and a list of operands.

{
  "id": "avg_10_pass_price",
  "type": "composite",
  "operation": "divide",
  "operands": [{"metric_id": "pass_revenue"}, {"metric_id": "passes_sold"}],
  "unit": "currency"
}
Field Type Description
operation string One of sum, subtract, multiply, divide. sum and multiply take two or more operands; subtract and divide take exactly two, applied in order (A − B, A ÷ B).
operands list Two or more operands. Each is either a metric reference {"metric_id": "<id>"} naming another metric in the same request, or a numeric constant {"constant": 0.9} (e.g. a margin). At least one operand must be a metric reference.
unit string (optional) Override the series' unit (currency, count, or percent). Omit to infer it (see below).

A referenced metric may itself be a composite, so expressions nest — e.g. (A + B) ÷ C. The reference graph must be acyclic: a metric that references itself (directly or through a chain), or an unknown metric_id, is rejected with a 400. Composites count toward the per-request metric limit like any other entry. A composite entry takes no filters — scope each operand metric instead; a filters key on a composite is ignored.

The value is computed for each interval bucket over the aligned series. A bucket is returned as null — a gap, not a zero — when any operand is null there (for example a rate metric's undetermined bucket) or when a divide has a zero denominator.

The series' flags follow from its operands:

  • unit — an explicit unit wins; otherwise, if every metric operand shares one unit that unit is used; a divide by a count denominator keeps the numerator's unit (so revenue ÷ count is a currency average price); failing those, count. A percent composite carries a fraction in 0–1, like the rate metrics (multiply by 100 to display).
  • additive — true only for a sum or subtract whose every metric operand is itself additive; multiply and divide composites are never additive.

Intervals

Name Syntax
Dailydaily
Weeklyweekly
Monthlymonthly
Quarterlyquarterly
Annualannual (or yearly)

Buckets are computed in your organization's time zone. Weekly buckets start on your organization's configured first day of the week.

Comparing Earlier Periods

An optional compare block asks for the same metrics over one or more earlier periods, returned alongside the primary series — year over year, month over month, or the last few years together.

{
  "start_date": "2026-01-01",
  "end_date": "2026-08-04",
  "interval": "monthly",
  "compare": {
    "mode": "previous_year",
    "periods_back": 2
  },
  "metrics": [ ... ]
}
Name Type Description
mode string previous_year shifts back a calendar year at a time. previous_period shifts back by the requested range's own calendar unit — the previous month for a range covering a month, the previous quarter for a quarter, the previous year for a year. A range that does not line up with a calendar unit shifts back by its own length in days.
periods_back integer How many earlier periods to return, 1 to 3. Defaults to 1.

Shifts are calendar-aligned, not day counts. Asking for August 1–4 against the previous period returns July 1–4, not the four days before August 1st. This is what makes month-over-month comparison mean what it says when months have different lengths.

A partial range is where the two modes differ. If the requested range stops short of its own calendar unit — year to date, or the current month so far — previous_year returns each earlier period complete (all of last year) so it can be drawn as context, while previous_period returns the matching span only (January–August of last year for a January–August request). A range that covers a whole calendar unit is compared against a whole one in both modes, so September is compared against all 31 days of August rather than the first 30.

Only part of an earlier period may be comparable. elapsed_buckets says how many leading buckets line up with what the primary range has actually reached — bounded by today, not by the requested end date, since "this year" covers a whole calendar year even in August. Compute a percent change over those buckets only: eight months of this year against all twelve of last year is not a comparison, it is a fabricated collapse.

Every metric is computed once per period, so "periods_back": 3 runs four passes over the database. The query's time budget is divided between them, which means a request that only just fits without a comparison can be stopped with one.

Limits

A single request may ask for up to 100 metrics (composites included).

The date range a request may cover depends on the interval, because a narrower bucket over the same range means more buckets to compute:

Interval Maximum date range
daily1 year
weekly3 years
monthly10 years
quarterly30 years
annual100 years

The same start and end dates that are accepted for a monthly interval can therefore be rejected for a daily one.

There is no separate limit on buckets multiplied by metrics: the two limits above already bound it, and asking for one series per product over a long range is a normal thing to do.

Beyond these limits, each query is given a fixed amount of database time. A query that exceeds it is stopped and returns a 400 asking you to narrow the request — a shorter date range, a coarser interval, fewer metrics, or narrower filters. It is not a failure you should retry unchanged: the same request will take the same amount of time again. Interactive requests to this endpoint get a smaller time budget than the same query run from a scheduled emailed report, so a query that is too slow here may still succeed on a schedule.

Response

The response echoes the resolved interval and date range, and returns one series per requested metric, in request order.

Name Type Description
interval string The interval used (the resolved default if none was given).
start_date, end_date string (ISO-8601 date) The resolved date range.
series list of series objects One per requested metric, in request order.
comparisons list Present only when the request carried a compare block: one entry per earlier period, nearest first. Each has periods_back, label (a short name for the period, e.g. 2025 or Jul 2026), start_date and end_date (the period as returned), elapsed_end_date and elapsed_buckets (how much of it is comparable with the primary range), and its own series list carrying the same metric ids.

When a compare block is present and the requested range is a partial calendar unit, the buckets are extended to cover the whole unit so the earlier periods can be drawn against them. The primary series then reads null for every bucket past its own end_date — a gap, not a zero.

Each series object:

Name Type Description
id string The metric id from the request.
additive boolean Whether the series' values may be summed across intervals. This is false for a unique check-in count (the same customer can appear in more than one interval), so such a series must not be rolled up into a wider period by addition.
unit string The unit the values are measured in, so a consumer can format an axis or tooltip without a per-metric-type lookup. One of currency (money, in the organization's currency), count (a whole-number count), or percent. A percent series carries fractional values in the range 0–1 — multiply by 100 and append % to display (e.g. 0.42 → 42%).
points list One point per interval bucket, in chronological order. Every series in a response shares the same buckets. Each point has time_period_machine (a machine-readable key, e.g. 2026-06-01 for a day or 2026-06 for a month), time_period_human (a display label), and value (a number, or null for a bucket a rate/gauge series has no value for — e.g. a retention rate with no new customers to retain; a count series reads 0 for an empty bucket). A series inside comparisons is labelled with the primary period's buckets, so it lines up against them position by position, and carries its own period name in comparison_time_period_human.

Operations

Request
POST /api/metrics/query/
{
    "interval": "monthly",
    "start_date": "2026-01-01",
    "end_date": "2026-03-31",
    "metrics": [
        {
            "id": "membership_revenue",
            "type": "revenue",
            "filters": {"product_type": "membership"}
        },
        {
            "id": "unique_check_ins",
            "type": "check_in_count",
            "filters": {"check_in_type": "unique"}
        }
    ]
}

Response
200 OK
{
    "interval": "monthly",
    "start_date": "2026-01-01",
    "end_date": "2026-03-31",
    "series": [
        {
            "id": "membership_revenue",
            "additive": true,
            "unit": "currency",
            "points": [
                {"time_period_machine": "2026-01", "time_period_human": "Jan 2026", "value": 12500.0},
                {"time_period_machine": "2026-02", "time_period_human": "Feb 2026", "value": 13100.0},
                {"time_period_machine": "2026-03", "time_period_human": "Mar 2026", "value": 12980.0}
            ]
        },
        {
            "id": "unique_check_ins",
            "additive": false,
            "unit": "count",
            "points": [
                {"time_period_machine": "2026-01", "time_period_human": "Jan 2026", "value": 412},
                {"time_period_machine": "2026-02", "time_period_human": "Feb 2026", "value": 388},
                {"time_period_machine": "2026-03", "time_period_human": "Mar 2026", "value": 401}
            ]
        }
    ]
}