Analytics API
Read CallQueryAnalyticsCustom3
Run an ad-hoc Analytics V4 query that an agent works out on the spot: any grouping, filter or aggregate that no ready metric covers, such as how many customers held at least 500 points at the end of a given day. Query language compatibility is NOT maintained: figure the query out anew for every call, never save or reuse one.
| Kind | Read API call |
|---|---|
| Method | GET or POST |
| URL | https://app.bubblehouse.com/api/v2023061/<shop>/QueryAnalyticsCustom3 |
| Authentication | Shop Token |
Made for agentic ad-hoc querying. An agent that needs a number no ready metric gives looks at what is stored, works out a query, runs it and uses the answer in the same conversation.
Query language compatibility is NOT maintained; your agents should figure out a query anew when they need to make a call. The language is internal to Bubblehouse analytics. We change its operations, tables and columns whenever analytics needs it: without notice, without a version, and without keeping old queries working. A query that runs today may fail or mean something else next week.
So do not save a query, put one in a script, a report or a scheduled job, or build anything on a fixed query. For a number you will ask for again, use QueryAnalytics3; when no metric fits, ask Bubblehouse to add one.
Works only for shops on Analytics V4. It reads only the shop's analytics store, never live data. A query can return a row per customer: IDs and numbers, never names or emails.
How to write a query
- Call
DescribeAnalytics3withcustom_tables: true. It lists every stored table with its columns and a readyscanto paste, pluslaunch_day, which you need for day numbers. - Find a metric close to what you want and call
ExplainAnalytics3for it, orDescribeAnalytics3withdefinitions: true. Both return working queries:steps[].queryandmetrics[].query. - Change that query: its axes, its predicate, its aggregate. Run it for a short day range first.
The engine reports a mistake as bad_data with its own message, for example unknown table: customers.nope or cube axis missing. Fix the query and try again.
What is stored
fact-fieldtables hold one row per entity: for example a customer's enrollment day and first order day.snap-fieldtables hold an entity's state on a day, one row per day on which it changed: for example a customer's tier, membership and points balance.eventtables hold one row per action: orders, refunds, points earned and spent, redemptions, referrals, receipts.
Every column is an integer or a boolean. There are no names, emails or other text.
- IDs (customer, order, tier, campaign) are unsigned 64-bit integers. Results show them as 16-digit hex strings, the form the rest of the API uses.
- Days are whole numbers counted from the shop's
launch_day: 0 is the launch day, 1 the day after. The day column isd. To filter by a date, subtractlaunch_dayfrom it yourself. - Money and fractions are integers scaled by 1,000,000: $12.50 is 12500000, and 25% is 250000.
- Booleans read as 1 and 0 in values and coordinates.
start_day and end_day limit event scans automatically. They do not limit snap-field or fact-field scans: for a state as of some day, keep rows with d at or before that day and take the last value per entity, as the first example does. market and campaign filter the tables that have such a column and leave the others alone.
Rows and tables
A query is a tree of operations, each a JSON object with an op. Some yield rows (scan, union, expr-field, and where over rows). The rest yield a table: cells addressed by zero or more axes, each cell holding one or more values called layers. A table with no axes is a single number. group turns rows into a table. The outermost operation must yield a table.
Operations
{"op":"scan","entity":E,"kind":K,"table":T}: the rows of a stored table. Take it fromcustom_tables[].scan.{"op":"where","input":X,"pred":EXPR}: keeps the rows, or the cells of a table, for which the expression is not 0. Over rows the expression reads columns withfield; over a table it reads values withlayerand coordinates withcoord.{"op":"expr-field","input":ROWS,"key":NAME,"expr":EXPR}: adds a computed column to each row.{"op":"union","inputs":[ROWS,...]}: the rows of several inputs with the same columns, one after another.{"op":"group","input":ROWS,"agg":AGG,"value":EXPR,"axes":[COLUMN,...]}: one cell per distinct combination of the axis columns, holding the aggregate of the expression over that cell's rows. Omitaxesfor a single number.{"op":"join","mode":"inner"|"outer"|"anti","inputs":[TABLE,...]}: matches cells with equal coordinates. The result has one layer per input, in input order.innerkeeps cells present in every input,outerkeeps cells present in any (missing values read as 0),antikeeps cells of the first input that are absent from the others. Inputs need the same axes, or a subset of them: a table with fewer axes is repeated along the missing ones. Tables with unrelated axes cannot be joined.{"op":"fold","input":TABLE,"agg":AGG,"axis":AXIS}: removes one axis by aggregating along it. Folding the only axis gives a single number.{"op":"emit","input":TABLE,"value":EXPR,"axes":[{"atom":NAME,"expr":EXPR},...]}: computes one new value per cell from that cell's layers, replacing them. Optionalaxesadd axes computed the same way.{"op":"sweep","input":TABLE,"step":"sum"|"diff","axis":"d"}: walks along an axis in order.sumgives running totals and fills in every day of the period;diffgives the change from the previous cell.{"op":"metric-ref","id":METRIC}: the table of a ready metric for the same days and filters. Use it to combine metrics or to continue from one.{"op":"empty-rows"}: no rows.
Aggregates
sum, count, avg, min, max; unicnt (number of distinct values); popcnt (number of values that are not 0); first and last (the value from the first or last row in stored order, which for snapshots is day order; not allowed over a union).
Expressions
{"op":"field","atom":COLUMN}: a column of the current row. Only where rows are read.{"op":"layer","layer":N}: value number N of the current cell, from 0. Only where a table is read.{"op":"coord","atom":AXIS}: the current cell's coordinate on an axis.{"op":"const","value":INTEGER}: a number. Write money and fractions already scaled, and days as day numbers.{"op":"param","id":ID}: a shop setting:point_value(money value of one point) orpoint_margin_value(the same after margin), both scaled by 1,000,000.{"op":"field-in","atom":COLUMN,"values":[{"kind":"u64"|"i64"|"day"|"bool","value":V},...]}: 1 when the column equals one of the values. Give an ID as a decimal number, not hex.- With
args:add,mul,min,max,and,ortake one or more;sub,div,ceil,eq,ne,gt,gte,lt,ltetake two;between(value, low, high, both ends included) andmul-div(a × b ÷ c without overflow in the middle) take three.
All arithmetic is on 64-bit integers. div drops the remainder, ceil divides and rounds up, and dividing by 0 gives 0. Comparisons give 1 or 0. Multiplying two scaled numbers scales twice: use mul-div with 1000000 as the divisor.
Results
result.axes names the axes; each cell has coords in that order and values, one per layer. A table without axes comes back as result.value. Values are raw: this call does not convert money, fractions or booleans. The day axis d is the exception: it is named day and its coordinates are dates. Cells are sorted by coordinates and cut at limit, with truncated set. A query that matches nothing returns no cells.
Limits
- The query is at most 64 KiB, 64 levels deep and 1,000 objects and lists.
- It computes for at most 30 seconds, or
time_limitseconds, up to 120. - The tables it builds along the way may take 5 GB in total. This cannot be raised.
- One analytics computation runs at a time per shop, and a few on the whole server. A call waits up to 10 seconds for its turn and gets
too_many_requestsif none comes. Do not send calls for one shop in parallel. - Days after today count as today.
Custom queries are rationed per shop, because nobody knows what one costs until it has run. A shop may send 5 at once, then 5 per minute. Each query is also charged after it runs for the seconds it computed, at least one. A shop has 5 seconds saved after a rest, and every 6 seconds of waiting pay off 1 second. So small queries can follow one another, and a query that computed for 10 seconds must be waited off for about a minute. A shop over either limit gets slow_down_custom_analytics_queries with the number of seconds to wait; nothing is computed and nothing is charged. Queries stopped by a limit and queries that failed in the engine are charged for the time they took. QueryAnalytics3, ExplainAnalytics3 and DescribeAnalytics3 are not rationed.
Keep intermediate tables small. A table with a cell per customer per day (grouping by customer and d, and above all a sweep over it) grows as customers × days and hits the memory limit on any sizable shop. Filter to the customers or days you need first, and group by one of the two. A query stopped by a limit returns bad_data that says which limit.
Input
-
queryobject requiredThe query: a tree of operations whose outermost one yields a table.
See the reference above. Start from
steps[].queryofExplainAnalytics3,metrics[].queryofDescribeAnalytics3, or acustom_tables[].scan. -
First day to include, in the shop's time zone.
Defaults to the shop's analytics launch day. Earlier days count as the launch day. Limits
eventscans and the days asweepfills in. -
Last day to include, in the shop's time zone.
Defaults to today. Later days count as today.
-
marketstring optionalLimit the computation to one market: a key from
marketsinDescribeAnalytics3. -
campaignstring optionalLimit the computation to one campaign: a name from
campaignsinDescribeAnalytics3, or the campaign's ID. -
time_limitinteger optionalHow many seconds the computation may run before it is stopped.
Defaults to 30. The server allows at most 120 and treats a larger number as 120. The limit is on computing. When analytics is busy the call first waits for its turn, for 10 seconds at most whatever this number is.
-
limitinteger optionalMost cells to return. Defaults to 1000; at most 10,000.
Output
-
First day actually computed.
-
Last day actually computed.
-
resultobject requiredThe table:
axes,cellswithcoordsand rawvalues(orvaluefor a single number), andtruncated.
Usage Examples
How many customers held at least 50 points at the end of a day
No ready metric or list call gives this count. Read the query from the inside out:
scan reads the customer snapshot rows; the inner where keeps the rows up to day 858 (858 days after launch_day).
group takes the last points balance of each customer, which makes a table with one cell per customer.
The outer where keeps the customers whose balance (layer 0 of that table) is at least 50, and fold counts them along the customer axis, which leaves one number.
Request
{
"end_day": "2026-07-07",
"query": {
"agg": "count",
"axis": "customer",
"input": {
"input": {
"agg": "last",
"axes": [
"customer"
],
"input": {
"input": {
"entity": "customers",
"kind": "snap-field",
"op": "scan",
"table": "snaps"
},
"op": "where",
"pred": {
"args": [
{
"atom": "d",
"op": "field"
},
{
"op": "const",
"value": 858
}
],
"op": "lte"
}
},
"op": "group",
"value": {
"atom": "pts_bal",
"op": "field"
}
},
"op": "where",
"pred": {
"args": [
{
"layer": 0,
"op": "layer"
},
{
"op": "const",
"value": 50
}
],
"op": "gte"
}
},
"op": "fold"
}
}
Response
{
"end_day": "2026-07-07",
"result": {
"axes": [],
"metric": "custom",
"unit": "raw",
"value": 1
},
"start_day": "2024-03-01"
}
Combine a ready metric with a shop setting
Outstanding points times the value of a point, in millionths of the currency unit: 1400000 is 1.40.
Request
{
"end_day": "2026-07-07",
"query": {
"input": {
"id": "points_outstanding",
"op": "metric-ref"
},
"op": "emit",
"value": {
"args": [
{
"layer": 0,
"op": "layer"
},
{
"id": "point_value",
"op": "param"
}
],
"op": "mul"
}
}
}
Response
{
"end_day": "2026-07-07",
"result": {
"axes": [],
"metric": "custom",
"unit": "raw",
"value": 1400000
},
"start_day": "2024-03-01"
}
Two values per customer, joined
Balance and number of snapshot rows per customer: one layer per join input.
Request
{
"end_day": "2026-07-07",
"query": {
"inputs": [
{
"agg": "last",
"axes": [
"customer"
],
"input": {
"input": {
"entity": "customers",
"kind": "snap-field",
"op": "scan",
"table": "snaps"
},
"op": "where",
"pred": {
"args": [
{
"atom": "d",
"op": "field"
},
{
"op": "const",
"value": 858
}
],
"op": "lte"
}
},
"op": "group",
"value": {
"atom": "pts_bal",
"op": "field"
}
},
{
"agg": "count",
"axes": [
"customer"
],
"input": {
"entity": "customers",
"kind": "snap-field",
"op": "scan",
"table": "snaps"
},
"op": "group",
"value": {
"op": "const",
"value": 1
}
}
],
"mode": "inner",
"op": "join"
}
}
Response
{
"end_day": "2026-07-07",
"result": {
"axes": [
"customer"
],
"cells": [
{
"coords": [
"1086983a00000005"
],
"values": [
100,
1
]
},
{
"coords": [
"1086983a00000006"
],
"values": [
40,
1
]
}
],
"metric": "custom",
"unit": "raw"
},
"start_day": "2024-03-01"
}
Running total per day
Snapshot rows per day, accumulated. The sweep fills in every day of the period.
Request
{
"end_day": "2026-07-07",
"query": {
"axis": "d",
"input": {
"agg": "count",
"axes": [
"d"
],
"input": {
"entity": "customers",
"kind": "snap-field",
"op": "scan",
"table": "snaps"
},
"op": "group",
"value": {
"op": "const",
"value": 1
}
},
"op": "sweep",
"step": "sum"
},
"start_day": "2026-07-05"
}
Response
{
"end_day": "2026-07-07",
"result": {
"axes": [
"day"
],
"cells": [
{
"coords": [
"2026-07-05"
],
"values": [
0
]
},
{
"coords": [
"2026-07-06"
],
"values": [
2
]
},
{
"coords": [
"2026-07-07"
],
"values": [
2
]
}
],
"metric": "custom",
"unit": "raw"
},
"start_day": "2026-07-05"
}
Specific Errors
| Status | Error | Reason & Examples |
|---|---|---|
| 400 | bad_data |
The shop is not on Analytics V4, its analytics data is not built yet, an input is invalid, or the query is wrong, too large or too slow. For example: For example: For example: For example: For example: For example: |
| 429 | too_many_requests |
Analytics is busy: another computation is running for this shop, or six are running on the server, and no turn came up within 10 seconds. Nothing was computed; try again shortly. For example: |
| 429 | slow_down_custom_analytics_queries |
The shop has sent too many custom queries, or its custom queries have cost more than its budget. The message says how many seconds to wait. Nothing was computed. For example: For example: |
Global Errors
| Status | Error | Reason & Examples |
|---|---|---|
| 401 | invalid_token |
The provided authentication token is invalid or has expired. For example: |
| 429 | rate_limit_exceeded |
Your usage is over the rate limit. Ensure that you're not making duplicate calls, and contact our team for a rate limit increase. For example: |