Query Analytics Data

The query_analytics_data tool reads rows from one analytics table: a Datamart, a Data Source, a Rollup, or a table of a Model Object. You can select columns, filter and sort the rows, aggregate values grouped by columns, and join other analytics tables. The tool returns up to 500 rows per call. To find the tables and their columns, use the List Tables tool and the Get Table Fields tool.

Tool Name – query_analytics_data

Title – Query Analytics Data

Type – Private

Access Profile – Operator

Permissions

The tool is available to the Operator access profile when tool access control is on. The query_analytics_data tool uses your Pricefx permissions. Pricefx applies your permissions to every query, so the tool returns only the rows that you have permission to view.

Parameters

Parameter

Required

Description

kind

Yes

The kind of analytics table: datamart, dataSource, rollup, or modelTable.

table_id

Yes

The first identifier of the table, as the list_tables tool returns it in tableId: the unique name of the Datamart or Data Source, the label of the Rollup, or the unique name of the Model Object.

table_id_2

Only for modelTable

The name of the Model Object table, as the list_tables tool returns it in tableId2. Empty for every other kind.

columns

Yes

The exact column names to return, as the get_table_fields tool returns them. Every column in filters must also be in columns, in aggregates[].column, or be a joined column. When aggregates is set, columns become the group-by columns. In that case columns can be empty ([]) to return one grand-total row.

filters

No

A list of filter conditions. Each condition is an object: {column, operator, value}. The tool combines all conditions with AND.

Default: No filter.

filters[].column

Yes, for each filter

The column to filter on.

filters[].operator

Yes, for each filter

The comparison: equals, notEqual, greaterThan, greaterOrEqual, lessThan, lessOrEqual, inSet, notInSet, like, ilike, notLike, or notIlike. The like operators match a text pattern: % matches any number of characters and _ matches one character. The ilike and notIlike operators ignore case. To filter a range, use two filters on the same column: greaterOrEqual and lessOrEqual.

filters[].value

Yes, for each filter

The comparison value. Use a non-empty list with inSet or notInSet. Use a single text, number, or true/false value with every other operator.

sort_by

No

One column to sort the result by: one of columns, an aggregate result column, or, without aggregation, a joined column. Add - before the name to sort in descending order, for example -Revenue. Ascending order puts empty values first. Descending order puts empty values last. Default: No specific order.

max_rows

No

Maximum number of rows to return, from 1 to 500. When aggregating, the limit applies to the number of groups. The tool changes a value below 1 to 1 and a value above 500 to 500.

Default: 50.

aggregates

No

A list of aggregations, grouped by columns. Each entry is an object: {column, method, alias}. Each entry adds one column to the result. Default: No aggregation.

aggregates[].column

Yes, except for COUNT_ALL

The column to aggregate. The column must not also be in columns.

aggregates[].method

Yes, for each aggregate

The aggregation method: SUM, AVG, MIN, MAX, COUNT_ALL (counts every row, including rows with empty values), COUNT_NON_NULL (counts rows where the column has a value), or COUNT_DISTINCT_NON_NULL (counts the different values of the column).

aggregates[].alias

No

The name of the result column.

Default: {method}_{column}, for example SUM_Revenue. A COUNT_ALL without a column is named COUNT_ALL.

joins

No

A list of other analytics tables to join, applied in the listed order.

Default: No join.

joins[].kind

Yes, for each join

The kind of the joined table: datamart, dataSource, rollup, or modelTable.

joins[].table_id

Yes, for each join

The first identifier of the joined table.

joins[].table_id_2

Only for modelTable

The second identifier of the joined table.

joins[].type

Yes, for each join

INNER keeps only rows with a match. LEFT_OUTER keeps all rows and leaves the joined columns empty when there is no match.

joins[].keys

Yes, for each join

One or more {left, right} column pairs. left is a column that exists before this join: one of columns, or a joined column from an earlier join. right is a column of the joined table. With several pairs, all of them must match.

joins[].columns

Yes, for each join

