Filters
How you can filter the query results by date or any other available field
Last updated
How you can filter the query results by date or any other available field
Last updated
Commerce Layer Metrics API lets you filter any type of query by date or any other available field. The fields to which you can apply filters depend on the specific resource you want to do statistics on (see the related tables to check the full list of filterable fields for , , or ). Adding a filter to your request enables you to narrow and fine-tune the results of the query according to your needs and use cases.
The examples in the following pages will be focused on the filter part of the request. Simple queries on the order resource will be used (we suggest you read the related section first, to learn more about the different types of query works) and no meta options will be defined (i.e. the response won't include the request payload). See the use cases section for more complex combinations of queries and filters.
Metrics API filters apply to specific fields of the resource. For each field you want to use for filtering your data, you need to specify the attribute(s) involved in the process and (for each attribute) the operator that will be used to actually filter the results. Some attributes of the field named as the resource itself — e.g. {{resource_name}} in the examples below — can be also used to set the time range of the filter.
The filter object you need to add to the request payload is a JSON object made of a set of key/value pairs where the keys are the filterable field. Each of them is a JSON object made of a set of key/value pairs where the keys are the field's attribute(s).
Apart from boolean attributes (which can simply be true or false) all attributes keys are objects made of a single key/value pair where the key is the selected operator.
Summing up, the filter object is generically structured as follows:
Both the filter object and all the field objects it contains are optional, but cannot be empty (i.e. if you want to use a filter you need to specify at least one field, and for that field at least one attribute with the related operator). If no filter object is defined a default filter will be used.
To extract your metrics over a specific date range you can use three attributes of the field named as the resource on which you're doing statistics (order, return, or cart):
date_from
String
The lower limit of the date and time range used to filter the collected records (required if you specified date_to).
30 days before the current day, beginning of day (e.g. 2021-08-19T00:00:00Z).
Must be before date_to.
date_to
String
The upper limit of the date and time range used to filter the collected records (required if you specified date_from).
The current day, end of day (e.g. 2022-09-19T23:59:59Z).
Must be after date_from.
date_field
String
The date field where to apply the date and time range filter (learn how it works).
current_date
The three attributes above are all optional, meaning that you can omit them all. The only constraint is that date_from and date_to must be set together (i.e. you cannot use the default value for one of the two and a custom value for the other, otherwise the API will return an error — see example).
The time window within which you can request to extract your metrics is not limited. Anyway, for performance reasons and to avoid dealing with huge response payloads we recommend not to search over huge lapses of time.
String parameters with format date-time follow the ISO 8601 standard, according to the complete date plus hours, minutes, and seconds format (e.g. 2018-01-01T19:20:30Z, or 2018-01-01T19:20:30+02:00):
YYYY
The four digits representing the year.
2018
MM
The two digits representing the month of the year.
01 for January, etc.
DD
The two digits representing the day of the month.
01 through 28, 29, 30, or 31 (based on the related month)
hh
The two digits representing the hour of the day.
00 through 23 (AM/PM not allowed)
mm
The two digits representing the minute of the hour.
00 through 59
ss
The two digits representing the second of the minute.
00 through 59
Please note that the T appears literally in the string, to indicate the beginning of the time element.
date_field worksThe default value current_date indicates the date and time of the latest resource status change, regardless of its previous and current status.
Let's consider this simple example where we have 3 orders:
Order 1 — created and placed in the year 2020, then cancelled in 2021.
Order 2 — created and placed in the year 2021.
Order 3 — created in the year 2019, updated and placed in 2020, then approved in 2021.
Let's assume we want to filter our query on a time range corresponding to the whole year 2021 (i.e.: "date_from": "2021-01-01T00:00:00Z" and "date_to": "2021-12-31T23:59:00Z"). These will be the order(s) that will result after the filtering, based on different values of the date_field attribute:
current_date
Order 1, Order 2, Order 3
placed_at
Order 2
created_at
Order 2
updated_at
approved_at
Order 3
Properly using the date_filed attribute in combination with the interval key of a date breakdown query gives you almost endless possibilities on how you can extract data from specific time windows of your order, return, or cart history (see examples).
Adding a custom filter to the request payload is optional (even if strongly recommended to get the most out of the Metrics API). If the filter section is omitted a default filter will be used to set the date range of the query results. Given what was mentioned above, the default filter will be structured as follows:
Please note that you can always check any default value used to perform a query by inspecting the request payload in the response. You just need to use the meta object and set the payload attribute to true (more info here).
Boolean attributes can be filtered based on their value (one of true or false). For example: discounted: true
Attributes of type String can be filtered using the following operators:
in
The attribute value matches any of the specified array's values.
"country_codes": { "in": [ "US", "UK, IT"] }
not_in
The attribute value matches none of the specified array's values.
"ids": { "not_in": [ "yzXKjYzaCx", "JxYabZKcAw" ] }
Numeric attributes (Integer or Float) and date-time string formats can be filtered using the following operators:
eq
The attribute value is equal to the specified value.
"skus_count": { "eq": 3 }
ne
The attribute value is not equal to the specified value.
"placed_day_of_week": { "ne": 6 }
gt
The attribute value is greater than the specified value.
"seconds_in_draft": { "gt": 0 }
gte
The attribute value is greater than (or equal to) the specified value.
"total_amount_with_taxes": { "gte": 10000 }
lt
The attribute value is lower than the specified value.
"shipments_count": { "lt": 2 }
lte
The attribute value is lower than (or equal to) the specified value.
"gift_card_amount": { "lte": 5000 }
gt_lt
The attribute value is greater than the first value of the specified array and lower than the second.
"discount_amount": { "gt_lt": [ 1000, 3000 ] }
gte_lte
The attribute value is greater than (or equal to) the first value of the specified array and lower than (or equal to) the second.
"placed_at": { "gte_lte": [ "2018-01-01T00:00:00+02:00", "2018-03-31T23:59:00+02:00" ] }
gte_lt
The attribute value is greater than (or equal to) the first value of the specified array and lower than the second.
"discount_amount": { "gte_lt": [ 7000, 10000 ] }
gt_lte
The attribute value is greater than the first value of the specified array and lower than (or equal to) the second.
"created_at": { "gt_lte": [ "2018-01-31T23:59:00Z", "2018-03-31T23:59:00Z" ] }
query
The query to be used for the full-text search.
"query": "(*@gmail.com | *@hotmail.com) + placed"
The query value can be a simple string or a more complex one, composed according to the following rules:
+ signifies AND operation.
| signifies OR operation.
- negates a single term.
"" wraps a number of terms to signify a phrase for searching.
* at the beginning of a term signifies a suffix query.
* at the end of a term signifies a prefix query.
( and ) signifies precedence.
~N after a word signifies edit distance (fuzziness — i.e. allows you to specify how many edits between the query and a term in a document are allowed).
~N after a phrase signifies slop amount (i.e. allows you to specify the number of positions by which the terms in the query can be transposed to match a document).
The default rule is AND, meaning that terms separated by a simple space are searched according to the AND logic (e.g. "query": "Grafton Street" is the same as "query": "Grafton + Street").
Let's consider the following 3 examples, where we will:
request a date breakdown to get the total count of the placed orders by year
filter the result on the whole year 2021 time window
use different values for the date_field attribute
The following request performs a date breakdown query to get the total count of placed orders per year, filtered by date on the year 2021 time window considering the placed_at date field:
The following request performs a date breakdown query to get the total count of placed orders per year, filtered by date on the year 2021 time window using the default value — current_date — as the date field:
The following request performs a date breakdown query to get the total count of placed orders per year, filtered by date on the year 2021 time window considering the cancelled_at date field:
As you can see from the responses:
"date_field": "placed_at" — since the order's date_field attribute in the filter and the by key of the query refer to the same status change, the first request returns the actual total count of placed orders in the year 2021.
"date_field": "current_date" — the second request returns the breakdown by year of the placed orders that have changed their status in the year 2021 (i.e. 123000 orders were actually placed in 2021, 1234 orders were placed in 2020 and then changed their status — e.g. they were cancelled, or approved — in 2021, 3 orders were placed in 2019 and then changed their status in 2021).
"date_field": "cancelled_at" — the third request returns the breakdown by year of the placed orders that have been cancelled in the year 2021 (i.e. 23456 orders were placed and then cancelled in 2021, 1123 orders were placed in 2020 and then cancelled in 2021, 1 orders were placed in 2019 and then cancelled in 2021).
The following request performs a stats query to get the stats about the orders placed in the year 2021 which have a total amount between 10 and 100 euros and to which a discount was applied:
The following request performs a breakdown query to get the maximum total amount among the orders cancelled in the year 2021 that were to be shipped to Canada and the US and were refunded with an amount greater than $ 100:
The following request performs a search query to get specific information about the two most recently approved orders (among the ones placed in the year 2021) whose line items have some line item option:
The available values for this key depend on the resource you're doing statistics on (see , , or for the related lists).
When you want to narrow the results of a query to a certain date and time range you need to specify which is the date field that the filter must consider for the computation. To do that, set the date_field to one of the available values (or omit it if you want to use the default one). The available values for the date_field attribute are strings referring to the resource's status changes (e.g. created_at, placed_at, received_at, etc.) and depend on the resource on which you're doing statistics (see , , ).
The valid operators you can use when filtering your queries depend on the attribute of the filterable field on which you want to compute the calculation. They can be grouped based on the type of the attribute itself (see all the fields and attributes for , , and ). You can find here below the complete list of the available operators for the Metrics API queries (see the related tables to check the subset of valid operators by field and attribute for , , and ).
aggregated_details is a special text field that contains all the or data, on which you can perform a full-text search using the dedicated query operator. The aggregated details text is composed of all the string attributes of the order (i.e. all attributes except for booleans and dates) separated by spaces, for example: