Comment on page

Filters

How you can filter the query results by date or any other available field

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 orders, returns, or carts). 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.
The filter object
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:
{
  "{{query_type}}": { ... },
  "filter": {
    "{{resource_name}}": {
      "date_from": "...",
      "date_to": "...",
      "date_field": "...",
      "{{attribute_name}}": {
        "{{selected_operator}}": ...
      }
​
      // ...
​
    },
    "{{field_name}}": {
      "{{attribute_name}}": {
        "{{selected_operator}}": ...
      },
​
      // ...
​
    },
​
    // ...
​
  }
}
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.
Date filters
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):
Attribute
Type
Required
Description
Default value
Limits
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).
No more than 365 days 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).
No more than 365 days 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 available values for this key depend on the resource you're doing statistics on (see orders, returns, or carts for the related lists).
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).
For performance reasons and to avoid dealing with huge response payloads the time window within which you can request to extract your metrics is limited. The maximum date range is one year (365 days).
Date formats
​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-MM-DDThh:mm:ssTZD
Placeholder
Description
Examples
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
TZD
The time zone designator (Z for UTC, otherwise +hh:mm or -hh:mm)
Z , +02:00, -03:00
Please note that the T appears literally in the string, to indicate the beginning of the time element.
How date_field works
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 orders, returns, carts).
The 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:
Date field
Filtered results
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).
Default filter
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:
{
  "{{query_type}}": { ... },
  "filter": {
    "{{resource_name}}": {
      "date_from": "{{default_from_value}}", // 30 days ago, beginning of day
      "date_to": "{{default_to_value}}", // today, end of day
      "date_field": "current_date"
    }
  }
}
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).
Filter operators
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 orders, returns, and carts). 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 orders, returns, and carts).
Booleans
Boolean attributes can be filtered based on their value (one of true or false). For example: discounted: true
Strings
Attributes of type String can be filtered using the following operators:
Operator
Description
Example
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" ] }
Numbers and Dates
Numeric attributes (Integer or Float) and date-time string formats can be filtered using the following operators:
Operator
Description
Example
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" ] }
Examples
Combining date filters and date breakdown queries
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
Request
Response
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:
curl -g -X POST \
  'https://{{your_domain}}.commercelayer.io/metrics/orders/date_breakdown' \
  -H 'Accept: application/vnd.api.v1+json' \
  -H 'Content-Type: application/vnd.api+json' \
  -H 'Authorization: Bearer {{your_access_token}}' \
  -d '{
    "date_breakdown": {
      "by": "order.placed_at",
      "field": "order.id",
      "interval": "year",
      "operator": "value_count"
    },
    "filter": {
      "order": {
        "date_from": "2021-01-01T00:00:00Z",
        "date_to": "2021-12-31T23:59:00Z",
        "date_field": "placed_at"
      }
    }
  }'
On success, the API responds with a 200 OK status code, returning the aggregated values in the data object and extra information in the meta object:
{
  "data": [
    {
      "date": "2021-01-01T00:00:00.000Z",
      "value": 123456
    }
  ],
  "meta": {
    "type": "date_breakdown",
    "trace_id": "fe571ea2-8a4f-4a5e-bd26-ac54651bb2e4",
    "mode": "test",
    "organization_id": "xYZkjABcde",
    "market_ids": [ "yzXKjYzaCx", "..." ]
  }
}
Request
Response
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:
curl -g -X POST \
  'https://{{your_domain}}.commercelayer.io/metrics/orders/date_breakdown' \
  -H 'Accept: application/vnd.api.v1+json' \
  -H 'Content-Type: application/vnd.api+json' \
  -H 'Authorization: Bearer {{your_access_token}}' \
  -d '{
    "date_breakdown": {
      "by": "order.placed_at",
      "field": "order.id",
      "interval": "year",
      "operator": "value_count"
    },
    "filter": {
      "order": {
        "date_from": "2021-01-01T00:00:00Z",
        "date_to": "2021-12-31T23:59:00Z"
      }
    }
  }'
On success, the API responds with a 200 OK status code, returning the aggregated values in the data object and extra information in the meta object:
{
  "data": [
    {
      "date": "2019-01-01T00:00:00.000Z",
      "value": 3
    },
    {
      "date": "2020-01-01T00:00:00.000Z",
      "value": 1234
    },
    {
      "date": "2021-01-01T00:00:00.000Z",
      "value": 123000
    }
  ],
  "meta": {
    "type": "date_breakdown",
    "trace_id": "fe571ea2-8a4f-4a5e-bd26-ac54651bb2e4",
    "mode": "test",
    "organization_id": "xYZkjABcde",
    "market_ids": [ "yzXKjYzaCx", "..." ]
  }
}
Request
Response
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:
curl -g -X POST \
  'https://{{your_domain}}.commercelayer.io/metrics/orders/date_breakdown' \
  -H 'Accept: application/vnd.api.v1+json' \
  -H 'Content-Type: application/vnd.api+json' \
  -H 'Authorization: Bearer {{your_access_token}}' \
  -d '{
    "date_breakdown": {
      "by": "order.placed_at",
      "field": "order.id",
      "interval": "year",
      "operator": "value_count"
    },
    "filter": {
      "order": {
        "date_from": "2021-01-01T00:00:00Z",
        "date_to": "2021-12-31T23:59:00Z",
        "date_field": "cancelled_at"
      }
    }
  }'
On success, the API responds with a 200 OK status code, returning the aggregated values in the data object and extra information in the meta object:
{
  "data": [
    {
      "date": "2019-01-01T00:00:00.000Z",
      "value": 1
    },
    {
      "date": "2020-01-01T00:00:00.000Z",
      "value": 1123
    },
    {
      "date": "2021-01-01T00:00:00.000Z",
      "value": 23456
    }
  ],
  "meta": {
    "type": "date_breakdown",
    "trace_id": "fe571ea2-8a4f-4a5e-bd26-ac54651bb2e4",
    "mode": "test",
    "organization_id": "xYZkjABcde",
    "market_ids": [ "yzXKjYzaCx", "..." ]
  }
}
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).
Applying filters on the field named as the resource
Request
Response
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:
curl -g -X POST \
  'https://{{your_domain}}.commercelayer.io/metrics/orders/stats' \
  -H 'Accept: application/vnd.api.v1+json' \
  -H 'Content-Type: application/vnd.api+json' \
  -H 'Authorization: Bearer {{your_access_token}}' \
  -d '{
    "stats": {
      "field": "order.total_amount_with_taxes",
      "operator": "stats"
    },
    "filter": {
      "order": {
        "date_from": "2021-01-01T00:00:00Z",
        "date_to": "2021-12-31T23:59:00Z",
        "date_field": "placed_at",
        "currency_codes": {
          "in": [ "EUR" ]
        },
        "total_amount_with_taxes": {
          "gt_lt": [ 1000, 10000 ]
        },
        "discounted": true
      }
    }
  }'
On success, the API responds with a 200 OK status code, returning the aggregated values in the data object and extra information in the meta object:
{
  "data": {
    "value": {
      "count": 67,
      "min": 1002.9,
      "max": 9996.0,
      "avg": 1900.54,
      "sum": 127336.07
    }
  },
  "meta": {
    "type": "stats",
    "trace_id": "fe571ea2-8a4f-4a5e-bd26-ac54651bb2e4",
    "mode": "test",
    "organization_id": "xYZkjABcde",
    "market_ids": [ "yzXKjYzaCx", "..." ]
  }
}
Appling filters on other fields
Request
Response
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:
curl -g -X POST \
  'https://{{your_domain}}.commercelayer.io/metrics/orders/breakdown' \
  -H 'Accept: application/vnd.api.v1+json' \
  -H 'Content-Type: application/vnd.api+json' \
  -H 'Authorization: Bearer {{your_access_token}}' \
  -d '{
    "breakdown": {
      "by": "market.name",
      "field": "order.total_amount_with_taxes",
      "operator": "max",
      "sort": "desc",
      "limit": 3
    },
    "filter": {
      "order": {
        "date_from": "2021-01-01T00:00:00Z",
        "date_to": "2021-12-31T23:59:00Z",
        "date_field": "cancelled_at",
        "currency_codes": {
          "in": [ "USD" ]
        },
        "refunded": true
      },
      "shipping_address": {
        "country_codes": {
          "in": [ "CA", "US" ]
        }
      },
      "refunds" : {
        "amount": {
          "gt": 10000
        }
      }
    }
  }'