The columns of the joined table to add to the result. Each entry is an object: {column, alias}. alias is the output name, by default the column name. Can be empty ([]) to use the join only to keep or drop rows.

Output

The query_analytics_data tool returns an object with the following fields.

Field

Description

kind

The kind of the queried table.

tableId

The first identifier of the queried table.

tableId2

The second identifier of the queried table. Empty when the kind needs none.

columns

The columns in each row, in order. When aggregating, the group-by columns come first, followed by the aggregate result columns.

rows

The matching rows. Each row contains the values keyed by column name.

rowCount

The number of rows returned.

maxRows

The row limit that the tool applied, after any change to the requested value.

appliedFilters

The filter conditions that the tool applied.

aggregates

The aggregations that the tool applied: {column, method, result_column}. result_column is the column in columns and rows that holds the aggregated value.

joins

The joins that the tool applied, in order: {kind, tableId, tableId2, type, keys, columns}. Each entry in columns shows the output name that was used.

truncated

true when rowCount equals maxRows. More matching rows (or groups, when aggregating) can exist beyond the row limit.

Behavior and Limitations

  • The query_analytics_data tool reads one analytics table per call and can join other analytics tables to it.

  • The query_analytics_data tool joins only Datamarts, Data Sources, Rollups, and Model Object tables. To read Products, Customers, Sellers, or their extensions, use the query_business_data tool. Analytics tables and business tables are stored in different databases and cannot be joined.

  • Joins match rows by equal column values only. The tool supports INNER and LEFT_OUTER joins.

  • When one row matches several rows in a joined table, the result has one row for each match. A SUM can then count the same value more than once.

  • The tool uses a join key column that is not in columns for the join, but does not return it.

  • The tool applies filters after the joins and before aggregation. You cannot filter on an aggregated value. To find the top or bottom groups, sort by the aggregate result column instead.

  • The tool combines filter conditions with AND only. A condition that matches one of several values uses inSet.

  • max_rows limits the number of rows after the joins and aggregation.

  • A filter can fail when the type of the filter value does not match the type of the column. The get_table_fields tool shows the type of each column.

Errors

Situation

Error Message

kind is not an analytics table kind

Unsupported table kind 'X'. Please use one of: Y.

An identifier is given for a kind that does not take it

Table kind 'X' takes only N identifier(s): Y.

A required identifier is empty

Table kind 'X' needs Y.

A joins[].kind is not an analytics table kind, for example products

Unsupported table kind to join 'X'. Please use one of: Y.

A join has no keys

List should have at least 1 item after validation, not 0

Two joined columns have the same output name

Duplicate joined result column(s): [X]. Each joined column across all `joins` needs a distinct result column — set `alias` on the conflicting one(s) to disambiguate.

A filters[].column is not in columns, aggregates[].column, or the joined columns

[X] used in `filters` but missing from `columns`. Add them to `columns` to filter on them. The message also names aggregate columns and joined columns when the request uses them.

The sort_by column is not in columns or the result columns

'X' used in `sort_by` but missing from `columns`. The message also names the aggregate result columns when the request uses them.

An inSet or notInSet filter has no list or an empty list

'X' on 'Y' needs a non-empty list of values, got Z.

A filter with any other operator has a list or no value

'X' on 'Y' needs a single value, got Z.

An aggregation uses a method other than COUNT_ALL and has no column

aggregate method 'X' requires a column. Only COUNT_ALL can be used without one.

The same column is in both columns and aggregates[].column

'X' is both an aggregate column and one of `columns`. `columns` become the group-by dimensions when aggregating — remove it from `columns`.

Two aggregations have the same result column name

Duplicate aggregate result column(s): [X]. Each entry in `aggregates` needs a distinct result column — set `alias` to pick one explicitly.

An aggregate result column name matches a name in columns

Aggregate result column(s) [X] collide with `columns` of the same name. Set `alias` on the conflicting aggregate to a different name.

Pricefx rejects the query, for example because a column does not exist or a filter value has the wrong type

Query API rejected the request: X