Queries for metric and segment fields can be sent to the reports.search
method. To construct a query in the Merchant Center Query Language, you need to first compose the
query using the language grammar
.
A query is made up of a number of clauses:
-
SELECT -
FROM -
WHERE -
ORDER BY -
LIMIT
Clauses use field names , table names , operators , conditions , and orderings to help you select the desired data. Once combined into a single query, a request can be made using the Google Content API for Shopping. Let's look at how each clause can be used.
Clauses
SELECT
The SELECT
clause specifies a set of fields to fetch in the request. SELECT
takes a comma-separated list of segment fields and metrics, returning the values
in the response. The SELECT
clause is requiredin a query.
Here is a sample query that selects click metrics from a given table:
SELECT
metrics
.
clicks
FROM
MerchantPerformanceView
WHERE
segments
.
date
BETWEEN
‘
2020
-
08
-
01
’
AND
‘
2020
-
08
-
31
’
You can also query different field types in a single request:
SELECT
segments
.
date
,
segments
.
program
,
metrics
.
impressions
,
metrics
.
clicks
FROM
MerchantPerformanceView
WHERE
segments
.
date
BETWEEN
‘
2020
-
08
-
01
’
AND
‘
2020
-
08
-
31
’
-
Segment fields
-
segments.date -
segments.program
-
-
Metrics
-
metrics.impressions -
metrics.clicks
-
Some fields are not allowed in the SELECT
clause due to the following
restriction:
- Querying segment fields without at least one metric field.
Information related to the above condition can be found in our reference docs.
FROM
The FROM
clause specifies the table to fetch data from in the request. The
table in the FROM
clause defines what fields can be used by all of the other
clauses for the given query. Only a single table can be specified in the FROM
clause. Currently, only the MerchantPerformanceView table is supported.
The FROM
clause is requiredin a query to the search
method on the reports
service.
WHERE
The WHERE
clause specifies conditions to apply when filtering data for the
request. When using the WHERE
clause, one or more conditions can be specified
using AND
to separate them. Each condition should follow the pattern field_name Operator value
. Any segments field can be used in the WHERE
clause, but metrics fields need to be specified in the SELECT
clause to be
used in the WHERE
clause. The WHERE
clause is requiredin a query,
because you must always specify the date range for when you want your
performance data returned.
The following is an example of using WHERE
to return metrics from a given time
period:
SELECT
segments
.
offer_id
,
metrics
.
impressions
FROM
MerchantPerformanceView
WHERE
segments
.
date
BETWEEN
‘
2020
-
08
-
01
’
AND
‘
2020
-
08
-
31
’
You can combine multiple conditions to filter the data. This example will return the number of clicks per offer for the SHOPPING_ADS program where the clicks > 100 within the given 30-day period.
SELECT
segments
.
offer_id
,
segments
.
program
,
metrics
.
clicks
FROM
MerchantPerformanceView
WHERE
metrics
.
clicks
>
100
AND
segments
.
program
=
SHOPPING_ADS
AND
segments
.
date
BETWEEN
‘
2020
-
08
-
01
’
AND
‘
2020
-
08
-
31
’
;
In the following query, you'll notice that segments.date
was selected.
Regardless about whether you select segments.date
, a finite date range in the WHERE
clause always has to be provided to retrieve performance data.
SELECT
segments
.
date
,
metrics
.
clicks
FROM
MerchantPerformanceView
WHERE
segments
.
date
BETWEEN
‘
2020
-
08
-
01
’
AND
‘
2020
-
08
-
31
’
When filtering, the case-sensitivity of your operator is important to keep in mind.
For a complete list of operators, consult the language grammar .
ORDER BY
The ORDER BY
clause specifies the order in which the results are to be
returned. This allows you to arrange the data in ascending or descending order
based on a field name. Each ordering is specified as a field_name
followed by ASC
or DESC
. If neither ASC
nor DESC
is specified, the order defaults
to ASC
. Only fields specified in the SELECT
clause can be used in the ORDER BY
clause. The ORDER BY
clause is optionalin a query.
The following query orders the returned rows by number of clicks from highest to lowest:
SELECT
segments
.
offer_id
,
metrics
.
clicks
FROM
MerchantPerformanceView
WHERE
segments
.
date
BETWEEN
‘
2020
-
08
-
01
’
AND
‘
2020
-
08
-
31
’
ORDER
BY
metrics
.
clicks
DESC
You can specify multiple fields in the ORDER BY
clause using a comma-separated
list. The ordering will occur in the same sequence as specified in the query.
For example, in this query, the results will be sorted in ascending order by offer_id
, then in descending order by number of impressions, then in
descending order by number of clicks:
SELECT
segments
.
offer_id
,
metrics
.
impressions
,
metrics
.
clicks
FROM
MerchantPerformanceView
WHERE
segments
.
date
BETWEEN
‘
2020
-
08
-
01
’
AND
‘
2020
-
08
-
31
’
ORDER
BY
segments
.
offer_id
,
metrics
.
impressions
DESC
,
metrics
.
clicks
DESC
LIMIT
The LIMIT
clause allows you to specify the number of results to be returned.
This is useful if you're only interested in a summary.
For example, LIMIT
can be used to restrict the total number of results for the
following query:
SELECT
segments
.
program
,
segments
.
offer_id
,
metrics
.
impressions
FROM
MerchantPerformanceView
WHERE
segments
.
date
BETWEEN
‘
2020
-
08
-
01
’
AND
‘
2020
-
08
-
31
’
ORDER
BY
metrics
.
impressions
DESC
LIMIT
50
