Getting started
Metrics API
Other APIs
Changelog

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):

AttributeTypeRequiredDescriptionDefault valueLimits

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

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.

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
PlaceholderDescriptionExamples

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 fieldFiltered 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:

OperatorDescriptionExample

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:

OperatorDescriptionExample

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" ] }

Aggregated details

aggregated_details is a special text field that contains all the order or cart 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:

"aggregated_details": "US USD unfulfilled 123abc45-e6fg-7hi8-90c3-677da0bd6ad1 en authorized placed NlalhzJJzb Gift card: €100,00 kdPgteerDk gift_cards NkvGBUyeor Stripe Payment vBoqtOOZJN payment_methods wmBvQsARml Shipment #1234567/S/002 yZYjtllQDN shipments yBmNCjGVrP Shipment #1234567/S/001 vzbZtLLMnv eMWdCDoXXy Black Canvas with White Logo (18x24) vPrYtRRQQy CANVASAU000000FFFFFF1824 skus znOpOSAQeZ White Men T-Shirt with Black Logo (XL) yKlZtxxBVk TSHIRTMMFFFFFF000000XLXX KWkGgSgOMW ABCDEFGHIJKLMNOPQRSTUVWYXZ New York John Doe 5th Avenue 123, +1 212-555-0176 NY 10001 brQLuVxxqW deJOugnnZW stripe_payments card visa JkAdBhNGjQ john@example.com repeat USA market_1 CLI dlQbPhNNop WXlEOFrjnr upcoming VNoxGFBywb Merchandising shipping_category_1 DEqjzFGqOn Standard Shipping shipping_method_1 ZnYqXuqPnY EU Warehouse stock_location_1 QkxoeuXZGW US Warehouse stock_location_2"
OperatorDescriptionExample

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").

Examples

Combining date filters and date breakdown queries

Let's consider the following 3 examples, where we will:

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"
      }
    }
  }'

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"
      }
    }
  }'

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"
      }
    }
  }'

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

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
      }
    }
  }'

Appling filters on other fields

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
        }
      }
    }
  }'

Applying filters on nested fields (line item options)

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
          }
        }
      }
    }
  }'
PreviousSearchNextAnalysis

Last updated