On success, the API responds with a 200 OK status code, returning the aggregated values in the data object and extra information in the meta object:
{
  "data": {
    "market.name": [
      {
        "label": "New York",
        "value": 142550.0
      },
      {
        "label": "Boston",
        "value": 44740.0
      },
      {
        "label": "Miami",
        "value": 31410.0
      }
    ]
  },
  "meta": {
    "type": "breakdown",
    "trace_id": "fe571ea2-8a4f-4a5e-bd26-ac54651bb2e4",
    "mode": "test",
    "organization_id": "xYZkjABcde",
    "market_ids": [ "yzXKjYzaCx", "..." ]
  }
}
Applying filters on nested fields (line item options)
Request
Response
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:
curl -g -X POST \
  'https://{{your_domain}}.commercelayer.io/metrics/orders/search' \
  -H 'Accept: application/vnd.api.v1+json' \
  -H 'Content-Type: application/vnd.api+json' \
  -H 'Authorization: Bearer {{your_access_token}}' \
  -d '{
    "search": {
      "limit": 2,
      "sort": "desc",
      "sort_by": "order.approved_at",
      "fields": [
        "order.id",
        "order.placed_at",
        "order.approved_at",
        "market.name"
        "line_items.options.*"
      ]
    },
    "filter": {
      "order": {
        "date_from": "2021-01-01T00:00:00Z",
        "date_to": "2021-12-31T23:59:00Z",
        "date_field": "placed_at"
      },
      "line_items": {
        "options": {
          "quantity": {
            "gt": 0
          }
        }
      }
    }
  }'
On success, the API responds with a 200 OK status code, returning the requested fields in the data object and additional information alongside pagination in the meta object:
{
  "data": [
    {
      "id": "waMAhOZdzO",
      "placed_at": "2021-12-17T20:16:48.629Z",
      "approved_at": "2022-01-03T15:17:56.749Z",
      "market": {
        "name": "UK"
      },
      "line_items": [
        {
          "options": [
            {
              "quantity": 3,
              "total_amount": 30.0,
              "name": "Sticker",
              "id": "ymnziklnAo",
              "unit_amount": 10.0
            }
          ]
        },
        {
          "options": [
            {
              "quantity": 1,
              "total_amount": 20.0,
              "name": "Engraving",
              "id": "yrMViYqjQN",
              "unit_amount": 20.0
            }
          ]
        }
      ]
    },
    {
      "id": "NZrQhXryRy",
      "placed_at": "2021-12-23T14:50:52.185Z",
      "approved_at": "2022-01-03T09:52:32.591Z",
      "market": {
        "name": "Europe"
      },
      "line_items": [
        {
          "options": [
            {
              "quantity": 2,
              "total_amount": 40.0,
              "name": "Engraving",
              "id": "ynQziEkgdN",
              "unit_amount": 20.0
            }
          ]
        }
      ]
    }
  ],
  "meta": {
    "pagination": {
      "record_count": 12345,
      "cursor": "LS0tCi0gJzIwMjEtMTItMzFUMjM6NDE6M=="
    },
    "type": "search",
    "trace_id": "fe571ea2-8a4f-4a5e-bd26-ac54651bb2e4",
    "mode": "test",
    "organization_id": "xYZkjABcde",
    "market_ids": [ "yzXKjYzaCx", "..." ]
  }
}

Attribute	Type	Description	Default value	Limits
`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`).	No more than 365 days 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`).	No more than 365 days 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 available values for this key depend on the resource you're doing statistics on (see orders, returns, or carts for the related lists).

Placeholder	Description	Examples
`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`
`TZD`	The time zone designator (`Z` for UTC, otherwise `+hh:mm` or `-hh:mm`)	`Z` , `+02:00`, `-03:00`

Date field	Filtered results
`current_date`	Order 1, Order 2, Order 3
`placed_at`	Order 2
`created_at`	Order 2
`updated_at`
`approved_at`	Order 3

Operator	Description	Example
`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" ] }`

Operator	Description	Example
`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" ] }`

Next - Getting started

Analysis

Last modified 9mo ago