Analytics API

Read Call

QueryAnalyticsCustom3

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

  1. Call DescribeAnalytics3 with custom_tables: true. It lists every stored table with its columns and a ready scan to paste, plus launch_day, which you need for day numbers.
  2. Find a metric close to what you want and call ExplainAnalytics3 for it, or DescribeAnalytics3 with definitions: true. Both return working queries: steps[].query and metrics[].query.
  3. 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-field tables hold one row per entity: for example a customer's enrollment day and first order day.
  • snap-field tables 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.
  • event tables 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 is d. To filter by a date, subtract launch_day from 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 from custom_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 with field; over a table it reads values with layer and coordinates with coord.
  • {"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. Omit axes for 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. inner keeps cells present in every input, outer keeps cells present in any (missing values read as 0), anti keeps 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. Optional axes add axes computed the same way.
  • {"op":"sweep","input":TABLE,"step":"sum"|"diff","axis":"d"}: walks along an axis in order. sum gives running totals and fills in every day of the period; diff gives 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) or point_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, or take one or more; sub, div, ceil, eq, ne, gt, gte, lt, lte take two; between (value, low, high, both ends included) and mul-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_limit seconds, 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_requests if 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

  • query object required

    The query: a tree of operations whose outermost one yields a table.

    See the reference above. Start from steps[].query of ExplainAnalytics3, metrics[].query of DescribeAnalytics3, or a custom_tables[].scan.

  • start_day date optional

    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 event scans and the days a sweep fills in.

  • end_day date optional

    Last day to include, in the shop's time zone.

    Defaults to today. Later days count as today.

  • market string optional

    Limit the computation to one market: a key from markets in DescribeAnalytics3.

  • campaign string optional

    Limit the computation to one campaign: a name from campaigns in DescribeAnalytics3, or the campaign's ID.

  • time_limit integer optional

    How 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.

  • limit integer optional

    Most cells to return. Defaults to 1000; at most 10,000.

Output

  • start_day date required

    First day actually computed.

  • end_day date required

    Last day actually computed.

  • result object required

    The table: axes, cells with coords and raw values (or value for a single number), and truncated.

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: Analytics V4 is not available for this shop

For example: invalid query: unsupported op "grup". Take a query from ExplainAnalytics3 steps or DescribeAnalytics3 definitions, and change it

For example: the query failed: derive metric custom: unknown table: customers.nope

For example: the query failed: cube axis missing

For example: the computation was stopped: it needs more memory than the 5120 MB allowed. Narrow the days or the filters, or ask for fewer metrics at once.

For example: the computation was stopped: it ran longer than the 30s allowed. Narrow the days or the filters, or ask for fewer metrics at once. time_limit can raise the time limit, up to 2m0s.

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: analytics is busy: another computation is running for this shop, or the server is at its limit. Nothing was computed after waiting 10s. Try again in about 10 seconds, and send calls for one shop one at a time

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: too many custom analytics queries for this shop: 5 at once, then 5 per minute. Wait about 12 seconds before the next one. QueryAnalytics3 is not affected

For example: this shop's custom analytics queries have cost more than its budget allows. Wait about 36 seconds before the next one. A query is charged after it runs for the seconds it computed, at least one; 6 seconds of waiting pay off one second. Faster queries (fewer days, a filter, one grouping axis) cost less. QueryAnalytics3 is not affected

Global Errors

Status Error Reason & Examples
401 invalid_token

The provided authentication token is invalid or has expired.

For example: The token has expired

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: You are over the read limit per second for this customer

Previous
QueryAnalytics3