Merchant Center Query Language

Merchant Center Query Language (MCQL) is a query language, similar to SQL. You can use MCQL with the Merchant Reports API 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".

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

Clauses

Here are the clauses you can query with the MCQL:

SELECT

The SELECTclause takes a comma-separated list of fields to retrieve.

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

Metrics
- impressions
- clicks
Segments
- date
- marketingMethod

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

SELECT
  date,
  marketingMethod,
  impressions,
  clicks
FROM ProductPerformanceView
WHERE date BETWEEN '2020-08-01' AND '2020-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. You can filter by all segments fields, and by additional 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 offerId, impressions
FROM ProductPerformanceView
WHERE date BETWEEN '2020-08-01' AND '2020-08-31'

You can filter by multiple conditions in a single query with the AND operator. Use AND between complete conditions, for example: WHERE marketingMethod != "ADS" AND marketingMethod != "ORGANIC". You can't use AND between values in a single condition, for example: WHERE marketingMethod != "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 offerId, clicks
FROM ProductPerformanceView
WHERE clicks > 100
  AND marketingMethod = 'ADS'
  AND date BETWEEN '2020-08-01' AND '2020-08-31'

The WHERE clause doesn't support OR. Operators are case-sensitive. For a complete list of operators, see grammar.

ORDER BY (optional)

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

Specify the order by a fieldName, 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 offerId, clicks
FROM ProductPerformanceView
WHERE date BETWEEN '2020-08-01' AND '2020-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 offerId, then in descending order by number of impressions, then in descending order by number of clicks:

SELECT offerId, impressions, clicks
FROM ProductPerformanceView
WHERE date BETWEEN '2020-08-01' AND '2020-08-31'
ORDER BY
  offerId,
  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 offerId, impressions
FROM ProductPerformanceView
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.

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 for the full list of available relative date ranges.