Merchant Center Query Language (MCQL) is a query language, similar to SQL.
You can use MCQL with the
[Merchant Reports API](https://developers.google.com/merchant/api/reference/rest/reports_v1/accounts.reports/search)
to retrieve performance data on your products and the market they're competing
in.

## Grammar

Here's the MCQL grammar reference (in regular expression notation):

    Query            -> SelectClause FromClause? WhereClause? OrderByClause? LimitClause?
    SelectClause     -> SELECT FieldName (, FieldName)*
    FromClause       -> FROM TableName
    WhereClause      -> WHERE Condition (AND Condition)*
    OrderByClause    -> ORDER BY Ordering (, Ordering)*
    LimitClause      -> LIMIT PositiveInteger

    Condition        -> FieldName Operator Value | FieldName BETWEEN Value AND Value
    Operator         -> = | != | > | >= | < | <= | <> | IN | NOT IN |
                        CONTAINS ANY | CONTAINS ALL | CONTAINS NONE | DURING |
                        LIKE | NOT LIKE | REGEXP_MATCH | NOT REGEXP_MATCH
    Value            -> Number | NumberList | String | StringList | Function
    Ordering         -> FieldName (ASC | DESC)?

    FieldName        -> [a-z] ([a-zA-Z0-9._])*
    TableName        -> [A-Z] ([a-zA-Z_])*

    StringList       -> ( String (, String)* )
    NumberList       -> ( Number (, Number)* )

    PositiveInteger  -> [1-9] ([0-9])*
    Number           -> -? [0-9]+ (. [0-9] [0-9]*)?
    String           -> (' Char* ') | (" Char* ")
    Function         -> LAST_14_DAYS | LAST_30_DAYS | LAST_7_DAYS |
                        LAST_BUSINESS_WEEK | LAST_MONTH | LAST_WEEK_MON_SUN |
                        LAST_WEEK_SUN_SAT | THIS_MONTH | THIS_WEEK_MON_TODAY |
                        THIS_WEEK_SUN_TODAY | TODAY | YESTERDAY

You can use the following symbols:

- `?`indicates an optional element.
- `*` means zero or more; `+` means one or more.
- `(xxxxxx)` indicates a grouping.
- `[a-z0-9]` signifies character ranges.
- `|` stands for "or".

> [!IMPORTANT]
> **Key Point:** The `REGEXP_MATCH` operator uses [RE2 syntax](https://github.com/google/re2/wiki/Syntax).

## Case-sensitivity

Most MCQL operators are case-sensitive:

| Operators | Case Sensitivity |
|---|---|
| = or != | Case sensitive |
| (NOT) IN | Case sensitive |
| (NOT) LIKE | Case sensitive |
| CONTAINS (...) | Case sensitive |
| REGEXP_MATCH | Optionally Both |

> [!IMPORTANT]
> **Key Point:** `REGEXP_MATCH` and `NOT REGEXP_MATCH` are case-sensitive by default, but you can include `(?i)` as described in the [RE2
> syntax](https://github.com/google/re2/wiki/Syntax) to make them case-insensitive.

## Clauses

Here are the clauses you can query with the MCQL:

### SELECT

The `SELECT`clause takes a comma-separated list of fields to retrieve.

> [!IMPORTANT]
> **Important:** The `SELECT` clause is required for all queries. You must select at least one metrics field in [performance
> queries](https://developers.google.com/merchant/api/guides/reports/evaluate-products#measure_performance).

You can select any field from the view you're using. Here are some examples of
fields you can select:

- [Metrics](https://developers.google.com/merchant/api/guides/reports/performance-reports#metrics)

  - `impressions`
  - `clicks`
- [Segments](https://developers.google.com/merchant/api/guides/reports/performance-reports#segments)

  - `date`
  - `marketing_method`

You can query multiple field types in a single request. Here's an example:

    SELECT
      date,
      marketing_method,
      impressions,
      clicks
    FROM product_performance_view
    WHERE date BETWEEN '2023-08-01' AND '2023-08-31'

### FROM

The `FROM` clause specifies the table to fetch data from in the request. You can
only specify one field in the `FROM` clause. The `FROM` clause is required for
all queries.

### WHERE

Use the `WHERE` clause to filter the data for your request. The `WHERE` clause
is required for [performance
queries](https://developers.google.com/merchant/api/guides/reports/evaluate-products#measure_performance).
You can filter by all
[segments](https://developers.google.com/merchant/api/guides/reports/performance-reports#segments) fields,
and by additional
[metrics](https://developers.google.com/merchant/api/guides/reports/performance-reports#metrics) fields if
you specify them in the `SELECT` clause.

Here's an example that uses `WHERE` to return impressions from only a specified
time period (the month of August):

    SELECT offer_id, impressions
    FROM product_performance_view
    WHERE date BETWEEN '2023-08-01' AND '2023-08-31'

You can filter by multiple conditions in a single query with the `AND` operator.
Use `AND` between complete conditions, for example: `WHERE marketing_method !=
"ADS" AND marketing_method != "ORGANIC"`. You can't use `AND` between values in
a single condition, for example: `WHERE marketing_method != "ADS" AND
"ORGANIC"`.

Here's an example that returns the number of clicks per offer, where there are
more than 100 clicks, for the `ADS` marketing method, during the month of
August:

    SELECT offer_id, clicks
    FROM product_performance_view
    WHERE clicks > 100
      AND marketing_method = 'ADS'
      AND date BETWEEN '2023-08-01' AND '2023-08-31'

The `WHERE` clause doesn't support `OR`. Operators are case-sensitive. For a
complete list of operators, see [grammar](https://developers.google.com/merchant/api/guides/reports/query-language#grammar).

### ORDER BY (optional)

The `ORDER BY` clause lets you retrieve results in a specified order.

Specify the order by a `field_name`, then `ASC` or `DESC`. You can only order by
fields specified in the `SELECT` clause of your query. `ORDER BY` defaults to
`ASC` if you don't specify.

The following query orders the returned rows by number of clicks, from highest
to lowest:

    SELECT offer_id, clicks
    FROM product_performance_view
    WHERE date BETWEEN '2023-08-01' AND '2023-08-31'
    ORDER BY clicks DESC

You can specify multiple fields in the `ORDER BY` clause using a comma-separated
list.

For example, the results of the following query are sorted in ascending order by
`offer_id`, then in descending order by number of impressions, then in
descending order by number of clicks:

    SELECT offer_id, impressions, clicks
    FROM product_performance_view
    WHERE date BETWEEN '2020-08-01' AND '2020-08-31'
    ORDER BY
      offer_id,
      impressions DESC,
      clicks DESC

### LIMIT

The `LIMIT` clause lets you specify the number of results to return.

Here's an example that returns only 50 results, even if more rows are available.

    SELECT offer_id, impressions
    FROM product_performance_view
    WHERE date BETWEEN '2020-08-01' AND '2020-08-31'
    ORDER BY impressions DESC
    LIMIT 50

Use the `pageSize` field to handle long lists of results.

## Date ranges

The MCQL lets you specify a custom or relative date range. You must use the
`WHERE` clause to specify a `date` range for all [performance queries](https://developers.google.com/merchant/api/reference/rest/reports_v1/accounts.reports/search#productperformanceview).

### Custom

You can specify ISO 8601(YYYY-MM-DD) dates in the following formats:

- `date BETWEEN '2021-01-01' AND '2021-01-31'`
- `date >= '2021-01-01' AND date <= '2021-01-31'`

You can define date strings with either single quotes (`'`) or double quotes
(`"`).

### Relative

You can specify a relative date range, such as `LAST_30_DAYS` or `THIS_MONTH`,
by using `DURING` instead of `BETWEEN` and `AND`:

    WHERE date DURING LAST_30_DAYS

See [grammar](https://developers.google.com/merchant/api/guides/reports/query-language#grammar) for the full list of available relative date ranges.